YAML 構成ファイルを使用して SQL 変換を行う

このドキュメントでは、YAML 構成ファイルを使用して、SQL コードを BigQuery に移行する際に変換する方法について説明します。また、独自の YAML 構成ファイルを作成するためのガイドラインと、この機能でサポートされているさまざまな変換の例も示します。

BigQuery のインタラクティブ SQL トランスレータを使用するか、バッチ SQL 変換を実行する場合、SQL クエリ変換を変更するための YAML 構成ファイルを指定できます。YAML 構成ファイルを使用すると、ソース データベースから SQL クエリを変換するときにさらにカスタマイズできます。

SQL 変換で使用する YAML 構成ファイルは、次の方法で指定できます。

インタラクティブ SQL トランスレータ、バッチ SQL トランスレータ、バッチ変換 Python クライアントは、1 つの変換ジョブで複数の YAML 構成ファイルを使用できます。詳細については、複数の YAML 構成の適用をご覧ください。

YAML 構成ファイルの要件

YAML 構成ファイルを作成する前に、YAML ファイルに BigQuery Migration Service で使用する互換性があることを確認してください。

  • YAML 構成ファイルは、SQL 変換入力ファイルを含む Cloud Storage バケットのルート ディレクトリにアップロードする必要があります。バケットを作成してファイルを Cloud Storage にアップロードする方法については、バケットを作成するとファイル システムからオブジェクトをアップロードするをご覧ください。
  • 1 つの YAML 構成ファイルのファイルサイズは、1 MB を超えないようにしてください。
  • 1 つの SQL 変換ジョブで使用されるすべての YAML 構成ファイルの合計ファイルサイズは、4 MB を超えないようにしてください。
  • 名前の照合に regex 構文を使用する場合は、RE2/J を使用します。
  • すべての YAML 構成ファイル名には、.config.yaml 拡張子が必要です（例: change-case.config.yaml）。
    • config.yaml のみは構成ファイルの有効な名前ではありません。

YAML 構成ファイルを作成するためのガイドライン

このセクションでは、YAML 構成ファイルを作成するための一般的なガイドラインを示します。

各構成ファイルには、構成のタイプを指定するヘッダーを含める必要があります。object_rewriter タイプは、YAML 構成ファイルで SQL 変換を指定するために使用されます。次の例では、object_rewriter タイプを使用して名前の大文字 / 小文字を変換しています。

type: object_rewriter
global:
  case:
    all: UPPERCASE

エンティティの選択

エンティティ固有の変換を実行するには、構成ファイルでエンティティを指定します。match プロパティはすべて省略可能で、変換に必要な match プロパティのみを使用します。次の構成 YAML は、特定のエンティティを選択するために照合するプロパティを公開します。

match:
  db: <literal_name>
  schema: <literal_name>
  relation: <literal_name>
  attribute: <literal_name>
  dbRegex: <regex>
  schemaRegex: <regex>
  relationRegex: <regex>
  attributeRegex: <regex>

各 match プロパティの説明:

  • db: project_id コンポーネント。
  • schema: データセット コンポーネント。
  • relation: テーブル コンポーネント。
  • attribute: 列コンポーネント。属性の選択でのみ有効
  • dbRegex: db プロパティと正規表現を照合します（プレビュー）。
  • schemaRegex: schema プロパティと正規表現を照合します（プレビュー）。
  • relationRegex: relation プロパティと正規表現を照合します（プレビュー）。
  • attributeRegex: attribute プロパティと正規表現を照合します。属性の選択でのみ有効です（プレビュー）。

たとえば、次の構成 YAML では、一時テーブル変換に testdb.acme.employee テーブルを選択する match プロパティが指定されています。

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

エンティティのサブセットを選択するには、dbRegex、schemaRegex、relationRegex、attributeRegex の各プロパティを使用して正規表現を指定します。次の例では、名前が tmp_ で始まる限り、testdb の tmp_schema スキーマのすべてのリレーションを一時的なリレーションに変更します。

type: object_rewriter
relation:
-
  match:
    schema: tmp_schema
    relationRegex: "tmp_.*"
  temporary: true

リテラル プロパティと regex プロパティは、どちらも大文字と小文字を区別せずに照合されます。大文字と小文字を区別する一致を適用するには、次の例に示すように、無効になっている i フラグを指定して regex を使用します。

match:
  relationRegex: "(?-i:<actual_regex>)"

