Running an MCP Server for BigQuery: Connect Your Data Warehouse to AI Agents

blog preview

In some of previous posts, we discussed, what MCP is is and how to automatically bootstrap an MCP server from your existing APIs. In this post we'll expand on this topic and look at how to run an MCP server for BigQuery. While we could use FastMCP for creating an MCP server and then connecting it to BigQuery, we'll take a different approach here. We'll use the MCP Toolbox for Databases created by Google.

What is the MCP Toolbox for Databases by Google?

MCP Toolbox for Databases (formerly Gen AI Toolbox for Databases) is an open-source MCP (Model Context Protocol) server that allows developers to connect gen AI agents to enterprise data easily and securely. As a reminder, MCP is an emerging open standard created by Anthropic and supported by many AI companies (Microsoft, OpenAI, Google, etc.) for connecting AI systems with tools and data sources through a standardized protocol, replacing fragmented integrations that require custom integrations.

Currently, Toolbox can be used to build tools for a large number of databases: AlloyDB for PostgreSQL (including AlloyDB Omni), Spanner, Cloud SQL for PostgreSQL, Cloud SQL for MySQL, Cloud SQL for SQL Server, Bigtable and self-managed MySQL and PostgreSQL. And, of course, BigQuery. Because it's fully open-source, it includes contributions from third-party databases such as Neo4j and Dgraph.

Toolbox makes development quite easy, with reduced boilerplate code, enhanced security through OAuth2 and OIDC, and end-to-end observability with OpenTelemetry integration. This enables you to develop tools easier, faster, and more securely by handling the complexities such as connection pooling, authentication, and more.

MCP Toolbox for Databases
architectureMCP Toolbox for Databases architecture

Note that the Toolbox also supports the Google AI Agent Development Kit, but it's not required to use it. When using Toolbox for MCP server, you can simply use any MCP client to connect to it.

Prerequisites

For this guide we ask you to prepare a dataset in BigQuery that you want to use with the MCP server.

Setting Up the MCP Toolbox for BigQuery

Installing the MCP Toolbox is rather simple. It's a single binary file which you might download from Github releases or from googles own storage.

1export VERSION=0.7.0
2curl -O https://storage.googleapis.com/genai-toolbox/v$VERSION/linux/amd64/toolbox
3chmod +x toolbox

Note: For MacOS, replace linux/amd64 with darwin/amd64 in the URL above.

Deploying the MCP Toolbox with docker compose

Additionally to the above binary approach, there is a docker image available, if you'd prefer that.

You can use the following docker-compose.yml file or the equivalent docker run command.

1services:
2 toolbox:
3 image: us-central1-docker.pkg.dev/database-toolbox/toolbox/toolbox:0.7.0
4 hostname: toolbox
5 platform: linux/amd64
6 ports:
7 - "5000:5000"
8 volumes:
9 - ./config:/config
10 command:
11 ["toolbox", "--tools-file", "/config/tools.yaml", "--address", "0.0.0.0"]
12 environment:
13 - GOOGLE_APPLICATION_CREDENTIALS=/run/secrets/bigquery_credentials
14secrets:
15 bigquery_credentials:
16 file: /local/path/to/bigquery_serviceaccount_key.json # Path to your service account key file

Create a config/tools.yaml file (on the machine where you run the docker compose). Use the same content as you would use for the tools.yaml file in the binary approach.

Then run the following command to start the MCP Toolbox server:

1docker compose up -d

The server will start and listen on port 5000.

1http://localhost:5000

Authenticating BigQuery for the MCP Toolbox

The MCP Toolbox uses the Google Application Default Credentials (ADC) to authenticate with BigQuery. To set up the ADC, you need to create a service account or user account with the necessary permissions to access your BigQuery dataset.

Navigate to the Google Cloud Console and there to the IAM & Admin -> Service Accounts section. Click on "Create Service Account" and follow the prompts to create a new service account. In the "Permissions" step, grant the minimum required permissions for accessing your MCP datasets. See here for a list of BigQuery roles and permissions.

Note: If you want to just start quick and dirty, you might add the "BigQuery Data Viewer" and "BigQuery Job User" roles. But make sure to change this for production scenarios.

