Trasforma le traduzioni SQL con YAML

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

Quando utilizzi il traduttore SQL interattivo di BigQuery, l'API BigQuery Migration o esegui una traduzione SQL batch, puoi fornire file YAML di configurazione per modificare una traduzione di query SQL. L'utilizzo 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, l'API BigQuery Migration e il traduttore SQL batch supportano l'utilizzo di più file YAML di configurazione in un singolo job di traduzione. Per saperne di più, consulta Applicazione di più configurazioni YAML.

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 del bucket Cloud Storage che contiene i file di input della traduzione SQL. Per informazioni su come creare bucket e caricare file su Cloud Storage, consulta Crea bucket e Carica oggetti da un file system.
  • La dimensione di un singolo file YAML di configurazione non deve 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 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 specifica 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:

type: object_rewriter
global:
  case:
    all: UPPERCASE

Selezione delle entità

Per eseguire trasformazioni specifiche per le 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 YAML di configurazione espone le proprietà da abbinare per selezionare entità specifiche:

match:
  database: <literal_name>
  schema: <literal_name>
  relation: <literal_name>
  attribute: <literal_name>
  databaseRegex: <regex>
  schemaRegex: <regex>
  relationRegex: <regex>
  attributeRegex: <regex>

Descrizione di ogni proprietà match:

  • database o 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 degli attributi
  • databaseRegex o dbRegex: corrisponde a una proprietà database con un'espressione regolare (Anteprima).
  • schemaRegex: associa le proprietà schema alle espressioni regolari (anteprima).
  • relationRegex: corrisponde alle proprietà relation con espressioni regolari (anteprima).
  • attributeRegex: corrisponde alle proprietà attribute con espressioni regolari. Valido solo per la selezione degli attributi (Anteprima).

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

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

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

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

La corrispondenza delle proprietà letterali e regex viene trovata senza distinzione tra maiuscole e minuscole. Puoi applicare la corrispondenza sensibile alle maiuscole utilizzando un regex con un flag i disattivato, come mostrato nell'esempio seguente:

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

Puoi anche specificare entità con nome completo utilizzando una sintassi di stringa breve equivalente. Una sintassi di stringa breve prevede esattamente 3 (per la selezione della relazione) o 4 (per la selezione dell'attributo) segmenti di nome delimitati da punti, come nell'esempio testdb.acme.employee. I segmenti vengono quindi interpretati internamente come se fossero passati rispettivamente come database, schema, relation e attribute. Ciò significa che i nomi vengono abbinati letteralmente, pertanto le espressioni regolari non sono consentite nella sintassi breve. L'esempio seguente mostra l'utilizzo della sintassi di stringa breve per specificare un'entità con nome completo 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 oggetto. L'esempio seguente modifica la tabella testdb.acme.stg.employee in temporanea:

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

Il file 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 abbinare le entità è omettere la proprietà database 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 trasformazioni del tipo di attributo, in cui trasformi il tipo di dati di una colonna dal tipo di origine a un tipo di destinazione. Il file YAML di configurazione supporta i seguenti tipi di target:

  • BOOLEAN
  • TINYINT
  • SMALLINT
  • INTEGER
  • BIGINT
  • FLOAT
  • DOUBLE
  • NUMERIC (supporta precisione e scala facoltative, ad esempio 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 di file 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 file YAML di configurazione su una traduzione di query SQL BigQuery.

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

Modifica maiuscole/minuscole del nome dell'oggetto

Il seguente file YAML di configurazione modifica l'uso di lettere maiuscole o minuscole nei 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

Il seguente YAML di configurazione 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 seguente configurazione YAML 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 la scadenza della partizione

Il seguente YAML di configurazione modifica la scadenza di una tabella partizionata in 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
    );

Modifica la posizione o il formato esterno di una tabella

Il seguente file YAML di configurazione modifica la posizione e il formato esterni 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 file 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.'
    );

Imposta o modifica il partizionamento delle tabelle

Il seguente YAML di configurazione modifica lo schema di partizionamento di una tabella in modo che venga partizionata in base a una colonna di date:

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

Il seguente YAML di configurazione modifica lo schema di partizionamento di una tabella in modo che venga partizionato per bucket di intervallo:

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

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

snowflake-input.sql 
     create table 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
      );

Imposta o modifica il clustering delle tabelle

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

Modifica il tipo di un attributo di colonna

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

type: object_rewriter
attribute:
  -
    match:
      database: 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 di destinazione 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 connessione al data lake esterno

