Standard SQL User-Defined Functions

BigQuery supports user-defined functions (UDFs). A UDF enables you to create
a function using another SQL expression or JavaScript. These functions accept
columns of input and perform actions, returning the result of those actions as a
value. For information on user-defined functions in legacy SQL, see
User-Defined Functions in Legacy
SQL.

UDFs can either be persistent or temporary. You can reuse persistent UDFs
across multiple queries, whereas you can only use temporary UDFs in a single
query. For information on persistent UDFs, see the
CREATE FUNCTION documentation.

UDF Syntax

Temporary user-defined functions in BigQuery use the following syntax:

CREATE { TEMPORARY | TEMP }
FUNCTION.
Creates a new temporary function. A function can contain zero or more
named_parameters. You must include
either TEMPORARY or TEMP when you create a
temporary UDF.

named_parameter. Consists of a comma-separated param_name and
param_type pair. The value of param_type is a BigQuery
data type.
For a SQL UDF, the value of param_type may also be ANY TYPE.

[RETURNS data_type]. Specifies the data type
that the function returns. If the function is defined in SQL, then the
RETURNS clause is optional and BigQuery infers the result type
of the function from the SQL function body. If the
function is defined in JavaScript, then the RETURNS clause is
required. See Supported UDF data types
for more information about allowed values for
data_type.

[LANGUAGE js AS javascript_code]. Specifies the
definition of a JavaScript function.

AS (function_definition). Specifies the SQL code that defines the
function. function_definition is a SQL expression.

Supported JavaScript UDF data types

SQL type encodings in JavaScript

Some SQL types have a direct mapping to JavaScript types, but others do not.

Because JavaScript does not support a 64-bit integer type,
INT64 is unsupported as an input type for JavaScript
UDFs. Instead, use FLOAT64 to represent integer
values as a number, or STRING to represent integer
values as a string.

BigQuery does support INT64 as a return type
in JavaScript UDFs. In this case, the JavaScript function body can return either
a JavaScript Number or a String. BigQuery then converts either of
these types to INT64.

BigQuery represents types in the following manner:

BigQuery Data Type

JavaScript Data Type

ARRAY

ARRAY

BOOL

BOOLEAN

BYTES

base64-encoded STRING

FLOAT64

NUMBER

NUMERIC

If a NUMERIC value can be represented exactly as an
IEEE 754 floating-point
value and has no fractional part, it is encoded as a Number. These values are
in the range [-253, 253]. Otherwise, it is encoded as a
String.

STRING

STRING

STRUCT

OBJECT where each STRUCT field is a named field

TIMESTAMP

DATE with a microsecond field containing the microsecond
fraction of the timestamp

DATE

DATE

Quoting rules

You must enclose JavaScript code in quotes. For simple, one line code snippets,
you can use a standard quoted string:

SQL UDF structure

Templated SQL UDF parameters

A templated parameter can match more than one argument type at function call
time. If a function signature includes a templated parameter, BigQuery
allows function calls to pass one of several argument types to the function.

ANY TYPE. The function will accept an input of any type for this argument.
If more than one parameter has the type ANY TYPE, BigQuery does
not enforce any relationship between these arguments at the time of function
creation. However, passing the function arguments of types that are
incompatible with the function definition will result in an error at call
time.

In the preceding example, code in lib1.js, lib2.js, and lib3.js is
available to any code in the [external_code] section of the UDF. Notice that
you can specify library files using single-element or array syntax.

Best practices for JavaScript UDFs

If your input can be easily filtered down before being passed to a JavaScript
UDF, your query will likely be faster and cheaper.

Avoid persistent mutable state

Do not store or access mutable state across JavaScript UDF calls.

Use memory efficiently

The JavaScript processing environment has limited memory available per query.
JavaScript UDF queries that accumulate too much local state may fail due to
memory exhaustion.

Limits

The amount of data that your JavaScript UDF outputs when processing a single
row should be approximately 5 MB or less.

A JavaScript UDF can timeout and prevent your query from completing. Timeouts
can be as short as 5 minutes, but can vary depending on several factors,
including how much user CPU time your function consumes and how large your
inputs and outputs to the JS function are.

A query job can have a maximum of 50 JavaScript UDF resources (inline code
blobs or external files).

Each inline code blob is limited to a maximum size of 32 KB.

Each external code resource is limited to a maximum size of 1 MB.

Limitations

For temporary UDFs, function_name cannot contain periods.

The DOM objects Window, Document and Node, and functions that require
them, are unsupported.

JavaScript functions that rely on native code are unsupported.

Bitwise operations in JavaScript handle only the most significant 32 bits.

Because of their non-deterministic nature, queries that invoke user-defined functions cannot use cached results.