Skip to content
dbprint

BigQuery

Terminal window
$ pip install 'dbprint[bigquery]'

The extra carries google-cloud-bigquery>=3.27. Absent driver: google-cloud-bigquery is not installed. Install dbprint with the [bigquery] extra: \pip install dbprint[bigquery]`.A failed connection:could not open a BigQuery session for project , dataset : ` (without the dataset when none is configured).

Fully-qualified names are dataset.table.

Credentials

project and dataset are required. There is no password key. Credentials resolve through the client's own Application Default Credentials unless credentials_file names a service-account key, in which case the file loads through google.oauth2.service_account. A relative credentials_file resolves against the project root, and ~ expands. Read-only is the connected principal's own IAM role — the adapter enforces nothing itself. See Configuration.

Privileges

Two roles, not one, because every catalog read is a query job:

roles/bigquery.dataViewer   -- reading structure and computing statistics
roles/bigquery.jobUser      -- required separately; no data role carries bigquery.jobs.create
roles/bigquery.dataEditor   -- only if sampling: the copy is a real table in the profiled dataset

roles/bigquery.dataViewer carries bigquery.tables.getData; roles/bigquery.metadataViewer does not, so a metadata-only principal reads structure and computes no statistics at all. A reader who never samples needs strictly less than one who does — the dataEditor grant is needed only for the CREATE OR REPLACE TABLE / DROP TABLE pair described below.

Cost

INFORMATION_SCHEMA queries carry a 10 MB minimum each, are never cached, and the adapter issues several per table — so a generate run bills a floor well above what the data itself costs. This is the one adapter page that has to say that plainly: every catalog read here is a billed query, not a free metadata lookup.

Sampling

ConstructTABLESAMPLE SYSTEM (p PERCENT)
Seededno — BigQuery's TABLESAMPLE takes no seed at all
looks_like sub-drawcannot be seeded either; orders by the same seeded hash the final list ships under

Because the fraction cannot be seeded, coherence across a sampled table depends entirely on the materialized copy below — like ClickHouse and Redshift, BigQuery refuses a sample-scoped table when the copy is unavailable rather than publish a statistics.yaml whose fields describe different rows.

When dbprint writes — and where the copy lives

Only one thing makes dbprint write, and only under one condition: profile.read.tables.materialize is on by default, and it fires only for a table read in sample mode — by a fraction, or by a max_rows ceiling that resolves below the table's size, at whatever level the ceiling is set. A project that narrows nothing, which is what dbprint init scaffolds, never writes. A filter is a predicate rather than a fraction and never materializes.

BigQuery is the one adapter where the copy is not a session-scoped temporary table — it is a real table in the profiled dataset:

CREATE OR REPLACE TABLE <dataset>.dbprint_sample_<...>
OPTIONS(expiration_timestamp = TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 6 HOUR))
AS SELECT * FROM (SELECT * FROM <table> TABLESAMPLE SYSTEM (p PERCENT)) src

The profiling role needs create rights in that dataset (roles/bigquery.dataEditor above), and CREATE OR REPLACE means an orphaned copy from an earlier interrupted run cannot wedge a later one. The scratch prefix is excluded from enumeration, so a copy is never profiled as a table of its own. Release is DROP TABLE IF EXISTS; when the drop itself fails, the run warns that the copy outlives this run and is not session-scoped — the 6-hour expiration is what bounds its storage in that case, not any cleanup dbprint performs.

read.tables.materialize: false never falls back on BigQuery: an unseeded TABLESAMPLE never reads the same rows twice, so a sample-scoped table with the write disabled is refused before any statement runs, the same as on ClickHouse or Redshift.

Identifiers

BigQuery is case-sensitive while the format addresses objects by lowercased paths, so dbprint records each table's physical dataset and table name, and each column's physical spelling, when it lists the dataset, and addresses the table only by those — never by a lowercased name the catalog does not hold. Dataset and table names are folded for the artifact path; the physical spelling is kept for every query. The rule check refuses a case collision the same way as every other lowercasing adapter, at table grain and, for two columns folding to one key, at column grain (SPEC 1.5.2).

Table kinds

A table clone is profiled as a table; the print names no base table. An external table is listed with external: true (SPEC 2.2.20) and a snapshot as a plain table; unless a matching rule writes its read.tables.mode, it is described from INFORMATION_SCHEMA alone. A snapshot is skipped by default for cost: its rows never change, so profiling it would bill a full read of a past state on every run. Other table types are not listed.

