Build a Google Trends Analyst Agent with BigQuery MCP

1. Overview

In this codelab, you will build a Google Trends Analyst Agent using the Google ADK. This agent leverages the BigQuery MCP server to dynamically discover and execute tools that query the Google Trends public dataset (bigquery-public-data.google_trends).

What you'll learn

How to set up a project for ADK development.
How to use the managed MCP server for BigQuery.
How to build an agent that uses MCP tools.
How to run the agent locally for testing.
How to deploy the agent to Google Cloud Run.

What you'll need

A Google Cloud project with billing enabled
A web browser such as Chrome
Python 3.11+ and uv, if you run the code on your own machine instead of Cloud Shell

This codelab is for intermediate developers who have some familiarity with Python and Google Cloud.

This codelab takes approximately 20 minutes to complete.

The resources created in this codelab should cost less than $5.

2. Set up your environment

Create a Google Cloud Project

In the Google Cloud Console, on the project selector page, select or create a Google Cloud project.
Make sure that billing is enabled for your Cloud project. Learn how to check if billing is enabled on a project.

Authenticate and Configure gcloud

gcloud auth login
gcloud auth application-default login

Note: gcloud auth login authenticates the gcloud CLI commands, while gcloud auth application-default login authenticates local application code.

Set the Google Cloud project:

gcloud config set project <YOUR_PROJECT_ID>

Enable Necessary APIs

Enable the required Google Cloud APIs for Cloud Run, Artifact Registry, BigQuery, and Gemini Enterprise.

gcloud services enable run.googleapis.com \
                       cloudbuild.googleapis.com \
                       artifactregistry.googleapis.com \
                       bigquery.googleapis.com \
                       aiplatform.googleapis.com

The BigQuery remote MCP server is enabled when you enable the BigQuery API, so you don't need a separate step for it.

Create a Project Folder

Start by creating a root folder for your agent and an internal folder for the agent implementation.

mkdir -p ~/google-trends-agent/google_trends
cd ~/google-trends-agent
uv init --bare

uv creates an isolated Python environment for this codelab, so you don't need to activate anything. Prefix Python commands with uv run.

Configure your project settings

Run the following command to create google_trends/.env with your project and location. GOOGLE_GENAI_USE_ENTERPRISE=True has ADK call Gemini through your Google Cloud project, using Cloud IAM for authentication instead of a GEMINI_API_KEY. ADK loads this file automatically, so the settings also work in new terminals.

cat > ~/google-trends-agent/google_trends/.env <<EOF
GOOGLE_CLOUD_PROJECT=$(gcloud config get project)
GOOGLE_CLOUD_LOCATION=global
GOOGLE_GENAI_USE_ENTERPRISE=True
EOF

3. Create the Agent Code

Create an empty file named google_trends/__init__.py:

touch google_trends/__init__.py

Then, create a file named google_trends/agent.py and paste the following code:

import os
import textwrap
import warnings
from datetime import date

import google.auth
from google.adk.agents import LlmAgent
from google.adk.apps import App
from google.adk.models import Gemini
from google.adk.tools.mcp_tool import McpToolset
from google.adk.tools.mcp_tool.mcp_session_manager import StreamableHTTPConnectionParams
from google.auth.transport.requests import Request
from google.genai import types

# Suppress experimental ADK credential warnings
warnings.filterwarnings("ignore")

def get_agent_instruction(project_id: str) -> str:
    """Generates a clear and formatted prompt for the data analyst."""
    instruction = f"""
    # ROLE
    You are a Google Search Trends Analyst. Your mission is to provide clear answers using SQL data.

    # DATA CONSTRAINTS
    - BigQuery tool `execute_sql` requires explicit billing project mapping. Use: '{project_id}'.
    - Target dataset strictly: `bigquery-public-data.google_trends`

    # SCHEMA DISCOVERY (CRITICAL)
    1. DO NOT call `get_table_info` or `list_table_ids` (Triggers Permission Errors).
    2. Run `SELECT * FROM table LIMIT 0` via `execute_sql` for field definition mapping.

    # OUTPUT PRESENTATION
    - Render purely as a cleanly aligned Markdown table.
    - Use clear and descriptive headers for each column.
    - Remove conversational preambles. Output only the results.
    """
    return textwrap.dedent(instruction).strip()

credentials, _ = google.auth.default(
    scopes=["https://www.googleapis.com/auth/cloud-platform"]
)

def get_auth_headers(context=None) -> dict[str, str]:
    """Fetch auth headers, refreshing the access token when it expires."""
    if not credentials.valid:
        credentials.refresh(Request())

    return {"Authorization": f"Bearer {credentials.token}"}

def get_todays_date() -> str:
    """Returns today's date in YYYY-MM-DD format."""
    return date.today().isoformat()

# --- Application Initialization ---
project_id = os.environ.get("GOOGLE_CLOUD_PROJECT")
if not project_id:
    raise ValueError("GOOGLE_CLOUD_PROJECT environment variable is not set")

