Transform SQL translations using configuration YAML files

This document shows you how to use configuration YAML files to transform SQLcode while migrating it to BigQuery. It provides guidelines tocreate your own configuration YAML files, and provides examples for varioustranslation transformations that are supported by this feature.

When using theBigQueryinteractive SQL translator,using theBigQuery Migration API,or performing abatch SQL translation,you can provide configuration YAML files to modify a SQL query translation.Using configuration YAML files allows for further customization whentranslating SQL queries from your source database.

You can specify a configuration YAML file to use in a SQL translation in thefollowing ways:

• If you are using theinteractive SQL translator,specify the file path to theconfiguration file or batch translation job ID in the translationsettings.
• If you are using the BigQuery Migration API, place theconfiguration YAML in the same Cloud Storage bucket as the input SQLfiles.
• If you are performing a batch SQL translation, place the configuration YAMLin the same Cloud Storage bucket as the input SQL files.
• If you are using thebatch translation Python client,place the configuration YAML file in the local translation input folder.

The interactive SQL translator, BigQuery Migration API,the batch SQL translator, and the batch translation Python client supportsthe use of multiple configuration YAML files in a single translation job. SeeApplying multiple YAML configurationsfor more information.

Configuration YAML file requirements

Before creating a configuration YAML file, review the following informationto ensure that your YAML file is compatible to use with theBigQuery Migration Service:

• You must upload the configuration YAML files in the directory of theCloud Storage bucket that contains your SQL translation input files.For information on how to create buckets and upload files to Cloud Storage,seeCreate buckets andUpload objects from a filesystem.
• The file size for a single configuration YAML file must not exceed 1 MB.
• The total file size of all configuration YAML files used in a single SQLtranslation job must not exceed 4 MB.
• If you are usingregex syntax for name matching, useRE2/J.
• All configuration YAML file names must include a.config.yaml extension—for example,change-case.config.yaml.
  • config.yaml alone is not a valid name for the configuration file.

Guidelines to create a configuration YAML file

This section provides some general guidelines to create a configuration YAMLfile:

Header

Each configuration file must contain a header specifying the type ofconfiguration. Theobject_rewriter type is used to specify SQL translations ina configuration YAML file. The following example uses theobject_rewritertype to transform a name case:

type:object_rewriterglobal:case:all:UPPERCASE

Entity selection

To perform entity-specific transformations, specify the entity in theconfiguration file. Allmatch properties are optional; only use thematchproperties needed for a transformation. The following configuration YAMLexposes 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 eachmatch property:

• database ordb: the project_id component.
• schema: the dataset component.
• relation: the table component.
• attribute: the column component. Only valid for attribute selection
• databaseRegex ordbRegex: matches adatabase property with a regular expression (Preview).
• schemaRegex: matchesschema properties to regular expressions (Preview).
• relationRegex: matchesrelation properties with regular expressions (Preview).
• attributeRegex: matchesattribute properties with regularexpressions. Only valid for attribute selection (Preview).

For example, the following configuration YAML specifies thematchproperties to select thetestdb.acme.employee table for a temporary tabletransformation.

type:object_rewriterrelation:-match:database:testdbschema:acmerelation:employeetemporary:true

You can use thedatabaseRegex,schemaRegex,relationRegex, andattributeRegex properties to specify regular expressions in order to select asubset of entities. The following example changes all relations fromtmp_schema schema intestdb to temporary, as long as their name startswithtmp_:

type:object_rewriterrelation:-match:schema:tmp_schemarelationRegex:"tmp_.*"temporary:true

Both literal andregex properties are matched in a case-insensitive manner.You can enforce case-sensitive matching by using aregex with a disablediflag, as seen in the following example:

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

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

type:object_rewriterrelation:-match:"testdb.acme.employee"temporary:true

If a table contains a dot in the name, you cannot specify the name using a shortsyntax. In this case, you must use an object match. The following examplechanges thetestdb.acme.stg.employee table to temporary:

type:object_rewriterrelation:-match:database:testdbschema:acmerelation:stg.employeetemporary:true

