User-defined functions in BigQuery (SQL, JavaScript and Python)

BigQuery UDFs come in SQL, JavaScript and Python. SQL UDFs are the fastest and can take ANY TYPE arguments. JavaScript gets INT64 values as strings, so 41 + 1 returns 411. Temporary functions live for one query, persistent ones in a dataset.

A user-defined function (UDF) wraps an expression or a piece of code under a name you can call in SQL. BigQuery has three kinds: SQL UDFs, which are expressions and run as fast as the SQL they contain; JavaScript UDFs, which run code in a sandbox, for logic SQL can’t express; and Python UDFs, which run in a container and can use Python packages. A UDF can be temporary, defined at the top of a query and gone after it, or persistent, stored in a dataset and shared.

SQL UDFs

A SQL UDF is a named expression with typed parameters:

CREATE TEMP FUNCTION net_price(price NUMERIC, discount_pct NUMERIC)
RETURNS NUMERIC
AS (ROUND(price * (1 - discount_pct / 100), 2));
SELECT net_price(19.99, 15) AS sale_price

CREATE TEMP FUNCTION must be followed by a query in the same request. On its own, BigQuery rejects it:

CREATE TEMP FUNCTION double_it(x INT64) AS (x * 2)

The body can contain a subquery, which is how you aggregate over an array argument:

CREATE TEMP FUNCTION order_total(lines ARRAY<STRUCT<qty INT64, price NUMERIC>>)
AS ((SELECT SUM(qty * price) FROM UNNEST(lines)));
SELECT order_total([STRUCT(2 AS qty, NUMERIC '1.50' AS price), (1, 4.00)]) AS total

ANY TYPE parameters

A parameter declared ANY TYPE accepts any type, and the function is checked for each call with the actual types. One function then works for arrays of numbers and arrays of strings:

CREATE TEMP FUNCTION first_or_default(arr ANY TYPE, fallback ANY TYPE)
AS (IFNULL(arr[SAFE_OFFSET(0)], fallback));
SELECT
  first_or_default([3, 4], 0) AS first_int,
  first_or_default(ARRAY<STRING>[], 'none') AS first_string

RETURNS is optional for any SQL UDF. Without it, BigQuery works out the return type for each call, which is what you want when the parameters are ANY TYPE.

JavaScript UDFs

When a calculation needs loops, string handling or math that SQL can’t do well, write it in JavaScript. The body is a string, usually a raw triple-quoted string so that quotes and backslashes need no escaping:

CREATE TEMP FUNCTION slugify(title STRING)
RETURNS STRING
LANGUAGE js
AS r"""
  return title.toLowerCase().replace(/[^a-z0-9]+/g, '-').replace(/^-|-$/g, '');
""";
SELECT slugify('How BigQuery Bills a Query!') AS slug

INT64 arrives as a string

JavaScript numbers can’t hold every 64-bit integer, and BigQuery doesn’t convert INT64 arguments to numbers. Google’s docs say INT64 is unsupported as an input type. In practice BigQuery accepts it and passes the value as a string, so + concatenates:

CREATE TEMP FUNCTION plus_one(x INT64)
RETURNS INT64
LANGUAGE js
AS r"""
  return x + 1;
""";
SELECT plus_one(41) AS answer

Declare integer parameters as FLOAT64 (exact up to 2^53) or as STRING and parse them yourself:

CREATE TEMP FUNCTION plus_one(x FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS r"""
  return x + 1;
""";
SELECT plus_one(41) AS answer

INT64 is fine as a return type: BigQuery converts the returned number or string to INT64. NUMERIC and BIGNUMERIC arguments arrive as numbers when they are whole and within ±2^53, otherwise as strings. BYTES arrive base64-encoded.

JavaScript limits

• Each call runs in a sandbox without DOM objects or system calls.
• A query can use up to 50 inline code blobs or library files, each blob up to 32 KB. Larger code goes in a file in Cloud Storage, loaded with OPTIONS (library = ['gs://...']).
• Output per row is limited to about 5 MB.
• A slow function can time out the query, sometimes after only 5 minutes.

Calling JavaScript starts a separate process, which is much slower than evaluating SQL. Use a SQL UDF whenever the logic can be written in SQL.

Python UDFs

Python UDFs run Python 3.11 in a managed container and can install packages from PyPI. They must be persistent: there are no temporary Python UDFs.

CREATE FUNCTION mydataset.multiply(x FLOAT64, y FLOAT64)
RETURNS FLOAT64
LANGUAGE python
OPTIONS (runtime_version = 'python-3.11', entry_point = 'multiply')
AS r'''
def multiply(x, y):
    return x * y
''';

They are billed separately from the query, for building the container image and for the compute that runs your code. Use them for things only a Python library does well, such as parsing a file format or calling a model.

Persistent UDFs

Leave out TEMP and give the function a dataset, and it is stored there for any query, view or other function to use:

CREATE OR REPLACE FUNCTION mydataset.net_price(price NUMERIC, discount_pct NUMERIC)
RETURNS NUMERIC
AS (ROUND(price * (1 - discount_pct / 100), 2));

SELECT mydataset.net_price(19.99, 15);

• A view or a persistent UDF can call persistent UDFs, but not temporary ones. Inside them, the called function’s name must include the dataset.
• The names of persistent functions are case-sensitive: mydataset.Net_Price is a different function. Temporary function names are not.
• Other users need the bigquery.routines.get permission on the dataset to call the function. An authorized routine can read tables the caller can’t.

Google recommends keeping shared business logic in persistent UDFs in a central dataset, instead of each query defining its own copy.

In Querylab.io, a dataset’s SQL Templates menu has skeletons for SQL, JavaScript, Python and remote functions (DDL operations), and a function in the schema tree opens a tab with its Details and its definition under Query (Table information).

Table functions

A UDF returns one value per call. To wrap a whole query with parameters and return a table, use a table function.

What UDFs cost

A SQL UDF costs what the SQL inside it costs, and the optimizer treats it as part of the query. A JavaScript UDF uses more slot time but reads no extra bytes, so with on-demand pricing it costs nothing extra; with capacity pricing it uses slots. Python UDFs are billed for their container time on top of the query.