Docs
Connections

Google BigQuery

Connect BigQuery with a read-only service account key, read tables at no query cost, and run SELECT queries under a dry-run estimate and a hard cost cap.

A BigQuery connection reads tables and standard-SQL SELECT queries with a service account. Tables are read without a query job, so they cost no query bytes. Every query is checked with a free dry run first and always runs with a hard cap on the bytes it may bill.

Availability

BigQuery is not yet generally available on xentropy.ai. Contact support@xentropy.ai to request access. Until it is enabled for your account, it does not appear in the catalogue and its reads fail with PROVIDER_DISABLED.

Create a service account and key

In the Google Cloud console, open IAM & Admin → Service accounts in the project that should run (and pay for) queries, and create a service account, for example xentropy-reader.
Grant it the roles in the table below: BigQuery Data Viewer on the datasets to read, and BigQuery Job User on the billing project.
Open the service account, choose Keys → Add key → Create new key, choose JSON, and download the key file.

Keep the key file safe. Xentropy encrypts the whole key when you save it and never shows or returns the private key again.

Required roles

RoleGrant itRequired
roles/bigquery.dataViewerOn the datasets to readYes
roles/bigquery.jobUserOn the billing project (queries and dry runs)Yes

Do not grant editor or admin roles. Data Viewer can read but not change data. Xentropy also refuses every statement other than a single SELECT, whatever the roles allow.

Set up the connection

Open Connections, choose New connection, then Google BigQuery.
Drop the JSON key file on the form, or paste its text.
Optionally set the billing project that runs query jobs. It defaults to the key's project.
Set the location where queries run: US, EU or a region such as europe-west3.
Set the cost cap per read. The default is 1 GiB and the maximum is 100 GiB.
Set the time zone that zone-less DATETIME values are written in (default UTC).
Choose Test connection. The stages are auth (the key), permissions (listing datasets) and jobs (a free dry run of SELECT 1 in the location). Then choose Save connection.

By default the connection reads the billing project. Through the API you can list up to 10 projects to read (projects). Up to 500 tables are listed.

BigQuery needs no IP allowlist: Xentropy connects only to Google's API hosts.

Tables

Pick a table in the connection's resources, then its fields. Tables are read with BigQuery's table data API: no query job runs and no query bytes are billed. When a table has not changed since the last capture, the capture is reused.

Views, external tables and tables that require a partition filter cannot use the table data API. Xentropy reads them through a generated query, which has the same dry run and cost cap as your own queries. A table that requires a partition filter needs a partition range.

Queries

Choose BigQuery query in the resources, write one standard-SQL SELECT statement, and reference tables as project.dataset.table.

  1. Choose Estimate. Xentropy runs a free dry run and shows the bytes the query would process next to the cap.
  2. Save as dataset stays disabled while the estimate is over the cap or the statement is not a SELECT.
  3. Every read runs the dry run again before the query. The query job always carries the cap (maximumBytesBilled), so BigQuery fails a job that would bill more, without charging for it.

Scripts, DML (INSERT, UPDATE, DELETE, MERGE), DDL, EXPORT DATA and CALL fail with QUERY_NOT_READ_ONLY. Queries always use standard SQL.

Cost

BigQuery bills your billing project, not Xentropy. A query is billed for at least 10 MiB. Scheduled refreshes run the query again each time, so keep queries selective and the cap low.

Type mapping

BigQuery typeDataset type
INT64number. A value beyond ±2^53 fails with TYPE_MISMATCH: bind the field as a decimal
NUMERIC, BIGNUMERICdecimal (exact, as text)
FLOAT64number. NaN and infinity become empty with a NONFINITE_NULLED warning
BOOLboolean
STRINGstring
DATEdate
DATETIMEdatetime, read in the connection's time zone and returned in UTC
TIMESTAMPdatetime (UTC)
TIME, INTERVAL, GEOGRAPHY, RANGEstring
STRUCTflattened into dotted fields such as address.city
ARRAY, JSONJSON text
BYTESexcluded

Decimal fields

INT64 values above 2^53 cannot be held exactly as a number. If a read meets one, it fails with TYPE_MISMATCH and names the field and row. Choose Bind as decimal for that field in the field picker, or set decimalFields in the dataset's BigQuery resource. The field is then read as an exact decimal.

Limits

LimitValue
Cost cap per readDefault 1 GiB, at least 10 MiB, at most 100 GiB
Rows per read50,000
Fields per dataset100
Pages per readFree 100, Pro 600
Projects read10
Tables listed500
Read deadline30 s interactive, 300 s scheduled

Errors

CodeCauseWhat to do
PROVIDER_ACCESS_DENIED, stage authThe key was rejected, disabled or deletedCreate a new key and replace the configuration
PROVIDER_ACCESS_DENIED, stage permissionsThe service account cannot list or read the datasetsGrant BigQuery Data Viewer
PROVIDER_ACCESS_DENIED, stage jobsThe service account cannot run jobs in the billing projectGrant BigQuery Job User on the billing project
COST_LIMIT_EXCEEDEDThe estimate is over the cap, or BigQuery stopped the job at the cap. Nothing was billedNarrow the query (fewer columns, a partition filter) or raise the cap
QUERY_NOT_READ_ONLYThe SQL is not a single SELECTRewrite it as one SELECT
TYPE_MISMATCHAn INT64 value beyond ±2^53 in a number fieldBind the field as a decimal
RATE_LIMITEDBigQuery's rate limit or quota is used upRetry after details.retryAt
LIMIT_EXCEEDEDThe result is too large, or more than 50,000 rowsSelect fewer columns or rows
PERMISSION_DENIEDThe table is outside the projects the connection readsAdd the project, or choose another table
VALIDATION_FAILEDBigQuery rejected the SQL or a parameter, or the key is not a service account keyFix the SQL or the key

On this page