Transform SQL translations with YAML

Configuration YAML files let you customize how BigQuery translates SQL code from your source database during a migration. The BigQuery Migration Service supports two kinds of configuration YAML files: *.config.yaml translation configuration files that give the translation engine deterministic rules, and *.ai_config.yaml Gemini-based configuration files that give Gemini natural-language rules.

To customize a SQL translation with configuration YAML files, do the following:

  1. Create configuration files: Create deterministic *.config.yaml translation configuration files, *.ai_config.yaml Gemini-based configuration files, or both types of configuration files to combine structural rules with Gemini customization.
  2. Provide the files to a translation job: Submit the configuration files when you translate a single query or batch SQL files.
  3. Review the translation outputs: Locate the translated SQL and Gemini suggestions in the response when you translate a single query, or in Cloud Storage when you translate batch SQL files.

Types of configuration YAML files

The following table compares the two types of configuration YAML files:

Feature Translation configuration (*.config.yaml) Gemini-based configuration (*.ai_config.yaml)
How rules are applied Rules are applied by the translation engine as deterministic rules on the parsed SQL, so the same input and configuration always produce the same output. Rules are applied by Gemini as natural-language instructions and examples applied to the SQL text. Output can vary between runs, so always review and verify suggestions before using them.
What can be transformed The translation engine can transform any objects or settings that it recognizes. This includes database, schema, table, column, and function names and types, table properties such as partitioning, clustering, and expiration, session defaults, UDF signatures, optimizations, and statement output such as GROUP BY and SELECT * style. Gemini can change any SQL transformation that you can describe in natural language. This includes formatting and comments, replacing patterns, rewriting constructs that the engine doesn't translate the way you want, and generating an explanation of the translation.
Requirements
  • The input SQL must parse in the source dialect.
  • Rules can't act on text in the input SQL that the parser doesn't recognize, such as ${VAR} placeholders or template syntax.
  • Use a SOURCE_SQL rule in *.ai_config.yaml files or your own preprocessing to remove such text first.
Where the result appears The result appears in the translated SQL itself, such as in the translation response or in the sql/ output directory in Cloud Storage. The result appears as separate Gemini suggestions in the Google Cloud console, in separate suggestion fields or output files in the Google Cloud CLI and MCP server, or in separate *_suggestion/ output directories in Cloud Storage. For more information, see Where to find translation outputs.
Filename File names must end with .config.yaml—for example, rename-tables.config.yaml. File names must end with .ai_config.yaml—for example, style-rules.ai_config.yaml.
The translation service ignores any YAML file in the input that doesn't end with .config.yaml or .ai_config.yaml and reports the ignored file in the translation log.
Reference For more information, see Guidelines to create a configuration YAML file and Configuration YAML examples on this page. For more information, see Create a Gemini-based configuration YAML file on this page.

How the two kinds of rules are applied

A translation job applies configuration rules in the following order:

  1. Gemini applies SOURCE_SQL rules in *.ai_config.yaml files to the original input SQL to produce rewritten source SQL.
  2. The translation engine translates the rewritten source SQL from the preceding step—or the original input SQL if no SOURCE_SQL rules apply—and applies any rules in *.config.yaml files to produce the translated SQL.
  3. Gemini applies TARGET_SQL rules in *.ai_config.yaml files to the translated SQL from the preceding step to produce rewritten target SQL. If your *.ai_config.yaml files include TRANSLATION_EXPLANATION rules, then Gemini also generates a text explanation of the translated SQL.

We recommend that you use *.config.yaml rules for repeatable, structural changes such as renaming objects, mapping types, and setting partitioning. We recommend that you use *.ai_config.yaml rules for changes that the engine can't express, or for cleaning up input that doesn't parse. You can use both types of configuration files in the same job.

Provide configuration files to a translation

The Google Cloud console, gcloud CLI, BigQuery Migration API, and BigQuery Migration Service MCP server support using multiple configuration files in a single translation job. For more information, see Applying multiple YAML configurations.

To provide configuration files to a translation job, select one of the following options:

Console

  • To use configuration files when translating a single query, specify them in the translation settings of the interactive translator:
    • *.config.yaml: In the Translation Configuration Source Location field in the translation settings, specify a Cloud Storage path to the configuration file.
    • *.ai_config.yaml: Create customization rules, which are saved to __default.ai_config.yaml, or specify a Cloud Storage path.
  • To use configuration files while batch translating SQL files, do the following:
    • *.config.yaml: Upload the configuration files in the input Cloud Storage directory, then in the translation settings, specify a path to the directory in the File location details field.
    • *.ai_config.yaml: Upload the configuration files in the input Cloud Storage directory, then in the translation settings, select Optional settings > Gemini AI suggestions.

