Mentransformasi terjemahan SQL menggunakan file YAML konfigurasi

Dokumen ini menunjukkan cara menggunakan file YAML konfigurasi untuk mentransformasi kode SQL saat memigrasikannya ke BigQuery. Halaman ini memberikan panduan untuk membuat file YAML konfigurasi Anda sendiri, dan memberikan contoh untuk berbagai transformasi terjemahan yang didukung oleh fitur ini.

Saat menggunakan penerjemah SQL interaktif BigQuery atau melakukan terjemahan SQL batch, Anda dapat memberikan file YAML konfigurasi untuk mengubah terjemahan kueri SQL. Penggunaan file YAML konfigurasi memungkinkan penyesuaian lebih lanjut saat menerjemahkan kueri SQL dari database sumber.

Anda dapat menentukan file YAML konfigurasi untuk digunakan dalam terjemahan SQL dengan cara berikut:

Penerjemah SQL interaktif, penerjemah SQL batch, dan klien Python terjemahan batch mendukung penggunaan beberapa file YAML konfigurasi dalam satu tugas terjemahan. Lihat Menerapkan beberapa konfigurasi YAML untuk informasi selengkapnya.

Persyaratan file YAML konfigurasi

Sebelum membuat file YAML konfigurasi, tinjau informasi berikut untuk memastikan bahwa file YAML kompatibel untuk digunakan dengan BigQuery Migration Service:

  • Anda harus mengupload file YAML konfigurasi ke direktori utama bucket Cloud Storage yang berisi file input terjemahan SQL. Untuk informasi tentang cara membuat bucket dan mengupload file ke Cloud Storage, lihat Membuat bucket dan Mengupload objek dari sistem file.
  • Ukuran file untuk satu file YAML konfigurasi tidak boleh melebihi 1 MB.
  • Ukuran total file semua file YAML konfigurasi yang digunakan dalam satu tugas terjemahan SQL tidak boleh melebihi 4 MB.
  • Jika Anda menggunakan sintaksis regex untuk pencocokan nama, gunakan RE2/J.
  • Semua nama file YAML konfigurasi harus menyertakan ekstensi .config.yaml—misalnya, change-case.config.yaml.
    • config.yaml saja bukan nama yang valid untuk file konfigurasi.

Panduan untuk membuat file YAML konfigurasi

Bagian ini memberikan beberapa panduan umum untuk membuat file YAML konfigurasi:

Setiap file konfigurasi harus berisi header yang menentukan jenis konfigurasi. Jenis object_rewriter digunakan untuk menentukan terjemahan SQL dalam file YAML konfigurasi. Contoh berikut menggunakan jenis object_rewriter untuk mengubah kapitalisasi nama:

type: object_rewriter
global:
  case:
    all: UPPERCASE

Pemilihan entity

Untuk melakukan transformasi khusus entity, tentukan entity di file konfigurasi. Semua properti match bersifat opsional; hanya gunakan properti match yang diperlukan untuk transformasi. YAML konfigurasi berikut mengekspos properti yang akan dicocokkan untuk memilih entity tertentu:

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

Deskripsi setiap properti match:

  • db: komponen project_id.
  • schema: komponen set data.
  • relation: komponen tabel.
  • attribute: komponen kolom. Hanya valid untuk pemilihan atribut
  • dbRegex: mencocokkan properti db dengan ekspresi reguler (Pratinjau).
  • schemaRegex: mencocokkan properti schema dengan ekspresi reguler (Pratinjau).
  • relationRegex: mencocokkan properti relation dengan ekspresi reguler (Pratinjau).
  • attributeRegex: cocok dengan properti attribute dengan ekspresi reguler. Hanya valid untuk pemilihan atribut (Pratinjau).

Misalnya, YAML konfigurasi berikut menentukan properti match guna memilih tabel testdb.acme.employee untuk transformasi tabel sementara.

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

Anda dapat menggunakan properti dbRegex, schemaRegex, relationRegex, dan attributeRegex untuk menentukan ekspresi reguler agar dapat memilih subset entity. Contoh berikut mengubah semua relasi dari skema tmp_schema di testdb menjadi sementara, selama namanya dimulai dengan tmp_:

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

