使用 YAML 轉換 SQL 翻譯

您可以使用 YAML 設定檔,自訂 BigQuery 在遷移期間如何翻譯來源資料庫的 SQL 程式碼。BigQuery 遷移服務支援兩種 YAML 設定檔:*.config.yaml 翻譯設定檔,可為翻譯引擎提供確定性規則;以及 *.ai_config.yaml Gemini 設定檔,可為 Gemini 提供自然語言規則。

如要使用 YAML 設定檔自訂 SQL 翻譯,請按照下列步驟操作:

  1. 建立設定檔:建立確定性*.config.yaml翻譯設定檔、*.ai_config.yamlGemini 設定檔,或同時建立這兩種設定檔,將結構規則與 Gemini 自訂功能結合。
  2. 將檔案提供給翻譯工作:翻譯單一查詢或批次 SQL 檔案時,請提交設定檔。
  3. 查看翻譯輸出內容:翻譯單一查詢時,請在回應中找出翻譯後的 SQL 和 Gemini 建議;翻譯批次 SQL 檔案時,請在 Cloud Storage 中找出這些內容。

設定 YAML 檔案的類型

下表比較兩種設定 YAML 檔案:

功能 翻譯設定 (*.config.yaml) Gemini 輔助設定 (*.ai_config.yaml)
規則套用方式 轉譯引擎會將規則套用為剖析 SQL 的確定性規則,因此相同的輸入和設定一律會產生相同的輸出。 Gemini 會將規則視為自然語言指令和範例,套用至 SQL 文字。每次執行時,輸出內容可能有所不同,因此請務必先檢查並驗證建議,再使用。
可轉換的項目 翻譯引擎可以轉換任何可辨識的物件或設定。包括資料庫、結構定義、資料表、資料欄和函式名稱與類型、資料表屬性 (例如分區、分群和到期)、工作階段預設值、UDF 簽章、最佳化,以及陳述式輸出內容 (例如 GROUP BY 和 SELECT * 樣式)。 只要您能以自然語言描述,Gemini 就能變更任何 SQL 轉換。包括格式和註解、取代模式、重寫引擎未按照您想要的方式翻譯的建構,以及產生翻譯說明。
需求條件
  • 輸入的 SQL 必須在來源方言中剖析。
  • 規則無法對剖析器無法辨識的輸入 SQL 文字執行動作,例如 ${VAR} 預留位置或範本語法。
  • 請在 *.ai_config.yaml 檔案中使用 SOURCE_SQL 規則,或自行預先處理,先移除這類文字。
  • 您必須為工作啟用 Gemini AI 建議。
  • 工作必須在支援的處理位置執行。
  • Gemini 在一定用量內可免費使用。
結果顯示位置 結果會顯示在翻譯後的 SQL 中,例如翻譯回應或 Cloud Storage 的 sql/ 輸出目錄。 結果會顯示在 Google Cloud 控制台,做為個別的 Gemini 建議;或顯示在 Google Cloud CLI 和 MCP 伺服器,做為個別的建議欄位或輸出檔案;或顯示在 Cloud Storage,做為個別的*_suggestion/輸出目錄。詳情請參閱「翻譯輸出內容的位置」。
檔案名稱 檔案名稱必須以 .config.yaml 結尾,例如 rename-tables.config.yaml。 檔案名稱必須以 .ai_config.yaml 結尾,例如 style-rules.ai_config.yaml。
翻譯服務會忽略輸入內容中任何不以 .config.yaml 或 .ai_config.yaml 結尾的 YAML 檔案,並在翻譯記錄中回報忽略的檔案。
參考資料 詳情請參閱本頁的「建立設定 YAML 檔案的指南」和「設定 YAML 範例」。 詳情請參閱本頁的「建立 Gemini 設定 YAML 檔案」一節。

兩種規則的套用方式

翻譯工作會按照下列順序套用設定規則:

  1. Gemini 會將 *.ai_config.yaml 檔案中的 SOURCE_SQL 規則套用至原始輸入 SQL,產生重寫的來源 SQL。
  2. 翻譯引擎會翻譯上一個步驟中重寫的來源 SQL,如果沒有適用的 SOURCE_SQL 規則,則會翻譯原始輸入 SQL,並套用 *.config.yaml 檔案中的任何規則,產生翻譯後的 SQL。
  3. Gemini 會將 *.ai_config.yaml 檔案中的 TARGET_SQL 規則套用至上一個步驟中轉譯的 SQL,產生重寫的目標 SQL。如果 *.ai_config.yaml 檔案包含 TRANSLATION_EXPLANATION 規則,Gemini 也會生成轉譯後 SQL 的文字說明。

建議您使用 *.config.yaml 規則進行可重複的結構性變更,例如重新命名物件、對應類型和設定分區。建議您使用 *.ai_config.yaml 規則來處理引擎無法表達的變更,或清理無法剖析的輸入內容。您可以在同一項工作中,同時使用這兩種設定檔。

提供翻譯設定檔

Google Cloud 控制台、gcloud CLI、BigQuery Migration API 和 BigQuery Migration Service MCP 伺服器,都支援在單一翻譯工作中使用多個設定檔。詳情請參閱「套用多個 YAML 設定」。

如要為翻譯工作提供設定檔,請選取下列其中一個選項:

控制台

  • 如要在翻譯單一查詢時使用設定檔,請在互動式翻譯工具的翻譯設定中指定設定檔:
  • 如要在批次翻譯 SQL 檔案時使用設定檔,請按照下列步驟操作:

