תרגום שאילתות SQL באמצעות Translation API

במאמר הזה מוסבר איך להשתמש ב-Translation API ב-BigQuery כדי לתרגם סקריפטים שנכתבו בניבים אחרים של SQL לשאילתות GoogleSQL. ‫Translation API יכול לפשט את תהליך העברת עומסי עבודה ל-BigQuery.

לפני שמתחילים

לפני ששולחים עבודת תרגום, צריך לבצע את השלבים הבאים:

1. מוודאים שיש לכם את כל ההרשאות הנדרשות.
2. מפעילים את BigQuery Migration API.
3. אוספים את קובצי המקור שמכילים את הסקריפטים והשאילתות של SQL שרוצים לתרגם.
4. מעלים את קובצי המקור ל-Cloud Storage.

ההרשאות הנדרשות

כדי לקבל את ההרשאות שנדרשות ליצירת משימות תרגום באמצעות Translation API, צריך לבקש מהאדמין להקצות לכם ב-IAM את התפקיד MigrationWorkflow Editor (roles/bigquerymigration.editor) במשאב parent. להסבר על מתן תפקידים, קראו איך מנהלים את הגישה ברמת הפרויקט, התיקייה והארגון.

זהו תפקיד שמוגדר מראש וכולל את ההרשאות שנדרשות ליצירת משימות תרגום באמצעות Translation API. כדי לראות בדיוק אילו הרשאות נדרשות, אפשר להרחיב את הקטע ההרשאות הנדרשות:

ההרשאות הנדרשות

כדי ליצור משימות תרגום באמצעות Translation API, נדרשות ההרשאות הבאות:

• bigquerymigration.workflows.create
• bigquerymigration.workflows.get

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

הפעלת BigQuery Migration API

אם הפרויקט שלכם ב-Google Cloud CLI נוצר לפני 15 בפברואר 2022, צריך להפעיל את BigQuery Migration API באופן הבא:

1. במסוף Google Cloud , עוברים לדף BigQuery Migration API.

   מעבר אל BigQuery Migration API

2. לוחצים על Enable.

הערה: ממשק ה-API הזה מופעל אוטומטית בפרויקטים שנוצרו אחרי 15 בפברואר 2022.

העלאת קובצי קלט ל-Cloud Storage

אם רוצים להשתמש במסוף Google Cloud או ב-BigQuery Migration API כדי לבצע משימת תרגום, צריך להעלות ל-Cloud Storage את קובצי המקור שמכילים את השאילתות והסקריפטים שרוצים לתרגם. אפשר גם להעלות קבצים של מטא-נתונים או קבצי YAML של הגדרות לאותה קטגוריה של Cloud Storage שמכילה את קובצי המקור. מידע נוסף על יצירת קטגוריות והעלאת קבצים ל-Cloud Storage זמין במאמרים בנושא יצירת קטגוריות והעלאת אובייקטים ממערכת קבצים.

סוגי המשימות הנתמכים

‫Translation API יכול לתרגם את ניבי ה-SQL הבאים ל-GoogleSQL:

• ‫Amazon Redshift SQL –‏ Redshift2BigQuery_Translation
• ‫Apache HiveQL ו-Beeline CLI – HiveQL2BigQuery_Translation
• ‫Apache Impala – Impala2BigQuery_Translation
• ‫Apache Spark SQL –‏ SparkSQL2BigQuery_Translation
• ‫Azure Synapse T-SQL – AzureSynapse2BigQuery_Translation
• ‫GoogleSQL‏ (BigQuery) – Bigquery2Bigquery_Translation
• ‫Greenplum SQL – Greenplum2BigQuery_Translation
• ‫IBM Db2 SQL – Db22BigQuery_Translation
• ‫IBM Netezza SQL ו-NZPLSQL – Netezza2BigQuery_Translation
• ‫MySQL SQL –‏ MySQL2BigQuery_Translation
• ‫Oracle SQL, ‏ PL/SQL, ‏ Exadata – Oracle2BigQuery_Translation
• ‫PostgreSQL SQL –‏ Postgresql2BigQuery_Translation
• ‫Presto או Trino SQL – Presto2BigQuery_Translation
• ‫Snowflake SQL –‏ Snowflake2BigQuery_Translation
• ‫SQLite – SQLite2BigQuery_Translation
• ‫SQL Server T-SQL –‏ SQLServer2BigQuery_Translation
• ‫Teradata ו-Teradata Vantage – Teradata2BigQuery_Translation
• ‫Vertica SQL –‏ Vertica2BigQuery_Translation

