Migrate code with the batch SQL translator

This document describes how to use the batch SQL translator inBigQuery to translate scripts written in other SQL dialects intoGoogleSQLqueries. This document is intended for users who are familiar with theGoogle Cloud console.

Before you begin

Before you submit a translation job, complete the following steps:

1. Ensure that you have all the required permissions.
2. Enable the BigQuery Migration API.
3. Collect the source files containing the SQL scripts and queries to betranslated.
4. Optional. Create a metadata file to improve the accuracy of the translation.
5. Optional. Decide if you need to map SQL object names in the source filesto new names in BigQuery. Determine what name mapping rules touse if this is necessary.
6. Decide what method to use to submit the translation job.
7. Upload the source files to Cloud Storage.

Required permissions

You must have the following permissions on the project to enable theBigQuery Migration Service:

• resourcemanager.projects.get
• serviceusage.services.enable
• serviceusage.services.get

You need the following permissions on the project to access and use theBigQuery Migration Service:

• bigquerymigration.workflows.create
• bigquerymigration.workflows.get
• bigquerymigration.workflows.list
• bigquerymigration.workflows.delete
• bigquerymigration.subtasks.get

• bigquerymigration.subtasks.list

  Alternatively, you can use the following roles to get the same permissions:

  • bigquerymigration.viewer - Read only access.
  • bigquerymigration.editor - Read/write access.

To access the Cloud Storage buckets for input and output files:

• storage.objects.get on the source Cloud Storage bucket.
• storage.objects.list on the source Cloud Storage bucket.
• storage.objects.create on the destination Cloud Storage bucket.

You can have all the above necessary Cloud Storage permissions fromthe following roles:

• roles/storage.objectAdmin
• roles/storage.admin

Enable the BigQuery Migration API

If your Google Cloud CLI project was created before February 15, 2022, enablethe BigQuery Migration API as follows:

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

   Go to BigQuery Migration API

2. ClickEnable.

To generate metadata information by using thedwh-migration-dumper tool, seeGenerate metadata for translation.

To provide your own metadata, collect the data definition language (DDL)statements for the SQL objects in your source system into separate text files.

Decide how to submit the translation job

You have three options for submitting a batch translation job:

• Batch translation client: Configure a job by changing settings ina configuration file, and submit the job using the command line. Thisapproach doesn't require you to manually upload source files toCloud Storage. The client still uses Cloud Storageto store files during translation job processing.

  The legacy batch translation client is an open-source Pythonclient that lets you translate source files located on your localmachine and have the translated files output to a local directory.You configure the client for basic use by changing a few settings inits configuration file. If you choose to, you can also configure the client toaddress more complex tasks like macro replacement, and pre- andpostprocessing of translation inputs and outputs. For more information,see the batch translation clientreadme.

• Google Cloud console: Configure and submit a job using a userinterface. This approach requires you to upload source files toCloud Storage.

Create configuration YAML files

You can optionally create and use configurationconfiguration YAML filesto customize your batch translations. These files can be used to transform yourtranslation output in various ways. For example,you cancreate a configuration YAML file to change the case of a SQL objectduring translation.

If you want to use the Google Cloud console or the BigQuery Migration API for abatch translation job, you canupload the configuration YAML file to theCloud Storage bucket containing the source files.

If you want to use the batch translation client, you can place the configuration YAMLfile in the local translation input folder.

Upload input files to Cloud Storage

If you want to use the Google Cloud console or the BigQuery Migration APIto perform a translation job, you must upload the source files containingthe queries and scripts you want to translate to Cloud Storage. Youcan also uploadany metadata files orconfiguration YAML filesto the same Cloud Storage bucket and directory containing the source files.For more information about creating buckets and uploading files toCloud Storage, seeCreate bucketsandUpload objects from a filesystem.

Supported SQL dialects

The batch SQL translator is part of the BigQuery Migration Service. Thebatch SQL translator can translate thefollowing 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 inpreview:

• Apache Impala SQL
• Apache Spark SQL
• Azure Synapse T-SQL
• GoogleSQL (BigQuery)
• 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

Handling unsupported SQL functions with helper UDFs

When translating SQL from a source dialect to BigQuery, some functions might not have a direct equivalent. To address this, the BigQuery Migration Service (and the broader BigQuery community) provide helper user-defined functions (UDFs) that replicate the behavior of these unsupported source dialect functions.

These UDFs are often found in thebqutil public dataset, allowing translated queries to initially reference them using the formatbqutil.<dataset>.<function>(). For example,bqutil.fn.cw_count().

Important considerations for production environments:

Whilebqutil offers convenient access to these helper UDFs for initial translation and testing, direct reliance onbqutil for production workloads is not recommended for several reasons:

1. Version control: Thebqutil project hosts the latest version of these UDFs, which means their definitions can change over time. Relying directly onbqutil could lead to unexpected behavior or breaking changes in your production queries if a UDF's logic is updated.
2. Dependency isolation: Deploying UDFs to your own project isolates your production environment from external changes.
3. Customization: You might need to modify or optimize these UDFs to better suit your specific business logic or performance requirements. This is only possible if they are within your own project.
4. Security and governance: Your organization's security policies might restrict direct access to public datasets likebqutil for production data processing. Copying UDFs to your controlled environment aligns with such policies.

Deploying helper UDFs to your project:

For reliable and stable production use, you should deploy these helper UDFs into your own project and dataset. This gives you full control over their version, customization, and access.For detailed instructions on how to deploy these UDFs, refer to theUDFs deployment guide on GitHub. This guide provides the necessary scripts and steps to copy the UDFs into your environment.

Locations

The batch SQL translator is available in the followingprocessing locations:

	Region description	Region name	Details
Asia Pacific
	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

Submit a translation job

Follow these steps to start a translation job, view its progress, and see theresults.

Explore the translation output

After running the translation job, you can see information about the jobin the Google Cloud console. If you used the Google Cloud console to run the job, you can see job results in the destinationCloud Storage bucket that you specified. If you used the batchtranslation client to run the job, you can see job results in the outputdirectory that you specified. The batch SQL translatoroutputs the following files to the specified destination:

• The translated files.
• The translation summary report in CSV format.
• The consumed output name mapping in JSON format.
• The AI suggestion files.

Google Cloud console output

To see translation job details, follow these steps:

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

   Go to BigQuery

2. In the navigation menu, clickSQL translation.

3. In the list of translation jobs, locate the job for which you want to see thetranslation details. Then, click the translation job name.You can see a Sankey visualization that illustrates the overall quality ofthe job, the number of input lines of code (excluding blank lines and comments),and a list of issues that occurred during the translation process.You should prioritize fixes from left to right. Issues in an early stage cancause additional issues in subsequent stages.

4. Hold the pointer over the error or warning bars, and review the suggestionsto determine next steps to debug the translation job.

5. Select theLog Summary tab to see a summary of the translation issues,including issue categories, suggested actions, and how often each issueoccurred. You can click the Sankey visualization bars to filter issues. Youcan also select an issue category to see log messagesassociated with that issue category.

6. Select theLog Messages tab to see more details about each translationissue, including the issue category, the specific issue message, and a linkto the file in which the issue occurred. You can click the Sankeyvisualization bars to filter issues. You can select an issue in theLogMessage tab to open theCode tabthat displays the input and output file if applicable.

7. Click theJob details tab to see the translation jobconfiguration details.

Summary report

The summary report is a CSV file that contains a table of all of thewarning and error messages encountered during the translation job.

To see the summary file in the Google Cloud console, follow these steps:

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

   Go to BigQuery

2. In the navigation menu, clickSQL translation.

3. In the list of translation jobs, locate the job that you are interested in,then click the job name or clickMore options> Show details.

4. In theJob details tab, in theTranslation report section, clicktranslation_report.csv.

5. On theObject details page, click the value in theAuthenticated URLrow to see the file in your browser.

The following table describes the summary file columns:

Code tab

The code tab lets you review further information about the inputand output files for a particular translation job. In the code tab, you canexamine the files used in a translation job, review aside-by-side comparison of an input file and its translation for anyinaccuracies, and view log summaries and messages for a specific file in a job.

To access the code tab, follow these steps:

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

   Go to BigQuery

2. In the navigation menu, clickSQL translation.

3. In the list of translation jobs, locate the job that you are interested in,then click the job name or clickMore options> Show details.

4. SelectCode tab. The code tab consists of the following panels:

   

   • File explorer: Contains all SQL files used for translation. Click a file toview its translation input and output, and any translation issues from itstranslation.
   • Gemini-enhanced input: The input SQL that was translated by thetranslation engine. If you have specified Geminicustomization rules for the source SQLin the Gemini configuration,then the translator transforms the original input firstand then translates the Gemini-enhanced input. To view theoriginal input, clickView original input.
   • Translation output: The translation result. If you have specifiedGemini customization rules for the target SQL inthe Gemini configuration,then the transformation is applied to the translated result as aGemini-enhanced output. If a Gemini-enhancedoutput is available, then you can click theGemini suggestion buttonto review the Gemini-enhanced output.