Il seguente YAML di configurazione contrassegna la tabella di origine come 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 la 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 il servizio di migrazione di BigQuery potrebbe identificare erroneamente la codifica di un file, puoi utilizzare un file YAML di configurazione per specificare esplicitamente la codifica dei caratteri.

Il seguente file YAML di configurazione specifica la codifica dei caratteri esplicita 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

Il seguente YAML di configurazione modifica un tipo di dati in un altro in tutti gli script e specifica un tipo di dati di origine da evitare nello script transpilato. Questa configurazione è diversa da Modifica il tipo 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 (accetta il fuso orario facoltativo)
  • Da TIMESTAMP WITH TIME ZONE a DATETIME (accetta il fuso orario facoltativo)
  • Da CHAR a VARCHAR

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

type: experimental_object_rewriter
global:
  typeConvert:
    timestamp: DATETIME

Impostazione del fuso orario predefinito

I dialetti dei database hanno semantiche e nomi diversi per i vari tipi di dati relativi a data e ora. Il servizio di traduzione utilizza la seguente terminologia standard nella configurazione YAML, indipendentemente dal nome del tipo di dati del dialetto di input:

  • Un datetime è una combinazione di Y-M-D H:M:S che non è fissata a un fuso orario specifico. datetime rappresenta un'ora di un orologio da parete e non un istante specifico.
  • Un timestamp rappresenta un istante di tempo particolare o assoluto e, in quanto tale, è implicitamente fissato a un fuso orario particolare, che potrebbe essere un'impostazione a livello di sessione o di database.
  • Un timestamptz rappresenta un istante particolare come un timestamp, ma, a differenza di un timestamp, porta con sé un offset di fuso orario particolare. Sebbene rappresentino lo stesso istante, 2019-06-01 12:00:00+4 e 2019-06-01 06:00:00-2 sono considerati valori timestamptz diversi.

In dialetti come Teradata, le funzioni relative a data e ora come current_date, current_time o current_timestamp restituiscono timestamp basati su un parametro del fuso orario della sessione configurato in modo implicito. BigQuery, invece, restituisce sempre i timestamp in UTC. Per garantire un comportamento coerente tra i due dialetti, potrebbe essere necessario configurare un fuso orario di conseguenza.

Ti consigliamo di specificare il fuso orario predefinito per la traduzione se il database di origine ha un valore predefinito diverso da UTC. In questo modo, il comportamento della query tradotta sarà corretto, poiché il fuso orario verrà conservato.

Nell'esempio seguente, il file YAML di configurazione converte un tipo di dati TIMESTAMP e un tipo di dati TIMESTAMPTZ in DATETIME, con il fuso orario di destinazione impostato su Europe/Paris.

Per i valori di stringa validi per i fusi orari, consulta Fusi orari.

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

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

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
          x.c_timestamp
        FROM
          test.x
        WHERE x.c_timestamp > datetime(current_timestamp(), 'Europe/Paris')
      ;
      SELECT
          x.c_timestamptz
        FROM
          test.x
        WHERE x.c_timestamptz > datetime(current_timestamp(), 'Europe/Paris')
      ;
      SELECT
          x.c_datetime
        FROM
          test.x
        WHERE x.c_datetime > datetime(current_timestamp(), 'Europe/Paris')
      ;

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

Per impostazione predefinita, TIMESTAMPTZ viene convertito in TIMESTAMP senza che sia necessaria alcuna configurazione.

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

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

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
          x.c_timestamp
        FROM
          test.x
        WHERE x.c_timestamp > current_timestamp()
      ;
      SELECT
          x.c_timestamptz
        FROM
          test.x
        WHERE x.c_timestamptz > current_timestamp()
      ;
      SELECT
          x.c_datetime
        FROM
          test.x
        WHERE x.c_datetime > current_timestamp()
      ;

Seleziona la modifica dell'istruzione

Il seguente YAML di configurazione modifica la proiezione a stella, GROUP BY e le clausole 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 file di configurazione YAML 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 UDF

Nella maggior parte dei casi, il traduttore SQL di BigQuery può analizzare automaticamente il linguaggio di definizione dei dati (DDL) delle funzioni definite dall'utente (UDF) esterne per determinare i tipi di dati di input e output, anche se il corpo della UDF è scritto in un linguaggio esterno. Tuttavia, per alcuni dialetti di origine, il traduttore potrebbe non essere in grado di compilare automaticamente la firma della funzione definita dall'utente. Per assicurarti che le istruzioni SQL che fanno riferimento a queste funzioni definite dall'utente vengano tradotte in modo accurato senza dover fornire il DDL originale, puoi utilizzare un file di configurazione YAML per registrare esplicitamente le firme delle funzioni definite dall'utente.

