Skip to main content

BigQuery

BigQuery fits best with a Google Cloud Storage destination. The objects follow Delivery format. You can query the delivered objects in place through an external table, or load them into a native table on a schedule. Native tables are faster and cheaper to query again and again; the external table always shows the latest deliveries.

note

This page follows Google's BigQuery documentation. We have not run these statements against BigQuery ourselves; if one fails for you, tell us and we will fix the page.

Query objects in place​

Your events can change shape from day to day, so the external table reads each line as one string and parses it at query time. The CSV reader does this when quoting is off and the delimiter is a byte that never appears in Send2Bucket's output:

CREATE SCHEMA IF NOT EXISTS s2b;

CREATE OR REPLACE EXTERNAL TABLE s2b.raw_lines (line STRING)
WITH PARTITION COLUMNS (stream STRING, date DATE, hour INT64)
OPTIONS (
format = 'CSV',
field_delimiter = '\x01',
quote = '',
compression = 'GZIP',
uris = ['gs://<bucket>/<prefix>/s2b/stream=*'],
hive_partition_uri_prefix = 'gs://<bucket>/<prefix>/s2b',
require_hive_partition_filter = false
);
  • stream=* keeps the destination's test objects under s2b/_verify/ out of the table.
  • Send2Bucket escapes control characters inside JSON strings, so byte 0x01 never occurs in a line.
  • Gzip objects are read one per worker, so a query over many hours of objects is slower than over Parquet. Filter on date and hour: BigQuery reads only the matching folders.

A view turns the line into the same columns the other engine pages use:

CREATE OR REPLACE VIEW s2b.records AS
SELECT stream, date, hour,
JSON_VALUE(line, '$.signal') AS signal,
TIMESTAMP(JSON_VALUE(line, '$.received_at')) AS received_at,
JSON_VALUE(line, '$.delivery_batch_id') AS delivery_batch_id,
SAFE.PARSE_JSON(JSON_QUERY(line, '$.resource')) AS resource,
SAFE.PARSE_JSON(JSON_QUERY(line, '$.data')) AS data
FROM s2b.raw_lines;

Load into a native table​

Load each finished day once, for example with a scheduled query that runs after midnight UTC for the previous day:

CREATE TABLE IF NOT EXISTS s2b.records_native (
v INT64, delivery_batch_id STRING, batch_id STRING, received_at TIMESTAMP,
signal STRING, resource JSON, scope JSON, data JSON)
PARTITION BY DATE(received_at)
CLUSTER BY signal;

LOAD DATA INTO s2b.records_native
FROM FILES (
format = 'JSON',
uris = ['gs://<bucket>/<prefix>/s2b/stream=<stream_id>/date=2026-11-18/*']
);

LOAD DATA decompresses gzip on its own. Loading the same files twice loads the records twice; if you need to rerun a day, delete that day's partition first, or load into a staging table and insert only the rows whose delivery_batch_id is not in records_native yet.

Example queries​

These run against the s2b.records view, or s2b.records_native with the same column names.

Records per signal:

SELECT signal, COUNT(*) AS records
FROM s2b.records
WHERE date = '2026-11-19'
GROUP BY signal
ORDER BY records DESC;

JSON events by name, with revenue from order.paid:

SELECT JSON_VALUE(data, '$.event') AS event,
COUNT(*) AS events,
SUM(CAST(JSON_VALUE(data, '$.cents') AS INT64)) / 100 AS revenue_usd
FROM s2b.records
WHERE signal = 'event'
GROUP BY event
ORDER BY events DESC;

Log records by service and severity:

SELECT (SELECT JSON_VALUE(a, '$.value.stringValue')
FROM UNNEST(JSON_QUERY_ARRAY(resource, '$.attributes')) AS a
WHERE JSON_VALUE(a, '$.key') = 'service.name') AS service,
JSON_VALUE(data, '$.severityText') AS severity,
COUNT(*) AS logs
FROM s2b.records
WHERE signal = 'log'
GROUP BY service, severity;

The five slowest spans:

SELECT JSON_VALUE(data, '$.name') AS span,
JSON_VALUE(data, '$.traceId') AS trace_id,
(CAST(JSON_VALUE(data, '$.endTimeUnixNano') AS INT64)
- CAST(JSON_VALUE(data, '$.startTimeUnixNano') AS INT64)) / 1e6 AS duration_ms
FROM s2b.records
WHERE signal = 'span'
ORDER BY duration_ms DESC
LIMIT 5;

Other destinations​

  • Amazon S3: BigQuery Omni can create the same external table over S3 through an AWS connection (WITH CONNECTION), in Omni regions only. Otherwise copy objects to Cloud Storage with Storage Transfer Service and use the steps above.
  • Apache Iceberg: BigQuery reads an Iceberg table in Cloud Storage as a BigLake external table created from one metadata file. It does not follow new commits on its own, so the table lags behind Send2Bucket's appends until you point it at a newer metadata file. For BigQuery, a Cloud Storage bucket destination is simpler.

Notes​

  • External table queries are billed on the bytes BigQuery reads from Cloud Storage, which for gzip is the decompressed size of every object in the folders you select. Filter on date and hour.
  • A native table loaded from these objects is cheaper and faster for repeated dashboards. Partition it on received_at and cluster it on signal.
  • JSON_VALUE returns NULL for a missing field instead of failing, so a typo in a path returns empty results rather than an error.
  • See the querying overview for other engines.