Introduction to continuous queries

This document describes BigQuery continuous queries.

BigQuery continuous queries are SQL statements that runcontinuously. Continuous queries let you analyze incoming data inBigQuery in real time. You can insert the output rows producedby a continuous query into a BigQuery table or export them toPub/Sub, Bigtable, or Spanner. Continuousqueries can process data that has been written tostandard BigQuery tablesby using one of the following methods:

• TheBigQuery Storage Write API
• Thetabledata.insertAll method
• Batch load
• TheINSERT DML statement
• Writes from theresults of a batch query to a permanent table
• Writes from theresults of a BigQuery continuous query to a permanent table
• APub/Sub BigQuery subscription
• Writes fromDataflow to BigQuery

You can use continuous queries to perform time sensitive tasks, such ascreating and immediately acting on insights, applying real time machinelearning (ML) inference, and replicating data into other platforms. Thislets you use BigQuery as an event-driven data processingengine for your application's decision logic.

The following diagram shows common continuous query workflows:

Use cases

Common use cases where you might want to use continuous queries are as follows:

• Personalized customer interaction services: use generative AI to createtailored messages customized for each customer interaction.
• Anomaly detection: build solutions that let you perform anomaly and threatdetection on complex data in real time, so that you can react to issues morequickly.
• Customizable event-driven pipelines: use continuous query integration withPub/Sub to trigger downstream applications based on incomingdata.
• Data enrichment and entity extraction: use continuous queries to performreal time data enrichment and transformation by using SQL functions andML models.
• Reverse extract-transform-load (ETL): perform real time reverse ETL into otherstorage systems more suited for low latency application serving. For example,analyzing or enhancing event data that is written to BigQuery,and then streaming it to Bigtable or Spanner for applicationserving.

Supported operations

The following operations are supported in continuous queries:

• RunningINSERT statementsto write data from a continuous query into a BigQuery table.

• RunningEXPORT DATA statementstopublishcontinuous query output to Pub/Sub topics. Formore information, seeExport data to Pub/Sub.

  From a Pub/Sub topic, you can use the data with otherservices, such as performing streaming analytics by using Dataflow,or using the data in an application integration workflow.

• RunningEXPORT DATA statements to export data from BigQuerytoBigtable tables.For more information, seeExport data to Bigtable.

• RunningEXPORT DATA statements to export data from BigQueryto Spanner tables. For more information, seeExport data to Spanner (reverse ETL).

• Calling the following generative AI function:

  • AI.GENERATE_TEXT

  This function requires you to have aBigQuery ML remote modelover aVertex AI model.

• Calling the following AI functions:

  • ML.UNDERSTAND_TEXT
  • ML.TRANSLATE

  These functions require you to have aBigQuery ML remote modelover a Cloud AI API.

• Normalizing numerical data by using theML.NORMALIZER function.

• Using stateless GoogleSQL functions—for example,conversion functions.In stateless functions, each row is processed independently from otherrows in the table.

• Using theAPPENDSchange history function to start continuous query processing from aspecific point in time.

Authorization

TheGoogle Cloud access tokensthat are used when running continuous query jobs have a time to live (TTL) oftwo days when they are generated by a user account. Therefore, such jobs stoprunning after two days. The access tokens that are generated by serviceaccounts can run longer, but must still adhere to the maximum query runtime.For more information, seeRun a continuous query by using a service account.

Locations

Continuous queries are supported in the followinglocations:

	Region description	Region name	Details
Americas
	US multi-region	us
	Columbus	us-east5
	Dallas	us-south1	Low CO2
	Iowa	us-central1	Low CO2
	Las Vegas	us-west4
	Los Angeles	us-west2
	Mexico	northamerica-south1
	Montréal	northamerica-northeast1	Low CO2
	Northern Virginia	us-east4
	Oregon	us-west1	Low CO2
	Salt Lake City	us-west3
	Santiago	southamerica-west1	Low CO2
	São Paulo	southamerica-east1	Low CO2
	South Carolina	us-east1
	Toronto	northamerica-northeast2	Low CO2
Asia Pacific
	Delhi	asia-south2
	Hong Kong	asia-east2
	Jakarta	asia-southeast2
	Melbourne	australia-southeast2
	Mumbai	asia-south1
	Osaka	asia-northeast2
	Seoul	asia-northeast3
	Singapore	asia-southeast1
	Sydney	australia-southeast1
	Taiwan	asia-east1
	Tokyo	asia-northeast1
Europe
	EU multi-region	eu
	Belgium	europe-west1	Low CO2
	Berlin	europe-west10
	Finland	europe-north1	Low CO2
	Frankfurt	europe-west3
	London	europe-west2	Low CO2
	Madrid	europe-southwest1	Low CO2
	Milan	europe-west8
	Netherlands	europe-west4	Low CO2
	Paris	europe-west9	Low CO2
	Stockholm	europe-north2	Low CO2
	Turin	europe-west12
	Warsaw	europe-central2
	Zurich	europe-west6	Low CO2
Middle East
	Doha	me-central1
	Dammam	me-central2
	Tel Aviv	me-west1
Africa
	Johannesburg	africa-south1

Limitations

Continuous queries are subject to the following limitations:

