Enable SQL translations for BigQuery migrations
As you migrate from your data warehouses to BigQuery, use the BigQuery SQL translators to translate SQL scripts and queries from the source dialect into GoogleSQL.
BigQuery offers the following SQL translation as part of the BigQuery Migration Service:
Before you translate your source SQL, we recommend that you use the
dwh-migration-dumper tool to extract DDL schema metadata from your source data
warehouses. The extracted metadata can be
used with the SQL translators to improve SQL translation accuracy.
This document shows you the required steps to take before you can use any of the SQL translators. This document also shows you the SQL dialects supported by the SQL translators, and the regions where SQL translation is supported.
Enable SQL translations
Before you translate your SQL using the SQL translators, do the following steps.
Enable the BigQuery Migration API
If your Google Cloud CLI project was created before February 15, 2022, enable the BigQuery Migration API by doing the following:
In the Google Cloud console, go to the BigQuery Migration API page.
Click Enable.
Add the required permissions
To get the permissions that
you need to create translation jobs with the interactor translator, the translation API, or the batch SQL translator,
ask your administrator to grant you the
following IAM roles on the parent resource:
-
Viewing and monitoring migration jobs:
MigrationWorkflow Viewer (
roles/bigquerymigration.viewer) -
Submitting migration jobs:
MigrationWorkflow Editor (
roles/bigquerymigration.editor) -
Access the Cloud Storage buckets for input and files:
Storage Object Admin (
roles/storage.objectAdmin) - on the source and destination Cloud Storage bucket.
For more information about granting roles, see Manage access to projects, folders, and organizations.
These predefined roles contain the permissions required to create translation jobs with the interactor translator, the translation API, or the batch SQL translator. To see the exact permissions that are required, expand the Required permissions section:
Required permissions
The following permissions are required to create translation jobs with the interactor translator, the translation API, or the batch SQL translator:
-
bigquerymigration.workflows.create -
bigquerymigration.workflows.get -
bigquerymigration.workflows.list -
bigquerymigration.workflows.delete -
bigquerymigration.subtasks.get -
bigquerymigration.subtasks.list -
storage.objects.get -
storage.objects.list -
storage.objects.create
You might also be able to get these permissions with custom roles or other predefined roles.
Supported SQL dialects
The BigQuery SQL translators supports the translations of the following SQL dialects into GoogleSQL:
- Amazon Redshift SQL (
Redshift2BigQuery_Translation) - Apache HiveQL and Beeline CLI (
HiveQL2BigQuery_Translation) - IBM Netezza SQL and NZPLSQL (
Netezza2BigQuery_Translation) - Snowflake SQL (
Snowflake2BigQuery_Translation) - Teradata and Teradata Vantage (
Teradata2BigQuery_Translation)- SQL
- Basic Teradata Query (BTEQ)
- Teradata Parallel Transport (TPT)
Additionally, translation of the following SQL dialects is supported in preview:
- Apache Impala SQL (
Impala2BigQuery_Translation) - Apache Spark SQL (
SparkSQL2BigQuery_Translation) - Azure Synapse T-SQL (
AzureSynapse2BigQuery_Translation) - GoogleSQL, BigQuery (
Bigquery2Bigquery_Translation) - Greenplum SQL (
Greenplum2BigQuery_Translation) - IBM DB2 SQL (
Db22BigQuery_Translation) - MySQL SQL (
MySQL2BigQuery_Translation) - Oracle SQL, PL/SQL, Exadata (
Oracle2BigQuery_Translation) - PostgreSQL SQL (
Postgresql2BigQuery_Translation) - Trino or PrestoSQL (
Presto2BigQuery_Translation) - SQL Server T-SQL (
SQLServer2BigQuery_Translation) - SQLite (
SQLite2BigQuery_Translation) - Vertica SQL (
Vertica2BigQuery_Translation)
Locations
The BigQuery SQL translators are available in the following processing locations:
| Region description | Region name | Details | |
|---|---|---|---|
| Asia Pacific | |||
| Bangkok | asia-southeast3 |
||
| Delhi | asia-south2 |
||
| Hong Kong | asia-east2 |
||
| Jakarta | asia-southeast2 |
||
| Melbourne | australia-southeast2 |
||
| Mumbai | asia-south1 |
||
| Osaka | asia-northeast2 |
||
| Seoul | asia-northeast3 |
||
| Singapore | asia-southeast1 |
||
| Sydney | australia-southeast1 |
||
| Taiwan | asia-east1 |
||
| Tokyo | asia-northeast1 |
||
| Europe | |||
| Belgium | europe-west1 |
 Low CO2
|
|
| Berlin | europe-west10 |
||
| EU multi-region | eu |
||
| Finland | europe-north1 |
Low CO2
|
|
| Frankfurt | europe-west3 |
||
| London | europe-west2 |
Low CO2
|
|
| Madrid | europe-southwest1 |
Low CO2
|
|
| Milan | europe-west8 |
||
| Netherlands | europe-west4 |
Low CO2
|
|
| Paris | europe-west9 |
Low CO2
|
|
| Stockholm | europe-north2 |
Low CO2
|
|
| Turin | europe-west12 |
||
| Warsaw | europe-central2 |
||
| Zürich | europe-west6 |
Low CO2
|
|
| Americas | |||
| Columbus, Ohio | us-east5 |
||
| Dallas | us-south1 |
Low CO2
|
|
| Iowa | us-central1 |
Low CO2
|
|
| Las Vegas | us-west4 |
||
| Los Angeles | us-west2 |
||
| Mexico | northamerica-south1 |
||
| Northern Virginia | us-east4 |
||
| Oregon | us-west1 |
Low CO2 |
|
| Québec | northamerica-northeast1 |
Low CO2
|
|
| São Paulo | southamerica-east1 |
Low CO2
|
|
| Salt Lake City | us-west3 |
||
| Santiago | southamerica-west1 |
Low CO2
|
|
| South Carolina | us-east1 |
||
| Toronto | northamerica-northeast2 |
Low CO2
|
|
| US multi-region | us |
||
| Africa | |||
| Johannesburg | africa-south1 |
||
| MiddleEast | |||
| Dammam | me-central2 |
||
| Doha | me-central1 |
||
| Israel | me-west1 |
||