Skip to main content

Google BigQuery

Connect Google BigQuery to BotDojo so agents can discover accessible datasets and tables, inspect table schemas, and run GoogleSQL using a Google user's permissions.

Before You Start​

You will need:

  • a Google Cloud project with the BigQuery API enabled
  • a Google account that has bigquery.jobs.create on the project that will run and be billed for queries
  • permission to read the tables referenced by each query
  • the dataset location, such as US, EU, or us-central1

The exact data permissions depend on the SQL. Reading a table commonly requires bigquery.tables.getData in addition to permission to create the query job.

Configure BotDojo's Google OAuth Client​

BotDojo deployment administrators must configure the shared Google web OAuth client before users connect BigQuery:

  1. Open the Google Cloud Console project that owns BotDojo's OAuth client.

  2. Enable the BigQuery API.

  3. Add the read-only BigQuery scope: https://www.googleapis.com/auth/bigquery.readonly.

  4. Register each environment's exact callback URL. Production uses:

    https://app.botdojo.com/integrations/google-bigquery/oauth_redirect

  5. Ensure the server has GOOGLE_CLIENT_ID and GOOGLE_CLIENT_SECRET configured.

The callback URL must exactly match the URL generated from BOTDOJO_APP_URL.

Connect BigQuery in BotDojo​

  1. Open Integrations in BotDojo.
  2. Find Google BigQuery and click Connect.
  3. Enter the Google Cloud Project ID that should create and be billed for query jobs.
  4. Optionally enter a Default Dataset for unqualified table names.
  5. Enter the BigQuery Location when the datasets are not discoverable in the default location.
  6. Continue to Google, choose an account, and approve the requested access.
  7. Use Test Connection to run a minimal query in the configured project.

Discover Datasets and Tables​

The integration provides three read-only metadata tools:

  • List BigQuery Datasets returns datasets in the configured project that Google makes visible to the connected account.
  • List BigQuery Tables returns tables and views in a specified dataset.
  • Get BigQuery Table Schema returns column definitions, nested fields, descriptions, and basic partitioning or clustering metadata.

Dataset and table listings return up to 100 results by default. Pass maxResults to request between 1 and 1,000 results. When a response includes nextPageToken, pass it back as pageToken to retrieve the next page.

These tools use BigQuery's native metadata APIs instead of project-wide INFORMATION_SCHEMA queries. List BigQuery Datasets is filtered to datasets on which the connected account has bigquery.datasets.get. Listing tables requires bigquery.tables.list on the dataset, and schema lookup requires bigquery.tables.get on the table.

For common least-privilege access:

  • grant roles/bigquery.jobUser on the project that runs query jobs
  • grant roles/bigquery.metadataViewer on datasets whose tables should be discoverable
  • grant roles/bigquery.dataViewer on datasets whose table data may be queried

Grant roles/bigquery.metadataViewer at project level only when the connected account should discover metadata across the entire project.

Execute BigQuery SQL​

The Execute BigQuery SQL Agent Tool accepts read-only GoogleSQL beginning with SELECT or WITH:

  • query: Google Standard SQL
  • parameters: optional named parameters referenced as @name
  • useQueryCache: whether BigQuery may reuse cached results
  • maximumBytesBilled: an optional decimal byte limit that causes BigQuery to reject a more expensive query

Example:

SELECT order_id, total
FROM `analytics.sales.orders`
WHERE customer_id = @customerId
ORDER BY created_at DESC
LIMIT 20

For @customerId, pass a named parameter with type STRING.

The tool waits for asynchronous query jobs, follows all result pages, and returns rows as JSON objects. Numeric and temporal values retain BigQuery's lossless string representation.

warning

The tool rejects DML and DDL before calling BigQuery, and the OAuth token is limited to BigQuery read access. Google Cloud IAM still controls which projects, datasets, tables, rows, and columns the connected account may query. Use maximumBytesBilled where query cost needs a hard ceiling.

Manual Verification​

  1. Connect a Google account that can query a small test dataset.
  2. Test the connection and confirm the configured project is named in the success message.
  3. Add List BigQuery Datasets to an agent and confirm it returns only datasets shared with the connected account.
  4. List tables in one returned dataset and inspect one table's schema.
  5. Add Execute BigQuery SQL to the agent and run SELECT 1 AS value.
  6. Run a query with a named STRING parameter and confirm the returned row values.
  7. Run a multi-page query and confirm rowCount matches totalRows.
  8. Set a low maximumBytesBilled value and confirm an over-limit query returns a clear BigQuery error.
  9. Attempt a mutation statement and confirm BotDojo rejects it before calling BigQuery.

Troubleshooting​

  • redirect_uri_mismatch: register the exact environment callback URL on the Google OAuth web client.
  • No refresh token: reconnect and approve consent; BotDojo requests offline access for scheduled loaders.
  • bigquery.jobs.create denied: grant the connected user permission to create jobs in the configured project.
  • No datasets returned: verify the connected Google account has bigquery.datasets.get on at least one dataset in the configured project.
  • Table listing denied: grant roles/bigquery.metadataViewer or another role containing bigquery.tables.list on the dataset.
  • Schema lookup denied: grant a role containing bigquery.tables.get on the table or its dataset.
  • Query access denied: grant roles/bigquery.dataViewer on the queried dataset or table, or connect a different Google account.
  • Location error: set the integration location to the dataset's location.

References​