Transformar traduções de SQL usando arquivos YAML de configuração

Neste documento, mostramos como usar arquivos YAML de configuração para transformar o código SQL ao migrá-lo para o BigQuery. Ele apresenta diretrizes para criar seus próprios arquivos YAML de configuração e exemplos para várias transformações de tradução compatíveis com esse recurso.

Ao usar o tradutor de SQL interativo do BigQuery ou realizar uma tradução de SQL em lote, é possível fornecer arquivos YAML de configuração para modificar uma tradução da consulta SQL. O uso de arquivos YAML de configuração permite maior personalização ao traduzir consultas SQL do seu banco de dados de origem.

É possível especificar um arquivo YAML de configuração para usar em uma tradução de SQL das seguintes maneiras:

O tradutor de SQL interativo, o tradutor de SQL em lote e o cliente Python de tradução em lote aceitam o uso de vários arquivos YAML de configuração em um único job de tradução. Consulte Como aplicar várias configurações YAML para mais informações.

Requisitos do arquivo YAML de configuração

Antes de criar um arquivo YAML de configuração, leia as seguintes informações para verificar se ele é compatível com o Serviço de migração do BigQuery:

  • É preciso fazer upload dos arquivos YAML de configuração para o diretório raiz do bucket do Cloud Storage que contém os arquivos de entrada da tradução de SQL. Para informações sobre como criar buckets e fazer upload de arquivos no Cloud Storage, consulte Criar buckets e Fazer upload de objetos de um sistema de arquivos.
  • O tamanho de um único arquivo YAML de configuração não pode exceder 1 MB.
  • O tamanho total de todos os arquivos YAML de configuração usados em um único job de tradução de SQL não pode exceder 4 MB.
  • Se você estiver usando a sintaxe regex para a correspondência de nomes, use RE2/J.
  • Todos os nomes dos arquivos YAML de configuração precisam incluir uma extensão .config.yaml, por exemplo, change-case.config.yaml.
    • config.yaml não é um nome válido para o arquivo de configuração.

Diretrizes para criar um arquivo YAML de configuração

Nesta seção, apresentamos algumas diretrizes gerais para criar um arquivo YAML de configuração:

Cada arquivo de configuração precisa conter um cabeçalho que especifique o tipo de configuração. O tipo object_rewriter é usado para especificar traduções de SQL em um arquivo YAML de configuração. O exemplo a seguir usa o tipo object_rewriter para transformar a caixa de um nome:

type: object_rewriter
global:
  case:
    all: UPPERCASE

Seleção de entidade

Para realizar transformações específicas de entidade, especifique a entidade no arquivo de configuração. Todas as properties match são opcionais. Use apenas as properties match necessárias para uma transformação. O YAML de configuração a seguir expõe properties a serem correspondidas para a seleção de entidades específicas:

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

Descrição de cada property match:

  • db: o componente do ID do projeto.
  • schema: o componente do conjunto de dados.
  • relation: o componente da tabela.
  • attribute: o componente da coluna. Válido apenas para seleção de atributo
  • dbRegex: corresponde a uma propriedade db com uma expressão regular (Prévia).
  • schemaRegex: faz a correspondência das propriedades schema com expressões regulares (Prévia).
  • relationRegex: faz a correspondência das propriedades relation com expressões regulares (Prévia).
  • attributeRegex: combina as propriedades attribute com expressões regulares. Válida somente para seleção de atributos (Prévia).

Por exemplo, o YAML de configuração a seguir especifica as properties match para selecionar a tabela testdb.acme.employee para uma transformação de tabela temporária.

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

É possível usar as properties dbRegex, schemaRegex, relationRegex e attributeRegex para especificar expressões regulares a fim de selecionar um subconjunto de entidades. O exemplo a seguir altera todas as relações do esquema tmp_schema em testdb para temporário, desde que o nome comece com tmp_:

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

As properties regex e o literal são correspondidos de uma maneira que não diferencia maiúsculas de minúsculas. É possível aplicar a correspondência que diferencia maiúsculas de minúsculas usando um regex com uma flag i desativada, como mostrado no exemplo a seguir:

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

Também é possível especificar entidades totalmente qualificadas usando uma sintaxe de string curta equivalente. Uma sintaxe de string curta espera exatamente três segmentos de nome (para seleção de relação) ou quatro (para seleção de atributo) delimitados por pontos, como no exemplo testdb.acme.employee. Os segmentos são interpretados internamente como se fossem transmitidos como db, schema, relation e attribute, respectivamente. Isso significa que os nomes são correspondidos literalmente. Portanto, expressões regulares não são permitidas em sintaxe curta. O exemplo a seguir mostra o uso de sintaxe de string curta para especificar uma entidade totalmente qualificada em um arquivo YAML de configuração:

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

Se uma tabela tiver um ponto no nome, não será possível especificar o nome usando uma sintaxe curta. Nesse caso, é preciso usar uma correspondência de objeto. O exemplo a seguir altera a tabela testdb.acme.stg.employee para temporária:

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

O YAML de configuração aceita key como alias para match.

Banco de dados padrão

