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.

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 use the interactive SQL translator, you need the following permission:

• bigquerymigration.translation.translate

The following predefined IAM role provides the permission that you need to use the interactive SQL translator:

• roles/bigquerymigration.translationUser

To run a query from the interactive SQL translator, you need the same permissions as you would need to run any query in BigQuery. For example, to query a table, you must have permission to read the table's data. For more information, see Querying BigQuery data.

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
• 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:

• us (US multi-region)
• eu (EU multi-region)
• southamerica-east1 (São Paulo)
• us-central1 (Iowa)
• asia-northeast1 (Tokyo)
• asia-south1 (Mumbai)
• asia-southeast1 (Singapore)
• australia-southeast1 (Sydney)
• europe-central2 (Warsaw)
• europe-north1 (Finland)
• europe-west1 (Belgium)
• europe-west2 (London)
• europe-west3 (Frankfurt)

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.

Create a translation rule

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: To get support or 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 the following:

1. After running an interactive translation, click Customize.

   

2. 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) with date_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.

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 a configuration YAML file as specified in the Translation Configuration Source Location field in the translation settings. For more information about configuration YAML files, see Translate a query with additional configurations.. A configuration YAML file can support up to 10 translation rules.

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

Improve interactive translation job performance

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: To provide feedback or ask questions that are related to this preview feature, contact [e-mail].

To improve stability and prevent network timeout issues on long-running interactive translation jobs (for example, using a large metadata file package), enable the consolidated translation API. The consolidated translation API combines the interactive and batch translator into a single workflow, improving the efficiency and stability of your interactive translation jobs.

Required roles

To get the permissions that you need to use the consolidated translation API with 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.

This predefined role contains the permissions required to use the consolidated translation API with 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 consolidated translation API with 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.

Enable the consolidated translation API

To enable the consolidated translation API for the interactive translator, complete the Consolidated Translation API Allowlisting form. For more information about using the consolidated translation API with the batch SQL translator, see the API instructions in Submit a translation job.

Translate a query with additional configurations

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.

You can run an interactive query with additional translation configurations by providing a batch translation configuration ID or with configuration 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 batch translation configuration ID, do the following:

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.

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;

In addition to adding DDLs, you can also resolve RelationNotFound or AttributeNotFound errors by using the metadata extractor to generate metadata packages to make sure all object definitions are present. You can then add the metadata file to the Translation Configuration Source location as detailed in Translate a query with additional configurations. Adding metadata can resolve most translation errors as well as fix other errors indirectly caused from a lack of metadata.

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