Stored procedures in BigQuery

A BigQuery stored procedure is a saved script you run with CALL. It takes IN, OUT and INOUT arguments, OUT arguments must be variables, temp tables it creates stay visible to the caller, and its name is case-sensitive.

A stored procedure is a script saved in a dataset under a name, with arguments. You run it with CALL. Everything a script can do, a procedure can do: variables, loops, DML, DDL, temp tables, transactions and dynamic SQL. Procedures are how you package a multi-step job, such as a nightly rebuild or a data-quality check, so that schedulers and people run the same code.

Creating and calling one

CREATE OR REPLACE PROCEDURE mydataset.count_big_orders(min_amount INT64, OUT n INT64)
BEGIN
  CREATE TEMP TABLE big_orders AS
  SELECT * FROM mydataset.orders WHERE amount >= min_amount;
  SET n = (SELECT COUNT(*) FROM big_orders);
END;

DECLARE n INT64;
CALL mydataset.count_big_orders(50, n);
SELECT n;

In Querylab.io, a dataset’s SQL Templates menu has a Procedure skeleton to start from (DDL operations), and a procedure in the schema tree opens a tab with its Details and its defining SQL under Query (Table information).

Arguments are IN (the default; a value goes in), OUT (the procedure sets it) or INOUT (both). The body is between BEGIN and END, and the DECLARE rules of scripts apply inside it.

OUT arguments must be variables

An OUT or INOUT argument needs somewhere to write, so you must pass a declared variable. Passing a literal or NULL fails with ”… must be a variable”. That also means a procedure with an OUT argument can’t be called as a single statement: declare the variable first, which makes it a script.

Temp tables stay visible after CALL

A temporary table created inside a procedure belongs to the calling script, not to the procedure. After CALL mydataset.count_big_orders(50, n), the caller can run SELECT * FROM big_orders. We checked this: the rows the procedure selected were there.

That is useful for returning a whole result set, since a procedure has no RETURNS. It also means two procedures that create a temp table with the same name in one script collide. Use CREATE OR REPLACE TEMP TABLE, or distinct names.

Errors

A RAISE or a failed statement inside a procedure stops it and passes the error to the caller. An EXCEPTION block around the CALL catches it, and @@error.message is the original message:

BEGIN
  CALL mydataset.check_orders();
EXCEPTION WHEN ERROR THEN
  SELECT @@error.message;  -- 'too big', the message the procedure raised
END;

Uncaught, the error names the procedure and the line and column inside it, such as ... at [mydataset.check_orders:1:21].

Names and permissions

• Procedure names are case-sensitive. CALL mydataset.COUNT_BIG_ORDERS(...) fails with “Not found” when the procedure is count_big_orders.
• A caller needs bigquery.routines.get on the procedure and access to everything the procedure reads and writes, unless it is an authorized routine.

What a CALL costs

A CALL is a script job, and each statement inside the procedure is billed like it would be in a script: bytes per statement, added up. There is no extra charge for calling a procedure. See Scripting.

Procedures in other languages

Besides SQL, BigQuery can run stored procedures for Apache Spark, written in Python, Java or Scala. They run on Spark through a connection and are billed for that compute. Use them for work that needs Spark libraries. Everything else is simpler as a SQL procedure.

Procedure or table function

A procedure can change data and run many statements, but it can’t be used inside a query. A table function is a single SELECT you call in FROM. Use a procedure for jobs, a table function for reusable queries.