After creating the service account, add the service account key to your service account.

  • Click the email address of the service account that you want to create a key for.
  • Click the Keys tab.
  • Click the Add key drop-down menu, then select Create new key.
  • Select JSON as the Key type and click Create.
  • Save the JSON key file to your local machine.

Next, you need to set the GOOGLE_APPLICATION_CREDENTIALS environment variable to point to the path of the service account key file.

You can do this by running the following command in your terminal:

1export GOOGLE_APPLICATION_CREDENTIALS="/path/to/your/service-account-file.json"

Note: There are different ways to use the ADC, as described here.

Configuring the MCP Toolbox for BigQuery

Ok, now that we have the MCP Toolbox binary ready and we have a Key, let's configure the MCP Toolbox to connect to BigQuery. Create a tools.yaml file and add the following content:

1sources:
2 bigquery-source:
3 kind: bigquery
4 project: <project-id>
5
6tools:
7 get_article_information:
8 kind: bigquery-sql
9 source: bigquery-source
10 statement: |
11 SELECT
12 article_id,
13 title,
14 editorial,
15 author,
16 published_at
17 FROM
18 `<your-project-name>`.`<your-dataset-name>`.`<your-table-name>`
19 WHERE
20 article_id = @article_id and published_at >= @published_at
21 ORDER BY published_at DESC
22 description: |
23 Use this tool to get information for a specific article.
24 Takes the article ID and published date as parameters.
25
26 Example:
27 {{
28 "article_id": 123,
29 "published_at": "2025-02-01T00:00:00Z",
30 }}
31 parameters:
32 - name: article_id
33 type: integer
34 description: Article ID
35 - name: published_at
36 type: string
37 description:
38 Published date (DateTime) in ISO 8601 format. Only articles published
39 after this date will be returned.
40toolsets:
41 bigquery-toolset:
42 - get_article_information

The file-content is quite self-explanatory we think. The sources section defines the BigQuery source, where you need to replace <project-id> with your actual Google Cloud project ID. The tools section defines one or many tools that can interact with BigQuery. In this example, we define a tool to get article information from a BigQuery table. You need to replace <your-project-name>, <your-dataset-name>, and <your-table-name> with your actual BigQuery project, dataset, and table names.

As we have a parametrized SQL statement, we also define the parameters which should be provided when calling the tool. This section is optional, if there are no parameters.

Finally, we need the toolsets section, which allows us to group multiple tools together. In this case, we only have one tool, but you can add more tools to the bigquery-toolset if needed. This might come in handy if you to group multiple tools for different purposes and use cases.

Running the MCP Toolbox for BigQuery

Now that we have the tools.yaml file ready, we can run the MCP Toolbox for BigQuery. If you are using the binary approach, run the following command:

1./toolbox --tools-file=tools.yaml

The output should look like this:

12025-06-30T12:23:18.424461+02:00 INFO "Initialized 1 sources."
22025-06-30T12:23:18.424569+02:00 INFO "Initialized 0 authServices."
32025-06-30T12:23:18.424579+02:00 INFO "Initialized 1 tools."
42025-06-30T12:23:18.424586+02:00 INFO "Initialized 2 toolsets."
52025-06-30T12:23:18.42478+02:00 INFO "Server ready to serve!"

Navigate to http://localhost:5000 in your web browser, and you should see the following message:

1🧰 Hello, World! 🧰

You can navigate to the /api/toolset route to check if your tool configuration is set up correctly.

To test out the MCP server, let's use another Google too, the MCP Inspector with Toolbox. (Great naming Google...)

Run the inspector with the following command:

1npx @modelcontextprotocol/inspector

Navigate to the url provided in the terminal output.

Note: Make sure to use the url with the appended session token, which looks like this: http://localhost:6274/?MCP_PROXY_AUTH_TOKEN=5483afb722593d8b47810ba9bf8b4d720e136a94299a153d226c0cd3eb4513f2.

You should see the MCP Inspector interface.

MCP InspectorMCP Inspector

Select Streamable HTTP as the connection type and enter the URL of your MCP Toolbox server, with the mcp/sse endpoint, like this:

1http://127.0.0.1:5000/mcp/sse

Hit the "Connect" button, and you should see the MCP methods.

MCP Inspector with
ToolboxMCP Inspector with Toolbox

Click on the List Tools button, then select the get_article_information tool.

On the right side, you should see the tool description and the parameters. Enter some parameters and then click Run Tool. If you configured everything correctly, you should get some data back.

MCP Inspector successMCP Inspector success

Perfect! We have successfully set up an MCP server for BigQuery using the MCP Toolbox. But this tool is kinda lame, isn't it? We only have the option to get one very specific information from one table. What a good database MCP server requires are two things:

  1. A way to find which tables are available in the database and what columns they have. Ideally with a description of the table and columns.

  2. A way to query the database with any supported SQL statement. The MCP clients can then use Text-to-SQL models to generate the SQL from their natural language user queries.

    Note: In theory an MCP server could also provide the Text-to-SQL functionality. But this is mostly not desirable, as the client should pay the bill for the Text-to-SQL model, not the server. Also, the client is on the 'source' of the natural language input and therefore often is better suited to generate the SQL query.

Extending the MCP server for BigQuery with Table Metadata and SQL Querying

Let's first extend the MCP server to provide table metadata. For getting table infos, we simply can add a second tool:

1tools:
2 other_tools: ...
3 bigquery_get_table_info:
4 kind: bigquery-get-table-info
5 source: bigquery-source
6 description: Use this tool to get table metadata. Always use the dataset-id
7 <my-dataset>

When using this tool, we need two parameters:

  • the dataset-id
  • the table-id

While most of the time, we can hardcode the dataset-id, we might need to dynamically retrieve the table-id. Otherwise our tool is kinda static. Luckily, there is another tool for that - the bigquery-list-table-ids tool.

1tools:
2 list_table_ids:
3 kind: bigquery-list-table-ids
4 source: bigquery-source
5 description: Use this tool to get table metadata. Always use the dataset-id
6 <my-dataset>

This was quite easy, right? Now we can use the MCP Inspector to test the new tools. Select the bigquery_get_table_info tool and enter the dataset-id and table-id as parameters. You should get a response with the table metadata, including the table name, description, and columns with their types and descriptions. Do the same for the list_table_ids tool, and you should get a list of all tables in the dataset with their IDs.

MCP Inspector get table idsMCP Inspector get table ids

Note: Why are these metadata tools useful? Because clients can then automatically discover the available tables and columns in the database and use this information to generate more accurate SQL queries.

Now let's add the second part, the SQL querying. For this, we can use the bigquery-execute-sql tool. This tool allows us to execute any SQL statement on the BigQuery database. Add the following tool to your tools.yaml file:

1tools:
2 execute_sql:
3 kind: bigquery-execute-sql
4 source: bigquery-source
5 description: Use this tool to execute sql statements on BigQuery. Always use the dataset-id
6 <my-dataset>

And once again - and finally - we can use the MCP Inspector to test the new tool.

What we implemented

With just a single yaml file we were able to implement a fully functional MCP server querying data on BigQuery. That's pretty cool and a huge selling point for the MCP Toolbox. We were able to:

  • Connect to BigQuery using the MCP Toolbox
  • Define tools to query data from BigQuery
  • Define tools to get table metadata and list tables in a dataset
  • Define a tool to execute any SQL statement on BigQuery
  • Test the MCP server using the MCP Inspector

What's next?

While this post focused on setting up a basic MCP server for BigQuery, there are two more things we want to explore in the next posts:

  • Authenticating the MCP server
  • Using the MCP server with ChatGPT (or similar)

Stay tuned!

Interested in building high-quality AI agent systems?

We prepared a comprehensive guide based on cutting-edge research for how to build robust, reliable AI agent systems that actually work in production. This guide covers:

  • Understanding the 14 systematic failure modes in multi-agent systems
  • Evidence-based best practices for agent design
  • Structured communication protocols and verification mechanisms

Further Reading

More information on our managed RAG solution?
To Pondhouse AI
More tips and tricks on how to work with AI?
To our Blog