Transformer des traductions SQL à l'aide de fichiers YAML de configuration

Ce document explique comment utiliser des fichiers de configuration YAML pour transformer du code SQL lors de sa migration vers BigQuery. Il fournit des instructions permettant de créer vos propres fichiers YAML de configuration ainsi que des exemples pour les différentes transformations de traduction compatibles avec cette fonctionnalité.

Lorsque vous utilisez la traduction SQL interactive de BigQuery ou effectuez une traduction SQL par lot, vous pouvez fournir des fichiers de configuration YAML pour modifier une traduction de requête SQL. L'utilisation des fichiers de configuration YAML vous permet de personnaliser davantage la traduction des requêtes SQL à partir de votre base de données source.

Vous pouvez spécifier un fichier YAML de configuration à utiliser dans une traduction SQL de différentes manières :

Si vous effectuez une traduction SQL par lot, placez la configuration YAML dans le même bucket Cloud Storage que les fichiers SQL d'entrée.
Si vous utilisez le traducteur SQL interactif, spécifiez le chemin d'accès au fichier de configuration ou l'ID du job de traduction par lot dans les paramètres de traduction.
Si vous utilisez le client Python de traduction par lot, placez le fichier de configuration YAML dans le dossier d'entrée de traduction local.

La traduction SQL interactive, la traduction SQL par lot et le client Python de traduction par lot acceptent l'utilisation de plusieurs fichiers YAML de configuration dans une seule tâche de traduction. Consultez la section Appliquer plusieurs configurations YAML pour en savoir plus.

Configuration du fichier YAML de configuration

Avant de créer un fichier YAML de configuration, consultez les informations suivantes pour vous assurer que votre fichier YAML est compatible avec le service de migration BigQuery :

Vous devez importer les fichiers de configuration YAML dans le répertoire racine du bucket Cloud Storage contenant vos fichiers d'entrée de traduction SQL. Pour en savoir plus sur la création de buckets et l'importation de fichiers dans Cloud Storage, consultez les sections Créer des buckets et Importer des objets à partir d'un système de fichiers.
La taille d'un seul fichier YAML de configuration ne doit pas dépasser 1 Mo.
La taille totale de tous les fichiers de configuration YAML utilisés dans un seul job de traduction SQL ne doit pas dépasser 4 Mo.
Si vous utilisez la syntaxe regex pour la mise en correspondance des noms, utilisez RE2/J.
Tous les noms de fichiers YAML de configuration doivent inclure une extension .config.yaml (par exemple, change-case.config.yaml).
- config.yaml seul n'est pas un nom valide pour le fichier de configuration.

Consignes pour la création d'un fichier YAML de configuration

Cette section fournit des instructions générales pour créer un fichier YAML de configuration :

Chaque fichier de configuration doit contenir un en-tête spécifiant le type de configuration. Le type object_rewriter permet de spécifier des traductions SQL dans un fichier YAML de configuration. L'exemple suivant utilise le type object_rewriter pour transformer la casse d'un nom :

type: object_rewriter
global:
  case:
    all: UPPERCASE

Sélection d'entités

Pour effectuer des transformations spécifiques à l'entité, spécifiez l'entité dans le fichier de configuration. Toutes les propriétés match sont facultatives. Utilisez uniquement les propriétés match nécessaires à une transformation. Le YAML de configuration suivante expose les propriétés à mettre en correspondance afin de sélectionner des entités spécifiques :

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

Description de chaque propriété match :

db : composant project_id
schema : composant de l'ensemble de données
relation : composant de la table
attribute : composant de la colonne. Uniquement valide pour la sélection d'attributs
dbRegex : correspond à une propriété db avec une expression régulière (preview).
schemaRegex : met en correspondance les propriétés schema et les expressions régulières (preview).
relationRegex : correspond aux propriétés relation avec des expressions régulières (preview).
attributeRegex : correspond aux propriétés attribute avec des expressions régulières. Uniquement valide pour la sélection d'attributs (preview).