gcloud

  • 使用 gcloud alpha bq migration translate 指令時,請用 --translation-config-files 旗標指定 *.config.yaml 或 *.ai_config.yaml 檔案。
  • 使用 gcloud alpha bq migration translate-batch 指令時,請執行下列任一操作:
    • *.config.yaml:納入傳遞至 --source-gcs-uris 或 --source-local-dirs 的輸入目錄中的檔案,或使用 --source-gcs-files 或 --source-local-files 旗標傳遞個別檔案。
    • *.ai_config.yaml:加入傳遞至 --source-gcs-uris 或 --source-local-dirs 的輸入目錄中的檔案,或使用 --source-gcs-files 或 --source-local-files 旗標傳遞個別檔案,並新增 --enable-ai-suggestion 旗標。

API

在 BigQuery Migration API 中,無論是翻譯單一查詢或批次 SQL 檔案,請執行下列操作:

  • *.config.yaml:在 source_spec.base_uri 的輸入路徑下上傳檔案,當檔案不在 base_uri 下時,在 source_spec 中傳遞 gcs_file_path 項目,或在 source_spec 中傳遞內嵌 literal 項目。
  • *.ai_config.yaml:在 base_uri 下提供 source_spec 中的檔案,當檔案不在 base_uri 下時,則提供為 gcs_file_path 項目,或提供為內嵌 literal 項目,並在 target_types 中加入 "suggestion",例如 "target_types": ["sql", "suggestion"]。

MCP

在 BigQuery Migration Service MCP 伺服器中:

  • 如要翻譯單一查詢 (translate_query),請按照下列步驟操作:
    • *.config.yaml:傳遞內嵌 translationConfigs 項目。
    • *.ai_config.yaml:傳遞內嵌 aiSuggestionConfigs 項目。
  • 如要翻譯批次 SQL 檔案 (translate_batch_queries),請按照下列步驟操作:
    • *.config.yaml:上傳 sourceBaseUri 下的檔案,或在 configurationFilePaths 中列出不在 sourceBaseUri 下的檔案。
    • *.ai_config.yaml:上傳 sourceBaseUri 下的檔案,或在檔案不在 sourceBaseUri 下時,將檔案列在 configurationFilePaths 中,並將 enableAiSuggestions 設為 true。

如何查看翻譯輸出內容

視您提供的設定檔而定,翻譯工作可能會產生下列輸出內容:

  • 翻譯引擎套用 *.config.yaml 檔案中的任何規則後,翻譯出的 SQL。
  • Gemini 套用 *.ai_config.yaml 檔案中的 SOURCE_SQL 規則後,重寫的來源 SQL。
  • Gemini 在 *.ai_config.yaml 檔案中套用 TARGET_SQL 規則後,重寫的目標 SQL。
  • 如果 *.ai_config.yaml 檔案包含 TRANSLATION_EXPLANATION 規則,或您要求說明,Gemini 會提供翻譯說明。

輸出內容的位置取決於翻譯方式:

  • 如要在Google Cloud 控制台或 gcloud CLI 中翻譯單一查詢時查看輸出內容,請參閱互動式 SQL 翻譯工具指南中的「探索翻譯輸出內容」一節。
  • 如要在 Cloud Storage 中翻譯批次 SQL 檔案時尋找輸出內容,或在 Google Cloud 控制台中查看輸出內容,請參閱批次 SQL 翻譯工具指南中的「探索翻譯輸出內容」。
  • 如要在使用 BigQuery Migration API 時尋找輸出內容,請參閱 BigQuery Migration API 指南中的「擷取結果」一節。如要翻譯單一查詢,請將所需的輸出路徑 (sql/<var>PATH</var>、source_sql_query_customization_suggestion/<var>PATH</var>、target_sql_query_customization_suggestion/<var>PATH</var> 或 translation_explanation_suggestion/<var>PATH</var>) 新增至 target_return_literals,以便在 translatedLiterals 中接收。
  • 如要瞭解如何在使用 BigQuery Migration Service MCP 伺服器時查看輸出內容,請參閱「使用 BigQuery Migration Service MCP 伺服器」。translate_query 和 get_translation 工具會傳回 translatedQuery、sourceSqlSuggestion 和 targetSqlSuggestion,explain_translation 工具則會傳回翻譯說明。

翻譯 *.config.yaml 檔案規定

翻譯 *.config.yaml 檔案必須符合下列規定,才能與 BigQuery 遷移服務相容:

  • 在批次翻譯工作的輸入 Cloud Storage 路徑下上傳 *.config.yaml 檔案時,放置在輸入路徑下任何位置的設定檔都會套用至整個工作。不一定要放在根層級,也不必複製到每個子目錄。如要瞭解如何建立 bucket 並將檔案上傳至 Cloud Storage,請參閱「建立 bucket」和「從檔案系統上傳物件」。
  • 單一 *.config.yaml 檔案的大小不得超過 1 MB。
  • 在單一 SQL 轉譯工作中,所有 *.config.yaml 檔案的總大小不得超過 4 MB。
  • 如果您使用 regex 語法進行名稱比對,請使用 RE2/J。
  • 所有翻譯設定檔名稱都必須以 .config.yaml 結尾,例如 change-case.config.yaml。config.yaml 本身並非有效的設定檔名稱。

建立 YAML 設定檔的指南

本節提供建立設定 YAML 檔案的一般指南:

每個 *.config.yaml 檔案都必須包含頂層 type 標頭,用於指定檔案中的設定規則類別:

  • object_rewriter:重寫 relation 屬性 (例如 temporary、ephemeral、partitionLifetime、external、description、partition 和 clustering) 和 attribute 資料類型 (type)。
  • experimental_object_rewriter:使用 outputName 重新編寫 global、database、schema、relation、attribute、function、index、sequence、variable 和 projection 的輸出物件名稱,並使用 global.typeConvert 全域轉換資料類型。詳情請參閱「輸出名稱對應」和「全域類型轉換」。
  • environment:在 session 下設定工作階段層級預設值,例如 defaultDatabase、schemaSearchPath 和 dateFormat。
  • metadata:宣告使用者定義函式 (UDF) 簽章 (udfs),並在中繼資料查詢 (nameAlias) 期間移除輸入名稱前置字元、後置字元或規則運算式模式。
  • statement_rewriter:自訂陳述式輸出內容,例如 SELECT * 展開、GROUP BY 和 ORDER BY 格式設定 (select),以及小數或字串精確度嚴格程度 (common)。
  • input_formats:指定輸入 SQL 檔案 (formats) 的字元編碼。
  • optimizer:啟用、停用或設定選用的查詢最佳化 (transformations)。

以下範例使用 experimental_object_rewriter 類型,將所有輸出物件名稱轉換為大寫:

type: experimental_object_rewriter
global:
  outputName:
    all:
      case: UPPERCASE

實體選取

如要執行實體專屬轉換,請在設定檔中指定來源 SQL 實體。所有 match 屬性皆為選用,請只使用轉換所需的 match 屬性。match 欄位會與來源物件名稱進行比較,但前提是已解析所有設定的 defaultDatabase 和 schemaSearchPath,除非在 outputName 規則中也設定了 global.outputName。詳情請參閱「輸出名稱對應」。下列設定 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:比對來源資料庫名稱,對應至 BigQuery 中的 project_id 元件。
  • schema:與來源結構定義名稱相符,對應至 BigQuery 中的 dataset 元件。
  • relation:與來源資料表或檢視表名稱相符。
  • attribute:與來源資料欄名稱相符。這個屬性僅適用於 attribute 選取項目。
  • databaseRegex 或 dbRegex:根據不區分大小寫的規則運算式,比對整個 database 名稱。
  • schemaRegex:根據不區分大小寫的規則運算式,比對整個 schema 名稱。
  • relationRegex:根據不區分大小寫的規則運算式,比對整個 relation 名稱。
  • attributeRegex:根據不區分大小寫的規則運算式,比對整個 attribute 名稱。這個屬性僅適用於 attribute 選取項目。

舉例來說,下列設定 YAML 會指定 match 屬性,選取 testdb.acme.employee 資料表進行臨時資料表轉換。

type: object_rewriter
relation:
-
  match:
    database: testdb
    schema: acme
    relation: employee
  temporary: true

您可以使用 databaseRegex、schemaRegex、relationRegex 和 attributeRegex 屬性指定規則運算式,選取實體子集。以下範例會將 testdb 中 tmp_schema 結構定義的所有關係變更為暫時關係,前提是其名稱開頭為 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 的別名。key 和 match 都支援明確的名稱元件子欄位,以及以點分隔的 "database.schema.relation" 或 "database.schema.relation.attribute" 短字串語法。按照慣例,key 通常會與短字串語法搭配使用,而 match 則會與明確子欄位搭配使用。

預設資料庫

部分輸入 SQL 方言 (尤其是 Teradata) 不支援限定 ID (schema.table) 中的資料庫名稱。在這種情況下,請使用物件 match 並省略 database 屬性。或者,您也可以設定 defaultDatabase (例如在 type: environment 下方),以便在物件或短字串 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 查詢翻譯的影響。

下列範例使用 Teradata 或 Apache Hive 做為輸入 SQL 方言,並使用 BigQuery SQL 做為輸出方言。

變更物件名稱大小寫

下列設定 YAML 會將 SQL 物件名稱的大小寫變更為 UPPERCASE 或 LOWERCASE。您可以在 all 物件上設定 case,或針對特定物件類型 (database、schema、relation、attribute、function、sequence、index、variable 或 projection) 覆寫該屬性:

type: experimental_object_rewriter
global:
  outputName:
    all:
      case: UPPERCASE
    database:
      case: LOWERCASE
    attribute:
      case: LOWERCASE

使用這個設定 YAML 檔案的 SQL 轉譯可能如下所示:

teradata-input.sql
      create table TESTDB.testschema.x(A int);
      select * from TESTDB.testschema.x;
    
bq-output.sql
      CREATE TABLE testdb.TESTSCHEMA.X
      (
        a INT64
      )
      ;
      SELECT
          X.*
        FROM
          testdb.TESTSCHEMA.X
      ;
    

將資料表設為臨時資料表

下列設定 YAML 會將一般資料表變更為臨時資料表:

type: object_rewriter
relation:
  -
    match: "testdb.testschema.x"
    temporary: true

使用這個設定 YAML 檔案的 SQL 翻譯可能如下所示:

teradata-input.sql
    create table testdb.testschema.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

使用這個設定 YAML 檔案的 SQL 翻譯可能如下所示:

teradata-input.sql
    create table testdb.testschema.x(a int);
    
bq-output.sql
    CREATE TABLE testdb.testschema.x
    (
      a INT64
    )
    OPTIONS(
      expiration_timestamp=timestamp_add(current_timestamp(), interval 60 SECOND)
    );
    

設定分區到期時間

下列設定 YAML 會將分區資料表的到期時間變更為 1 天:

type: object_rewriter
relation:
  -
    match: "testdb.testschema.x"
    partitionLifetime:
      expireAfterSeconds: 86400

使用這個設定 YAML 檔案的 SQL 翻譯可能如下所示:

teradata-input.sql
    create table testdb.testschema.x(a date, b int) partition by (a);
    
bq-output.sql
    CREATE TABLE testdb.testschema.x
    (
      a DATE,
      b INT64
    )
    PARTITION 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

使用這個設定 YAML 檔案的 SQL 翻譯可能如下所示:

