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.

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.

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

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:

    Displays a Teradata SQL query translated into GoogleSQL

  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.

Improve interactive translation job performance

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

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 translation configuration source file, do the following:

  1. In the query editor, click More > Translation settings.
  2. In the Translation Configuration Source Location field, specify the path to a translation configuration file 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.

Limitations

The interactive SQL translator doesn't have any schema information for the queries you input for translation. To ensure the most accurate translation, 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;

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)

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: