Transformer des traductions SQL avec YAML

Les fichiers de configuration YAML vous permettent de personnaliser la façon dont BigQuery traduit le code SQL de votre base de données source lors d'une migration. Le service de migration BigQuery accepte deux types de fichiers de configuration YAML : les fichiers de configuration de traduction *.config.yaml qui fournissent des règles déterministes au moteur de traduction, et les fichiers de configuration basés sur Gemini *.ai_config.yaml qui fournissent des règles en langage naturel à Gemini.

Pour personnaliser une traduction SQL avec des fichiers YAML de configuration, procédez comme suit :

  1. Créer des fichiers de configuration : créez des fichiers de configuration de traduction déterministes *.config.yaml, des fichiers de configuration basés sur Gemini *.ai_config.yaml ou les deux types de fichiers de configuration pour combiner des règles structurelles avec la personnalisation Gemini.
  2. Fournir les fichiers à un job de traduction : envoyez les fichiers de configuration lorsque vous traduisez une seule requête ou des fichiers SQL par lot.
  3. Examinez les résultats de la traduction : recherchez le code SQL traduit et les suggestions Gemini dans la réponse lorsque vous traduisez une seule requête, ou dans Cloud Storage lorsque vous traduisez des fichiers SQL par lot.

Types de fichiers YAML de configuration

Le tableau suivant compare les deux types de fichiers YAML de configuration :

Fonctionnalité Configuration de la traduction (*.config.yaml) Configuration basée sur Gemini (*.ai_config.yaml)
Application des règles Les règles sont appliquées par le moteur de traduction en tant que règles déterministes sur le code SQL analysé. Ainsi, la même entrée et la même configuration produisent toujours le même résultat. Les règles sont appliquées par Gemini sous forme d'instructions en langage naturel et d'exemples appliqués au texte SQL. Les résultats peuvent varier d'une exécution à l'autre. Par conséquent, examinez et vérifiez toujours les suggestions avant de les utiliser.
Éléments pouvant être transformés Le moteur de traduction peut transformer tous les objets ou paramètres qu'il reconnaît. Cela inclut les noms et les types de bases de données, de schémas, de tables, de colonnes et de fonctions, les propriétés de table telles que le partitionnement, le clustering et l'expiration, les valeurs par défaut de session, les signatures UDF, les optimisations et les résultats d'instructions tels que le style GROUP BY et SELECT *. Gemini peut modifier n'importe quelle transformation SQL que vous pouvez décrire en langage naturel. Cela inclut la mise en forme et les commentaires, le remplacement des modèles, la réécriture des constructions que le moteur ne traduit pas comme vous le souhaitez et la génération d'une explication de la traduction.
Conditions requises
  • Le code SQL d'entrée doit être analysé dans le dialecte source.
  • Les règles ne peuvent pas agir sur le texte du code SQL d'entrée que l'analyseur ne reconnaît pas, comme les espaces réservés ${VAR} ou la syntaxe du modèle.
  • Utilisez une règle SOURCE_SQL dans les fichiers *.ai_config.yaml ou votre propre prétraitement pour supprimer d'abord ce texte.
  • Vous devez activer les suggestions de l'IA Gemini pour le job.
  • Le job doit s'exécuter dans un emplacement de traitement compatible.
  • L'utilisation de Gemini est sans frais jusqu'à une certaine limite.
Où le résultat s'affiche-t-il ? Le résultat apparaît dans le code SQL traduit lui-même, par exemple dans la réponse de traduction ou dans le répertoire de sortie sql/ de Cloud Storage. Le résultat s'affiche sous la forme de suggestions Gemini distinctes dans la console Google Cloud , dans des champs de suggestion ou des fichiers de sortie distincts dans la Google Cloud CLI et le serveur MCP, ou dans des répertoires de sortie *_suggestion/ distincts dans Cloud Storage. Pour en savoir plus, consultez Où trouver les résultats de traduction.
Nom de fichier Les noms de fichier doivent se terminer par .config.yaml, par exemple rename-tables.config.yaml. Les noms de fichier doivent se terminer par .ai_config.yaml, par exemple style-rules.ai_config.yaml.
Le service de traduction ignore tout fichier YAML de l'entrée qui ne se termine pas par .config.yaml ou .ai_config.yaml, et signale le fichier ignoré dans le journal de traduction.
Référence Pour en savoir plus, consultez les sections Consignes pour créer un fichier YAML de configuration et Exemples de fichiers YAML de configuration sur cette page. Pour en savoir plus, consultez Créer un fichier de configuration YAML basé sur Gemini sur cette page.

Mode d'application des deux types de règles

Un job de traduction applique les règles de configuration dans l'ordre suivant :

  1. Gemini applique les règles SOURCE_SQL des fichiers *.ai_config.yaml au code SQL d'entrée d'origine pour produire un code SQL source réécrit.
  2. Le moteur de traduction traduit le code SQL source réécrit à l'étape précédente (ou le code SQL d'entrée d'origine si aucune règle SOURCE_SQL ne s'applique) et applique toutes les règles des fichiers *.config.yaml pour produire le code SQL traduit.
  3. Gemini applique les règles TARGET_SQL dans les fichiers *.ai_config.yaml au code SQL traduit de l'étape précédente pour produire un code SQL cible réécrit. Si vos fichiers *.ai_config.yaml incluent des règles TRANSLATION_EXPLANATION, Gemini génère également une explication textuelle du code SQL traduit.

Nous vous recommandons d'utiliser des règles *.config.yaml pour les modifications structurelles reproductibles, telles que le changement de nom des objets, le mappage des types et la définition du partitionnement. Nous vous recommandons d'utiliser des règles *.ai_config.yaml pour les modifications que le moteur ne peut pas exprimer ou pour nettoyer les entrées qui ne sont pas analysées. Vous pouvez utiliser les deux types de fichiers de configuration dans le même job.

Fournir des fichiers de configuration à une traduction

La console Google Cloud , la gcloud CLI, l'API BigQuery Migration et le serveur MCP du service de migration BigQuery permettent d'utiliser plusieurs fichiers de configuration dans un même job de traduction. Pour en savoir plus, consultez Appliquer plusieurs configurations YAML.

Pour fournir des fichiers de configuration à un job de traduction, sélectionnez l'une des options suivantes :

Console

  • Pour utiliser des fichiers de configuration lors de la traduction d'une seule requête, spécifiez-les dans les paramètres de traduction du traducteur interactif :
  • Pour utiliser des fichiers de configuration lors de la traduction par lot de fichiers SQL, procédez comme suit :