teradata-input.sql
    create table testdb.testschema.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."

使用這個設定 YAML 檔案的 SQL 翻譯可能如下所示:

teradata-input.sql
    create table testdb.testschema.x(a int);
    
bq-output.sql
    CREATE TABLE testdb.testschema.x
    (
      a INT64
    )
    OPTIONS(
      description='Example description.'
    );
    

設定或變更資料表分區

下列設定 YAML 會使用 add、remove 或 set 變更資料表的分區配置,其中 set 會取代任何現有的分區資料欄:

type: object_rewriter
relation:
  -
    match: "testdb.testschema.x"
    partition:
      simple:
        add: [a]
  -
    match: "testdb.testschema.y"
    partition:
      simple:
        remove: [a]

使用這個設定 YAML 檔案的 SQL 翻譯可能如下所示:

teradata-input.sql
    create table testdb.testschema.x(a date, b int);
    create table testdb.testschema.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 會變更資料表的分區配置,依範圍 bucket 分區:

type: object_rewriter
relation:
-
  match: "testdb.testschema.mytable"
  partition:
    range_bucket:
      column: "id"
      start: 0
      end: 100
      step: 10
      options:
        require_partition_filter: "TRUE"

使用這個設定 YAML 檔案的 SQL 翻譯可能如下所示:

snowflake-input.sql
      create table testdb.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 會使用 add、remove 或 set 變更資料表的叢集配置,其中 set 會取代任何現有的叢集資料欄:

type: object_rewriter
relation:
  -
    match: "testdb.testschema.x"
    clustering:
      add: [a]
  -
    match: "testdb.testschema.y"
    clustering:
      remove: [b]

使用這個設定 YAML 檔案的 SQL 翻譯可能如下所示:

hive-input.sql
    create table testdb.testschema.x(a int, b int);
    create table testdb.testschema.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)

您可以將來源資料類型轉換為任何支援的目標屬性類型。

使用這個設定 YAML 檔案的 SQL 翻譯可能如下所示:

teradata-input.sql
    create table testdb.testschema.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)
    )
    ;
    

如果來源和目標類型之間沒有直接轉換,例如將儲存 YYYYMMDD 日期的 INT 資料欄轉換為 DATE 資料欄,請提供 GoogleSQL sourceToTarget 和 targetToSource 轉換運算式。請使用 ? 做為資料欄或運算式值的預留位置。例如:

type: object_rewriter
attribute:
  -
    match: "testdb.acme.employee.jd"
    type:
      target: DATE
      sourceToTarget: "parse_date('%Y%m%d', cast(? as string))"
      targetToSource: "cast(format_date('%Y%m%d', cast(? as date)) as int64)"

使用這個設定 YAML 檔案的 SQL 翻譯可能如下所示:

teradata-input.sql
    create table testdb.acme.employee(eid int, jd int);
    insert into testdb.acme.employee select 42, 20011213;
    select eid, jd from testdb.acme.employee e where jd > 20010101;
    
bq-output.sql
    CREATE TABLE testdb.acme.employee
    (
      eid INT64,
      jd DATE
    )
    ;
    INSERT INTO testdb.acme.employee
      SELECT
          42,
          parse_date('%Y%m%d', CAST(20011213 as STRING))
    ;
    SELECT
        eid,
        CAST(format_date('%Y%m%d', jd) as INT64) AS jd
      FROM
        testdb.acme.employee AS e
      WHERE CAST(format_date('%Y%m%d', jd) as INT64) > 20010101
    ;
    

新增外部資料湖泊連線

下列設定 YAML 會將來源資料表標示為外部資料表,指向儲存在外部資料湖泊中的資料,並由資料湖泊連線指定。

type: object_rewriter
relation:
-
  match: "testdb.testschema.x"
  external:
    connection_id: "connection_test"

使用這個設定 YAML 檔案的 SQL 翻譯可能如下所示:

hive-input.sql
    CREATE TABLE testdb.testschema.x
    (
      a VARCHAR(150),
      b INT
    );
    
bq-output.sql
    CREATE EXTERNAL TABLE testdb.testschema.x
    (
      a STRING,
      b INT64
    )
    WITH CONNECTION `connection_test`
    OPTIONS(
    );
    

變更輸入檔案的字元編碼

根據預設,BigQuery 移轉服務會嘗試自動偵測輸入檔案的字元編碼。如果 BigQuery 遷移服務可能誤判檔案編碼,您可以使用 YAML 設定檔明確指定字元編碼。

下列設定 YAML 將輸入檔案的明確字元編碼指定為 ISO-8859-1。您可以使用確切路徑 (path)、glob 模式 (pathGlob) 或規則運算式 (pathRegex) 比對輸入檔案:

type: input_formats
formats:
- source:
    pathGlob: "*.sql"
  contents:
    raw:
      charset: iso-8859-1

全域類型轉換

下列設定 YAML 會在所有指令碼中將資料類型變更為另一種,並指定在轉譯指令碼中應避免的來源資料類型。這與「變更資料欄屬性的類型」設定不同,後者只會變更單一屬性的資料類型。

翻譯服務支援下列資料類型轉換:

  • DATETIME 至 TIMESTAMP
  • TIMESTAMP到DATETIME (可選填時區)
  • TIMESTAMP WITH TIME ZONE (timestamptz) 至 DATETIME (可選填時區)
  • CHAR 至 VARCHAR (接受選用的 precisionAction 和 comparisonBehavior)
  • VARCHAR 至 VARCHAR (接受選用的 precisionAction 和 comparisonBehavior)

在以下範例中,設定 YAML 會將 TIMESTAMP 資料類型轉換為 DATETIME。