Properti literal dan regex dicocokkan tanpa peka huruf besar/kecil. Anda dapat menerapkan pencocokan peka huruf besar/kecil menggunakan regex dengan flag i yang dinonaktifkan, seperti yang terlihat dalam contoh berikut:

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

Anda juga dapat menentukan entity yang sepenuhnya memenuhi syarat menggunakan sintaksis string pendek yang setara. Sintaksis string pendek mengharapkan tepat 3 segmen nama (untuk pemilihan relasi) atau 4 (untuk pemilihan atribut) yang dipisahkan dengan titik, sebagai contoh testdb.acme.employee. Segmen ini kemudian ditafsirkan secara internal seolah-olah diteruskan sebagai db, schema, relation, dan attribute. Artinya, nama dicocokkan secara harfiah, sehingga ekspresi reguler tidak diizinkan dalam sintaksis singkat. Contoh berikut menunjukkan penggunaan sintaksis string pendek untuk menentukan entity yang sepenuhnya memenuhi syarat dalam file YAML konfigurasi:

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

Jika tabel berisi titik dalam nama tabel, Anda tidak dapat menentukan nama tersebut menggunakan sintaksis singkat. Dalam hal ini, Anda harus menggunakan pencocokan objek. Contoh berikut mengubah tabel testdb.acme.stg.employee menjadi sementara:

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

YAML konfigurasi menerima key sebagai alias untuk match.

Database bawaan

Beberapa dialek SQL input, terutama Teradata, tidak mendukung database-name dalam nama yang memenuhi syarat. Dalam hal ini, cara termudah untuk mencocokkan entity adalah dengan menghilangkan properti db di match.

Namun, Anda dapat menetapkan properti default_database dari BigQuery Migration Service dan menggunakan database default tersebut di match.

Jenis atribut target yang didukung

Anda dapat menggunakan file YAML konfigurasi untuk melakukan transformasi jenis atribut, yaitu ketika Anda mentransformasi jenis data kolom dari jenis sumber ke jenis target. File YAML konfigurasi mendukung jenis target berikut:

  • BOOLEAN
  • TINYINT
  • SMALLINT
  • INTEGER
  • BIGINT
  • FLOAT
  • DOUBLE
  • NUMERIC (Mendukung presisi dan skala opsional, seperti NUMERIC(18, 2))
  • TIME
  • TIMETZ
  • DATE
  • DATETIME
  • TIMESTAMP
  • TIMESTAMPTZ
  • CHAR (Mendukung presisi opsional, seperti CHAR(42))
  • VARCHAR (Mendukung presisi opsional, seperti VARCHAR(42))

Contoh YAML konfigurasi

Bagian ini menyediakan contoh untuk membuat berbagai file YAML konfigurasi untuk digunakan dengan terjemahan SQL. Setiap contoh menguraikan sintaks YAML untuk mentransformasi terjemahan SQL Anda dengan cara tertentu, beserta deskripsi singkat. Setiap contoh juga menyediakan konten file teradata-input.sql atau hive-input.sql dan file bq-output.sql, sehingga Anda dapat membandingkan efek YAML konfigurasi pada terjemahan kueri SQL BigQuery.

Contoh berikut menggunakan Teradata atau Hive sebagai dialek SQL input dan BigQuery SQL sebagai dialek output. Contoh berikut juga menggunakan testdb sebagai database default, dan testschema sebagai jalur penelusuran skema.

Mengubah kapitalisasi nama objek

YAML konfigurasi berikut mengubah huruf besar atau kecil pada nama objek:

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

Terjemahan SQL dengan file YAML konfigurasi ini mungkin terlihat seperti berikut:

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
      ;

Membuat tabel menjadi sementara

YAML konfigurasi berikut mengubah tabel reguler menjadi tabel sementara:

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

Terjemahan SQL dengan file YAML konfigurasi ini mungkin terlihat seperti berikut:

teradata-input.sql 

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

    CREATE TEMPORARY TABLE x
    (
      a INT64
    )
    ;

Membuat tabel menjadi efemeral

YAML konfigurasi berikut mengubah tabel reguler menjadi tabel efemeral dengan masa berlaku 60 detik.

type: object_rewriter
relation:
  -
    match: "testdb.testschema.x"
    ephemeral:
      expireAfterSeconds: 60

Terjemahan SQL dengan file YAML konfigurasi ini mungkin terlihat seperti berikut:

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

Menetapkan akhir masa berlaku partisi

YAML konfigurasi berikut mengubah akhir masa berlaku tabel berpartisi menjadi 1 hari:

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

Terjemahan SQL dengan file YAML konfigurasi ini mungkin terlihat seperti berikut:

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

Mengubah lokasi atau format eksternal untuk tabel

YAML konfigurasi berikut mengubah lokasi dan formasi eksternal untuk tabel:

type: object_rewriter
relation:
  -
    match: "testdb.testschema.x"
    external:
      locations: "gs://path/to/department/files"
      format: ORC

Terjemahan SQL dengan file YAML konfigurasi ini mungkin terlihat seperti berikut:

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

Menetapkan atau mengubah deskripsi tabel

YAML konfigurasi berikut menetapkan deskripsi tabel:

type: object_rewriter
relation:
  -
    match: "testdb.testschema.x"
    description:
      text: "Example description."

Terjemahan SQL dengan file YAML konfigurasi ini mungkin terlihat seperti berikut:

teradata-input.sql 

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

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

Menetapkan atau mengubah partisi tabel

YAML konfigurasi berikut mengubah skema partisi tabel:

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

Terjemahan SQL dengan file YAML konfigurasi ini mungkin terlihat seperti berikut:

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

Menetapkan atau mengubah pengelompokan tabel

YAML konfigurasi berikut mengubah skema pengelompokan tabel:

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

Terjemahan SQL dengan file YAML konfigurasi ini mungkin terlihat seperti berikut:

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

Mengubah jenis atribut kolom

YAML konfigurasi berikut mengubah jenis data untuk atribut kolom:

type: object_rewriter
attribute:
  -
    match:
      db: testdb
      schema: testschema
      attributeRegex: "a+"
    type:
      target: NUMERIC(10,2)

Anda dapat mengubah jenis data sumber ke salah satu jenis atribut target yang didukung.

Terjemahan SQL dengan file YAML konfigurasi ini mungkin terlihat seperti berikut:

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

Menambahkan koneksi ke data lake eksternal

YAML konfigurasi berikut menandai tabel sumber sebagai tabel eksternal yang mengarah ke data yang disimpan di data lake eksternal, yang ditentukan oleh koneksi danau data.

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

Terjemahan SQL dengan file YAML konfigurasi ini mungkin terlihat seperti berikut:

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

Mengubah encoding karakter pada file input

Secara default, BigQuery Migration Service mencoba mendeteksi encoding karakter file input secara otomatis. Jika BigQuery Migration Service mungkin salah mengidentifikasi encoding file, Anda dapat menggunakan YAML konfigurasi untuk menentukan encoding karakter secara eksplisit.

YAML konfigurasi berikut menentukan encoding karakter eksplisit file input sebagai ISO-8859-1.

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

Konversi jenis global

YAML konfigurasi berikut mengubah jenis data ke jenis data lain di semua skrip, dan menentukan jenis data sumber yang harus dihindari dalam skrip yang ditranspilasi. Ini berbeda dengan konfigurasi Change type of a column attribute, yang hanya mengubah jenis data untuk satu atribut.

BigQuery mendukung konversi jenis data berikut:

  • DATETIME ke TIMESTAMP
  • TIMESTAMP ke DATETIME
  • TIMESTAMP WITH TIME ZONE ke DATETIME
  • CHAR ke VARCHAR

Pada contoh berikut, YAML konfigurasi mengonversi jenis data DATETIME menjadi TIMESTAMP.

type: experimental_object_rewriter
global:
  typeConvert:
    timestamp: DATETIME

Terjemahan SQL dengan file YAML konfigurasi ini mungkin terlihat seperti berikut:

teradata-input.sql 

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

      CREATE TABLE x
      (
        a TIMESTAMP
      )
      ;

Pilih perubahan pernyataan

YAML konfigurasi berikut mengubah klausa proyeksi bintang, GROUP BY, dan ORDER BY dalam pernyataan SELECT.

starProjection mendukung konfigurasi berikut:

  • ALLOW
  • PRESERVE (default)
  • EXPAND

groupBy dan orderBy mendukung konfigurasi berikut:

  • EXPRESSION
  • ALIAS
  • INDEX

Dalam contoh berikut, YAML konfigurasi mengonfigurasi proyeksi bintang ke EXPAND.

