שינוי תרגומי SQL באמצעות YAML

קובצי YAML של הגדרות אישיות מאפשרים לכם להתאים אישית את האופן שבו BigQuery מתרגם קוד SQL ממסד הנתונים של המקור במהלך ההעברה. שירות ההעברה ל-BigQuery תומך בשני סוגים של קובצי תצורה מסוג YAML: קובצי תצורה לתרגום של *.config.yaml, שנותנים למנוע התרגום כללים דטרמיניסטיים, וקובצי תצורה מבוססי-Gemini של *.ai_config.yaml, שנותנים ל-Gemini כללים בשפה טבעית.

כדי להתאים אישית תרגום SQL באמצעות קובצי YAML של הגדרות אישיות:

  1. יצירת קובצי תצורה: יוצרים קובצי תצורה דטרמיניסטיים לתרגום של *.config.yaml, קובצי תצורה מבוססי-Gemini של *.ai_config.yaml או שני סוגי קובצי התצורה כדי לשלב כללים מבניים עם התאמה אישית של Gemini.
  2. לספק את הקבצים למשימת תרגום: שולחים את קובצי התצורה כשמתרגמים שאילתה אחת או קובצי SQL באצווה.
  3. בדיקת פלט התרגום: מאתרים את הצעות ה-SQL וה-Gemini המתורגמות בתשובה כשמתרגמים שאילתה אחת, או ב-Cloud Storage כשמתרגמים קובצי SQL באצווה.

סוגים של קובצי YAML להגדרות אישיות

בטבלה הבאה מוצגת השוואה בין שני הסוגים של קובצי YAML להגדרות אישיות:

תכונה הגדרות התרגום (*.config.yaml) הגדרה מבוססת-Gemini (*.ai_config.yaml)
איך הכללים חלים מנוע התרגום מחיל את הכללים ככללים דטרמיניסטיים על ה-SQL המנותח, כך שאותו קלט ואותה הגדרה תמיד מפיקים את אותו פלט. ‫Gemini מיישם את הכללים כהוראות בשפה טבעית וכדוגמאות שמוחלות על טקסט ה-SQL. הפלט יכול להשתנות בין הפעלות, לכן תמיד צריך לבדוק ולאמת את ההצעות לפני שמשתמשים בהן.
מה אפשר לשנות מנוע התרגום יכול להמיר כל אובייקט או הגדרה שהוא מזהה. זה כולל שמות וסוגים של מסדי נתונים, סכימות, טבלאות, עמודות ופונקציות, מאפייני טבלאות כמו חלוקה למחיצות, אשכולות ותפוגה, ברירות מחדל של סשנים, חתימות של פונקציות בהגדרת המשתמש (UDF), אופטימיזציות ופלט של הצהרות כמו סגנון GROUP BY ו-SELECT *. ‫Gemini יכול לשנות כל טרנספורמציית SQL שאתם יכולים לתאר בשפה טבעית. זה כולל עיצוב והערות, החלפת תבניות, כתיבה מחדש של מבנים שהמנוע לא מתרגם כמו שרוצים, ויצירת הסבר על התרגום.
דרישות
  • צריך לנתח את קלט ה-SQL בניב המקור.
  • הכללים לא יכולים לפעול על טקסט בקלט ה-SQL שהמנתח לא מזהה, כמו פלייסהולדרים של ${VAR} או תחביר של תבניות.
  • כדי להסיר טקסט כזה, צריך להשתמש בכלל SOURCE_SQL בקובצי *.ai_config.yaml או בעיבוד מקדים משלכם.
  • צריך להפעיל את ההצעות של Gemini AI למשרה.
  • המשימה צריכה לפעול במיקום עיבוד נתמך.
  • השימוש ב-Gemini הוא בחינם עד למגבלה מסוימת.
איפה התוצאה מופיעה התוצאה מופיעה ב-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 שבדף הזה.

איך שני סוגי הכללים מוחלים

משימת תרגום מחילה את כללי ההגדרה בסדר הבא:

  1. ‫Gemini מחיל SOURCE_SQL כללים ב-*.ai_config.yaml קבצים על קלט ה-SQL המקורי כדי ליצור קוד מקור משוכתב ב-SQL.
  2. מנוע התרגום מתרגם את ה-SQL של המקור שנכתב מחדש בשלב הקודם, או את ה-SQL של הקלט המקורי אם לא חלים כללי SOURCE_SQL, ומחיל את הכללים בקובצי *.config.yaml כדי ליצור את ה-SQL המתורגם.
  3. ‫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.

כדי לספק קובצי תצורה למשימת תרגום, בוחרים באחת מהאפשרויות הבאות:

המסוף

  • כדי להשתמש בקובצי תצורה כשמתרגמים שאילתה אחת, מציינים אותם בהגדרות התרגום של המתרגם האינטראקטיבי:
  • כדי להשתמש בקובצי תצורה בזמן תרגום של קובצי 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 של ההגדרות תומך בסוגי היעדים הבאים:

  • BOOLEAN
  • TINYINT
  • SMALLINT
  • INTEGER
  • BIGINT
  • FLOAT
  • DOUBLE
  • NUMERIC (תומך בדיוק ובקנה מידה אופציונליים, כמו NUMERIC(18, 2))
  • TIME
  • TIMETZ
  • DATE
  • DATETIME
  • TIMESTAMP
  • TIMESTAMPTZ
  • ‫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)
    )
    ;