טיפול בפונקציות SQL שלא נתמכות באמצעות פונקציות UDF מסייעות

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

פונקציות UDF כאלה נמצאות לרוב במערך הנתונים הציבורי bqutil, כך ששאילתות מתורגמות יכולות להפנות אליהן בהתחלה באמצעות הפורמט bqutil.<dataset>.<function>(). לדוגמה, bqutil.fn.cw_count().

שיקולים חשובים לגבי סביבות ייצור:

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

1. ניהול גרסאות: פרויקט bqutil מארח את הגרסה העדכנית של הפונקציות האלה, כלומר ההגדרות שלהן יכולות להשתנות עם הזמן. הסתמכות ישירה על bqutil עלולה להוביל להתנהגות לא צפויה או לשינויים שוברים בשאילתות הייצור שלכם אם הלוגיקה של UDF תעודכן.
2. בידוד תלות: פריסת פונקציות UDF בפרויקט שלכם מבודדת את סביבת הייצור משינויים חיצוניים.
3. התאמה אישית: יכול להיות שתצטרכו לשנות או לבצע אופטימיזציה של הפונקציות המוגדרות על ידי המשתמש כדי שיתאימו יותר ללוגיקה העסקית הספציפית או לדרישות הביצועים שלכם. אפשר לעשות את זה רק אם הם נמצאים בפרויקט שלכם.
4. אבטחה וניהול: יכול להיות שמדיניות האבטחה של הארגון שלכם מגבילה גישה ישירה למערכי נתונים ציבוריים כמו bqutil לעיבוד נתוני ייצור. העתקת פונקציות מוגדרות על ידי המשתמש לסביבה המבוקרת תואמת למדיניות כזו.

פריסת פונקציות UDF מסוג helper בפרויקט:

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

מיקומים

‫Translation API זמין במיקומי העיבוד הבאים:

	תיאור האזור	שם האזור	פרטים
אסיה ואזור האוקיינוס השקט
	בנגקוק	asia-southeast3
	דלהי	asia-south2
	הונג קונג	asia-east2
	ג'קארטה	asia-southeast2
	מלבורן	australia-southeast2
	מומבאי	asia-south1
	אוסקה	asia-northeast2
	סיאול	asia-northeast3
	סינגפור	asia-southeast1
	סידני	australia-southeast1
	טייוואן	asia-east1
	טוקיו	asia-northeast1
אירופה
	בלגיה	europe-west1	‫ רמה נמוכה של CO2‎
	ברלין	europe-west10
	אירופה, במספר אזורים	eu
	פינלנד	europe-north1	‫ רמה נמוכה של CO2‎
	פרנקפורט	europe-west3
	לונדון	europe-west2	‫ רמה נמוכה של CO2‎
	מדריד	europe-southwest1	‫ רמה נמוכה של CO2‎
	מילאנו	europe-west8
	הולנד	europe-west4	‫ רמה נמוכה של CO2‎
	פריז	europe-west9	‫ רמה נמוכה של CO2‎
	שטוקהולם	europe-north2	‫ רמה נמוכה של CO2‎
	טורינו	europe-west12
	ורשה	europe-central2
	ציריך	europe-west6	‫ רמה נמוכה של CO2‎
אמריקה
	קולומבוס, אוהיו	us-east5
	דאלאס	us-south1	‫ רמה נמוכה של CO2‎
	אייווה	us-central1	‫ רמה נמוכה של CO2‎
	לאס וגאס	us-west4
	לוס אנג'לס	us-west2
	מקסיקו	northamerica-south1
	צפון וירג'יניה	us-east4
	אורגון	us-west1	‫ רמה נמוכה של CO2‎
	קוויבק	northamerica-northeast1	‫ רמה נמוכה של CO2‎
	סאו פאולו	southamerica-east1	‫ רמה נמוכה של CO2‎
	סולט לייק סיטי	us-west3
	סנטיאגו	southamerica-west1	‫ רמה נמוכה של CO2‎
	דרום קרוליינה	us-east1
	טורונטו	northamerica-northeast2	‫ רמה נמוכה של CO2‎
	ארה"ב, במספר אזורים	us