The configuration YAML acceptskey as an alias tomatch.

Default database

Some input SQL dialects, notably Teradata, do not supportdatabase-name in thequalified name. In this case, the easiest way to match entities is to omitdatabase property inmatch.

However, you can set thedefault_database property of the BigQuery Migration Serviceand use that default database in thematch.

Supported target attribute types

You can use the configuration YAML file toperform attribute typetransformations, where you transform thedata type of a column from the source type to a target type. Theconfiguration YAML file supports the following target types:

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

Configuration YAML examples

This section provides examples to create various configuration YAML files touse with your SQL translations. Each example outlines the YAML syntax totransform your SQL translation in specific ways, along with a brief description.Each example also provides the contents of ateradata-input.sql orhive-input.sql file and abq-output.sql file so that you can compare theeffects of a configuration YAML on a BigQuery SQL querytranslation.

The following examples use Teradata or Hive as the input SQLdialect and BigQuery SQL as the output dialect. The followingexamples also usetestdb as the default database, andtestschema as theschema search path.

Change object-name case

This product or feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA products and features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

The following configuration YAML changes the upper or lower-casing of objectnames:

type:object_rewriterglobal:case:all:UPPERCASEdatabase:LOWERCASEattribute:LOWERCASE

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

createtablex(aint);select*fromx;

CREATETABLEtestdb.TESTSCHEMA.X(aINT64);SELECTX.aFROMtestdb.TESTSCHEMA.X;

Make table temporary

This product or feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA products and features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

The following configuration YAML changes a regular table to atemporarytable:

type:object_rewriterrelation:-match:"testdb.testschema.x"temporary:true

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

createtablex(aint);

CREATETEMPORARYTABLEx(aINT64);

Make table ephemeral

This product or feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA products and features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

The following configuration YAML changes a regular table to anephemeraltable with a60 second expiration.

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

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

createtablex(aint);

CREATETABLEtestdb.testschema.x(aINT64)OPTIONS(expiration_timestamp=timestamp_add(current_timestamp(),interval60SECOND));

Set partition expiration

This product or feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA products and features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

The following configuration YAML changes theexpiration of a partitionedtable to 1 day:

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

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

createtablex(aint,bint)partitionby(a);

CREATETABLEtestdb.testschema.x(aINT64,bINT64)CLUSTERBYaOPTIONS(partition_expiration_days=1);

Change external location or format for a table

This product or feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA products and features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

The following configuration YAML changes theexternal location and formatfor a table:

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

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

createtablex(aint);

CREATEEXTERNALTABLEtestdb.testschema.x(aINT64)OPTIONS(format='ORC',uris=['gs://path/to/department/files']);

Set or change table description

This product or feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA products and features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

The following configuration YAML sets the description of a table:

type:object_rewriterrelation:-match:"testdb.testschema.x"description:text:"Exampledescription."

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

createtablex(aint);

CREATETABLEtestdb.testschema.x(aINT64)OPTIONS(description='Example description.');

Set or change table partitioning

The following configuration YAML changes thepartitioning scheme of a table:

type:object_rewriterrelation:-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 thefollowing:

createtablex(adate,bint);createtabley(adate,bint)partitionby(a);

CREATETABLEtestdb.testschema.x(aDATE,bINT64)PARTITIONBYa;CREATETABLEtestdb.testschema.y(aDATE,bINT64);

Set or change table clustering

The following configuration YAML changes theclustering scheme of a table:

type:object_rewriterrelation:-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 thefollowing:

createtablex(aint,bint);createtabley(aint,bint)clusteredby(b)into16buckets;

CREATETABLEtestdb.testschema.x(aINT64,bINT64)CLUSTERBYa;CREATETABLEtestdb.testschema.y(aINT64,bINT64);

Change type of a column attribute

This product or feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA products and features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

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

type:object_rewriterattribute:-match:database:testdbschema:testschemaattributeRegex:"a+"type:target:NUMERIC(10,2)

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

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

createtablex(aint,bint,aaint);

