טרנספורמציה של תרגומי SQL באמצעות קובצי YAML של הגדרות

במאמר הזה מוסבר איך להשתמש בקובצי YAML של הגדרות כדי לשנות קוד SQL בזמן ההעברה שלו ל-BigQuery. הוא כולל הנחיות ליצירת קובצי YAML משלכם להגדרות, ודוגמאות לשינויים שונים בתרגום שנתמכים בתכונה הזו.

כשמשתמשים בכלי האינטראקטיבי לתרגום SQL ב-BigQuery, ב-BigQuery Migration API או בתרגום SQL באצווה, אפשר לספק קובצי YAML של הגדרות כדי לשנות תרגום של שאילתת SQL. שימוש בקובצי YAML של הגדרות מאפשר התאמה אישית נוספת כשמתרגמים שאילתות SQL ממסד הנתונים של המקור.

אפשר לציין קובץ YAML של הגדרות לשימוש בתרגום SQL באחת מהדרכים הבאות:

כלי התרגום האינטראקטיבי של SQL,‏ BigQuery Migration API,‏ כלי התרגום של SQL באצווה ולקוח Python לתרגום באצווה תומכים בשימוש בכמה קובצי YAML של הגדרות בעבודת תרגום אחת. מידע נוסף מופיע במאמר בנושא החלת כמה הגדרות YAML.

הדרישות לקובץ YAML של ההגדרות

לפני שיוצרים קובץ YAML להגדרות, כדאי לעיין במידע הבא כדי לוודא שקובץ ה-YAML תואם לשימוש בשירות ההעברה של BigQuery:

  • צריך להעלות את קובצי ה-YAML של ההגדרות לספרייה של קטגוריית Cloud Storage שמכילה את קובצי הקלט של תרגום ה-SQL. מידע על יצירת קטגוריות והעלאת קבצים ל-Cloud Storage זמין במאמרים בנושא יצירת קטגוריות והעלאת אובייקטים ממערכת קבצים.
  • גודל הקובץ של קובץ YAML יחיד של הגדרה לא יכול לחרוג מ-1MB.
  • הגודל הכולל של כל קובצי ה-YAML של ההגדרות שמשמשים במשימת תרגום SQL אחת לא יכול להיות יותר מ-4MB.
  • אם אתם משתמשים בתחביר regex להתאמת שמות, השתמשו ב-RE2/J.
  • כל שמות הקבצים של הגדרות YAML חייבים לכלול את הסיומת .config.yaml, למשל change-case.config.yaml.
    • השם config.yaml לבדו לא תקין לקובץ תצורה.

הנחיות ליצירת קובץ YAML של הגדרות

בקטע הזה מפורטות הנחיות כלליות ליצירת קובץ הגדרות YAML:

כל קובץ תצורה חייב להכיל כותרת שמציינת את סוג התצורה. הסוג object_rewriter משמש לציון תרגומים של SQL בקובץ תצורת YAML. בדוגמה הבאה משתמשים בסוג object_rewriter כדי לשנות את האותיות בשם:

type: object_rewriter
global:
  case:
    all: UPPERCASE

בחירת ישויות

כדי לבצע טרנספורמציות ספציפיות לישות, מציינים את הישות בקובץ התצורה. כל המאפיינים של match הם אופציונליים. משתמשים רק במאפיינים של match שנדרשים לטרנספורמציה. קובץ ה-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.
  • ‫schema: רכיב מערך הנתונים.
  • ‫relation: רכיב הטבלה.
  • ‫attribute: רכיב העמודה. האפשרות הזו תקפה רק לבחירת מאפיינים
  • ‫databaseRegex או dbRegex: התאמה של מאפיין database באמצעות ביטוי רגולרי (תצוגה מקדימה).
  • ‫schemaRegex: התאמה של מאפייני schema לביטויים רגולריים (תצוגה מקדימה).
  • ‫relationRegex: התאמה של מאפייני relation באמצעות ביטויים רגולריים (תצוגה מקדימה).
  • ‫attributeRegex: התאמה של מאפייני 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.

מסד נתונים שמוגדר כברירת מחדל

חלק מהניבים של SQL, במיוחד Teradata, לא תומכים ב-database-name בשם המלא. במקרה כזה, הדרך הכי פשוטה להתאים ישויות היא להשמיט את המאפיין database ב-match.

עם זאת, אפשר להגדיר את המאפיין default_database של שירות ההעברה ל-BigQuery ולהשתמש במסד הנתונים שמוגדר כברירת מחדל ב-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.

בדוגמאות הבאות נעשה שימוש ב-Teradata או ב-Hive כקלט של דיאלקט SQL וב-BigQuery SQL כפלט של דיאלקט. בדוגמאות הבאות נעשה שימוש גם ב-testdb כמסד הנתונים שמוגדר כברירת מחדל, וב-testschema כנתיב החיפוש של הסכימה.

שינוי האותיות בשם האובייקט

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

type: object_rewriter
global:
  case:
    all: UPPERCASE
    database: LOWERCASE
    attribute: LOWERCASE

תרגום SQL עם קובץ ה-YAML הזה של ההגדרות יכול להיראות כך:

teradata-input.sql 
      create table x(a int);
      select * from x;
bq-output.sql 
      CREATE TABLE testdb.TESTSCHEMA.X
      (
        a INT64
      )
      ;
      SELECT
          X.a
        FROM
          testdb.TESTSCHEMA.X
      ;

הגדרת הטבלה כזמנית

השינויים הבאים בקובץ ה-YAML של ההגדרות משנים טבלה רגילה לטבלה זמנית:

type: object_rewriter
relation:
  -
    match: "testdb.testschema.x"
    temporary: true

תרגום SQL עם קובץ ה-YAML הזה של ההגדרות יכול להיראות כך:

teradata-input.sql 
    create table 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 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 x(a int, b int) partition by (a);
bq-output.sql 
    CREATE TABLE testdb.testschema.x
    (
      a INT64,
      b INT64
    )
    CLUSTER 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 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 x(a int);
bq-output.sql 
    CREATE TABLE testdb.testschema.x
    (
      a INT64
    )
    OPTIONS(
      description='Example description.'
    );

הגדרה או שינוי של חלוקת הטבלה למחיצות

השינויים הבאים בהגדרות ה-YAML משנים את סכימת החלוקה למחיצות (partitioning) של טבלה:

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 x(a date, b int);
    create table 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.x"
    clustering:
      add: [a]
  -
    match: "testdb.testschema.y"
    clustering:
      remove: [b]

תרגום SQL עם קובץ ה-YAML הזה של ההגדרות יכול להיראות כך:

hive-input.sql 
    create table x(a int, b int);
    create table 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 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)
    )
    ;

הוספת חיבור לאגם נתונים חיצוני

קובץ ה-YAML של ההגדרה הבאה מציין שהטבלה של המקור היא טבלה חיצונית שמפנה לנתונים שמאוחסנים באגם נתונים חיצוני, שמוגדר על ידי חיבור לאגם נתונים.

type: object_rewriter
relation:
-
  key: "testdb.acme.employee"
  external:
    connection_id: "connection_test"

תרגום SQL עם קובץ ה-YAML הזה של ההגדרות יכול להיראות כך:

hive-input.sql 
    CREATE TABLE x
    (
      a VARCHAR(150),
      b INT
    );
bq-output.sql 
    CREATE EXTERNAL TABLE x
    (
      a STRING,
      b INT64
    )
    WITH CONNECTION `connection_test`
    OPTIONS(
    );

שינוי קידוד התווים של קובץ קלט

כברירת מחדל, שירות ההעברה ל-BigQuery מנסה לזהות באופן אוטומטי את קידוד התווים של קובצי הקלט. במקרים שבהם יכול להיות ששירות ההעברה של BigQuery יזהה באופן שגוי את הקידוד של קובץ, אפשר להשתמש בקובץ YAML של הגדרות כדי לציין במפורש את קידוד התווים.

