Trasforma le traduzioni SQL utilizzando i file YAML di configurazione

Questo documento mostra come utilizzare i file YAML di configurazione per trasformare il codice SQL durante la migrazione a BigQuery. Fornisce linee guida per creare i tuoi file YAML di configurazione e fornisce esempi per varie trasformazioni di traduzione supportate da questa funzionalità.

Quando utilizzi il traduttore SQL interattivo di BigQuery o esegui una traduzione SQL in batch, puoi fornire file YAML di configurazione per modificare una traduzione delle query SQL. L'uso dei file YAML di configurazione consente un'ulteriore personalizzazione durante la traduzione delle query SQL dal database di origine.

Puoi specificare un file YAML di configurazione da utilizzare in una traduzione SQL nei seguenti modi:

Il traduttore SQL interattivo, il traduttore SQL batch e il client Python per la traduzione batch supportano l'utilizzo di più file YAML di configurazione in un singolo job di traduzione. Consulta Applicazione di più configurazioni YAML per ulteriori informazioni.

Requisiti del file YAML di configurazione

Prima di creare un file YAML di configurazione, esamina le seguenti informazioni per assicurarti che il file YAML sia compatibile con BigQuery Migration Service:

  • Devi caricare i file YAML di configurazione nella directory principale del bucket Cloud Storage che contiene i file di input di traduzione SQL. Per informazioni su come creare bucket e caricare file in Cloud Storage, consulta Creare bucket e Caricare oggetti da un file system.
  • Le dimensioni di un singolo file YAML di configurazione non devono superare 1 MB.
  • Le dimensioni totali di tutti i file YAML di configurazione utilizzati in un singolo job di traduzione SQL non devono superare i 4 MB.
  • Se utilizzi la sintassi regex per la corrispondenza dei nomi, utilizza RE2/J.
  • Tutti i nomi dei file YAML di configurazione devono includere un'estensione .config.yaml, ad esempio change-case.config.yaml.
    • config.yaml da solo non è un nome valido per il file di configurazione.

Linee guida per creare un file YAML di configurazione

Questa sezione fornisce alcune linee guida generali per creare un file YAML di configurazione:

Ogni file di configurazione deve contenere un'intestazione che specifichi il tipo di configurazione. Il tipo object_rewriter viene utilizzato per specificare le traduzioni SQL in un file YAML di configurazione. L'esempio seguente utilizza il tipo object_rewriter per trasformare un nome in maiuscole e minuscole:

type: object_rewriter
global:
  case:
    all: UPPERCASE

Selezione entità

Per eseguire trasformazioni specifiche dell'entità, specifica l'entità nel file di configurazione. Tutte le proprietà match sono facoltative; utilizza solo le proprietà match necessarie per una trasformazione. Il seguente codice YAML di configurazione mostra le proprietà da abbinare per selezionare entità specifiche:

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

Descrizione di ogni proprietà match:

  • db: il componente project_id.
  • schema: il componente del set di dati.
  • relation: il componente della tabella.
  • attribute: il componente della colonna. Valido solo per la selezione dell'attributo
  • dbRegex: corrisponde a una proprietà db con un'espressione regolare (Anteprima).
  • schemaRegex: abbina le proprietà schema alle espressioni regolari (Anteprima).
  • relationRegex: corrisponde alle proprietà relation con le espressioni regolari (Anteprima).
  • attributeRegex: corrisponde a attribute proprietà con espressioni regolari. Valido solo per la selezione dell'attributo (Anteprima).

Ad esempio, il seguente codice YAML di configurazione specifica le proprietà match per selezionare la tabella testdb.acme.employee per la trasformazione di una tabella temporanea.

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

Puoi utilizzare le proprietà dbRegex, schemaRegex, relationRegex e attributeRegex per specificare le espressioni regolari al fine di selezionare un sottoinsieme di entità. L'esempio seguente modifica tutte le relazioni dallo schema tmp_schema in testdb a temporaneo, a condizione che il nome inizi con tmp_:

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

