Work with user-defined functions in Python

A Python user-defined function (UDF) lets you implement a scalar function in Python and use it in a SQL query. Python UDFs are similar to SQL and Javascript UDFs, but with additional capabilities. Python UDFs let you install third-party libraries from the Python Package Index (PyPI) and let you access external services using a Cloud resource connection.

Python UDFs are built and run on BigQuery managed resources.

Limitations

  • python-3.11 is the only supported runtime.
  • You can't create a temporary Python UDF.
  • You can't use a Python UDF with a materialized view.
  • The results of a query that calls a Python UDF aren't cached because the return value of a Python UDF is always assumed to be non-deterministic.
  • Assured workloads aren't supported.
  • These data types are not supported: JSON, RANGE, INTERVAL, and GEOGRAPHY.
  • Containers that run Python UDFs can only be configured up to 4 vCpu and 16 GiB.
  • Encrypting Python UDF code with Customer-managed encryption keys (CMEK) isn't supported.
  • Python UDFs support VPC Service Controls, but VPC networks aren't supported.

Required roles

The required IAM roles are based on whether you are a Python UDF owner or a Python UDF user.

UDF owners

A Python UDF owner typically creates or updates a UDF. Additional roles are also required if you create a Python UDF that references a Cloud resource connection. This connection is required only if your UDF uses the WITH CONNECTION clause to access an external service.

To get the permissions that you need to create or update a Python UDF, ask your administrator to grant you the following IAM roles:

For more information about granting roles, see Manage access to projects, folders, and organizations.

These predefined roles contain the permissions required to create or update a Python UDF. To see the exact permissions that are required, expand the Required permissions section:

Required permissions

The following permissions are required to create or update a Python UDF:

  • Create a Python UDF using the CREATE FUNCTION statement: bigquery.routines.create on the dataset
  • Update a Python UDF using the CREATE FUNCTION statement: bigquery.routines.update on the dataset
  • Run a CREATE FUNCTION statement query job: bigquery.jobs.create on the project
  • Create a new Cloud resource connection: bigquery.connections.create on the project
  • Use a connection in the CREATE FUNCTION statement: bigquery.connections.delegate on the connection

You might also be able to get these permissions with custom roles or other predefined roles.

For more information about roles in BigQuery, see Predefined IAM roles.

UDF users

A Python UDF user invokes a UDF created by someone else. Additional roles are also required if you invoke a Python UDF that references a Cloud resource connection.

To get the permissions that you need to invoke a Python UDF created by someone else, ask your administrator to grant you the following IAM roles:

For more information about granting roles, see Manage access to projects, folders, and organizations.

These predefined roles contain the permissions required to invoke a Python UDF created by someone else. To see the exact permissions that are required, expand the Required permissions section:

Required permissions

The following permissions are required to invoke a Python UDF created by someone else:

  • To run a query job that references a Python UDF: bigquery.jobs.create on the project
  • To invoke a Python UDF created by someone else: bigquery.routines.get on the dataset
  • To run a Python UDF that references a Cloud resource connection: bigquery.connections.use on the connection

You might also be able to get these permissions with custom roles or other predefined roles.

For more information about roles in BigQuery, see Predefined IAM roles.

Create a persistent Python UDF

Follow these rules when you create a Python UDF:

  • The body of the Python UDF must be a quoted string literal that represents the Python code. To learn more about quoted string literals, see Formats for quoted literals.

  • The body of the Python UDF must include a Python function that is used in the entry_point argument in the Python UDF options list.

  • A Python runtime version needs to be specified in the runtime_version option. The only supported Python runtime version is python-3.11. For a full list of available options, see the Function option list for the CREATE FUNCTION statement.

To create a persistent Python UDF, use the CREATE FUNCTION statement without the TEMP or TEMPORARY keyword. To delete a persistent Python UDF, use the DROP FUNCTION statement.

Example

To see an example of creating a persistent Python UDF, choose on of the following options:

Console

