Connecting to BigQuery via MCP in VS Code with Roo Code #

This post outlines connecting Google BigQuery via Model Context Protocol (MCP) in VS Code with MCP Toolbox and Roo Code for seamless BigQuery interaction in a more generic way than advertized in the documentation of the MCP Toolbox.

Prerequisites #

Before you begin, ensure you have the following:

• MCP Toolbox: Integrates data sources and tools. (GitHub)
• Roo Code: VS Code extension. (GitHub)
• Google Cloud Project: With BigQuery API enabled and appropriate permissions.

Step 1: Configure tools.yaml #

tools.yaml defines the BigQuery source and a generic query tool, enabling MCP Toolbox to interact with BigQuery.

sources:
  my-bigquery-source:
    kind: "bigquery"
    project: "<your-project-id>" # Replace with your BigQuery project ID
tools:
  bigquery_query:
    kind: bigquery-sql
    source: my-bigquery-source
    description: Generic Query tool.
    templateParameters:
      - name: query
        type: string
        description: "Full query with semicolon at the end"
    statement: |
      {{.query}}

toolsets:
  generic-bigquery-toolset:
    - bigquery_query

Explanation:

• We are using templateParameters and make intentional use of the SQL injection properties by specifying the generic query parameter. For local execution with roo code this poses little risk.
• statement: The actual SQL query template. {{.query}} is a placeholder that will be replaced by the value of the query parameter when the tool is executed.
• toolsets.generic-bigquery-toolset: Groups the bigquery_query tool into a toolset, making it easier to manage and use, but could be omitted.

Step 2: Run the MCP Toolbox Server #

To make tools.yaml available to Roo Code, run the MCP Toolbox server. Navigate to your tools.yaml directory and execute:

genai-toolbox --tools-file "tools.yaml"

Explanation:

• genai-toolbox: The command to run the MCP Toolbox.
• --tools-file "tools.yaml": Specifies the path to your tools.yaml configuration file.

Keep this terminal running in the background as long as you want to use the BigQuery tool.

Step 3: Configure MCP in VS Code #

Configure Roo Code to connect to your running MCP Toolbox server via VS Code settings.json or mcp_settings.json.

Open your VS Code settings (File > Preferences > Settings) and search for “MCP”. Alternatively, you can directly edit the mcp_settings.json file. Add or update the bigquery entry under mcp.servers:

{
  "mcp.servers": {
    "bigquery": {
      "type": "sse",
      "url": "http://127.0.0.1:5000/mcp/sse",
      "disabled": false,
      "alwaysAllow": []
    }
  }
}

Step 4: Using the BigQuery Tool in Roo Code #

Once configured, you can use the bigquery_query tool directly within Roo Code. For example, in a Roo Code “Ask” session, you can prompt like this:

In your_project.your_dataset.your_table table. Can you please help me understand the distribution of null values in column_a in relation to column_b.

Roo Code will then execute queries against your BigQuery project and return the results and helps with interpretation.

By following these steps, you can effectively integrate BigQuery into your VS Code workflow, enabling powerful data querying analysis and debugging capabilities directly from your development environment.

This particular setup has saved me quite some time in the last couple days.