gcloud

  • When using the gcloud alpha bq migration translate command, use the --translation-config-files flag to specify either *.config.yaml or *.ai_config.yaml files.
  • When using the gcloud alpha bq migration translate-batch command, do one of the following:
    • *.config.yaml: Include the files in the input directory that you pass to --source-gcs-uris or --source-local-dirs, or pass individual files with the --source-gcs-files or --source-local-files flag.
    • *.ai_config.yaml: Include the files in the input directory that you pass to --source-gcs-uris or --source-local-dirs, or pass individual files with the --source-gcs-files or --source-local-files flag, and add the --enable-ai-suggestion flag.

API

In the BigQuery Migration API, whether you translate a single query or batch SQL files, do the following:

  • *.config.yaml: Upload the files under the input path in source_spec.base_uri, pass a gcs_file_path entry in source_spec when the file isn't under base_uri, or pass an inline literal entry in source_spec.
  • *.ai_config.yaml: Provide the files in source_spec under base_uri, as a gcs_file_path entry when the file isn't under base_uri, or as an inline literal entry, and include "suggestion" in target_types—for example, "target_types": ["sql", "suggestion"].

MCP

In the BigQuery Migration Service MCP server:

  • To translate a single query (translate_query), do the following:
    • *.config.yaml: Pass inline translationConfigs entries.
    • *.ai_config.yaml: Pass inline aiSuggestionConfigs entries.
  • To translate batch SQL files (translate_batch_queries), do the following:
    • *.config.yaml: Upload the files under sourceBaseUri, or list them in configurationFilePaths when they aren't under sourceBaseUri.
    • *.ai_config.yaml: Upload the files under sourceBaseUri, or list them in configurationFilePaths when they aren't under sourceBaseUri, and set enableAiSuggestions to true.

Where to find translation outputs

Depending on the configuration files that you provide, a translation job can produce the following outputs:

  • Translated SQL from the translation engine after it applies any rules in *.config.yaml files.
  • Rewritten source SQL from Gemini after it applies SOURCE_SQL rules in *.ai_config.yaml files.
  • Rewritten target SQL from Gemini after it applies TARGET_SQL rules in *.ai_config.yaml files.
  • A translation explanation from Gemini when your *.ai_config.yaml files include TRANSLATION_EXPLANATION rules, or when you request an explanation on demand.

Where you find these outputs depends on how you run the translation:

  • To find outputs when you translate a single query in the Google Cloud console or the gcloud CLI, see Explore the translation output in the interactive SQL translator guide.
  • To find outputs when you translate batch SQL files in Cloud Storage or review them in the Google Cloud console, see Explore the translation output in the batch SQL translator guide.
  • To find outputs when you use the BigQuery Migration API, see Retrieve results in the BigQuery Migration API guide. For single-query translations, add the output paths that you want—sql/<var>PATH</var>, source_sql_query_customization_suggestion/<var>PATH</var>, target_sql_query_customization_suggestion/<var>PATH</var>, or translation_explanation_suggestion/<var>PATH</var>—to target_return_literals to receive them in translatedLiterals.
  • To find outputs when you use the BigQuery Migration Service MCP server, see Use the BigQuery Migration Service MCP server. The translate_query and get_translation tools return translatedQuery, sourceSqlSuggestion, and targetSqlSuggestion, and the explain_translation tool returns a translation explanation.

Translation *.config.yaml file requirements

A translation *.config.yaml file must meet the following requirements to be compatible with the BigQuery Migration Service:

  • When you upload *.config.yaml files under the input Cloud Storage path for a batch translation job, configuration files placed anywhere under the input path apply to the entire job. They don't need to be at the root level or copied into each subdirectory. For information on how to create buckets and upload files to Cloud Storage, see Create buckets and Upload objects from a filesystem.
  • The file size for a single *.config.yaml file must not exceed 1 MB.
  • The total file size of all *.config.yaml files used in a single SQL translation job must not exceed 4 MB.
  • If you are using regex syntax for name matching, use RE2/J.
  • All translation configuration file names must end with .config.yaml—for example, change-case.config.yaml. config.yaml alone isn't a valid name for the configuration file.

Guidelines to create a configuration YAML file

