# Migrate code with the batch SQL translator The batch SQL translator in BigQuery lets you translate scripts written in other SQL dialects into GoogleSQL queries in bulk. You can submit and review the results of a batch translation job from the Google Cloud console, from the command line, or with the [BigQuery Migration Service MCP server](https://docs.cloud.google.com/bigquery/docs/use-bigquery-migration-mcp) to accelerate large-scale workload migrations. To build a custom software integration, an automated CI/CD pipeline, or another programmatic workflow, [call the BigQuery Migration API](https://docs.cloud.google.com/bigquery/docs/api-sql-translator) directly. For information about supported dialects and processing locations, see [Supported SQL dialects](https://docs.cloud.google.com/bigquery/docs/enable-sql-translations#supported_sql_dialects) and [Locations](https://docs.cloud.google.com/bigquery/docs/enable-sql-translations.md.txt#locations). ## Before you begin Before you submit a translation job, do the following steps. ### Enable SQL translations Enable the required API, and get the permissions needed to use a BigQuery SQL translator. For more information, see [Enable SQL translations](https://docs.cloud.google.com/bigquery/docs/enable-sql-translations#enable_sql_translations). ### Required permissions To get the permissions that you need to create translation jobs with the interactive 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](https://docs.cloud.google.com/iam/docs/roles-permissions/bigquerymigration.md.txt#bigquerymigration.viewer) (`roles/bigquerymigration.viewer`) - Submitting migration jobs: [MigrationWorkflow Editor](https://docs.cloud.google.com/iam/docs/roles-permissions/bigquerymigration.md.txt#bigquerymigration.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](https://docs.cloud.google.com/iam/docs/granting-changing-revoking-access.md.txt). These predefined roles contain the permissions required to create translation jobs with the interactive 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 interactive 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](https://docs.cloud.google.com/iam/docs/creating-custom-roles.md.txt) or other [predefined roles](https://docs.cloud.google.com/iam/docs/roles-overview.md.txt#predefined). To restrict specific translation workflow configurations in your organization, such as disallowing Gemini AI suggestions or restricting translation output types, configure [Custom Org Policies for BigQuery Migration Service](https://docs.cloud.google.com/bigquery/docs/migration-custom-org-policies.md.txt#example_constraints). ### Collect source files Source files must be text files that contain valid SQL for the source dialect. Source files can also include comments. Do your best to ensure the SQL is valid, using whatever methods are available to you. ### Create metadata files To help the service generate more accurate translation results, we recommend that you provide metadata files. However, this isn't mandatory. You can use the `dwh-migration-dumper` command-line extraction tool to generate the metadata information. After you prepare the metadata files, you can include them along with the source files in the translation source folder. The translator automatically detects them and leverages them to translate source files, you don't need to configure any extra settings to enable this. To generate metadata information by using the `dwh-migration-dumper` tool, see [Generate metadata for translation](https://docs.cloud.google.com/bigquery/docs/generate-metadata.md.txt). ### Create configuration YAML files You can optionally create and use configuration [configuration YAML files](https://docs.cloud.google.com/bigquery/docs/config-yaml-translation.md.txt) to customize your batch translations. These files can be used to transform your translation output in various ways. For example, you can [create a configuration YAML file to change the case of a SQL object](https://docs.cloud.google.com/bigquery/docs/config-yaml-translation.md.txt#change_object-name_case) during translation. Use one of the following options to include a configuration YAML file in your translation job. ### Console [Upload the configuration YAML file to the Cloud Storage directory that contains your source files](https://docs.cloud.google.com/bigquery/docs/batch-sql-translator#upload-files). When you select that directory as an input location, the translation job includes the configuration YAML file automatically. ### gcloud > [!WARNING] > > **Preview** > > > This product or feature is > > subject to the "Pre-GA Offerings Terms" in the General Service Terms section > of the [Service Specific > Terms](https://docs.cloud.google.com/terms/service-terms#1). > > Pre-GA products and features are available "as is" and might have limited support. > > For more information, see the > [launch stage descriptions](https://cloud.google.com/products/#product-launch-stages).
The flags that you use with the [`gcloud alpha bq migration translate-batch` command](https://docs.cloud.google.com/sdk/gcloud/reference/alpha/bq/migration/translate-batch.md.txt) depend on where the configuration YAML file is stored: - If the configuration YAML file is in the same directory as your source files, then you don't need any additional flags. When you set that directory in the `--source-gcs-uris` or `--source-local-dirs` flag, the job includes the configuration YAML file automatically. - If the configuration YAML file is stored separately on your local machine, then use the `--source-local-files` flag to upload it and add it to the job. - If the configuration YAML file is stored separately in Cloud Storage, then use the `--source-gcs-files` flag to add it to the job. For example, the following command uploads your source files and a separately stored configuration YAML file from your local machine, and then runs the translation job: ``` gcloud alpha bq migration translate-batch \ --source-dialect=SOURCE_DIALECT \ --target-dialect=TARGET_DIALECT \ --location=LOCATION \ --source-local-dirs=LOCAL_DIR=SOURCE_URI \ --source-local-files=LOCAL_CONFIG_YAML=CONFIG_YAML_URI \ --target-gcs-path=TARGET_URI ``` Replace the following: - `LOCAL_CONFIG_YAML`: the local path to the configuration YAML file, such as `./configs/change-case.config.yaml`. - `CONFIG_YAML_URI`: the Cloud Storage URI that the command uploads the configuration YAML file to, such as `gs://my_data_bucket/configs/change-case.config.yaml`. This URI must be outside the `SOURCE_URI` directory. For descriptions of the other placeholders, see [Submit a translation job](https://docs.cloud.google.com/bigquery/docs/batch-sql-translator.md.txt#submit_a_translation_job). ### MCP [Upload the configuration YAML file to the Cloud Storage directory that contains your source files](https://docs.cloud.google.com/bigquery/docs/batch-sql-translator#upload-files). When you translate that directory with the [`translate_batch_queries` tool](https://docs.cloud.google.com/bigquery/docs/reference/migration/mcp/tools_list/translate_batch_queries.md.txt), the translation job includes the configuration YAML file automatically. If the configuration YAML file is stored in a separate Cloud Storage location outside your source directory, specify its Cloud Storage URI in your prompt. For more information, see [Submit a translation job](https://docs.cloud.google.com/bigquery/docs/batch-sql-translator.md.txt#submit_a_translation_job). ### Upload input files to Cloud Storage Upload the source files containing the queries and scripts that you want to translate to Cloud Storage. You can also upload [any metadata files](https://docs.cloud.google.com/bigquery/docs/generate-metadata.md.txt) or [configuration YAML files](https://docs.cloud.google.com/bigquery/docs/config-yaml-translation.md.txt) to the same Cloud Storage bucket and directory containing the source files. For more information about creating buckets and uploading files to Cloud Storage, see [Create buckets](https://docs.cloud.google.com/storage/docs/creating-buckets.md.txt) and [Upload objects from a filesystem](https://docs.cloud.google.com/storage/docs/uploading-objects.md.txt). ### Choose how to submit the translation job You have three options for submitting a batch translation job: - **Google Cloud console**: Configure and submit a job using a user interface. This approach requires you to upload source files to Cloud Storage. - **Google Cloud CLI** : Submit a job from the command line using the gcloud CLI. The `translate-batch` command accepts your source and target locations as flags, and can upload local directories and files to Cloud Storage for you. For more information, see [Submit a translation job](https://docs.cloud.google.com/bigquery/docs/batch-sql-translator.md.txt#submit_a_translation_job). - **BigQuery Migration Service MCP server** : Submit a job from an AI application or agent connected to the [BigQuery Migration Service MCP server](https://docs.cloud.google.com/bigquery/docs/use-bigquery-migration-mcp). This approach requires your source files to be in Cloud Storage. For more information, see [Submit a translation job](https://docs.cloud.google.com/bigquery/docs/batch-sql-translator.md.txt#submit_a_translation_job). All options require your source files to be accessible in Cloud Storage, and all create the same kind of translation job. A job that you submit from the command line or from an MCP client still appears in the translation jobs list in the Google Cloud console. ## Submit a translation job Use one of the following options to start a translation job and view its progress. To review the results afterwards, see [Explore the translation output](https://docs.cloud.google.com/bigquery/docs/batch-sql-translator#explore_the_translation_output). ### Console These steps assume that you uploaded source files to a Cloud Storage bucket. To use the Google Cloud console to submit a batch translation job, do the following steps: 1. In the Google Cloud console, go to the **SQL Translation** page. [Go to SQL Translation](https://console.cloud.google.com/bigquery/migrations) 2. In the **SQL translation** panel, click **Start translation**. 3. For **Translation configuration**, enter the following: 1. For **Display name**, enter a name for the translation job. The name can contain letters, numbers or underscores. 2. 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. The translation job performs best when you choose the same location as your source file bucket. 3. For **Source dialect**, select the SQL dialect that you want to translate. 4. For **Target dialect** , select **GoogleSQL**. 4. Click **Next**. 5. For **File location details** , specify the Cloud Storage paths to use for translation input and output. You can enter the paths in the format `bucket_name/folder_name/` or use the **Browse** option to navigate to a folder. 1. For **Output directory location**, specify a path to the destination Cloud Storage folder for the translated files. This serves as a root directory for all translation output. 2. Choose one or more **Input directory locations** containing the path to the SQL files to translate. 3. Each input directory can optionally be given an **Output subdirectory name** underneath the root output directory if necessary. 6. Click **Next**. 7. Select any optional settings that you need to customize metadata and any additional translation outputs. 8. Optional: To further customize translation behavior, create configuration YAML files and place these files in the input Cloud Storage bucket. These files can be used to rename objects, enable optimizations, enhance translations with Gemini and more. For more information about configuration YAML files, see [Create a configuration YAML file](https://docs.cloud.google.com/bigquery/docs/config-yaml-translation.md.txt). 9. Click **Create** to start the translation job. After you create the translation job, you can see its status in the translation jobs list. ### gcloud > [!WARNING] > > **Preview** > > > This product or feature is > > subject to the "Pre-GA Offerings Terms" in the General Service Terms section > of the [Service Specific > Terms](https://docs.cloud.google.com/terms/service-terms#1). > > Pre-GA products and features are available "as is" and might have limited support. > > For more information, see the > [launch stage descriptions](https://cloud.google.com/products/#product-launch-stages).
To submit a batch translation job, use the [`gcloud alpha bq migration translate-batch` command](https://docs.cloud.google.com/sdk/gcloud/reference/alpha/bq/migration/translate-batch.md.txt). The flags that you use to identify your source files depend on whether the files are in Cloud Storage or on your local machine. #### Translate SQL files in Cloud Storage To translate SQL files that you already [uploaded to Cloud Storage](https://docs.cloud.google.com/bigquery/docs/batch-sql-translator.md.txt#upload-files), identify the source directories with the `--source-gcs-uris` flag. If you want to include files that are not in `--source-gcs-uris`, you can use the `--source-gcs-files` flag: ``` gcloud alpha bq migration translate-batch \ --source-dialect=SOURCE_DIALECT \ --target-dialect=TARGET_DIALECT \ --location=LOCATION \ --source-gcs-uris=SOURCE_URI \ --target-gcs-path=TARGET_URI ``` Replace the following: - `SOURCE_DIALECT`: the dialect of the source SQL files, such as `teradata`. For the supported values, see [Supported SQL dialects](https://docs.cloud.google.com/bigquery/docs/enable-sql-translations.md.txt#supported_sql_dialects). - `TARGET_DIALECT`: the dialect to translate the source files into. For example, `bigquery`. - `LOCATION`: the [location](https://docs.cloud.google.com/bigquery/docs/enable-sql-translations.md.txt#locations) that processes the job, such as `us`. - `SOURCE_URI`: the Cloud Storage directory that contains the source files, such as `gs://my_data_bucket/input/`. - `TARGET_URI`: the Cloud Storage directory that receives the translated files, such as `gs://my_data_bucket/output/`. #### Translate SQL files on your local machine To translate files that are on your local machine, map each local directory to a Cloud Storage URI with the `--source-local-dirs` flag. The command uploads the directory to that URI and then includes the URI in the translation job, so you don't need to upload the files yourself: ``` gcloud alpha bq migration translate-batch \ --source-dialect=SOURCE_DIALECT \ --target-dialect=TARGET_DIALECT \ --location=LOCATION \ --source-local-dirs=LOCAL_DIR=SOURCE_URI \ --target-gcs-path=TARGET_URI ``` Replace `LOCAL_DIR` with the local directory that contains the source files, such as `./input_queries`. For descriptions of the other placeholders, see [Translate SQL files in Cloud Storage](https://docs.cloud.google.com/bigquery/docs/batch-sql-translator.md.txt#translate-gcs-files). To map individual files instead of directories, use the `--source-local-files` flag. #### Add optional flags To generate Gemini suggestions alongside the translated SQL, add the `--enable-ai-suggestion` flag. By default, the command waits for the translation job to finish. To submit the job and return immediately, add the `--async` flag. The command then prints a translation ID that you can pass to the [`gcloud alpha bq migration translation describe` command](https://docs.cloud.google.com/sdk/gcloud/reference/alpha/bq/migration/translation/describe.md.txt) to check the job status: ``` gcloud alpha bq migration translation describe TRANSLATION_ID \ --location=LOCATION ``` #### Retrieve the output files The translation job writes its results to the Cloud Storage directory that you set in the `--target-gcs-path` flag. This target directory holds the translated files, the translation summary report, and any AI suggestion files. To copy the output to your local machine, use the following command: ``` gcloud storage cp --recursive TARGET_URI LOCAL_DIRECTORY ``` Replace the following: - `TARGET_URI`: your target base URI, such as `gs://my_data_bucket/output/`. - `LOCAL_DIRECTORY`: the local directory that receives the files. Your job also appears in the translation jobs list in the Google Cloud console, even though you submitted it from the command line. To review the quality of a translation output, see [Explore the translation output](https://docs.cloud.google.com/bigquery/docs/batch-sql-translator#explore_the_translation_output). ### MCP To submit a batch translation job from an AI application or agent, connect your MCP client to the BigQuery Migration Service MCP server. For setup instructions and required roles, see [Configure an MCP client to use the BigQuery Migration Service MCP server](https://docs.cloud.google.com/bigquery/docs/use-bigquery-migration-mcp.md.txt#configure-client). [Upload your input SQL files](https://docs.cloud.google.com/bigquery/docs/batch-sql-translator.md.txt#upload-files) and any [metadata ZIP files](https://docs.cloud.google.com/bigquery/docs/generate-metadata.md.txt) or [configuration YAML files](https://docs.cloud.google.com/bigquery/docs/config-yaml-translation.md.txt) to Cloud Storage, and then prompt your MCP client to translate the files: ``` Translate the DIALECT SQL files in SOURCE_URI into GoogleSQL and write the output to TARGET_URI. Use PROJECT_NUMBER and LOCATION. ``` Replace the following: - `DIALECT`: the dialect of the SQL files that you're translating, such as `Teradata`. For the supported values, see [Supported SQL dialects](https://docs.cloud.google.com/bigquery/docs/enable-sql-translations.md.txt#supported_sql_dialects). - `SOURCE_URI`: the Cloud Storage directory that contains the input files, such as `gs://my_data_bucket/input/`. - `TARGET_URI`: an empty Cloud Storage directory that receives the outputs, such as `gs://my_data_bucket/output/`. - `PROJECT_NUMBER`: your Google Cloud [project number](https://docs.cloud.google.com/resource-manager/docs/creating-managing-projects.md.txt#before_you_begin). - `LOCATION`: the [location](https://docs.cloud.google.com/bigquery/docs/enable-sql-translations.md.txt#locations) that processes the job, such as `us`. When you use this prompt, the MCP client calls the [`translate_batch_queries` tool](https://docs.cloud.google.com/bigquery/docs/reference/migration/mcp/tools_list/translate_batch_queries.md.txt), and then periodically calls the [`fetch_batch_translation` tool](https://docs.cloud.google.com/bigquery/docs/reference/migration/mcp/tools_list/fetch_batch_translation.md.txt) until the translation state is `SUCCEEDED` or `FAILED`. The server writes the translated SQL files and the `translation_report.csv` summary report to `TARGET_URI`. Your job also appears in the translation jobs list in the Google Cloud console. For more sample prompts, see [Use the BigQuery Migration Service MCP server](https://docs.cloud.google.com/bigquery/docs/use-bigquery-migration-mcp.md.txt#sample-prompts). ## Translate metadata > [!WARNING] > > **Preview** > > > This product or feature is > > subject to the "Pre-GA Offerings Terms" in the General Service Terms section > of the [Service Specific > Terms](https://docs.cloud.google.com/terms/service-terms#1). > > Pre-GA products and features are available "as is" and might have limited support. > > For more information, see the > [launch stage descriptions](https://cloud.google.com/products/#product-launch-stages). As well as translating SQL scripts, you can translate the metadata that describes your source data warehouse. A metadata translation job reads the metadata files that you extracted from your source system and writes GoogleSQL data definition language (DDL) statements that recreate those objects in BigQuery. The input is one or more metadata ZIP files. To learn how to produce these files with the `dwh-migration-dumper` tool, see [Generate metadata for translation](https://docs.cloud.google.com/bigquery/docs/generate-metadata.md.txt). You can translate metadata using the Google Cloud console, the gcloud CLI, or the BigQuery Migration Service MCP server. Select one of the following options: ### Console Metadata translation is an output option on a regular translation job: 1. Follow the steps in [Submit a translation job](https://docs.cloud.google.com/bigquery/docs/batch-sql-translator.md.txt#submit_a_translation_job) to configure a job, using the Cloud Storage directory that holds your metadata ZIP files as an input location. 2. In **Optional settings** , select **DDL**. 3. Click **Create** to create the job. The job writes the translated DDL statements to your output directory, alongside any translated SQL. ### gcloud You can translate metadata either as a job of its own, or as an extra output of a batch SQL translation job. **Translate metadata on its own** Use the [`gcloud alpha bq migration translate-metadata` command](https://docs.cloud.google.com/sdk/gcloud/reference/alpha/bq/migration/translate-metadata.md.txt) when your inputs are metadata ZIP files and you don't have SQL to translate: ``` gcloud alpha bq migration translate-metadata \ --source-dialect=SOURCE_DIALECT \ --target-dialect=TARGET_DIALECT \ --location=LOCATION \ --source-gcs-uris=SOURCE_URI \ --target-gcs-path=TARGET_URI ``` Replace the following: - `SOURCE_DIALECT`: the dialect of the source metadata, such as `teradata`. For the supported values, see [Supported SQL dialects](https://docs.cloud.google.com/bigquery/docs/enable-sql-translations.md.txt#supported_sql_dialects). - `TARGET_DIALECT`: the dialect of the target tables. For example, `bigquery`. - `LOCATION`: the [location](https://docs.cloud.google.com/bigquery/docs/enable-sql-translations.md.txt#locations) that processes the job, such as `us`. - `SOURCE_URI`: the Cloud Storage directory that contains the metadata ZIP files, such as `gs://my_data_bucket/metadata/`. - `TARGET_URI`: the Cloud Storage directory that receives the translated DDL statements, such as `gs://my_data_bucket/ddl_output/`. To point at individual metadata ZIP files rather than a directory, use the `--source-gcs-files` flag. To upload metadata files from your local machine as part of the job, use the `--source-local-dirs` or `--source-local-files` flag. As with a batch translation job, the command waits for the job to finish. Add the `--async` flag to submit the job and return a translation ID immediately. **Translate metadata as part of a batch SQL translation** If your batch translation inputs already include your metadata ZIP files, then you don't need a second job. Add `metadata` to the translation outputs of the batch job with the `--target-types` flag, and the job writes the translated SQL and the DDL statements in a single run: ``` gcloud alpha bq migration translate-batch \ --source-dialect=SOURCE_DIALECT \ --target-dialect=TARGET_DIALECT \ --location=LOCATION \ --source-gcs-uris=SOURCE_URI \ --target-gcs-path=TARGET_URI \ --target-types=sql,metadata ``` For the other flags that the [`translate-batch` command](https://docs.cloud.google.com/bigquery/docs/batch-sql-translator.md.txt#submit_a_translation_job) accepts, see [Submit a translation job](https://docs.cloud.google.com/bigquery/docs/batch-sql-translator.md.txt#submit_a_translation_job). ### MCP To translate a metadata ZIP file in Cloud Storage from an MCP client connected to the [BigQuery Migration Service MCP server](https://docs.cloud.google.com/bigquery/docs/use-bigquery-migration-mcp), prompt your MCP client: ``` Translate the DIALECT metadata file METADATA_FILE_URI into BigQuery DDL. Write the output to TARGET_URI. Use PROJECT_NUMBER and LOCATION. ``` Replace `METADATA_FILE_URI` with the Cloud Storage path of your metadata ZIP file, such as `gs://my_data_bucket/metadata.zip`. For descriptions of the other placeholders, see [Submit a translation job](https://docs.cloud.google.com/bigquery/docs/batch-sql-translator.md.txt#submit_a_translation_job). When you use this prompt, the MCP client calls the [`translate_metadata` tool](https://docs.cloud.google.com/bigquery/docs/reference/migration/mcp/tools_list/translate_metadata.md.txt), and then periodically calls the [`fetch_batch_translation` tool](https://docs.cloud.google.com/bigquery/docs/reference/migration/mcp/tools_list/fetch_batch_translation.md.txt) until the state is `SUCCEEDED` or `FAILED`. The server writes the generated DDL statements and table mappings to `TARGET_URI`. ## Generate source DDL > [!WARNING] > > **Preview** > > > This product or feature is > > subject to the "Pre-GA Offerings Terms" in the General Service Terms section > of the [Service Specific > Terms](https://docs.cloud.google.com/terms/service-terms#1). > > Pre-GA products and features are available "as is" and might have limited support. > > For more information, see the > [launch stage descriptions](https://cloud.google.com/products/#product-launch-stages). When your source SQL references tables whose definitions you don't have, the translator can't always resolve the objects, which leads to `RelationNotFound` or `AttributeNotFound` issues. The best way to resolve these issues is to supply the real definitions of your source objects. Run the `dwh-migration-dumper` tool against your source system and include the resulting metadata ZIP file in the translation inputs. For instructions, see [Generate metadata for translation](https://docs.cloud.google.com/bigquery/docs/generate-metadata.md.txt). Extracted metadata describes your objects exactly, so the translator resolves them with no guesswork. If you can't extract metadata, for example when you no longer have access to the source system, then you can ask Gemini to infer the missing DDL statements from your source SQL instead. Gemini infers these DDL statements from how the objects are used in your queries, so always review and verify these statements before using them. You can generate source DDL for your translations using the Google Cloud console, the gcloud CLI, or the BigQuery Migration Service MCP server. Select one of the following options: ### Console Gemini generates source DDL suggestions as part of a regular translation job: 1. Follow the steps in [Submit a translation job](https://docs.cloud.google.com/bigquery/docs/batch-sql-translator.md.txt#submit_a_translation_job) to configure a job. 2. In **Optional settings** , select **Gemini AI suggestions**. 3. Click **Create** to create the job. If the translation produces `RelationNotFound` or `AttributeNotFound` issues, then the job generates suggested source DDL statements for the unresolved objects. The job also translates your SQL, so you don't need a separate job. ### gcloud The [`gcloud alpha bq migration translation generate-source-ddl` command](https://docs.cloud.google.com/sdk/gcloud/reference/alpha/bq/migration/translation/generate-source-ddl.md.txt) reads your source SQL and returns suggested source DDL statements: ``` gcloud alpha bq migration translation generate-source-ddl \ --source-dialect=SOURCE_DIALECT \ --target-dialect=TARGET_DIALECT \ --location=LOCATION \ --source-gcs-uris=SOURCE_URI \ --target-gcs-path=TARGET_URI ``` Replace `SOURCE_URI` with the Cloud Storage directory that contains the source SQL files, and `TARGET_URI` with the Cloud Storage directory that receives the generated DDL statements. The other placeholders are the same as those described in [Translate metadata](https://docs.cloud.google.com/bigquery/docs/batch-sql-translator.md.txt#translate_metadata). To generate suggestions as part of a translation job instead, add the `--enable-ai-suggestion` flag to the [`translate-batch` command](https://docs.cloud.google.com/bigquery/docs/batch-sql-translator.md.txt#submit_a_translation_job). ### MCP To generate source DDL suggestions for your SQL files in Cloud Storage from an MCP client connected to the [BigQuery Migration Service MCP server](https://docs.cloud.google.com/bigquery/docs/use-bigquery-migration-mcp), prompt your MCP client: ``` Generate DDL for the DIALECT SQL files in SOURCE_URI and write the output to TARGET_URI. Use PROJECT_NUMBER and LOCATION. ``` For descriptions of the placeholders, see [Submit a translation job](https://docs.cloud.google.com/bigquery/docs/batch-sql-translator.md.txt#submit_a_translation_job). When you use this prompt, the MCP client calls the [`generate_batch_ddl_suggestion` tool](https://docs.cloud.google.com/bigquery/docs/reference/migration/mcp/tools_list/generate_batch_ddl_suggestion.md.txt), and then periodically calls the [`fetch_batch_ddl_suggestion` tool](https://docs.cloud.google.com/bigquery/docs/reference/migration/mcp/tools_list/fetch_batch_ddl_suggestion.md.txt) until the state is `SUCCEEDED` or `FAILED`. The server writes the suggested DDL statements to the Cloud Storage directory returned in the `fetch_batch_ddl_suggestion` response. To retranslate with the suggested DDL statements, copy them as unzipped SQL files to a directory under `SOURCE_URI` and run `translate_batch_queries` again. You can then supply the generated DDL statements as input to a later translation job to improve translation quality. For more information, see [`RelationNotFound` or `AttributeNotFound` translation issues](https://docs.cloud.google.com/bigquery/docs/batch-sql-translator.md.txt#batch-relation-attribute-not-found). ## Explore the translation output When you run a batch translation job, the batch SQL translator writes the translated files, the summary report, and any Gemini suggestion files to the destination Cloud Storage path that you specified. The Google Cloud console also provides an interface to view these output files, inspect translation issues, and manage your configuration files, regardless of whether you submitted the job from the Google Cloud console, from the command line, or from an MCP client. ### Translation output files The translation job writes output files to the destination Cloud Storage path under the following directories: - `sql/`: the translated SQL files that the translation engine produces after applying any rules in `*.config.yaml` files. If `SOURCE_SQL` rules in `*.ai_config.yaml` files apply, then the engine translates the rewritten SQL files from `source_sql_query_customization_suggestion/` instead of the original input SQL files. - `metadata/`: SQL files that contain the [translated DDL statements](https://docs.cloud.google.com/bigquery/docs/batch-sql-translator.md.txt#translate_metadata) for the tables and views in your input metadata ZIP files when you enable metadata translation. This directory also includes a `config/` subdirectory that contains the translated table metadata in YAML format. - `report/`: job-level report files. This directory includes the [`translation_report.csv` summary report](https://docs.cloud.google.com/bigquery/docs/batch-sql-translator.md.txt#summary_report). - `source_sql_query_customization_suggestion/`: rewritten source SQL files that Gemini produces before translation by applying `SOURCE_SQL` rules in `*.ai_config.yaml` files to the original input SQL files. - `target_sql_query_customization_suggestion/`: rewritten target SQL files that Gemini produces by applying `TARGET_SQL` rules in `*.ai_config.yaml` files to the translated SQL files in `sql/`. - `translation_explanation_suggestion/`: text files that contain Gemini-generated explanations of the translated SQL files in `sql/`. - `source_sql_metadata_generation_suggestion/`: a `generated_metadata.sql` file that contains [Gemini-suggested source DDL statements](https://docs.cloud.google.com/bigquery/docs/batch-sql-translator.md.txt#generate_source_ddl) for unresolved source objects in the input SQL files. The job writes the `*_suggestion/` directories only when you enable Gemini AI suggestions. In the `sql/`, `source_sql_query_customization_suggestion/`, `target_sql_query_customization_suggestion/`, and `translation_explanation_suggestion/` directories, each output file preserves the relative path of its source SQL file from the input directory. Files in the `translation_explanation_suggestion/` directory are text files named after their source SQL files, with the filename extension replaced. For example, the explanation for `queries/report.sql` is `translation_explanation_suggestion/queries/report.txt`. > [!IMPORTANT] > **Important:** Translation is done on a best effort basis. Whenever possible, validate the translated queries. ### Google Cloud console output You can use the Google Cloud console to view the output files in Cloud Storage and review the translation results. To see translation job details, follow these steps: 1. In the Google Cloud console, go to the **SQL Translation** page. [Go to SQL Translation](https://console.cloud.google.com/bigquery/migrations) 2. In the list of translation jobs, locate the job for which you want to see the translation details. Then, click the translation job name. You can see a Sankey visualization that illustrates the overall quality of the 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 can cause additional issues in subsequent stages. 3. Hold the pointer over the error or warning bars, and review the suggestions to determine next steps to debug the translation job. 4. Select the **Log Summary** tab to see a summary of the translation issues, including issue categories, suggested actions, and how often each issue occurred. You can click the Sankey visualization bars to filter issues. You can also select an issue category to see log messages associated with that issue category. 5. Select the **Log Messages** tab to see more details about each translation issue, including the issue category, the specific issue message, and a link to the file in which the issue occurred. You can click the Sankey visualization bars to filter issues. You can select an issue in the **Log Message** tab to open the [**Code tab**](https://docs.cloud.google.com/bigquery/docs/batch-sql-translator.md.txt#code-tab) that displays the input and output file if applicable. 6. Click the **Job details** tab to see the translation job configuration details. ### Summary report The summary report is a CSV file that contains a table of all of the warning 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 the **SQL Translation** page. [Go to SQL Translation](https://console.cloud.google.com/bigquery/migrations) 2. In the list of translation jobs, locate the job that you are interested in, then click the job name or click **More options \> Show details**. 3. In the **Job details** tab, in the **Translation report** section, click **translation_report.csv**. 4. On the **Object details** page, click the value in the **Authenticated URL** row to see the file in your browser. The following table describes the summary file columns: | **Column** | **Description** | |---|---| | Timestamp | The timestamp at which the issue occurred. | | FilePath | The path to the source file that the issue is associated with. | | FileName | The name of the source file that the issue is associated with. | | ScriptLine | The line number where the issue occurred. | | ScriptColumn | The column number where the issue occurred. | | TranspilerComponent | The translation engine internal component where the warning or error occurred. This column might be empty. | | Environment | The translation dialect environment associated with the warning or error. This column might be empty. | | ObjectName | The SQL object in the source file that is associated with the warning or error. This column might be empty. | | Severity | The severity of the issue, either warning or error. | | Category | The translation issue category. | | SourceType | The source of this issue. The value in this column can either be `SQL`, indicating an issue in the input SQL files, or `METADATA`, indicating an issue in the metadata package. | | Message | The translation issue warning or error message. | | ScriptContext | The SQL snippet in the source file that is associated with the issue. | | Action | The action we recommend you take to resolve the issue. | ### Code tab The code tab lets you review further information about the input and output files for a particular translation job. In the code tab, you can examine the files used in a translation job, review a side-by-side comparison of an input file and its translation for any inaccuracies, 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 the **SQL Translation** page. [Go to SQL Translation](https://console.cloud.google.com/bigquery/migrations) 2. In the list of translation jobs, locate the job that you are interested in, then click the job name or click **More options \> Show details**. 3. Select **Code tab**. The code tab consists of the following panels: ![View the code tab in the SQL translation page.](https://docs.cloud.google.com/static/bigquery/images/sql-translation-code-tab.png) - File explorer: Contains all SQL files used for translation. Click a file to view its translation input and output, and any translation issues from its translation. - **Gemini-enhanced input** : The input SQL that was translated by the translation engine. If you have specified Gemini customization rules for the source SQL [in the Gemini configuration](https://docs.cloud.google.com/bigquery/docs/config-yaml-translation.md.txt#ai_yaml_guidelines), then the translator transforms the original input first and then translates the Gemini-enhanced input. To view the original input, click **View original input**. - **Translation output** : The translation result. If you have specified Gemini customization rules for the target SQL in [the Gemini configuration](https://docs.cloud.google.com/bigquery/docs/config-yaml-translation.md.txt#ai_yaml_guidelines), then the transformation is applied to the translated result as a Gemini-enhanced output. If a Gemini-enhanced output is available, then you can click the **Gemini suggestion** button to review the Gemini-enhanced output. 4. Optional: To view an input file and its output file in the [BigQuery interactive SQL translator](https://docs.cloud.google.com/bigquery/docs/batch-sql-translator.md.txt#debug-interactive-translator), click **Edit**. You can edit the files and save the output file back to Cloud Storage. > [!NOTE] > **Note:** You can view log summaries and messages for the overall translation job from the [Results page](https://docs.cloud.google.com/bigquery/docs/batch-sql-translator.md.txt#explore_the_translation_output) ### Configuration tab You can add, rename, view, or edit your configuration YAML files in the **Configuration** tab. The **Schema Explorer** shows the documentation for supported configuration types to help you write your configuration 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 the **SQL Translation** page. [Go to SQL Translation](https://console.cloud.google.com/bigquery/migrations) 2. In the list of translation jobs, locate the job that you are interested in, then click the job name or click **More options \> Show details**. 3. In the **Translation details** window, click the **Configuration** tab. ![View the configuration tab in the SQL translation page.](https://docs.cloud.google.com/static/bigquery/images/sql-translation-config-tab.png) To add a new configuration file: 1. Click more_vert **More 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. Click **Create**. To edit an existing configuration file: 1. Click the configuration YAML file. 2. Edit the file, then click **Save**. 3. Click **Re-run** to run a new translation job that uses the edited configuration YAML files. You can rename an existing configuration file by clicking more_vert **More options** \> **Rename**. ## 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 the `bqutil` public dataset, allowing translated queries to initially reference them using the format `bqutil..()`. For example, `bqutil.fn.cw_count()`. ### Considerations for production environments While `bqutil` offers convenient access to these helper UDFs for initial translation and testing, direct reliance on `bqutil` for production workloads is not recommended for the following reasons: 1. Version control: The `bqutil` project hosts the latest version of these UDFs, which means their definitions can change over time. Relying directly on `bqutil` 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 like `bqutil` for production data processing. Copying UDFs to your controlled environment aligns with such policies. ### Deploying helper UDFs to your project To give you full control over UDF version, customization, and access, we recommend deploying helper UDFs into your own project and dataset for reliable and stable production use. For more information about the necessary scripts and steps to deploy helper UDFs into your environment, see [Deploying the UDFs](https://github.com/GoogleCloudPlatform/bigquery-utils/tree/master/udfs#deploying-the-udfs). ## Troubleshooting This section describes how to debug individual queries and how to resolve the most common translation errors. ### Debug batch translated SQL queries with the interactive SQL translator You can use the BigQuery interactive SQL translator to review or debug a SQL query using the same metadata or object mapping information as your source database. After you complete a batch translation job, BigQuery generates a translation configuration ID that contains information about the job's metadata, the object mapping, or the schema search path, as applicable to the query. You use the batch translation configuration ID with the interactive SQL translator to run SQL queries with the specified configuration. You can debug batch translated SQL queries using either the Google Cloud console or the gcloud CLI. Select one of the following options: ### Console To start an interactive SQL translation by using a batch translation configuration ID, follow these steps: 1. In the Google Cloud console, go to the **SQL Translation** page. [Go to SQL Translation](https://console.cloud.google.com/bigquery/migrations) 2. In the list of translation jobs, locate the job that you are interested in, and then click **More Options \> Open Interactive Translation**. The BigQuery interactive SQL translator now opens with the corresponding batch translation configuration ID. To view the translation configuration ID for the interactive translation, click **Tools** \> **Query translation** \> **Translation settings** in the interactive SQL translator. To debug a batch translation file in the interactive SQL translator, follow these steps: 1. In the Google Cloud console, go to the **SQL Translation** page. [Go to SQL Translation](https://console.cloud.google.com/bigquery/migrations) 2. In the list of translation jobs, locate the job that you are interested in, and then click the job name or click **More options \> Show details**. 3. In the **Translation details** window, click the **Code** tab. 4. In the file explorer, click your filename to open the file. 5. Next to the output filename, click **Edit** to open the files in the interactive SQL translator ([Preview](https://cloud.google.com/products/#product-launch-stages)). You see the input and output files populated in the interactive SQL translator that now uses the corresponding batch translation configuration ID. 6. To save the edited output file back to Cloud Storage, in the interactive SQL translator click **Save \> Save To GCS**. ### gcloud > [!WARNING] > > **Preview** > > > This product or feature is > > subject to the "Pre-GA Offerings Terms" in the General Service Terms section > of the [Service Specific > Terms](https://docs.cloud.google.com/terms/service-terms#1). > > Pre-GA products and features are available "as is" and might have limited support. > > For more information, see the > [launch stage descriptions](https://cloud.google.com/products/#product-launch-stages).
To re-translate and inspect a single query without opening the Google Cloud console, use the [`gcloud alpha bq migration translate` command](https://docs.cloud.google.com/sdk/gcloud/reference/alpha/bq/migration/translate.md.txt). This is useful when you have narrowed a batch translation problem down to one query and want to iterate on it locally. ``` gcloud alpha bq migration translate \ --source-dialect=SOURCE_DIALECT \ --target-dialect=TARGET_DIALECT \ --location=LOCATION \ --input-file=INPUT_FILE \ --output-file=OUTPUT_FILE \ --translation-log-file=LOG_FILE \ --explanation-output-file=EXPLANATION_FILE ``` Replace the following: - `INPUT_FILE`: the local file containing the query to translate. If you omit this flag, the command reads the query from standard input. - `OUTPUT_FILE`: the local file that receives the translated query. If you omit this flag, the command writes the query to standard output. - `LOG_FILE`: the local YAML file that receives the translation logs, which contain the same issue messages that the Google Cloud console shows in the **Log Messages** tab. - `EXPLANATION_FILE`: the local file that receives a Gemini-generated explanation of the translation. To reuse the metadata from your batch job so that the query resolves the same objects, add the `--metadata-gcs-uri` flag. For more information, see [Translate a query into GoogleSQL](https://docs.cloud.google.com/bigquery/docs/interactive-sql-translator.md.txt#translate_a_query_into_standard_sql). ### Troubleshoot translation errors The following sections describe commonly encountered errors when using the batch SQL translator. ### `RelationNotFound` or `AttributeNotFound` translation issues After translating a query using the [batch SQL translator](https://docs.cloud.google.com/bigquery/docs/batch-sql-translator.md.txt#submit_a_translation_job), you might encounter a failed translation with the `RelationNotFound` or `AttributeNotFound` error. You can find failed translations by going to the **Translation details** page in BigQuery in the Google Cloud console and opening the **Log Messages** tab. Translation works best with metadata DDLs. When SQL object definitions can't be found, the translation engine raises `RelationNotFound` or `AttributeNotFound` issues. We recommend using the metadata extractor to generate metadata packages to make sure all object definitions are present. Adding metadata is the recommended first step to resolve most translation errors, because this step often fixes many other errors that are indirectly caused by a lack of metadata. For more information, see [Generate metadata for translation and assessment](https://docs.cloud.google.com/bigquery/docs/generate-metadata.md.txt). #### Fix translation issues with Gemini > [!WARNING] > > **Preview** > > > This product or feature is > > subject to the "Pre-GA Offerings Terms" in the General Service Terms section > of the [Service Specific > Terms](https://docs.cloud.google.com/terms/service-terms#1). > > Pre-GA products and features are available "as is" and might have limited support. > > For more information, see the > [launch stage descriptions](https://cloud.google.com/products/#product-launch-stages). > [!NOTE] > **Note:** To request feedback or support for this feature, contact [bq-edw-migration-support@google.com](mailto:bq-edw-migration-support@google.com). To fix failed translation jobs with the `RelationNotFound` or `AttributeNotFound` errors, you can also use Gemini to resolve these issues: 1. Go to the **Translation details** page and open the **Log Messages** tab. 2. Click the query that has the message `RelationNotFound` or `AttributeNotFound` in the **Category** column. 3. To go to the file and line containing the error in the code tab, click the error message. 4. In the **Action** column, click **Suggested fix**. 5. Select one of the following options, **Apply** or **Apply and rerun**: - To copy the generated schema file from the output directory to the input directory, click **Apply**. - To copy the generated schema file from the output directory to the input directory and open a rerun window, click **Apply and rerun**. ## Quota and limits - [BigQuery Migration API quotas](https://docs.cloud.google.com/bigquery/quotas.md.txt#migration-api-limits) apply. - Each project can have at most 10 active translation tasks. - While there is no hard limit on the total number of source and metadata files, we recommend keeping the number of files to under 1000 for better performance. ## 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 more information, see [Storage pricing](https://cloud.google.com/bigquery/pricing#storage). ## Alternative migration workflows command-line tools You can also submit a batch translation job by using a translation configuration file with the Google Cloud CLI (`gcloud bq migration-workflows`) or with the bq command-line tool command-line tool. These steps assume that you uploaded source files to a Cloud Storage bucket. ### Create a translation configuration file A translation configuration file defines the path to the source files, the output destination, and the source and target dialects of your translation. You can write this file in either YAML or JSON. > [!NOTE] > **Note:** A translation configuration file is not the same as a [configuration YAML file](https://docs.cloud.google.com/bigquery/docs/config-yaml-translation.md.txt). A translation configuration file defines the job itself. A configuration YAML file customizes how the translator transforms your SQL. The following example shows a translation configuration YAML file for a Teradata to BigQuery translation: ```bash tasks: translation_task: type: Teradata2BigQuery_Translation translationDetails: sourceTargetMapping: - sourceSpec: baseUri: gs://bq-translations/input targetSpec: relativePath: output targetBaseUri: gs://bq-translations targetTypes: - sql sourceEnvironment: defaultDatabase: default_db schemaSearchPath: - foo ``` The following example shows a translation configuration JSON file for a Teradata to BigQuery translation: ```bash { "tasks": { "translation_task": { "type": "Teradata2BigQuery_Translation", "translationDetails": { "sourceTargetMapping": [ { "sourceSpec": { "literal": { "literalString": "sel 1", "relativePath": "my_input_1" }, "encoding": "UTF-8" } }, { "sourceSpec": { "literal": { "literalString": "sel 2", "relativePath": "my_input_2" }, "encoding": "UTF-8" } } ], "targetReturnLiterals": [ "sql/my_input_1", "sql/my_input_2" ] } } } } ``` ### Submit and manage translation jobs Use one of the following command-line tools to submit and manage your translation jobs. ### gcloud To create a translation job and run the workflow, use the following command: ```bash gcloud bq migration-workflows create --location=LOCATION --config-file=CONFIG_FILE ``` To create and run the workflow and return immediately with a link to the workflow, add the `--async` flag: ```bash gcloud bq migration-workflows create --location=LOCATION --config-file=CONFIG_FILE --async ``` To list your translation jobs, use the following command: ```bash gcloud bq migration-workflows list --location=LOCATION ``` To see the details of a specific translation job, use the following command: ```bash gcloud bq migration-workflows describe projects/PROJECT_ID/locations/LOCATION/workflows/WORKFLOW_ID ``` Replace the following: - `LOCATION`: the location of the Google Cloud project that is running this translation job. - `CONFIG_FILE`: the path to your translation configuration file. - `PROJECT_ID`: the ID of the Google Cloud project that is running this translation job. - `WORKFLOW_ID`: the ID of the translation job. ### bq To run the translation job, use the following command: ```bash bq mk --migration_workflow --location=LOCATION --config_file=CONFIG_FILE ``` To list all your translation jobs, use the following command: ```bash bq ls --migration_workflow --location=LOCATION ``` To view details about a specific translation job, use the following command: ```bash bq show --migration_workflow projects/PROJECT_ID/locations/LOCATION/workflows/WORKFLOW_ID ``` To remove a translation job from the list, use the following command: ```bash bq rm --migration_workflow projects/PROJECT_ID/locations/LOCATION/workflows/WORKFLOW_ID ``` Replace the following: - `LOCATION`: the location of the Google Cloud project that is running this translation job. - `CONFIG_FILE`: the path to your translation configuration file. - `PROJECT_ID`: the ID of the Google Cloud project that is running this translation job. - `WORKFLOW_ID`: the ID of the translation job. ### Retrieve output files To download the output files after the job completes, use `gcloud storage cp` as described in [Retrieve the output files](https://docs.cloud.google.com/bigquery/docs/batch-sql-translator.md.txt#retrieve-output-files). To review the job in the Google Cloud console, see [Explore the translation output](https://docs.cloud.google.com/bigquery/docs/batch-sql-translator#explore_the_translation_output). ## What's next Learn more about the following steps in data warehouse migration: - [Migration overview](https://docs.cloud.google.com/bigquery/docs/migration-intro.md.txt) - [Migration assessment](https://docs.cloud.google.com/bigquery/docs/migration-assessment.md.txt) - [Schema and data transfer overview](https://docs.cloud.google.com/bigquery/docs/migration/schema-data-overview.md.txt) - [Data pipelines](https://docs.cloud.google.com/bigquery/docs/migration/pipelines.md.txt) - [Interactive SQL translation](https://docs.cloud.google.com/bigquery/docs/interactive-sql-translator.md.txt) - [Use the BigQuery Migration Service MCP server](https://docs.cloud.google.com/bigquery/docs/use-bigquery-migration-mcp) - [Data security and governance](https://docs.cloud.google.com/bigquery/docs/data-governance.md.txt) - [Data validation tool](https://github.com/GoogleCloudPlatform/professional-services-data-validator#data-validation-tool)