Transforma traducciones de SQL con archivos YAML de configuración

En este documento, se muestra cómo usar archivos YAML de configuración para transformar el código SQL mientras se lo migra a BigQuery. Se proporcionan lineamientos a fin de crear tus propios archivos YAML de configuración y se brindan ejemplos para varias transformaciones de traducción compatibles con esta función.

Cuando usas el traductor de SQL interactivo de BigQuery o realizas una traducción de SQL por lotes, puedes proporcionar archivos YAML de configuración para modificar una traducción de una consulta en SQL. El uso de archivos YAML de configuración permite una mayor personalización cuando se traducen consultas en SQL desde tu base de datos de origen.

Puedes especificar un archivo YAML de configuración para usar en una traducción de SQL de las siguientes maneras:

Si realizas una traducción de SQL por lotes, coloca el YAML de configuración en el mismo bucket de Cloud Storage que los archivos SQL de entrada.
Si usas el traductor interactivo de SQL, especifica la ruta de acceso del archivo al archivo de configuración o el ID de tarea de traducción por lotes en la configuración de traducción.
Si usas el cliente de Python de traducción por lotes, coloca el archivo YAML de configuración en la carpeta de entrada de traducción local.

El traductor interactivo de SQL, el traductor de SQL por lotes y el cliente de Python de traducción por lotes admiten el uso de varios archivos YAML de configuración en un solo trabajo de traducción. Para obtener más información, consulta Aplica varias opciones de configuración de YAML.

Requisitos del archivo YAML de configuración

Antes de crear un archivo YAML de configuración, revisa la siguiente información a fin de asegurarte de que tu archivo YAML sea compatible para usarlo con el Servicio de migración de BigQuery:

Debes subir los archivos YAML de configuración al directorio raíz del bucket de Cloud Storage que contiene los archivos de entrada de traducción de SQL. Si deseas obtener información para crear buckets y subir archivos a Cloud Storage, consulta Crea buckets y Sube objetos desde un sistema de archivos.
El tamaño de un solo archivo YAML de configuración no debe superar los 1 MB.
El tamaño total del archivo de todos los archivos YAML de configuración que se usan en un solo trabajo de traducción de SQL no debe exceder los 4 MB.
Si usas la sintaxis regex para la coincidencia de nombres, usa RE2/J.
Todos los nombres de los archivos YAML de configuración deben incluir una extensión .config.yaml, por ejemplo, change-case.config.yaml.
- config.yaml por sí solo no es un nombre válido para el archivo de configuración.

Lineamientos para crear un archivo YAML de configuración

En esta sección, se proporcionan algunos lineamientos generales para crear un archivo YAML de configuración:

Cada archivo de configuración debe contener un encabezado que especifique el tipo de configuración. El tipo object_rewriter se usa para especificar traducciones de SQL en un archivo YAML de configuración. En el siguiente ejemplo, se usa el tipo object_rewriter para transformar un caso práctico:

type: object_rewriter
global:
  case:
    all: UPPERCASE

Selección de entidad

Para llevar a cabo transformaciones específicas de la entidad, especifica la entidad en el archivo de configuración. Todas las propiedades match son opcionales. Usa solo las propiedades match necesarias para una transformación. En la siguiente YAML de configuración, se exponen las propiedades que deben coincidir para elegir entidades específicas:

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

Descripción de cada propiedad match:

db: el componente project_id.
schema: el componente del conjunto de datos.
relation: el componente de la tabla.
attribute: el componente de la columna. Solo es válido para la selección de atributos
dbRegex: Coincide con una propiedad db que tiene una expresión regular (Vista previa).
schemaRegex: Hace coincidir propiedades schema con expresiones regulares (Vista previa).
relationRegex: Coincide con las propiedades relationque tienen expresiones regulares (Vista previa).
attributeRegex: Coincide con las propiedades attributeque tienen expresiones regulares. Solo es válido para la selección de atributos (Vista previa).

Por ejemplo, el siguiente YAML de configuración especifica las propiedades match con el objetivo de seleccionar la tabla testdb.acme.employee para una transformación de tabla temporal.

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

Puedes usar las propiedades dbRegex, schemaRegex, relationRegex y attributeRegex para especificar expresiones regulares a fin de seleccionar un subconjunto de entidades. En el siguiente ejemplo, se cambian todas las relaciones del esquema tmp_schema en testdb a temporal, siempre que su nombre comience con tmp_:

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