Alguns dialetos SQL de entrada, principalmente o Teradata, não são compatíveis com database-name no nome qualificado. Nesse caso, a maneira mais fácil de corresponder entidades é omitir a property db em match.

No entanto, é possível definir a property default_database do Serviço de migração do BigQuery e usar esse banco de dados padrão no match.

Tipos de atributo de destino compatíveis

É possível usar o arquivo YAML de configuração para realizar transformações de tipo de atributo, em que você transforma o tipo de dados de uma coluna do tipo de origem para um tipo de destino. O arquivo YAML de configuração é compatível com os seguintes tipos de destino:

  • BOOLEAN
  • TINYINT
  • SMALLINT
  • INTEGER
  • BIGINT
  • FLOAT
  • DOUBLE
  • NUMERIC (compatível com precisão e escala opcionais, como NUMERIC(18, 2))
  • TIME
  • TIMETZ
  • DATE
  • DATETIME
  • TIMESTAMP
  • TIMESTAMPTZ
  • CHAR (compatível com precisão opcional, como CHAR(42))
  • VARCHAR (compatível com precisão opcional, como VARCHAR(42))

Exemplos de YAML de configuração

Nesta seção, apresentamos exemplos para criar vários arquivos YAML de configuração para usar com as traduções de SQL. Cada exemplo descreve a sintaxe YAML para transformar a tradução de SQL de maneiras específicas, com uma breve descrição. Cada exemplo também apresenta o conteúdo de um arquivo teradata-input.sql ou hive-input.sql e um arquivo bq-output.sql para você comparar os efeitos de um YAML de configuração em uma tradução de consulta SQL do BigQuery.

Os exemplos a seguir usam o Teradata ou o Hive como o dialeto SQL de entrada e o SQL do BigQuery como o dialeto de saída. Os exemplos a seguir também usam testdb como o banco de dados padrão e testschema como o caminho de pesquisa do esquema.

Alterar caixa do nome do objeto

O YAML de configuração a seguir altera a caixa maiúscula ou minúscula nos nomes de objetos:

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

Uma tradução de SQL com esse arquivo YAML de configuração pode ter o seguinte formato:

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
      ;

Tornar tabela temporária

A configuração YAML a seguir altera uma tabela regular para uma tabela temporária:

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

Uma tradução de SQL com esse arquivo YAML de configuração pode ter o seguinte formato:

teradata-input.sql 

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

    CREATE TEMPORARY TABLE x
    (
      a INT64
    )
    ;

Tornar tabela efêmera

A configuração YAML a seguir altera uma tabela regular para uma tabela efêmera com validade de 60 segundos.

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

Uma tradução de SQL com esse arquivo YAML de configuração pode ter o seguinte formato:

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

Definir validade da partição

O YAML de configuração a seguir altera a validade de uma tabela particionada para 1 dia:

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

Uma tradução de SQL com esse arquivo YAML de configuração pode ter o seguinte formato:

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

Alterar local ou formato externo de uma tabela

O YAML de configuração a seguir altera o local externo e formato de uma tabela:

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

Uma tradução de SQL com esse arquivo YAML de configuração pode ter o seguinte formato:

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

Definir ou alterar descrição da tabela

O YAML de configuração a seguir define a descrição de uma tabela:

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

Uma tradução de SQL com esse arquivo YAML de configuração pode ter o seguinte formato:

teradata-input.sql 

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

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

Definir ou alterar particionamento de tabela

O YAML de configuração altera o esquema de particionamento de uma tabela:

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

Uma tradução de SQL com esse arquivo YAML de configuração pode ter o seguinte formato:

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

Definir ou alterar clustering de tabela

O YAML de configuração altera o esquema de clustering de uma tabela:

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

Uma tradução de SQL com esse arquivo YAML de configuração pode ter o seguinte formato:

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

Alterar tipo de um atributo de coluna

O YAML de configuração a seguir altera o tipo de dados para um atributo de uma coluna:

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

É possível transformar o tipo de dados de origem em qualquer um dos tipos de atributo de destino compatíveis.

Uma tradução de SQL com esse arquivo YAML de configuração pode ter o seguinte formato:

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

Adicionar conexão a um data lake externo

O YAML de configuração a seguir marca a tabela de origem como uma tabela externa que aponta para dados armazenados em um data lake externo, especificado por uma conexão de data lake.

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

Uma tradução de SQL com esse arquivo YAML de configuração pode ter o seguinte formato:

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

Alterar a codificação de caracteres de um arquivo de entrada

Por padrão, o serviço de migração do BigQuery tenta detectar automaticamente a codificação de caracteres dos arquivos de entrada. Nos casos em que o BigQuery Migration Service pode identificar incorretamente a codificação de um arquivo, é possível usar um YAML de configuração para especificar explicitamente a codificação de caracteres.

O YAML de configuração a seguir especifica a codificação explícita de caracteres do arquivo de entrada como ISO-8859-1.

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

Conversão de tipo global

O YAML de configuração a seguir altera um tipo de dados para outro em todos os scripts e especifica um tipo de dados de origem a ser evitado no script transcompilado. Isso é diferente da configuração Alterar o tipo de atributo de uma coluna, em que apenas o tipo de dados de um único atributo é alterado.