type: experimental_object_rewriter
global:
  typeConvert:
    timestamp: DATETIME

轉換 CHAR 和 VARCHAR 類型

在 Teradata 等方言中,CHAR(N) 值會以空格向右填補,而字串寫入會截斷至宣告的資料欄長度。字串比較預設為不區分大小寫 (NOT CASESPECIFIC),且會忽略結尾空格。根據預設,翻譯引擎會在 BigQuery 中模擬這些語意。它會在轉換和寫入時新增右側填補 (CONCAT) 和截斷 (SUBSTR) 包裝函式,在比較時新增 UPPER 和 RTRIM 包裝函式,並在 GROUP BY 鍵上新增 UPPER 包裝函式。

您可以使用下列屬性,設定 global.typeConvert 下的 char 和 varchar 欄位:

  • target:必須設為 VARCHAR。在 char 上設定 target: VARCHAR 會移除 CHAR 右側邊框間距 (CONCAT),即使 precisionAction 為 PRESERVE 也是如此。
  • precisionAction:控制字元長度精確度和 SUBSTR 截斷包裝函式:
    • PRESERVE (預設):保留運算式的字元長度精確度,並在轉換或插入 CHAR(N) 或 VARCHAR(N) 資料欄時,發出 SUBSTR 截斷包裝函式。如要在 CREATE TABLE DDL 中一併發出 STRING(N) 欄長度,請參閱「設定字串精確度嚴格程度」。
    • DROP:捨棄運算式的字元長度精確度,並省略 SUBSTR 截斷包裝函式。
  • comparisonBehavior:控制 RTRIM 和 UPPER 包裝函式,用於比較和 GROUP BY 鍵。如果將 char 或 varchar 設為 STRICT 以外的值,該值會套用至這兩種類型,因此在設定這兩個鍵時,請將兩者設為相同的值:
    • STRICT (預設):模擬結尾空格填補 (RTRIM) 和不區分大小寫的定序 (UPPER)。
    • PADDING_AWARE:僅模擬結尾空格填補 (RTRIM),並省略 UPPER。
    • CASE_AWARE:僅模擬不區分大小寫的定序 (UPPER),並省略 RTRIM。
    • NONE:省略 RTRIM 和 UPPER 包裝函式,因此輸出內容會使用直接比較和 GROUP BY 運算式。

下列設定 YAML 會將 CHAR 轉換為 VARCHAR,並移除 CHAR 和 VARCHAR 資料欄的截斷 (SUBSTR)、結尾空格 (RTRIM) 和不區分大小寫 (UPPER) 包裝函式:

type: experimental_object_rewriter
global:
  typeConvert:
    char:
      target: VARCHAR
      precisionAction: DROP
      comparisonBehavior: NONE
    varchar:
      target: VARCHAR
      precisionAction: DROP
      comparisonBehavior: NONE

使用這個設定 YAML 檔案的 SQL 翻譯可能如下所示:

teradata-input.sql
      create table r(a char(10), b varchar(20));
      select cast(b as char(5)) from r where a = b group by 1;
    
bq-output.sql
      CREATE TABLE r
      (
        a STRING,
        b STRING
      )
      ;
      SELECT
          b
        FROM
          r
        WHERE a = b
        GROUP BY 1
      ;
    

設定預設時區

資料庫方言對不同的日期和時間相關資料類型,有不同的語意和名稱。無論輸入方言的資料類型名稱為何,翻譯服務都會在 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) 的時間戳記。為確保兩種方言的行為一致,可能需要據此設定時區。

如果來源資料庫的預設時區不是世界標準時間,建議您指定翻譯的預設時區。這樣可以保留時區,確保翻譯後的查詢能正確運作。

在以下範例中,YAML 設定會將 TIMESTAMP 和 TIMESTAMPTZ 資料類型轉換為 DATETIME,並將目標時區設為 Europe/Paris。

如要瞭解有效的字串時區值,請參閱「時區」。

type: experimental_object_rewriter
global:
  typeConvert:
    timestamp:
      target: DATETIME
      timezone: Europe/Paris
    timestamptz:
      target: DATETIME
      timezone: Europe/Paris

使用這個設定 YAML 檔案的 SQL 翻譯可能如下所示:

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
          C_TIMESTAMP
        FROM
          X
        WHERE C_TIMESTAMP > datetime(current_timestamp(), 'Europe/Paris')
      ;
      SELECT
          X.C_TIMESTAMPTZ
        FROM
          X
        WHERE X.C_TIMESTAMPTZ > datetime(current_timestamp(), 'Europe/Paris')
      ;
      SELECT
          C_DATETIME
        FROM
          X
        WHERE C_DATETIME > datetime(current_timestamp(), 'Europe/Paris')
      ;
    

在以下範例中,設定 YAML 會將 DATETIME 資料類型轉換為 TIMESTAMP。

根據預設,TIMESTAMPTZ 會轉換為 TIMESTAMP,不需設定。

type: experimental_object_rewriter
global:
  typeConvert:
    datetime:
      target: TIMESTAMP

使用這個設定 YAML 檔案的 SQL 翻譯可能如下所示:

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
          C_TIMESTAMP
        FROM
          X
        WHERE C_TIMESTAMP > current_timestamp()
      ;
      SELECT
          C_TIMESTAMPTZ
        FROM
          X
        WHERE C_TIMESTAMPTZ > current_timestamp()
      ;
      SELECT
          X.C_DATETIME
        FROM
          X
        WHERE X.C_DATETIME > current_timestamp()
      ;
    

選取對帳單修改

下列設定 YAML 會變更 SELECT 陳述式中的星號投影、GROUP BY 和 ORDER BY 子句。