gcloud

  • Lorsque vous utilisez la commande gcloud alpha bq migration translate, utilisez l'option --translation-config-files pour spécifier des fichiers *.config.yaml ou *.ai_config.yaml.
  • Lorsque vous utilisez la commande gcloud alpha bq migration translate-batch, effectuez l'une des opérations suivantes :
    • *.config.yaml : incluez les fichiers du répertoire d'entrée que vous transmettez à --source-gcs-uris ou --source-local-dirs, ou transmettez des fichiers individuels avec l'option --source-gcs-files ou --source-local-files.
    • *.ai_config.yaml : incluez les fichiers du répertoire d'entrée que vous transmettez à --source-gcs-uris ou --source-local-dirs, ou transmettez des fichiers individuels avec l'option --source-gcs-files ou --source-local-files, puis ajoutez l'option --enable-ai-suggestion.

API

Dans l'API BigQuery Migration, que vous traduisiez une seule requête ou des fichiers SQL par lot, procédez comme suit :

  • *.config.yaml : importez les fichiers sous le chemin d'entrée dans source_spec.base_uri, transmettez une entrée gcs_file_path dans source_spec lorsque le fichier n'est pas sous base_uri, ou transmettez une entrée literal intégrée dans source_spec.
  • *.ai_config.yaml : fournissez les fichiers dans source_spec sous base_uri, en tant qu'entrée gcs_file_path lorsque le fichier n'est pas sous base_uri, ou en tant qu'entrée literal intégrée, et incluez "suggestion" dans target_types (par exemple, "target_types": ["sql", "suggestion"]).

MCP

Dans le serveur MCP du service de migration BigQuery :

  • Pour traduire une seule requête (translate_query), procédez comme suit :
    • *.config.yaml : transmet les entrées translationConfigs intégrées.
    • *.ai_config.yaml : transmet les entrées aiSuggestionConfigs intégrées.
  • Pour traduire des fichiers SQL par lot (translate_batch_queries), procédez comme suit :
    • *.config.yaml : importez les fichiers sous sourceBaseUri ou listez-les dans configurationFilePaths lorsqu'ils ne se trouvent pas sous sourceBaseUri.
    • *.ai_config.yaml : importez les fichiers sous sourceBaseUri ou listez-les dans configurationFilePaths lorsqu'ils ne se trouvent pas sous sourceBaseUri, puis définissez enableAiSuggestions sur true.

Où trouver les résultats de traduction ?

Selon les fichiers de configuration que vous fournissez, un job de traduction peut produire les résultats suivants :

  • SQL traduit par le moteur de traduction après l'application des règles dans les fichiers *.config.yaml.
  • Code SQL source réécrit par Gemini après l'application des règles SOURCE_SQL dans les fichiers *.ai_config.yaml.
  • SQL cible réécrit par Gemini après l'application de TARGET_SQL règles dans les fichiers *.ai_config.yaml.
  • Une explication de la traduction par Gemini lorsque vos fichiers *.ai_config.yaml incluent des règles TRANSLATION_EXPLANATION ou lorsque vous demandez une explication à la demande.

L'emplacement de ces résultats dépend de la façon dont vous exécutez la traduction :

  • Pour trouver les résultats lorsque vous traduisez une seule requête dans la consoleGoogle Cloud ou la gcloud CLI, consultez Explorer le résultat de la traduction dans le guide du traducteur SQL interactif.
  • Pour trouver les résultats lorsque vous traduisez des fichiers SQL par lot dans Cloud Storage ou les examiner dans la console Google Cloud , consultez Explorer le résultat de la traduction dans le guide du traducteur SQL par lot.
  • Pour trouver les résultats lorsque vous utilisez l'API BigQuery Migration, consultez Récupérer les résultats dans le guide de l'API BigQuery Migration. Pour les traductions à requête unique, ajoutez les chemins de sortie de votre choix (sql/<var>PATH</var>, source_sql_query_customization_suggestion/<var>PATH</var>, target_sql_query_customization_suggestion/<var>PATH</var> ou translation_explanation_suggestion/<var>PATH</var>) à target_return_literals pour les recevoir dans translatedLiterals.
  • Pour trouver les résultats lorsque vous utilisez le serveur MCP du service de migration BigQuery, consultez Utiliser le serveur MCP du service de migration BigQuery. Les outils translate_query et get_translation renvoient translatedQuery, sourceSqlSuggestion et targetSqlSuggestion, et l'outil explain_translation renvoie une explication de la traduction.

Exigences concernant le fichier de traduction *.config.yaml

Un fichier de traduction *.config.yaml doit répondre aux exigences suivantes pour être compatible avec le service de migration BigQuery :

  • Lorsque vous importez des fichiers *.config.yaml sous le chemin d'accès Cloud Storage d'entrée pour un job de traduction par lot, les fichiers de configuration placés n'importe où sous le chemin d'accès d'entrée s'appliquent à l'ensemble du job. Il n'est pas nécessaire qu'ils se trouvent au niveau racine ni qu'ils soient copiés dans chaque sous-répertoire. 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 *.config.yaml ne doit pas dépasser 1 Mo.
  • La taille totale de tous les fichiers *.config.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 correspondance des noms, utilisez RE2/J.
  • Tous les noms de fichiers de configuration de traduction doivent se terminer par .config.yaml, par exemple change-case.config.yaml. config.yaml seul n'est pas un nom valide pour le fichier de configuration.

Instructions pour créer un fichier YAML de configuration

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

Chaque fichier *.config.yaml doit inclure un en-tête type de premier niveau qui spécifie la catégorie des règles de configuration dans le fichier :

  • object_rewriter : réécrit les propriétés relation (telles que temporary, ephemeral, partitionLifetime, external, description, partition et clustering) et les types de données attribute (type).
  • experimental_object_rewriter : réécrit les noms des objets de sortie avec outputName dans global, database, schema, relation, attribute, function, index, sequence, variable et projection, et convertit les types de données de manière globale avec global.typeConvert. Pour en savoir plus, consultez Mappage des noms de sortie et Conversion de type globale.
  • environment : définit les valeurs par défaut au niveau de la session sous session, telles que defaultDatabase, schemaSearchPath et dateFormat.
  • metadata : déclare les signatures de fonction définie par l'utilisateur (UDF) (udfs) et supprime les préfixes, suffixes ou modèles d'expression régulière du nom d'entrée lors de la recherche de métadonnées (nameAlias).
  • statement_rewriter : personnalise le résultat de l'instruction, par exemple l'expansion SELECT *, la mise en forme GROUP BY et ORDER BY (select), et la rigueur de la précision décimale ou de la chaîne (common).
  • input_formats : spécifie l'encodage des caractères des fichiers SQL d'entrée (formats).
  • optimizer : active, désactive ou configure les optimisations de requête facultatives (transformations).