Le proprietà del valore letterale e regex vengono abbinate senza distinzione tra maiuscole e minuscole. Puoi applicare la corrispondenza sensibile alle maiuscole utilizzando un regex con un flag i disabilitato, come mostrato nell'esempio seguente:

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

Puoi anche specificare entità complete utilizzando una sintassi equivalente a corta corta. Una sintassi con stringhe corta prevede esattamente 3 (per la selezione di relazioni) o 4 (per la selezione di attributi) segmenti del nome delimitati da punti, come nell'esempio testdb.acme.employee. I segmenti vengono quindi interpretati internamente come se fossero stati passati rispettivamente come db, schema, relation e attribute. Ciò significa che i nomi vengono abbinati alla lettera, pertanto le espressioni regolari non sono consentite nella sintassi breve. L'esempio seguente mostra l'uso della sintassi corta stringhe per specificare un'entità completa in un file YAML di configurazione:

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

Se il nome di una tabella contiene un punto, non puoi specificarlo utilizzando una sintassi breve. In questo caso, devi utilizzare una corrispondenza dell'oggetto. L'esempio seguente modifica la tabella testdb.acme.stg.employee in temporanea:

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

Il codice YAML di configurazione accetta key come alias di match.

Database predefinito

Alcuni dialetti SQL di input, in particolare Teradata, non supportano database-name nel nome qualificato. In questo caso, il modo più semplice per creare una corrispondenza tra le entità è omettere la proprietà db in match.

Tuttavia, puoi impostare la proprietà default_database di BigQuery Migration Service e utilizzare il database predefinito in match.

Tipi di attributi target supportati

Puoi utilizzare il file YAML di configurazione per eseguire le trasformazioni dei tipi di attributi, in cui puoi trasformare il tipo di dati di una colonna da tipo di origine a tipo di destinazione. Il file YAML di configurazione supporta i seguenti tipi di destinazione:

  • BOOLEAN
  • TINYINT
  • SMALLINT
  • INTEGER
  • BIGINT
  • FLOAT
  • DOUBLE
  • NUMERIC (supporta precisione e scalabilità facoltative, come NUMERIC(18, 2))
  • TIME
  • TIMETZ
  • DATE
  • DATETIME
  • TIMESTAMP
  • TIMESTAMPTZ
  • CHAR (supporta la precisione facoltativa, ad esempio CHAR(42))
  • VARCHAR (supporta la precisione facoltativa, ad esempio VARCHAR(42))

Esempi YAML di configurazione

Questa sezione fornisce esempi per creare vari file YAML di configurazione da utilizzare con le traduzioni SQL. Ogni esempio illustra la sintassi YAML per trasformare la traduzione SQL in modi specifici, insieme a una breve descrizione. Ogni esempio fornisce anche i contenuti di un file teradata-input.sql o hive-input.sql e di un file bq-output.sql per consentirti di confrontare gli effetti di un YAML di configurazione su una traduzione di query SQL di BigQuery.

I seguenti esempi utilizzano Teradata o Hive come dialetto SQL di input e BigQuery SQL come dialetto di output. Anche i seguenti esempi utilizzano testdb come database predefinito e testschema come percorso di ricerca dello schema.

Modifica maiuscole/minuscole del nome dell'oggetto

La seguente configurazione YAML modifica le maiuscole o le minuscole dei nomi degli oggetti:

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

Una traduzione SQL con questo file YAML di configurazione potrebbe avere il seguente aspetto:

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
      ;

Rendi la tabella temporanea

La seguente configurazione YAML modifica una tabella normale in una tabella temporanea:

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

Una traduzione SQL con questo file YAML di configurazione potrebbe avere il seguente aspetto:

teradata-input.sql 

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

    CREATE TEMPORARY TABLE x
    (
      a INT64
    )
    ;

Rendi la tabella temporanea

La configurazione YAML seguente modifica una tabella normale in una tabella temporanea con una scadenza di 60 secondi.

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

