תרגום שאילתות SQL באמצעות Translate 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.

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

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

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

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

מיקומים

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

	תיאור האזור	שם האזור	פרטים
אסיה והאוקיינוס השקט
	דלהי	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, משתמשים ב-method‏ 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 Vertex AI כדי ליצור הצעות לשאילתת ה-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. אפשר לבדוק את נקודת הקצה הזו כדי לראות את הסטטוס של תהליך העבודה.