Il seguente YAML di configurazione specifica la firma delle funzioni definite dall'utente (UDF) utilizzate negli script di origine. Analogamente ai file ZIP dei 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 precisione decimale

Per impostazione predefinita, BigQuery Migration Service aumenta la precisione numerica alla precisione più alta disponibile per una determinata scala. Il seguente YAML di configurazione esegue l'override di questo comportamento configurando la severità della 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)
      )
      ;

Impostazione della precisione della stringa

Per impostazione predefinita, BigQuery Migration Service omette la precisione delle stringhe durante la traduzione delle colonne CHAR e VARCHAR. In questo modo, puoi evitare errori di troncamento durante la scrittura dei valori. Alcuni dialetti SQL, come Teradata, troncano i valori che superano la precisione massima in scrittura, mentre BigQuery restituisce un errore in questo scenario.

Se la tua applicazione non si basa sul comportamento di troncamento del dialetto di origine, valuta la possibilità di preservare la precisione della colonna nella definizione del tipo tradotto.

Il seguente YAML di configurazione esegue l'override di questo comportamento configurando la precisione in modo da mantenere la precisione della stringa dell'istruzione di origine.

type: experimental_statement_rewriter
common:
  stringPrecision: STRICT

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

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

Mappatura dei nomi di output

Puoi utilizzare il file YAML di configurazione per mappare i nomi degli oggetti SQL. Puoi modificare diverse parti del nome a seconda dell'oggetto mappato.

Mappatura dei nomi statica

Utilizza la mappatura dei nomi statici per mappare il nome di un'entità. Se vuoi modificare solo parti specifiche del nome mantenendo invariate le altre, includi solo le parti da modificare.

Il seguente YAML di configurazione modifica 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
      )

Puoi utilizzare la mappatura dei nomi statici per aggiornare la regione utilizzata dai nomi nelle funzioni definite dall'utente pubbliche.

L'esempio seguente modifica i nomi nella funzione definita dall'utente bqutil.fn dall'utilizzo della multi-regione predefinita us all'utilizzo della regione europe_west2:

type: experimental_object_rewriter
function:
-
  match:
    database: bqutil
    schema: fn
  outputName:
    database: bqutil
    schema: fn_europe_west2

Mappatura dinamica dei nomi

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

Il seguente file YAML di configurazione 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 database predefinito e del percorso di ricerca dello schema

Il seguente YAML di configurazione specifica un database predefinito e un percorso di ricerca dello 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

Configurazione di NLS_DATE_FORMAT in corso…

Il seguente YAML di configurazione imposta il parametro NLS_DATE_FORMAT sul formato DD/MM/YYYY. Ti consigliamo di specificare NLS_DATE_FORMAT per gli utilizzi impliciti del formato della data e dei cast. Se non viene impostato, viene utilizzato il formato predefinito per la traduzione, DD-MON-RR.

type: environment
session:
  dateFormat: DD/MM/YYYY

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

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

Riscrittura del nome dell'output globale

Il seguente file 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
      )
      ;

Ottimizza e migliora le prestazioni dell'SQL tradotto

È possibile applicare trasformazioni facoltative all'SQL tradotto per introdurre modifiche che possono migliorare le prestazioni o i costi. Queste ottimizzazioni dipendono rigorosamente dal caso e devono essere valutate rispetto all'output SQL non modificato per valutarne l'effetto effettivo sulle prestazioni.

Il seguente YAML di configurazione abilita le trasformazioni facoltative. La configurazione accetta un elenco di ottimizzazioni e, per le ottimizzazioni che accettano parametri, una sezione con valori parametro facoltativi.

type: optimizer
transformations:
  - name: PRECOMPUTE_INDEPENDENT_SUBSELECTS
  - name: REWRITE_CTE_TO_TEMP_TABLE
    parameters:
      threshold: 1
Ottimizzazione Parametro facoltativo Descrizione
PRECOMPUTE_INDEPENDENT_SUBSELECTS scope: [PREDICATE, PROJECTION] Riscrive la query aggiungendo un'istruzione DECLARE per sostituire un'espressione nelle clausole PREDICATE o PROJECTION con una variabile precalcolata. Questo verrà identificato come un predicato statico che consente di ridurre la quantità di dati letti. Se l'ambito viene omesso, il valore predefinito è PREDICATE (ovvero la clausola WHERE e JOIN-ON).