select.starProjection 欄位支援下列值:

  • ALLOW:當來源查詢使用 SELECT * 或依序顯示資料表的所有資料欄時,會發出 SELECT *。
  • PRESERVE (預設):只有在來源查詢使用 SELECT * 時,才會發出 SELECT *。
  • EXPAND:在資料表有中繼資料時,將 SELECT * 展開為個別資料欄參照。

select.groupBy 和 select.orderBy 欄位支援下列值:

  • EXPRESSION:將 GROUP BY 或 ORDER BY 項目格式化為完整運算式。
  • ALIAS:使用 SELECT 資料欄別名 (如有) 格式化 GROUP BY 或 ORDER BY 項目。
  • INDEX:使用以 1 為基準的欄位置,設定 GROUP BY 或 ORDER BY 項目的格式,例如 GROUP BY 1。

在以下範例中,設定 YAML 會將星號投影設為 EXPAND。

type: statement_rewriter
select:
  starProjection: EXPAND

使用這個設定 YAML 檔案的 SQL 翻譯可能如下所示:

teradata-input.sql
      create table x(a int, b TIMESTAMP);
      select * from x;
    
bq-output.sql
      CREATE TABLE x
      (
        a INT64,
        b TIMESTAMP
      )
      ;
      SELECT
          x.a,
          x.b
        FROM
          x
      ;
    

UDF 規格

在多數情況下,BigQuery SQL 翻譯工具可以自動剖析外部 user-defined function (UDF) 的資料定義語言 (DDL),判斷其輸入和輸出資料類型,即使 UDF 主體是以外部語言編寫也沒問題。不過,對於某些來源方言,翻譯工具可能無法自動編譯 UDF 簽章。為確保參照這些 UDF 的 SQL 陳述式能準確翻譯,且不必提供原始 DDL,您可以使用 YAML 設定檔明確註冊 UDF 簽章。

下列設定 YAML 會指定來源指令碼中使用的 user-defined function (UDF) 簽章。請以 RETURN_TYPE FUNCTION_NAME(ARG_NAME ARG_TYPE, ...) 指定來源方言中的每個簽章,包括每個參數的名稱。與中繼資料 ZIP 檔案類似,UDF 定義有助於更準確地翻譯輸入指令碼。

type: metadata
udfs:
  - "date parse_short_date(dt int)"

使用這個設定 YAML 檔案的 SQL 翻譯可能如下所示:

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(dt), interval 1 DAY)
        FROM
          x
      ;
    

部分來源方言支援在不同引數簽章中共用名稱的 UDF,有時稱為「多載 UDF」。如果是超載 UDF,請指定 udf 和不同的目標 name,讓每個簽章對應至 BigQuery 中的專屬函式名稱:

type: metadata
udfs:
  - "int custom_substr(pos int)"
  - udf: "int custom_substr(pos int, len int)"
    name: "custom_substr_with_len"

在中繼資料查詢期間移除 ID 模式

輸入 SQL 指令碼可能會參照具有前置字元、後置字元或命名模式的物件或資料欄,但這些物件或資料欄不在中繼資料定義中,例如中繼資料 DDL 定義資料欄 dt 時參照 a_dt。在這種情況下,請使用 type: metadata 下的 nameAlias,讓翻譯引擎在查詢中繼資料中的物件名稱時,移除該模式。原始 ID 會保留在輸出 SQL 中。

nameAlias 欄位支援下列屬性。每個屬性都接受單一字串或字串清單,且會套用每個清單中的第一個相符項目:

  • stripPrefix:在查詢中繼資料前,先移除常值前置字串。
  • stripSuffix:在查詢中繼資料前,先移除常值後置字串。
  • stripRegex:比對區分大小寫的規則運算式,並在隨手查中繼資料時,將每個相符的子字串替換為第一個擷取群組 (...)。錨定模式 (例如 ^a_(.*)$),只比對前置字元或完整 ID。

下列設定 YAML 會在查詢中繼資料中的物件名稱時,從 ID 中移除開頭的 a_ 前置字串:

type: metadata
nameAlias:
  stripRegex: "^a_(.*)$"

使用這個設定 YAML 檔案的 SQL 翻譯可能如下所示:

teradata-input.sql
      create table t(dt date);
      select a_dt + 1 from t;
    
bq-output.sql
      CREATE TABLE t
      (
        dt DATE
      )
      ;
      SELECT
          date_add(a_dt, interval 1 DAY)
        FROM
          t
      ;
    

設定小數位數嚴格程度

根據預設,BigQuery 遷移服務會將數值精確度提高至指定小數位數可用的最高精確度。下列設定 YAML 會將精確度嚴格程度設為保留來源陳述式的小數精確度,藉此覆寫這項行為。

type: statement_rewriter
common:
  decimalPrecision: STRICT

使用這個設定 YAML 檔案的 SQL 翻譯可能如下所示:

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: statement_rewriter
common:
  stringPrecision: STRICT

使用這個設定 YAML 檔案的 SQL 翻譯可能如下所示:

teradata-input.sql
      create table x(a varchar(3));
    
bq-output.sql
      CREATE TABLE x
      (
        a STRING(3)
      )
      ;
    

輸出名稱對應

您可以在設定 YAML 檔案中使用 type: experimental_object_rewriter,以靜態或動態方式對應 SQL 物件名稱 (database、schema、relation、attribute、function、sequence、index、variable 或 projection):

  • 每個物件類型只會展開自己的名稱和父項階層預留位置,包括 ${database}、${schema}、${relation}、${attribute}、${function}、${sequence}、${index}、${variable} 和 ${projection}。舉例來說,schema 規則會展開 ${database} 和 ${schema},而 relation 規則會展開 ${database}、${schema} 和 ${relation}。
  • 如果有多個相同物件類型的規則,且 match 條件不同,則會套用清單中最後一個符合條件的規則。
  • global.outputName 下的全域 replace 和 case 規則會在物件專屬 outputName 規則之前執行。物件專屬規則會比對並覆寫物件的全域轉換名稱。對於 attribute 規則,match 會將全域轉換的 attribute 名稱與來源 database、schema 和 relation 名稱進行比較。詳情請參閱「全域輸出名稱重寫」。

如要進一步瞭解名稱對應規則在資料庫、結構定義、關係、函式和屬性層級的運作方式,請參閱「對應 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"

使用這個設定 YAML 檔案的 SQL 翻譯可能如下所示:

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
      )
      ;
    

您可以使用靜態名稱對應,更新公開 user-defined functions 中名稱使用的區域。

以下範例會將 bqutil.fn UDF 中的名稱,從使用預設的 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}"

使用這個設定 YAML 檔案的 SQL 翻譯可能如下所示:

teradata-input.sql
      create table staging.my_table(a int);
    
bq-output.sql
      CREATE TABLE production.stg_my_table
      (
        a INT64
      )
      ;
    

資料欄名稱對應

使用 type: experimental_object_rewriter 下方的 attribute 區段,重新命名資料表定義和查詢參照中的資料欄:

type: experimental_object_rewriter
attribute:
-
  match:
    relation: employee
    attributeRegex: "\\d.*"
  outputName:
    attribute: "col_${attribute}"

使用這個設定 YAML 檔案的 SQL 翻譯可能如下所示:

teradata-input.sql
      create table employee(eid int, "2name" varchar(10));
      select eid, "2name" from employee e;
    
bq-output.sql
      CREATE TABLE employee
      (
        eid INT64,
        col_2name STRING
      )
      ;
      SELECT
          eid,
          col_2name
        FROM
          employee AS e
      ;
    

指定預設資料庫和結構定義搜尋路徑

下列設定 YAML 指定了預設資料庫和結構定義搜尋路徑。

type: environment
session:
  defaultDatabase: myproject
  schemaSearchPath: [myschema1, myschema2]

使用這個設定 YAML 檔案的 SQL 翻譯可能如下所示:

teradata-input.sql
      SELECT * FROM mydb.mytable;
      SELECT * FROM table1;
    
bq-output.sql
      SELECT * FROM myproject.mydb.mytable;
      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

使用這個設定 YAML 檔案的 SQL 翻譯可能如下所示:

oracle-input.sql
    create table x(dt date default '31/12/1999');
    insert into x values ('01/01/2000');
    
bq-output.sql
      CREATE TABLE X
      (
        DT DATETIME DEFAULT DATETIME '1999-12-31 00:00:00'
      )
      ;
      INSERT INTO X
        VALUES (DATETIME '2000-01-01 00:00:00')
      ;
    

全域輸出名稱重寫

下列設定 YAML 會使用 global.outputName 下方的 replace 規則,重寫所有物件 (all) 或特定物件類型 (database、schema、relation、attribute、function、sequence、index、variable 或 projection) 的輸出名稱。每項規則都會比對區分大小寫規則運算式,並將相符項目替換為新文字,包括 $0、$1 和其他擷取群組。

全域 outputName 轉換 (replace 後面接著 case) 會在物件專屬 outputName 規則 (例如 schema、relation 或 attribute) 之前執行。物件專屬規則會比對並覆寫物件的全域轉換名稱。對於 attribute 規則,match 會將全域轉換的 attribute 名稱與來源 database、schema 和 relation 名稱進行比較:

type: experimental_object_rewriter
global:
  outputName:
    all:
      replace:
        - regex: '\s'
          replaceWith: '_'
        - regex: '>='
          replaceWith: 'gte'
        - regex: '^[^a-zA-Z_].*'
          replaceWith: '_$0'

使用這個設定 YAML 檔案的 SQL 翻譯可能如下所示:

teradata-input.sql
      create table "test special chars >= 12"("42eid" int, "custom column" varchar(10));
    
bq-output.sql
      CREATE TABLE test_special_chars_gte_12
      (
        _42eid INT64,
        custom_column STRING
      )
      ;
    

最佳化及提升翻譯 SQL 的效能

您可以對翻譯後的 SQL 套用選用轉換,藉此導入可提升效能或降低成本的變更。這些最佳化措施嚴格取決於個案,應與未修改的 SQL 輸出進行評估,以評估其對效能的實際影響。

下列設定 YAML 會啟用選用轉換。transformations 清單中的每個項目都支援下列欄位:

  • name:最佳化名稱。
  • enabled (選用):布林值,預設為 true。將 enabled 設為 false,即可停用來源方言預設啟用的最佳化功能。
  • parameters (選用):最佳化專屬參數的對應。
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 子句或兩者中的運算式替換為預先計算的變數,藉此重寫查詢。這項重寫作業會將運算式轉換為靜態述詞,可減少讀取的資料量。scope 參數接受包含 PREDICATE、PROJECTION 或兩者的清單。如果省略 parameters,scope 預設為 [PREDICATE],其中包含 WHERE 和 JOIN ON 子句。

將純量子查詢擷取至 DECLARE 陳述式,會使原始述詞變成靜態,因此能改善執行計畫。這項最佳化作業會導入新的 SQL 陳述式。
REWRITE_CTE_TO_TEMP_TABLE threshold: N 如果對同一個一般資料表運算式 (CTE) 至少有 N 個參照,則將 CTE 重新編寫為臨時資料表。這種重寫方式可降低查詢複雜度,並強制執行單一 CTE。如果省略 N,則預設值為 4。

