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 destination_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.

Parquet files in a gcs bucket can load without passing through SQLLoad: BigQuery queries them where they lie, as temporary external tables, into the staging table, in one query job per batch (several for a batch of over 1,000 files or a very long query). SQLLoad still lists the files, reads their footers and picks the batch; it checks the jobs’ row count against the footers, and a batch that differs fails.

BigQuery doesn’t report a row’s position in its file, so it can’t fill _sqlload_source_row, the row’s number in its file. A table that keeps that column, as every files table does by default, has SQLLoad read the files instead, and sqlload plan -v <table> says why:

├── route read parquet into Arrow -> BigQuery takes Arrow
├── why SQLLoad reads the files because BigQuery doesn't report a row's position in its file, so it can't fill _sqlload_source_row; to have BigQuery load the files itself, add _sqlload_source_row to exclude_columns; the destination takes Arrow directly

To have BigQuery load them, drop the column:

[sources.orders]
kind = "files"
connection = "lake"
path = "orders/"
format = "parquet"
exclude_columns = ["_sqlload_source_row"]
└── route BigQuery reads directly from gs://example-bucket/orders/ (nothing through the loader)

Without the row number, a file’s rows have no order. That matters only to a merge, which doesn’t load files into BigQuery yet.

  • BigQuery reads with its own identity. The query job reads the bucket as the BigQuery connection’s service account, not with the gcs connection’s key. Give that service account roles/storage.objectViewer on the bucket. plan checks it with a dry run over the first file to load, which bills nothing, and fails naming the fix when BigQuery can’t read it.
  • Every value loads as SQLLoad would load it, or SQLLoad reads the files. SQLLoad reads them instead when the table masks columns, when the gcs connection sets a custom endpoint, when a column is a UUID, a list, JSON, a native type, or decimal(20,0) (the type of an unsigned 64-bit integer, which BigQuery reads as a signed one), or when a column’s name is one BigQuery would change: anything other than letters, digits and underscores starting with a letter or underscore, a name starting with a prefix BigQuery reserves such as _FILE_, or two names differing only in case.
  • Files with different columns load together. BigQuery takes one file’s columns for a whole external table, so each set of files with the same columns gets its own, in the same job.

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.

  • BigQuery loads Parquet in GCS itself only on a table that excludes _sqlload_source_row; see Parquet in Google Cloud Storage.

  • 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.
  • Parquet in GCS: files in two partition folders, with different columns, loaded both by BigQuery itself and by SQLLoad’s reader, and the two tables matched value for value: decimals up to 38 digits, timestamps with and without a time zone, dates from year 1, times, floats and 16-bit integers. An unsigned 64-bit value above BigQuery’s INT64 range loaded through SQLLoad’s reader.
  • 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.