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, usando a API BigQuery Migration, 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, a API BigQuery Migration, 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:
  database: <literal_name>
  schema: <literal_name>
  relation: <literal_name>
  attribute: <literal_name>
  databaseRegex: <regex>
  schemaRegex: <regex>
  relationRegex: <regex>
  attributeRegex: <regex>

Descrição de cada property match:

  • database ou db: o componente project_id.
  • 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
  • databaseRegex ou dbRegex: corresponde a uma propriedade database 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:
    database: testdb
    schema: acme
    relation: employee
  temporary: true

É possível usar as propriedades databaseRegex, schemaRegex, relationRegex e attributeRegex para especificar expressões regulares a fim de selecionar um subconjunto de entidades. O exemplo a seguir muda 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 database, 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:
    database: 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 propriedade database 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:
      database: 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:

  • DATETIME a TIMESTAMP
  • TIMESTAMP para DATETIME (aceita fuso horário opcional)
  • TIMESTAMP WITH TIME ZONE para DATETIME (aceita fuso horário opcional)
  • CHAR a VARCHAR

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

type: experimental_object_rewriter
global:
  typeConvert:
    timestamp: DATETIME

Em dialetos como o Teradata, funções relacionadas a data e hora, como current_date, current_time ou current_timestamp, retornam carimbos de data/hora com base no fuso horário configurado, seja local ou de sessão. O BigQuery, por outro lado, sempre retorna carimbos de data/hora no fuso horário UTC. Para garantir um comportamento consistente entre os dois dialetos, é necessário configurar o fuso horário de acordo.

No exemplo a seguir, o YAML de configuração converte um tipo de dados TIMESTAMP e um TIMESTAMP WITH TIME ZONE em DATETIME, com o fuso horário de destino definido como Europe/Paris.

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

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);
      select a from x where a > current_timestamp(0);
bq-output.sql 
      CREATE TABLE x
      (
        a TIMESTAMP
      )
      ;
      SELECT
          x.a
        FROM
          test.x
        WHERE x.a > datetime_trunc(current_datetime('Europe/Paris'), SECOND)
      ;

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
      )

É possível usar o mapeamento de nomes estáticos para atualizar a região usada por nomes nas funções públicas definidas pelo usuário.

O exemplo a seguir muda os nomes na UDF bqutil.fn de usar a multirregião padrão us para usar a região europe_west2:

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

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

Otimizar e melhorar o desempenho do SQL traduzido

Transformações opcionais podem ser aplicadas ao SQL traduzido para introduzir mudanças que podem melhorar a consulta em termos de desempenho ou custo. Essas otimizações são estritamente dependentes do caso e precisam ser avaliadas em relação à saída SQL não modificada para avaliar o efeito real delas no desempenho.

O YAML de configuração a seguir ativa transformações opcionais. A configuração aceita uma lista de otimizações e, para otimizações que aceitam parâmetros, uma seção com valores de parâmetros opcionais.

type: experimental_optimizer
transformations:
  - name: PRECOMPUTE_INDEPENDENT_SUBSELECTS
  - name: REWRITE_CTE_TO_TEMP_TABLE
    parameters:
      threshold: 1
Otimização Parâmetro opcional Descrição
PRECOMPUTE_INDEPENDENT_SUBSELECTS scope: [PREDICATE, PROJECTION] Reescreva a consulta adicionando uma instrução DECLARE para substituir uma expressão nas cláusulas PREDICATE ou PROJECTION por uma variável pré-computada. Isso será identificado como um predicado estático, permitindo a redução da quantidade de dados lidos. Se o escopo for omitido, o valor padrão será PREDICATE (ou seja, as cláusulas WHERE e JOIN-ON).

Extrair uma subconsulta escalar para uma instrução DECLARE vai tornar o predicado original estático e, portanto, atender aos requisitos para um planejamento de execução melhor. Essa otimização vai introduzir novas instruções SQL.
REWRITE_CTE_TO_TEMP_TABLE threshold: N Reescreva expressões de tabela comuns (CTE) em tabelas temporárias quando houver mais de N referências à mesma expressão de tabela comum. Isso reduz a complexidade da consulta e força a execução única da expressão de tabela comum. Se N for omitido, o valor padrão será 4.

Recomendamos usar essa otimização quando CTEs não triviais forem mencionados várias vezes. A introdução de tabelas temporárias tem um overhead que pode ser maior do que as execuções múltiplas de uma CTE de baixa complexidade ou de baixa cardinalidade. Essa otimização vai introduzir novas instruções SQL.
REWRITE_ZERO_SCALE_NUMERIC_AS_INTEGER bigint: N Reescreva os atributos NUMERIC/BIGNUMERIC de escala zero para o tipo INT64 se a precisão estiver dentro de N. Se N for omitido, o valor padrão será 18.

Recomendamos usar essa otimização ao traduzir de dialetos de origem que não têm tipos de números inteiros. A mudança de tipos de coluna exige a análise de todos os usos dependentes para verificar a compatibilidade de tipos e mudanças semânticas. Por exemplo, divisões fracionárias se tornando divisões inteiras, código que espera valores numéricos
DROP_TEMP_TABLE Adiciona instruções DROP TABLE para todas as tabelas temporárias criadas em um script e não descartadas ao final dele. Isso reduz o período de cobrança de armazenamento da tabela temporária de 24 horas para o tempo de execução do script. Essa otimização vai introduzir novas instruções SQL.