L'exemple suivant utilise le type experimental_object_rewriter pour convertir tous les noms d'objets de sortie en majuscules :

type: experimental_object_rewriter
global:
  outputName:
    all:
      case: UPPERCASE

Sélection d'entités

Pour effectuer des transformations spécifiques à l'entité, spécifiez l'entité SQL source dans le fichier de configuration. Toutes les propriétés match sont facultatives. N'utilisez que les propriétés match nécessaires à une transformation. Le champ match est comparé aux noms des objets sources après la résolution des defaultDatabase et schemaSearchPath configurés, sauf dans les règles outputName lorsque global.outputName est également configuré. Pour en savoir plus, consultez Mappage des noms de sortie. Le YAML de configuration suivante expose les propriétés à mettre en correspondance afin de sélectionner des entités spécifiques :

match:
  database: <literal_name>
  schema: <literal_name>
  relation: <literal_name>
  attribute: <literal_name>
  databaseRegex: <regex>
  schemaRegex: <regex>
  relationRegex: <regex>
  attributeRegex: <regex>

Description de chaque propriété match :

  • database ou db : correspond au nom de la base de données source, qui correspond au composant project_id dans BigQuery.
  • schema : correspond au nom du schéma source, qui correspond au composant dataset dans BigQuery.
  • relation : correspond au nom de la table ou de la vue source.
  • attribute : correspond au nom de la colonne source. Cette propriété n'est valide que pour la sélection attribute.
  • databaseRegex ou dbRegex : compare l'intégralité du nom database à une expression régulière non sensible à la casse.
  • schemaRegex : compare l'intégralité du nom schema à une expression régulière non sensible à la casse.
  • relationRegex : met en correspondance l'intégralité du nom relation avec une expression régulière non sensible à la casse.
  • attributeRegex : met en correspondance l'intégralité du nom attribute avec une expression régulière non sensible à la casse. Cette propriété n'est valide que pour la sélection attribute.

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:
    database: testdb
    schema: acme
    relation: employee
  temporary: true

Vous pouvez utiliser les propriétés databaseRegex, 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 sans tenir compte de 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 sous la forme database, schema, relation et attribute. Cela signifie que les noms sont mis en correspondance littéralement. Les expressions régulières ne sont donc pas autorisées dans la 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 le nom d'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:
    database: testdb
    schema: acme
    relation: stg.employee
  temporary: true

Un fichier YAML de configuration accepte également key comme alias pour match. key et match sont compatibles avec les sous-champs de composant de nom explicites, ainsi qu'avec la syntaxe de chaîne courte "database.schema.relation" ou "database.schema.relation.attribute" délimitée par des points. Par convention, key est souvent utilisé avec la syntaxe de chaîne courte et match est utilisé avec des sous-champs explicites.

Base de données par défaut

Certains dialectes SQL d'entrée, y compris Teradata, ne sont pas compatibles avec un nom de base de données dans les identifiants complets (schema.table). Dans ce cas, utilisez un objet match et omettez la propriété database. Vous pouvez également configurer defaultDatabase (par exemple, sous type: environment) afin de pouvoir effectuer une correspondance avec cette base de données par défaut dans les règles match d'objet ou de chaîne courte.

Types d'attributs cibles compatibles

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 source au type cible. Le fichier YAML de configuration accepte les types de cibles suivants :

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

Exemples de configuration YAML

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 traduction de requête SQL BigQuery.

Les exemples suivants utilisent Teradata ou Apache Hive comme dialecte SQL d'entrée et BigQuery SQL comme dialecte de sortie.

Modifier la casse du nom d'objet

La configuration YAML suivante modifie la casse des noms d'objets SQL en UPPERCASE ou LOWERCASE. Vous pouvez définir case sur des objets all ou le remplacer pour des types d'objets spécifiques (database, schema, relation, attribute, function, sequence, index, variable ou projection) :

type: experimental_object_rewriter
global:
  outputName:
    all:
      case: UPPERCASE
    database:
      case: LOWERCASE
    attribute:
      case: LOWERCASE

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

teradata-input.sql
      create table TESTDB.testschema.x(A int);
      select * from TESTDB.testschema.x;
    
bq-output.sql
      CREATE TABLE testdb.TESTSCHEMA.X
      (
        a INT64
      )
      ;
      SELECT
          X.*
        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 testdb.testschema.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 testdb.testschema.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 l'expiration de la 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 testdb.testschema.x(a date, b int) partition by (a);
    
bq-output.sql
    CREATE TABLE testdb.testschema.x
    (
      a DATE,
      b INT64
    )
    PARTITION 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 le format 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 testdb.testschema.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 d'une table

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 testdb.testschema.x(a int);
    
bq-output.sql
    CREATE TABLE testdb.testschema.x
    (
      a INT64
    )
    OPTIONS(
      description='Example description.'
    );
    

Définir ou modifier le partitionnement de tables

La configuration YAML suivante modifie le schéma de partitionnement d'une table à l'aide de add, remove ou set, où set remplace toutes les colonnes de partition existantes :

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 testdb.testschema.x(a date, b int);
    create table testdb.testschema.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
    )
    ;
    

La configuration YAML suivante modifie le schéma de partitionnement d'une table afin qu'elle soit partitionnée par bucket de plage :

type: object_rewriter
relation:
-
  match: "testdb.testschema.mytable"
  partition:
    range_bucket:
      column: "id"
      start: 0
      end: 100
      step: 10
      options:
        require_partition_filter: "TRUE"

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

snowflake-input.sql
      create table testdb.testschema.mytable (
        date timestamp_ntz,
        id number,
        content variant
      );
    
bq-output.sql
      CREATE TABLE TESTDB.TESTSCHEMA.MYTABLE
      (
        DATE DATETIME,
        ID INT64,
        CONTENT JSON
      )
      PARTITION BY RANGE_BUCKET(ID, generate_array(0, 100, 10))
      OPTIONS(
        require_partition_filter=TRUE
      );
    

Définir ou modifier le clustering de tables

La configuration YAML suivante modifie le schéma de clustering d'une table à l'aide de add, remove ou set, où set remplace toutes les colonnes de clustering existantes :

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 testdb.testschema.x(a int, b int);
    create table testdb.testschema.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:
      database: testdb
      schema: testschema
      attributeRegex: "a+"
    type:
      target: NUMERIC(10,2)

