Scripting in standard SQL

Beta

This feature is in a pre-release state and might change or have limited support. For more information, see the product launch stages.

BigQuery scripting

BigQuery scripting enables you to send multiple statements to BigQuery in one request, to use variables, and to use control flow statements such as IF and WHILE. For example, you can declare a variable, assign a value to it, and then reference it in a third statement.

In BigQuery, a script is a SQL statement list to be executed in sequence. A SQL statement list is a list of any valid BigQuery statements that are separated by semicolons.

For example:

-- Declare a variable to hold names as an array.
DECLARE top_names ARRAY<STRING>;
-- Build an array of the top 100 names from the year 2017.
SET top_names = (
  SELECT ARRAY_AGG(name ORDER BY number DESC LIMIT 100)
  FROM `bigquery-public-data`.usa_names.usa_1910_current
  WHERE year = 2017
);
-- Which names appear as words in Shakespeare's plays?
SELECT
  name AS shakespeare_name
FROM UNNEST(top_names) AS name
WHERE name IN (
  SELECT word
  FROM `bigquery-public-data`.samples.shakespeare
);

BigQuery interprets any request with multiple statements as a script, unless the statements consist of CREATE TEMP FUNCTION statement(s), with a single final query statement. For example, the following would not be considered a script:

CREATE TEMP FUNCTION Add(x INT64, y INT64) AS (x + y);

SELECT Add(3, 4);

DECLARE

Syntax

DECLARE variable_name[, ...] variable_type [ DEFAULT expression];

variable_name must be a valid identifier, and variable_type is any BigQuery type.

Description

Declares a variable of the specified type. If the DEFAULT clause is specified, the variable is initialized with the value of the expression; if no DEFAULT clause is present, the variable is initialized with the value NULL.

Variable declarations must appear at the start of a script, prior to any other statements, or at the start of a block declared with BEGIN. Variable names are case-insensitive.

Multiple variable names can appear in a single DECLARE statement, but only one variable_type and expression.

It is an error to declare a variable with the same name as a variable declared earlier in the current block or in a containing block.

If the DEFAULT clause is present, the value of the expression must be coercible to the specified type. The expression may reference other variables declared previously within the same block or a containing block.

The maximum size of a variable is 1 MB, and the maximum size of all variables used in a script is 10 MB.

Examples

The following example intializes the variable x as an INT64 with the value NULL.

DECLARE x INT64;

The following example intializes the variable x as a DATE with the value of the current date.

DECLARE d DATE DEFAULT CURRENT_DATE();

The following example initializes the variables x, y, and z as INT64 with the value 0.

DECLARE x, y, z INT64 DEFAULT 0;

SET

Syntax

SET name = expression;

SET (variable_name_1, variable_name_2, …, variable_name_n) =
  (expression_1, expression_2, …, expression_n);

Description

Sets a variable to have the value of the provided expression, or sets multiple variables at the same time based on the result of multiple expressions.

The SET statement may appear anywhere within the body of a script.

Examples

The following example sets the variable x to have the value 5.

SET x = 5;

The following example sets the variable a to have the value 4, b to have the value 'foo', and the variable c to have the value false.

SET (a, b, c) = (1 + 3, 'foo', false);

The following example assigns the result of a query to multiple variables. First, it declares two variables, target_word and corpus_count; next, it assigns the results of a SELECT AS STRUCT query to the two variables. The result of the query is a single row containing a STRUCT with two fields; the first element is assigned to the first variable, and the second element is assigned to the second variable.

DECLARE target_word STRING DEFAULT 'methinks';
DECLARE corpus_count, word_count INT64;

SET (corpus_count, word_count) = (
  SELECT AS STRUCT COUNT(DISTINCT corpus), SUM(word_count)
  FROM `bigquery-public-data`.samples.shakespeare
  WHERE LOWER(word) = target_word
);

SELECT
  FORMAT('Found %d occurrences of "%s" across %d Shakespeare works',
         word_count, target_word, corpus_count) AS result;

This statement list outputs the following string:

Found 151 occurrences of "methinks" across 38 Shakespeare works

BEGIN

Syntax

BEGIN
  sql_statement_list
END;

Description

BEGIN initiates a block of statements where declared variables exist only until the corresponding END. sql_statement_list is a list of zero or more SQL statements ending with semicolons.

Variable declarations must appear at the start of the block, prior to other types of statements. Variables declared inside a block may only be referenced within that block and in any nested blocks. It is an error to declare a variable with the same name as a variable declared in the same block or an outer block.

There is a maximum nesting level of 50 for blocks and conditional statements such as BEGIN/END, IF/ELSE/END IF, and WHILE/END WHILE.

Example

The following example declares a variable x with the default value 10; then, it initiates a block, in which a variable y is assigned the value of x, which is 10, and returns this value; next, the END statement terminates the block, ending the scope of variable y; finally, it returns the value of x.

DECLARE x INT64 DEFAULT 10;
BEGIN
  DECLARE y INT64;
  SET y = x;
  SELECT y;
