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.
Connection
Section titled “Connection”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)"Authentication
Section titled “Authentication”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.
A first load
Section titled “A first load”With the quickstart’s Postgres database and its sqlload.toml, using the
connection and target above with default_target = "bq":
$ sqlload plan -vPlan ready 2 selected, 2 objects to create, 2 to loadto 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 columnscreated raw.app__orders 7 columns
$ sqlload load…loaded raw.app__customers 50 rows 0.1sloaded raw.app__orders 1,000 rows 0.1s
✓ Completed 2 loaded, 1,050 rows, 0.1s└── slowest raw.app__orders 0.1sHow rows arrive
Section titled “How rows arrive”- 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 forreplace). 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 in Google Cloud Storage
Section titled “Parquet in Google Cloud Storage”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 directlyTo 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
gcsconnection’s key. Give that service accountroles/storage.objectVieweron the bucket.planchecks 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
gcsconnection sets a customendpoint, when a column is a UUID, a list, JSON, a native type, ordecimal(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.
Write strategies
Section titled “Write strategies”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 endThe 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.
Staging cleanup
Section titled “Staging cleanup”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.
Limitations
Section titled “Limitations”-
Records load as
JSON, not as typedSTRUCTcolumns. Columns pinned asarray(T)load asARRAY<T>, exceptarray(timestamp_tz). -
copy_to_internal_stageis 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;
planrefuses 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
Tested
Section titled “Tested”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.httpJSON pages, each withappend,replace,delete_insertandmergewhereplanaccepts 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)tonumeric(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 bymerge. - Contracts:
contract diff,contract diff --source(an unloaded column reported) andcontract 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
INT64range loaded through SQLLoad’s reader. - Deletes: the key scan and tombstone rounds of
deletesanddeletes_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
inttofloat, kept aNUMERIC(20,2)column as it was, turned aBIGNUMERIC(20,2)made outside SQLLoad intoNUMERIC(20,2)with its values, and rewrote a table with aSTRUCTcolumn.