BigQuery Control Flow Statements

Similar to relational databases such as SQL Server, BigQuery supports scripting. BigQuery scripting allows you to send multiple statements to BigQuery in one request. You can also set variables, and use it in any supported 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 loop statement. In this article, we will check what are BigQuery Control Flow Statements with some examples.

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.

You can use two types of control structures while using scripting in BigQuery.

Following are the BigQuery control flow statements:

Conditional control Statements– Sometimes called branching structures
Looping Structures – Sometimes called Iterative control structures.

Conditional Control Statements

The BigQuery supports IF statement to control the execution.

The if statements can perform a particular task based on the certain conditions. For example, execute set of statements based on the variable value

There are three forms of IF statement that you can use. For example,

if
if-else
if-else-if

Following is the syntax of IF statement in BigQuery:

IF condition THEN [sql_statement_list1]
[ELSEIF condition THEN sql_statement_list2]
...
[ELSE sql_statement_listn]
END IF;

The IF Executes the first sql_statement_list where the condition is true, or the optional ELSE sql_statement_list if no conditions match.

Following is the example of using IF statement in BigQuery script.

DECLARE x INT64 DEFAULT 1;
IF x = 1 THEN
  SELECT 'Statement 1';
  SET x = x + 2;
ELSEIF x = 2 THEN 
  SELECT 'Statement 2';
ELSE
  SELECT 'Statement n';
END IF;

Note that, there is a maximum nesting level of 50 for blocks and conditional statements such as IF/ELSE/END IF,

BigQuery Looping Structures

With the help of BigQuery looping statements, you can control the flow of execution in BigQuery script.

The BigQuery supports following looping structures:

LOOP
WHILE

BigQuery also provide statements to break or continue the loop.

BREAK / LEAVE
CONTINUE / ITERATE

Now, let us check how to use loops in BigQuery script.

BigQuery LOOP Statement

The LOOP executes the set of SQL statements until BREAK or LEAVE exits the loop. The set of SQL statement is separated by the semicolon. LOOP is restricted from being executed dynamically as a nested element.

Following is the syntax of LOOP statement:

LOOP
  sql_statement_list
END LOOP;

The LOOP executes sql_statement_list until LEAVE or BREAK encountered.

Following is the example of using LOOP in BigQuery.

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

BigQuery WHILE Loop Statement

The while loop loops through a list of SQL statements as long as a boolean condition is true.

Following is the syntax of BigQuery while loop:

WHILE boolean_expression DO
  sql_statement_list
END WHILE;

Following is the example of using WHILE loop inside BigQuery script:

DECLARE x INT64 DEFAULT 0;
WHILE x < 10 
DO
  SET x = x + 1;
END WHILE;
SELECT x;

Note that, there is a maximum nesting level of 50 for blocks and conditional statements such as WHILE/END WHILE,

Example to use BigQuery CONTINUE and BREAK

Following example demonstrates the use of CONTINUE and BREAK statement.

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

Note that, ITERATE is a synonym for CONTINUE and LEAVE is a synonym for BREAK. You can use them interchangeably.

Hope this helps 🙂