使用 Translation API 翻譯 SQL 查詢

本文說明如何使用 BigQuery 中的翻譯 API，將以其他 SQL 方言編寫的指令碼翻譯成 GoogleSQL 查詢。翻譯 API 可簡化將工作負載遷移至 BigQuery的程序。

事前準備

提交翻譯工作前，請先完成下列步驟：

  1. 請確認您具備所有必要權限。
  2. 啟用 BigQuery Migration API。
  3. 收集含有待翻譯 SQL 指令碼和查詢的來源檔案。
  4. 將來源檔案上傳至 Cloud Storage。

所需權限

如要取得使用翻譯 API 建立翻譯工作的必要權限，請要求管理員為您授予 parent 資源的 MigrationWorkflow Editor (roles/bigquerymigration.editor) 身分與存取權管理角色。如要進一步瞭解如何授予角色，請參閱「管理專案、資料夾和機構的存取權」。

這個預先定義的角色包含使用翻譯 API 建立翻譯工作所需的權限。如要查看確切的必要權限，請展開「必要權限」部分：

所需權限

如要使用翻譯 API 建立翻譯工作，必須具備下列權限：

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

您或許還可透過自訂角色或其他預先定義的角色取得這些權限。

啟用 BigQuery Migration API

如果您的 Google Cloud CLI 專案是在 2022 年 2 月 15 日前建立，請按照下列步驟啟用 BigQuery Migration API：

  1. 前往 Google Cloud 控制台的「BigQuery Migration API」頁面。

    前往 BigQuery Migration API

  2. 按一下「啟用」。

將輸入檔案上傳至 Cloud Storage

如果您想使用 Google Cloud 主控台或 BigQuery Migration API 執行轉譯工作，必須將含有要轉譯的查詢和指令碼的原始檔案上傳至 Cloud Storage。您也可以將任何中繼資料檔案或設定 YAML 檔案上傳至含有原始檔案的同一 Cloud Storage 值區。如要進一步瞭解如何建立值區及將檔案上傳至 Cloud Storage，請參閱「建立值區」和「從檔案系統上傳物件」相關說明。

支援的工作類型

翻譯 API 可將下列 SQL 方言翻譯為 GoogleSQL：

  • Amazon Redshift SQL - Redshift2BigQuery_Translation
  • Apache HiveQL 和 Beeline CLI - HiveQL2BigQuery_Translation
  • Apache Spark SQL - SparkSQL2BigQuery_Translation
  • Azure Synapse T-SQL - AzureSynapse2BigQuery_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

使用輔助 UDF 處理不支援的 SQL 函式

將 SQL 從來源方言轉譯至 BigQuery 時，某些函式可能沒有直接對應的函式。為解決這個問題，BigQuery 遷移服務 (以及更廣泛的 BigQuery 社群) 提供輔助使用者定義函式 (UDF)，可複製這些不支援的來源方言函式的行為。

這些 UDF 通常會出現在 bqutil 公開資料集，讓經過翻譯的查詢最初以 bqutil.<dataset>.<function>() 格式參照這些 UDF。例如：bqutil.fn.cw_count()。

正式環境的重要注意事項：

雖然 bqutil 可讓您輕鬆存取這些輔助 UDF，用於初始轉譯和測試，但不建議直接依賴 bqutil 處理實際工作負載，原因如下：

  1. 版本控制：bqutil 專案會代管這些 UDF 的最新版本，因此定義可能會隨時間變更。如果 UDF 邏輯更新，直接依賴 bqutil 可能會導致意外行為或破壞實際工作環境查詢。
  2. 依附元件隔離：將 UDF 部署至專案，可將正式環境與外部變更隔離。
  3. 自訂：您可能需要修改或最佳化這些 UDF，以便更符合特定業務邏輯或成效需求。只有在這些資源位於您自己的專案中時，才能使用這項功能。
  4. 安全性和治理：貴機構的安全性政策可能會限制直接存取 bqutil 等公開資料集，以便處理實際工作環境中的資料。將 UDF 複製到受控環境，即可符合這類政策。

將輔助 UDF 部署至專案：

為了確保可靠且穩定的實際工作環境使用體驗，您應將這些輔助 UDF 部署至專屬專案和資料集。這樣一來，您就能完全掌控版本、自訂選項和存取權。如需部署這些 UDF 的詳細操作說明，請參閱 GitHub 上的 UDF 部署指南。本指南提供必要的指令碼和步驟，協助您將 UDF 複製到環境中。

位置

