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:

1. In the Google Cloud console, go to the BigQuery Migration API page.

   Go to BigQuery Migration API

2. Click Enable.

Note: Projects created after February 15, 2022 have this API enabled automatically.

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	Low CO2
	Madrid	europe-southwest1	Low CO2
	Belgium	europe-west1	Low CO2
	London	europe-west2	Low CO2
	Frankfurt	europe-west3	Low CO2
	Netherlands	europe-west4	Low CO2
	Zürich	europe-west6	Low CO2
	Paris	europe-west9	Low CO2
	Turin	europe-west12
Americas
	Québec	northamerica-northeast1	Low CO2
	São Paulo	southamerica-east1	Low CO2
	US multi-region	us
	Iowa	us-central1	Low CO2
	South Carolina	us-east1
	Northern Virginia	us-east4
	Columbus, Ohio	us-east5
	Dallas	us-south1	Low CO2
	Oregon	us-west1	Low CO2
	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:

1. In the Google Cloud console, go to the BigQuery page.

   Go to BigQuery

2. In the Editor pane, click More, and then select Translation settings.

3. For Source dialect, select the SQL dialect that you want to translate.

4. 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.

5. Click Save.

6. In the Editor pane, click More, and then select Enable SQL translation.

   The Editor pane splits into two panes.

7. In the left pane, enter the query you want to translate.

8. Click Translate.

   BigQuery translates your query into GoogleSQL and displays it in the right pane. For example, the following screenshot shows translated Teradata SQL:

   

9. Optional: To run the translated GoogleSQL query, click Run.

10. 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

Note: With Gemini-enhanced SQL translations, you can have your scripts customized by the Gemini model. Gemini-enhanced SQL translations are allowed a limited amount of Gemini usage at no charge, which should be sufficient for most migration projects. To request an increase of this limit, or to get support and provide feedback for this Preview feature, contact [e-mail].

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:

1. After running an interactive translation, click Assist, and then click Customize this translation.

   

2. Use one or both of the following prompts to create a translation rule:

   • In the Find and replace a pattern 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) with date_diff(X,Y,MONTH).

   • In the Describe a change to the output field, type 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.

3. Click Preview.

4. In the Preview suggested changes dialog, review the changes made by the Gemini-enhanced SQL translation to the translation output based on your rule.

5. 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 is subject to configuration file size limitations.

6. To apply the suggested changes to the translation output, click Apply.

YAML

To create a Gemini-enhanced SQL translation rule, you can create a Gemini-based configuration YAML file and upload it to Cloud Storage. For more information, see Create a Gemini-based configuration YAML file and Apply a Gemini-based configuration YAML file to your next translation.

Apply a Gemini-based configuration YAML file to your next translation

To apply a configuration YAML file for AI outputs:

1. In the Google Cloud console, go to the BigQuery page.

   Go to BigQuery

2. In the query editor, click More > Translation settings.

3. In the Translation Configuration Source Location field, specify the path to the Gemini-based YAML file stored in a Cloud Storage folder.

4. Click Save. After running an interactive translation, a red dot appears on the Assist button to indicate that a Gemini suggestion is available.

5. Click Assist, and then click View suggestions to view suggested changes to the translation output by the Gemini-enhanced SQL translation service.

   

6. In the Preview suggested changes dialog, review the changes made by the Gemini-enhanced SQL translation to the translation output based on your rule.

7. To apply the suggested changes to the translation output, click Apply.

Explain a translation

Preview

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

Note: With Gemini-enhanced SQL translations, you can generate text explanation for your scripts by the Gemini model. Gemini-enhanced SQL translations are allowed a limited amount of Gemini usage at no charge. This usage is sufficient for most migration projects. To request an increase to this limit, or to get support and provide feedback for this Preview feature, contact [e-mail].

After running an interactive translation, you can request a Gemini-generated text explanation. The generated text includes a summary of the translated SQL query. Gemini also identifies translation differences and inconsistencies between the source SQL query and the translated GoogleSQL query.

To get Gemini-generate SQL translation explanation, do the following:

1. To create a Gemini-generated SQL translation explanation, click Assist, and then click Explain this translation.

   

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.

1. In the query editor, click More > Translation settings.

2. 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.

3. 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:

1. In the query editor, click More > Translation settings.

2. 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.

3. 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