• BigQuery continuous queries don't maintain the state ofingested data. Common operations that rely on state, such as aJOIN,aggregation function, or window function, aren'tsupported.

• You can't use the following SQL capabilities in a continuous query:

  • JOIN operations
  • Aggregate functions
  • Approximate aggregate functions

  • The followingquery clauses:

    • GROUP BY
    • HAVING
    • ORDER BY
    • LIMIT

  • The followingquery operators:

    • PIVOT
    • UNPIVOT
    • TABLESAMPLE

  • Queryset operators

  • TheSELECT DISTINCT statement

  • EXISTS orNOT EXISTS subqueries

  • Recursive CTEs

  • User-defined functions

  • Windowing functions

  • BigQuery ML functions other than those listed inSupported operations

  • Data definition language (DDL) statements

  • Data manipulation language (DML) statementsexcept forINSERT.

  • Data control language (DCL) statements

  • EXPORT DATA statements that don't target Bigtable,Pub/Sub, or Spanner.

  • Procedural language

  • Debugging statements

• Continuous queries don't support the following data sources:

  • External tables.
  • Information schema views.
  • BigLake tables for Apache Iceberg in BigQuery.
  • Wildcard tables.
  • Change Data Capture (CDC) upsertdata.
  • Materialized views.
  • Views that are defined by other continuous querylimitations, such asJOIN operations, aggregate functions, change datacapture enabled tables.

• Continuous queries don't support thecolumn-androw-level security features.

• Whenexporting data to Bigtable,you can only targetBigtable instances that fall within the sameGoogle Cloud regional boundary as the BigQuerydataset that contains the table you are querying. For more information, seeLocation considerations.This restriction doesn't apply to exporting data to Pub/Subbecause Pub/Sub is a global resource.

• When exporting data to Bigtable, Spanner, orPub/Sub locationalendpointsyou can only target Bigtable, Spanner, orPub/Sub resources that fall within the same Google Cloudregional boundary as the BigQuery dataset that contains thetable you are querying. This restriction doesn't apply when exporting datato Pub/Sub global endpoints.

• You can't run a continuous query from adata canvas.

• You can't modify the SQL used in a continuous query while the continuousquery job is running. For more information, seeModify the SQL of a continuous query.

• If a continuous query job falls behind in processing incoming data and hasanoutput watermark lag ofmore than 48 hours, then it fails. You can run the query againand use theAPPENDSchange history function to resume processing from the point in time at whichyou stopped the previous continuous query job. For more information, seeStart a continuous query from a particular point in time.

• A continuous query configured with a user account can run for up to twodays. A continuous query configured with a service account can run forup to 150 days. When the maximum query runtime is reached, the query failsand stops processing incoming data.

• Although continuous queries are built usingBigQueryreliability features, occasionaltemporary issues can occur. Issues might lead to some amount of automaticreprocessing of your continuous query, which could result in duplicatedata in the continuous query output. Design your downstream systems tohandle such scenarios.

Reservation limitations

• You must create Enterprise edition or Enterprise Pluseditionreservations in order to runcontinuous queries. Continuous queries don't support the on-demand computebilling model.
• When you create aCONTINUOUSreservation assignment,the associated reservation is limited to at most500 slots. You can request an increase to this limit by contactingbq-continuous-queries-feedback@google.com.
• You can't create a reservation assignment that uses a differentjob type in thesame reservation as a continuous query reservation assignment.
• You can't configure continuous query concurrency. BigQueryautomatically determines the number of continuous queries that can runconcurrently, based on available reservation assignments that use theCONTINUOUS job type.
• When running multiple continuous queries using the same reservation,individual jobs might not split available resources fairly, asdefined byBigQuery fairness.

Slots autoscaling

Continuous queries can useslot autoscaling to dynamically scaleallocated capacity to accommodate your workload. As your continuous queriesworkload increases or decreases, BigQuery dynamically adjustsyour slots.

After a continuous query starts running, it activelylistens for incoming data,which consumes slot resources. While a reservation with a running continuousquery does not scale down to zero slots, an idle continuous query that isprimarily listening for incoming data is expected to consume a minimal amount ofslots, typically around 1 slot.

Idle slot sharing

Continuous queries can useidle slot sharingto share unused slot resources with other reservations andjob types.

• ACONTINUOUSreservation assignment is stillrequired to run a continuous query and can't solely rely on idle slotsfrom other reservations. Thus aCONTINUOUS reservation assignment requireseither a non-zero slot baseline or a non-zero slot autoscaling configuration.
• Only idle baseline slots or committed slots from aCONTINUOUS reservationassignment are sharable.Autoscaled slots aren'tshareable as idle slots for other reservations.

Pricing

Continuous queries useBigQuery capacity compute pricing,which is measured inslots.To run continuous queries, you must have areservation that uses theEnterprise or Enterprise Plus edition,and areservation assignmentthat uses theCONTINUOUS job type.

Usage of other BigQuery resources, such as data ingestion andstorage, are charged at the rates shown inBigQuery pricing.

Usage of other services that receive continuous query results or that are calledduring continuous query processing are charged at the rates published for thoseservices. For the pricing of other Google Cloud services used by continuousqueries, see the following topics:

• Bigtable pricing
• Pub/Sub pricing
• Spanner pricing
• Vertex AI pricing

What's next

Trycreating a continuous query.