This section provides some general guidelines to create a configuration YAML file:

Each *.config.yaml file must include a top-level type header that specifies the category of configuration rules in the file:

  • object_rewriter: rewrites relation properties—such as temporary, ephemeral, partitionLifetime, external, description, partition, and clustering—and attribute data types (type).
  • experimental_object_rewriter: rewrites output object names with outputName across global, database, schema, relation, attribute, function, index, sequence, variable, and projection, and converts data types globally with global.typeConvert. For more information, see Output name mapping and Global type conversion.
  • environment: sets session-level defaults under session, such as defaultDatabase, schemaSearchPath, and dateFormat.
  • metadata: declares user-defined function (UDF) signatures (udfs) and strips input name prefixes, suffixes, or regular expression patterns during metadata lookup (nameAlias).
  • statement_rewriter: customizes statement output such as SELECT * expansion, GROUP BY and ORDER BY formatting (select), and decimal or string precision strictness (common).
  • input_formats: specifies the character encoding of input SQL files (formats).
  • optimizer: enables, disables, or configures optional query optimizations (transformations).

The following example uses the experimental_object_rewriter type to convert all output object names to uppercase:

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

Entity selection

To perform entity-specific transformations, specify the source SQL entity in the configuration file. All match properties are optional; only use the match properties needed for a transformation. The match field compares against source object names after resolving any configured defaultDatabase and schemaSearchPath, except in outputName rules when global.outputName is also configured. For more information, see Output name mapping. The following configuration YAML exposes properties to be matched in order to select specific entities:

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

Description of each match property:

  • database or db: matches the source database name, which corresponds to the project_id component in BigQuery.
  • schema: matches the source schema name, which corresponds to the dataset component in BigQuery.
  • relation: matches the source table or view name.
  • attribute: matches the source column name. This property is valid only for attribute selection.
  • databaseRegex or dbRegex: matches the entire database name against a case-insensitive regular expression.
  • schemaRegex: matches the entire schema name against a case-insensitive regular expression.
  • relationRegex: matches the entire relation name against a case-insensitive regular expression.
  • attributeRegex: matches the entire attribute name against a case-insensitive regular expression. This property is valid only for attribute selection.

For example, the following configuration YAML specifies the match properties to select the testdb.acme.employee table for a temporary table transformation.

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

You can use the databaseRegex, schemaRegex, relationRegex, and attributeRegex properties to specify regular expressions in order to select a subset of entities. The following example changes all relations from tmp_schema schema in testdb to temporary, as long as their name starts with tmp_:

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

Both literal and regex properties are matched in a case-insensitive manner. You can enforce case-sensitive matching by using a regex with a disabled i flag, as seen in the following example:

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

You can also specify fully-qualified entities using an equivalent short-string syntax. A short-string syntax expects exactly 3 (for relation selection) or 4 (for attribute selection) name segments delimited with dots, as the example testdb.acme.employee. The segments are then internally interpreted as if they were passed as database, schema, relation and attribute respectively. This means that names are matched literally, thus regular expressions are not allowed in short syntax. The following example shows the use of short-string syntax to specify a fully-qualified entity in a configuration YAML file:

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

If a table contains a dot in the name, you cannot specify the name using a short syntax. In this case, you must use an object match. The following example changes the testdb.acme.stg.employee table to temporary:

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

A configuration YAML file also accepts key as an alias for match. Both key and match support explicit name-component subfields as well as the dot-delimited "database.schema.relation" or "database.schema.relation.attribute" short-string syntax. By convention, key is often used with the short-string syntax and match is used with explicit subfields.

Default database

Some input SQL dialects, notably Teradata, don't support a database name in qualified identifiers (schema.table). In this case, use an object match and omit the database property. Alternatively, configure defaultDatabase—for example, under type: environment—so that you can match against that default database in object or short-string match rules.

Supported target attribute types

You can use the configuration YAML file to perform attribute type transformations, where you transform the data type of a column from the source type to a target type. The configuration YAML file supports the following target types:

  • BOOLEAN
  • TINYINT
  • SMALLINT
  • INTEGER
  • BIGINT
  • FLOAT
  • DOUBLE
  • NUMERIC (Supports optional precision and scale, such as NUMERIC(18, 2))
  • TIME
  • TIMETZ
  • DATE
  • DATETIME
  • TIMESTAMP
  • TIMESTAMPTZ
  • CHAR (Supports optional precision, such as CHAR(42))
  • VARCHAR (Supports optional precision, such as VARCHAR(42))