Read that way, either is profiled like a table. An external table bills every statement for the bytes it reads, results from sources other than Cloud Storage are not cached, and a dry run may report 0 bytes; mode: sample is the cheapest way to profile one, since TABLESAMPLE reads a subset of its files.

Tables that require a partition filter

A partitioned table created with OPTIONS (require_partition_filter = TRUE) refuses every query without a predicate on its partitioning column. dbprint reads the option while listing and describes such a table from INFORMATION_SCHEMA alone unless a rule reads it in filter mode, warning on every run with the rule to add. The filter must hold a predicate on the partitioning column alone — _PARTITIONTIME or _PARTITIONDATE on an ingestion-time partitioned table — which is what BigQuery requires. Under it, row_count comes from INFORMATION_SCHEMA.PARTITIONS, where no partition row means no rows, and the table carries no sketch. A view or materialized view reading such a table inherits the requirement and is not detected, so read it in catalog mode or with a filter.

Namespaces

project stays required: it is the billing and authentication context. dataset is optional; omitted, the connection lists every dataset in the project through datasets.list — not a billed query, filtered to the datasets the caller holds bigquery.datasets.get on, already part of dataViewer and metadataViewer, and leaving hidden datasets out — then reads each one's INFORMATION_SCHEMA.TABLES. Each of those is billed at the on-demand minimum, so listing cost scales with the dataset count. Every per-table read, and a sampled table's copy, uses the table's own dataset. Two datasets differing only in case that hold the same table name collide, as any case collision does. A dataset whose listing fails is skipped with a warning.

Column types

Listed values. An INTERVAL or RANGE column lists its values as BigQuery's CAST(... AS STRING) text (0-1 2 3:4:5, [2024-01-01, 2024-02-01)), the text length is measured over.

TypeProfiled asWhat to know
ARRAY<T>composite—
STRUCT<...>composite—
JSONjsontypes keys are what JSON_TYPE returns
GEOGRAPHYspatialAlways SRID 4326 and never invalid, so invalid_count is absent; an empty value's kind is geometrycollection, and an extent crossing the antimeridian may hold a bound outside [-180, 180]

What it cannot deliver

  • indexes — always empty. Search and vector indexes are neither secondary indexes nor a SQL join target.
  • depends_on — always absent on every view. There is no view-dependency catalog, only VIEWS.view_definition, so the field is omitted rather than guessed from DDL text.
  • unique_keys beyond the primary key — BigQuery has no UNIQUE constraint type.
  • Column default — always null. column_default is absent from INFORMATION_SCHEMA.COLUMNS.
  • Foreign key on_delete / on_update — always NO ACTION. enforced is documented "Only NO".

The row-count estimate

A rule carrying min_rows, and any max_rows ceiling, needs to know how big a table is before profiling it. That size comes from INFORMATION_SCHEMA.PARTITIONS, summed over the table's partitions — an unpartitioned table is a single row keyed NULL. It is dataset-qualified, which BigQuery documents for that view, and it needs bigquery.tables.get and bigquery.tables.list, both already carried by the roles/bigquery.dataViewer above.

Unlike the three reads below, this one does not degrade: a refused or unavailable read fails that table and names estimate_row_count as the operation, rather than answering "no estimate". The two are not the same fact — an absent estimate is something the catalog said about the table, while a missing grant would otherwise leave every size-conditioned rule declining in silence, on every table, with nothing in the output saying so.

Three catalog reads degrade rather than fail when the connection cannot see a column: TABLES.ddl, COLUMNS.collation_name, and COLUMNS.clustering_ordinal_position. Losing the third means clustering is not detected, and a clustered table may publish partition instead — worth knowing if physical layout looks wrong on a table you know is clustered. COLUMN_FIELD_PATHS degrades the same way, leaving every column comment absent.

Statement timeout

run.statement_timeout becomes every query job's jobTimeoutMs. BigQuery stops a job that exceeds it on a best-effort basis, and one it fails to stop keeps the run waiting.

Parallelism

run.parallelism: N runs up to N tables' jobs at once, each table on one client throughout. Concurrent query jobs count against the project's interactive-query quota and, on capacity pricing, share the reservation's slots.

Reference