אפריקה
	יוהנסבורג	africa-south1
MiddleEast
	דמאם	me-central2
	דוחה	me-central1
	ישראל	me-west1

שליחת עבודת תרגום

כדי לשלוח משימת תרגום באמצעות Translation API, משתמשים בשיטה projects.locations.workflows.create ומספקים מופע של המשאב MigrationWorkflow עם סוג משימה נתמך.

אחרי ששולחים את העבודה, אפשר להריץ שאילתה כדי לקבל תוצאות.

יצירת תרגום באצווה

הפקודה curl הבאה יוצרת משימת תרגום באצווה שבה קובצי הקלט והפלט מאוחסנים ב-Cloud Storage. השדה source_target_mapping מכיל רשימה שממפה את הערכים של מקור literal לנתיב יחסי אופציונלי של פלט היעד.

curl -d "{
  \"tasks\": {
      string: {
        \"type\": \"TYPE\",
        \"translation_details\": {
            \"target_base_uri\": \"TARGET_BASE\",
            \"source_target_mapping\": {
              \"source_spec\": {
                  \"base_uri\": \"BASE\"
              }
            },
            \"target_types\": \"TARGET_TYPES\",
        }
      }
  }
  }" \
  -H "Content-Type:application/json" \
  -H "Authorization: Bearer TOKEN" -X POST https://bigquerymigration.googleapis.com/v2alpha/projects/PROJECT_ID/locations/LOCATION/workflows

מחליפים את מה שכתוב בשדות הבאים:

• ‫TYPE: סוג המשימה של התרגום, שקובע את הניב של שפת המקור ושפת היעד.
• ‫TARGET_BASE: ה-URI הבסיסי לכל פלט התרגום.
• ‫BASE: ה-URI הבסיסי של כל הקבצים שנקראים כמקורות לתרגום.

• ‫TARGET_TYPES (אופציונלי): סוגי הפלט שנוצרו. אם לא מציינים את הפרמטר הזה, המערכת יוצרת SQL.

  • ‫sql (ברירת מחדל): קובצי שאילתות ה-SQL המתורגמות.
  • ‫suggestion: הצעות שנוצרו על ידי AI.

  הפלט מאוחסן בתיקיית משנה בתיקיית הפלט. שם תיקיית המשנה נקבע לפי הערך ב-TARGET_TYPES.