Par exemple, le fichier YAML de configuration suivant spécifie les propriétés match pour sélectionner la table testdb.acme.employee en vue d'une transformation de table temporaire.

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

Vous pouvez utiliser les propriétés dbRegex, schemaRegex, relationRegex et attributeRegex pour spécifier des expressions régulières afin de sélectionner un sous-ensemble d'entités. L'exemple suivant remplace toutes les relations du schéma tmp_schema dans testdb par des noms temporaires, à condition que leur nom commence par tmp_ :

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

Les propriétés littérales et regex sont mises en correspondance de manière non sensible à la casse. Vous pouvez appliquer la correspondance sensible à la casse en utilisant regex avec une option i désactivée, comme illustré dans l'exemple suivant :

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

Vous pouvez également spécifier des entités complètes à l'aide d'une syntaxe de chaîne courte équivalente. Une syntaxe de chaîne courte attend exactement 3 segments de nom (pour la sélection de relation) ou 4 (pour la sélection d'attributs) délimités par des points, comme dans l'exemple testdb.acme.employee. Les segments sont ensuite interprétés en interne comme s'ils étaient transmis respectivement en tant que db, schema, relation et attribute. Cela signifie que les noms sont mis en correspondance de manière littérale. Les expressions régulières ne sont donc pas autorisées dans une syntaxe courte. L'exemple suivant montre comment utiliser la syntaxe de chaîne courte pour spécifier une entité complète dans un fichier YAML de configuration :

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

Si une table contient un point, vous ne pouvez pas spécifier le nom à l'aide d'une syntaxe courte. Dans ce cas, vous devez utiliser une correspondance d'objet. L'exemple suivant remplace la table testdb.acme.stg.employee par une table temporaire :

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

La configuration YAML accepte key comme alias de match.

Base de données par défaut

Certains dialectes SQL d'entrée, y compris Teradata, ne sont pas compatibles avec database-name dans le nom complet. Dans ce cas, le moyen le plus simple de mettre en correspondance les entités consiste à omettre la propriété db dans match.

Toutefois, vous pouvez définir la propriété default_database du service de migration BigQuery et utiliser cette base de données par défaut dans le fichier match.

Types d'attributs cibles acceptés

Vous pouvez utiliser le fichier de configuration YAML pour effectuer des transformations de type d'attribut, où vous transformez le type de données d'une colonne du type de source en type de cible. Le fichier YAML de configuration accepte les types de cibles suivants :

BOOLEAN
TINYINT
SMALLINT
INTEGER
BIGINT
FLOAT
DOUBLE
NUMERIC (compatible avec la précision et l'échelle facultatives, telles que NUMERIC(18, 2))
TIME
TIMETZ
DATE
DATETIME
TIMESTAMP
TIMESTAMPTZ
CHAR (compatible avec la précision facultative, par exemple CHAR(42))
VARCHAR (compatible avec la précision facultative, par exemple VARCHAR(42))

Exemples YAML de configuration

Cette section fournit des exemples permettant de créer divers fichiers YAML de configuration à utiliser avec vos traductions SQL. Chaque exemple décrit la syntaxe YAML permettant de transformer votre traduction SQL de manière spécifique, ainsi qu'une brève description. Chaque exemple fournit également le contenu d'un fichier teradata-input.sql ou hive-input.sql et d'un fichier bq-output.sql afin que vous puissiez comparer les effets d'une configuration YAML sur une requête de traduction SQL BigQuery.

Les exemples suivants utilisent Teradata ou Hive comme dialecte d'entrée SQL et BigQuery SQL comme dialecte de sortie. Les exemples suivants utilisent également testdb comme base de données par défaut et testschema comme chemin d'accès de recherche du schéma.

Modifier la casse du nom d'objet

La configuration YAML suivante modifie les majuscules ou les minuscules des noms d'objet :

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

Une traduction SQL avec ce fichier YAML de configuration peut se présenter comme suit :

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

Rendre une table temporaire

La configuration YAML suivante modifie une table standard en table temporaire :

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

Une traduction SQL avec ce fichier YAML de configuration peut se présenter comme suit :

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

Rendre une table éphémère

La configuration YAML suivante convertit une table standard en table éphémère avec un délai d'expiration de 60 secondes.

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

Une traduction SQL avec ce fichier YAML de configuration peut se présenter comme suit :

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

Définir le délai d'expiration d'une partition

La configuration YAML suivante modifie le délai d'expiration d'une table partitionnée sur un jour :

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

Une traduction SQL avec ce fichier YAML de configuration peut se présenter comme suit :

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

Modifier l'emplacement ou le format externe d'une table

La configuration YAML suivante modifie l'emplacement et la formation externes d'une table :

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

Une traduction SQL avec ce fichier YAML de configuration peut se présenter comme suit :

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

Définir ou modifier la description du tableau

La configuration YAML suivante définit la description d'une table :

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

Une traduction SQL avec ce fichier YAML de configuration peut se présenter comme suit :

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

Définir ou modifier le partitionnement des tables

La configuration YAML suivante modifie le schéma de partitionnement d'une table :

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

Une traduction SQL avec ce fichier YAML de configuration peut se présenter comme suit :

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

Définir ou modifier le clustering de tables

La configuration YAML suivante modifie le schéma de clustering d'une table :

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

Une traduction SQL avec ce fichier YAML de configuration peut se présenter comme suit :

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

Modifier le type d'un attribut de colonne

La configuration YAML suivante modifie le type de données d'un attribut d'une colonne :

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

Vous pouvez transformer le type de données source en n'importe quel type d'attribut cible compatible.

Une traduction SQL avec ce fichier YAML de configuration peut se présenter comme suit :

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

Ajouter une connexion au lac de données externe

La configuration YAML suivante marque la table source en tant que table externe qui pointe vers des données stockées dans un lac de données externe, spécifiée par une connexion de lac de données.

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

Une traduction SQL avec ce fichier YAML de configuration peut se présenter comme suit :

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

Modifier l'encodage des caractères d'un fichier d'entrée

Par défaut, le service de migration BigQuery tente de détecter automatiquement l'encodage des caractères des fichiers d'entrée. Dans les cas où BigQuery Migration Service peut mal identifier l'encodage d'un fichier, vous pouvez utiliser un fichier de configuration YAML pour spécifier explicitement l'encodage des caractères.

La configuration YAML suivante spécifie l'encodage de caractères explicite du fichier d'entrée sous la forme ISO-8859-1.

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

Conversion de type global

La configuration YAML suivante modifie un type de données en un autre pour tous les scripts et spécifie un type de données source à éviter dans le script transcompilé. Cela diffère de la configuration Modifier le type d'un attribut de colonne, où seul le type de données d'un seul attribut est modifié.

BigQuery accepte les conversions des types de données suivants :

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

Dans l'exemple suivant, le fichier de configuration YAML convertit le type de données DATETIME en TIMESTAMP.

type: experimental_object_rewriter
global:
  typeConvert:
    timestamp: DATETIME

Une traduction SQL avec ce fichier YAML de configuration peut se présenter comme suit :

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

Sélectionner une modification de déclaration

La configuration YAML suivante modifie la projection en étoile, GROUP BY, et les clauses ORDER BY dans les instructions SELECT.

starProjection accepte les configurations suivantes :

ALLOW
PRESERVE (par défaut)
EXPAND

groupBy et orderBy acceptent les configurations suivantes :

EXPRESSION
ALIAS
INDEX

Dans l'exemple suivant, le fichier de configuration YAML configure la projection d'étoiles sur EXPAND.

type: experimental_statement_rewriter
select:
  starProjection: EXPAND

Une traduction SQL avec ce fichier YAML de configuration peut se présenter comme suit :

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

Spécification des fonctions définies par l'utilisateur

Le code YAML de configuration suivant spécifie la signature des fonctions définies par l'utilisateur utilisées dans les scripts sources. Tout comme les fichiers ZIP des métadonnées, les définitions des fonctions définies par l'utilisateur peuvent contribuer à produire une traduction plus précise des scripts d'entrée.

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

Une traduction SQL avec ce fichier YAML de configuration peut se présenter comme suit :

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

Définir la rigueur de la précision décimale

Par défaut, le service de migration BigQuery augmente la précision numérique à la plus haute précision disponible pour une échelle donnée. La configuration YAML suivante remplace ce comportement en configurant la rigueur de la précision pour conserver la précision décimale de l'instruction source.

type: experimental_statement_rewriter
common:
  decimalPrecision: STRICT

Une traduction SQL avec ce fichier YAML de configuration peut se présenter comme suit :

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

Mappage des noms de sortie

Vous pouvez utiliser la configuration YAML pour mapper les noms d'objets SQL. Vous pouvez modifier différentes parties du nom en fonction de l'objet mappé.

Mappage des noms statiques

Utilisez le mappage de noms statique pour mapper le nom d'une entité. Si vous souhaitez ne modifier que certaines parties du nom tout en conservant les autres parties, n'incluez que les parties à modifier.

Le fichier YAML de configuration suivant remplace le nom de la table my_db.my_schema.my_table par 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"

Une traduction SQL avec ce fichier YAML de configuration peut se présenter comme suit :

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

Mappage de nom dynamique

Le mappage de noms dynamique permet de modifier plusieurs objets en même temps et de créer des noms basés sur les objets mappés.

La configuration YAML suivante modifie le nom de toutes les tables en ajoutant le préfixe stg_ à celles appartenant au schéma staging, puis déplace ces tables vers le schéma production.

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

Une traduction SQL avec ce fichier YAML de configuration peut se présenter comme suit :

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

Spécifier le chemin de recherche de la base de données et du schéma par défaut

La configuration YAML suivante spécifie une base de données par défaut et un chemin de recherche de schéma.

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

Une traduction SQL avec ce fichier YAML de configuration peut se présenter comme suit :

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

Réécriture du nom de sortie global

La configuration YAML suivante modifie les noms de sortie de tous les objets (base de données, schéma, relation et attributs) dans le script conformément aux règles configurées.

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

Une traduction SQL avec ce fichier YAML de configuration peut se présenter comme suit :

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

Appliquer plusieurs configurations YAML

Lorsque vous spécifiez un fichier YAML de configuration dans une traduction SQL par lot ou en SQL interactif, vous pouvez sélectionner plusieurs fichiers YAML de configuration dans un seul job de traduction pour refléter plusieurs transformations. Si plusieurs configurations sont en conflit, une transformation peut remplacer une autre. Nous vous recommandons d'utiliser différents types de paramètres de configuration dans chaque fichier pour éviter les conflits de transformations dans le même job de traduction.

L'exemple suivant répertorie deux fichiers de configuration YAML distincts fournis pour un seul job de traduction SQL, l'un pour modifier l'attribut d'une colonne et l'autre pour définir la table comme temporaire :

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

Une traduction SQL avec ces deux fichiers YAML de configuration peut se présenter comme suit :

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

Transformer des traductions SQL à l'aide de fichiers YAML de configuration

Configuration du fichier YAML de configuration

Consignes pour la création d'un fichier YAML de configuration

En-tête

Sélection d'entités

Base de données par défaut

Types d'attributs cibles acceptés

Exemples YAML de configuration

Modifier la casse du nom d'objet

Rendre une table temporaire

Rendre une table éphémère

Définir le délai d'expiration d'une partition

Modifier l'emplacement ou le format externe d'une table

Définir ou modifier la description du tableau

Définir ou modifier le partitionnement des tables

Définir ou modifier le clustering de tables

Modifier le type d'un attribut de colonne

Ajouter une connexion au lac de données externe

Modifier l'encodage des caractères d'un fichier d'entrée

Conversion de type global

Sélectionner une modification de déclaration

Spécification des fonctions définies par l'utilisateur

Définir la rigueur de la précision décimale

Mappage des noms de sortie

Mappage des noms statiques

Mappage de nom dynamique

Spécifier le chemin de recherche de la base de données et du schéma par défaut

Réécriture du nom de sortie global

Appliquer plusieurs configurations YAML