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:
- Create configuration files: Create deterministic
*.config.yamltranslation configuration files,*.ai_config.yamlGemini-based configuration files, or both types of configuration files to combine structural rules with Gemini customization. - Provide the files to a translation job: Submit the configuration files when you translate a single query or batch SQL files.
- 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 |
|
|
| 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:
- Gemini applies
SOURCE_SQLrules in*.ai_config.yamlfiles to the original input SQL to produce rewritten source SQL. - The translation engine translates the rewritten source SQL from the
preceding step—or the original input SQL if no
SOURCE_SQLrules apply—and applies any rules in*.config.yamlfiles to produce the translated SQL. - Gemini applies
TARGET_SQLrules in*.ai_config.yamlfiles to the translated SQL from the preceding step to produce rewritten target SQL. If your*.ai_config.yamlfiles includeTRANSLATION_EXPLANATIONrules, 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 translatecommand, use the--translation-config-filesflag to specify either*.config.yamlor*.ai_config.yamlfiles. - When using the
gcloud alpha bq migration translate-batchcommand, do one of the following:*.config.yaml: Include the files in the input directory that you pass to--source-gcs-urisor--source-local-dirs, or pass individual files with the--source-gcs-filesor--source-local-filesflag.*.ai_config.yaml: Include the files in the input directory that you pass to--source-gcs-urisor--source-local-dirs, or pass individual files with the--source-gcs-filesor--source-local-filesflag, and add the--enable-ai-suggestionflag.
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 insource_spec.base_uri, pass agcs_file_pathentry insource_specwhen the file isn't underbase_uri, or pass an inlineliteralentry insource_spec.*.ai_config.yaml: Provide the files insource_specunderbase_uri, as agcs_file_pathentry when the file isn't underbase_uri, or as an inlineliteralentry, and include"suggestion"intarget_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 inlinetranslationConfigsentries.*.ai_config.yaml: Pass inlineaiSuggestionConfigsentries.
- To translate batch SQL files (
translate_batch_queries), do the following:*.config.yaml: Upload the files undersourceBaseUri, or list them inconfigurationFilePathswhen they aren't undersourceBaseUri.*.ai_config.yaml: Upload the files undersourceBaseUri, or list them inconfigurationFilePathswhen they aren't undersourceBaseUri, and setenableAiSuggestionstotrue.
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.yamlfiles. - Rewritten source SQL from Gemini after it applies
SOURCE_SQLrules in*.ai_config.yamlfiles. - Rewritten target SQL from Gemini after it applies
TARGET_SQLrules in*.ai_config.yamlfiles. - A translation explanation from Gemini when your
*.ai_config.yamlfiles includeTRANSLATION_EXPLANATIONrules, 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>, ortranslation_explanation_suggestion/<var>PATH</var>—totarget_return_literalsto receive them intranslatedLiterals. - To find outputs when you use the BigQuery Migration Service MCP server, see
Use the BigQuery Migration Service MCP
server. The
translate_queryandget_translationtools returntranslatedQuery,sourceSqlSuggestion, andtargetSqlSuggestion, and theexplain_translationtool 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.yamlfiles 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.yamlfile must not exceed 1 MB. - The total file size of all
*.config.yamlfiles used in a single SQL translation job must not exceed 4 MB. - If you are using
regexsyntax for name matching, use RE2/J. - All translation configuration file names must end with
.config.yaml—for example,change-case.config.yaml.config.yamlalone 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:
Header
Each *.config.yaml file must include a top-level type header that
specifies the category of configuration rules in the file:
object_rewriter: rewritesrelationproperties—such astemporary,ephemeral,partitionLifetime,external,description,partition, andclustering—andattributedata types (type).experimental_object_rewriter: rewrites output object names withoutputNameacrossglobal,database,schema,relation,attribute,function,index,sequence,variable, andprojection, and converts data types globally withglobal.typeConvert. For more information, see Output name mapping and Global type conversion.environment: sets session-level defaults undersession, such asdefaultDatabase,schemaSearchPath, anddateFormat.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 asSELECT *expansion,GROUP BYandORDER BYformatting (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:
databaseordb: matches the source database name, which corresponds to theproject_idcomponent in BigQuery.schema: matches the source schema name, which corresponds to thedatasetcomponent in BigQuery.relation: matches the source table or view name.attribute: matches the source column name. This property is valid only forattributeselection.databaseRegexordbRegex: matches the entiredatabasename against a case-insensitive regular expression.schemaRegex: matches the entireschemaname against a case-insensitive regular expression.relationRegex: matches the entirerelationname against a case-insensitive regular expression.attributeRegex: matches the entireattributename against a case-insensitive regular expression. This property is valid only forattributeselection.
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:
BOOLEANTINYINTSMALLINTINTEGERBIGINTFLOATDOUBLENUMERIC(Supports optional precision and scale, such asNUMERIC(18, 2))TIMETIMETZDATEDATETIMETIMESTAMPTIMESTAMPTZCHAR(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 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:
DATETIMEtoTIMESTAMPTIMESTAMPtoDATETIME(accepts optional time zone)TIMESTAMP WITH TIME ZONE(timestamptz) toDATETIME(accepts optional time zone)CHARtoVARCHAR(accepts optionalprecisionActionandcomparisonBehavior)VARCHARtoVARCHAR(accepts optionalprecisionActionandcomparisonBehavior)
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 toVARCHAR. Configuringtarget: VARCHARoncharremovesCHARright-padding (CONCAT) even whenprecisionActionisPRESERVE.precisionAction: controls character-length precision andSUBSTRtruncation wrappers:PRESERVE(default): retains character-length precision on expressions and emitsSUBSTRtruncation wrappers when casting or inserting intoCHAR(N)orVARCHAR(N)columns. To also emitSTRING(N)column lengths inCREATE TABLEDDL, see Setting string precision strictness.DROP: drops character-length precision on expressions and omitsSUBSTRtruncation wrappers.
comparisonBehavior: controlsRTRIMandUPPERwrappers for comparisons andGROUP BYkeys. Setting a value other thanSTRICTon eithercharorvarcharapplies 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 omitsUPPER.CASE_AWARE: emulates only case-insensitive collation (UPPER) and omitsRTRIM.NONE: omits bothRTRIMandUPPERwrappers, so the output uses direct comparisons andGROUP BYexpressions.
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
datetimeis a combination ofY-M-D H:M:Sthat is not fixed to any particular time zone.datetimerepresents a wall-clock time and not a particular instant. - A
timestamprepresents 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
timestamptzrepresents a particular instant like atimestamp, but unlike atimestampit carries with it a particular time zone offset. While they represent the same instant,2019-06-01 12:00:00+4and2019-06-01 06:00:00-2are considered differenttimestamptzvalues.
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: emitsSELECT *when the source query usesSELECT *or lists all columns of the table in order.PRESERVE(default): emitsSELECT *only when the source query usesSELECT *.EXPAND: expandsSELECT *into individual column references when table metadata is available.
The select.groupBy and select.orderBy fields support the following values:
EXPRESSION: formatsGROUP BYorORDER BYitems as full expressions.ALIAS: formatsGROUP BYorORDER BYitems usingSELECTcolumn aliases when available.INDEX: formatsGROUP BYorORDER BYitems using 1-based column positions, such asGROUP 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, aschemarule expands${database}and${schema}, while arelationrule expands${database},${schema}, and${relation}. - When multiple rules of the same object type with distinct
matchcriteria match an object, the last matching rule in the list applies. - Global
replaceandcaserules underglobal.outputNamerun before object-specificoutputNamerules. An object-specific rule matches and can override the globally transformed name of its object. Forattributerules,matchcompares against the globally transformedattributename alongside the sourcedatabase,schema, andrelationnames. 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 totrue. Setenabledtofalseto 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.yamlfile.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): SpecifySOURCE_SQLto apply the rule to the original input SQL before the translation engine runs, orTARGET_SQLto apply the rule to the translated SQL that the translation engine produces. The default value isTARGET_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 forQUERY_CUSTOMIZATIONrules, or the explanation to generate forTRANSLATION_EXPLANATIONrules.examples(optional): Provide a list ofinputandoutputSQL 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) ) ; |