Continuous queries in BigQuery

A continuous query processes rows as they land in a table and writes results to another table, Pub/Sub, Bigtable or Spanner. It needs an Enterprise reservation, supports a subset of SQL, can emit duplicates, and stops after 2 days on a user account or 150 on a service account.

A normal query reads a table once and stops. A continuous query keeps running: as new rows are written to its source table, it processes them and sends the results on within seconds. It turns BigQuery into an event processor: enrich incoming events with SQL or a model, filter for alerts, and push the results to Pub/Sub, Bigtable, Spanner or another table, all without a separate streaming system.

What it looks like

A continuous query is an INSERT ... SELECT or an EXPORT DATA statement, started with the continuous flag:

EXPORT DATA OPTIONS (
  format = 'CLOUD_PUBSUB',
  uri = 'https://pubsub.googleapis.com/projects/myproject/topics/large-orders'
) AS
SELECT TO_JSON_STRING(STRUCT(order_id, customer_id, amount)) AS message
FROM APPENDS(TABLE mydataset.orders, CURRENT_TIMESTAMP() - INTERVAL 10 MINUTE)
WHERE amount > 1000;

bq query --use_legacy_sql=false --continuous=true \
  --connection_property=service_account=pipeline@myproject.iam.gserviceaccount.com \
  "$(cat large_orders.sql)"

In the console, switch the query editor to continuous query mode. APPENDS with a start time tells the query where to begin. The start must be within the table’s time travel window (seven days by default), and you leave out APPENDS’ end time so the query never stops. Rows are picked up however they arrive: streaming, load jobs, INSERT statements or other continuous queries.

What you can write to

• Another BigQuery table, with INSERT INTO ... SELECT.
• Pub/Sub, Bigtable or Spanner, with EXPORT DATA. Exporting to Pub/Sub requires running the query as a service account.

Anything else, like Cloud Storage files or MERGE into a table, isn’t supported.

What SQL is allowed

Continuous queries support filters, projections, stateless functions (casts, string, date and JSON functions), UNNEST, and a few generative AI and ML functions such as AI.GENERATE, ML.UNDERSTAND_TEXT and ML.TRANSLATE. Joins, aggregations over time windows and UNION ALL are supported as stateful operations, in Preview.

Not allowed:

• DISTINCT, EXISTS, set operators other than UNION ALL, PIVOT, UNPIVOT, TABLESAMPLE, recursive CTEs, window function calls and user-defined functions
• DML other than INSERT, DDL, scripts
• Reading external tables, wildcard tables, INFORMATION_SCHEMA, materialized views, CDC upsert tables, or tables with row- or column-level security

You can’t change the SQL of a running continuous query. Stop it and start a new one, from the point the old one stopped (use APPENDS with that time).

Reservations and cost

Continuous queries don’t run on on-demand pricing. They need an Enterprise or Enterprise Plus reservation with an assignment of job type CONTINUOUS:

• that reservation can’t hold assignments of other job types,
• it is limited to 500 slots unless Google raises it,
• BigQuery admits a new continuous query only if 10 slots are available for it, even though an idle query uses about 1 slot,
• slot autoscaling is supported, and an idle continuous query keeps the reservation from scaling to zero.

So the cost is the slot time of the reservation for as long as the queries run, plus the destination’s own costs (Pub/Sub messages, Bigtable or Spanner writes). See On-demand vs capacity pricing.

How long it runs

• A continuous query started with your user account stops after 2 days, when its access token expires. Use a service account for anything long-lived.
• With a service account it can run for up to 150 days, then fails. Plan to restart it, starting from the last processed time.
• If it falls more than 48 hours behind the incoming data, it fails.

Duplicates

Google states that temporary issues can make a continuous query reprocess some data, so its output can contain duplicates. Make the receiver idempotent: include an event ID in each message and deduplicate on it, or write to a table and deduplicate when you read.

Continuous query, scheduled query or materialized view