קובץ ה-YAML הבא של ההגדרות מציין את קידוד התווים המפורש של קובץ הקלט כ-ISO-8859-1.

type: experimental_input_formats
formats:
- source:
    pathGlob: "*.sql"
  contents:
    raw:
      charset: iso-8859-1

המרת סוג גלובלית

ההגדרה הבאה ב-YAML משנה סוג נתונים אחד לסוג נתונים אחר בכל הסקריפטים, ומציינת סוג נתונים של מקור שצריך להימנע ממנו בסקריפט שעבר המרה. זה שונה מההגדרה של שינוי הסוג של מאפיין עמודה, שבה משנים רק את סוג הנתונים של מאפיין יחיד.

‫BigQuery תומך בהמרות של סוגי הנתונים הבאים:

  • DATETIME עד TIMESTAMP
  • ‫TIMESTAMP עד DATETIME (אפשר לציין אזור זמן)
  • ‫TIMESTAMP WITH TIME ZONE עד DATETIME (אפשר לציין אזור זמן)
  • CHAR עד VARCHAR

בדוגמה הבאה, קובץ ה-YAML של ההגדרות ממיר את סוג הנתונים TIMESTAMP לסוג הנתונים DATETIME.

type: experimental_object_rewriter
global:
  typeConvert:
    timestamp: DATETIME

בניבים כמו Teradata, פונקציות שקשורות לתאריך ולשעה, כמו current_date,‏ current_time או current_timestamp, מחזירות חותמות זמן על סמך אזור הזמן שהוגדר, שהוא אזור הזמן המקומי או אזור הזמן של הסשן. לעומת זאת, BigQuery תמיד מחזיר חותמות זמן ב-UTC. כדי להבטיח התנהגות עקבית בין שני הניבים, צריך להגדיר את אזור הזמן בהתאם.

בדוגמה הבאה, קובץ ה-YAML של ההגדרה ממיר את סוג הנתונים TIMESTAMP ו-TIMESTAMP WITH TIME ZONE ל-DATETIME, ואזור הזמן של היעד מוגדר כ-Europe/Paris.

type: experimental_object_rewriter
global:
  typeConvert:
    timestamp:
      target: DATETIME
      timezone: Europe/Paris
    timestamptz:
      target: DATETIME
      timezone: Europe/Paris

תרגום SQL עם קובץ ה-YAML הזה של ההגדרות יכול להיראות כך:

teradata-input.sql 
      create table x(a timestamp);
      select a from x where a > current_timestamp(0);
bq-output.sql 
      CREATE TABLE x
      (
        a TIMESTAMP
      )
      ;
      SELECT
          x.a
        FROM
          test.x
        WHERE x.a > datetime_trunc(current_datetime('Europe/Paris'), SECOND)
      ;

בחירת שינוי בדוח התנועות בחשבון

השינויים הבאים ב-YAML של ההגדרה משנים את ההטלה של הכוכב, GROUP BY, ואת סעיפי ORDER BY בהצהרות SELECT.

‫starProjection תומך בהגדרות הבאות:

  • ALLOW
  • PRESERVE (ברירת מחדל)
  • EXPAND

‫groupBy ו-orderBy תומכים בהגדרות הבאות:

  • EXPRESSION
  • ALIAS
  • INDEX

בדוגמה הבאה, קובץ ה-YAML של ההגדרות קובע שהקרנת הכוכבים EXPAND.

type: experimental_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 DATETIME
      )
      ;
      SELECT
          x.a
          x.b
        FROM
          x
      ;

מפרט UDF

קובץ ה-YAML הבא של ההגדרה מציין את החתימה של פונקציות שהוגדרו על ידי המשתמש (UDF) שנעשה בהן שימוש בסקריפטים של המקור. בדומה לקובצי 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(x.dt), interval 1 DAY)
        FROM
          x
      ;

הגדרת רמת הדיוק של מספרים עשרוניים

