Work with multi-statement queries

Amulti-statement query is a collection of SQL statements that you can executein a sequence, with shared state.

This document describes how to use multi-statement queries inBigQuery, such as how to write multi-statement queries, usetemporary tables in multi-statement queries, reference variables inmulti-statement queries, and debug multi-statement queries.

Multi-statement queries are often used instored procedures and supportprocedural language statements,which let you do things like define variables and implement control flow.Multi-statement queries can contain DDL and DML statements that have sideeffects, such as creating or modifying tables or table data.

Write, run, and save multi-statement queries

A multi-statement query consists of one or more SQL statementsseparated by semicolons. Any valid SQL statement can be used in amulti-statement query. Multi-statement queries can also includeprocedural language statements,which let you use variables or implement control flow with your SQL statements.

Write a multi-statement query

You can write a multi-statement query in BigQuery. The followingmulti-query statement query declares a variable and uses thevariable inside anIFstatement:

DECLAREdayINT64;SETday=(SELECTEXTRACT(DAYOFWEEKfromCURRENT_DATE));ifday=1orday=7THENSELECT'Weekend';ELSESELECT'Weekday';ENDIF

BigQuery interprets any request with multiple statements as amulti-statement query, unless the statements consist entirely ofCREATE TEMP FUNCTION statements followed by a singleSELECT statement.For example, the following is not considered a multi-statement query:

CREATETEMPFUNCTIONAdd(xINT64,yINT64)AS(x+y);SELECTAdd(3,4);

Run a multi-statement query

You can run a multi-statement query in the same way as any other query,for example, in the Google Cloud console or using the bq command-line tool.

Dry-run a multi-statement query

To estimate the number of bytes read by a multi-statement query, consider adry run. A dry run of amulti-statement query is most accurate for queries that only containSELECTstatements.

Dry runs have special handling for the following query and statement types:

• CALL statements: the dry run validates that the called procedure exists andhas a signature matching the arguments provided. The content of the calledprocedure and all statements after theCALL statement are not validated.
• DDL statements:the dry run validates the first DDL statement and thenstops. All subsequent statements are skipped. Dry runs ofCREATE TEMP TABLE statements aren't supported.
• DML statements:the dry run validates the DML statement and then continues to validatesubsequent statements. In this case, byte estimates are based on originaltable sizes, and don't take into account the outcome of the DML statement.
• EXECUTE IMMEDIATE statements: the dry run validates the query expression,but does not evaluate the dynamic query itself. All statements following theEXECUTE IMMEDIATE statement are skipped.
• Queries that use variables in a partition filter: the dry run validates theinitial query and subsequent statements. However, the dry run is unable tocalculate the runtime value of variables in a partition filter. This affectsthe bytes read estimate.
• Queries that use variables in the timestamp expression of aFOR SYSTEM TIME AS OF clause: the dry run uses the table's current content andignores theFOR SYSTEM TIME AS OF clause. This affects the bytes readestimate if there are size differences between the current table and the prioriteration of the table.
• FOR,IF andWHILE control statements: the dry run stops immediately.Condition expressions, bodies of the control statement, and all subsequentstatements are not validated.

Dry runs operate on a best-effort basis, and the underlying process is subjectto change. Dry runs are subject to the following stipulations:

• A query that successfully completes a dry run might not execute successfully.For example, queries might fail at runtime due to reasons that are notdetected by dry runs.
• A query that successfully executes might not complete a dry run successfully.For example, queries might fail dry runs due to reasons caught at execution.
• Dry runs that successfully run today are not guaranteed to always run in thefuture. For example, changes to the dry run implementation might detect errorsin a query that were previously undetected.

Save a multi-statement query

To save a multi-statement query, seeWork with saved queries.

Use variables in a multi-statement query

A multi-statement query can containuser-created variablesandsystem variables.

• You can declare user-created variables, assign values tothem, and reference them throughout the query.

• You can reference system variables in a query and assignvalues to some of them, but unlike user-defined variables, you don't declarethem. System variables are built into BigQuery.

Declare a user-created variable

You must declare user-created variables either at the start of themulti-statement query or at the start ofaBEGINblock. Variables declared at the start of the multi-statement query are in scopefor the entire query. Variables declared inside aBEGIN block have scope forthe block. They go out of scope after the correspondingEND statement. Themaximum size of a variable is 1 MB, and the maximum size of all variables usedin a multi-statement query is 10 MB.

You can declare a variable with theDECLAREprocedural statement like this:

DECLARExINT64;BEGINDECLAREyINT64;-- Here you can reference x and yEND;-- Here you can reference x, but not y

Set a user-created variable

After you declare a user-created variable, you can assign a value to it with theSETprocedural statement like this:

DECLARExINT64DEFAULT0;SETx=10;

Set a system variable

You don't create system variables, but you can overridethe default value for some of them like this:

SET@@dataset_project_id='MyProject';

You can also set and implicitly use a system variable in a multi-statementquery. For example, in the following query you must include the project eachtime you wish to create a new table:

BEGINCREATETABLEMyProject.MyDataset.MyTempTableA(idSTRING);CREATETABLEMyProject.MyDataset.MyTempTableB(idSTRING);END;

If you don't want to add the project to table paths multiple times, you canassign the dataset project IDMyProject to the@@dataset_project_id systemvariable in the multi-statement query. This assignment makesMyProjectthe default project for the rest of the query.

SET@@dataset_project_id='MyProject';BEGINCREATETABLEMyDataset.MyTempTableA(idSTRING);CREATETABLEMyDataset.MyTempTableB(idSTRING);END;

Similarly, you can set the@@dataset_id system variable to assign a defaultdataset for the query. For example:

SET@@dataset_project_id='MyProject';SET@@dataset_id='MyDataset';BEGINCREATETABLEMyTempTableA(idSTRING);CREATETABLEMyTempTableB(idSTRING);END;

You can also explicitly reference system variables like@@dataset_id inmany parts of a multi-statement query. To learn more, see thesystem variable examples.

Reference a user-created variable

After you have declared and set a user-created variable, you can reference it ina multi-statement query. If a variable and column share the same name, thecolumn takes precedence.

This returnscolumn x +column x:

DECLARExINT64DEFAULT0;SETx=10;WITHNumbersAS(SELECT50ASx)SELECT(x+x)ASresultFROMNumbers;

+--------+| result |+--------+| 100    |+--------+

This returnscolumn y +variable x:

DECLARExINT64DEFAULT0;SETx=10;WITHNumbersAS(SELECT50ASy)SELECT(y+x)ASresultFROMNumbers;

+--------+| result |+--------+| 60     |+--------+

Use temporary tables in a multi-statement query

Temporary tables let you save intermediate results to a table. Temporary tables are managed by BigQuery, so you don't need to save or maintain them in a dataset. You are charged for storage of temporary tables.

You can create and reference a temporary table in a multi-statement query. When you are finished with the temporary table, you can delete it manually tominimize storage costs, or wait for BigQuery to delete it after24 hours.

Create a temporary table

You can create a temporary table for a multi-statement query with theCREATE TABLE statement.The following example creates a temporary table to store the results of a queryand uses the temporary table in a subquery:

-- Find the top 100 names from the year 2017.CREATETEMPTABLEtop_names(nameSTRING)ASSELECTnameFROM`bigquery-public-data`.usa_names.usa_1910_currentWHEREyear=2017ORDERBYnumberDESCLIMIT100;-- Which names appear as words in Shakespeare's plays?SELECTnameASshakespeare_nameFROMtop_namesWHEREnameIN(SELECTwordFROM`bigquery-public-data`.samples.shakespeare);

Other than the use ofTEMP orTEMPORARY, the syntax is identical to theCREATE TABLE syntax.

When you create a temporary table, don't use a project or dataset qualifier inthe table name. The table is automatically created in a special dataset.

Reference a temporary table

You can refer to a temporary table by name for the duration of the currentmulti-statement query. This includes temporary tables created by a procedurewithin the multi-statement query. You cannot share temporary tables. Temporarytables reside in hidden_script% datasets with randomly generated names.Listing datasets article describes how to list hidden datasets.

Delete temporary tables

You can delete a temporary table explicitly before the multi-statement querycompletes by using theDROP TABLE statement:

CREATETEMPTABLEtable1(xINT64);SELECT*FROMtable1;-- SucceedsDROPTABLEtable1;SELECT*FROMtable1;-- Results in an error

After a multi-statement query finishes, the temporary table exists for up to24 hours.

View temporary table data

After you create a temporary table, you can view the structure of thetable and any data in it. To view the table structure and data, followthese steps:

1. In the Google Cloud console, open theBigQuery page.

   Go to BigQuery

2. In the left pane, clickexploreExplorer:

   

   If you don't see the left pane, clicklast_pageExpand left pane to open the pane.

3. In theExplorer pane, clickJob history.

4. In thePersonal history orProject history tab, click the query thatcreated the temporary table.

5. In theDestination table row, clickTemporary table.

   Note: The name of the temporary table used in the multi-statement query isnot preserved. In the Google Cloud console, it has a random name.

Qualify temporary tables with_SESSION

When temporary tables are used together with a default dataset, unqualifiedtable names refer to a temporary table if one exists, or a table in the defaultdataset. The exception is forCREATE TABLE statements, where the target tableis considered a temporary table if and only if theTEMP orTEMPORARY keywordis present.

For example, consider the following multi-statement query:

-- Create table t1 in the default datasetCREATETABLEt1(xINT64);-- Create temporary table t1.CREATETEMPTABLEt1(xINT64);-- This statement selects from the temporary table.SELECT*FROMt1;-- Drop the temporary tableDROPTABLEt1;-- Now that the temporary table is dropped, this statement selects from the-- table in the default dataset.SELECT*FROMt1;

-- Create a temp tableCREATETEMPTABLEt1(xINT64);-- Create a temp table using the `_SESSION` qualifierCREATETEMPTABLE_SESSION.t2(xINT64);-- Select from a temporary table using the `_SESSION` qualifierSELECT*FROM_SESSION.t1;

CREATETABLE_SESSION.t4(xINT64);-- Fails

Thejobs.getQueryResultsmethod returns the query results for the last statement to execute in themulti-statement query. If no statement was executed, no results arereturned.

Return all executed statements

To get the results of all statements in amulti-statement query,enumerate the child jobsand calljobs.getQueryResultson each of them.

Enumerate child jobs

Multi-statement queries are executed in BigQuery usingjobs.insert,similar to any other query, with the multi-statement queries specified as thequery text. When a multi-statement query runs, additional jobs, known aschild jobs, are created for each statement in the multi-statement query. Youcan enumerate the child jobs of a multi-statement query by callingjobs.list, passing in themulti-statement query job ID as theparentJobId parameter.

Debug a multi-statement-query

Here are some tips for debugging multi-statement queries:

• Use theASSERTstatement to assert that a Boolean condition is true.

• UseBEGIN...EXCEPTION...ENDto catch errors and display the error message and stack trace.

• UseSELECT FORMAT("....") to show intermediate results.

• When you run a multi-statement query in the Google Cloud console, you can view theoutput of each statement in the multi-statement query. The bq command-line tool'sbq query command also shows the results of each step when you run amulti-statement query.

• In the Google Cloud console, you can select an individual statement inside thequery editor and run it.

Permissions

Permission to access a table, model, or other resource is checked at the time ofexecution. If a statement is not executed, or an expression is not evaluated,BigQuery does not check whether the user executing themulti-statement query has access to any resources referenced by it.

Within a multi-statement query, the permissions for each expression or statementare validated separately. For example:

SELECT*FROMdataset_with_access.table1;SELECT*FROMdataset_without_access.table2;

If the user executing the query has access totable1but does not have access totable2, the first query succeeds and thesecond query fails. The multi-statement query job itself alsofails.

Security constraints

In multi-statement queries, you can usedynamic SQLto build SQL statements at runtime. This is convenient, but can offer newopportunities for misuse. For example, executing the following query poses apotentialSQL injection securitythreat since the table parameter could be improperly filtered, allow access to,and be executed on unintended tables.

-- Risky query vulnerable to SQL injection attack.EXECUTEIMMEDIATECONCAT('SELECT * FROM SensitiveTable WHERE id = ',@id);

To avoid exposing or leaking sensitive data in a table or runningcommands likeDROP TABLE to delete data in a table, BigQuery'sdynamic procedural statements support multiple security measures toreduce exposure to SQL injection attacks, including:

• AnEXECUTE IMMEDIATE statement does not allow its query, expanded withquery parameters and variables, to embed multiple SQL statements.
• The following commands are restricted from being executed dynamically:BEGIN/END,CALL,CASE,IF,LOOP,WHILE, andEXECUTE IMMEDIATE.

Configuration field limitations

The followingjob configuration query fieldscannot be set for a multi-statement query:

