Skip to content

Targets

BigQuery

Load into BigQuery (beta): rows streamed as Arrow through the Storage Write API into a staging table, then one query job per write.

This target is in beta: it is tested, but gets less everyday use than the other connectors, so expect rough edges, and please report them in GitHub issues.

A BigQuery target loads into BigQuery datasets. SQLLoad streams each batch as Arrow through BigQuery’s Storage Write API into a staging table, then moves the rows into the table with one query job.

SQLLoad signs in with a service account’s key file:

[connections.bq]
kind = "bigquery"
location = "EU"
service_account_key = "${ENV:BIGQUERY_KEY_JSON}"
# project = "my-project"
[targets.bq]
connection = "bq"
loader_schema = "raw"
Key Meaning
location Where query jobs run and datasets are created, such as EU or europe-west2. Required.
service_account_key The key file’s JSON text, not its path. Required.
project The project tables load into and jobs run in. Default: the key’s own project.

The target’s loader_schema is the dataset tables load into; a source’s schema overrides it. sqlload apply creates the dataset when it doesn’t exist.

Pass the key through the environment, so it stays out of sqlload.toml:

export BIGQUERY_KEY_JSON="$(cat loader-key.json)"

SQLLoad signs a token request with the key’s private key and exchanges it for an OAuth token scoped to BigQuery, which it renews shortly before it expires. The service account needs to create datasets and tables and run query jobs in the project: the BigQuery Data Editor and BigQuery Job User roles.

sqlload debug checks the connection by running a query job, and prints the service account, the project and the location jobs run in.

With the quickstart’s Postgres database and its sqlload.toml, using the connection and target above with default_target = "bq":

$ sqlload plan -v
Plan ready 2 selected, 2 objects to create, 2 to load
to BigQuery bq
Objects (2) sqlload apply
├── raw.app__customers create table
│ ├── customer_id int -> INT64
│ ├── email text -> STRING
│ ├── country text -> STRING
│ ├── created_at timestamp_tz -> TIMESTAMP (stored as a UTC instant; the offset is not kept)
│ └── + _sqlload_load_id, _loaded_at
└── raw.app__orders create table
├── order_id int -> INT64
├── customer_id int -> INT64
├── status text -> STRING
├── total decimal(12,2) -> NUMERIC(12,2)
├── updated_at timestamp_tz -> TIMESTAMP (stored as a UTC instant; the offset is not kept)
└── + _sqlload_load_id, _loaded_at
Loads (2) sqlload load
├── raw.app__customers merge (unique_key: customer_id ← {primary_key})
│ ├── source postgres app public.customers
│ └── route read as Arrow -> BigQuery takes Arrow
└── raw.app__orders merge (unique_key: order_id ← {primary_key})
├── source postgres app public.orders
├── route read as Arrow -> BigQuery takes Arrow
└── reads updated_at: first run, everything
✓ Plan complete 2 selected, 2 objects to create, 2 to load
$ sqlload apply
…
created raw.app__customers 6 columns
created raw.app__orders 7 columns
$ sqlload load
…
loaded raw.app__customers 50 rows 0.1s
loaded raw.app__orders 1,000 rows 0.1s
✓ Completed 2 loaded, 1,050 rows, 0.1s
└── slowest raw.app__orders 0.1s
  • Arrow, through the Storage Write API. Each batch is converted to the Arrow types BigQuery reads and sent in append requests of at most 9 MiB, spread over up to four pending streams. A value that doesn’t fit its column fails the batch; it never loads as NULL.
  • All or nothing per batch. Each stream is finalized, its row count is checked against the rows sent, and all of them are committed together. Rows of a stream that is never committed are never visible, so no reader sees part of a batch.
  • Into a staging table, never the table itself. Rows stream into a staging table named for the load, <table>__sqlload_staging_<load id> (a build table for replace). BigQuery refuses streams into a table recreated under the same name for a while afterwards, so SQLLoad never streams into the destination table.
  • Then one query job. A query job moves the rows from the staging table into the table. Each job carries a request ID, so a retried request never runs it twice.

Each statement is its own query job and commits on its own. The run record commits after it.

Strategy Statement
append One INSERT ... SELECT from the staging table.
merge One MERGE on the key.
delete_insert One MERGE that inserts the batch and deletes the rows it replaces.
replace CREATE OR REPLACE TABLE ... LIKE the build table AS SELECT from it, so its columns keep their precision, scale and length, such as NUMERIC(20,2).

replace loads the whole source into a build table, then swaps it in:

└── raw.app__orders replace
├── source postgres app public.orders
├── route read as Arrow -> BigQuery takes Arrow
└── batch about 1 GB at a time into a new table, swapped in once at the end

The new table takes its description from the config, so a description set outside SQLLoad is lost, as are labels, column descriptions and policy tags. BigQuery won’t replace a partitioned or clustered table with one that isn’t, so replace fails on such a table and leaves it as it was. So does a type change that rewrites the table, such as int to float. That rewrite keeps each column’s type, except that a BIGNUMERIC whose digits fit a NUMERIC, such as BIGNUMERIC(20,2), becomes NUMERIC(20,2). In a table with a column whose type SQLLoad can’t name again, a STRUCT, RANGE, GEOGRAPHY, INTERVAL or a BIGNUMERIC of more than 38 digits, the rewrite takes its types from the query, so its columns lose their precision, scale and length. The build table holds only the table’s own columns, so a column a contract or on_schema_change = "ignore" leaves out isn’t loaded.

A load drops its staging and build tables when it ends. When a failed run leaves one behind, the next load into that dataset drops it once it’s over a day old.

Type BigQuery
bool BOOL
smallint INT64 ¹
int INT64
decimal(p,s) NUMERIC(p,s), or BIGNUMERIC(p,s) for more than 29 digits before the point or a scale over 9
float FLOAT64
text STRING
varchar(n) STRING(n)
date DATE
time TIME
timestamp_tz TIMESTAMP ¹
timestamp_ntz DATETIME
json JSON
uuid STRING ¹
binary BYTES

¹ Held with a loss: smallint is stored as a 64-bit integer, uuid as text, and timestamp_tz as a UTC instant without its offset.

  • Records load as JSON, not as typed STRUCT columns. Columns pinned as array(T) load as ARRAY<T>, except array(timestamp_tz).

  • copy_to_internal_stage is for Snowflake and is refused here.

  • A Postgres CDC source can’t load into BigQuery; plan refuses it:

    Errors (1)
    └── public.customers, public.orders
    └── reason kind = "postgres_cdc" loads into DuckDB, Snowflake and Databricks targets, and into files targets as a changelog, in this release; not BigQuery or ClickHouse

A local fake BigQuery (OAuth, query jobs, table metadata and the Storage Write API) covers the protocol and failure cases: wrong offsets, refused appends and commits, and expired tokens.

A live round ran against a real BigQuery project, in one dataset dropped afterwards:

  • Sources and strategies: Postgres, local Parquet files, Python rows, Python Arrow and sl.http JSON pages, each with append, replace, delete_insert and merge where plan accepts it, 19 tables in all, checked against their sources after every round.
  • Rounds: a first load; a re-run; then new and updated rows, an added column, a decimal widened in place from numeric(20,2) to numeric(24,2) holding a 19-digit value, and a new day of files carrying a new column.
  • Incremental reads: cursors on updated_at, with NULL cursors included by merge.
  • Contracts: contract diff, contract diff --source (an unloaded column reported) and contract generate.
  • A failed load: a Python table that raised after two batches committed no rows and left no staging table.
  • Deletes: the key scan and tombstone rounds of deletes and deletes_from, soft and hard, matched on every step, with a key that can’t be NULL and with a text key holding a NULL row.
  • Rebuilds: the statement a type change that rewrites a table renders, such as int to float, kept a NUMERIC(20,2) column as it was, turned a BIGNUMERIC(20,2) made outside SQLLoad into NUMERIC(20,2) with its values, and rewrote a table with a STRUCT column.