同等の短い文字列の構文を使用して、完全修飾されたエンティティを指定することもできます。短い文字列の構文は、ドットで区切られた 3 個（リレーション選択用）または 4 個（属性選択用）の名前セグメントを想定します（例: testdb.acme.employee）。その後、セグメントは、それぞれ db、schema、relation、attribute として渡されたかのように、内部的に解釈されます。つまり、名前は文字どおりに照合されるため、短い構文では正規表現を使用できません。次の例は、短い文字列の構文を使用して、YAML 構成ファイルで完全修飾エンティティを指定する方法を示しています。

type: object_rewriter
relation:
-
  match : "testdb.acme.employee"
  temporary: true

テーブル名にドットが含まれている場合、短い構文で名前を指定することはできません。この場合は、オブジェクト一致を使用する必要があります。次の例では、testdb.acme.stg.employee テーブルを一時テーブルに変更します。

type: object_rewriter
relation:
-
  match:
    db: testdb
    schema: acme
    relation: stg.employee
  temporary: true

構成 YAML は、match のエイリアスとして key を受け入れます。

デフォルトのデータベース

一部の入力 SQL 言語（特に Teradata など）では、修飾名での database-name がサポートされていません。この場合、エンティティを照合する最も簡単な方法は、match の db プロパティを省略することです。

ただし、BigQuery Migration Service の default_database プロパティを設定すると、そのデフォルト データベースを 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 の例

このセクションでは、SQL 変換で使用するさまざまな YAML 構成ファイルを作成する例を示します。各例では、具体的な方法で SQL 変換を行うための YAML 構文の概要と簡単な説明を示します。それぞれの例では、teradata-input.sql ファイルまたは hive-input.sql ファイルと bq-output.sql ファイルの内容が提供されるため、構成 YAML が BigQuery SQL クエリ変換に与える影響を比較できます。

次の例では、入力 SQL 言語として Teradata または Hive を使用し、出力言語として BigQuery SQL を使用しています。次の例では、デフォルトのデータベースとして testdb を使用し、スキーマ検索パスとして testschema を使用しています。

オブジェクト名の大文字と小文字を変更する

次の構成 YAML では、オブジェクト名の大文字と小文字が変更されます。

type: object_rewriter
global:
  case:
    all: UPPERCASE
    database: LOWERCASE
    attribute: LOWERCASE

この YAML 構成ファイルを使用した SQL 変換は次のようになります。

teradata-input.sql 

      create table x(a int);
      select * from x;
bq-output.sql 

      CREATE TABLE testdb.TESTSCHEMA.X
      (
        a INT64
      )
      ;
      SELECT
          X.a
        FROM
          testdb.TESTSCHEMA.X
      ;

一時テーブルに変更する

次の構成 YAML は、通常のテーブルを一時テーブルに変更します。

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

この YAML 構成ファイルを使用した SQL 変換は次のようになります。

teradata-input.sql 

    create table 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 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 x(a int, b int) partition by (a);
bq-output.sql 

    CREATE TABLE testdb.testschema.x
    (
      a INT64,
      b INT64
    )
    CLUSTER 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 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 x(a int);
bq-output.sql 

    CREATE TABLE testdb.testschema.x
    (
      a INT64
    )
    OPTIONS(
      description='Example description.'
    );

テーブルのパーティショニングを設定または変更する

次の構成 YAML は、テーブルのパーティショニング スキームを変更します。

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 x(a date, b int);
    create table 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 は、テーブルのクラスタリング スキームを変更します。

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 x(a int, b int);
    create table 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:
      db: testdb
      schema: testschema
      attributeRegex: "a+"
    type:
      target: NUMERIC(10,2)

ソースのデータ型は、サポートされているターゲット属性のタイプのいずれかに変換できます。

この YAML 構成ファイルを使用した SQL 変換は次のようになります。

teradata-input.sql 

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

外部データレイクへの接続を追加する

次の構成 YAML は、データレイク接続で指定された外部データレイクに格納されたデータを指す外部テーブルとして、ソーステーブルをマークします。

type: object_rewriter
relation:
-
  key: "testdb.acme.employee"
  external:
    connection_id: "connection_test"

この YAML 構成ファイルを使用した SQL 変換は次のようになります。

hive-input.sql 

    CREATE TABLE x
    (
      a VARCHAR(150),
      b INT
    );
bq-output.sql 

    CREATE EXTERNAL TABLE x
    (
      a STRING,
      b INT64
    )
    WITH CONNECTION `connection_test`
    OPTIONS(
    );

入力ファイルの文字エンコードを変更する