Vous pouvez transformer le type de données source en l'un des types d'attributs cibles compatibles.

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

teradata-input.sql
    create table testdb.testschema.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)
    )
    ;
    

Lorsqu'il n'existe pas de conversion directe entre les types source et cible (par exemple, lors de la conversion d'une colonne INT qui stocke des dates YYYYMMDD en une colonne DATE), fournissez des expressions de conversion GoogleSQL sourceToTarget et targetToSource. Utilisez ? comme espace réservé pour la valeur de la colonne ou de l'expression. Exemple :

type: object_rewriter
attribute:
  -
    match: "testdb.acme.employee.jd"
    type:
      target: DATE
      sourceToTarget: "parse_date('%Y%m%d', cast(? as string))"
      targetToSource: "cast(format_date('%Y%m%d', cast(? as date)) as int64)"

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

teradata-input.sql
    create table testdb.acme.employee(eid int, jd int);
    insert into testdb.acme.employee select 42, 20011213;
    select eid, jd from testdb.acme.employee e where jd > 20010101;
    
bq-output.sql
    CREATE TABLE testdb.acme.employee
    (
      eid INT64,
      jd DATE
    )
    ;
    INSERT INTO testdb.acme.employee
      SELECT
          42,
          parse_date('%Y%m%d', CAST(20011213 as STRING))
    ;
    SELECT
        eid,
        CAST(format_date('%Y%m%d', jd) as INT64) AS jd
      FROM
        testdb.acme.employee AS e
      WHERE CAST(format_date('%Y%m%d', jd) as INT64) > 20010101
    ;
    

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:
-
  match: "testdb.testschema.x"
  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 testdb.testschema.x
    (
      a VARCHAR(150),
      b INT
    );
    
bq-output.sql
    CREATE EXTERNAL TABLE testdb.testschema.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.

Le fichier de configuration YAML suivant spécifie l'encodage de caractères explicite du fichier d'entrée en tant que ISO-8859-1. Vous pouvez faire correspondre les fichiers d'entrée à l'aide d'un chemin d'accès exact (path), d'un format glob (pathGlob) ou d'une expression régulière (pathRegex) :

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

Conversion de type globale

La configuration YAML suivante remplace un type de données par un autre dans tous les scripts et spécifie un type de données source à éviter dans le script transcompilé. Cette configuration est différente de celle de la section Changer le type d'un attribut de colonne, où seul le type de données d'un seul attribut est modifié.

Le service de traduction accepte les conversions des types de données suivants :

  • De DATETIME à TIMESTAMP
  • De TIMESTAMP à DATETIME (accepte un fuseau horaire facultatif)
  • TIMESTAMP WITH TIME ZONE (timestamptz) à DATETIME (accepte un fuseau horaire facultatif)
  • De CHAR à VARCHAR (accepte les valeurs facultatives precisionAction et comparisonBehavior)
  • VARCHAR à VARCHAR (accepte les valeurs facultatives precisionAction et comparisonBehavior)

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

type: experimental_object_rewriter
global:
  typeConvert:
    timestamp: DATETIME

Convertir les types CHAR et VARCHAR

Dans les dialectes tels que Teradata, les valeurs CHAR(N) sont complétées à droite par des espaces, et les écritures de chaînes sont tronquées à la longueur de colonne déclarée. Les comparaisons de chaînes ne sont pas sensibles à la casse (NOT CASESPECIFIC) par défaut et ignorent les espaces de fin. Par défaut, le moteur de traduction émule cette sémantique dans BigQuery. Il ajoute des wrappers de marge intérieure droite (CONCAT) et de troncature (SUBSTR) sur les casts et les écritures, des wrappers UPPER et RTRIM sur les comparaisons, et des wrappers UPPER sur les clés GROUP BY.

Vous pouvez configurer les champs char et varchar sous global.typeConvert avec les propriétés suivantes :

  • target : doit être défini sur VARCHAR. La configuration de target: VARCHAR sur char supprime la marge intérieure droite de CHAR (CONCAT) même lorsque precisionAction est défini sur PRESERVE.
  • precisionAction : contrôle la précision de la longueur des caractères et les wrappers de troncature SUBSTR :
    • PRESERVE (par défaut) : conserve la précision de la longueur des caractères sur les expressions et émet des wrappers de troncature SUBSTR lors de la conversion ou de l'insertion dans des colonnes CHAR(N) ou VARCHAR(N). Pour émettre également des longueurs de colonne STRING(N) dans le LDD CREATE TABLE, consultez Définir la rigueur de la précision des chaînes.
    • DROP : supprime la précision de la longueur des caractères sur les expressions et omet les wrappers de troncature SUBSTR.
  • comparisonBehavior : contrôle les wrappers RTRIM et UPPER pour les comparaisons et les clés GROUP BY. Si vous définissez une valeur autre que STRICT sur char ou varchar, elle s'applique aux deux types. Par conséquent, définissez la même valeur sur les deux clés lorsque vous configurez les deux :
    • STRICT (par défaut) : émule à la fois le remplissage par espaces de fin (RTRIM) et le classement non sensible à la casse (UPPER).
    • PADDING_AWARE : émule uniquement le remplissage des espaces de fin (RTRIM) et omet UPPER.
    • CASE_AWARE : émule uniquement le classement non sensible à la casse (UPPER) et omet RTRIM.
    • NONE : omet les wrappers RTRIM et UPPER, de sorte que la sortie utilise des comparaisons directes et des expressions GROUP BY.

La configuration YAML suivante convertit CHAR en VARCHAR et supprime les wrappers de troncature (SUBSTR), d'espace de fin (RTRIM) et de non-sensibilité à la casse (UPPER) pour les colonnes CHAR et VARCHAR :

type: experimental_object_rewriter
global:
  typeConvert:
    char:
      target: VARCHAR
      precisionAction: DROP
      comparisonBehavior: NONE
    varchar:
      target: VARCHAR
      precisionAction: DROP
      comparisonBehavior: NONE

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

teradata-input.sql
      create table r(a char(10), b varchar(20));
      select cast(b as char(5)) from r where a = b group by 1;
    
bq-output.sql
      CREATE TABLE r
      (
        a STRING,
        b STRING
      )
      ;
      SELECT
          b
        FROM
          r
        WHERE a = b
        GROUP BY 1
      ;
    

Définition du fuseau horaire par défaut…

Les dialectes de base de données ont des sémantiques et des noms différents pour les types de données liés à la date et à l'heure. Le service de traduction normalise la terminologie suivante dans sa configuration YAML, quel que soit le nom du type de données du dialecte d'entrée :

  • Un datetime est une combinaison de Y-M-D H:M:S qui n'est pas fixée à un fuseau horaire particulier. datetime représente une heure et non un instant particulier.
  • Un timestamp représente un instant particulier ou absolu et, en tant que tel, est implicitement fixé à un fuseau horaire particulier, qui peut être un paramètre au niveau de la session ou de la base de données.
  • Un timestamptz représente un instant particulier, comme un timestamp, mais contrairement à un timestamp, il comporte un décalage de fuseau horaire particulier. Bien qu'ils représentent le même instant, 2019-06-01 12:00:00+4 et 2019-06-01 06:00:00-2 sont considérés comme des valeurs timestamptz différentes.

Dans des dialectes tels que Teradata, les fonctions liées à la date et à l'heure, telles que current_date, current_time ou current_timestamp, renvoient des codes temporels basés sur un paramètre de fuseau horaire de session configuré implicitement. En revanche, BigQuery renvoie toujours les codes temporels au format UTC. Pour garantir un comportement cohérent entre les deux dialectes, il peut être nécessaire de configurer un fuseau horaire en conséquence.

Nous vous recommandons de spécifier le fuseau horaire par défaut pour la traduction si votre base de données source utilise un fuseau horaire par défaut autre que UTC. Cela garantit le bon comportement de la requête traduite en préservant le fuseau horaire.

Dans l'exemple suivant, le fichier de configuration YAML convertit les types de données TIMESTAMP et TIMESTAMPTZ en DATETIME, avec le fuseau horaire cible défini sur Europe/Paris.

Pour connaître les valeurs de fuseau horaire valides, consultez Fuseaux horaires.

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

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

snowflake-input.sql
      create table x(c_timestamp timestamp_ltz, c_timestamptz timestamp_tz, c_datetime timestamp_ntz);

      select c_timestamp from x where c_timestamp > current_timestamp(0);
      select c_timestamptz from x where c_timestamptz > cast(current_timestamp(0) as timestamp_tz);
      select c_datetime from x where c_datetime > cast(current_timestamp(0) as timestamp_ntz);
    
bq-output.sql
      CREATE TABLE X
      (
        C_TIMESTAMP DATETIME,
        C_TIMESTAMPTZ DATETIME,
        C_DATETIME DATETIME
      )
      ;
      SELECT
          C_TIMESTAMP
        FROM
          X
        WHERE C_TIMESTAMP > datetime(current_timestamp(), 'Europe/Paris')
      ;
      SELECT
          X.C_TIMESTAMPTZ
        FROM
          X
        WHERE X.C_TIMESTAMPTZ > datetime(current_timestamp(), 'Europe/Paris')
      ;
      SELECT
          C_DATETIME
        FROM
          X
        WHERE C_DATETIME > datetime(current_timestamp(), 'Europe/Paris')
      ;
    

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

Par défaut, TIMESTAMPTZ est converti en TIMESTAMP sans qu'aucune configuration ne soit requise.

type: experimental_object_rewriter
global:
  typeConvert:
    datetime:
      target: TIMESTAMP

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

snowflake-input.sql
      create table x(c_timestamp timestamp_ltz, c_timestamptz timestamp_tz, c_datetime timestamp_ntz);

      select c_timestamp from x where c_timestamp > current_timestamp(0);
      select c_timestamptz from x where c_timestamptz > cast(current_timestamp(0) as timestamp_tz);
      select c_datetime from x where c_datetime > cast(current_timestamp(0) as timestamp_ntz);
    
bq-output.sql
      CREATE TABLE X
      (
        C_TIMESTAMP TIMESTAMP,
        C_TIMESTAMPTZ TIMESTAMP,
        C_DATETIME TIMESTAMP
      )
      ;
      SELECT
          C_TIMESTAMP
        FROM
          X
        WHERE C_TIMESTAMP > current_timestamp()
      ;
      SELECT
          C_TIMESTAMPTZ
        FROM
          X
        WHERE C_TIMESTAMPTZ > current_timestamp()
      ;
      SELECT
          X.C_DATETIME
        FROM
          X
        WHERE X.C_DATETIME > current_timestamp()
      ;
    

Sélectionner la modification du relevé

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

Le champ select.starProjection accepte les valeurs suivantes :

  • ALLOW : émet SELECT * lorsque la requête source utilise SELECT * ou liste toutes les colonnes de la table dans l'ordre.
  • PRESERVE (par défaut) : émet SELECT * uniquement lorsque la requête source utilise SELECT *.
  • EXPAND : développe SELECT * en références de colonnes individuelles lorsque les métadonnées de la table sont disponibles.

Les champs select.groupBy et select.orderBy acceptent les valeurs suivantes :

  • EXPRESSION : formate les éléments GROUP BY ou ORDER BY en tant qu'expressions complètes.
  • ALIAS : formate les éléments GROUP BY ou ORDER BY à l'aide des alias de colonne SELECT, lorsqu'ils sont disponibles.
  • INDEX : formate les éléments GROUP BY ou ORDER BY à l'aide de positions de colonne basées sur 1, telles que GROUP BY 1.

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

type: 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 TIMESTAMP
      )
      ;
      SELECT
          x.a,
          x.b
        FROM
          x
      ;
    

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

