# 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.

Source: https://docs.querylab.io/bigquery/user-defined-functions/

Updated: 2026-10-14

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:

**a temporary SQL function**

```sql
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
```

Result in BigQuery (1 row):

| sale_price |
| --- |
| 16.99 |

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

**a temporary function and nothing else**

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

BigQuery rejects this query:

```text
CREATE TEMPORARY FUNCTION statements must be followed by an actual query.
```

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

**sum the lines of an order**

```sql
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
```

Result in BigQuery (1 row):

| total |
| --- |
| 7 |

### 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:

**one function, two element types**

```sql
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
```

Result in BigQuery (1 row):

| first_int | first_string |
| --- | --- |
| 3 | none |

`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:

**turn a title into a URL slug**

```sql
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
```

Result in BigQuery (1 row):

| slug |
| --- |
| how-bigquery-bills-a-query |

### 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:

**plus_one(41) with an INT64 parameter**

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

Result in BigQuery (1 row):

| answer |
| --- |
| 411 |

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

**the same function with FLOAT64**

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

Result in BigQuery (1 row):

| answer |
| --- |
| 42.0 |

`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.

```sql
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:

```sql
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](https://docs.querylab.io/bigquery/authorized-views/) 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](https://docs.querylab.io/schema-browser/ddl-operations/#dataset-templates)), and a function in the schema tree opens a tab with its Details and its definition under Query ([Table information](https://docs.querylab.io/schema-browser/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](https://docs.querylab.io/bigquery/table-functions/).

## 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.

Write a temporary SQL UDF and call it. Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in.

## Sources

Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery:

- [User-defined functions](https://cloud.google.com/bigquery/docs/user-defined-functions)

- [Python user-defined functions](https://cloud.google.com/bigquery/docs/user-defined-functions-python)

- [Best practices for functions](https://cloud.google.com/bigquery/docs/best-practices-performance-functions)

- [Quotas and limits, UDFs](https://cloud.google.com/bigquery/quotas#udf_limits)