CREATETABLEtestdb.testschema.x(aNUMERIC(31,2),bINT64,aaNUMERIC(31,2));

This product or feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA products and features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

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

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

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

CREATETABLEx(aVARCHAR(150),bINT);

CREATEEXTERNALTABLEx(aSTRING,bINT64)WITHCONNECTION`connection_test`OPTIONS();

Change the character encoding of an input file

This product or feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA products and features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

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

The following configuration YAML specifies the explicit character encodingof the input file asISO-8859-1.

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

Global type conversion

This product or feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA products and features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

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

BigQuery supports the following data type conversions:

• DATETIME toTIMESTAMP
• TIMESTAMP toDATETIME (accepts optional time zone)
• TIMESTAMP WITH TIME ZONE toDATETIME (accepts optional time zone)
• CHAR toVARCHAR

In the following example, the configuration YAML converts aTIMESTAMPdata type toDATETIME.

type:experimental_object_rewriterglobal:typeConvert:timestamp:DATETIME

In dialects like Teradata, datetime-related functions such ascurrent_date,current_time, orcurrent_timestamp return timestamps based onthe configured time zone, either local or session. BigQuery, on theother hand, always returns timestamps in UTC. To ensure consistentbehavior between the two dialects, it is necessary to configure the time zoneaccordingly.

In the following example, the configuration YAML converts aTIMESTAMP and aTIMESTAMP WITH TIME ZONE data type toDATETIME, with the target time zoneset toEurope/Paris.

type:experimental_object_rewriterglobal:typeConvert:timestamp:target:DATETIMEtimezone:Europe/Paristimestamptz:target:DATETIMEtimezone:Europe/Paris

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

createtablex(atimestamp);selectafromxwherea>current_timestamp(0);

CREATETABLEx(aTIMESTAMP);SELECTx.aFROMtest.xWHEREx.a>datetime_trunc(current_datetime('Europe/Paris'),SECOND);

Select statement modification

This product or feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA products and features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

The following configuration YAML changes the star projection,GROUP BY, andORDER BY clauses inSELECT statements.

starProjection supports the following configurations:

• ALLOW
• PRESERVE (default)
• EXPAND

groupBy andorderBy support the following configurations:

• EXPRESSION
• ALIAS
• INDEX

In the following example, the configuration YAML configures the starprojection toEXPAND.

type:experimental_statement_rewriterselect:starProjection:EXPAND

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

createtablex(aint,bTIMESTAMP);select*fromx;

CREATETABLEx(aINT64,bDATETIME);SELECTx.ax.bFROMx;

UDF specification

The following configuration YAML specifies the signature of user-definedfunctions (UDFs) that are used in the source scripts. Much likemetadata zip files,UDF definitions can help to produce a more accurate translation of inputscripts.

type:metadataudfs:-"dateparse_short_date(dtint)"

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

createtablex(dtint);selectparse_short_date(dt)+1fromx;

CREATETABLEx(dtINT64);SELECTdate_add(parse_short_date(x.dt),interval1DAY)FROMx;

Setting decimal precision strictness

This product or feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA products and features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

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

type:experimental_statement_rewritercommon:decimalPrecision:STRICT

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

createtablex(adecimal(3,0));

CREATETABLEx(aNUMERIC(3));

Setting string precision strictness

This product or feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA products and features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

By default, BigQuery Migration Service omits string precision when translatingCHARandVARCHAR columns. This can help prevent truncation errors when valuesare written. Some SQL dialects, such as Teradata, truncate valuesthat exceed the maximum precision on write, while BigQueryreturns 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 YAMLoverrides this behavior by configuring the precision strictness to retainthe string precision of the source statement.

type:experimental_statement_rewritercommon:stringPrecision:STRICT

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

createtablex(avarchar(3));

CREATETABLEx(aSTRING(3));

Output name mapping

This product or feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA products and features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

You can use configuration YAML to map SQL object names. You can changedifferent parts of the name depending on the object being mapped.

Static name mapping

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