• clustering
• create_disposition
• destination_table
• destination_encryption_configuration
• range_partitioning
• schema_update_options
• time_partitioning
• user_defined_function_resources
• write_disposition

Pricing

Pricing for multi-statement queries includes charges for queries (when usingtheon-demand billing model)and storage fortemporary tables. When you are usingreservations, query usage is covered byyour reservation charges.

On-demand query size calculation

If you use on-demand billing, BigQuery charges formulti-statement queries based on the number of bytes processed during executionof the multi-statement queries.

To get an estimate of how many bytes a multi-statement query might process,you can run adry run.

The following pricing applies for these multi-statement queries:

• DECLARE: the sum of bytes scanned for any tables referenced in theDEFAULTexpression.DECLARE statements with no table references don't incur a cost.

• SET: the sum of bytes scanned for any tables referenced in the expression.SETstatements with no table references don't incur a cost.

• IF: the sum of bytes scanned for any tables referenced in the conditionexpression.IF condition expressions with no table reference don't incur acost. Any statements within theIF block that are not executed don't incur acost.

• WHILE: the sum of bytes scanned for any tables referenced in the conditionexpression.WHILE statements with no table references in the conditionexpression don't incur a cost. Any statements within theWHILE block that arenot executed don't incur a cost.

• CONTINUE orITERATE: No associated cost.

• BREAK orLEAVE: No associated cost.

• BEGIN orEND: No associated cost.

If a multi-statement query fails, the cost of any statements up untilthe failure still applies. The statement that failed does not incur any costs.

For example, the following sample code contains comments precedingevery statement that explain what cost, if any, is incurred by each statement:

-- No cost, since no tables are referenced.DECLARExDATEDEFAULTCURRENT_DATE();-- Incurs the cost of scanning string_col from dataset.table.DECLAREySTRINGDEFAULT(SELECTMAX(string_col)FROMdataset.table);-- Incurs the cost of copying the data from dataset.big_table.  Once the-- table is created, you are not charged for storage while the rest of the-- multi-statement query runs.CREATETEMPTABLEtASSELECT*FROMdataset.big_table;-- Incurs the cost of scanning column1 from temporary table t.SELECTcolumn1FROMt;-- No cost, since y = 'foo' doesn't reference a table.IFy='foo'THEN-- Incurs the cost of scanning all columns from dataset.other_table, if-- y was equal to 'foo', or otherwise no cost since it is not executed.SELECT*FROMdataset.other_table;ELSE-- Incurs the cost of scanning all columns from dataset.different_table, if-- y was not equal to 'foo', or otherwise no cost since it is not executed.UPDATEdataset.different_tableSETcol=10WHEREtrue;ENDIF;-- Incurs the cost of scanning date_col from dataset.table for each-- iteration of the loop.WHILEx<(SELECTMIN(date_col)FROMdataset.table)DO-- No cost, since the expression does not reference any tables.SETx=DATE_ADD(x,INTERVAL1DAY);-- No cost, since the expression does not reference any tables.IFtrueTHEN-- LEAVE has no associated cost.LEAVE;ENDIF;-- Never executed, since the IF branch is always taken, so does not incur-- a cost.SELECT*FROMdataset.big_table;ENDWHILE;

For more information, seeQuery size calculation.

Storage pricing

You are charged fortemporary tables created by multi-statement queries. You can use theTABLE_STORAGE orTABLE_STORAGE_USAGE_TIMELINEviews to see the storage used by these temporary tables. Temporarytables reside in hidden_script% datasets with randomly generated names.

Quotas

For information about multi-statement query quotas, seeQuotas and limits.

View the number of multi-statement queries

You can view the number of active multi-statement queries using theINFORMATION_SCHEMA.JOBS_BY_PROJECT view.The following example uses theINFORMATION_SCHEMA.JOBS_BY_PROJECT view toshow the number of multi-statement queries from the previous day:

SELECTCOUNT(*)FROM`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECTWHEREcreation_timeBETWEENTIMESTAMP_SUB(CURRENT_TIMESTAMP(),INTERVAL1DAY)ANDCURRENT_TIMESTAMP()ANDjob_type="QUERY"ANDstate='RUNNING'ANDstatement_type='SCRIPT'

For more information about queryingINFORMATION_SCHEMA.JOBS formulti-statement queries, seeMulti-statement query job.