Configuration YAML examples

This section provides examples to create various configuration YAML files to use with your SQL translations. Each example outlines the YAML syntax to transform your SQL translation in specific ways, along with a brief description. Each example also provides the contents of a teradata-input.sql or hive-input.sql file and a bq-output.sql file so that you can compare the effects of a configuration YAML on a BigQuery SQL query translation.

The following examples use Teradata or Apache Hive as the input SQL dialect and BigQuery SQL as the output dialect.

Change object-name case

The following configuration YAML changes the case of SQL object names to UPPERCASE or LOWERCASE. You can set case on all objects or override it for specific object types (database, schema, relation, attribute, function, sequence, index, variable, or projection):

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

A SQL translation with this configuration YAML file might look like the following:

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
      ;
    

Make table temporary

The following configuration YAML changes a regular table to a temporary table:

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

A SQL translation with this configuration YAML file might look like the following:

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

Make table ephemeral

The following configuration YAML changes a regular table to an ephemeral table with a 60 second expiration.

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

A SQL translation with this configuration YAML file might look like the following:

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

Set partition expiration

The following configuration YAML changes the expiration of a partitioned table to 1 day:

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

A SQL translation with this configuration YAML file might look like the following:

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

Change external location or format for a table

The following configuration YAML changes the external location and format for a table:

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

A SQL translation with this configuration YAML file might look like the following:

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

Set or change table description

The following configuration YAML sets the description of a table:

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

A SQL translation with this configuration YAML file might look like the following:

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

Set or change table partitioning

The following configuration YAML changes the partitioning scheme of a table by using add, remove, or set, where set replaces any existing partition columns:

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

A SQL translation with this configuration YAML file might look like the following:

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

The following configuration YAML changes the partitioning scheme of a table so that it partitions by range bucket:

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

A SQL translation with this configuration YAML file might look like the following:

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

Set or change table clustering

The following configuration YAML changes the clustering scheme of a table by using add, remove, or set, where set replaces any existing clustering columns:

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

A SQL translation with this configuration YAML file might look like the following:

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

Change type of a column attribute

The following configuration YAML changes the data type for an attribute of a column:

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

You can transform the source data type to any of the supported target attribute types.

A SQL translation with this configuration YAML file might look like the following:

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

When no direct cast exists between the source and target types—for example, when converting an INT column that stores YYYYMMDD dates into a DATE column—provide GoogleSQL sourceToTarget and targetToSource conversion expressions. Use ? as the placeholder for the column or expression value. For example:

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

A SQL translation with this configuration YAML file might look like the following:

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
    ;
    

Add connection to external data lake

The following configuration YAML marks the source table as being an external table that points to data stored in an external data lake, specified by a data lake connection.

type: object_rewriter
relation:
-
  match: "testdb.testschema.x"
  external:
    connection_id: "connection_test"

A SQL translation with this configuration YAML file might look like the following:

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

Change the character encoding of an input file

By default, the BigQuery Migration Service attempts to automatically detect the character encoding of input files. In cases where BigQuery Migration Service might misidentify the encoding of a file, you can use a configuration YAML to specify the character encoding explicitly.

The following configuration YAML specifies the explicit character encoding of the input file as ISO-8859-1. You can match input files by using an exact path (path), a glob pattern (pathGlob), or a regular expression (pathRegex):

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

Global type conversion

The following configuration YAML changes a data type to another across all scripts, and specifies a source data type to avoid in the transpiled script. This is different from the Change type of a column attribute configuration, where only the data type for a single attribute is changed.

The translation service supports the following data type conversions:

  • DATETIME to TIMESTAMP
  • TIMESTAMP to DATETIME (accepts optional time zone)
  • TIMESTAMP WITH TIME ZONE (timestamptz) to DATETIME (accepts optional time zone)
  • CHAR to VARCHAR (accepts optional precisionAction and comparisonBehavior)
  • VARCHAR to VARCHAR (accepts optional precisionAction and comparisonBehavior)

In the following example, the configuration YAML converts a TIMESTAMP data type to DATETIME.

type: experimental_object_rewriter
global:
  typeConvert:
    timestamp: DATETIME

Convert CHAR and VARCHAR types