• ‫TOKEN: הטוקן לאימות. כדי ליצור אסימון, משתמשים בפקודה gcloud auth print-access-token או ב-OAuth 2.0 playground (צריך להשתמש בהיקף https://www.googleapis.com/auth/cloud-platform).

• ‫PROJECT_ID: הפרויקט שבו תתבצע התרגום.

• ‫LOCATION: המיקום שבו המשימה מעובדת.

הפקודה הקודמת מחזירה תשובה שכוללת מזהה של תהליך עבודה בפורמט projects/PROJECT_ID/locations/LOCATION/workflows/WORKFLOW_ID.

דוגמה לתרגום באצווה

כדי לתרגם את סקריפטי ה-SQL של Teradata בספריית Cloud Storage‏ gs://my_data_bucket/teradata/input/ ולאחסן את התוצאות בספריית Cloud Storage‏ gs://my_data_bucket/teradata/output/, אפשר להשתמש בשאילתה הבאה:

{
  "tasks": {
     "task_name": {
       "type": "Teradata2BigQuery_Translation",
       "translation_details": {
         "target_base_uri": "gs://my_data_bucket/teradata/output/",
           "source_target_mapping": {
             "source_spec": {
               "base_uri": "gs://my_data_bucket/teradata/input/"
             }
          },
       }
    }
  }
}

הערה: המחרוזת "task_name" בדוגמה הזו היא מזהה של משימת התרגום, ואפשר להגדיר אותה לכל ערך שרוצים.

הקריאה הזו תחזיר הודעה שמכילה את מזהה תהליך העבודה שנוצר בשדה "name":

{
  "name": "projects/123456789/locations/us/workflows/12345678-9abc-def1-2345-6789abcdef00",
  "tasks": {
    "task_name": { /*...*/ }
  },
  "state": "RUNNING"
}

כדי לקבל את הסטטוס המעודכן של תהליך העבודה, מריצים שאילתת GET. העבודה שולחת פלט ל-Cloud Storage כשהיא מתקדמת. סטטוס המשימה state משתנה לCOMPLETED אחרי שכל target_types שביקשתם נוצרו. אם המשימה מצליחה, אפשר למצוא את שאילתת ה-SQL המתורגמת ב-gs://my_data_bucket/teradata/output.

דוגמה לתרגום באצווה עם הצעות מבוססות-AI

תצוגה מקדימה

השימוש במוצר או בתכונה כפוף לתנאי השימוש במוצרים בגרסת טרום-GA, בחלק "תנאים כלליים לשירות" של התנאים הספציפיים לשירות. השימוש בתכונות ובמוצרים בגרסת טרום-GA הוא "כפי שהם" (As is), ויכול להיות שהתמיכה בהם תהיה מוגבלת. מידע נוסף זמין בקטע תיאור שלבי ההשקה.

הערה: ה-API לתרגום יכול לקרוא ל-Gemini באמצעות שילוב של BigQuery Agent Platform כדי ליצור הצעות לשאילתת ה-SQL המתורגמת על סמך קובץ ה-YAML של הגדרת ה-AI.

בדוגמה הבאה, התסריטים של Teradata SQL שנמצאים בספרייה gs://my_data_bucket/teradata/input/ ב-Cloud Storage מתורגמים, והתוצאות מאוחסנות בספרייה gs://my_data_bucket/teradata/output/ ב-Cloud Storage עם הצעה נוספת מ-AI:

{
  "tasks": {
     "task_name": {
       "type": "Teradata2BigQuery_Translation",
       "translation_details": {
         "target_base_uri": "gs://my_data_bucket/teradata/output/",
           "source_target_mapping": {
             "source_spec": {
               "base_uri": "gs://my_data_bucket/teradata/input/"
             }
          },
          "target_types": "suggestion",
       }
    }
  }
}

הערה: כדי ליצור הצעות מבוססות-AI, ספריית קובצי המקור ב-Cloud Storage צריכה להכיל לפחות קובץ YAML אחד של הגדרות עם הסיומת .ai_config.yaml. כדי ללמוד איך לכתוב את קובץ ה-YAML של ההגדרות להצעות מבוססות-AI, אפשר לעיין במאמר בנושא יצירת קובץ YAML של הגדרות מבוססות-Gemini.

אחרי שהמשימה תפעל בהצלחה, ההצעות מבוססות-ה-AI יופיעו בספרייה gs://my_data_bucket/teradata/output/suggestion Cloud Storage.

יצירת משימת תרגום אינטראקטיבית עם קלט ופלט של מחרוזות מילוליות

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

curl -d "{
  \"tasks\": {
      string: {
        \"type\": \"TYPE\",
        \"translation_details\": {
        \"source_target_mapping\": {
            \"source_spec\": {
              \"literal\": {
              \"relative_path\": \"PATH\",
              \"literal_string\": \"STRING\"
              }
            }
        },
        \"target_return_literals\": \"TARGETS\",
        }
      }
  }
  }" \
  -H "Content-Type:application/json" \
  -H "Authorization: Bearer TOKEN" -X POST https://bigquerymigration.googleapis.com/v2alpha/projects/PROJECT_ID/locations/LOCATION/workflows

מחליפים את מה שכתוב בשדות הבאים:

• ‫TYPE: סוג המשימה של התרגום, שקובע את הניב של שפת המקור ושפת היעד.
• ‫PATH: המזהה של הרשומה המילולית, בדומה לשם קובץ או לנתיב.
• ‫STRING: מחרוזת של נתוני קלט מילוליים (לדוגמה, SQL) שצריך לתרגם.
• ‫TARGETS: היעדים הצפויים שהמשתמש רוצה שיוחזרו ישירות בתגובה בפורמט literal. הם צריכים להיות בפורמט URI של יעד (לדוגמה, GENERATED_DIR + target_spec.relative_path + source_spec.literal.relative_path). כל מה שלא מופיע ברשימה הזו לא יוחזר בתשובה. הספרייה שנוצרת, GENERATED_DIR לתרגומי SQL כלליים היא sql/.
• ‫TOKEN: הטוקן לאימות. כדי ליצור אסימון, משתמשים בפקודה gcloud auth print-access-token או ב-OAuth 2.0 playground (צריך להשתמש בהיקף https://www.googleapis.com/auth/cloud-platform).
• ‫PROJECT_ID: הפרויקט שבו תתבצע התרגום.
• ‫LOCATION: המיקום שבו המשימה מעובדת.

הפקודה הקודמת מחזירה תשובה שכוללת מזהה של תהליך עבודה בפורמט projects/PROJECT_ID/locations/LOCATION/workflows/WORKFLOW_ID.

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

דוגמה לתרגום אינטראקטיבי

כדי לתרגם את מחרוזת ה-Hive SQL‏ select 1 באופן אינטראקטיבי, אפשר להשתמש בשאילתה הבאה:

"tasks": {
  string: {
    "type": "HiveQL2BigQuery_Translation",
    "translation_details": {
      "source_target_mapping": {
        "source_spec": {
          "literal": {
            "relative_path": "input_file",
            "literal_string": "select 1"
          }
        }
      },
      "target_return_literals": "sql/input_file",
    }
  }
}

הערה: המחרוזת "task_name" בדוגמה הזו היא מזהה של משימת התרגום, ואפשר להגדיר אותה לכל ערך שרוצים.

אפשר להשתמש בכל relative_path שרוצים לצירוף המילולי, אבל הצירוף המילולי המתורגם יופיע בתוצאות רק אם כוללים את sql/$relative_path ב-target_return_literals. אפשר גם לכלול כמה מחרוזות מילוליות בשאילתה אחת. במקרה כזה, צריך לכלול את הנתיבים היחסיים של כל אחת מהן ב-target_return_literals.

הקריאה הזו תחזיר הודעה שמכילה את מזהה תהליך העבודה שנוצר בשדה "name":

{
  "name": "projects/123456789/locations/us/workflows/12345678-9abc-def1-2345-6789abcdef00",
  "tasks": {
    "task_name": { /*...*/ }
  },
  "state": "RUNNING"
}

כדי לקבל את הסטטוס המעודכן של תהליך העבודה, מריצים שאילתת GET. המשימה תושלם כשהסמל "state" ישתנה לסמל COMPLETED. אם המשימה תצליח, ה-SQL המתורגם יופיע בהודעת התגובה:

{
  "name": "projects/123456789/locations/us/workflows/12345678-9abc-def1-2345-6789abcdef00",
  "tasks": {
    "string": {
      "id": "0fedba98-7654-3210-1234-56789abcdef",
      "type": "HiveQL2BigQuery_Translation",
      /* ... */
      "taskResult": {
        "translationTaskResult": {
          "translatedLiterals": [
            {
              "relativePath": "sql/input_file",
              "literalString": "-- Translation time: 2023-10-05T21:50:49.885839Z\n-- Translation job ID: projects/123456789/locations/us/workflows/12345678-9abc-def1-2345-6789abcdef00\n-- Source: input_file\n-- Translated from: Hive\n-- Translated to: BigQuery\n\nSELECT\n    1\n;\n"
            }
          ],
          "reportLogMessages": [
            ...
          ]
        }
      },
      /* ... */
    }
  },
  "state": "COMPLETED",
  "createTime": "2023-10-05T21:50:49.543221Z",
  "lastUpdateTime": "2023-10-05T21:50:50.462758Z"
}

בדיקת פלט התרגום

אחרי שמריצים את משימת התרגום, מאחזרים את התוצאות באמצעות ציון מזהה זרימת העבודה של משימת התרגום באמצעות הפקודה הבאה:

curl \
-H "Content-Type:application/json" \
-H "Authorization:Bearer TOKEN" -X GET https://bigquerymigration.googleapis.com/v2alpha/projects/PROJECT_ID/locations/LOCATION/workflows/WORKFLOW_ID

מחליפים את מה שכתוב בשדות הבאים:

• ‫TOKEN: הטוקן לאימות. כדי ליצור אסימון, משתמשים בפקודה gcloud auth print-access-token או ב-OAuth 2.0 playground (צריך להשתמש בהיקף https://www.googleapis.com/auth/cloud-platform).
• ‫PROJECT_ID: הפרויקט שבו תתבצע התרגום.
• ‫LOCATION: המיקום שבו המשימה מעובדת.
• ‫WORKFLOW_ID: המזהה שנוצר כשיוצרים תהליך עבודה לתרגום.

התשובה מכילה את הסטטוס של תהליך העבודה של ההעברה, וכל קובץ שהושלם ב-target_return_literals.

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