デフォルトでは、BigQuery Migration Service は入力ファイルの文字エンコードを自動的に検出しようとします。BigQuery Migration Service がファイルのエンコードを誤って識別する可能性がある場合は、構成 YAML を使用して文字エンコードを明示的に指定できます。

次の構成 YAML では、入力ファイルの明示的な文字エンコードを ISO-8859-1 として指定しています。

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

グローバル型変換

次の構成 YAML は、すべてのスクリプトでデータ型を別のデータ型に変更し、トランスパイルされたスクリプトで回避するソースデータ型を指定します。これは、1 つの属性のデータ型のみが変更される列属性のタイプの変更構成とは異なります。

BigQuery は、次のデータ型変換をサポートしています。

  • DATETIME から TIMESTAMP
  • TIMESTAMP から DATETIME
  • TIMESTAMP WITH TIME ZONE から DATETIME
  • CHAR から VARCHAR

次の例では、構成 YAML によって DATETIME データ型が TIMESTAMP に変換されます。

type: experimental_object_rewriter
global:
  typeConvert:
    timestamp: DATETIME

この YAML 構成ファイルを使用した SQL 変換は次のようになります。

teradata-input.sql 

      create table x(a timestamp);
bq-output.sql 

      CREATE TABLE x
      (
        a TIMESTAMP
      )
      ;

ステートメントの変更を選択する

次の構成 YAML は、SELECT ステートメントのスター投影、GROUP BY、ORDER BY 句を変更します。

starProjection は、次の構成をサポートしています。

  • ALLOW
  • PRESERVE（デフォルト）
  • EXPAND

groupBy と orderBy は、次の構成をサポートしています。

  • EXPRESSION
  • ALIAS
  • INDEX

次の例では、構成 YAML によってスター投影が EXPAND に構成されます。

type: experimental_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 DATETIME
      )
      ;
      SELECT
          x.a
          x.b
        FROM
          x
      ;

UDF の仕様

次の構成 YAML は、ソース スクリプトで使用されるユーザー定義関数（UDF）のシグネチャを指定します。メタデータ 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(x.dt), interval 1 DAY)
        FROM
          x
      ;

小数精度の厳格性の設定

デフォルトでは、BigQuery Migration Service は数値精度を特定のスケールで利用可能な最高精度まで向上させます。次の構成 YAML は、ソース ステートメントの小数精度を保持するように精度の厳格性を構成することで、この動作をオーバーライドします。

type: experimental_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)
      )
      ;

出力名のマッピング

構成 YAML を使用して 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
      )

動的名前マッピング

動的名前マッピングを使用すると、複数のオブジェクトを同時に変更し、マッピングされたオブジェクトに基づいて新しい名前を作成できます。

次の構成 YAML は、staging スキーマに属するものに接頭辞 stg_ を追加してすべてのテーブルの名前を変更し、それらのテーブルを 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
      )
      ;

デフォルトのデータベースとスキーマの検索パスの指定

次の構成 YAML は、デフォルトのデータベースとスキーマ検索パスを指定しています。

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

この YAML 構成ファイルを使用した SQL 変換は次のようになります。

teradata-input.sql 

      SELECT * FROM database.table
      SELECT * FROM table1
bq-output.sql 

      SELECT * FROM myproject.database.table.
      SELECT * FROM myproject.myschema1.table1

グローバル出力名の書き換え

次の構成 YAML は、構成されたルールに従って、スクリプト内のすべてのオブジェクト（データベース、スキーマ、リレーション、属性）の出力名を変更します。

type: experimental_object_rewriter
global:
  outputName:
    regex:
      - match: '\s'
        replaceWith: '_'
      - match: '>='
        replaceWith: 'gte'
      - match: '^[^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_employees_gte_12
      (
        _42eid INT64,
        custom_column STRING
      )
      ;

複数の YAML 構成の適用

バッチまたはインタラクティブ SQL 変換で YAML 構成ファイルを指定する場合、1 つの変換ジョブで複数の YAML 構成ファイルを選択して、複数の変換を反映できます。複数の構成が競合する場合、ある変換が別の変換をオーバーライドすることがあります。同じ変換ジョブ内の変換の競合を避けるため、各ファイルで異なるタイプの構成設定を使用することをおすすめします。

次の例では、1 つの SQL 変換ジョブに指定された 2 つの個別の 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

この 2 つの YAML 構成ファイルを使用した SQL 変換は次のようになります。

teradata-input.sql 

    create table x(a int);
bq-output.sql 

    CREATE TEMPORARY TABLE x
    (
      a NUMERIC(31, 2)
    )
    ;