DECLARE Questa ottimizzazione introdurrà nuove istruzioni SQL.
REWRITE_CTE_TO_TEMP_TABLE threshold: N Riscrive le espressioni di tabella comuni (CTE) in tabelle temporanee quando sono presenti più di N riferimenti alla stessa espressione di tabella comune. In questo modo, la complessità delle query viene ridotta e viene forzata l'esecuzione singola dell'espressione di tabella comune. Se N viene omesso, il valore predefinito è 4.

Ti consigliamo di utilizzare questa ottimizzazione quando le CTE non banali vengono referenziate più volte. L'introduzione di tabelle temporanee comporta un overhead che potrebbe essere maggiore di eventuali esecuzioni multiple di una CTE a bassa complessità o bassa cardinalità. Questa ottimizzazione introdurrà nuove istruzioni SQL.
REWRITE_ZERO_SCALE_NUMERIC_AS_INTEGER bigint: N Riscrive gli attributi NUMERIC/BIGNUMERIC con scala zero nel tipo INT64 se la precisione rientra in N. Se N viene omesso, il valore predefinito è 18.

Ti consigliamo di utilizzare questa ottimizzazione quando traduci da dialetti di origine che non hanno tipi di numeri interi. La modifica dei tipi di colonna richiede la revisione di tutti gli utilizzi downstream per verificare la compatibilità dei tipi e le modifiche semantiche. Ad esempio, le divisioni frazionarie diventano divisioni intere o il codice prevede valori numerici.

Le traduzioni di Snowflake hanno questa ottimizzazione per i numeri con scala zero fino alla precisione 38 abilitata per impostazione predefinita. Questa ottimizzazione garantisce che un INTEGER Snowflake, rappresentato implicitamente come NUMBER(38,0) in Snowflake, venga tradotto in un INT64 BigQuery anziché in un BIGNUMERIC(38).

Se la tua applicazione utilizza numeri con precisioni superiori a 18, ti consigliamo di disattivare questa funzionalità per assicurarti che BigQuery possa elaborare l'intera gamma di valori richiesti dalla tua applicazione.
DROP_TEMP_TABLE Aggiunge istruzioni DROP TABLE per tutte le tabelle temporanee create in uno script e non eliminate alla fine dello script. In questo modo, il periodo di fatturazione dello spazio di archiviazione per la tabella temporanea viene ridotto da 24 ore al tempo di esecuzione dello script. Questa ottimizzazione introdurrà nuove istruzioni SQL.

Ti consigliamo di utilizzare questa ottimizzazione quando non viene eseguito l'accesso alle tabelle temporanee per ulteriori elaborazioni dopo il termine dell'esecuzione dello script. Questa ottimizzazione introdurrà nuove istruzioni SQL.
REGEXP_CONTAINS_TO_LIKE Riscrive alcune categorie di pattern di corrispondenza REGEXP_CONTAINS in espressioni LIKE.

Ti consigliamo di utilizzare questa ottimizzazione quando nessun altro processo, ad esempio la sostituzione di macro, si basa sulla conservazione invariata dei valori letterali del pattern di espressione regolare nell'SQL di output.
ADD_DISTINCT_TO_SUBQUERY_IN_SET_COMPARISON Aggiunge la clausola DISTINCT alle sottoquery utilizzate come set di valori per l'operatore [NOT] IN.

Ti consigliamo di utilizzare questa ottimizzazione quando la cardinalità (numero distinto di valori) del risultato della sottoquery è significativamente inferiore al numero di valori. Se questa precondizione non viene soddisfatta, questa trasformazione può avere effetti negativi sulle prestazioni.
APPROXIMATE_RANGE_PARTITIONS Approssima gli schemi di partizionamento di numeri interi non contigui o non regolari convertendoli in intervalli di partizione contigui di dimensioni uguali supportati da BigQuery. Per impostazione predefinita, questi schemi di partizionamento non influiscono sullo schema di partizionamento della tabella nelle istruzioni DDL tradotte.

Ti consigliamo di utilizzare questa ottimizzazione quando la tabella di origine utilizza una funzione di partizionamento non contigua come la funzione Teradata RANGE_N e trarrebbe vantaggio da uno schema di partizionamento di dimensioni uguali in BigQuery.

Esempi di ottimizzazione

