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

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

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

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

הכלי האינטראקטיבי לתרגום SQL, ‏BigQuery Migration API והכלי לתרגום SQL באצווה תומכים בשימוש בכמה קובצי 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 לביטוי רגולרי (גרסת טרום-השקה (Preview)).
  • ‫schemaRegex: התאמה של מאפייני schema לביטויים רגולריים (גרסת טרום-השקה (Preview)).
  • ‫relationRegex: התאמה של מאפייני relation באמצעות ביטויים רגולריים (גרסת טרום-השקה (Preview)).
  • ‫attributeRegex: התאמה של מאפייני attribute לביטויים רגולריים. רלוונטי רק לבחירת מאפיינים (גרסת טרום-השקה (Preview)).

לדוגמה, בקובץ ה-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.

בדוגמאות הבאות, הדיאלקט של קלט ה-SQL הוא Teradata או Apache Hive, והדיאלקט של הפלט הוא 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:
-
  key: "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 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 הבא משנה את סכימת האשכולות של טבלה:

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

ברוב המקרים, מתרגם ה-SQL של BigQuery יכול לנתח באופן אוטומטי את שפת הגדרת הנתונים (DDL) של פונקציות חיצוניות בהגדרת המשתמש (UDF), כדי לקבוע את סוגי נתוני הקלט והפלט שלהן, גם אם גוף ה-UDF כתוב בשפה חיצונית. עם זאת, בניבים מסוימים של מקורות, יכול להיות שהמתרגם לא יוכל לקמפל באופן אוטומטי את החתימה של ה-UDF. כדי לוודא שהצהרות SQL שמפנות לפונקציות UDF האלה מתורגמות בצורה מדויקת בלי לספק את ה-DDL המקורי, אתם יכולים להשתמש בקובץ תצורה של YAML כדי לרשום באופן מפורש את החתימות של פונקציות 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 משמיט את הדיוק של מחרוזות כשהוא מתרגם עמודות מסוג 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

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

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

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