כברירת מחדל, שירות ההעברה ל-BigQuery מגדיל את הדיוק המספרי לרמה הגבוהה ביותר של דיוק שזמינה עבור קנה מידה נתון. הגדרת ה-YAML הבאה מבטלת את ההתנהגות הזו על ידי הגדרת רמת הדיוק כך שיישמר הדיוק העשרוני של הצהרת המקור.

type: experimental_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 Migration Service משמיט את הדיוק של מחרוזות כשמתרגמים עמודות CHAR ו-VARCHAR. כך אפשר למנוע שגיאות חיתוך כשכותבים ערכים. ניבים מסוימים של SQL, כמו Teradata, חותכים ערכים שגדולים מהדיוק המקסימלי בכתיבה, בעוד ש-BigQuery מחזיר שגיאה בתרחיש הזה.

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

הגדרת ה-YAML הבאה מבטלת את ההתנהגות הזו על ידי הגדרת רמת הדיוק כך שתשמר רמת הדיוק של המחרוזת בהצהרת המקור.

type: experimental_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)
      )
      ;

מיפוי של שם הפלט

אפשר להשתמש ב-YAML של ההגדרות כדי למפות שמות של אובייקטים ב-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
      )
      ;

ציון נתיב חיפוש של מסד נתונים וסכימה שמוגדרים כברירת מחדל

קובץ ה-YAML הבא להגדרות מציין מסד נתונים שמוגדר כברירת מחדל ונתיב חיפוש של סכימה.

type: environment
session:
  defaultDatabase: myproject
  schemaSearchPath: [myschema1, myschema2]

תרגום SQL עם קובץ ה-YAML הזה של ההגדרות יכול להיראות כך:

teradata-input.sql 
      SELECT * FROM database.table
      SELECT * FROM table1
bq-output.sql 
      SELECT * FROM myproject.database.table.
      SELECT * FROM myproject.myschema1.table1

שכתוב גלובלי של שם הפלט

ההגדרה הבאה ב-YAML משנה את שמות הפלט של כל האובייקטים (מסד נתונים, סכימה, קשר ומאפיינים) בסקריפט בהתאם לכללים שהוגדרו.

type: experimental_object_rewriter
global:
  outputName:
    regex:
      - match: '\s'
        replaceWith: '_'
      - match: '>='
        replaceWith: 'gte'
      - match: '^[^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_employees_gte_12
      (
        _42eid INT64,
        custom_column STRING
      )
      ;

אופטימיזציה ושיפור של הביצועים של SQL מתורגם

אפשר להחיל טרנספורמציות אופציונליות על SQL מתורגם כדי להציג שינויים שיכולים לשפר את הביצועים או העלות. האופטימיזציות האלה תלויות באופן מובהק באותיות רישיות וקטנות, וכדאי להשוות אותן לפלט SQL שלא עבר שינוי כדי להעריך את ההשפעה שלהן על הביצועים.

קובץ ה-YAML הבא מאפשר לבצע טרנספורמציות אופציונליות. ההגדרה מקבלת רשימה של אופטימיזציות, ובאופטימיזציות שמקבלות פרמטרים, מקטע עם ערכי פרמטרים אופציונליים.

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 במשתנה שחושב מראש. הוא יזוהה כפרדיקט סטטי, וכך יאפשר צמצום של כמות הנתונים שנקראים. אם לא מציינים את ההיקף, ערך ברירת המחדל הוא PREDICATE (כלומר, סעיף WHERE וסעיף JOIN-ON).

חילוץ של שאילתת משנה סקלרית להצהרת DECLARE יהפוך את פרדיקט המקור לסטטי, ולכן הוא יעמוד בדרישות לשיפור תכנון הביצוע. האופטימיזציה הזו תוסיף הצהרות SQL חדשות.
REWRITE_CTE_TO_TEMP_TABLE threshold: N מבצעת שכתוב של ביטויי טבלה נפוצים (CTE) לטבלאות זמניות אם יש יותר מ-N הפניות לאותו ביטוי טבלה נפוץ. כך מצטמצמת המורכבות של השאילתה והביצוע של ביטוי הטבלה הנפוץ מתבצע רק פעם אחת. אם לא מציינים את N, ערך ברירת המחדל הוא 4.

