Translate queries with the interactive SQL translator
This document describes how to translate a query from a different SQL dialect into a GoogleSQL query by using the BigQuery interactive SQL translator. The interactive SQL translator can help reduce time and effort when you migrate workloads to BigQuery. This document is intended for users who are familiar with the Google Cloud console.
If your location is supported, you can use the translation rule feature (Preview) to customize the way the interactive SQL translator translates SQL.
Limitations
You must be logged in with a Google Account to use the interactive SQL translator. The interactive SQL translator does not support the use of federated identities.
Before you begin
If your Google Cloud CLI project was created before February 15, 2022, enable the BigQuery Migration API as follows:
In the Google Cloud console, go to the BigQuery Migration API page.
Click Enable.
Permissions and roles
This section describes the Identity and Access Management (IAM) permissions that you need in order to use the interactive SQL translator, including the predefined IAM roles that grant those permissions. The section also describes the permissions needed to configure additional translation configurations.
Permissions to use the interactive SQL translator
To get the permissions that you need to use the interactive translator,
ask your administrator to grant you the
MigrationWorkflow Editor (roles/bigquerymigration.editor
) IAM role on the parent
resource.
For more information about granting roles, see Manage access to projects, folders, and organizations.
This predefined role contains the permissions required to use the interactive translator. To see the exact permissions that are required, expand the Required permissions section:
Required permissions
The following permissions are required to use the interactive translator:
-
bigquerymigration.workflows.create
-
bigquerymigration.workflows.get
You might also be able to get these permissions with custom roles or other predefined roles.
Permissions to configure additional translation configurations
You can configure additional translation configurations using the Translation Config ID and Translation Configuration Source Location fields in the translation settings. To configure these translation configurations, you need the following permissions:
bigquerymigration.workflows.get
bigquerymigration.workflows.list
The following predefined IAM role provide the permissions that you need to configure additional translation configurations:
roles/bigquerymigration.viewer
For more information about BigQuery IAM, see Access control with IAM.
Supported SQL dialects
The BigQuery interactive SQL translator can translate the following SQL dialects into GoogleSQL:
- Amazon Redshift SQL
- Apache HiveQL and Beeline CLI
- IBM Netezza SQL and NZPLSQL
- Teradata and Teradata Vantage:
- SQL
- Basic Teradata Query (BTEQ)
- Teradata Parallel Transport (TPT)
Additionally, translation of the following SQL dialects is supported in preview:
- Apache Spark SQL
- Azure Synapse T-SQL
- Greenplum SQL
- IBM DB2 SQL
- MySQL SQL
- Oracle SQL, PL/SQL, Exadata
- PostgreSQL SQL
- Trino or PrestoSQL
- Snowflake SQL
- SQL Server T-SQL
- SQLite
- Vertica SQL
Locations
The interactive SQL translator is available in the following processing locations:
Region description | Region name | Details | |
---|---|---|---|
Asia Pacific | |||
Tokyo | asia-northeast1 |
||
Mumbai | asia-south1 |
||
Singapore | asia-southeast1 |
||
Sydney | australia-southeast1 |
||
Europe | |||
EU multi-region | eu |
||
Warsaw | europe-central2 |
||
Finland | europe-north1 |
|
|
Madrid | europe-southwest1 |
|
|
Belgium | europe-west1 |
|
|
London | europe-west2 |
|
|
Frankfurt | europe-west3 |
|
|
Netherlands | europe-west4 |
|
|
Zürich | europe-west6 |
|
|
Paris | europe-west9 |
|
|
Turin | europe-west12 |
||
Americas | |||
São Paulo | southamerica-east1 |
|
|
US multi-region | us |
||
Iowa | us-central1 |
|
|
South Carolina | us-east1 |
||
Northern Virginia | us-east4 |
||
Columbus, Ohio | us-east5 |
||
Dallas | us-south1 |
|
|
Oregon | us-west1 |
|
|
Los Angeles | us-west2 |
||
Salt Lake City | us-west3 |
The translation rule feature is available in the following processing locations:
us
(US multi-region)eu
(EU multi-region)us-central1
(Iowa)europe-west4
(Netherlands)
Translate a query into GoogleSQL
Follow these steps to translate a query into GoogleSQL:
In the Google Cloud console, go to the BigQuery page.
In the Editor pane, click More, and then select Translation settings.
For Source dialect, select the SQL dialect that you want to translate.
Optional. For Processing location, select the location where you want the translation job to run. For example, if you are in Europe and you don't want your data to cross any location boundaries, select the
eu
region.Click Save.
In the Editor pane, click More, and then select Enable SQL translation.
The Editor pane splits into two panes.
In the left pane, enter the query you want to translate.
Click Translate.
BigQuery translates your query into GoogleSQL and displays it in the right pane. For example, the following screenshot shows translated Teradata SQL:
Optional: To run the translated GoogleSQL query, click Run.
Optional: To return to the SQL editor, click More, and then select Disable SQL translation.
The Editor pane returns to a single pane.
Configure the interactive SQL translator
You can configure the interactive SQL translator to adjust how the interactive SQL translator translates your source SQL. You can do so by providing your own rules for use with Gemini in a YAML configuration file, or by providing a configuration YAML file containing SQL object metadata or object mapping information.
Create a translation rule
You can customize the way the interactive SQL translator translates SQL by creating translation rules. The interactive SQL translator adjusts its translations based on any Gemini-enhanced SQL translation rules that you assign to it, letting you customize the translation results based on your migration needs. This feature is supported only in certain locations.
To create a Gemini-enhanced SQL translation rule, do one of the following:
Console
To create a Gemini-enhanced SQL translation rule, do the following:
After running an interactive translation, click Customize.
Use one or both of the following prompts to create a translation rule:
In the Replace a SQL pattern with another prompt, specify a SQL pattern that you want to replace in the Replace field, and a SQL pattern to replace it in the With field.
A SQL pattern can contain any number of statements, clauses, or functions in a SQL script. When you create a rule using this prompt, the Gemini enhanced SQL translation identifies any instances of that SQL pattern in the translation output and dynamically replaces it with another SQL pattern. For example, you can use this prompt to create a rule that replaces all occurrences of
months_between (X,Y)
withdate_diff(X,Y,MONTH)
.In the Describe a change to the translation output field, describe a change to the SQL translation output in natural language.
When you create a rule using this prompt, the Gemini-enhanced SQL translation identifies the request and makes the specified change to the translation output.
Click Preview.
In the Preview suggested changes dialog, review the changes made by the Gemini-enhanced SQL translation to the translation output based on your rule.
Optional: To add this rule for use with future translations, select the Add rule... checkbox.
Rules are saved in the default configuration YAML file, or
__default.ai_config.yaml
. This configuration YAML file is saved to the Cloud Storage folder as specified in the Translation Configuration Source Location field in the translation settings. A configuration YAML file can support up to 10 translation rules, and is subject to configuration file size limitations.To apply the suggested changes to the translation output, click Apply.
YAML
You can create a Gemini-enhanced SQL translation rule by creating a configuration YAML file and uploading it to Cloud Storage.
Requirements
The translation rule YAML file must have a suffix of .ai_config.yaml
.
For example, rules_1.ai_config.yaml
Limitations
- You can only apply one translation rule YAML file in the interactive SQL translator.
- Each translation rule YAML file supports up to 10 rules, and is subject to configuration file size limitations.
Supported fields
The following example shows you how you can create a Gemini-enhanced translation rule YAML file using a natural language prompt.
rewrite_target: TARGET
instruction: NL_PROMPT
Replace the following:
TARGET
: Specify if you want to apply the translation rule to your input SQL,SOURCE_SQL
, or the output SQL,TARGET_SQL
(default).NL_PROMPT
: In natural language, describe a change to the target SQL. The Gemini-enhanced SQL translation identifies the request and makes the specified change
You can also create a translation rule YAML file with multiple translation
rules (up to 10). Each rule can consist of a natural language prompt
(instruction
) that describes a rule, and one or more examples to replace
one SQL pattern (input
) with another (output
). A translation rule can
either use the instruction
field, one or more examples
fields, or use
both types of fields to describe your rule.
rewrite_target: TARGET
instruction: NL_PROMPT
translation_rules:
- instruction: NL_RULE_1
examples:
- input: RULE_1_INPUT_1
output: RULE_1_OUTPUT_1
- input: RULE_1_INPUT_2
output: RULE_1_OUTPUT_2
- instruction: NL_RULE_2
examples:
- input: RULE_2_INPUT_1
output: RULE_2_OUTPUT_1
…
…
Replace the following:
NL_RULE_1
(optional): In natural language, describe a translation rule.RULE_1_INPUT_1
(optional): A SQL pattern that you want to be replaced.RULE_1_OUTPUT_1
(optional): The expected SQL pattern afterinput
has been replaced.You can add more
translation_rules
(up to 10) and moreexamples
as necessary.
For examples of Gemini-enhanced SQL translation rule YAML files, see Translation rule YAML file examples.
Apply a translation rule YAML file to your next translation
To apply a translation rule YAML file:
- In the query editor, click More > Translation settings.
- In the Translation Configuration Source Location field, specify the path to the translation rule YAML file stored in a Cloud Storage folder.
- Click Save.
Translation rule YAML file examples
The following samples are examples of Gemini-enhanced SQL translation rule YAML files.
Example 1: Specify a function to be replaced with another.
translation_rules:
- instruction: "Remove upper() function"
examples:
- input: "upper(X)"
output: "X"
Example 2: Specify multiple translation rules in a single YAML file.
translation_rules:
- instruction: "Remove upper() function"
examples:
- input: "upper(X)"
output: "X"
- instruction: "Insert a comment at the head that explains each statement in detail."
Translate with a batch translation configuration ID
You can run an interactive query with the same translation configurations as a batch translation job by providing a batch translation configuration ID.
- In the query editor, click More > Translation settings.
In the Translation Configuration ID field, provide a batch translation configuration ID to apply the same translation configuration from a completed BigQuery batch migration job.
To find a job's batch translation configuration ID, select a batch translation job from the SQL translation page, and then click the Translation Configuration tab. The batch translation configuration ID is listed as Resource name.
Click Save.
Translate with additional configurations
You can run an interactive query with additional translation configurations by specifying configuration YAML files stored on a Cloud Storage folder. Translation configurations might include SQL object metadata or object mapping information from the source database that can improve translation quality. For example, include DDL information or schemas from the source database to improve interactive SQL translation quality.
To specify translation configurations by providing a location to the translation configuration source files, do the following:
- In the query editor, click More > Translation settings.
In the Translation Configuration Source Location field, specify the path to the translation configuration files stored in a Cloud Storage folder.
The BigQuery interactive SQL translator supports configuration files containing translation metadata, and object name mapping. For information on how to upload files to Cloud Storage, see Upload objects from a filesystem.
Click Save.
Configuration file size limitations
When you use a translation configuration file with the BigQuery interactive SQL translator, the compressed metadata file or YAML config file must be smaller than 50 MB. If the file size exceeds 50 MB, the interactive translator skips that configuration file during translation and produces an error message similar to the following:
CONFIG ERROR: Skip reading file "gs://metadata-file.zip". File size (150,000,000 bytes)
exceeds limit (50 MB).
One method to reduce metadata file size is to use the --database
or --schema
flags to only extract metadata for databases or schemas that are relevant for
the translation input queries. For more information about using these flags when
you generate metadata files, see Global flags.
Troubleshoot translation errors
The following are commonly encountered errors when using the interactive SQL translator.
RelationNotFound
or AttributeNotFound
translation issues
To ensure the most accurate translation,
you can input the data definition language (DDL) statements for any tables used
in a query prior to the query itself. For example, if you want
to translate the Amazon Redshift query select table1.field1, table2.field1
from table1, table2 where table1.id = table2.id;
, you would input the
following SQL statements into the interactive SQL translator:
create table schema1.table1 (id int, field1 int, field2 varchar(16));
create table schema1.table2 (id int, field1 varchar(30), field2 date);
select table1.field1, table2.field1
from table1, table2
where table1.id = table2.id;
Pricing
There is no charge to use the interactive SQL translator. However, storage used to store input and output files incurs the normal fees. For more information, see Storage pricing.
What's next
Learn more about the following steps in data warehouse migration:
- Migration overview
- Migration assessment
- Schema and data transfer overview
- Batch SQL translation
- Data pipelines
- Data security and governance
- Data validation tool