mcp_headers = {
    "Content-Type": "application/json",
    "Accept": "application/json, text/event-stream",
}

# Configure BigQuery Tools via MCP
bq_tools = McpToolset(
    connection_params=StreamableHTTPConnectionParams(
        url="https://bigquery.googleapis.com/mcp",
        headers=mcp_headers,
    ),
    # Called for each MCP request, so the token stays fresh on long-running servers
    header_provider=get_auth_headers,
)

# Initialize the LLM Agent
root_agent = LlmAgent(
    name="google_trends",
    model=Gemini(
        model="gemini-3.8-flash",
        retry_options=types.HttpRetryOptions(attempts=5),
    ),
    tools=[get_todays_date, bq_tools],
    instruction=get_agent_instruction(project_id),
)

# Create the ADK App
app = App(name=root_agent.name, root_agent=root_agent)

4. Run the agent

In this step, you will install dependencies and run the agent to verify its behavior on your machine (or Google Cloud Shell).

Navigate to the application root directory (google-trends-agent):

cd ~/google-trends-agent

Install the required Python packages:

uv add google-auth "google-adk[mcp]"

We will use ADK Web to test our new agent.

For local development and testing, run the following command:

uv run adk web

For development and testing within Google Cloud Shell, run the following command:

uv run adk web --allow_origins="*"

This will start the ADK web server. You can interact with the agent using the local web interface (usually at http://localhost:8000).

Note: You may also use the simpler uv run adk run google_trends command to test the agent.

5. Deploy to Cloud Run

Follow these steps to deploy the agent securely to Google Cloud Run.

Grant Permissions

Cloud Run needs permission to call Gemini and use BigQuery MCP tools.

# Get your project ID and number automatically
PROJECT_ID=$(gcloud config get project)
PROJECT_NUMBER=$(gcloud projects describe ${PROJECT_ID} --format='value(projectNumber)')

# Model access: To talk to the Gemini model
gcloud projects add-iam-policy-binding ${PROJECT_ID} \
  --member="serviceAccount:${PROJECT_NUMBER}-compute@developer.gserviceaccount.com" \
  --role="roles/aiplatform.user"

# MCP & BigQuery: To execute tools and run SQL jobs
gcloud projects add-iam-policy-binding ${PROJECT_ID} \
  --member="serviceAccount:${PROJECT_NUMBER}-compute@developer.gserviceaccount.com" \
  --role="roles/mcp.toolUser"

gcloud projects add-iam-policy-binding ${PROJECT_ID} \
  --member="serviceAccount:${PROJECT_NUMBER}-compute@developer.gserviceaccount.com" \
  --role="roles/bigquery.jobUser"

gcloud projects add-iam-policy-binding ${PROJECT_ID} \
  --member="serviceAccount:${PROJECT_NUMBER}-compute@developer.gserviceaccount.com" \
  --role="roles/bigquery.dataViewer"

Create the Dockerfile

In the root of your project (google-trends-agent), create a file named Dockerfile and paste the following content:

FROM python:3.11-slim
WORKDIR /app

# Create a non-root user
RUN adduser --disabled-password --gecos "" myuser
USER myuser
ENV PATH="/home/myuser/.local/bin:$PATH"

# Copy the agent folder into the container
COPY --chown=myuser:myuser google_trends/ /app/agents/google_trends/

# Install the python packages
RUN pip install google-auth "google-adk[mcp]"

# Set environment variables
ENV GOOGLE_CLOUD_LOCATION=global
ENV GOOGLE_GENAI_USE_ENTERPRISE=True

# Expose port
EXPOSE 8080

# Run ADK web server
CMD ["adk", "web", "--port=8080", "--host=0.0.0.0", "/app/agents"]

Deploy the Agent

Run the following command from the root directory. This will containerize and deploy your agent to Cloud Run.

gcloud run deploy google-trends-agent \
  --source . \
  --region us-west1 \
  --allow-unauthenticated \
  --set-env-vars="GOOGLE_CLOUD_PROJECT=$(gcloud config get project)"

Quick Verification

Once the deployment finishes, the console will output a Service URL. Open this URL in your browser; it will provide the same interactive UI you saw locally, but now running in the cloud!

You can also monitor the logs to ensure everything is running smoothly:

gcloud logging read "resource.type=cloud_run_revision AND resource.labels.service_name=google-trends-agent" --limit 10

6. Clean Up

To avoid ongoing charges, delete the resources created during this codelab.

Delete the Cloud Run service:

gcloud run services delete google-trends-agent --region us-west1

If you created a project specifically for this codelab, you can delete the entire project:

gcloud projects delete <YOUR_PROJECT_ID>

You may also delete all the files related to this codelab:

cd ~
rm -rf ~/google-trends-agent

7. Congratulations

Congratulations! You have successfully built and deployed a Google Trends Analyst Agent.