Dans la plupart des cas, le traducteur SQL BigQuery peut analyser automatiquement le langage de définition de données (LDD) des fonctions définies par l'utilisateur (UDF) externes pour déterminer leurs types de données d'entrée et de sortie, même si le corps de l'UDF est écrit dans un langage externe. Toutefois, pour certains dialectes sources, le traducteur peut ne pas être en mesure de compiler automatiquement la signature de la UDF;utilisateur. Pour vous assurer que les instructions SQL faisant référence à ces UDF sont traduites avec précision sans avoir à fournir le LDD d'origine, vous pouvez utiliser un fichier de configuration YAML pour enregistrer explicitement les signatures des UDF.

Le fichier de configuration YAML suivant spécifie la signature des fonctions définies par l'utilisateur (UDF) utilisées dans les scripts sources. Spécifiez chaque signature dans le dialecte source en tant que RETURN_TYPE FUNCTION_NAME(ARG_NAME ARG_TYPE, ...), y compris un nom pour chaque paramètre. Tout comme les fichiers ZIP de métadonnées, les définitions des UDF peuvent aider à 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(dt), interval 1 DAY)
        FROM
          x
      ;
    

Certains dialectes sources sont compatibles avec les fonctions définies par l'utilisateur qui partagent un nom entre différentes signatures d'arguments, parfois appelées fonctions définies par l'utilisateur surchargées. Pour les UDF surchargées, spécifiez udf et un name cible distinct afin que chaque signature soit mappée à un nom de fonction unique dans BigQuery :