END;
SELECT x;

END

Terminates a block initiated by BEGIN.

IF

Syntax

IF condition THEN
  [if_statement_list]
[ELSE
  else_statement_list
]
END IF;

Description

If condition is true, executes if_statement_list. Otherwise executes else_statement_list, if the optional ELSE clause is provided.

There is a maximum nesting level of 50 for blocks and conditional statements such as BEGIN/END, IF/ELSE/END IF, and WHILE/END WHILE.

Examples

The following example declares a INT64 variable target_product_id with a default value of 103; then, it checks whether the table dataset.products contains a row with the product_id column matches the value of target_product_id; if so, it outputs a string stating that the product has been found, along with the value of default_product_id; if not, it outputs a string stating that the product has not been found, also with the value of default_product_id.

DECLARE target_product_id INT64 DEFAULT 103;
IF EXISTS (SELECT 1 FROM dataset.products
           WHERE product_id = target_product_id) THEN
  SELECT CONCAT('found product ', CAST(target_product_id AS STRING));
ELSE
  SELECT CONCAT('did not find product ', CAST(target_product_id AS STRING));
END IF;

Loops

LOOP

Syntax

LOOP
  sql_statement_list
END LOOP;

Description

Executes sql_statement_list until a BREAK or LEAVE statement exits the loop. sql_statement_list is a list of zero or more SQL statements ending with semicolons.

Example

The following example declares a variable x with the default value 0; then, it uses the LOOP statement to create a loop that executes until the variable x is greater than or equal to 10; after the loop terminates, the example outputs the value of x.

DECLARE x INT64 DEFAULT 0;
LOOP
  SET x = x + 1;
  IF x >= 10 THEN
    LEAVE;
  END IF;
END LOOP;
SELECT x;

This example outputs the following:

+----+
| x  |
+----+
| 10 |
+----+

WHILE

Syntax

WHILE boolean_expression DO
  sql_statement_list
END WHILE;

There is a maximum nesting level of 50 for blocks and conditional statements such as BEGIN/END, IF/ELSE/END IF, and WHILE/END WHILE.

Description

While boolean_expression is true, executes sql_statement_list. boolean_expression is evaluated for each iteration of the loop.

BREAK

Description

Exit the current loop.

It is an error to use BREAK outside of a loop.

Example

The following example delares two variables, heads and heads_count; next, it initiates a loop, which assigns a random boolean value to heads, then checks to see whether heads is true; if so, it outputs "Heads!" and increments heads_count; if not, it outputs "Tails!" and exits the loop; finally, it outputs a string stating how many times the "coin flip" resulted in "heads."

DECLARE heads BOOL;
DECLARE heads_count INT64 DEFAULT 0;
LOOP
  SET heads = RAND() < 0.5;
  IF heads THEN
    SELECT 'Heads!';
    SET heads_count = heads_count + 1;
  ELSE
    SELECT 'Tails!';
    BREAK;
  END IF;
END LOOP;
SELECT CONCAT(CAST(heads_count AS STRING), ' heads in a row');

LEAVE

Synonym for BREAK.

CONTINUE

Description

Skip any following statements in the current loop and return to the start of the loop.

It is an error to use CONTINUE outside of a loop.

Example

The following example delares two variables, heads and heads_count; next, it initiates a loop, which assigns a random boolean value to heads, then checks to see whether heads is true; if so, it outputs "Heads!", increments heads_count, and restarts the loop, skipping any remaining statements; if not, it outputs "Tails!" and exits the loop; finally, it outputs a string stating how many times the "coin flip" resulted in "heads."

DECLARE heads BOOL;
DECLARE heads_count INT64 DEFAULT 0;
LOOP
  SET heads = RAND() < 0.5;
  IF heads THEN
    SELECT 'Heads!';
    SET heads_count = heads_count + 1;
    CONTINUE;
  END IF;
  SELECT 'Tails!';
  BREAK;
END LOOP;
SELECT CONCAT(CAST(heads_count AS STRING), ' heads in a row');

ITERATE

Synonym for CONTINUE.

RETURN

In a BigQuery script, RETURN terminates execution of the current script.

CALL

Syntax

CALL procedure_name (procedure_argument[, …])

Description

Calls a procedure with an argument list. procedure_argument may be a variable or an expression. For OUT or INOUT arguments, a variable passed as an argument must have the proper BigQuery type.

The same variable may not appear multiple times as an OUT or INOUT argument in the procedure's argument list.

The maximum depth of procedure calls is 50 frames.

Example

The following example declares a variable retCode. Then, it calls the procedure updateSomeTables in the dataset myDataset, passing the arguments 'someAccountId' and retCode. Finally, it returns the value of retCode.

DECLARE retCode INT64;
-- Procedure signature: (IN account_id STRING, OUT retCode INT64)
CALL myDataset.UpdateSomeTables('someAccountId', retCode);
SELECT retCode;