Google BigQuery

Google BigQuery

This connector works with Google BigQuery through the BigQuery REST API v2 (https://bigquery.googleapis.com/bigquery/v2): projects, datasets, tables and their schemas, free row previews, SQL queries with a cost estimate first, query results page by page, and the job history. It signs in with your Google account (OAuth), so the AI sees exactly what that account may see in BigQuery.

Setup (one-time, about 10 minutes)

1. Open https://console.cloud.google.com, pick or create a project, then APIs & Services → Library → enable BigQuery API.
2. APIs & Services → OAuth consent screen: choose Internal if you are on Google Workspace (simplest), otherwise External. For External, set the publishing status to In production: while an app is in Testing, Google revokes its refresh tokens after 7 days and the connector stops working every week. For your own use, the "unverified app" warning can be clicked through.
3. APIs & Services → Credentials → Create credentials → OAuth client ID → application type Web application → add the authorized redirect URI <your AnythingMCP URL>/api/mcp-oauth/callback (on AnythingMCP Cloud: https://cloud.anythingmcp.com/api/mcp-oauth/callback).
4. Paste the client ID and secret into GOOGLE_CLIENT_ID and GOOGLE_CLIENT_SECRET and install the connector.
5. On the connector page, click Authorize with Provider and sign in with the Google account whose BigQuery data the AI should use. The refresh token is stored encrypted and access tokens are renewed automatically.

One Google client for all Google connectors: the same Cloud project and OAuth client can serve every Google connector. Enable each API in that project, reuse the same GOOGLE_CLIENT_ID and GOOGLE_CLIENT_SECRET, and authorize each connector once.

Permissions and scope

• The connector asks for https://www.googleapis.com/auth/bigquery (view and manage BigQuery data). What the AI can actually read, query or change is decided by the signed-in account's IAM roles. To run queries the account needs BigQuery Job User on the project that runs them and BigQuery Data Viewer on the data.
• Read-only setup: Google publishes no bigquery.readonly scope for this API. Use https://www.googleapis.com/auth/cloud-platform.read-only instead (it allows listing, reading and running query jobs) in the connector's OAuth settings, authorize again, and give the account only BigQuery Data Viewer and BigQuery Job User: without write roles on the data, statements that change data fail with 403. Also disable bigquery_cancel_job.

Exploring (free, no bytes scanned): bigquery_list_projects → bigquery_list_datasets → bigquery_list_tables → bigquery_get_table (schema, size, partitioning). bigquery_get_dataset shows a dataset's location and settings. Always read the schema before writing SQL.

Queries cost money: dry-run first. On on-demand pricing BigQuery bills the bytes a query scans, about US$6.25 per TiB in most regions (the first 1 TiB each month is free; at least 10 MB per table referenced). Capacity (editions) pricing bills slots instead.

1. Before running a query on a table you have not sized, call bigquery_run_query with dryRun: true. It costs nothing and returns totalBytesProcessed (the estimate) and statementType.
2. Tell the user the estimate in GB and the approximate cost. If it is above 10 GB (about 6 US cents), ask the user to confirm before running it, and suggest narrowing it first: select only the needed columns (SELECT * reads every column), filter on the partition column, use a smaller date range.
3. Then run it without dryRun. Pass maximumBytesBilled slightly above the estimate as a safety net: a query that would bill more fails without charge.

• LIMIT does not reduce the bytes scanned. To look at sample rows use bigquery_preview_table_rows, which is free.
• Check bigquery_get_table first: numBytes shows the table size, timePartitioning and requirePartitionFilter show whether a partition filter is needed.

Statements that change data need confirmation. bigquery_run_query runs any GoogleSQL statement the account is allowed to run, including INSERT, UPDATE, DELETE, MERGE, CREATE, ALTER and DROP. Before running anything other than a SELECT, show the user the exact statement and its effect and wait for an explicit yes. A dry run returns the statementType, which tells you what a statement does.

Writing queries

• The connector always uses GoogleSQL (useLegacySql: false). Qualify tables as `project.dataset.table` with backticks, e.g. `bigquery-public-data.samples.shakespeare`, or set defaultDataset.
• Use query parameters for user-supplied values: @name in the SQL plus parameterMode: "NAMED" and queryParameters, e.g. [{"name": "country", "parameterType": {"type": "STRING"}, "parameterValue": {"value": "DE"}}].
• Run queries in the project the user pays for; data in another project (such as bigquery-public-data) is read across projects.

Reading results

• rows come back as a list of {"values": [...]}, one value per column in the order of schema. Every value is a string (numbers too), null stays null; TIMESTAMP values are seconds since 1970 (e.g. "1.7290944E9"), RECORD values are nested {"f": [{"v": ...}]}.
• bigquery_run_query returns up to 100 rows by default (maxResults, at most 1,000). When pageToken is present, more rows exist: call bigquery_get_query_results with the jobId and location from jobReference and that pageToken. Summarise or aggregate in SQL rather than paging through large results.
• Long queries: when jobComplete is false, the query keeps running. Wait a few seconds and call bigquery_get_query_results with the same jobId until jobComplete is true, or bigquery_get_job to see its state. bigquery_cancel_job stops it (ask the user first; bytes already scanned may still be billed).

History: bigquery_list_jobs lists recent jobs of a project (your own by default, allUsers: true for everyone's with the right permission) and bigquery_get_job shows one job's SQL, state, bytes processed and billed and errors.

Common errors

• 401: the authorization expired or was revoked (always after 7 days while the consent screen is in Testing). Click Authorize with Provider again.
• 403 accessDenied: the account lacks a role (Job User on the query project, Data Viewer on the data), or a read-only scope was used for a change. 403 BigQuery API has not been used in project: enable the BigQuery API in the Cloud project of the OAuth client.
• 403 bytesBilledLimitExceeded: the query would scan more than maximumBytesBilled; nothing was charged.
• 400 invalidQuery: SQL error, the message names the position. 404 notFound: wrong project, dataset or table id, or a job queried without its location.
• 403 quotaExceeded / rateLimitExceeded: wait and retry.

Out of scope here: loading or exporting data, copying tables, creating or deleting datasets and tables through the API (DDL statements in a query can do it, with confirmation), BigQuery ML model management, scheduled queries and the Storage Read API.