O BigQuery é compatível com as seguintes conversões de tipo de dados:

  • TIMESTAMP recebeu o papel de DATETIME
  • De TIMESTAMP para DATETIME
  • De TIMESTAMP WITH TIME ZONE para DATETIME
  • VARCHAR recebeu o papel de CHAR

No exemplo a seguir, o YAML de configuração converte um tipo de dados DATETIME em TIMESTAMP.

type: experimental_object_rewriter
global:
  typeConvert:
    timestamp: DATETIME

Uma tradução de SQL com esse arquivo YAML de configuração pode ter o seguinte formato:

teradata-input.sql 

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

      CREATE TABLE x
      (
        a TIMESTAMP
      )
      ;

Selecionar modificação de instrução

O YAML de configuração a seguir altera a projeção em estrela, as cláusulas GROUP BY e ORDER BY nas instruções SELECT.

starProjection é compatível com as seguintes configurações:

  • ALLOW
  • PRESERVE (padrão)
  • EXPAND

groupBy e orderBy são compatíveis com as seguintes configurações:

  • EXPRESSION
  • ALIAS
  • INDEX

No exemplo a seguir, o YAML de configuração define a projeção em estrela como EXPAND.

type: experimental_statement_rewriter
select:
  starProjection: EXPAND

Uma tradução de SQL com esse arquivo YAML de configuração pode ter o seguinte formato:

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
      ;

Especificação de UDF

O YAML de configuração a seguir especifica a assinatura de funções definidas pelo usuário (UDFs, na sigla em inglês) que são usadas nos scripts de origem. Assim como os arquivos ZIP de metadados, as definições de UDF podem ajudar a produzir uma tradução mais precisa dos scripts de entrada.

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

Uma tradução de SQL com esse arquivo YAML de configuração pode ter o seguinte formato:

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
      ;

Como definir a rigidez da precisão decimal

Por padrão, o serviço de migração do BigQuery aumenta a precisão numérica para a maior precisão disponível em uma determinada escala. O YAML de configuração a seguir substitui esse comportamento configurando o rigor de precisão para manter a precisão decimal da instrução de origem.

type: experimental_statement_rewriter
common:
  decimalPrecision: STRICT

Uma tradução de SQL com esse arquivo YAML de configuração pode ter o seguinte formato:

teradata-input.sql 

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

      CREATE TABLE x
      (
        a NUMERIC(3)
      )
      ;

Mapeamento de nome de saída

É possível usar o YAML de configuração para mapear nomes de objetos SQL. É possível alterar diferentes partes do nome, dependendo do objeto que está sendo mapeado.

Mapeamento de nome estático

Use o mapeamento de nomes estáticos para mapear o nome de uma entidade. Se você quiser mudar apenas partes específicas do nome, mantendo as outras partes iguais, inclua apenas as partes que precisam ser alteradas.

O YAML de configuração a seguir altera o nome da tabela de my_db.my_schema.my_table para 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"

Uma tradução de SQL com esse arquivo YAML de configuração pode ter o seguinte formato:

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
      )

Mapeamento de nomes dinâmico

Use o mapeamento de nomes dinâmico para alterar vários objetos ao mesmo tempo e crie novos nomes com base nos objetos mapeados.

O YAML de configuração a seguir altera o nome de todas as tabelas adicionando o prefixo stg_ àquelas que pertencem ao esquema staging e, em seguida, move essas tabelas para o esquema production.

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

Uma tradução de SQL com esse arquivo YAML de configuração pode ter o seguinte formato:

teradata-input.sql 

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

      CREATE TABLE production.stg_my_table
      (
        a INT64
      )
      ;

Como especificar o banco de dados padrão e o caminho de pesquisa do esquema

O YAML de configuração a seguir especifica um banco de dados padrão e um caminho de pesquisa de esquema.

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

Uma tradução de SQL com esse arquivo YAML de configuração pode ter o seguinte formato:

teradata-input.sql 

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

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

Reescrever o nome da saída global

O YAML de configuração a seguir altera os nomes de saída de todos os objetos (banco de dados, esquema, relação e atributos) no script de acordo com as regras configuradas.

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

Uma tradução de SQL com esse arquivo YAML de configuração pode ter o seguinte formato:

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

Como aplicar várias configurações YAML

Ao especificar um arquivo YAML de configuração em uma tradução de SQL interativa ou em lote, é possível selecionar vários arquivos YAML de configuração em um único job de tradução para refletir várias transformações. Se várias configurações entrarem em conflito, uma transformação poderá substituir outra. Recomendamos usar diferentes tipos de configurações em cada arquivo para evitar transformações conflitantes no mesmo job de tradução.

O exemplo a seguir lista dois arquivos YAML de configuração separados que foram fornecidos para um único job de tradução do SQL, um para alterar o atributo de uma coluna e o outro para definir a tabela como temporária:

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

Uma tradução de SQL com esses dois arquivos YAML de configuração pode ter o seguinte formato:

teradata-input.sql 

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

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