type: metadata
udfs:
  - "int custom_substr(pos int)"
  - udf: "int custom_substr(pos int, len int)"
    name: "custom_substr_with_len"

Supprimer les schémas d'identifiants lors de la recherche de métadonnées

Les scripts SQL d'entrée peuvent référencer des objets ou des colonnes avec un préfixe, un suffixe ou un format de nom qui ne figure pas dans vos définitions de métadonnées. Par exemple, ils peuvent référencer a_dt alors que le LDD de métadonnées définit la colonne dt. Dans ce cas, utilisez nameAlias sous type: metadata afin que le moteur de traduction supprime le modèle lors de la recherche de noms d'objets dans les métadonnées. L'identifiant d'origine est conservé dans le code SQL de sortie.

Le champ nameAlias accepte les propriétés suivantes. Chaque propriété accepte une seule chaîne ou une liste de chaînes, et la première entrée correspondante de chaque liste s'applique :

  • stripPrefix : supprime un préfixe littéral avant de rechercher des métadonnées.
  • stripSuffix : supprime un suffixe littéral avant de rechercher des métadonnées.
  • stripRegex : correspond à une expression régulière sensible à la casse et remplace chaque sous-chaîne correspondante par son premier groupe de capture (...) lors de la recherche rapide de métadonnées. Ancrez le modèle (par exemple, ^a_(.*)$) pour qu'il ne corresponde qu'à un préfixe ou à l'identifiant complet.

La configuration YAML suivante supprime le préfixe a_ des identifiants lors de la recherche de noms d'objets dans les métadonnées :

type: metadata
nameAlias:
  stripRegex: "^a_(.*)$"

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

teradata-input.sql
      create table t(dt date);
      select a_dt + 1 from t;
    
bq-output.sql
      CREATE TABLE t
      (
        dt DATE
      )
      ;
      SELECT
          date_add(a_dt, interval 1 DAY)
        FROM
          t
      ;
    

Définir le niveau de 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 ignore ce comportement en configurant la rigueur de la précision pour conserver la précision décimale de l'instruction source.

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

Définir le niveau de rigueur de la précision des chaînes

Par défaut, le service de migration BigQuery omet la précision des chaînes lors de la traduction des colonnes CHAR et VARCHAR. Cela peut aider à éviter les erreurs de troncature lors de l'écriture des valeurs. Certains dialectes SQL, tels que Teradata, tronquent les valeurs qui dépassent la précision maximale lors de l'écriture, tandis que BigQuery renvoie une erreur dans ce scénario.

Si votre application ne repose pas sur le comportement de troncature du dialecte source, envisagez de conserver la précision de la colonne dans la définition du type traduit.

La configuration YAML suivante ignore ce comportement en configurant la rigueur de la précision pour conserver la précision de la chaîne de l'instruction source.

type: statement_rewriter
common:
  stringPrecision: STRICT

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

teradata-input.sql
      create table x(a varchar(3));
    
bq-output.sql
      CREATE TABLE x
      (
        a STRING(3)
      )
      ;
    

Mappage des noms de sortie

Vous pouvez utiliser type: experimental_object_rewriter dans un fichier de configuration YAML pour mapper les noms d'objets SQL (database, schema, relation, attribute, function, sequence, index, variable ou projection) de manière statique ou dynamique :

  • Chaque type d'objet ne développe que ses propres espaces réservés de nom et de hiérarchie parente à partir de ${database}, ${schema}, ${relation}, ${attribute}, ${function}, ${sequence}, ${index}, ${variable} et ${projection}. Par exemple, une règle schema développe ${database} et ${schema}, tandis qu'une règle relation développe ${database}, ${schema} et ${relation}.
  • Lorsque plusieurs règles du même type d'objet avec des critères match distincts correspondent à un objet, la dernière règle correspondante de la liste s'applique.
  • Les règles globales replace et case sous global.outputName s'exécutent avant les règles outputName spécifiques à l'objet. Une règle spécifique à un objet correspond au nom de l'objet transformé de manière globale et peut le remplacer. Pour les règles attribute, match effectue une comparaison avec le nom attribute transformé de manière globale, ainsi qu'avec les noms database, schema et relation sources. Pour en savoir plus, consultez Réécriture globale des noms de sortie.

Pour en savoir plus sur le fonctionnement des règles de mappage de noms au niveau de la base de données, du schéma, de la relation, de la fonction et des attributs, consultez Mapper les noms d'objets SQL pour la traduction.

Mappage des noms statiques

Utilisez le mappage des noms statiques 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.

La configuration YAML suivante 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
      )
      ;
    

Vous pouvez utiliser le mappage des noms statiques pour mettre à jour la région utilisée par les noms dans les fonctions publiques définies par l'utilisateur.

L'exemple suivant modifie les noms de la UDF;utilisateur bqutil.fn pour qu'elle utilise la région europe_west2 au lieu de l'emplacement multirégional par défaut us :

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

Mappage des noms dynamiques

Utilisez le mappage des noms dynamique pour modifier plusieurs objets en même temps et créer des noms en fonction des objets mappés.

La configuration YAML suivante modifie le nom de toutes les tables en ajoutant le préfixe stg_ à celles qui appartiennent 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
      )
      ;
    

Mappage des noms de colonnes

Utilisez la section attribute sous type: experimental_object_rewriter pour renommer les colonnes dans les définitions de table et les références de requête :

type: experimental_object_rewriter
attribute:
-
  match:
    relation: employee
    attributeRegex: "\\d.*"
  outputName:
    attribute: "col_${attribute}"

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

teradata-input.sql
      create table employee(eid int, "2name" varchar(10));
      select eid, "2name" from employee e;
    
bq-output.sql
      CREATE TABLE employee
      (
        eid INT64,
        col_2name STRING
      )
      ;
      SELECT
          eid,
          col_2name
        FROM
          employee AS e
      ;
    

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

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 mydb.mytable;
      SELECT * FROM table1;
    
bq-output.sql
      SELECT * FROM myproject.mydb.mytable;
      SELECT * FROM myproject.myschema1.table1;
    

Configurer NLS_DATE_FORMAT

La configuration YAML suivante définit le paramètre NLS_DATE_FORMAT sur le format DD/MM/YYYY. Nous vous recommandons de spécifier NLS_DATE_FORMAT pour les utilisations implicites du format de date et des conversions. Si ce paramètre n'est pas défini, le format de traduction par défaut, DD-MON-RR, est utilisé.

type: environment
session:
  dateFormat: DD/MM/YYYY

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

oracle-input.sql
    create table x(dt date default '31/12/1999');
    insert into x values ('01/01/2000');
    
bq-output.sql
      CREATE TABLE X
      (
        DT DATETIME DEFAULT DATETIME '1999-12-31 00:00:00'
      )
      ;
      INSERT INTO X
        VALUES (DATETIME '2000-01-01 00:00:00')
      ;
    

Réécriture global des noms de sortie

La configuration YAML suivante utilise des règles replace sous global.outputName pour réécrire les noms de sortie de tous les objets (all) ou de types d'objets spécifiques (database, schema, relation, attribute, function, sequence, index, variable ou projection). Chaque règle correspond à une expression régulière sensible à la casse et remplace les correspondances par un nouveau texte, y compris $0, $1 et d'autres groupes de capture.

Les transformations outputName globales (replace suivies de case) s'exécutent avant les règles outputName spécifiques à l'objet, telles que schema, relation ou attribute. Une règle spécifique à un objet correspond au nom de son objet transformé de manière globale et peut le remplacer. Pour les règles attribute, match compare le nom attribute transformé de manière globale avec les noms database, schema et relation sources :

type: experimental_object_rewriter
global:
  outputName:
    all:
      replace:
        - regex: '\s'
          replaceWith: '_'
        - regex: '>='
          replaceWith: 'gte'
        - regex: '^[^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_gte_12
      (
        _42eid INT64,
        custom_column STRING
      )
      ;
    

Optimiser et améliorer les performances du code SQL traduit

Des transformations facultatives peuvent être appliquées au code SQL traduit afin d'apporter des modifications susceptibles d'améliorer les performances ou de réduire les coûts. Ces optimisations dépendent strictement de la casse et doivent être évaluées par rapport à une sortie SQL non modifiée pour évaluer leur effet réel sur les performances.

Le fichier de configuration YAML suivant permet d'activer des transformations facultatives. Chaque entrée de la liste transformations accepte les champs suivants :

  • name : nom de l'optimisation.
  • enabled (facultatif) : valeur booléenne dont la valeur par défaut est true. Définissez enabled sur false pour désactiver une optimisation activée par défaut pour le dialecte source.
  • parameters (facultatif) : mappage des paramètres spécifiques à l'optimisation.
type: optimizer
transformations:
  - name: PRECOMPUTE_INDEPENDENT_SUBSELECTS
  - name: REWRITE_CTE_TO_TEMP_TABLE
    parameters:
      threshold: 1
Optimisation Paramètre facultatif Description
PRECOMPUTE_INDEPENDENT_SUBSELECTS scope: [PREDICATE, PROJECTION] Réécrit la requête en ajoutant une instruction DECLARE pour remplacer une expression dans les clauses PREDICATE, les clauses PROJECTION ou les deux par une variable précalculée. Cette réécriture transforme l'expression en un prédicat statique qui peut réduire la quantité de données lues. Le paramètre scope accepte une liste contenant PREDICATE, PROJECTION ou les deux. Si parameters est omis, scope est défini par défaut sur [PREDICATE], qui couvre les clauses WHERE et JOIN ON.

Le fait d'extraire une sous-requête scalaire vers une instruction DECLARE rend statique le prédicat d'origine et permet donc d'améliorer la planification de l'exécution. Cette optimisation introduira de nouvelles instructions SQL.
REWRITE_CTE_TO_TEMP_TABLE threshold: N Réécrit les expressions de table courantes (CTE) en tables temporaires lorsqu'il existe au moins N références à la même CTE. Cette réécriture réduit la complexité de la requête et force l'exécution unique de la CTE. Si N est omis, la valeur par défaut est 4.

Nous vous recommandons d'utiliser cette optimisation lorsque des CTE non triviales sont référencées plusieurs fois. L'introduction de tables temporaires entraîne une surcharge qui peut être supérieure aux exécutions multiples d'une CTE de faible complexité ou de faible cardinalité. Cette optimisation introduira de nouvelles instructions SQL.
REWRITE_ZERO_SCALE_NUMERIC_AS_INTEGER bigint: N Réécrit des attributs NUMERIC/BIGNUMERIC à échelle nulle en type INT64 si la précision est comprise dans N. Si N est omis, la valeur par défaut est 18.

Nous vous recommandons d'utiliser cette optimisation lorsque vous traduisez des dialectes sources qui n'ont pas de types d'entiers. La modification des types de colonnes nécessite d'examiner toutes les utilisations en aval pour vérifier la compatibilité des types et les changements sémantiques. Par exemple, les divisions fractionnaires qui deviennent des divisions entières, ou le code qui attend des valeurs numériques.

Les traductions Snowflake ont cette optimisation activée par défaut pour les valeurs numériques à échelle zéro jusqu'à une précision de 38. Cette optimisation garantit qu'un INTEGER Snowflake, qui est implicitement représenté par NUMBER(38,0) dans Snowflake, se traduit par un INT64 BigQuery au lieu d'un BIGNUMERIC(38).

Si votre application utilise des nombres dont la précision est supérieure à 18, nous vous recommandons de désactiver cette fonctionnalité pour vous assurer que BigQuery peut traiter l'ensemble des valeurs requises par votre application.
DROP_TEMP_TABLE Ajoute des instructions DROP TABLE pour toutes les tables temporaires créées dans un script et non supprimés à la fin de celui-ci. Cela réduit la période de facturation du stockage de la table temporaire de 24 heures à la durée d'exécution du script.

Nous vous recommandons d'utiliser cette optimisation lorsque les tables temporaires ne sont pas accessibles pour un traitement ultérieur après la fin de l'exécution du script. Cette optimisation introduira de nouvelles instructions SQL.
REGEXP_CONTAINS_TO_LIKE Réécrit certaines catégories de modèles de correspondance REGEXP_CONTAINS en expressions LIKE.

Nous vous recommandons d'utiliser cette optimisation lorsqu'aucun autre processus, tel que le remplacement de macro, ne repose sur la conservation des littéraux de modèle d'expression régulière sans modification dans le code SQL de sortie.
ADD_DISTINCT_TO_SUBQUERY_IN_SET_COMPARISON Ajoute la clause DISTINCT aux sous-requêtes utilisées comme ensemble de valeurs pour l'opérateur [NOT] IN.

Nous vous recommandons d'utiliser cette optimisation lorsque la cardinalité (nombre distinct de valeurs) du résultat de la sous-requête est nettement inférieure au nombre de valeurs. Si cette condition préalable n'est pas remplie, cette transformation peut avoir des effets négatifs sur les performances.
APPROXIMATE_RANGE_PARTITIONS Approximer les schémas de partitionnement d'entiers non contigus ou non réguliers en les convertissant en plages de partition contiguës de taille égale compatibles avec BigQuery. Par défaut, ces schémas de partitionnement n'influencent pas le schéma de partitionnement de table dans les instructions LDD traduites.

Nous vous recommandons d'utiliser cette optimisation lorsque la table source utilise une fonction de partitionnement non contiguë telle que la fonction RANGE_N de Teradata et qu'elle bénéficierait d'un schéma de partition de taille égale dans BigQuery.

Exemples d'optimisation

L'optimisation suivante convertit les types numériques à échelle zéro avec une précision inférieure ou égale à 38 en INT64 dans BigQuery.

# An INTEGER is internally represented as NUMBER(38,0) in Snowflake.
# To convert Snowflake INTEGER to INT64 in BigQuery, enable the rewrite for precision <= 38.
# Note that this can produce incorrect results if your application logic uses more than 18 digits of precision.
#
# This configuration is enabled by default for the Snowflake Dialect.
type: optimizer
transformations:
  - name: REWRITE_ZERO_SCALE_NUMERIC_AS_INTEGER
    parameters:
      bigint: 38

Une traduction SQL avec cette optimisation peut se présenter comme suit :

snowflake-input.sql
      CREATE TABLE numbers(i INTEGER, n NUMERIC(10,0));
    
bq-output.sql
      CREATE TABLE NUMBERS
      (
        I INT64,
        N INT64
      )
      ;
    

La configuration suivante désactive l'optimisation dans les dialectes, tels que Snowflake, où elle est activée par défaut. Cette configuration convertit les types numériques en NUMERIC ou BIGNUMERIC en fonction de la précision d'entrée, au lieu de la valeur par défaut INT64.

type: optimizer
transformations:
  - name: REWRITE_ZERO_SCALE_NUMERIC_AS_INTEGER
    enabled: false

Une traduction SQL avec cette optimisation peut se présenter comme suit :

snowflake-input.sql
      CREATE TABLE numbers(i INTEGER, n NUMERIC(10,0));
    
bq-output.sql
      CREATE TABLE NUMBERS
      (
        I BIGNUMERIC(38),
        N NUMERIC(29)
      )
      ;
    

Créer un fichier YAML de configuration basé sur Gemini

Pour générer un résultat d'IA, fournissez au moins un fichier *.ai_config.yaml et activez les suggestions de l'IA Gemini lorsque l'interface le demande. Pour en savoir plus, consultez Fournir des fichiers de configuration à une traduction.

Conditions requises

Le fichier YAML de configuration des sorties d'IA doit avoir le suffixe .ai_config.yaml. Exemple :rules_1.ai_config.yaml

Champs pris en charge

Contrairement à un fichier *.config.yaml, un fichier *.ai_config.yaml n'inclut pas d'en-tête type. À la place, le fichier contient une liste translation_rules de premier niveau, où chaque règle accepte les champs suivants :

  • suggestion_type (facultatif) : spécifiez le type de suggestion d'IA à générer. Les types de suggestions suivants sont acceptés :
    • QUERY_CUSTOMIZATION (par défaut) : génère des suggestions d'IA pour le code SQL en fonction des règles de traduction spécifiées dans le fichier *.ai_config.yaml.
    • TRANSLATION_EXPLANATION : génère un texte qui inclut un résumé de la requête GoogleSQL traduite, ainsi que les différences et les incohérences entre la requête SQL source et la requête GoogleSQL traduite.
  • rewrite_target (facultatif) : spécifiez SOURCE_SQL pour appliquer la règle au code SQL d'entrée d'origine avant l'exécution du moteur de traduction, ou TARGET_SQL pour appliquer la règle au code SQL traduit produit par le moteur de traduction. La valeur par défaut est TARGET_SQL. Pour en savoir plus, consultez Application des deux types de règles.
  • instruction (facultatif) : décrivez en langage naturel la modification à appliquer au code SQL d'entrée ou au code SQL traduit pour les règles QUERY_CUSTOMIZATION, ou l'explication à générer pour les règles TRANSLATION_EXPLANATION.
  • examples (facultatif) : fournissez une liste de paires SQL input et output qui montrent comment vous souhaitez modifier un modèle SQL.

Vous pouvez ajouter des translation_rules et des examples supplémentaires si nécessaire.

Exemples

Les exemples suivants créent des fichiers YAML de configuration basés sur Gemini que vous pouvez utiliser avec vos traductions SQL.

Supprimer la fonction UPPER dans la requête de sortie de traduction par défaut

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

Créer plusieurs règles de traduction pour personnaliser la sortie de traduction

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

Supprimer les commentaires SQL de la requête d'entrée de traduction

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

Générer des explications de traduction à l'aide du prompt LLM par défaut

Cet exemple utilise les prompts LLM par défaut fournis par le service de traduction pour générer des explications textuelles :

translation_rules:
- suggestion_type: "TRANSLATION_EXPLANATION"

Génère des explications de traduction à l'aide de vos propres requêtes en langage naturel

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

Plusieurs types de suggestions dans un seul fichier de configuration YAML

translation_rules:
- suggestion_type: "TRANSLATION_EXPLANATION"
  instruction: "Explain the syntax differences between the source Teradata query and the translated GoogleSQL query."
- instruction: "Remove upper() function"
  suggestion_type: QUERY_CUSTOMIZATION
  rewrite_target: TARGET_SQL
  examples:
  - input: "upper(X)"
    output: "X"
- instruction: "Remove all the sql comments in the input sql query."
  suggestion_type: QUERY_CUSTOMIZATION
  rewrite_target: SOURCE_SQL

Appliquer plusieurs configurations YAML

Lorsque vous traduisez une seule requête ou des fichiers SQL par lot, vous pouvez fournir plusieurs fichiers YAML de configuration dans un même job de traduction pour refléter plusieurs transformations. Si plusieurs configurations sont en conflit, une transformation peut en 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 testdb.testschema.x(a int);
    
bq-output.sql
    CREATE TEMPORARY TABLE x
    (
      a NUMERIC(31, 2)
    )
    ;