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

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
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.
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.
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:
The server will start and listen on port 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:
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:
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:
The output should look like this:
Navigate to http://localhost:5000
in your web browser, and you should see
the following message:
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:
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 Inspector
Select Streamable HTTP
as the connection type and enter the URL of your
MCP Toolbox server, with the mcp/sse
endpoint, like this:
Hit the "Connect" button, and you should see the MCP methods.
MCP 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 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:
-
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.
-
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:
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.
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 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:
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