如果多次參照非一般 CTE,建議使用這項最佳化功能。 導入臨時資料表會產生額外負擔,可能比最終多次執行低複雜度或低基數 CTE 的負擔更大。這項最佳化作業會導入新的 SQL 陳述式。
REWRITE_ZERO_SCALE_NUMERIC_AS_INTEGER bigint: N 如果精確度在 N 內,則將零尺度 NUMERIC/BIGNUMERIC 屬性重寫為 INT64 類型。如果省略 N,預設值為 18。

如果來源方言沒有整數類型,建議您在翻譯時使用這項最佳化功能。變更資料欄類型時,必須檢查所有下游用途,確認類型相容性和語意變更。例如,分數除法變成整數除法,或程式碼預期數值。

Snowflake 翻譯預設會啟用這項最佳化功能,適用於精確度不超過 38 的零小數位數值。這項最佳化功能可確保 Snowflake INTEGER (在 Snowflake 中隱含表示為 NUMBER(38,0)) 會轉譯為 BigQuery INT64,而非 BIGNUMERIC(38)。

如果應用程式使用的數字精確度超過 18,建議您停用這項功能,確保 BigQuery 能處理應用程式所需的所有值。
DROP_TEMP_TABLE 為指令碼中建立的所有暫存資料表新增 DROP TABLE 陳述式,且不會在指令碼結束時捨棄。這樣一來,臨時資料表的儲存空間帳單週期就會從 24 小時縮短為指令碼執行時間。

如果指令碼執行完畢後,不會再存取臨時資料表進行任何進一步處理,建議使用這項最佳化功能。這項最佳化作業會導入新的 SQL 陳述式。
REGEXP_CONTAINS_TO_LIKE 將部分類別的 REGEXP_CONTAINS 比對模式重寫為 LIKE 運算式。

如果沒有其他程序 (例如巨集替換) 依賴輸出 SQL 中保持不變的規則運算式模式文字,建議使用這項最佳化功能。
ADD_DISTINCT_TO_SUBQUERY_IN_SET_COMPARISON 將 DISTINCT 子句新增至做為 [NOT] IN 運算子值集的子查詢。

如果子查詢結果的基數 (不重複值的數量) 遠低於值的數量,建議使用這項最佳化功能。如果未滿足這項前提,這項轉換可能會對效能造成負面影響。
APPROXIMATE_RANGE_PARTITIONS 將非連續或非常規整數分區配置轉換為 BigQuery 支援的連續、等大小分區範圍,藉此近似這些配置。根據預設,這類分區配置不會影響翻譯 DDL 陳述式中的資料表分區配置。

如果來源資料表使用非連續分區函式 (例如 Teradata RANGE_N 函式),且會受益於 BigQuery 中大小相等的分區配置,建議您使用這項最佳化功能。

最佳化範例

下列最佳化作業會將精確度小於或等於 38 的零小數位數數值類型,轉換為 BigQuery 中的 INT64。

# 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)
      )
      ;
    

建立以 Gemini 為基礎的設定 YAML 檔案

如要生成 AI 輸出內容,請提供至少一個 *.ai_config.yaml 檔案,並在介面要求時啟用 Gemini AI 建議。詳情請參閱「提供翻譯設定檔」。

需求條件

AI 輸出的設定 YAML 檔案必須有 .ai_config.yaml 後置字串。例如:rules_1.ai_config.yaml。

支援的欄位

與 *.config.yaml 檔案不同,*.ai_config.yaml 檔案不含 type 標頭。而是包含頂層 translation_rules 清單,其中每項規則都支援下列欄位:

  • suggestion_type (選用):指定要生成的 AI 建議類型。支援的建議類型如下:
    • QUERY_CUSTOMIZATION (預設):根據 *.ai_config.yaml 檔案中指定的翻譯規則,生成 SQL 程式碼的 AI 建議。
    • TRANSLATION_EXPLANATION:生成文字,其中包含翻譯後的 GoogleSQL 查詢摘要,以及來源 SQL 查詢與翻譯後的 GoogleSQL 查詢之間的差異和不一致之處。
  • rewrite_target (選用):指定 SOURCE_SQL,在翻譯引擎執行前,將規則套用至原始輸入 SQL;或指定 TARGET_SQL,將規則套用至翻譯引擎產生的翻譯 SQL。預設值為 TARGET_SQL。詳情請參閱「兩種規則的套用方式」。
  • instruction (選用):以自然語言描述要對輸入 SQL 或翻譯 SQL 套用的變更 (適用於 QUERY_CUSTOMIZATION 規則),或要生成的說明 (適用於 TRANSLATION_EXPLANATION 規則)。
  • examples (選用):提供 input 和 output SQL 配對清單,說明您希望如何修改 SQL 模式。

您可以視需要新增其他 translation_rules 和 examples。

範例

下列範例會建立 Gemini 輔助設定 YAML 檔案,可用於 SQL 轉譯。

移除預設翻譯輸出查詢中的 upper 函式

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 設定

翻譯單一查詢或批次 SQL 檔案時,您可以在單一翻譯工作中提供多個設定 YAML 檔案,以反映多項轉換。如果多項設定發生衝突,其中一項轉換可能會覆寫另一項。建議在每個檔案中使用不同類型的配置設定,避免在同一翻譯工作中發生轉換衝突。

以下範例列出為單一 SQL 轉譯工作提供的兩個獨立設定 YAML 檔案,一個用於變更資料欄屬性,另一個用於將資料表設為暫時:

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

使用這兩個設定 YAML 檔案的 SQL 轉譯可能如下所示:

teradata-input.sql
    create table testdb.testschema.x(a int);
    
bq-output.sql
    CREATE TEMPORARY TABLE x
    (
      a NUMERIC(31, 2)
    )
    ;