מומלץ להשתמש באופטימיזציה הזו כשמתבצעת הפניה לכמה CTEs לא טריוויאליים. השימוש בטבלאות זמניות כרוך בתקורה שעשויה להיות גדולה יותר מביצועים חוזרים של CTE עם מורכבות נמוכה או קרדינליות נמוכה. האופטימיזציה הזו תציג הצהרות SQL חדשות.
REWRITE_ZERO_SCALE_NUMERIC_AS_INTEGER bigint: N מבצעת שכתוב של מאפיינים מסוג NUMERIC/BIGNUMERIC עם סולם של אפס, לערך מסוג INT64 אם רמת הדיוק היא N. אם לא מציינים את N, ערך ברירת המחדל הוא 18.

מומלץ להשתמש באופטימיזציה הזו כשמתרגמים מניבים של מקורות שלא כוללים סוגי מספרים שלמים. כדי לשנות את סוגי העמודות, צריך לבדוק את כל השימושים בהמשך הדרך כדי לוודא שהסוגים תואמים ושהמשמעות לא משתנה. לדוגמה, חלוקות שבריות הופכות לחלוקות של מספרים שלמים, קוד שמצפה לערכים מספריים
DROP_TEMP_TABLE מוסיף הצהרות DROP TABLE לכל הטבלאות הזמניות שנוצרו בתסריט ולא נמחקו בסופו. כך תקופת החיוב על אחסון הטבלה הזמנית תקוצר מ-24 שעות לזמן הפעלת הסקריפט. האופטימיזציה הזו תוסיף הצהרות SQL חדשות.

מומלץ להשתמש באופטימיזציה הזו אם לא ניגשים לטבלאות זמניות לעיבוד נוסף כלשהו אחרי סיום ההרצה של הסקריפט. האופטימיזציה הזו תציג הצהרות 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.

יצירת קובץ YAML של תצורה שמבוססת על Gemini

כדי ליצור פלט מ-AI, ספריית קובצי המקור שמכילה את קלט התרגום של SQL צריכה לכלול קובץ תצורה מסוג YAML.

דרישות

קובץ ה-YAML של ההגדרות לפלט של AI חייב להסתיים בסיומת .ai_config.yaml. לדוגמה, rules_1.ai_config.yaml.

שדות נתמכים

אפשר להשתמש בשדות הבאים כדי להגדיר את הפלט של תרגום ה-AI:

  • ‫suggestion_type (אופציונלי): מציינים את סוג ההצעה שרוצים ליצור באמצעות AI. יש תמיכה בסוגי ההצעות הבאים:
    • ‫QUERY_CUSTOMIZATION (ברירת מחדל): יוצר הצעות מבוססות-AI לקוד SQL על סמך כללי התרגום שצוינו בקובץ ההגדרות ב-YAML.
    • ‫TRANSLATION_EXPLANATION: יוצר טקסט שכולל סיכום של שאילתת GoogleSQL המתורגמת, ושל ההבדלים וחוסר העקביות בין שאילתת ה-SQL המקורית לבין שאילתת GoogleSQL המתורגמת.
  • ‫rewrite_target (אופציונלי): מציינים SOURCE_SQL אם רוצים להחיל את כלל התרגום על קלט ה-SQL, או TARGET_SQL (ברירת מחדל) אם רוצים להחיל את כלל התרגום על פלט ה-SQL.
  • ‫instruction (אופציונלי): מתארים בשפה טבעית שינוי ב-SQL של היעד. התרגום ל-SQL עם Gemini מעריך את הבקשה ומבצע את השינוי שצוין.
  • ‫examples (אופציונלי): אפשר לספק דוגמאות ל-SQL כדי להסביר איך רוצים לשנות את תבנית ה-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

כשמציינים קובץ 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 x(a int);
bq-output.sql 
    CREATE TEMPORARY TABLE x
    (
      a NUMERIC(31, 2)
    )
    ;