Las propiedades literales y regex coinciden de manera que no distingue mayúsculas de minúsculas. Puedes aplicar la coincidencia que distingue mayúsculas de minúsculas mediante un regex con una marca i inhabilitada, como se ve en el siguiente ejemplo:

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

También puedes especificar entidades completamente calificadas mediante una sintaxis de string corta equivalente. Una sintaxis de string corta espera exactamente 3 segmentos de nombre (para la selección de relaciones) o 4 segmentos de nombre (para la selección de atributos) delimitados por puntos, como el ejemplo testdb.acme.employee. A continuación, los segmentos se interpretan de forma interna como si se hubieran pasado como db, schema, relation y attribute, respectivamente. Esto significa que los nombres coinciden de manera literal, por lo que las expresiones regulares no están permitidas en la sintaxis corta. En el siguiente ejemplo, se muestra el uso de la sintaxis de string corta para especificar una entidad completamente calificada en un archivo YAML de configuración:

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

Si una tabla contiene un punto en el nombre, no puedes especificar el nombre con una sintaxis corta. En este caso, debes usar una coincidencia de objeto. En el siguiente ejemplo, se cambia la tabla testdb.acme.stg.employee a temporal:

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

El YAML de configuración acepta key como un alias para match.

Base de datos predeterminada

Algunos dialectos de SQL de entrada, en particular Teradata, no admiten database-name en el nombre calificado. En este caso, la forma más fácil de hacer coincidir las entidades es omitir la propiedad db en match.

Sin embargo, puedes configurar la propiedad default_database del Servicio de migración de BigQuery y usar esa base de datos predeterminada en match.

Tipos de atributos de destino admitidos

Puedes usar el archivo YAML de configuración para realizar transformaciones de tipo de atributo, en las que transformas el tipo de datos de una columna del tipo de origen a un tipo de destino. El archivo YAML de configuración admite los siguientes tipos de destino:

BOOLEAN
TINYINT
SMALLINT
INTEGER
BIGINT
FLOAT
DOUBLE
NUMERIC (admite precisión y escala opcionales, como NUMERIC(18, 2))
TIME
TIMETZ
DATE
DATETIME
TIMESTAMP
TIMESTAMPTZ
CHAR (admite precisión opcional, como CHAR(42))
VARCHAR (admite precisión opcional, como VARCHAR(42))

Ejemplos de YAML de configuración

En esta sección, se proporcionan ejemplos con el objetivo de crear varios archivos YAML de configuración para usar con tus traducciones de SQL. En cada ejemplo, se describe la sintaxis YAML para transformar tu traducción de SQL de maneras específicas, junto con una descripción breve. En cada ejemplo, también se proporciona el contenido de un archivo teradata-input.sql o hive-input.sql y del archivo bq-output.sql para que puedas comparar los efectos de un YAML de configuración en la traducción de una consulta en SQL de BigQuery.

En los siguientes ejemplos, se usa Teradata o Hive como el dialecto de entrada de SQL y BigQuery SQL como el dialecto de salida. En los siguientes ejemplos, también se usa testdb como la base de datos predeterminada, y testschema como la ruta de búsqueda del esquema.

Cambia mayúsculas y minúsculas del nombre del objeto

En el siguiente YAML de configuración, se cambia las mayúsculas o minúsculas de los nombres de objetos:

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

Una traducción de SQL con este archivo YAML de configuración podría tener el siguiente aspecto:

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

Haz que la tabla sea temporal

En el siguiente YAML de configuración, se cambia una tabla normal a una tabla temporal:

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

Una traducción de SQL con este archivo YAML de configuración podría tener el siguiente aspecto:

`teradata-input.sql`	create table x(a int);
`bq-output.sql`	CREATE TEMPORARY TABLE x ( a INT64 ) ;

Haz que la tabla sea efímera

En el siguiente YAML de configuración, se cambia una tabla normal a una tabla efímera con un vencimiento de 60 segundos.

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

Una traducción de SQL con este archivo YAML de configuración podría tener el siguiente aspecto:

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

Establece el vencimiento de la partición

En el siguiente YAML de configuración, se cambia el vencimiento de una tabla particionada a 1 día:

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

Una traducción de SQL con este archivo YAML de configuración podría tener el siguiente aspecto:

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

Cambia la ubicación o el formato externos de una tabla

En el siguiente YAML de configuración, se cambia la ubicación externa y formación de una tabla:

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

Una traducción de SQL con este archivo YAML de configuración podría tener el siguiente aspecto:

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