Recomendamos usar essa otimização quando as tabelas temporárias não forem acessadas para nenhum processamento adicional após o término da execução do script. Essa otimização vai introduzir novas instruções SQL.
REGEXP_CONTAINS_TO_LIKE Reescreve algumas categorias de padrões de correspondência REGEXP_CONTAINS para expressões LIKE.

Recomendamos usar essa otimização quando nenhum outro processo, como a substituição de macros, depender de que os literais de padrão de expressão regular sejam preservados sem alterações no SQL de saída.
ADD_DISTINCT_TO_SUBQUERY_IN_SET_COMPARISON Adiciona a cláusula DISTINCT às subconsultas usadas como conjunto de valores para o operador [NOT] IN.

Recomendamos usar essa otimização quando a cardinalidade (número distinto de valores) do resultado da subconsulta for significativamente menor que o número de valores. Quando essa condição prévia não é atendida, essa transformação pode ter efeitos negativos no desempenho.

Criar um arquivo YAML de configuração baseado em Gemini

Para gerar a saída da IA, o diretório de origem que contém a entrada de tradução de SQL precisa incluir um arquivo YAML de configuração.

Requisitos

O arquivo YAML de configuração para saídas de IA precisa ter o sufixo .ai_config.yaml. Por exemplo, rules_1.ai_config.yaml.

Campos aceitos

suggestion_type: SUGGESTION_TYPE
rewrite_target: TARGET
instruction: NL_PROMPT
translation_rules:
- instruction: NL_RULE_1
  examples:
  - input: RULE_1_INPUT_1
    output: RULE_1_OUTPUT_1
  - input: RULE_1_INPUT_2
    output: RULE_1_OUTPUT_2
- instruction: NL_RULE_2
  examples:
  - input: RULE_2_INPUT_1
    output: RULE_2_OUTPUT_1
…
…

É possível substituir as seguintes variáveis para configurar a saída da tradução por IA:

  • SUGGESTION_TYPE (opcional): especifica o tipo de sugestão de IA a ser gerada. Os seguintes tipos de sugestão são aceitos:

    • QUERY_CUSTOMIZATION (padrão): gera sugestões de IA para código SQL com base nas regras de tradução especificadas no arquivo YAML de configuração.
    • TRANSLATION_EXPLANATION: gera um texto que inclui um resumo da consulta do GoogleSQL traduzida e as diferenças e inconsistências entre a consulta SQL de origem e a consulta do GoogleSQL traduzida.
    • CUSTOM_SUGGESTION: gera saídas de SQL ou texto usando artefatos de tradução. Os usuários podem consultar artefatos de tradução em comandos que incluem marcadores de posição. O serviço de tradução anexa os artefatos correspondentes ao comando LLM final enviado ao Gemini. Os seguintes artefatos de tradução podem ser incluídos no comando:
      • {{SOURCE_DIALECT}}: use para fazer referência ao dialeto SQL de origem.
      • {{SOURCE_SQL}}: use para fazer referência ao SQL da origem da tradução.
      • {{TARGET_SQL}}: use para fazer referência ao SQL traduzido padrão.

  • TARGET (opcional): especifique se você quer aplicar a regra de tradução ao SQL de entrada, SOURCE_SQL, ou ao SQL de saída, TARGET_SQL (padrão).

  • NL_PROMPT (opcional): em linguagem natural, descreva uma mudança no SQL de destino. A tradução de SQL aprimorada do Gemini avalia a solicitação e faz a mudança especificada.

  • NL_RULE_1 (opcional): em linguagem natural, descreva uma regra de tradução.

  • RULE_1_INPUT_1 (opcional): um padrão SQL que você quer substituir.

  • RULE_1_OUTPUT_1 (opcional): o padrão SQL esperado depois que input foi substituído.

É possível adicionar mais translation_rules e examples conforme necessário.

Exemplos

Os exemplos a seguir criam arquivos YAML de configuração baseados no Gemini que podem ser usados com as traduções de SQL.

Remover a função superior na consulta de saída de tradução padrão

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

Criar várias regras de tradução para personalizar a saída da tradução

translation_rules:
- instruction: "Remove upper() function"
  examples:
  - input: "upper(X)"
    output: "X"
- instruction: "Insert a comment at the head that explains each statement in detail.

Remover comentários SQL da consulta de entrada de tradução

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

Gerar explicações de tradução usando o comando LLM padrão

Este exemplo usa as instruções de LLM padrão fornecidas pelo serviço de tradução para gerar explicações de texto:

suggestion_type: "TRANSLATION_EXPLANATION"

Gera explicações de tradução usando seus próprios comandos de linguagem natural

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

Correção do erro de conversão do MySQL para GoogleSQL: unsupported constraint on PRIMARY

suggestion_type: "CUSTOM_SUGGESTION"
instruction: "Add PRIMARY KEY (...) NOT ENFORCED to the target sql as a column constraint based on the source sql. Output sql without sql code block.\n\nsource sql: {{SOURCE_SQL}}\ntarget sql: {{TARGET_SQL}}"

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