La seguente ottimizzazione converte i tipi numerici con scala zero e precisione inferiore o uguale a 38 in INT64 in BigQuery.

# 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

Una traduzione SQL con questa ottimizzazione potrebbe essere simile alla seguente:

snowflake-input.sql 
      CREATE TABLE numbers(i INTEGER, n NUMERIC(10,0));
bq-output.sql 
      CREATE TABLE numbers(i INT64, n INT64);

La seguente configurazione disattiva l'ottimizzazione nei dialetti, come Snowflake, in cui è attivata per impostazione predefinita. Questa configurazione converte i tipi numerici in NUMERIC o BIGNUMERIC a seconda della precisione di input, anziché nel valore predefinito di INT64.

type: optimizer
transformations:
  - name: REWRITE_ZERO_SCALE_NUMERIC_AS_INTEGER
    enabled: false

Una traduzione SQL con questa ottimizzazione potrebbe essere simile alla seguente:

snowflake-input.sql 
      CREATE TABLE numbers(i INTEGER, n NUMERIC(10,0));
bq-output.sql 
      CREATE TABLE numbers(i BIGNUMERIC(38), n NUMERIC(29));

Crea un file YAML di configurazione basato su Gemini

Per generare l'output dell'AI generativa, la directory di origine contenente l'input della traduzione SQL deve includere un file YAML di configurazione.

Requisiti

Il file YAML di configurazione per gli output AI deve avere il suffisso .ai_config.yaml. Ad esempio, rules_1.ai_config.yaml.

Campi supportati

Puoi utilizzare i seguenti campi per configurare l'output della traduzione AI:

  • suggestion_type (facoltativo): specifica il tipo di suggerimento AI da generare. Sono supportati i seguenti tipi di suggerimenti:
    • QUERY_CUSTOMIZATION (predefinito): genera suggerimenti AI per il codice SQL in base alle regole di traduzione specificate nel file YAML di configurazione.
    • TRANSLATION_EXPLANATION: genera un testo che include un riepilogo della query GoogleSQL tradotta e le differenze e le incoerenze tra la query SQL di origine e la query GoogleSQL tradotta.
  • rewrite_target (facoltativo): specifica SOURCE_SQL se vuoi applicare la regola di traduzione all'SQL di input o TARGET_SQL (predefinito) se vuoi applicare la regola di traduzione all'SQL di output.
  • instruction (facoltativo): in linguaggio naturale, descrivi una modifica all'SQL di destinazione. La traduzione SQL ottimizzata con Gemini valuta la richiesta e apporta la modifica specificata.
  • examples (facoltativo): fornisci esempi SQL di come vuoi che venga modificato il pattern SQL.

Puoi aggiungere altri translation_rules e altri examples in base alle tue esigenze.

Esempi

I seguenti esempi creano file YAML di configurazione basati su Gemini che puoi utilizzare con le tue traduzioni SQL.

Rimuovi la funzione UPPER nella query di output della traduzione predefinita

translation_rules:
- instruction: "Remove upper() function"
  examples:
  - input: "upper(X)"
    output: "X"

Crea più regole di traduzione per personalizzare l'output della traduzione

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

Rimuovi i commenti SQL dalla query di input della traduzione

translation_rules:
- instruction: "Remove all the sql comments in the input sql query."
  suggestion_type: QUERY_CUSTOMIZATION
  rewrite_target: SOURCE_SQL

Genera spiegazioni della traduzione utilizzando il prompt LLM predefinito

Questo esempio utilizza i prompt LLM predefiniti forniti dal servizio di traduzione per generare spiegazioni di testo:

translation_rules:
- suggestion_type: "TRANSLATION_EXPLANATION"

Genera spiegazioni della traduzione utilizzando i tuoi prompt in linguaggio naturale

translation_rules:
- suggestion_type: "TRANSLATION_EXPLANATION"
  instruction: "Explain the syntax differences between the source Teradata query and the translated GoogleSQL query."

Più tipi di suggerimenti in un unico file YAML di configurazione

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

Applicazione di più configurazioni YAML

Quando specifichi un file YAML di configurazione in una traduzione SQL batch o interattiva, puoi selezionare più file YAML di configurazione in un singolo job di traduzione per riflettere più trasformazioni. Se più configurazioni sono in conflitto, una trasformazione potrebbe sostituirne un'altra. Ti consigliamo di utilizzare diversi tipi di impostazioni di configurazione in ogni file per evitare trasformazioni in conflitto nello stesso job di traduzione.

L'esempio seguente elenca 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)
    )
    ;