תרגום שאילתות SQL באמצעות ממשק ה-API של התרגום

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

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

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

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

הפעלת התרגום

מפעילים את BigQuery Migration API הנדרש. מידע נוסף זמין במאמר בנושא הפעלת תרגומים של SQL.

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

כדי לקבל את ההרשאות הדרושות לך ליצירת עבודות תרגום באמצעות מתרגם האינטראקטור, ממשק ה-API של התרגום או מתרגם ה-SQL של ​​הקבוצה המאוחדת, בקש ממנהל המערכת שלך להעניק לך את תפקידי ה-IAM הבאים במשאב parent:

  • צפייה במשימות העברה ומעקב אחריהן: צפייה ב-MigrationWorkflow (roles/bigquerymigration.viewer)
  • שליחת משימות העברה: MigrationWorkflow Editor (roles/bigquerymigration.editor)
  • גישה לקטגוריות של Cloud Storage לקלט ולקבצים: Storage Object Admin‏ (roles/storage.objectAdmin) – בקטגוריית המקור ובקטגוריית היעד של Cloud Storage.

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

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

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

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

  • bigquerymigration.workflows.create
  • bigquerymigration.workflows.get
  • bigquerymigration.workflows.list
  • bigquerymigration.workflows.delete
  • bigquerymigration.subtasks.get
  • bigquerymigration.subtasks.list
  • storage.objects.get
  • storage.objects.list
  • storage.objects.create

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

העלאת קבצי קלט לאחסון ענן

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

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

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

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

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

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

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

פריסת פונקציות UDF מסייעות בפרויקט

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

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

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

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

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

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

    הפלט מאוחסן בתיקיית משנה בתיקיית הפלט. שם תיקיית המשנה נקבע לפי הערך ב-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/"
             }
          },
       }
    }
  }
}

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

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

בדוגמה הבאה, התסריטים של 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",
       }
    }
  }
}

לאחר שהמשימה תבוצע בהצלחה, ניתן למצוא הצעות של בינה מלאכותית בספריית אחסון הענן של gs://my_data_bucket/teradata/output/suggestion.

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

הפקודה 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",
    }
  }
}

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