翻譯 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 節能綠葉圖示 二氧化碳排放量低2
柏林 europe-west10 節能綠葉圖示 二氧化碳排放量低2
歐盟多個區域 eu
芬蘭 europe-north1 節能綠葉圖示 低二氧化碳
法蘭克福 europe-west3 節能綠葉圖示 二氧化碳排放量低2
倫敦 europe-west2 節能綠葉圖示 二氧化碳排放量低2
馬德里 europe-southwest1 節能綠葉圖示 二氧化碳排放量低2
米蘭 europe-west8
荷蘭 europe-west4 節能綠葉圖示 二氧化碳排放量低
巴黎 europe-west9 節能綠葉圖示 二氧化碳排放量低2
斯德哥爾摩 europe-north2 節能綠葉圖示 低二氧化碳
杜林 europe-west12
華沙 europe-central2
蘇黎世 europe-west6 節能綠葉圖示 二氧化碳排放量低
美洲
俄亥俄州哥倫布 us-east5
達拉斯 us-south1 節能綠葉圖示 二氧化碳排放量低2
愛荷華州 us-central1 節能綠葉圖示 二氧化碳排放量低2
拉斯維加斯 us-west4
洛杉磯 us-west2
墨西哥 northamerica-south1
北維吉尼亞州 us-east4
奧勒岡州 us-west1 節能綠葉圖示 二氧化碳排放量低2
魁北克 northamerica-northeast1 節能綠葉圖示 二氧化碳排放量低2
聖保羅 southamerica-east1 節能綠葉圖示 二氧化碳排放量低
鹽湖城 us-west3
聖地亞哥 southamerica-west1 節能綠葉圖示 二氧化碳排放量低2
南卡羅來納州 us-east1
多倫多 northamerica-northeast2 節能綠葉圖示 二氧化碳排放量低2
美國 (多個區域) us
非洲
約翰尼斯堡 africa-south1
MiddleEast
達曼 me-central2
杜哈 me-central1
以色列 me-west1

提交翻譯工作

如要使用翻譯 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 格式編寫的工作流程 ID。

批次翻譯範例

如要翻譯 Cloud Storage 目錄 gs://my_data_bucket/teradata/input/ 中的 Teradata SQL 指令碼，並將結果儲存在 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" 欄位中傳回訊息，其中包含已建立的工作流程 ID：

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

如要取得工作流程的更新狀態，請執行 GET 查詢。工作會在執行期間將輸出內容傳送至 Cloud Storage。所有要求的 target_types 產生後，工作 state 會變更為 COMPLETED。如果工作順利完成，您可以在 gs://my_data_bucket/teradata/output 中找到已翻譯的 SQL 查詢。

使用 AI 建議的批次翻譯範例

以下範例會翻譯位於 gs://my_data_bucket/teradata/input/ Cloud Storage 目錄中的 Teradata SQL 指令碼，並將結果儲存在 Cloud Storage 目錄 gs://my_data_bucket/teradata/output/ 中，並提供額外的 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 Cloud Storage 目錄中找到 AI 建議。

使用字串文字輸入和輸出建立互動式翻譯工作

下列 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：常值項目的 ID，類似檔案名稱或路徑。
  • STRING：要轉譯的字串式輸入資料 (例如 SQL)。
  • TARGETS：使用者希望在回應中直接以 literal 格式傳回的預期目標。這些項目應採用指定 URI 格式 (例如 GENERATED_DIR + target_spec.relative_path + source_spec.literal.relative_path)。不在這個清單中的項目不會在回應中傳回。一般 SQL 翻譯的產生目錄 GENERATED_DIR 為 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 格式編寫的工作流程 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 做為字面字串，但只有在 target_return_literals 中加入 sql/$relative_path 時，系統才會在結果中顯示已翻譯的字面字串。您也可以在單一查詢中加入多個文字常值，在這種情況下，必須在 target_return_literals 中加入各個相對路徑。

這項呼叫會在 "name" 欄位中傳回訊息，其中包含已建立的工作流程 ID：

{
  "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"
}

探索翻譯輸出內容

執行翻譯工作後，請使用下列指令指定翻譯工作工作流程 ID，擷取結果：

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：建立翻譯工作流程時產生的 ID。

回應包含遷移工作流程的狀態，以及 target_return_literals 中的任何已完成的檔案。

回應會包含移轉工作流程的狀態，以及 target_return_literals 中的任何已完成的檔案。您可以輪詢這個端點，檢查工作流程的狀態。