bigquery-migration-sql-translator

Files (33)

• SKILL.md
• python_embedded_sql_migrator_template.py · assets/
• environment_example.yaml · assets/translation_config_examples/
• experimental_input_formats_example.yaml · assets/translation_config_examples/
• experimental_metadata_example.yaml · assets/translation_config_examples/
• experimental_object_rewriter_example.yaml · assets/translation_config_examples/
• experimental_optimizer_example.yaml · assets/translation_config_examples/
• experimental_statement_rewriter_example.yaml · assets/translation_config_examples/
• input_formats_example.yaml · assets/translation_config_examples/
• metadata_example.yaml · assets/translation_config_examples/
• object_rewriter_example.yaml · assets/translation_config_examples/
• optimizer_example.yaml · assets/translation_config_examples/
• statement_rewriter_example.yaml · assets/translation_config_examples/
• environment.json · assets/translation_config_schemas/
• experimental_input_formats.json · assets/translation_config_schemas/
• experimental_metadata.json · assets/translation_config_schemas/
• experimental_object_rewriter.json · assets/translation_config_schemas/
• experimental_optimizer.json · assets/translation_config_schemas/
• experimental_statement_rewriter.json · assets/translation_config_schemas/
• input_formats.json · assets/translation_config_schemas/
• metadata.json · assets/translation_config_schemas/
• object_rewriter.json · assets/translation_config_schemas/
• optimizer.json · assets/translation_config_schemas/
• statement_rewriter.json · assets/translation_config_schemas/
• transpiler_config_spec.json · assets/translation_config_schemas/
• effect_compatibility.md · references/
• effect_completeness.md · references/
• effect_correctness.md · references/
• mcp_config.md · references/
• oracle_gotchas.md · references/
• snowflake_gotchas.md · references/
• teradata_gotchas.md · references/
• translation_yaml_config.md · references/

SKILL.md (first lines)

# 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.

## Usage Attribution

**MUST READ**: Before executing any `gcloud`, REST API, or MCP command, you
MUST read and follow the instructions in
[Usage Attribution](references/attribution.md).

## 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:

    ```bash
    gcloud config set project {project_id}
    ```

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

    ```bash
    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:

    ```bash
    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 only the logs: BigQuery Migration Service
        (BQMS) emits `ERROR_TYPE(...)` for types it cannot map, sometimes with
        no log entry. Treat any `ERROR_TYPE` or inline error comment as an
        error, and when a BigQuery project is available, dry-run the result (`bq
        query --dry_run --use_legacy_sql=false`). A `Not found` or `must be
        qualified with a dataset` error still means the SQL is syntactically
        valid; the referenced objects just don't exist in BigQuery yet. Say so
        and move on, even if the error suggests a fix: don't stub tables or

From google/skills, Apache-2.0.

More from google/skills