Una traduzione SQL con questo file YAML di configurazione potrebbe avere il seguente aspetto:

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

Imposta scadenza partizione

La seguente configurazione YAML modifica la scadenza di una tabella partizionata a 1 giorno:

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

Una traduzione SQL con questo file YAML di configurazione potrebbe avere il seguente aspetto:

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

Modificare la località o il formato esterno per una tabella

La seguente configurazione YAML modifica la posizione e la struttura esterne di una tabella:

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

Una traduzione SQL con questo file YAML di configurazione potrebbe avere il seguente aspetto:

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

Imposta o modifica la descrizione della tabella

Il seguente codice YAML di configurazione imposta la descrizione di una tabella:

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

Una traduzione SQL con questo file YAML di configurazione potrebbe avere il seguente aspetto:

teradata-input.sql 

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

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

Impostare o modificare il partizionamento delle tabelle

La seguente configurazione YAML modifica lo schema di partizionamento di una tabella:

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

Una traduzione SQL con questo file YAML di configurazione potrebbe avere il seguente aspetto:

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

Impostare o modificare il clustering delle tabelle

Il seguente codice YAML di configurazione modifica lo schema di clustering di una tabella:

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

Una traduzione SQL con questo file YAML di configurazione potrebbe avere il seguente aspetto:

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

Modificare il tipo di un attributo della colonna

La seguente configurazione YAML modifica il tipo di dati per un attributo di una colonna:

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

Puoi trasformare il tipo di dati di origine in uno qualsiasi dei tipi di attributi target supportati.

Una traduzione SQL con questo file YAML di configurazione potrebbe avere il seguente aspetto:

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

Aggiungi una connessione a un data lake esterno

La seguente configurazione YAML contrassegna la tabella di origine come una tabella esterna che punta ai dati archiviati in un data lake esterno, specificato da una connessione al data lake.

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

Una traduzione SQL con questo file YAML di configurazione potrebbe avere il seguente aspetto:

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

Modifica della codifica dei caratteri di un file di input

Per impostazione predefinita, BigQuery Migration Service tenta di rilevare automaticamente la codifica dei caratteri dei file di input. Nei casi in cui BigQuery Migration Service possa identificare in modo errato la codifica di un file, puoi utilizzare un codice YAML di configurazione per specificare esplicitamente la codifica dei caratteri.

La seguente configurazione YAML specifica la codifica esplicita dei caratteri del file di input come ISO-8859-1.

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

Conversione del tipo globale

La seguente configurazione YAML cambia un tipo di dati in un altro in tutti gli script e specifica un tipo di dati di origine da evitare nello script con transpile. È diversa dalla configurazione Tipo di modifica di un attributo di colonna, in cui viene modificato solo il tipo di dati per un singolo attributo.

BigQuery supporta le seguenti conversioni dei tipi di dati:

  • Da DATETIME a TIMESTAMP
  • Da TIMESTAMP a DATETIME
  • Da TIMESTAMP WITH TIME ZONE a DATETIME
  • Da CHAR a VARCHAR

Nell'esempio seguente, il file YAML di configurazione converte un tipo di dati DATETIME in TIMESTAMP.

type: experimental_object_rewriter
global:
  typeConvert:
    timestamp: DATETIME

Una traduzione SQL con questo file YAML di configurazione potrebbe avere il seguente aspetto:

teradata-input.sql 

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

      CREATE TABLE x
      (
        a TIMESTAMP
      )
      ;

Seleziona la modifica dell'istruzione

Il seguente codice YAML di configurazione modifica le clausole di proiezione a stella, GROUP BY e ORDER BY nelle istruzioni SELECT.

starProjection supporta le seguenti configurazioni:

  • ALLOW
  • PRESERVE (valore predefinito)
  • EXPAND

groupBy e orderBy supportano le seguenti configurazioni:

  • EXPRESSION
  • ALIAS
  • INDEX

