/bigquery-migration-sql-translator

name: bigquery-migration-sql-translator
metadata:
  version: "1.0.0"
  category: BigDataAndAnalytics
  publisher: google
description: >-
  Translates SQL queries, DDL schemas, and stored procedures from legacy database
  dialects (Teradata, Oracle, Redshift, Snowflake, SparkSQL, HiveQL) to BigQuery
  GoogleSQL using the BigQuery Migration Service (BQMS) MCP server. Use when
  translating legacy SQL queries, migrating schemas or metadata zip archives,
  or configuring BigQuery Migration Service (BQMS) YAML translation settings.
  Don't use for executing BigQuery queries or managing datasets and tables (use
  bigquery-basics), diagnosing runtime job errors (use bigquery-troubleshooting),
  or tuning slot reservations (use bigquery-optimization).

BigQuery Migration Service (BQMS) SQL Translator

The BigQuery Migration Service (BQMS) translates legacy database SQL scripts, queries, and schemas into GoogleSQL. This skill coordinates translation workflows, Cloud Storage staging, compiler error triage, and YAML configuration.

Before you begin

Before translating queries or schemas, verify the following prerequisites:

1. **Google Cloud SDK & Project Setup**: Ensure the [Google Cloud SDK](https://cloud.google.com/sdk/docs/install) is installed, verify that your Google Cloud project has an active Cloud Billing account attached, and select the target project:

    gcloud config set project {project_id}

2. **Enable the API**: Enable the BigQuery Migration API (`bigquerymigration.googleapis.com`) in your Google Cloud project:

    gcloud services enable bigquerymigration.googleapis.com

3. **Required IAM Roles**: Verify that your identity or service account has:

• `roles/mcp.toolUser` (MCP Tool User)
• `roles/bigquerymigration.editor` (Migration Workflow Editor)
• For batch translation, `roles/storage.objectViewer` and

`roles/storage.objectCreator` (or `roles/storage.objectAdmin`) on the Cloud Storage buckets that hold the input and output files. 4. **MCP Server Connection**: If BigQuery Migration Service (BQMS) tools (e.g. `translate_query`, `translate_batch_queries`) are not present in your active tool list, configure the remote BigQuery Migration Service (BQMS) MCP server. For setup instructions across clients (Claude, Cursor, Gemini CLI, VS Code), see [mcp_config.md](references/mcp_config.md). 5. **Cost & Cloud Storage Cleanup**: Review [Google Cloud Pricing](https://cloud.google.com/pricing) for Cloud Storage and BigQuery usage details. After completing a batch migration, clean up temporary staged input and output folders in Cloud Storage:

    gcloud storage rm -r gs://{bucket}/{folder}

6. **Translation Tracking**: For batch translations, and for queries that need more than one translation iteration, keep a translation tracker (e.g. `translation_tracker.md`) to document goals, planned steps, and progress. After each translation, record one row with this template:

Iteration | Status | Total Errors | Correctness Errors | Completeness Errors | Compatibility Errors | Key Actions / Transformations --------- | ------ | ------------ | ------------------ | ------------------- | -------------------- | -----------------------------

A single query that translates cleanly on the first attempt needs no tracker: report its translation ID and logs in your response.

Workflow Selection

Choose the workflow based on your migration scope:

• **Interactive Translation (Single Query)**: Best for ad-hoc queries,

exploratory testing, dialect syntax comparison, or quick error debugging.

• **Batch Translation (Multiple Files & Cloud Storage)**: Use for more than

one file or a directory, including SQL embedded in application code (extract it into `.sql` files first). For multi-file Python codebases containing embedded SQL, use [python_embedded_sql_migrator_template.py](assets/python_embedded_sql_migrator_template.py) to extract queries into staged files and re-inject translated SQL. Batch translation is faster and cheaper than translating files one by one, and it resolves cross-file dependencies, so it usually gives better results. Run `translate_batch_queries` on the staged files first; use `translate_query` only afterwards if needed to debug a specific statement from `translation_report.csv`.

1. Interactive Translation Workflow

Use the interactive workflow to translate a single query string, inspect compiler logs, and iteratively resolve issues.

Steps

1. **Submit Query (`translate_query`)**: Call `translate_query` with `projectNumber`, `location`, `inputQuery`, `sourceDialect`, and `targetDialect` (`BigQuery`). Include optional parameters:

• `metadataFilePath`: Cloud Storage URI to a metadata zip file

(`gs://.../metadata.zip`) for schema resolution.

• `translationConfigs`: Inline YAML configurations (e.g. object renaming,

schema redirection, optimizer rules). Show the YAML you used in your response so the user can reuse it (e.g. in batch translation).

2. **Poll Status (`get_translation`)**: If the returned `translation_state` is `RUNNING` or `PENDING`, poll `get_translation` using the `translation` ID, `projectNumber`, and `location` until `translation.state` is `SUCCEEDED` or `FAILED`, at the interval given in the BigQuery Migration Service (BQMS) MCP tool descriptions. While the job runs, do work that doesn't need the result (e.g. read the dialect gotchas reference).

3. **Inspect Output & Logs**:

• Examine `translated_query` for the converted GoogleSQL.
• Review each entry in `translation_logs`: inspect `severity` (`ERROR`,

`WARNING`), `category` (e.g. `RelationNotFound`), and `effect` (`CORRECTNESS`, `COMPLETENESS`, `COMPATIBILITY`).

• Check the SQL itself, not