In dialects such as Teradata, CHAR(N) values are right-padded with spaces, and string writes are truncated to the declared column length. String comparisons are case-insensitive (NOT CASESPECIFIC) by default and ignore trailing spaces. By default, the translation engine emulates these semantics in BigQuery. It adds right-padding (CONCAT) and truncation (SUBSTR) wrappers on casts and writes, UPPER and RTRIM wrappers on comparisons, and UPPER wrappers on GROUP BY keys.

You can configure the char and varchar fields under global.typeConvert with the following properties:

  • target: must be set to VARCHAR. Configuring target: VARCHAR on char removes CHAR right-padding (CONCAT) even when precisionAction is PRESERVE.
  • precisionAction: controls character-length precision and SUBSTR truncation wrappers:
    • PRESERVE (default): retains character-length precision on expressions and emits SUBSTR truncation wrappers when casting or inserting into CHAR(N) or VARCHAR(N) columns. To also emit STRING(N) column lengths in CREATE TABLE DDL, see Setting string precision strictness.
    • DROP: drops character-length precision on expressions and omits SUBSTR truncation wrappers.
  • comparisonBehavior: controls RTRIM and UPPER wrappers for comparisons and GROUP BY keys. Setting a value other than STRICT on either char or varchar applies across both types, so set the same value on both keys when configuring both:
    • STRICT (default): emulates both trailing-space padding (RTRIM) and case-insensitive collation (UPPER).
    • PADDING_AWARE: emulates only trailing-space padding (RTRIM) and omits UPPER.
    • CASE_AWARE: emulates only case-insensitive collation (UPPER) and omits RTRIM.
    • NONE: omits both RTRIM and UPPER wrappers, so the output uses direct comparisons and GROUP BY expressions.

The following configuration YAML converts CHAR to VARCHAR and removes truncation (SUBSTR), trailing-space (RTRIM), and case-insensitivity (UPPER) wrappers for both CHAR and VARCHAR columns:

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

A SQL translation with this configuration YAML file might look like the following:

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
      ;
    

Setting default time zone

Database dialects have varying semantics and names for different date and time related data types. The translation service standardizes on the following terminology in its YAML configuration, regardless of the name of the input dialect's data type:

  • A datetime is a combination of Y-M-D H:M:S that is not fixed to any particular time zone. datetime represents a wall-clock time and not a particular instant.
  • A timestamp represents a particular, or absolute time instant and as such, is implicitly fixed to a particular time zone, which might be a session-level or database setting.
  • A timestamptz represents a particular instant like a timestamp, but unlike a timestamp it carries with it a particular time zone offset. While they represent the same instant, 2019-06-01 12:00:00+4 and 2019-06-01 06:00:00-2 are considered different timestamptz values.

In dialects like Teradata, datetime-related functions such as current_date, current_time, or current_timestamp return timestamps based on an implicitly configured session time zone parameter. BigQuery, on the other hand, always returns timestamps in UTC. To ensure consistent behavior between the two dialects, it might be necessary to configure a time zone accordingly.

We recommend that you specify the default time zone for translation if your source database has a default that is not UTC. This will ensure correct behavior of the translated query by preserving the time zone.

In the following example, the configuration YAML converts a TIMESTAMP and a TIMESTAMPTZ data type to DATETIME, with the target time zone set to Europe/Paris.

For valid string time zone values, see Time zones.

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

A SQL translation with this configuration YAML file might look like the following:

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

In the following example, the configuration YAML converts a DATETIME data type to TIMESTAMP.

By default, TIMESTAMPTZ is converted to TIMESTAMP with no configuration required.

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

A SQL translation with this configuration YAML file might look like the following:

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

Select statement modification

The following configuration YAML changes the star projection, GROUP BY, and ORDER BY clauses in SELECT statements.

The select.starProjection field supports the following values:

  • ALLOW: emits SELECT * when the source query uses SELECT * or lists all columns of the table in order.
  • PRESERVE (default): emits SELECT * only when the source query uses SELECT *.
  • EXPAND: expands SELECT * into individual column references when table metadata is available.

The select.groupBy and select.orderBy fields support the following values:

  • EXPRESSION: formats GROUP BY or ORDER BY items as full expressions.
  • ALIAS: formats GROUP BY or ORDER BY items using SELECT column aliases when available.
  • INDEX: formats GROUP BY or ORDER BY items using 1-based column positions, such as GROUP BY 1.

In the following example, the configuration YAML configures the star projection to EXPAND.

type: statement_rewriter
select:
  starProjection: EXPAND

A SQL translation with this configuration YAML file might look like the following:

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
      ;
    

UDF specification

