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 di varie trasformazioni di traduzione supportate da questa funzionalità.
Quando utilizzi il traduttore SQL interattivo BigQuery o esegui una traduzione SQL batch, puoi fornire file YAML di configurazione per modificare una traduzione di 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:
- Se esegui una traduzione SQL batch, posiziona il codice YAML di configurazione nello stesso bucket Cloud Storage dei file SQL di input.
- Se utilizzi il traduttore SQL interattivo, specifica il percorso del file del file di configurazione o dell'ID del job di traduzione batch nelle impostazioni di traduzione.
- Se utilizzi il client Python per la traduzione batch, inserisci il file YAML di configurazione nella cartella di input della traduzione locale.
Il traduttore SQL interattivo, il traduttore SQL batch e il client Python di traduzione batch supportano l'utilizzo di più file YAML di configurazione in un singolo job di traduzione. Per ulteriori informazioni, 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 per l'utilizzo 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, usa RE2/J. - Tutti i nomi dei file YAML di configurazione devono includere un'estensione
.config.yaml
, ad esempiochange-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:
Titolo
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 maiuscolo:
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. La seguente configurazione YAML mostra le proprietà di cui trovare la corrispondenza 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'attributodbRegex
: corrisponde a una proprietàdb
con un'espressione regolare (Anteprima).schemaRegex
: associa le proprietàschema
alle espressioni regolari (anteprima).relationRegex
: associa le proprietàrelation
con le espressioni regolari (anteprima).attributeRegex
: abbina le 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 temporanea della tabella.
type: object_rewriter
relation:
-
match:
db: testdb
schema: acme
relation: employee
temporary: true
Puoi utilizzare le proprietà dbRegex
, schemaRegex
, relationRegex
e attributeRegex
per specificare espressioni regolari in modo da 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
Sia le proprietà letterali che regex
vengono associate 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à qualificate utilizzando una sintassi a stringa corta equivalente. Una sintassi a stringa breve prevede esattamente 3 (per la selezione delle relazioni) o 4
(per la selezione degli attributi) segmenti di nomi delimitati da punti, come nell'esempio
testdb.acme.employee
. I segmenti vengono quindi interpretati internamente come se fossero
trasmessi rispettivamente come db
, schema
, relation
e attribute
.
Ciò significa che i nomi vengono trovati letteralmente, pertanto le espressioni regolari non sono consentite
nella sintassi breve. L'esempio seguente mostra l'utilizzo della sintassi a stringhe brevi per specificare un'entità valida 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
cambia la tabella testdb.acme.stg.employee
in temporanea:
type: object_rewriter
relation:
-
match:
db: testdb
schema: acme
relation: stg.employee
temporary: true
La configurazione YAML 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 corrispondenze 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 del tipo di attributo, in cui trasformerai 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 scala facoltative, ad esempioNUMERIC(18, 2)
)TIME
TIMETZ
DATE
DATETIME
TIMESTAMP
TIMESTAMPTZ
CHAR
(supporta la precisione facoltativa, ad esempioCHAR(42)
)VARCHAR
(supporta la precisione facoltativa, ad esempioVARCHAR(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 l'effetto 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. Gli esempi seguenti utilizzano anche testdb
come database predefinito e testschema
come percorso di ricerca dello schema.
Modifica maiuscole/minuscole del nome oggetto
Il seguente codice YAML di configurazione modifica la parte superiore o inferiore delle maiuscole 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 ; |
Imposta tabella temporanea
Il seguente codice 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 ) ; |
Imposta la tabella come 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 della partizione
Il seguente codice 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 ); |
Modificare la posizione o il formato esterno di una tabella
Il seguente codice YAML di configurazione modifica la posizione esterna e la creazione 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 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 codice YAML di configurazione 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 ) ; |
Imposta o modifica 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 di colonna
Il seguente codice YAML di configurazione 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 connessione a data lake esterno
Il seguente codice YAML di configurazione contrassegna la tabella di origine come tabella esterna che punta ai dati archiviati in un data lake esterno, specificati da una connessione 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( ); |
Modificare 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 BigQuery Migration Service potrebbe identificare erroneamente la codifica di un file, puoi utilizzare un YAML di configurazione per specificare esplicitamente la codifica dei caratteri.
Il seguente codice 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 di 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 sottoposto a transpile. Questo è diverso dalla configurazione Modifica del tipo di 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
aTIMESTAMP
- Da
TIMESTAMP
aDATETIME
- Da
TIMESTAMP WITH TIME ZONE
aDATETIME
- Da
CHAR
aVARCHAR
Nell'esempio seguente, la configurazione YAML 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 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, la 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 della funzione definita dall'utente
Il seguente codice YAML di configurazione specifica la firma delle funzioni definite dall'utente utilizzate negli script di origine. Proprio come i 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 ; |
Impostare la severità di precisione decimale
Per impostazione predefinita, BigQuery Migration Service aumenta la precisione numerica fino al valore più alto disponibile per una determinata scala. La configurazione YAML seguente sostituisce questo comportamento configurando la severità di precisione per 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) ) ; |
Mappatura dei nomi di output
Puoi utilizzare YAML di configurazione per mappare i nomi degli oggetti SQL. Puoi modificare parti diverse del nome a seconda dell'oggetto da mappare.
Mapping dei nomi statico
Utilizza la mappatura dei nomi statica per mappare il nome di un'entità. Se vuoi modificare solo parti specifiche del nome mantenendo invariate altre parti, 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 dinamica dei nomi per modificare più oggetti contemporaneamente e creare nuovi nomi in base agli oggetti mappati.
Il seguente codice YAML di configurazione modifica il nome di tutte le tabelle aggiungendo il prefisso stg_
a quelle che appartengono allo schema staging
, per poi spostare queste tabelle allo 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 ) ; |
Specificare il database predefinito e il percorso di ricerca dello schema
Il seguente codice YAML di configurazione 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
La configurazione YAML seguente 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 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 prevalere su un'altra. Consigliamo di utilizzare diversi tipi di impostazioni di configurazione in ogni file per evitare trasformazioni in conflitto 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 essere simile alla seguente:
teradata-input.sql |
create table x(a int); |
bq-output.sql |
CREATE TEMPORARY TABLE x ( a NUMERIC(31, 2) ) ; |