Nell'esempio seguente, il codice YAML di configurazione configura la proiezione a stella su EXPAND.

type: experimental_statement_rewriter
select:
  starProjection: EXPAND

Una traduzione SQL con questo file YAML di configurazione potrebbe avere il seguente aspetto:

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
      ;

Specifica delle funzioni definite dall'utente

La seguente configurazione YAML specifica la firma delle funzioni definite dall'utente (UDF) utilizzate negli script di origine. Proprio come accade per i file ZIP di metadati, le definizioni delle funzioni definite dall'utente possono aiutare a produrre una traduzione più accurata degli script di input.

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

Una traduzione SQL con questo file YAML di configurazione potrebbe avere il seguente aspetto:

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
      ;

Impostazione della severità di precisione decimale

Per impostazione predefinita, BigQuery Migration Service aumenta la precisione numerica fino alla massima precisione disponibile per una determinata scala. La seguente configurazione YAML sostituisce questo comportamento configurando la severità di precisione in modo da mantenere la precisione decimale dell'istruzione di origine.

type: experimental_statement_rewriter
common:
  decimalPrecision: STRICT

Una traduzione SQL con questo file YAML di configurazione potrebbe avere il seguente aspetto:

teradata-input.sql 

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

      CREATE TABLE x
      (
        a NUMERIC(3)
      )
      ;

Mapping dei nomi di output

Puoi utilizzare YAML di configurazione per mappare i nomi degli oggetti SQL. Puoi modificare diverse parti del nome a seconda dell'oggetto che stai mappando.

Mappatura dei nomi statica

Utilizza la mappatura dei nomi statica per mappare il nome di un'entità. Se vuoi modificare solo parti specifiche del nome e mantenere uguali le altre parti del nome, includi solo le parti che devono essere modificate.

Il seguente codice YAML di configurazione cambia il nome della tabella da my_db.my_schema.my_table a 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"

Una traduzione SQL con questo file YAML di configurazione potrebbe avere il seguente aspetto:

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
      )

Mappatura dinamica dei nomi

Utilizza la mappatura dei nomi dinamica per modificare più oggetti contemporaneamente e creare nuovi nomi in base agli oggetti mappati.

La seguente configurazione YAML modifica il nome di tutte le tabelle aggiungendo il prefisso stg_ a quelle che appartengono allo schema staging, quindi sposta queste tabelle nello schema production.

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

Una traduzione SQL con questo file YAML di configurazione potrebbe avere il seguente aspetto:

teradata-input.sql 

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

      CREATE TABLE production.stg_my_table
      (
        a INT64
      )
      ;

Specifica del percorso di ricerca predefinito di database e schemi

La seguente configurazione YAML specifica un database predefinito e un percorso di ricerca schema.

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

Una traduzione SQL con questo file YAML di configurazione potrebbe avere il seguente aspetto:

teradata-input.sql 

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

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

Riscrittura nome output globale

Il seguente codice YAML di configurazione modifica i nomi di output di tutti gli oggetti (database, schema, relazione e attributi) nello script in base alle regole configurate.

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

Una traduzione SQL con questo file YAML di configurazione potrebbe avere il seguente aspetto:

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

Applicazione di più configurazioni YAML

Quando specifichi un file YAML di configurazione in una traduzione SQL in batch o interattiva, puoi selezionare più file YAML di configurazione in un singolo job di traduzione per riflettere più trasformazioni. In caso di conflitto tra più configurazioni, una trasformazione potrebbe sostituire un'altra. Consigliamo di utilizzare tipi diversi di impostazioni di configurazione in ogni file per evitare conflitti tra trasformazioni nello stesso job di traduzione.

Nell'esempio seguente sono elencati due file YAML di configurazione separati forniti per un singolo job di traduzione SQL, uno per modificare l'attributo di una colonna e l'altro per impostare la tabella come temporanea:

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

Una traduzione SQL con questi due file YAML di configurazione potrebbe avere il seguente aspetto:

teradata-input.sql 

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

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