In most cases, the BigQuery SQL translator can automatically parse the data definition language (DDL) of external user-defined functions (UDFs) to determine their input and output data types, even if the UDF body is written in an external language. However, for certain source dialects, the translator might not be able to automatically compile the UDF signature. To ensure that SQL statements referencing these UDFs are translated accurately without having to supply the original DDL, you can use a YAML configuration file to explicitly register the UDF signatures.

The following configuration YAML specifies the signature of user-defined functions (UDFs) that are used in the source scripts. Specify each signature in the source dialect as RETURN_TYPE FUNCTION_NAME(ARG_NAME ARG_TYPE, ...), including a name for each parameter. Much like metadata zip files, UDF definitions can help produce a more accurate translation of input scripts.

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

A SQL translation with this configuration YAML file might look like the following:

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
      ;
    

Some source dialects support UDFs that share a name across different argument signatures, sometimes called overloaded UDFs. For overloaded UDFs, specify udf and a distinct target name so that each signature maps to a unique function name in BigQuery:

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

Strip identifier patterns during metadata lookup

Input SQL scripts might reference objects or columns with a prefix, suffix, or naming pattern that isn't in your metadata definitions—for example, referencing a_dt when the metadata DDL defines column dt. In this case, use nameAlias under type: metadata so that the translation engine strips the pattern when looking up object names in metadata. The original identifier is preserved in the output SQL.

The nameAlias field supports the following properties. Each property accepts a single string or a list of strings, and the first matching entry in each list applies:

  • stripPrefix: strips a literal prefix before looking up metadata.
  • stripSuffix: strips a literal suffix before looking up metadata.
  • stripRegex: matches a case-sensitive regular expression and replaces each matched substring with its first capturing group (...) when looking up metadata. Anchor the pattern—for example, ^a_(.*)$—to match only a prefix or the full identifier.

The following configuration YAML strips the leading a_ prefix from identifiers when looking up object names in metadata:

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

A SQL translation with this configuration YAML file might look like the following:

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
      ;
    

Setting decimal precision strictness

By default, BigQuery Migration Service increases numeric precision to the highest precision available for a given scale. The following configuration YAML overrides this behavior by configuring the precision strictness to retain the decimal precision of the source statement.

type: statement_rewriter
common:
  decimalPrecision: STRICT

A SQL translation with this configuration YAML file might look like the following:

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

Setting string precision strictness

By default, BigQuery Migration Service omits string precision when translating CHAR and VARCHAR columns. This can help prevent truncation errors when values are written. Some SQL dialects, such as Teradata, truncate values that exceed the maximum precision on write, while BigQuery returns an error in this scenario.

If your application doesn't rely on the source dialect's truncation behavior, consider preserving the column's precision in the translated type definition.

The following configuration YAML overrides this behavior by configuring the precision strictness to retain the string precision of the source statement.

type: statement_rewriter
common:
  stringPrecision: STRICT

A SQL translation with this configuration YAML file might look like the following:

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

Output name mapping

You can use type: experimental_object_rewriter in a configuration YAML file to map SQL object names (database, schema, relation, attribute, function, sequence, index, variable, or projection) statically or dynamically:

  • Each object type expands only its own name and parent hierarchy placeholders from ${database}, ${schema}, ${relation}, ${attribute}, ${function}, ${sequence}, ${index}, ${variable}, and ${projection}. For example, a schema rule expands ${database} and ${schema}, while a relation rule expands ${database}, ${schema}, and ${relation}.
  • When multiple rules of the same object type with distinct match criteria match an object, the last matching rule in the list applies.
  • Global replace and case rules under global.outputName run before object-specific outputName rules. An object-specific rule matches and can override the globally transformed name of its object. For attribute rules, match compares against the globally transformed attribute name alongside the source database, schema, and relation names. For more information, see Global output name rewrite.

For more information about how name mapping rules work across database, schema, relation, function, and attribute levels, see Map SQL object names for translation.

Static name mapping

Use static name mapping to map the name of an entity. If you only want to change specific parts of the name while keeping other parts of the name the same, then only include the parts that need to change.

The following configuration YAML changes the name of the table from my_db.my_schema.my_table to 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"

A SQL translation with this configuration YAML file might look like the following:

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

You can use static name mapping to update the region used by names in the public user-defined functions.

The following example changes the names in the bqutil.fn UDF from using the default us multi-region to using the europe_west2 region:

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

Dynamic name mapping

Use dynamic name mapping to change several objects at the same time, and create new names based on the mapped objects.

The following configuration YAML changes the name of all tables by adding the prefix stg_ to those that belong to the staging schema, and then moves those tables to the production schema.

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

A SQL translation with this configuration YAML file might look like the following:

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

Column name mapping

Use the attribute section under type: experimental_object_rewriter to rename columns across both table definitions and query references:

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

A SQL translation with this configuration YAML file might look like the following:

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
      ;
    

Specifying default database and schema search path

The following configuration YAML specifies a default database and schema search path.

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

A SQL translation with this configuration YAML file might look like the following:

teradata-input.sql
      SELECT * FROM mydb.mytable;
      SELECT * FROM table1;
    
bq-output.sql
      SELECT * FROM myproject.mydb.mytable;
      SELECT * FROM myproject.myschema1.table1;
    

Setting NLS_DATE_FORMAT

The following configuration YAML sets the NLS_DATE_FORMAT parameter to the format DD/MM/YYYY. We recommend that you specify NLS_DATE_FORMAT for implicit uses of date format and casts. If not set, the default format for translation, DD-MON-RR, is used.

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

A SQL translation with this configuration YAML file might look like the following:

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

Global output name rewrite

The following configuration YAML uses replace rules under global.outputName to rewrite the output names of all objects (all) or specific object types (database, schema, relation, attribute, function, sequence, index, variable, or projection). Each rule matches a case-sensitive regular expression and replaces matches with new text, including $0, $1, and other capture groups.

Global outputName transformations (replace followed by case) run before object-specific outputName rules, such as schema, relation, or attribute. An object-specific rule matches and can override the globally transformed name of its object. For attribute rules, match compares against the globally transformed attribute name alongside the source database, schema, and relation names:

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

A SQL translation with this configuration YAML file might look like the following:

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

Optimize and improve the performance of translated SQL

Optional transformations can be applied to translated SQL in order to introduce changes that can improve performance or cost. These optimizations are strictly case dependent and should be evaluated against unmodified SQL output to assess their actual effect on performance.

The following configuration YAML enables optional transformations. Each entry in the transformations list supports the following fields:

  • name: the optimization name.
  • enabled (optional): a boolean that defaults to true. Set enabled to false to disable an optimization that is enabled by default for the source dialect.
  • parameters (optional): a map of optimization-specific parameters.
type: optimizer
transformations:
  - name: PRECOMPUTE_INDEPENDENT_SUBSELECTS
  - name: REWRITE_CTE_TO_TEMP_TABLE
    parameters:
      threshold: 1
Optimization Optional parameter Description
PRECOMPUTE_INDEPENDENT_SUBSELECTS scope: [PREDICATE, PROJECTION] Rewrites the query by adding a DECLARE statement to replace an expression in PREDICATE clauses, PROJECTION clauses, or both with a precomputed variable. This rewrite turns the expression into a static predicate that can reduce the amount of data read. The scope parameter accepts a list containing PREDICATE, PROJECTION, or both. If parameters is omitted, then scope defaults to [PREDICATE], which covers WHERE and JOIN ON clauses.

Extracting a scalar subquery to a DECLARE statement will make the original predicate static and therefore qualify for improved execution planning. This optimization will introduce new SQL statements.
REWRITE_CTE_TO_TEMP_TABLE threshold: N Rewrites common table expressions (CTEs) to temporary tables when there are at least N references to the same CTE. This rewrite reduces query complexity and forces single execution of the CTE. If N is omitted, then the default value is 4.

We recommend using this optimization when non-trivial CTEs are referenced multiple times. Introducing temporary tables has an overhead that might be larger than eventual multiple executions of a low complexity or low cardinality CTE. This optimization will introduce new SQL statements.
REWRITE_ZERO_SCALE_NUMERIC_AS_INTEGER bigint: N Rewrites zero-scale NUMERIC/BIGNUMERIC attributes to INT64 type if the precision is within N. If N is omitted, the default value is 18.

We recommend using this optimization when you translate from source dialects that don't have integer types. Changing column types requires reviewing all downstream uses for type compatibility and semantic changes. For example, fractional divisions becoming integer divisions, or code expecting numeric values.

Snowflake translations have this optimization for zero-scale numerics up to precision 38 enabled by default. This optimization ensures that a Snowflake INTEGER, which is implicitly represented as NUMBER(38,0) in Snowflake, translates to a BigQuery INT64 instead of a BIGNUMERIC(38).

If your application uses numbers with precisions that are over 18, we recommend disabling this functionality to ensure that BigQuery can process the full range of values needed by your application.
DROP_TEMP_TABLE Adds DROP TABLE statements for all temporary tables created in a script and not dropped by the end of it. This reduces the storage billing period for the temporary table from 24 hours to the script running time.

We recommend using this optimization when temporary tables are not accessed for any further processing after the end of script execution. This optimization will introduce new SQL statements.
REGEXP_CONTAINS_TO_LIKE Rewrites some categories of REGEXP_CONTAINS matching patterns to LIKE expressions.

We recommend using this optimization when no other process, such as macro replacement, relies on the regular expression pattern literals being preserved unchanged in output SQL.
ADD_DISTINCT_TO_SUBQUERY_IN_SET_COMPARISON Adds DISTINCT clause to subqueries used as value set for [NOT] IN operator.

We recommend using this optimization when the cardinality (distinct number of values) of the subquery result is significantly lower than the number of values. When this precondition is not met this transformation can have negative effects on performance.
APPROXIMATE_RANGE_PARTITIONS Approximates non-contiguous or non-regular integer partitioning schemes by converting them to contiguous, equally sized partition ranges supported by BigQuery. By default, such partitioning schemes don't influence the table partitioning scheme in translated DDL statements.

We recommend using this optimization when the source table uses a non-contiguous partitioning function like the Teradata RANGE_N function and would benefit from an equally sized partition scheme in BigQuery.

Optimization examples

The following optimization converts zero-scale numeric types with precision less than or equal to 38 to INT64 in 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

A SQL translation with this optimization might look like the following:

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

The following configuration disables the optimization in dialects, such as Snowflake, where it is enabled by default. This configuration converts numeric types to either NUMERIC or BIGNUMERIC depending on the input precision, instead of the default of INT64.

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

A SQL translation with this optimization might look like the following:

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

Create a Gemini-based configuration YAML file

To generate AI output, provide at least one *.ai_config.yaml file and enable Gemini AI suggestions when the interface requires it. For more information, see Provide configuration files to a translation.

Requirements

The configuration YAML file for AI outputs must have a suffix of .ai_config.yaml. For example, rules_1.ai_config.yaml.

Supported fields

Unlike a *.config.yaml file, a *.ai_config.yaml file doesn't include a type header. Instead, the file contains a top-level translation_rules list, where each rule supports the following fields:

  • suggestion_type (optional): Specify the type of AI suggestion to be generated. The following suggestion types are supported:
    • QUERY_CUSTOMIZATION (default): Generates AI suggestions for SQL code based on the translation rules specified in the *.ai_config.yaml file.
    • TRANSLATION_EXPLANATION: Generates text that includes a summary of the translated GoogleSQL query and the differences and inconsistencies between the source SQL query and the translated GoogleSQL query.
  • rewrite_target (optional): Specify SOURCE_SQL to apply the rule to the original input SQL before the translation engine runs, or TARGET_SQL to apply the rule to the translated SQL that the translation engine produces. The default value is TARGET_SQL. For more information, see How the two kinds of rules are applied.
  • instruction (optional): Describe in natural language the change to apply to the input SQL or translated SQL for QUERY_CUSTOMIZATION rules, or the explanation to generate for TRANSLATION_EXPLANATION rules.
  • examples (optional): Provide a list of input and output SQL pairs that show how you want a SQL pattern to be modified.

You can add additional translation_rules and additional examples as necessary.

Examples

The following examples create Gemini-based configuration YAML files which you can use with your SQL translations.

Remove the upper function in the default translation output query

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

Create multiple translation rules to customize the translation output

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

Remove SQL comments from the translation input query

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

Generate translation explanations using default LLM prompt

This example uses the default LLM prompts provided by the translation service to generate text explanations:

translation_rules:
- suggestion_type: "TRANSLATION_EXPLANATION"

Generates translation explanations using your own natural language prompts

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

Multiple suggestion types in a single configuration YAML file

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

Applying multiple YAML configurations

When you translate a single query or batch SQL files, you can provide multiple configuration YAML files in a single translation job to reflect multiple transformations. If multiple configurations conflict, one transformation might override another. We recommend using different types of configuration settings in each file to avoid conflicting transformations in the same translation job.

The following example lists two separate configuration YAML files that were provided for a single SQL translation job, one to change a column's attribute, and the other to set the table as temporary:

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

A SQL translation with these two configuration YAML files might look like the following:

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