The following configuration YAML changes the name of the table frommy_db.my_schema.my_table tomy_new_db.my_schema.my_new_table.

type:experimental_object_rewriterrelation:-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 thefollowing:

createtablemy_db.my_schema.my_table(aint);

CREATETABLEmy_new_db.my_schema.my_new_table(aINT64)

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

The following example changes the names in thebqutil.fn UDFfrom using the defaultus multi-region to using theeurope_west2 region:

type:experimental_object_rewriterfunction:-match:database:bqutilschema:fnoutputName:database:bqutilschema:fn_europe_west2

Dynamic name mapping

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

The following configuration YAML changes the name of all tables by adding theprefixstg_ to those that belong to thestaging schema, and then moves thosetables to theproduction schema.

type:experimental_object_rewriterrelation:-match:schema:stagingoutputName:schema:productionrelation:"stg_${relation}"

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

createtablestaging.my_table(aint);

CREATETABLEproduction.stg_my_table(aINT64);

Specifying default database and schema search path

This product or feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA products and features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

The following configuration YAML specifies adefault databaseandschema search path.

type:environmentsession:defaultDatabase:myprojectschemaSearchPath:[myschema1,myschema2]

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

SELECT*FROMdatabase.tableSELECT*FROMtable1

SELECT*FROMmyproject.database.table.SELECT*FROMmyproject.myschema1.table1

Global output name rewrite

This product or feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA products and features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

The following configuration YAML changes the output names of all objects(database, schema, relation, and attributes) in the script according tothe configured rules.

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

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

createtable"test special chars >= 12"("42eid"int,"custom column"varchar(10));

CREATETABLEtest_special_chars_employees_gte_12(_42eidINT64,custom_columnSTRING);

Optimize and improve the performance of translated SQL

Optional transformations can be applied to translated SQL in order to introducechanges that can improve the query in terms of performance or cost. Theseoptimizations are strictly case dependent and should be evaluated against unmodifiedSQL output to assess their actual effect on performance.

The following configuration YAML enables optional transformations. The configurationaccepts a list of optimizations and, for optimizations which accept parameters,a section with optional parameter values.

type:optimizertransformations:-name:PRECOMPUTE_INDEPENDENT_SUBSELECTS-name:REWRITE_CTE_TO_TEMP_TABLEparameters:threshold:1

Create a Gemini-based configuration YAML file

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

translation_rules:-instruction:"Removeupper()function"suggestion_type:QUERY_CUSTOMIZATIONrewrite_target:TARGET_SQLexamples:-input:"upper(X)"output:"X"-instruction:"Insertacommentattheheadthatexplainseachstatementindetail.suggestion_type:QUERY_CUSTOMIZATIONrewrite_target:TARGET_SQL

translation_rules:-instruction:"Removeallthesqlcommentsintheinputsqlquery."suggestion_type:QUERY_CUSTOMIZATIONrewrite_target:SOURCE_SQL

translation_rules:-suggestion_type:"TRANSLATION_EXPLANATION"

translation_rules:-suggestion_type:"TRANSLATION_EXPLANATION"instruction:"ExplainthesyntaxdifferencesbetweenthesourceTeradataqueryandthetranslatedGoogleSQLquery."

translation_rules:-suggestion_type:"TRANSLATION_EXPLANATION"instruction:"ExplainthesyntaxdifferencesbetweenthesourceTeradataqueryandthetranslatedGoogleSQLquery."-instruction:"Removeupper()function"suggestion_type:QUERY_CUSTOMIZATIONrewrite_target:TARGET_SQLexamples:-input:"upper(X)"output:"X"-instruction:"Removeallthesqlcommentsintheinputsqlquery."suggestion_type:QUERY_CUSTOMIZATIONrewrite_target:SOURCE_SQL

type:object_rewriterattribute:-match:"testdb.testschema.x.a"type:target:NUMERIC(10,2)

type:object_rewriterrelation:-match:"testdb.testschema.x"temporary:true

createtablex(aint);

CREATETEMPORARYTABLEx(aNUMERIC(31,2));