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

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

הגדרת אזור הזמן שמוגדר כברירת מחדל

לניבים שונים של מסדי נתונים יש סמנטיקה ושמות שונים לסוגים שונים של נתונים שקשורים לתאריך ולשעה. שירות התרגום משתמש בטרמינולוגיה הבאה בהגדרות ה-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
          x.c_timestamp
        FROM
          test.x
        WHERE x.c_timestamp > datetime(current_timestamp(), 'Europe/Paris')
      ;
      SELECT
          x.c_timestamptz
        FROM
          test.x
        WHERE x.c_timestamptz > datetime(current_timestamp(), 'Europe/Paris')
      ;
      SELECT
          x.c_datetime
        FROM
          test.x
        WHERE x.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
          x.c_timestamp
        FROM
          test.x
        WHERE x.c_timestamp > current_timestamp()
      ;
      SELECT
          x.c_timestamptz
        FROM
          test.x
        WHERE x.c_timestamptz > current_timestamp()
      ;
      SELECT
          x.c_datetime
        FROM
          test.x
        WHERE x.c_datetime > current_timestamp()
      ;

בחירת שינוי בדוח

השינויים הבאים ב-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
      )

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

בדוגמה הבאה, הפונקציה bqutil.fn UDF משתנה משימוש באזור ברירת המחדל 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

הגדרה 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 testdb.testschema.x
      (
        DT DATETIME DEFAULT DATETIME '1999-12-31 00:00:00'
      )
      ;
      INSERT INTO testdb.testschema.x (DT)
        VALUES (DATETIME '2000-01-01 00:00:00')
      ;

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

ההגדרה הבאה ב-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.

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

האופטימיזציה הזו מופעלת כברירת מחדל בתרגומים של Snowflake למספרים עם אפס ספרות אחרי הנקודה, עד לדיוק של 38. האופטימיזציה הזו מבטיחה ש-Snowflake INTEGER, שמיוצג באופן מרומז כ-NUMBER(38,0) ב-Snowflake, יתורגם ל-BigQuery INT64 במקום ל-BIGNUMERIC(38).

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

דוגמאות לאופטימיזציה

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