The following example creates a persistent Python UDF named multiplyInputs and calls the UDF from within a SELECT statement:

  1. Go to the BigQuery page.

    Go to BigQuery

  2. In the query editor, enter the following CREATE FUNCTION statement:

    CREATE FUNCTION `PROJECT_ID.DATASET_ID`.multiplyInputs(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

''';

-- Call the Python UDF.
WITH numbers AS
    (SELECT 1 AS x, 5 as y
    UNION ALL
    SELECT 2 AS x, 10 as y
    UNION ALL
    SELECT 3 as x, 15 as y)
SELECT x, y,
`PROJECT_ID.DATASET_ID`.multiplyInputs(x, y) AS product
FROM numbers;

    Replace PROJECT_ID.DATASET_ID with your project ID and dataset ID.

  3. Click  Run.

    This example produces the following output:

    +-----+-----+--------------+
| x   | y   | product      |
+-----+-----+--------------+
| 1   | 5   |  5.0         |
| 2   | 10  | 20.0         |
| 3   | 15  | 45.0         |
+-----+-----+--------------+

BigQuery DataFrames

The following example uses BigQuery DataFrames to turn a custom function into a Python UDF:

import bigframes.pandas as bpd

# Set BigQuery DataFrames options
bpd.options.bigquery.project = your_gcp_project_id
bpd.options.bigquery.location = "US"

# BigQuery DataFrames gives you the ability to turn your custom functions
# into a BigQuery Python UDF. One can find more details about the usage and
# the requirements via `help` command.
help(bpd.udf)

# Read a table and inspect the column of interest.
df = bpd.read_gbq("bigquery-public-data.ml_datasets.penguins")
df["body_mass_g"].peek(10)

# Define a custom function, and specify the intent to turn it into a
# BigQuery Python UDF. Let's try a `pandas`-like use case in which we want
# to apply a user defined function to every value in a `Series`, more
# specifically bucketize the `body_mass_g` value of the penguins, which is a
# real number, into a category, which is a string.
@bpd.udf(
    dataset=your_bq_dataset_id,
    name=your_bq_routine_id,
)
def get_bucket(num: float) -> str:
    if not num:
        return "NA"
    boundary = 4000
    return "at_or_above_4000" if num >= boundary else "below_4000"

# Then we can apply the udf on the `Series` of interest via
# `apply` API and store the result in a new column in the DataFrame.
df = df.assign(body_mass_bucket=df["body_mass_g"].apply(get_bucket))

# This will add a new column `body_mass_bucket` in the DataFrame. You can
# preview the original value and the bucketized value side by side.
df[["body_mass_g", "body_mass_bucket"]].peek(10)

# The above operation was possible by doing all the computation on the
# cloud through an underlying BigQuery Python UDF that was created to
# support the user's operations in the Python code.

# The BigQuery Python UDF created to support the BigQuery DataFrames
# udf can be located via a property `bigframes_bigquery_function`
# set in the udf object.
print(f"Created BQ Python UDF: {get_bucket.bigframes_bigquery_function}")

# If you have already defined a custom function in BigQuery, either via the
# BigQuery Google Cloud Console or with the `udf` decorator,
# or otherwise, you may use it with BigQuery DataFrames with the
# `read_gbq_function` method. More details are available via the `help`
# command.
help(bpd.read_gbq_function)

existing_get_bucket_bq_udf = get_bucket.bigframes_bigquery_function

# Here is an example of using `read_gbq_function` to load an existing
# BigQuery Python UDF.
df = bpd.read_gbq("bigquery-public-data.ml_datasets.penguins")
get_bucket_function = bpd.read_gbq_function(existing_get_bucket_bq_udf)

df = df.assign(body_mass_bucket=df["body_mass_g"].apply(get_bucket_function))
df.peek(10)

# Let's continue trying other potential use cases of udf. Let's say we
# consider the `species`, `island` and `sex` of the penguins sensitive
# information and want to redact that by replacing with their hash code
# instead. Let's define another scalar custom function and decorate it
# as a udf. The custom function in this example has external package
# dependency, which can be specified via `packages` parameter.
@bpd.udf(
    dataset=your_bq_dataset_id,
    name=your_bq_routine_id,
    packages=["cryptography"],
)
def get_hash(input: str) -> str:
    from cryptography.fernet import Fernet

    # handle missing value
    if input is None:
        input = ""

    key = Fernet.generate_key()
    f = Fernet(key)
    return f.encrypt(input.encode()).decode()

# We can use this udf in another `pandas`-like API `map` that
# can be applied on a DataFrame
df_redacted = df[["species", "island", "sex"]].map(get_hash)
df_redacted.peek(10)

# If the BigQuery routine is no longer needed, we can clean it up
# to free up any cloud quota
session = bpd.get_global_session()
session.bqclient.delete_routine(f"{your_bq_dataset_id}.{your_bq_routine_id}")

Container build status

When you create a Python UDF using the CREATE FUNCTION statement, BigQuery creates or updates a container image that is based on a base image. The container is built on the base image using your code and any specified package dependencies.

Creating the container is a long-running process. The first query after you run the CREATE FUNCTION statement waits for the image build to complete. If there are no external dependencies, the container image is typically created in less than a minute.

The size of all Python UDF containers per project and per region is restricted to a sum total of 10GiB. For more information, see User-defined function limits for persistent UDFs. Your container build fails if your project has reached the quota.

To see the status of your container build, choose one of the following: