שינוי תרגומי SQL באמצעות YAML
קובצי YAML של הגדרות אישיות מאפשרים לכם להתאים אישית את האופן שבו BigQuery מתרגם קוד SQL ממסד הנתונים של המקור במהלך ההעברה. שירות ההעברה ל-BigQuery תומך בשני סוגים של קובצי תצורה מסוג YAML: קובצי תצורה לתרגום של *.config.yaml, שנותנים למנוע התרגום כללים דטרמיניסטיים, וקובצי תצורה מבוססי-Gemini של *.ai_config.yaml, שנותנים ל-Gemini כללים בשפה טבעית.
כדי להתאים אישית תרגום SQL באמצעות קובצי YAML של הגדרות אישיות:
- יצירת קובצי תצורה: יוצרים קובצי תצורה דטרמיניסטיים לתרגום של
*.config.yaml, קובצי תצורה מבוססי-Gemini של*.ai_config.yamlאו שני סוגי קובצי התצורה כדי לשלב כללים מבניים עם התאמה אישית של Gemini. - לספק את הקבצים למשימת תרגום: שולחים את קובצי התצורה כשמתרגמים שאילתה אחת או קובצי SQL באצווה.
- בדיקת פלט התרגום: מאתרים את הצעות ה-SQL וה-Gemini המתורגמות בתשובה כשמתרגמים שאילתה אחת, או ב-Cloud Storage כשמתרגמים קובצי SQL באצווה.
סוגים של קובצי YAML להגדרות אישיות
בטבלה הבאה מוצגת השוואה בין שני הסוגים של קובצי YAML להגדרות אישיות:
| תכונה | הגדרות התרגום
(*.config.yaml) |
הגדרה מבוססת-Gemini
(*.ai_config.yaml) |
|---|---|---|
| איך הכללים חלים | מנוע התרגום מחיל את הכללים ככללים דטרמיניסטיים על ה-SQL המנותח, כך שאותו קלט ואותה הגדרה תמיד מפיקים את אותו פלט. | Gemini מיישם את הכללים כהוראות בשפה טבעית וכדוגמאות שמוחלות על טקסט ה-SQL. הפלט יכול להשתנות בין הפעלות, לכן תמיד צריך לבדוק ולאמת את ההצעות לפני שמשתמשים בהן. |
| מה אפשר לשנות | מנוע התרגום יכול להמיר כל אובייקט או הגדרה שהוא מזהה. זה כולל שמות וסוגים של מסדי נתונים, סכימות, טבלאות, עמודות ופונקציות, מאפייני טבלאות כמו חלוקה למחיצות, אשכולות ותפוגה, ברירות מחדל של סשנים, חתימות של פונקציות בהגדרת המשתמש (UDF), אופטימיזציות ופלט של הצהרות כמו סגנון GROUP BY ו-SELECT *. |
Gemini יכול לשנות כל טרנספורמציית SQL שאתם יכולים לתאר בשפה טבעית. זה כולל עיצוב והערות, החלפת תבניות, כתיבה מחדש של מבנים שהמנוע לא מתרגם כמו שרוצים, ויצירת הסבר על התרגום. |
| דרישות |
|
|
| איפה התוצאה מופיעה | התוצאה מופיעה ב-SQL המתורגם עצמו, למשל בתגובת התרגום או בספריית הפלט sql/ ב-Cloud Storage. |
התוצאה מופיעה כהצעות נפרדות של Gemini במסוף Google Cloud , בשדות נפרדים של הצעות או בקובצי פלט ב-Google Cloud CLI ובשרת MCP, או בספריות פלט נפרדות של *_suggestion/ ב-Cloud Storage.
מידע נוסף זמין במאמר איפה אפשר למצוא את הפלט של התרגום. |
| שם קובץ | שמות הקבצים צריכים להסתיים ב-.config.yaml, למשל rename-tables.config.yaml. |
שמות הקבצים צריכים להסתיים ב-.ai_config.yaml, למשל style-rules.ai_config.yaml. |
שירות התרגום מתעלם מכל קובץ YAML בקלט שלא מסתיים ב-.config.yaml או ב-.ai_config.yaml, ומדווח על הקובץ שהתעלם ממנו ביומן התרגום. |
||
| חומרי עזר | מידע נוסף זמין בקטעים הנחיות ליצירת קובץ YAML להגדרות אישיות ודוגמאות לקובצי YAML להגדרות אישיות שבדף הזה. | מידע נוסף זמין בקטע יצירת קובץ YAML של הגדרות שמבוססות על Gemini שבדף הזה. |
איך שני סוגי הכללים מוחלים
משימת תרגום מחילה את כללי ההגדרה בסדר הבא:
- Gemini מחיל
SOURCE_SQLכללים ב-*.ai_config.yamlקבצים על קלט ה-SQL המקורי כדי ליצור קוד מקור משוכתב ב-SQL. - מנוע התרגום מתרגם את ה-SQL של המקור שנכתב מחדש בשלב הקודם, או את ה-SQL של הקלט המקורי אם לא חלים כללי
SOURCE_SQL, ומחיל את הכללים בקובצי*.config.yamlכדי ליצור את ה-SQL המתורגם. - Gemini מחיל את הכללים של
TARGET_SQLבקובצי*.ai_config.yamlעל ה-SQL המתורגם מהשלב הקודם, כדי ליצור SQL יעד משוכתב. אם קובצי ה-*.ai_config.yamlכוללים כלליTRANSLATION_EXPLANATION, Gemini יוצר גם הסבר טקסטואלי ל-SQL המתורגם.
מומלץ להשתמש בכללי *.config.yaml לשינויים מבניים שחוזרים על עצמם, כמו שינוי שמות של אובייקטים, מיפוי סוגים והגדרת חלוקה למחיצות. מומלץ להשתמש בכללי *.ai_config.yaml לשינויים שהמנוע לא יכול לבטא, או לניקוי קלט שלא ניתן לנתח. אפשר להשתמש בשני סוגי קובצי התצורה באותה משימה.
הוספת קובצי תצורה לתרגום
אפשר להשתמש בכמה קובצי תצורה במשימת תרגום אחת במסוף Google Cloud , ב-CLI של gcloud, ב-BigQuery Migration API ובשרת ה-MCP של BigQuery Migration Service. מידע נוסף זמין במאמר החלת כמה הגדרות YAML.
כדי לספק קובצי תצורה למשימת תרגום, בוחרים באחת מהאפשרויות הבאות:
המסוף
- כדי להשתמש בקובצי תצורה כשמתרגמים שאילתה אחת, מציינים אותם בהגדרות התרגום של המתרגם האינטראקטיבי:
-
*.config.yaml: בשדה Translation Configuration Source Location שבהגדרות התרגום, מציינים נתיב ב-Cloud Storage לקובץ התצורה. -
*.ai_config.yaml: יוצרים כללי התאמה אישית שנשמרים ב-__default.ai_config.yaml, או מציינים נתיב ב-Cloud Storage.
-
- כדי להשתמש בקובצי תצורה בזמן תרגום של קובצי SQL באצווה:
-
*.config.yaml: מעלים את קובצי התצורה לספריית הקלט ב-Cloud Storage, ואז בהגדרות התרגום, מציינים נתיב לספרייה בשדה פרטי מיקום הקובץ. -
*.ai_config.yaml: מעלים את קובצי התצורה לספריית הקלט ב-Cloud Storage, ואז בהגדרות התרגום בוחרים באפשרות Optional settings > Gemini AI suggestions.
-
gcloud
- כשמשתמשים בפקודה
gcloud alpha bq migration translate, משתמשים בדגל--translation-config-filesכדי לציין קובצי*.config.yamlאו*.ai_config.yaml. - כשמשתמשים בפקודה
gcloud alpha bq migration translate-batch, מבצעים אחת מהפעולות הבאות:-
*.config.yaml: כוללים את הקבצים בספריית הקלט שמעבירים אל--source-gcs-urisאו אל--source-local-dirs, או מעבירים קבצים ספציפיים עם הדגל--source-gcs-filesאו--source-local-files. -
*.ai_config.yaml: כוללים את הקבצים בספריית הקלט שמעבירים אל--source-gcs-urisאו אל--source-local-dirs, או שמעבירים קבצים ספציפיים עם הדגל--source-gcs-filesאו--source-local-files, ומוסיפים את הדגל--enable-ai-suggestion.
-
API
ב-BigQuery Migration API, בין שאתם מתרגמים שאילתה אחת או קובצי SQL באצווה, בצעו את הפעולות הבאות:
-
*.config.yaml: מעלים את הקבצים בנתיב הקלט ב-source_spec.base_uri, מעבירים רשומתgcs_file_pathב-source_specכשהקובץ לא נמצא ב-base_uri, או מעבירים רשומתliteralמוטבעת ב-source_spec. -
*.ai_config.yaml: מספקים את הקבצים ב-source_specבקטעbase_uri, כרשומה שלgcs_file_pathכשהקובץ לא נמצא בקטעbase_uri, או כרשומה שלliteralבתוך השורה, וכוללים את"suggestion"ב-target_types– לדוגמה,"target_types": ["sql", "suggestion"].
MCP
בשרת ה-MCP של BigQuery Migration Service:
- כדי לתרגם שאילתה אחת (
translate_query):-
*.config.yaml: מעבירים רשומותtranslationConfigsבתוך השורה. -
*.ai_config.yaml: מעבירים רשומותaiSuggestionConfigsבתוך השורה.
-
- כדי לתרגם קובצי SQL באצווה (
translate_batch_queries):-
*.config.yaml: מעלים את הקבצים ב-sourceBaseUri, או מפרטים אותם ב-configurationFilePathsאם הם לא ב-sourceBaseUri. -
*.ai_config.yaml: מעלים את הקבצים ב-sourceBaseUri, או מפרטים אותם ב-configurationFilePathsאם הם לא ב-sourceBaseUri, ומגדירים אתenableAiSuggestionsל-true.
-
איפה אפשר למצוא את התרגומים
בהתאם לקובצי התצורה שאתם מספקים, משימת תרגום יכולה להפיק את הפלטים הבאים:
- SQL מתורגם ממנוע התרגום אחרי שהוא מחיל את הכללים בקובצי
*.config.yaml. - קוד המקור ב-SQL ששוכתב על ידי Gemini אחרי החלת
SOURCE_SQLכללים ב-*.ai_config.yamlקבצים. - SQL יעד משוכתב מ-Gemini אחרי שהוא מחיל
TARGET_SQLכללים בקובצי*.ai_config.yaml. - הסבר על התרגום מ-Gemini כשקובצי
*.ai_config.yamlכוללים כלליTRANSLATION_EXPLANATION, או כשמבקשים הסבר לפי דרישה.
המיקום שבו הפלטים האלה מופיעים תלוי באופן שבו אתם מריצים את התרגום:
- כדי למצוא פלטים כשמתרגמים שאילתה אחת במסוףGoogle Cloud או ב-CLI של gcloud, קראו את הקטע בדיקת הפלט של התרגום במדריך למתרגם SQL אינטראקטיבי.
- כדי למצוא את הפלט כשמתרגמים קובצי SQL באצווה ב-Cloud Storage או בודקים אותם במסוף Google Cloud , קראו את הקטע בדיקת הפלט של התרגום במדריך למתרגם SQL באצווה.
- כדי למצוא פלטים כשמשתמשים ב-BigQuery Migration API, עיינו בקטע אחזור תוצאות במדריך של BigQuery Migration API. כדי לתרגם שאילתה אחת, מוסיפים את נתיבי הפלט הרצויים –
sql/<var>PATH</var>,source_sql_query_customization_suggestion/<var>PATH</var>,target_sql_query_customization_suggestion/<var>PATH</var>אוtranslation_explanation_suggestion/<var>PATH</var>– אלtarget_return_literalsכדי לקבל אותם ב-translatedLiterals. - כדי למצוא פלט כשמשתמשים בשרת MCP של BigQuery Migration Service, קראו את המאמר שימוש בשרת MCP של BigQuery Migration Service. הכלים
translate_queryו-get_translationמחזירים את הערכיםtranslatedQuery,sourceSqlSuggestionו-targetSqlSuggestion, והכליexplain_translationמחזיר הסבר על התרגום.
הדרישות לגבי קובץ התרגום *.config.yaml
כדי שקובץ תרגום *.config.yaml יהיה תואם לשירות ההעברה ל-BigQuery, הוא צריך לעמוד בדרישות הבאות:
- כשמעלים קובצי
*.config.yamlבנתיב הקלט של Cloud Storage למשימת תרגום באצווה, קובצי התצורה שנמצאים בכל מקום בנתיב הקלט חלים על המשימה כולה. הם לא צריכים להיות ברמת הבסיס או להיות מועתקים לכל ספריית משנה. במאמרים יצירת קטגוריות והעלאת אובייקטים ממערכת קבצים מוסבר איך יוצרים קטגוריות ומעלים קבצים ל-Cloud Storage. - גודל הקובץ של קובץ
*.config.yamlיחיד לא יכול להיות יותר מ-1MB. - הגודל הכולל של כל קובצי ה-
*.config.yamlשמשמשים במשימת תרגום SQL אחת לא יכול להיות יותר מ-4MB. - אם אתם משתמשים בתחביר של
regexלהתאמת שמות, השתמשו ב-RE2/J. - כל שמות קובצי התצורה של התרגום חייבים להסתיים ב-
.config.yaml, לדוגמהchange-case.config.yaml. השםconfig.yamlלבדו הוא לא שם חוקי לקובץ התצורה.
הנחיות ליצירת קובץ YAML של הגדרות אישיות
בקטע הזה מפורטות כמה הנחיות כלליות ליצירת קובץ YAML של הגדרות אישיות:
כותרת
כל קובץ *.config.yaml חייב לכלול כותרת type ברמה העליונה, שמציינת את הקטגוריה של כללי ההגדרות בקובץ:
-
object_rewriter: כותב מחדש את המאפיינים שלrelation, כמוtemporary,ephemeral,partitionLifetime,external,description,partitionו-clustering, ואת סוגי הנתונים שלattribute(type). -
experimental_object_rewriter: כותב מחדש את שמות אובייקטי הפלט באמצעותoutputNameב-global,database,schema,relation,attribute,function,index,sequence,variableו-projection, וממיר סוגי נתונים באופן גלובלי באמצעותglobal.typeConvert. מידע נוסף זמין במאמרים מיפוי שמות פלט והמרת סוגים גלובלית. -
environment: מגדיר את ברירות המחדל ברמת הסשן בקטעsession, כמוdefaultDatabase,schemaSearchPathו-dateFormat. -
metadata: מצהיר על חתימות של פונקציות בהגדרת המשתמש (UDF) (udfs) ומסיר קידומות, סיומות או דפוסי ביטויים רגולריים של שמות קלט במהלך חיפוש מטא-נתונים (nameAlias). -
statement_rewriter: התאמה אישית של פלט ההצהרה, כמו הרחבתSELECT *, עיצוב שלGROUP BYו-ORDER BY(select) ורמת הדיוק של מחרוזות או ערכים עשרוניים (common). -
input_formats: מציין את קידוד התווים של קובצי ה-SQL של הקלט (formats). -
optimizer: הפעלה, השבתה או הגדרה של אופטימיזציות אופציונליות של שאילתות (transformations).
בדוגמה הבאה משתמשים בסוג experimental_object_rewriter כדי להמיר את כל שמות אובייקטי הפלט לאותיות רישיות:
type: experimental_object_rewriter
global:
outputName:
all:
case: UPPERCASE
בחירת ישות
כדי לבצע טרנספורמציות ספציפיות לישות, צריך לציין את ישות ה-SQL של המקור בקובץ התצורה. כל המאפיינים של match הם אופציונליים. צריך להשתמש רק במאפיינים של match שנדרשים לטרנספורמציה. השדה match משווה בין שמות של אובייקטים במקור אחרי פתרון של כל defaultDatabase ו-schemaSearchPath שהוגדרו, חוץ מבכללי outputName כשגם global.outputName מוגדר. מידע נוסף זמין במאמר מיפוי שמות פלט. קובץ ה-YAML הבא חושף מאפיינים שצריך להתאים כדי לבחור ישויות ספציפיות:
match:
database: <literal_name>
schema: <literal_name>
relation: <literal_name>
attribute: <literal_name>
databaseRegex: <regex>
schemaRegex: <regex>
relationRegex: <regex>
attributeRegex: <regex>
תיאור של כל מאפיין של match:
-
databaseאוdb: תואם לשם של מסד הנתונים במקור, שתואם לרכיבproject_idב-BigQuery. -
schema: תואם לשם הסכימה של המקור, שתואם לרכיבdatasetב-BigQuery. -
relation: תואם לשם של טבלת המקור או התצוגה. -
attribute: תואם לשם של עמודת המקור. המאפיין הזה תקף רק לבחירה שלattribute. -
databaseRegexאוdbRegex: התאמה של כל השם שלdatabaseלביטוי רגולרי לא תלוי-רישיות. -
schemaRegex: התאמה של כל השם שלschemaלביטוי רגולרי שלא תלוי-רישיות. -
relationRegex: התאמה של כל השם שלrelationלביטוי רגולרי לא תלוי-רישיות. -
attributeRegex: התאמה של כל השם שלattributeלביטוי רגולרי לא תלוי-רישיות. המאפיין הזה תקף רק לבחירה שלattribute.
לדוגמה, בקובץ ה-YAML הבא, המאפיינים match מציינים את הטבלה testdb.acme.employee לטרנספורמציה של טבלה זמנית.
type: object_rewriter
relation:
-
match:
database: testdb
schema: acme
relation: employee
temporary: true
אפשר להשתמש במאפיינים databaseRegex, schemaRegex, relationRegex ו-attributeRegex כדי לציין ביטויים רגולריים לבחירת קבוצת משנה של ישויות. בדוגמה הבאה, כל הקשרים מהסכימה tmp_schema ב-testdb משתנים לזמניים, כל עוד השם שלהם מתחיל ב-tmp_:
type: object_rewriter
relation:
-
match:
schema: tmp_schema
relationRegex: "tmp_.*"
temporary: true
ההתאמה של מאפיינים מילוליים ושל מאפייני regex לא תלוית-רישיות.
כדי לאכוף התאמה תלויית אותיות רישיות, אפשר להשתמש ב-regex עם דגל i מושבת, כמו בדוגמה הבאה:
match:
relationRegex: "(?-i:<actual_regex>)"
אפשר גם לציין ישויות מלאות באמצעות תחביר מקביל של מחרוזת קצרה. תחביר של מחרוזת קצרה מצפה ל-3 (לבחירת קשר) או 4 (לבחירת מאפיין) קטעי שם שמופרדים בנקודות, כמו בדוגמה testdb.acme.employee. הפלחים מפורשים באופן פנימי כאילו הם הועברו בתור database, schema, relation ו-attribute בהתאמה.
כלומר, השמות מותאמים באופן מילולי, ולכן אי אפשר להשתמש בביטויים רגולריים בתחביר מקוצר. בדוגמה הבאה מוצג השימוש בתחביר של מחרוזת קצרה כדי לציין ישות מלאה בקובץ YAML של הגדרות אישיות:
type: object_rewriter
relation:
-
match: "testdb.acme.employee"
temporary: true
אם שם הטבלה מכיל נקודה, אי אפשר לציין את השם באמצעות תחביר קצר. במקרה כזה, צריך להשתמש בהתאמת אובייקט. בדוגמה הבאה, הטבלה testdb.acme.stg.employee משתנה לטבלה זמנית:
type: object_rewriter
relation:
-
match:
database: testdb
schema: acme
relation: stg.employee
temporary: true
קובץ YAML של הגדרות מקבל גם את key כשם חלופי ל-match. גם key וגם match תומכים בשדות משנה מפורשים של רכיבי שם, וגם בתחביר של מחרוזת קצרה "database.schema.relation" או "database.schema.relation.attribute" שמופרדים בנקודה. מקובל להשתמש ב-key עם תחביר של מחרוזת קצרה, וב-match עם שדות משנה מפורשים.
מסד נתונים שמוגדר כברירת מחדל
בחלק מהניבים של SQL, בעיקר Teradata, אין תמיכה בשם של מסד נתונים במזהים מוגדרים (schema.table). במקרה כזה, צריך להשתמש באובייקט match ולהשמיט את המאפיין database. לחלופין, אפשר להגדיר את defaultDatabase – לדוגמה, בקטע type: environment – כדי שתוכלו להתאים למסד הנתונים שמוגדר כברירת מחדל בכללים של match לאובייקטים או למחרוזות קצרות.
סוגי מאפייני היעד הנתמכים
אתם יכולים להשתמש בקובץ התצורה בפורמט YAML כדי לבצע טרנספורמציות של סוגי מאפיינים, שבהן אתם משנים את סוג הנתונים של עמודה מסוג המקור לסוג היעד. קובץ ה-YAML של ההגדרות תומך בסוגי היעדים הבאים:
BOOLEANTINYINTSMALLINTINTEGERBIGINTFLOATDOUBLENUMERIC(תומך בדיוק ובקנה מידה אופציונליים, כמוNUMERIC(18, 2))TIMETIMETZDATEDATETIMETIMESTAMPTIMESTAMPTZ-
CHAR(אפשר להוסיף דיוק, למשלCHAR(42)) -
VARCHAR(אפשר להוסיף דיוק, למשלVARCHAR(42))
דוגמאות להגדרות YAML
בקטע הזה מופיעות דוגמאות ליצירת קובצי YAML שונים של הגדרות, לשימוש בתרגומי SQL. בכל דוגמה מתואר תחביר ה-YAML לשינוי תרגום ה-SQL בדרכים ספציפיות, וגם תיאור קצר.
בכל דוגמה מופיע גם התוכן של קובץ teradata-input.sql או hive-input.sql וקובץ bq-output.sql, כדי שתוכלו להשוות בין ההשפעות של קובץ YAML של הגדרות אישיות על תרגום של שאילתת BigQuery SQL.
בדוגמאות הבאות, הדיאלקט של קלט ה-SQL הוא Teradata או Apache Hive, והדיאלקט של הפלט הוא BigQuery SQL.
שינוי האותיות הרישיות בשם האובייקט
קובץ ה-YAML הבא משנה את האותיות הרישיות של שמות אובייקטים ב-SQL ל-UPPERCASE או ל-LOWERCASE. אפשר להגדיר את case באובייקטים מסוג all או לשנות אותו לסוגי אובייקטים ספציפיים (database, schema, relation, attribute, function, sequence, index, variable או projection):
type: experimental_object_rewriter
global:
outputName:
all:
case: UPPERCASE
database:
case: LOWERCASE
attribute:
case: LOWERCASE
תרגום SQL עם קובץ התצורה הזה ב-YAML עשוי להיראות כך:
teradata-input.sql |
create table TESTDB.testschema.x(A int); select * from TESTDB.testschema.x; |
bq-output.sql |
CREATE TABLE testdb.TESTSCHEMA.X ( a INT64 ) ; SELECT X.* FROM testdb.TESTSCHEMA.X ; |
הגדרת הטבלה כזמנית
קובץ ה-YAML הבא משנה טבלה רגילה לטבלה זמנית:
type: object_rewriter
relation:
-
match: "testdb.testschema.x"
temporary: true
תרגום SQL עם קובץ התצורה הזה ב-YAML עשוי להיראות כך:
teradata-input.sql |
create table testdb.testschema.x(a int); |
bq-output.sql |
CREATE TEMPORARY TABLE x ( a INT64 ) ; |
הפיכת הטבלה לזמנית
קובץ ה-YAML הבא משנה טבלה רגילה לטבלה זמנית שתוקפה יפוג תוך 60 שניות.
type: object_rewriter
relation:
-
match: "testdb.testschema.x"
ephemeral:
expireAfterSeconds: 60
תרגום SQL עם קובץ התצורה הזה ב-YAML עשוי להיראות כך:
teradata-input.sql |
create table testdb.testschema.x(a int); |
bq-output.sql |
CREATE TABLE testdb.testschema.x ( a INT64 ) OPTIONS( expiration_timestamp=timestamp_add(current_timestamp(), interval 60 SECOND) ); |
הגדרת תפוגה למחיצה
הגדרת ה-YAML הבאה משנה את תאריך התפוגה של טבלה מחולקת ליום אחד:
type: object_rewriter
relation:
-
match: "testdb.testschema.x"
partitionLifetime:
expireAfterSeconds: 86400
תרגום SQL עם קובץ התצורה הזה ב-YAML עשוי להיראות כך:
teradata-input.sql |
create table testdb.testschema.x(a date, b int) partition by (a); |
bq-output.sql |
CREATE TABLE testdb.testschema.x ( a DATE, b INT64 ) PARTITION BY a OPTIONS( partition_expiration_days=1 ); |
שינוי המיקום או הפורמט החיצוני של טבלה
קובץ ה-YAML הבא משנה את המיקום החיצוני והפורמט של טבלה:
type: object_rewriter
relation:
-
match: "testdb.testschema.x"
external:
locations: "gs://path/to/department/files"
format: ORC
תרגום SQL עם קובץ התצורה הזה ב-YAML עשוי להיראות כך:
teradata-input.sql |
create table testdb.testschema.x(a int); |
bq-output.sql |
CREATE EXTERNAL TABLE testdb.testschema.x ( a INT64 ) OPTIONS( format='ORC', uris=[ 'gs://path/to/department/files' ] ); |
הגדרה או שינוי של תיאור הטבלה
קובץ ה-YAML הבא מגדיר את התיאור של טבלה:
type: object_rewriter
relation:
-
match: "testdb.testschema.x"
description:
text: "Example description."
תרגום SQL עם קובץ התצורה הזה ב-YAML עשוי להיראות כך:
teradata-input.sql |
create table testdb.testschema.x(a int); |
bq-output.sql |
CREATE TABLE testdb.testschema.x ( a INT64 ) OPTIONS( description='Example description.' ); |
הגדרה או שינוי של חלוקת הטבלה למחיצות
קובץ ה-YAML הבא משנה את סכימת החלוקה למחיצות של טבלה באמצעות add, remove או set, כאשר set מחליף את כל עמודות המחיצות הקיימות:
type: object_rewriter
relation:
-
match: "testdb.testschema.x"
partition:
simple:
add: [a]
-
match: "testdb.testschema.y"
partition:
simple:
remove: [a]
תרגום SQL עם קובץ התצורה הזה ב-YAML עשוי להיראות כך:
teradata-input.sql |
create table testdb.testschema.x(a date, b int); create table testdb.testschema.y(a date, b int) partition by (a); |
bq-output.sql |
CREATE TABLE testdb.testschema.x ( a DATE, b INT64 ) PARTITION BY a; CREATE TABLE testdb.testschema.y ( a DATE, b INT64 ) ; |
ה-YAML הבא משנה את סכימת החלוקה למחיצות של טבלה, כך שהיא מחולקת למחיצות לפי קטגוריית טווח:
type: object_rewriter
relation:
-
match: "testdb.testschema.mytable"
partition:
range_bucket:
column: "id"
start: 0
end: 100
step: 10
options:
require_partition_filter: "TRUE"
תרגום SQL עם קובץ התצורה הזה ב-YAML עשוי להיראות כך:
snowflake-input.sql |
create table testdb.testschema.mytable ( date timestamp_ntz, id number, content variant ); |
bq-output.sql |
CREATE TABLE TESTDB.TESTSCHEMA.MYTABLE ( DATE DATETIME, ID INT64, CONTENT JSON ) PARTITION BY RANGE_BUCKET(ID, generate_array(0, 100, 10)) OPTIONS( require_partition_filter=TRUE ); |
הגדרה או שינוי של אשכולות בטבלה
קובץ ה-YAML הבא משנה את סכימת האשכולות של טבלה באמצעות add, remove או set, כאשר set מחליף את כל עמודות האשכולות הקיימות:
type: object_rewriter
relation:
-
match: "testdb.testschema.x"
clustering:
add: [a]
-
match: "testdb.testschema.y"
clustering:
remove: [b]
תרגום SQL עם קובץ התצורה הזה ב-YAML עשוי להיראות כך:
hive-input.sql |
create table testdb.testschema.x(a int, b int); create table testdb.testschema.y(a int, b int) clustered by (b) into 16 buckets; |
bq-output.sql |
CREATE TABLE testdb.testschema.x ( a INT64, b INT64 ) CLUSTER BY a; CREATE TABLE testdb.testschema.y ( a INT64, b INT64 ) ; |
שינוי הסוג של מאפיין עמודה
ההגדרה הבאה של YAML משנה את סוג הנתונים של מאפיין של עמודה:
type: object_rewriter
attribute:
-
match:
database: testdb
schema: testschema
attributeRegex: "a+"
type:
target: NUMERIC(10,2)
אתם יכולים להמיר את סוג הנתונים של המקור לכל אחד מסוגי מאפייני היעד הנתמכים.
תרגום SQL עם קובץ התצורה הזה ב-YAML עשוי להיראות כך:
teradata-input.sql |
create table testdb.testschema.x(a int, b int, aa int); |
bq-output.sql |
CREATE TABLE testdb.testschema.x ( a NUMERIC(31, 2), b INT64, aa NUMERIC(31, 2) ) ; |
אם אין המרה ישירה בין סוג המקור לסוג היעד – לדוגמה, כשממירים עמודת INT שמאחסנת תאריכים בפורמט YYYYMMDD לעמודת DATE – צריך לספק ביטויי המרה של GoogleSQL ב-sourceToTarget וב-targetToSource. משתמשים ב-? כפלייסהולדר של העמודה או של ערך הביטוי. לדוגמה:
type: object_rewriter
attribute:
-
match: "testdb.acme.employee.jd"
type:
target: DATE
sourceToTarget: "parse_date('%Y%m%d', cast(? as string))"
targetToSource: "cast(format_date('%Y%m%d', cast(? as date)) as int64)"
תרגום SQL עם קובץ התצורה הזה ב-YAML עשוי להיראות כך:
teradata-input.sql |
create table testdb.acme.employee(eid int, jd int); insert into testdb.acme.employee select 42, 20011213; select eid, jd from testdb.acme.employee e where jd > 20010101; |
bq-output.sql |
CREATE TABLE testdb.acme.employee ( eid INT64, jd DATE ) ; INSERT INTO testdb.acme.employee SELECT 42, parse_date('%Y%m%d', CAST(20011213 as STRING)) ; SELECT eid, CAST(format_date('%Y%m%d', jd) as INT64) AS jd FROM testdb.acme.employee AS e WHERE CAST(format_date('%Y%m%d', jd) as INT64) > 20010101 ; |
הוספת חיבור לאגם נתונים חיצוני
הגדרת ה-YAML הבאה מסמנת את טבלת המקור כטבלה חיצונית שמפנה לנתונים שמאוחסנים באגם נתונים חיצוני, שצוין על ידי חיבור לאגם נתונים.
type: object_rewriter
relation:
-
match: "testdb.testschema.x"
external:
connection_id: "connection_test"
תרגום SQL עם קובץ התצורה הזה ב-YAML עשוי להיראות כך:
hive-input.sql |
CREATE TABLE testdb.testschema.x ( a VARCHAR(150), b INT ); |
bq-output.sql |
CREATE EXTERNAL TABLE testdb.testschema.x ( a STRING, b INT64 ) WITH CONNECTION `connection_test` OPTIONS( ); |
שינוי קידוד התווים של קובץ קלט
כברירת מחדל, שירות ההעברה ל-BigQuery מנסה לזהות באופן אוטומטי את קידוד התווים של קובצי הקלט. במקרים שבהם שירות ההעברה ל-BigQuery עלול לזהות באופן שגוי את הקידוד של קובץ, אתם יכולים להשתמש בקובץ YAML של הגדרות אישיות כדי לציין את קידוד התווים באופן מפורש.
בקובץ ה-YAML הבא, קידוד התווים המפורש של קובץ הקלט מוגדר כ-ISO-8859-1. אפשר להתאים קובצי קלט באמצעות נתיב מדויק (path), תבנית glob (pathGlob) או ביטוי רגולרי (pathRegex):
type: input_formats
formats:
- source:
pathGlob: "*.sql"
contents:
raw:
charset: iso-8859-1
המרת סוג גלובלית
קובץ ה-YAML הבא משנה סוג נתונים אחד לסוג נתונים אחר בכל הסקריפטים, ומציין סוג נתונים של מקור שצריך להימנע ממנו בסקריפט שעבר טרנספילציה. זה שונה מההגדרה שינוי הסוג של מאפיין עמודה, שבה משתנה רק סוג הנתונים של מאפיין אחד.
שירות התרגום תומך בהמרות הבאות של סוגי נתונים:
DATETIMEעדTIMESTAMP-
TIMESTAMPעדDATETIME(אפשר להוסיף אזור זמן) -
TIMESTAMP WITH TIME ZONE(timestamptz) עדDATETIME(אפשר להוסיף אזור זמן) -
CHARעדVARCHAR(אפשר להוסיף אתprecisionActionו-comparisonBehavior) -
VARCHARעדVARCHAR(אפשר להוסיף את הפרמטרים האופציונלייםprecisionActionו-comparisonBehavior)
בדוגמה הבאה, קובץ ה-YAML של ההגדרות ממיר סוג נתונים TIMESTAMP ל-DATETIME.
type: experimental_object_rewriter
global:
typeConvert:
timestamp: DATETIME
המרת סוגי CHAR ו-VARCHAR
בניבים כמו Teradata, ערכי CHAR(N) מרופדים מימין ברווחים, וכתיבת מחרוזות נחתכת לאורך העמודה המוצהר.
כברירת מחדל, השוואות של מחרוזות הן לא תלויות-רישיות (NOT CASESPECIFIC) ומתעלמות מרווחים בסוף המחרוזת. כברירת מחדל, מנוע התרגום מחקה את הסמנטיקה הזו ב-BigQuery. הוא מוסיף מעטפות של ריפוד מימין (CONCAT) וחיתוך (SUBSTR) בהמרות סוגים ובכתיבות, מעטפות של UPPER ו-RTRIM בהשוואות ומעטפות של UPPER במפתחות GROUP BY.
אפשר להגדיר את השדות char ו-varchar בקטע global.typeConvert באמצעות המאפיינים הבאים:
-
target: צריך להגדיר לערךVARCHAR. הגדרתtarget: VARCHARב-charמסירה את המרווח הפנימי מימין (CONCAT) שלCHAR, גם כש-precisionActionהואPRESERVE. -
precisionAction: שולט בדיוק של אורך התווים וב-wrappers של חיתוךSUBSTR:-
PRESERVE(ברירת מחדל): שומר על דיוק אורך התווים בביטויים ופולט מעטפות חיתוך שלSUBSTRבזמן המרה או הוספה לעמודותCHAR(N)אוVARCHAR(N). כדי להפיק גם אורכי עמודות שלSTRING(N)ב-DDL שלCREATE TABLE, קראו את המאמר הגדרת רמת הדיוק של מחרוזות. -
DROP: משמיט את הדיוק של אורך התווים בביטויים ואת מעטפות החיתוך שלSUBSTR.
-
-
comparisonBehavior: שולט ב-wrappers שלRTRIMו-UPPERלהשוואות ולמפתחותGROUP BY. אם מגדירים ערך שהוא לאSTRICTב-charאו ב-varchar, הוא חל על שני הסוגים. לכן, כשמגדירים את שני המפתחות, צריך להגדיר בהם את אותו ערך:-
STRICT(ברירת המחדל): מדמה גם ריפוד של רווחים בסוף (RTRIM) וגם איסוף לא תלוי-רישיות (UPPER). -
PADDING_AWARE: מדמה רק ריפוד של רווחים נגררים (RTRIM) ומשמיט אתUPPER. -
CASE_AWARE: מדמה רק איסוף לא תלוי-רישיות (UPPER) ומשמיט אתRTRIM. -
NONE: משמיט את רכיבי ה-wrapper שלRTRIMו-UPPER, כך שהפלט משתמש בהשוואות ישירות ובביטוייGROUP BY.
-
קובץ ה-YAML הבא ממיר את CHAR ל-VARCHAR ומסיר את ה-wrappers של החיתוך (SUBSTR), הרווח בסוף (RTRIM) וחוסר הרגישות לאותיות רישיות (UPPER) מהעמודות CHAR ו-VARCHAR:
type: experimental_object_rewriter
global:
typeConvert:
char:
target: VARCHAR
precisionAction: DROP
comparisonBehavior: NONE
varchar:
target: VARCHAR
precisionAction: DROP
comparisonBehavior: NONE
תרגום SQL עם קובץ התצורה הזה ב-YAML עשוי להיראות כך:
teradata-input.sql |
create table r(a char(10), b varchar(20)); select cast(b as char(5)) from r where a = b group by 1; |
bq-output.sql |
CREATE TABLE r ( a STRING, b STRING ) ; SELECT b FROM r WHERE a = b GROUP BY 1 ; |
הגדרת אזור הזמן שמוגדר כברירת מחדל
לניבים של מסדי נתונים יש סמנטיקה ושמות שונים לסוגי נתונים שונים שקשורים לתאריך ולשעה. שירות התרגום משתמש במונחים הבאים בהגדרות ה-YAML שלו, בלי קשר לשם של סוג הנתונים בניב הקלט:
- הערך
datetimeהוא שילוב שלY-M-D H:M:Sשלא קבוע לאזור זמן מסוים. הערךdatetimeמייצג זמן שעון ולא רגע מסוים. - הערך
timestampמייצג רגע מסוים או מוחלט בזמן, ולכן הוא קבוע באופן משתמע לאזור זמן מסוים, שיכול להיות הגדרה ברמת הסשן או מסד הנתונים. - הערך
timestamptzמייצג רגע מסוים כמוtimestamp, אבל בניגוד ל-timestamp, הוא כולל הפרש מסוים מאזור הזמן. למרות שהם מייצגים את אותו רגע, הערכים2019-06-01 12:00:00+4ו-2019-06-01 06:00:00-2נחשבים לערכיtimestamptzשונים.
בניבים כמו Teradata, פונקציות שקשורות לתאריך ולשעה, כמו current_date, current_time או current_timestamp, מחזירות חותמות זמן שמבוססות על פרמטר של אזור זמן בסשן שמוגדר באופן משתמע. לעומת זאת, BigQuery תמיד מחזיר חותמות זמן ב-UTC. כדי להבטיח התנהגות עקבית בין שני הניבים, יכול להיות שיהיה צורך להגדיר אזור זמן בהתאם.
אם במסד הנתונים של המקור יש ברירת מחדל שהיא לא UTC, מומלץ לציין את אזור הזמן שמוגדר כברירת מחדל לתרגום. כך אפשר לוודא שהשאילתה המתורגמת תפעל בצורה תקינה, כי אזור הזמן נשמר.
בדוגמה הבאה, קובץ ה-YAML של ההגדרות ממיר את סוגי הנתונים TIMESTAMP ו-TIMESTAMPTZ ל-DATETIME, כשאזור הזמן של היעד מוגדר ל-Europe/Paris.
לרשימה של ערכי מחרוזות חוקיים של אזורי זמן, ראו אזורי זמן.
type: experimental_object_rewriter
global:
typeConvert:
timestamp:
target: DATETIME
timezone: Europe/Paris
timestamptz:
target: DATETIME
timezone: Europe/Paris
תרגום SQL עם קובץ התצורה הזה ב-YAML עשוי להיראות כך:
snowflake-input.sql |
create table x(c_timestamp timestamp_ltz, c_timestamptz timestamp_tz, c_datetime timestamp_ntz); select c_timestamp from x where c_timestamp > current_timestamp(0); select c_timestamptz from x where c_timestamptz > cast(current_timestamp(0) as timestamp_tz); select c_datetime from x where c_datetime > cast(current_timestamp(0) as timestamp_ntz); |
bq-output.sql |
CREATE TABLE X ( C_TIMESTAMP DATETIME, C_TIMESTAMPTZ DATETIME, C_DATETIME DATETIME ) ; SELECT C_TIMESTAMP FROM X WHERE C_TIMESTAMP > datetime(current_timestamp(), 'Europe/Paris') ; SELECT X.C_TIMESTAMPTZ FROM X WHERE X.C_TIMESTAMPTZ > datetime(current_timestamp(), 'Europe/Paris') ; SELECT C_DATETIME FROM X WHERE C_DATETIME > datetime(current_timestamp(), 'Europe/Paris') ; |
בדוגמה הבאה, קובץ ה-YAML של ההגדרות ממיר סוג נתונים של DATETIME ל-TIMESTAMP.
כברירת מחדל, TIMESTAMPTZ מומר ל-TIMESTAMP בלי צורך בהגדרה.
type: experimental_object_rewriter
global:
typeConvert:
datetime:
target: TIMESTAMP
תרגום SQL עם קובץ התצורה הזה ב-YAML עשוי להיראות כך:
snowflake-input.sql |
create table x(c_timestamp timestamp_ltz, c_timestamptz timestamp_tz, c_datetime timestamp_ntz); select c_timestamp from x where c_timestamp > current_timestamp(0); select c_timestamptz from x where c_timestamptz > cast(current_timestamp(0) as timestamp_tz); select c_datetime from x where c_datetime > cast(current_timestamp(0) as timestamp_ntz); |
bq-output.sql |
CREATE TABLE X ( C_TIMESTAMP TIMESTAMP, C_TIMESTAMPTZ TIMESTAMP, C_DATETIME TIMESTAMP ) ; SELECT C_TIMESTAMP FROM X WHERE C_TIMESTAMP > current_timestamp() ; SELECT C_TIMESTAMPTZ FROM X WHERE C_TIMESTAMPTZ > current_timestamp() ; SELECT X.C_DATETIME FROM X WHERE X.C_DATETIME > current_timestamp() ; |
בחירת שינוי בהצהרה
ההגדרה הבאה ב-YAML משנה את היטל הכוכב, GROUP BY, ואת הסעיפים ORDER BY בהצהרות SELECT.
אפשר להזין בשדה select.starProjection את הערכים הבאים:
-
ALLOW: פולט אתSELECT *כששאילתת המקור משתמשת ב-SELECT *או מציגה את כל העמודות בטבלה לפי הסדר. PRESERVE(ברירת המחדל): הפקתSELECT *רק כששאילתת המקור משתמשת ב-SELECT *.-
EXPAND: מרחיב אתSELECT *להפניות לעמודות נפרדות כשהמטא-נתונים של הטבלה זמינים.
השדות select.groupBy ו-select.orderBy תומכים בערכים הבאים:
-
EXPRESSION: הפורמט שלGROUP BYאוORDER BYפריטים כביטויים מלאים. -
ALIAS: הפורמט שלGROUP BYאוORDER BYפריטים באמצעותSELECTשמות סודיים של עמודות, אם הם זמינים. -
INDEX: מעצב פריטים מסוגGROUP BYאוORDER BYבאמצעות מיקומי עמודות שמבוססים על 1, כמוGROUP BY 1.
בדוגמה הבאה, קובץ ה-YAML מגדיר את היטל הכוכב ל-EXPAND.
type: statement_rewriter
select:
starProjection: EXPAND
תרגום SQL עם קובץ התצורה הזה ב-YAML עשוי להיראות כך:
teradata-input.sql |
create table x(a int, b TIMESTAMP); select * from x; |
bq-output.sql |
CREATE TABLE x ( a INT64, b TIMESTAMP ) ; SELECT x.a, x.b FROM x ; |
מפרט UDF
ברוב המקרים, מתרגם ה-SQL של BigQuery יכול לנתח באופן אוטומטי את שפת הגדרת הנתונים (DDL) של פונקציות חיצוניות בהגדרת המשתמש (UDF), כדי לקבוע את סוגי נתוני הקלט והפלט שלהן, גם אם גוף ה-UDF כתוב בשפה חיצונית. עם זאת, בניבים מסוימים של מקורות, יכול להיות שהמתרגם לא יוכל לקמפל באופן אוטומטי את החתימה של ה-UDF. כדי לוודא שהצהרות SQL שמפנות לפונקציות UDF האלה מתורגמות בצורה מדויקת בלי לספק את ה-DDL המקורי, אתם יכולים להשתמש בקובץ תצורה של YAML כדי לרשום באופן מפורש את החתימות של פונקציות UDF.
קובץ ה-YAML הבא של ההגדרות מציין את החתימה של פונקציות בהגדרת המשתמש (UDF) שמשמשות בסקריפטים של המקור. מציינים כל חתימה בניב המקור בתור RETURN_TYPE
FUNCTION_NAME(ARG_NAME ARG_TYPE, ...), כולל שם לכל פרמטר. בדומה לקובצי ZIP של מטא-נתונים, הגדרות של UDF יכולות לעזור ליצור תרגום מדויק יותר של סקריפטים של קלט.
type: metadata
udfs:
- "date parse_short_date(dt int)"
תרגום SQL עם קובץ התצורה הזה ב-YAML עשוי להיראות כך:
teradata-input.sql |
create table x(dt int); select parse_short_date(dt) + 1 from x; |
bq-output.sql |
CREATE TABLE x ( dt INT64 ) ; SELECT date_add(parse_short_date(dt), interval 1 DAY) FROM x ; |
חלק מהניבים של המקור תומכים בפונקציות UDF שחולקות שם בין חתימות שונות של ארגומנטים, לפעמים הן נקראות פונקציות UDF עמוסות. לפונקציות UDF עם עומס יתר, צריך לציין את udf ואת היעד הנפרד name כדי שכל חתימה תמופה לשם פונקציה ייחודי ב-BigQuery:
type: metadata
udfs:
- "int custom_substr(pos int)"
- udf: "int custom_substr(pos int, len int)"
name: "custom_substr_with_len"
הסרת תבניות של מזהים במהלך חיפוש מטא-נתונים
סקריפטים של קלט SQL עשויים להפנות לאובייקטים או לעמודות עם תחילית, סיומת או תבנית שמות שלא נמצאת בהגדרות המטא-נתונים שלכם. לדוגמה, הפניה ל-a_dt כש-DDL של המטא-נתונים מגדיר את העמודה dt. במקרה כזה, משתמשים ב-nameAlias בקטע type: metadata כדי שמנוע התרגום יסיר את התבנית כשהוא מבצע חיפוש מהיר של שמות אובייקטים במטא-נתונים. המזהה המקורי נשמר בפלט ה-SQL.
השדה nameAlias תומך במאפיינים הבאים. כל מאפיין מקבל מחרוזת אחת או רשימה של מחרוזות, והרשומה התואמת הראשונה בכל רשימה חלה:
-
stripPrefix: הסרת קידומת מילולית לפני חיפוש מטא-נתונים. -
stripSuffix: הסרת סיומת מילולית לפני חיפוש מטא-נתונים. -
stripRegex: התאמה לביטוי רגולרי תלוי אותיות רישיות (case-sensitive) והחלפה של כל מחרוזת משנה תואמת בקבוצה הראשונה לחילוץ(...)בזמן חיפוש מטא-נתונים. עוגן לתבנית – לדוגמה,^a_(.*)$– כדי להתאים רק לקידומת או למזהה המלא.
קובץ ה-YAML הבא מסיר את הקידומת a_ ממזהים כשמחפשים שמות של אובייקטים במטא-נתונים:
type: metadata
nameAlias:
stripRegex: "^a_(.*)$"
תרגום SQL עם קובץ התצורה הזה ב-YAML עשוי להיראות כך:
teradata-input.sql |
create table t(dt date); select a_dt + 1 from t; |
bq-output.sql |
CREATE TABLE t ( dt DATE ) ; SELECT date_add(a_dt, interval 1 DAY) FROM t ; |
הגדרת רמת הדיוק של הערכים העשרוניים
כברירת מחדל, שירות BigQuery Migration Service מגדיל את הדיוק המספרי לדיוק הגבוה ביותר שזמין לקנה מידה נתון. קובץ ה-YAML הבא משנה את ההתנהגות הזו על ידי הגדרת רמת הדיוק כך שתשמור על הדיוק העשרוני של הצהרת המקור.
type: statement_rewriter
common:
decimalPrecision: STRICT
תרגום SQL עם קובץ התצורה הזה ב-YAML עשוי להיראות כך:
teradata-input.sql |
create table x(a decimal(3,0)); |
bq-output.sql |
CREATE TABLE x ( a NUMERIC(3) ) ; |
הגדרת רמת הדיוק של המחרוזת
כברירת מחדל, שירות ההעברה ל-BigQuery משמיט את הדיוק של מחרוזות כשהוא מתרגם עמודות מסוג CHAR ו-VARCHAR. כך אפשר למנוע שגיאות חיתוך כשכותבים ערכים. בניבי SQL מסוימים, כמו Teradata, ערכים שחורגים מהדיוק המקסימלי נחתכים בזמן הכתיבה, ואילו ב-BigQuery מוחזרת שגיאה בתרחיש הזה.
אם האפליקציה לא מסתמכת על התנהגות החיתוך של ניב המקור, כדאי לשמור על הדיוק של העמודה בהגדרה של הסוג המתורגם.
קובץ ה-YAML הבא משנה את ההתנהגות הזו על ידי הגדרת רמת הדיוק, כדי לשמור על הדיוק של המחרוזת בהצהרת המקור.
type: statement_rewriter
common:
stringPrecision: STRICT
תרגום SQL עם קובץ התצורה הזה ב-YAML עשוי להיראות כך:
teradata-input.sql |
create table x(a varchar(3)); |
bq-output.sql |
CREATE TABLE x ( a STRING(3) ) ; |
מיפוי שמות הפלט
אתם יכולים להשתמש ב-type: experimental_object_rewriter בקובץ YAML של הגדרות אישיות כדי למפות שמות של אובייקטים ב-SQL (database, schema, relation, attribute, function, sequence, index, variable או projection) באופן סטטי או דינמי:
- כל סוג אובייקט מרחיב רק את השם שלו ואת ה-placeholder של היררכיית ההורה מ-
${database},${schema},${relation},${attribute},${function},${sequence},${index},${variable}ו-${projection}. לדוגמה, כללschemaמרחיב את${database}ו-${schema}, וכללrelationמרחיב את${database},${schema}ו-${relation}. - אם כמה כללים מאותו סוג אובייקט עם קריטריונים שונים של
matchתואמים לאובייקט, הכלל התואם האחרון ברשימה חל. - כללים גלובליים של
replaceו-caseב-global.outputNameפועלים לפני כללים שלoutputNameשספציפיים לאובייקט. כלל ספציפי לאובייקט תואם לשם האובייקט שעבר טרנספורמציה גלובלית, ויכול לשנות אותו. בכללים שלattribute, המערכת שלmatchמשווה בין השם שלattributeשעבר טרנספורמציה גלובלית לבין השמות שלdatabase,schemaו-relationבמקור. למידע נוסף, קראו את המאמר שכתוב גלובלי של שם הפלט.
למידע נוסף על האופן שבו כללי מיפוי השמות פועלים ברמות של מסד הנתונים, הסכימה, הקשר, הפונקציה והמאפיין, ראו מיפוי שמות של אובייקטים ב-SQL לתרגום.
מיפוי שמות סטטי
כדי למפות את השם של ישות, משתמשים במיפוי שמות סטטי. אם אתם רוצים לשנות רק חלקים מסוימים בשם ולהשאיר חלקים אחרים כמו שהם, אתם צריכים לכלול רק את החלקים שצריך לשנות.
קובץ ה-YAML הבא משנה את שם הטבלה מ-my_db.my_schema.my_table ל-my_new_db.my_schema.my_new_table.
type: experimental_object_rewriter
relation:
-
match: "my_db.my_schema.my_table"
outputName:
database: "my_new_db"
relation: "my_new_table"
תרגום SQL עם קובץ התצורה הזה ב-YAML עשוי להיראות כך:
teradata-input.sql |
create table my_db.my_schema.my_table(a int); |
bq-output.sql |
CREATE TABLE my_new_db.my_schema.my_new_table ( a INT64 ) ; |
אתם יכולים להשתמש במיפוי שמות סטטי כדי לעדכן את האזור שבו משתמשים בשמות בפונקציות בהגדרת המשתמש הציבוריות.
בדוגמה הבאה, השמות ב-UDF bqutil.fn משתנים משימוש בברירת המחדל במספר אזורים us לשימוש באזור europe_west2:
type: experimental_object_rewriter
function:
-
match:
database: bqutil
schema: fn
outputName:
database: bqutil
schema: fn_europe_west2
מיפוי שמות דינמי
אתם יכולים להשתמש במיפוי שמות דינמי כדי לשנות כמה אובייקטים בו-זמנית, וליצור שמות חדשים על סמך האובייקטים הממופים.
קובץ ה-YAML הבא משנה את השם של כל הטבלאות על ידי הוספת הקידומת stg_ לאלה ששייכות לסכימה staging, ואז מעביר את הטבלאות האלה לסכימה production.
type: experimental_object_rewriter
relation:
-
match:
schema: staging
outputName:
schema: production
relation: "stg_${relation}"
תרגום SQL עם קובץ התצורה הזה ב-YAML עשוי להיראות כך:
teradata-input.sql |
create table staging.my_table(a int); |
bq-output.sql |
CREATE TABLE production.stg_my_table ( a INT64 ) ; |
מיפוי שמות של עמודות
כדי לשנות את השמות של העמודות גם בהגדרות של הטבלה וגם בהפניות לשאילתות, משתמשים בקטע attribute שב-type: experimental_object_rewriter:
type: experimental_object_rewriter
attribute:
-
match:
relation: employee
attributeRegex: "\\d.*"
outputName:
attribute: "col_${attribute}"
תרגום SQL עם קובץ התצורה הזה ב-YAML עשוי להיראות כך:
teradata-input.sql |
create table employee(eid int, "2name" varchar(10)); select eid, "2name" from employee e; |
bq-output.sql |
CREATE TABLE employee ( eid INT64, col_2name STRING ) ; SELECT eid, col_2name FROM employee AS e ; |
ציון מסד נתונים ונתיב חיפוש סכימה שמוגדרים כברירת מחדל
בקובץ ה-YAML הבא מוגדרים מסד נתונים שמוגדר כברירת מחדל ונתיב חיפוש סכימה.
type: environment
session:
defaultDatabase: myproject
schemaSearchPath: [myschema1, myschema2]
תרגום SQL עם קובץ התצורה הזה ב-YAML עשוי להיראות כך:
teradata-input.sql |
SELECT * FROM mydb.mytable; SELECT * FROM table1; |
bq-output.sql |
SELECT * FROM myproject.mydb.mytable; SELECT * FROM myproject.myschema1.table1; |
ההגדרה NLS_DATE_FORMAT
בקובץ ה-YAML הבא, הפרמטר NLS_DATE_FORMAT מוגדר לפורמט DD/MM/YYYY. מומלץ לציין את NLS_DATE_FORMAT לשימושים משתמעים בפורמט תאריך ובהמרות. אם לא מגדירים את המדיניות, המערכת משתמשת בפורמט ברירת המחדל לתרגום, DD-MON-RR.
type: environment
session:
dateFormat: DD/MM/YYYY
תרגום SQL עם קובץ התצורה הזה ב-YAML עשוי להיראות כך:
oracle-input.sql |
create table x(dt date default '31/12/1999'); insert into x values ('01/01/2000'); |
bq-output.sql |
CREATE TABLE X ( DT DATETIME DEFAULT DATETIME '1999-12-31 00:00:00' ) ; INSERT INTO X VALUES (DATETIME '2000-01-01 00:00:00') ; |
שכתוב גלובלי של שם הפלט
בקובץ ה-YAML הבא של ההגדרות נעשה שימוש בכללים של replace בקטע global.outputName כדי לשכתב את שמות הפלט של כל האובייקטים (all) או של סוגי אובייקטים ספציפיים (database, schema, relation, attribute, function, sequence, index, variable או projection). כל כלל תואם לביטוי רגולרי תלוי אותיות רישיות ומחליף את ההתאמות בטקסט חדש, כולל $0, $1 וקבוצות לחילוץ אחרות.
טרנספורמציות גלובליות של outputName (replace ואחריו case) פועלות לפני כללים של outputName שספציפיים לאובייקטים, כמו schema, relation או attribute. כלל ספציפי לאובייקט תואם לשם של האובייקט שעבר טרנספורמציה גלובלית, ויכול לשנות אותו. בכללי attribute, המערכת של match משווה את השם של attribute שעבר טרנספורמציה גלובלית לשמות של database, schema ו-relation במקור:
type: experimental_object_rewriter
global:
outputName:
all:
replace:
- regex: '\s'
replaceWith: '_'
- regex: '>='
replaceWith: 'gte'
- regex: '^[^a-zA-Z_].*'
replaceWith: '_$0'
תרגום SQL עם קובץ התצורה הזה ב-YAML עשוי להיראות כך:
teradata-input.sql |
create table "test special chars >= 12"("42eid" int, "custom column" varchar(10)); |
bq-output.sql |
CREATE TABLE test_special_chars_gte_12 ( _42eid INT64, custom_column STRING ) ; |
אופטימיזציה ושיפור הביצועים של SQL מתורגם
אפשר להחיל טרנספורמציות אופציונליות על ה-SQL המתורגם כדי להכניס שינויים שיכולים לשפר את הביצועים או העלות. האופטימיזציות האלה תלויות מאוד במקרה הספציפי, וצריך להעריך אותן בהשוואה לפלט SQL ללא שינוי כדי להעריך את ההשפעה שלהן בפועל על הביצועים.
קובץ ה-YAML הבא מאפשר טרנספורמציות אופציונליות. כל רשומה ברשימה transformations תומכת בשדות הבאים:
-
name: שם האופטימיזציה. -
enabled(אופציונלי): ערך בוליאני שמוגדר כברירת מחדל ל-true. מגדירים אתenabledלערךfalseכדי להשבית אופטימיזציה שמופעלת כברירת מחדל לניב המקור. -
parameters(אופציונלי): מפה של פרמטרים ספציפיים לאופטימיזציה.
type: optimizer
transformations:
- name: PRECOMPUTE_INDEPENDENT_SUBSELECTS
- name: REWRITE_CTE_TO_TEMP_TABLE
parameters:
threshold: 1
| אופטימיזציה | פרמטר אופציונלי | תיאור |
|---|---|---|
PRECOMPUTE_INDEPENDENT_SUBSELECTS |
scope: [PREDICATE, PROJECTION]
|
כתיבה מחדש של השאילתה על ידי הוספת הצהרת DECLARE כדי להחליף ביטוי בסעיפי PREDICATE, בסעיפי PROJECTION או בשניהם במשתנה שחושב מראש. השכתוב הזה הופך את הביטוי לפרדיקט סטטי שיכול להפחית את כמות הנתונים שנקראים. הפרמטר scope מקבל רשימה שמכילה את PREDICATE, את PROJECTION או את שניהם. אם משמיטים את parameters, ברירת המחדל של scope היא [PREDICATE], שכוללת את הסעיפים WHERE ו-JOIN ON.
חילוץ של שאילתת משנה סקלרית להצהרת DECLARE
יהפוך את הפרדיקט המקורי לסטטי, ולכן הוא יתאים לשיפור
תכנון הביצוע. האופטימיזציה הזו תציג הצהרות SQL חדשות.
|
REWRITE_CTE_TO_TEMP_TABLE |
threshold: N
|
משכתב ביטויי טבלה נפוצים (CTE) לטבלאות זמניות כשיש לפחות N הפניות לאותו CTE. הכתיבה מחדש הזו מפחיתה את מורכבות השאילתה ומאלצת ביצוע יחיד של ה-CTE. אם משמיטים את N, ערך ברירת המחדל הוא 4.
מומלץ להשתמש באופטימיזציה הזו כשמפנים כמה פעמים לביטויי CTE לא טריוויאליים. הוספת טבלאות זמניות כרוכה בתקורה שעשויה להיות גדולה יותר מההפעלה הסופית של כמה CTE עם מורכבות נמוכה או עוצמה נמוכה. האופטימיזציה הזו תציג הצהרות SQL חדשות. |
REWRITE_ZERO_SCALE_NUMERIC_AS_INTEGER |
bigint: N
|
הוא משכתב מאפייני NUMERIC/BIGNUMERIC בקנה מידה אפס לסוג INT64 אם הדיוק הוא בטווח של N. אם לא מציינים את N, ערך ברירת המחדל הוא 18.
מומלץ להשתמש באופטימיזציה הזו כשמתרגמים מניבים של מקורות שאין בהם סוגי מספרים שלמים. אם משנים סוגי עמודות, צריך לבדוק את כל השימושים במורד הזרם כדי לוודא שהסוגים תואמים ושהשינויים סמנטיים. לדוגמה, חלוקה של שברים שהופכת לחלוקה של מספרים שלמים, או קוד שמצפה לערכים מספריים. בתרגומים של Snowflake, האופטימיזציה הזו מופעלת כברירת מחדל למספרים עם אפס ספרות אחרי הנקודה העשרונית ועד לדיוק של 38 ספרות. האופטימיזציה הזו מבטיחה ש- INTEGER של Snowflake, שמיוצג באופן משתמע כ-NUMBER(38,0) ב-Snowflake, יתורגם ל-INT64 של BigQuery במקום ל-BIGNUMERIC(38). אם באפליקציה שלכם נעשה שימוש במספרים עם דיוק של יותר מ-18, מומלץ להשבית את הפונקציונליות הזו כדי לוודא ש-BigQuery יכול לעבד את כל טווח הערכים שנדרש לאפליקציה. |
DROP_TEMP_TABLE |
הוספת הצהרות DROP TABLE לכל הטבלאות הזמניות שנוצרו בסקריפט ולא נמחקו עד סופו. כך תקופת החיוב על האחסון של הטבלה הזמנית תצטמצם מ-24 שעות לזמן הריצה של הסקריפט.
מומלץ להשתמש באופטימיזציה הזו כשלא ניגשים לטבלאות זמניות לעיבוד נוסף אחרי סיום הפעלת הסקריפט. האופטימיזציה הזו תציג הצהרות SQL חדשות. |
|
REGEXP_CONTAINS_TO_LIKE |
שכתוב של כמה קטגוריות של תבניות תואמות של REGEXP_CONTAINS
לביטויים של LIKE.
מומלץ להשתמש באופטימיזציה הזו כשאין תהליך אחר, כמו החלפת פקודות מאקרו, שמסתמך על כך שהליטרלים של תבניות הביטויים הרגולריים יישמרו ללא שינוי בפלט ה-SQL. |
|
ADD_DISTINCT_TO_SUBQUERY_IN_SET_COMPARISON |
הוספת משפט DISTINCT לשאילתות משנה שמשמשות כקבוצת ערכים לאופרטור [NOT] IN.
מומלץ להשתמש באופטימיזציה הזו כשהעוצמה (מספר הערכים הייחודיים) של תוצאת שאילתת המשנה נמוכה משמעותית ממספר הערכים. אם התנאי המוקדם הזה לא מתקיים, הטרנספורמציה הזו יכולה להשפיע לרעה על הביצועים. |
|
APPROXIMATE_RANGE_PARTITIONS |
מבצע קירוב של סכימות חלוקה של מספרים שלמים לא רציפים או לא רגילים, על ידי המרה שלהן לטווחי חלוקה רציפים בגודל שווה שנתמכים על ידי BigQuery. כברירת מחדל, סכימות חלוקה למחיצות כאלה לא משפיעות על סכימת החלוקה למחיצות של הטבלה בהצהרות DDL מתורגמות.
מומלץ להשתמש באופטימיזציה הזו כשבטבלת המקור נעשה שימוש בפונקציית חלוקה למחיצות לא רציפה, כמו הפונקציה RANGE_N של Teradata, וכשרוצים להשתמש בסכימת חלוקה למחיצות בגודל שווה ב-BigQuery.
|
דוגמאות לאופטימיזציה
האופטימיזציה הבאה ממירה סוגים מספריים עם קנה מידה אפס ודיוק של עד 38 ל-INT64 ב-BigQuery.
# An INTEGER is internally represented as NUMBER(38,0) in Snowflake.
# To convert Snowflake INTEGER to INT64 in BigQuery, enable the rewrite for precision <= 38.
# Note that this can produce incorrect results if your application logic uses more than 18 digits of precision.
#
# This configuration is enabled by default for the Snowflake Dialect.
type: optimizer
transformations:
- name: REWRITE_ZERO_SCALE_NUMERIC_AS_INTEGER
parameters:
bigint: 38
תרגום SQL עם האופטימיזציה הזו עשוי להיראות כך:
snowflake-input.sql |
CREATE TABLE numbers(i INTEGER, n NUMERIC(10,0)); |
bq-output.sql |
CREATE TABLE NUMBERS ( I INT64, N INT64 ) ; |
ההגדרה הבאה משביתה את האופטימיזציה בניבים, כמו Snowflake, שבהם היא מופעלת כברירת מחדל. ההגדרה הזו ממירה סוגים מספריים ל-NUMERIC או ל-BIGNUMERIC בהתאם לדיוק הקלט, במקום לברירת המחדל של INT64.
type: optimizer
transformations:
- name: REWRITE_ZERO_SCALE_NUMERIC_AS_INTEGER
enabled: false
תרגום SQL עם האופטימיזציה הזו עשוי להיראות כך:
snowflake-input.sql |
CREATE TABLE numbers(i INTEGER, n NUMERIC(10,0)); |
bq-output.sql |
CREATE TABLE NUMBERS ( I BIGNUMERIC(38), N NUMERIC(29) ) ; |
יצירת קובץ YAML של הגדרות שמבוססות על Gemini
כדי ליצור פלט באמצעות AI, צריך לספק לפחות קובץ *.ai_config.yaml אחד ולהפעיל את ההצעות של Gemini AI כשהממשק דורש זאת. מידע נוסף זמין במאמר הוספת קובצי תצורה לתרגום.
דרישות
קובץ ה-YAML של הגדרות הפלט של ה-AI חייב להסתיים בסיומת .ai_config.yaml.
לדוגמה, rules_1.ai_config.yaml.
שדות נתמכים
בניגוד לקובץ *.config.yaml, קובץ *.ai_config.yaml לא כולל כותרת type. במקום זאת, הקובץ מכיל רשימת translation_rules ברמה העליונה, שבה כל כלל תומך בשדות הבאים:
-
suggestion_type(אופציונלי): מציינים את סוג ההצעה שרוצים שה-AI ייצור. אלה סוגי ההצעות שנתמכים:-
QUERY_CUSTOMIZATION(ברירת המחדל): יצירת הצעות מבוססות-AI לקוד SQL על סמך כללי התרגום שצוינו בקובץ*.ai_config.yaml. -
TRANSLATION_EXPLANATION: יוצר טקסט דינמי מבוסס-AI שכולל סיכום של שאילתת GoogleSQL המתורגמת, ואת ההבדלים וחוסר העקביות בין שאילתת ה-SQL המקורית לבין שאילתת GoogleSQL המתורגמת.
-
-
rewrite_target(אופציונלי): מציינים את הערךSOURCE_SQLכדי להחיל את הכלל על קלט ה-SQL המקורי לפני שמנוע התרגום פועל, או את הערךTARGET_SQLכדי להחיל את הכלל על ה-SQL המתורגם שמנוע התרגום יוצר. ערך ברירת המחדל הואTARGET_SQL. מידע נוסף זמין במאמר איך שני סוגי הכללים מיושמים. -
instruction(אופציונלי): תיאור בשפה טבעית של השינוי שרוצים להחיל על קלט ה-SQL או על ה-SQL המתורגם לכלליQUERY_CUSTOMIZATION, או ההסבר שרוצים ליצור לכלליTRANSLATION_EXPLANATION. -
examples(אופציונלי): רשימה של צמדי SQL מסוגinputו-outputשמראים איך אתם רוצים לשנות תבנית SQL.
אפשר להוסיף עוד translation_rules ועוד examples לפי הצורך.
דוגמאות
בדוגמאות הבאות נוצרים קובצי YAML של הגדרות שמבוססות על Gemini, שאפשר להשתמש בהם בתרגומי SQL.
הסרת הפונקציה העליונה בשאילתת הפלט של תרגום ברירת המחדל
translation_rules:
- instruction: "Remove upper() function"
examples:
- input: "upper(X)"
output: "X"
יצירת כמה כללי תרגום כדי להתאים אישית את פלט התרגום
translation_rules:
- instruction: "Remove upper() function"
suggestion_type: QUERY_CUSTOMIZATION
rewrite_target: TARGET_SQL
examples:
- input: "upper(X)"
output: "X"
- instruction: "Insert a comment at the head that explains each statement in detail."
suggestion_type: QUERY_CUSTOMIZATION
rewrite_target: TARGET_SQL
הסרת הערות SQL משאילתת הקלט לתרגום
translation_rules:
- instruction: "Remove all the sql comments in the input sql query."
suggestion_type: QUERY_CUSTOMIZATION
rewrite_target: SOURCE_SQL
יצירת הסברים לתרגום באמצעות פרומפט ברירת המחדל של ה-LLM
בדוגמה הזו, כדי ליצור הסברים בטקסט, נעשה שימוש בהנחיות ברירת המחדל של מודל ה-LLM שסופקו על ידי שירות התרגום:
translation_rules:
- suggestion_type: "TRANSLATION_EXPLANATION"
יוצר הסברים לתרגום באמצעות הנחיות בשפה טבעית
translation_rules:
- suggestion_type: "TRANSLATION_EXPLANATION"
instruction: "Explain the syntax differences between the source Teradata query and the translated GoogleSQL query."
כמה סוגי הצעות בקובץ YAML אחד של הגדרות אישיות
translation_rules:
- suggestion_type: "TRANSLATION_EXPLANATION"
instruction: "Explain the syntax differences between the source Teradata query and the translated GoogleSQL query."
- instruction: "Remove upper() function"
suggestion_type: QUERY_CUSTOMIZATION
rewrite_target: TARGET_SQL
examples:
- input: "upper(X)"
output: "X"
- instruction: "Remove all the sql comments in the input sql query."
suggestion_type: QUERY_CUSTOMIZATION
rewrite_target: SOURCE_SQL
החלה של כמה הגדרות YAML
כשמתרגמים שאילתה אחת או קובצי SQL באצווה, אפשר לספק כמה קובצי YAML של הגדרות אישיות במשימת תרגום אחת כדי לשקף כמה טרנספורמציות. אם יש התנגשות בין כמה הגדרות, יכול להיות שטרנספורמציה אחת תבטל טרנספורמציה אחרת. מומלץ להשתמש בסוגים שונים של הגדרות אישיות בכל קובץ כדי למנוע התנגשויות בין טרנספורמציות באותה משימת תרגום.
בדוגמה הבאה מפורטים שני קובצי YAML נפרדים של הגדרות אישיות שסופקו למשימת תרגום אחת של SQL, אחד לשינוי מאפיין של עמודה והשני להגדרת הטבלה כזמנית:
change-type-example.config.yaml:
type: object_rewriter
attribute:
-
match: "testdb.testschema.x.a"
type:
target: NUMERIC(10,2)
make-temp-example.config.yaml:
type: object_rewriter
relation:
-
match: "testdb.testschema.x"
temporary: true
תרגום SQL עם שני קובצי ה-YAML האלה להגדרה עשוי להיראות כך:
teradata-input.sql |
create table testdb.testschema.x(a int); |
bq-output.sql |
CREATE TEMPORARY TABLE x ( a NUMERIC(31, 2) ) ; |