type: experimental_statement_rewriter
select:
  starProjection: EXPAND

Terjemahan SQL dengan file YAML konfigurasi ini mungkin terlihat seperti berikut:

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
      ;

Spesifikasi UDF

YAML konfigurasi berikut menetapkan tanda tangan fungsi yang ditentukan pengguna (UDF) yang digunakan dalam skrip sumber. Mirip dengan file zip metadata, definisi UDF dapat membantu menghasilkan terjemahan skrip input yang lebih akurat.

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

Terjemahan SQL dengan file YAML konfigurasi ini mungkin terlihat seperti berikut:

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
      ;

Menetapkan pengetatan presisi desimal

Secara default, BigQuery Migration Service meningkatkan presisi numerik hingga presisi tertinggi yang tersedia untuk skala tertentu. YAML konfigurasi berikut mengganti perilaku ini dengan mengonfigurasi pengetatan presisi untuk mempertahankan presisi desimal dari pernyataan sumber.

type: experimental_statement_rewriter
common:
  decimalPrecision: STRICT

Terjemahan SQL dengan file YAML konfigurasi ini mungkin terlihat seperti berikut:

teradata-input.sql 

      create table x(a decimal(3,0));
bq-output.sql 

      CREATE TABLE x
      (
        a NUMERIC(3)
      )
      ;

Pemetaan nama output

Anda dapat menggunakan konfigurasi YAML untuk memetakan nama objek SQL. Anda dapat mengubah bagian nama yang berbeda, bergantung pada objek yang dipetakan.

Pemetaan nama statis

Gunakan pemetaan nama statis untuk memetakan nama entity. Jika Anda hanya ingin mengubah bagian nama tertentu dan mempertahankan bagian nama lainnya agar tetap sama, sertakan hanya bagian yang perlu diubah.

YAML konfigurasi berikut mengubah nama tabel dari my_db.my_schema.my_table menjadi 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"

Terjemahan SQL dengan file YAML konfigurasi ini mungkin terlihat seperti berikut:

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
      )

Pemetaan nama dinamis

Gunakan pemetaan nama dinamis untuk mengubah beberapa objek sekaligus, dan buat nama baru berdasarkan objek yang dipetakan.

YAML konfigurasi berikut mengubah nama semua tabel dengan menambahkan awalan stg_ ke nama yang termasuk dalam skema staging, lalu memindahkan tabel tersebut ke skema production.

type: experimental_object_rewriter
relation:
-
  match:
    schema: staging
  outputName:
    schema: production
    relation: "stg_${relation}"

Terjemahan SQL dengan file YAML konfigurasi ini mungkin terlihat seperti berikut:

teradata-input.sql 

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

      CREATE TABLE production.stg_my_table
      (
        a INT64
      )
      ;

Menentukan database default dan jalur penelusuran skema

YAML konfigurasi berikut menetapkan database default dan jalur penelusuran skema.

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

Terjemahan SQL dengan file YAML konfigurasi ini mungkin terlihat seperti berikut:

teradata-input.sql 

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

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

Penulisan ulang nama output global

YAML konfigurasi berikut mengubah nama output semua objek (database, skema, relasi, dan atribut) dalam skrip sesuai dengan aturan yang dikonfigurasi.

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

Terjemahan SQL dengan file YAML konfigurasi ini mungkin terlihat seperti berikut:

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

Menerapkan beberapa konfigurasi YAML

Saat menentukan file YAML konfigurasi dalam batch atau terjemahan SQL interaktif, Anda dapat memilih beberapa file YAML konfigurasi dalam satu tugas terjemahan untuk mencerminkan beberapa transformasi. Jika beberapa konfigurasi bertentangan, satu transformasi mungkin menggantikan transformasi lainnya. Sebaiknya gunakan berbagai jenis setelan konfigurasi di setiap file untuk menghindari transformasi yang bertentangan dalam tugas terjemahan yang sama.

Contoh berikut mencantumkan dua file YAML konfigurasi terpisah yang disediakan untuk satu tugas terjemahan SQL, satu untuk mengubah atribut kolom, dan satu lagi untuk menetapkan tabel sebagai sementara:

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

Terjemahan SQL dengan dua file YAML konfigurasi ini mungkin terlihat seperti berikut:

teradata-input.sql 

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

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