5. Optional: To view an input file and its output file in theBigQuery interactive SQL translator, clickEdit.You can edit the files and save the output file back to Cloud Storage.

Configuration tab

You can add, rename, view, or edit your configuration YAML files in theConfiguration tab.TheSchema Explorer showsthe documentation for supported configuration types to help you write yourconfiguration YAML files. After you edit the configuration YAML files,you can rerun the job to use the new configuration.

To access the configuration tab, follow these steps:

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

   Go to BigQuery

2. In the navigation menu, clickSQL translation.

3. In the list of translation jobs, locate the job that you are interested in,then click the job name or clickMore options> Show details.

4. In theTranslation details window, click theConfiguration tab.

To add a new configuration file:

1. Clickmore_vertMore options>Create configuration YAML file.
2. A panel appears where you can choose the type, location, and name of the new configuration YAML file.
3. ClickCreate.

To edit an existing configuration file:

1. Click on the configuration YAML file.
2. Edit the file, then clickSave.
3. ClickRe-run to run a new translation job that uses the edited configuration YAML files.

You can rename an existing configuration file by clickingmore_vertMore options>Rename.

Consumed output name mapping file

This JSON file contains the output name mapping rules that were used by thetranslation job. The rules in this file might differ from theoutput name mapping rulesthat you specified for the translation job, due to either conflicts in thename mapping rules, or lack of name mapping rules for SQL objectsthat were identified during translation. Review this file todetermine whether the name mapping rulesneed correction. If they do, create new output name mapping rules that addressany issues you identify, and run a new translation job.

Translated files

For each source file, a corresponding output file is generated in thedestination path. The output file contains the translated query.

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

   Go to BigQuery

2. In the navigation menu, clickSQL translation.

3. In the list of translation jobs, locate the job that you are interested in,and then clickmore_vertMore Options> Open Interactive Translation.

   The BigQuery interactive SQL translator now openswith the corresponding batch translation configuration ID. To view thetranslation configuration ID for the interactive translation, clickMore> Translation settings in theinteractive SQL translator.

To debug a batch translation file in the interactive SQL translator,follow these steps:

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

   Go to BigQuery

2. In the navigation menu, clickSQL translation.

3. In the list of translation jobs, locate the job that you are interested in,and then click the job name or clickMore options> Show details.

4. In theTranslation details window, click theCode tab.

5. In the file explorer, click your filename to open the file.

6. Next to the output filename, clickEdit to open the files in theinteractive SQL translator (Preview).

   You see the input and output files populated in theinteractive SQL translator that now uses the corresponding batchtranslation configuration ID.

7. To save the edited output file back to Cloud Storage, in theinteractive SQL translator clickSave> Save To GCS.

Limitations

The translator can't translate user-defined functions (UDFs) from languagesother than SQL, because it can't parse them to determinetheir input and output data types. This causes translation of SQL statementsthat reference these UDFs to be inaccurate. To make sure non-SQL UDFs areproperly referenced during translation, use valid SQL to create placeholderUDFs with the same signatures.

For example, say you have a UDF written in C that calculates the sum of twointegers. To make sure that SQL statements that reference this UDF are correctlytranslated, create a placeholder SQL UDF that shares the same signature as theC UDF, as shown in the following example:

CREATEFUNCTIONTest.MySum(aINT,bINT)RETURNSINTLANGUAGESQLRETURNa+b;

Save this placeholder UDF in a text file, and include that file as one of thesource files for the translation job. This enables the translator to learn theUDF definition and identify the expected input and output data types.

Quota and limits

• BigQuery Migration API quotas apply.
• Each project can have at most 10 active translation tasks.
• While there is no hard limit on the total number of source and metadatafiles, we recommend keeping the number of files to under 1000 for betterperformance.

Troubleshoot translation errors

RelationNotFound orAttributeNotFound translation issues

Translation works best with metadata DDLs. When SQL object definitions cannot befound, the translation engine raisesRelationNotFound orAttributeNotFoundissues. We recommend using the metadata extractor to generate metadata packagesto make sure all object definitions are present. Adding metadata is therecommended first step to resolve most translation errors, as it often can fixmany other errors that are indirectly caused from a lack of metadata.

For more information, seeGenerate metadata for translation and assessment.

Pricing

There is no charge to use the batch SQL translator. However,storage used to store input and output files incurs the normal fees. For moreinformation, seeStorage pricing.

What's next

Learn more about the following steps in data warehouse migration:

• Migration overview
• Migration assessment
• Schema and data transfer overview
• Data pipelines
• Interactive SQL translation
• Data security and governance
• Data validation tool