Configura o cambia la descripción de la tabla

En el siguiente YAML de configuración, se establece la descripción de una tabla:

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

Una traducción de SQL con este archivo YAML de configuración podría tener el siguiente aspecto:

`teradata-input.sql`	create table x(a int);
`bq-output.sql`	CREATE TABLE testdb.testschema.x ( a INT64 ) OPTIONS( description='Example description.' );

Configura o cambia la partición de la tabla

En el siguiente YAML de configuración, se cambia el esquema de partición de una tabla:

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

Una traducción de SQL con este archivo YAML de configuración podría tener el siguiente aspecto:

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

Configura o cambia el agrupamiento en clústeres de la tabla

En el siguiente YAML de configuración de YAML, se cambia el esquema de agrupamiento en clústeres de una tabla:

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

Una traducción de SQL con este archivo YAML de configuración podría tener el siguiente aspecto:

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

Cambia el tipo de un atributo de columna

En el siguiente YAML de configuración, se cambia el tipo de datos para un atributo de una columna:

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

Puedes transformar el tipo de datos de origen a cualquiera de los tipos de atributos de destino admitidos.

Una traducción de SQL con este archivo YAML de configuración podría tener el siguiente aspecto:

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

Agrega una conexión al data lake externo

En la siguiente configuración de YAML, se marca la tabla de origen como una tabla externa que apunta a los datos almacenados en un data lake externo, especificado por una conexión de data lake.

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

Una traducción de SQL con este archivo YAML de configuración podría tener el siguiente aspecto:

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

Cambia la codificación de caracteres de un archivo de entrada

De forma predeterminada, el servicio de migración de BigQuery intenta detectar automáticamente la codificación de caracteres de los archivos de entrada. En los casos en los que el servicio de migración de BigQuery podría identificar de forma incorrecta la codificación de un archivo, puedes usar el YAML de configuración para especificar la codificación de caracteres de forma explícita.

El siguiente YAML de configuración especifica la codificación explícita de caracteres del archivo de entrada como ISO-8859-1.

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

Conversión de tipo global

El siguiente YAML de configuración cambia un tipo de datos a otro en todas las secuencias de comandos y especifica un tipo de datos de origen para evitar en la secuencia de comandos transpilada. Esto es diferente de la configuración Cambiar el tipo de atributo de la columna, en la que solo se cambia el tipo de datos para un solo atributo.

BigQuery admite las siguientes conversiones de tipo de datos:

De DATETIME a TIMESTAMP
De TIMESTAMP a DATETIME
De TIMESTAMP WITH TIME ZONE a DATETIME
De CHAR a VARCHAR

En el siguiente ejemplo, el YAML de configuración convierte DATETIME en TIMESTAMP.

type: experimental_object_rewriter
global:
  typeConvert:
    timestamp: DATETIME

Una traducción de SQL con este archivo YAML de configuración podría tener el siguiente aspecto:

`teradata-input.sql`	create table x(a timestamp);
`bq-output.sql`	CREATE TABLE x ( a TIMESTAMP ) ;

Elige la modificación de una sentencia

En el siguiente YAML de configuración, se cambia la proyección en estrella, las cláusulas GROUP BY y ORDER BY en las sentenciasSELECT.

starProjection admite los siguientes parámetros de configuración:

ALLOW
PRESERVE (predeterminada)
EXPAND

groupBy y orderBy admiten los siguientes parámetros de configuración:

EXPRESSION
ALIAS
INDEX

En el siguiente ejemplo, el YAML de configuración configura la proyección en estrella en EXPAND.

type: experimental_statement_rewriter
select:
  starProjection: EXPAND

Una traducción de SQL con este archivo YAML de configuración podría tener el siguiente aspecto:

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

Especificación de UDF

En el siguiente YAML de configuración, se especifica la firma de las funciones definidas por el usuario (UDF) que se usan en las secuencias de comandos de origen. Al igual que los archivos ZIP de metadatos, las definiciones de UDF pueden ayudar a producir una traducción más precisa de las secuencias de comandos de entrada.

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

Una traducción de SQL con este archivo YAML de configuración podría tener el siguiente aspecto:

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

Configura la rigurosidad de precisión decimal

De forma predeterminada, el servicio de migración de BigQuery aumenta la precisión numérica a la precisión más alta disponible para una escala determinada. El siguiente YAML de configuración anula este comportamiento con la configuración de la rigurosidad de precisión para retener la precisión decimal de la declaración de origen.

type: experimental_statement_rewriter
common:
  decimalPrecision: STRICT

Una traducción de SQL con este archivo YAML de configuración podría tener el siguiente aspecto:

`teradata-input.sql`	create table x(a decimal(3,0));
`bq-output.sql`	CREATE TABLE x ( a NUMERIC(3) ) ;

Asignación de nombre de salida

Puedes usar el YAML de configuración para asignar nombres de objetos SQL. Puedes cambiar diferentes partes del nombre según el objeto que se asigne.

Asignación de nombre estático

Usa la asignación de nombres estáticos para asignar el nombre de una entidad. Si solo deseas cambiar partes específicas del nombre y, al mismo tiempo, mantener otras partes del nombre iguales, incluye solo las partes que se deben cambiar.

El siguiente YAML de configuración cambia el nombre de la tabla de 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 traducción de SQL con este archivo YAML de configuración podría tener el siguiente aspecto:

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

Asignación dinámica de nombres

Usa la asignación dinámica de nombres para cambiar varios objetos al mismo tiempo y crear nombres nuevos basados en los objetos asignados.

En el siguiente YAML de configuración, se cambia el nombre de todas las tablas cuando se agrega el prefijo stg_ a los que pertenecen al esquema staging y, luego, se mueven a esas tablas al esquema production.

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

Una traducción de SQL con este archivo YAML de configuración podría tener el siguiente aspecto:

`teradata-input.sql`	create table staging.my_table(a int);
`bq-output.sql`	CREATE TABLE production.stg_my_table ( a INT64 ) ;

Especifica la base de datos predeterminada y la ruta de búsqueda del esquema

El siguiente YAML de configuración especifica una base de datos predeterminada y una ruta de búsqueda de esquema.

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

Una traducción de SQL con este archivo YAML de configuración podría tener el siguiente aspecto:

`teradata-input.sql`	SELECT * FROM database.table SELECT * FROM table1
`bq-output.sql`	SELECT * FROM myproject.database.table. SELECT * FROM myproject.myschema1.table1

Reescritura del nombre del resultado global

El siguiente YAML de configuración cambia los nombres de salida de todos los objetos (base de datos, esquema, relación y atributos) en la secuencia de comandos según las reglas configuradas.

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

Una traducción de SQL con este archivo YAML de configuración podría tener el siguiente aspecto:

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

Aplica varias opciones de configuración de YAML

Cuando especificas un archivo YAML de configuración en una traducción de SQL por lotes o interactiva, puedes seleccionar varios archivos YAML de configuración en un solo trabajo de traducción para reflejar varias transformaciones. Si varias opciones de configuración entran en conflicto, una transformación puede anular a otra. Recomendamos usar diferentes tipos de ajustes de configuración en cada archivo para evitar transformaciones conflictivas en el mismo trabajo de traducción.

En el siguiente ejemplo, se enumeran dos archivos YAML de configuración independientes que se proporcionaron para un solo trabajo de traducción de SQL, uno a fin de cambiar el atributo de una columna y el otro para configurar la tabla como temporal:

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 traducción de SQL con estos dos archivos YAML de configuración podría tener el siguiente aspecto:

`teradata-input.sql`	create table x(a int);
`bq-output.sql`	CREATE TEMPORARY TABLE x ( a NUMERIC(31, 2) ) ;

Transforma traducciones de SQL con archivos YAML de configuración

Requisitos del archivo YAML de configuración

Lineamientos para crear un archivo YAML de configuración

Encabezado

Selección de entidad

Base de datos predeterminada

Tipos de atributos de destino admitidos

Ejemplos de YAML de configuración

Cambia mayúsculas y minúsculas del nombre del objeto

Haz que la tabla sea temporal

Haz que la tabla sea efímera

Establece el vencimiento de la partición

Cambia la ubicación o el formato externos de una tabla

Configura o cambia la descripción de la tabla

Configura o cambia la partición de la tabla

Configura o cambia el agrupamiento en clústeres de la tabla

Cambia el tipo de un atributo de columna

Agrega una conexión al data lake externo

Cambia la codificación de caracteres de un archivo de entrada

Conversión de tipo global

Elige la modificación de una sentencia

Especificación de UDF

Configura la rigurosidad de precisión decimal

Asignación de nombre de salida

Asignación de nombre estático

Asignación dinámica de nombres

Especifica la base de datos predeterminada y la ruta de búsqueda del esquema

Reescritura del nombre del resultado global

Aplica varias opciones de configuración de YAML