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:

• BigQuery Data Editor (roles/bigquery.dataEditor) on the dataset
• BigQuery Job User (roles/bigquery.jobUser) on the project
• BigQuery Connection Admin (roles/bigquery.connectionAdmin) on the project

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:

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:

• BigQuery User (roles/bigquery.user) on the project
• BigQuery Data Viewer (roles/bigquery.dataViewer) on the dataset
• BigQuery Connection User (roles/bigquery.connectionUser) on the connection

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:

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:

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:

Create a vectorized Python UDF

You can implement your Python UDF to process a batch of rows instead of a single row by using vectorization. Vectorization can improve query performance. You can create a vectorized UDF using either Pandas or Apache Arrow.

To control batching behavior, specify the maximum number of rows in each batch by using the max_batching_rows option in the CREATE OR REPLACE FUNCTION option list. If you specify max_batching_rows, BigQuery determines the number of rows in a batch, up to the max_batching_rows limit. If max_batching_rows is not specified, the number of rows to batch is determined automatically.

Use Pandas

A vectorized Python UDF has a single pandas.DataFrame argument that must be annotated. The pandas.DataFrame argument has the same number of columns as the Python UDF parameters defined in the CREATE FUNCTION statement. The column names in the pandas.DataFrame argument have the same names as the UDF's parameters.

Your function needs to return either a pandas.Series or a single-column pandas.DataFrame with the same number of rows as the input.

The following example creates a vectorized Python UDF named multiplyInputs with two parameters—x and y:

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`.multiplyVectorized(x FLOAT64, y FLOAT64)
   RETURNS FLOAT64
   LANGUAGE python
   OPTIONS(runtime_version="python-3.11", entry_point="vectorized_multiply")
   AS r'''
   import pandas as pd
   
   def vectorized_multiply(df: pd.DataFrame):
     return df['x'] * df['y']
   
   ''';

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

   Calling the UDF is the same as in the previous example.

3. Click play_circle_filled Run.

Use Apache Arrow

The following example uses the Apache Arrow RecordBatch interface. When you use the RecordBatch interface, the function passes a batch of rows of columns of equal length to the entrypoint. The following example uses Apache Arrow to create a vectorized Python UDF named multiplyVectorizedArrow.

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`.multiplyVectorizedArrow(x FLOAT64, y FLOAT64)
   RETURNS FLOAT64
   LANGUAGE python
   OPTIONS(
     runtime_version="python-3.11",
     entry_point="vectorized_multiply_arrow"
   )
   AS r'''
   import pyarrow as pa
   import pyarrow.compute as pc
   
   def vectorized_multiply_arrow(batch: pa.RecordBatch):
       # Access columns directly from the Arrow RecordBatch
       x = batch.column('x')
       y = batch.column('y')
   
       # Use pyarrow.compute for vectorized operations
       return pc.multiply(x, y)
   ''';

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

   Calling the UDF is the same as in the previous examples.

3. Click play_circle_filled Run.

Call a Python UDF

If you have permission to invoke a Python UDF, then you can call it like any other function. To use a function defined in a different project, use the fully qualified name for the function. For example, to call an XML extraction function named cw_xml_extract in another project, complete the following steps.

import textwrap
from typing import Tuple

import bigframes.pandas as bpd
import pandas as pd
import pyarrow as pa


# Using partial ordering mode enables more efficient query optimizations.
bpd.options.bigquery.ordering_mode = "partial"


def call_python_udf(
    project_id: str, location: str,
) -> Tuple[pd.Series, bpd.Series]:
    # Set the billing project to use for queries. This step is optional, as the
    # project can be inferred from your environment in many cases.
    bpd.options.bigquery.project = project_id  # "your-project-id"

    # Since this example works with local data, set a processing location.
    bpd.options.bigquery.location = location  # "US"

    # Create a sample series.
    xml_series = pd.Series(
        [
            textwrap.dedent(
                """
                <book id="1">
                    <title>The Great Gatsby</title>
                    <author>F. Scott Fitzgerald</author>
                </book>
                """
            ),
            textwrap.dedent(
                """
                <book id="2">
                    <title>1984</title>
                    <author>George Orwell</author>
                </book>
                """
            ),
            textwrap.dedent(
                """
                <book id="3">
                    <title>Brave New World</title>
                    <author>Aldous Huxley</author>
                </book>
                """
            ),
        ],
        dtype=pd.ArrowDtype(pa.string()),
    )
    df = pd.DataFrame({"xml": xml_series})

    # Use the BigQuery Accessor, which is automatically registered on pandas
    # DataFrames when you import bigframes.  This example uses a function that
    # has been deployed to bigquery-utils for demonstration purposes. To use in
    # production, deploy the function at
    # https://github.com/GoogleCloudPlatform/bigquery-utils/blob/master/udfs/community/cw_xml_extract.sqlx
    # to your own project.
    titles_pandas = df.bigquery.sql_scalar(
        "`bqutil`.`fn`.cw_xml_extract({xml}, '//title/text()')",
    )

    # Alternatively, call read_gbq_function to get a pointer to the function
    # that can be applied on BigQuery DataFrames objects.
    cw_xml_extract = bpd.read_gbq_function("bqutil.fn.cw_xml_extract")
    xml_bigframes = bpd.read_pandas(xml_series)

    xpath_query = "//title/text()"
    titles_bigframes = xml_bigframes.apply(cw_xml_extract, args=(xpath_query,))
    return titles_pandas, titles_bigframes

Supported Python UDF data types

The following table defines the mapping between BigQuery data types, Python data types, and Pandas data types:

Supported runtime versions

BigQuery Python UDFs support the python-3.11 runtime. This Python version includes some additional pre-installed packages. For system libraries, check the runtime base image.

Use third-party packages

You can use the CREATE FUNCTION option list to use modules other than those provided by the Python standard library and pre-installed packages. You can install packages from the Python Package Index (PyPI), or you can import Python files from Cloud Storage.

Install a package from the Python package index

When you install a package, you must provide the package name, and you can optionally provide the package version using Python package version specifiers.

If the package is in the runtime, that package is used unless a particular version is specified in the CREATE FUNCTION option list. If a package version is not specified, and the package isn't in the runtime, the latest available version is used. Only packages with the wheels binary format are supported.

The following example shows you how to create a Python UDF that installs the scipy package using the CREATE OR REPLACE FUNCTION option list:

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`.area(radius FLOAT64)
   RETURNS FLOAT64 LANGUAGE python
   OPTIONS (entry_point='area_handler', runtime_version='python-3.11', packages=['scipy==1.15.3'])
   AS r"""
   import scipy
   
   def area_handler(radius):
     return scipy.constants.pi*radius*radius
   """;
   
   SELECT `PROJECT_ID.DATASET_ID`.area(4.5);

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

3. Click play_circle_filled Run.

Import additional Python files as libraries

You can extend your Python UDFs using the Function option list by importing Python files from Cloud Storage.

In your UDF's Python code, you can import the Python files from Cloud Storage as modules by using the import statement followed by the path to the Cloud Storage object. For example, if you are importing gs://BUCKET_NAME/path/to/lib1.py, then your import statement would be import path.to.lib1.

The Python filename needs to be a Python identifier. Each folder name in the object name (after the /) should be a valid Python identifier. Within the ASCII range (U+0001..U+007F), the following characters can be used in identifiers:

• Uppercase and lowercase letters A through Z.
• Underscores.
• The digits zero through nine, but a number cannot appear as the first character in the identifier.

The following example shows you how to create a Python UDF that imports the lib1.py client library package from a Cloud Storage bucket named my_bucket:

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`.myFunc(a FLOAT64, b STRING)
   RETURNS STRING LANGUAGE python
   OPTIONS (
   entry_point='compute', runtime_version='python-3.11',
   library=['gs://BUCKET_NAME/PATH/lib1.py'])
   AS r"""
   import path.to.lib1 as lib1
   
   def compute(a, b):
     # doInterestingStuff is a function defined in
     # gs://BUCKET_NAME/PATH/lib1.py
     return lib1.doInterestingStuff(a, b);
   
   """;

   Replace the following:

   • PROJECT_ID: your project ID.
   • DATASET_ID: your dataset ID.
   • BUCKET_NAME: the name of the Cloud Storage bucket that contains lib1.py.
   • PATH: the path to the Cloud Storage bucket.

3. Click play_circle_filled Run.

Configure container limits for Python UDFs

You can use the CREATE FUNCTION option list to specify CPU, memory, and container request concurrency limits for containers that run Python UDFs.

By default, containers are allocated the following resources:

• The memory allocated is 512Mi.
• The CPU allocated is 1.0 vCPU.
• The container request concurrency limit is 80.

The following example creates a Python UDF using the CREATE FUNCTION option list to specify container limits:

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`.square_area(length FLOAT64)
   RETURNS FLOAT64 LANGUAGE python
   OPTIONS (entry_point='square_area', runtime_version='python-3.11',
   container_memory='CONTAINER_MEMORY', container_cpu=CONTAINER_CPU, container_request_concurrency=CONTAINER_REQUEST_CONCURRENCY)
   AS r"""
   def square_area(length):
     return length*length
   """;
   
   SELECT `PROJECT_ID.DATASET_ID`.square_area(4.5);

   Replace the following:

   • PROJECT_ID.DATASET_ID: your project ID and dataset ID.
   • CONTAINER_MEMORY: the memory value in the following format: <integer_number><unit>. The unit must be one of these values: Mi (MiB), M (MB), Gi (GiB), or G (GB). For example, 2Gi.
   • CONTAINER_CPU: the CPU value. Python UDFs support fractional CPU values between 0.33 and 1.0 and non-fractional CPU values of 1, 2, and 4.
   • CONTAINER_REQUEST_CONCURRENCY: the maximum number of concurrent requests per Python UDF container instance. The value must be an integer from 1 to 1000.

3. Click play_circle_filled Run.

Supported CPU values

Python UDFs support fractional CPU values between 0.33 and 1.0 and non-fractional CPU values of 1, 2, and 4. Containers that run Python UDFs can be configured up to 4 vCpu. The default value is 1.0. Fractional input values are rounded to two decimal places before they're applied to the container.

Supported memory values

Python UDF containers support memory values in the following format: <integer_number><unit>. The unit must be one of these values: Mi, M, Gi, G. The minimum amount of memory you can configure is 256Mi. The maximum amount of memory you can configure is 16Gi.

Based on the memory value you choose, you must also specify an appropriate amount of CPU. The following table shows the minimum and maximum CPU values for each memory value:

Alternatively, if you've determined the amount of CPU you're allocating, you can use the following table to determine the appropriate memory range:

Call Cloud de Confiance by S3NS or online services in Python code

A Python UDF accesses a Cloud de Confiance by S3NS service or an external service by using the Cloud resource connection service account. The connection's service account must be granted permissions to access the service. The permissions required vary depending on the service that is accessed and the APIs that are called from your Python code.

If you create a Python UDF without using a Cloud resource connection, the function is executed in an environment that blocks network access. If your UDF accesses online services, you must create the UDF with a Cloud resource connection. If you don't, the UDF is blocked from accessing the network until an internal connection timeout is reached. When you use a Cloud resource connection, implement the following:

• Timeouts. When you make network calls within your Python UDF, always include a reasonable timeout. This prevents the UDF from hanging indefinitely if the external service is slow to respond or is unreachable.

• Use Error Handling. Wrap your network call code in a try...except block to gracefully handle potential errors, such as connection errors, timeouts, or HTTP failure status codes. This allows your UDF to return a meaningful error or a fallback value instead of causing the query to fail or stop responding.

The following example shows you how to access the Cloud Translation service from a Python UDF. This example has two projects—a project named my_query_project where you create the UDF and the Cloud resource connection, and a project where you are running the Cloud Translation named my_translate_project.

Create a Cloud resource connection

First, you create a Cloud resource connection in my_query_project. To create the cloud resource connection, follow these steps.

import google.api_core.exceptions
from google.cloud import bigquery_connection_v1

client = bigquery_connection_v1.ConnectionServiceClient()


def create_connection(
    project_id: str,
    location: str,
    connection_id: str,
):
    """Creates a BigQuery connection to a Cloud Resource.

    Cloud Resource connection creates a service account which can then be
    granted access to other Google Cloud resources for federated queries.

    Args:
        project_id: The Google Cloud project ID.
        location: The location of the connection (for example, "us-central1").
        connection_id: The ID of the connection to create.
    """

    parent = client.common_location_path(project_id, location)

    connection = bigquery_connection_v1.Connection(
        friendly_name="Example Connection",
        description="A sample connection for a Cloud Resource.",
        cloud_resource=bigquery_connection_v1.CloudResourceProperties(),
    )

    try:
        created_connection = client.create_connection(
            parent=parent, connection_id=connection_id, connection=connection
        )
        print(f"Successfully created connection: {created_connection.name}")
        print(f"Friendly name: {created_connection.friendly_name}")
        print(
            f"Service Account: {created_connection.cloud_resource.service_account_id}"
        )

    except google.api_core.exceptions.AlreadyExists:
        print(f"Connection with ID '{connection_id}' already exists.")
        print("Please use a different connection ID.")
    except Exception as e:
        print(f"An unexpected error occurred while creating the connection: {e}")

const {ConnectionServiceClient} =
  require('@google-cloud/bigquery-connection').v1;
const {status} = require('@grpc/grpc-js');

const client = new ConnectionServiceClient();

/**
 * Creates a new BigQuery connection to a Cloud Resource.
 *
 * A Cloud Resource connection creates a service account that can be granted access
 * to other Google Cloud resources.
 *
 * @param {string} projectId The Google Cloud project ID. for example, 'example-project-id'
 * @param {string} location The location of the project to create the connection in. for example, 'us-central1'
 * @param {string} connectionId The ID of the connection to create. for example, 'example-connection-id'
 */
async function createConnection(projectId, location, connectionId) {
  const parent = client.locationPath(projectId, location);

  const connection = {
    friendlyName: 'Example Connection',
    description: 'A sample connection for a Cloud Resource',
    // The service account for this cloudResource will be created by the API.
    // Its ID will be available in the response.
    cloudResource: {},
  };

  const request = {
    parent,
    connectionId,
    connection,
  };

  try {
    const [response] = await client.createConnection(request);

    console.log(`Successfully created connection: ${response.name}`);
    console.log(`Friendly name: ${response.friendlyName}`);

    console.log(`Service Account: ${response.cloudResource.serviceAccountId}`);
  } catch (err) {
    if (err.code === status.ALREADY_EXISTS) {
      console.log(`Connection '${connectionId}' already exists.`);
    } else {
      console.error(`Error creating connection: ${err.message}`);
    }
  }
}

# This queries the provider for project information.
data "google_project" "default" {}

# This creates a cloud resource connection in the US region named my_cloud_resource_connection.
# Note: The cloud resource nested object has only one output field - serviceAccountId.
resource "google_bigquery_connection" "default" {
  connection_id = "my_cloud_resource_connection"
  project       = data.google_project.default.project_id
  location      = "US"
  cloud_resource {}
}

Grant access to the connection's service account

You need the service account ID you copied previously when you configure permissions for the connection. When you create a connection resource, BigQuery creates a unique system service account and associates it with the connection.

To grant the Cloud resource connection service account access to your projects, grant the service account the Service usage consumer role (roles/serviceusage.serviceUsageConsumer) in my_query_project and the Cloud Translation API user role (roles/cloudtranslate.user) in my_translate_project.

Create a Python UDF that calls the Cloud Translation service

In my_query_project, create a Python UDF that calls the Cloud Translation service using your Cloud resource connection.

1. In the Cloud de Confiance console, go to the BigQuery page.

   Go to BigQuery

2. Enter the following CREATE FUNCTION statement in the query editor:

   CREATE FUNCTION `PROJECT_ID.DATASET_ID`.translate_to_es(x STRING)
   RETURNS STRING LANGUAGE python
   WITH CONNECTION `PROJECT_ID.REGION.CONNECTION_ID`
   OPTIONS (entry_point='do_translate', runtime_version='python-3.11', packages=['google-cloud-translate>=3.11', 'google-api-core'])
   AS r"""
   
   from google.api_core.retry import Retry
   from google.cloud import translate
   
   project = "my_translate_project"
   translate_client = translate.TranslationServiceClient()
   
   def do_translate(x : str) -> str:
   
       response = translate_client.translate_text(
           request={
               "parent": f"projects/PROJECT_ID/locations/us-central1",
               "contents": [x],
               "target_language_code": "es",
               "mime_type": "text/plain",
           },
           retry=Retry(),
       )
       return response.translations[0].translated_text
   
   """;
   
   -- Call the UDF.
   WITH text_table AS
     (SELECT "Hello" AS text
     UNION ALL
     SELECT "Good morning" AS text
     UNION ALL
     SELECT "Goodbye" AS text)
   SELECT text,
   `PROJECT_ID.DATASET_ID`.translate_to_es(text) AS translated_text
   FROM text_table;

   Replace the following:

   • PROJECT_ID: the project ID.
   • DATASET_ID: the dataset ID.
   • REGION: your connection's region.
   • CONNECTION_ID: the connection ID.

3. Click play_circle_filled Run.

   The output should look like the following:

   +--------------------------+-------------------------------+
   | text                     | translated_text               |
   +--------------------------+-------------------------------+
   | Hello                    | Hola                          |
   | Good morning             | Buen dia                      |
   | Goodbye                  | Adios                         |
   +--------------------------+-------------------------------+
   

Use VPC Service Controls

Python UDFs inherit the VPC Service Controls perimeter of the project that runs the query job. This perimeter protects your jobs from data exfiltration, and it ensures that service interactions are secure.

When you invoke a Python UDF inside the VPC Service Controls perimeter, it has the following network connectivity:

• Python UDFs that don't use a Cloud resource connection are fully isolated. All outbound traffic is blocked.
• Python UDFs that use a Cloud resource connection are blocked from public internet access. Python UDFs can only access Cloud de Confiance by S3NS services that support VPC Service Controls. Outbound traffic to any destination other than restricted.googleapis.com is blocked.

Configure Python UDFs to access Cloud de Confiance by S3NS services securely within VPC Service Controls

To access Cloud de Confiance by S3NS services from Python UDFs while enforcing VPC Service Controls, follow these steps:

1. Create the Python UDF using the CREATE FUNCTION statement's WITH CONNECTION clause.
2. Include the BigQuery project where the query job runs and the target service project in the service perimeter. Alternatively, configure a perimeter bridge.
3. Add the target service API to the perimeter configuration. For example, translate.googleapis.com if you're connecting to the Cloud Translation API.

For more details on configuring a VPC Service Controls perimeter, see:

• VPC Service Controls for BigQuery
• Overview of VPC Service Controls

Best practices

When you create Python UDFs, follow these best practices:

• Optimize your query logic for batching. Complex query structures can disable batching. This forces slow, row-by-row processing, which significantly increases latency on large datasets.
• Optimize the data payload. The size of individual rows can impact the efficiency of the batching feature. Keep each row as small as possible to maximize the number of rows that can be processed in a single batch.
• Configure container limits efficiently. Scalability is a function of CPU, memory, and request concurrency. Check monitoring metrics to tune the container configuration. If CPU utilization is high, increase CPU allocation using the container_cpu limit, or reduce container request concurrency using the container_request_concurrency limit.
• When you use iterative tuning, start with default values. If performance is suboptimal, analyze monitoring metrics to identify specific bottlenecks.
• Implement API timeouts. When your Python UDF accesses the internet, set a timeout on the API call to avoid unexpected behavior. An example of internet access is reading from a Cloud Storage bucket.

View Python UDF metrics

Python UDFs export metrics to Cloud Monitoring. These metrics help you monitor various aspects of your UDF's operational health and resource consumption, providing insights into the performance and behavior of your UDF instances.

Monitoring resource type

The metrics for Python UDFs are reported under the following Cloud Monitoring resource type:

• Type: bigquery.googleapis.com/ManagedRoutineInvocation
• Display Name: BigQuery Managed Routine Invocation
• Labels:
  • resource_container: the ID of the project where the query job ran.
  • location: the location where the query job ran.
  • query_job_id: the ID of the query job that invoked the Python UDF.
  • routine_project_id: the project ID where the invoked routine is stored.
  • routine_dataset_id: the dataset ID where the invoked routine is stored.
  • routine_id: the ID of the invoked routine.

Metrics

The following metrics are available for the bigquery.googleapis.com/ManagedRoutineInvocation resource type:

View metrics

To view the metrics for your Python UDFs, choose one of the options in the following sections.

Job details

To view Python UDF metrics for a specific query job, follow these steps:

1. Go to the BigQuery page.

   Go to BigQuery

2. Click Job history.

3. In the Job ID column, click the query job ID.

4. On the Query job details page, click Cloud Monitoring dashboard. This link displays a dashboard that is filtered to show the Python UDF metrics for the job.

Metrics Explorer

To view Python UDF metrics in the Metrics Explorer, follow these steps:

1. Go to the Cloud Monitoring Metrics explorer page.

   Go to Metrics explorer

2. Click Select a metric, and in the Filter field, type BigQuery Managed Routine Invocation or bigquery.googleapis.com/ManagedRoutineInvocation.

3. Choose Bigquery Managed Routine > Managed_routine.

4. Click any of the available metrics such as the following:

   • Instance CPU utilization
   • Instance memory utilization
   • Max concurrent requests

5. Click Apply.

   By default, the metrics are displayed in a chart.

6. You can filter and group the metrics using the labels defined in the Monitoring resource types. To filter the metrics, follow these steps:

   1. In the Filter field choose a resource type such as query_job_id or routine_id.

   2. In the Value field, enter the job ID or routine ID, or choose one from the list.

Cloud Monitoring dashboards

To view Python UDF metrics using the monitoring dashboards, follow these steps:

1. Go to the Cloud Monitoring Dashboards page.

   Go to Dashboards

2. Click the BigQuery Managed Routine Query Monitoring dashboard.

   This dashboard provides an overview of key metrics across your UDFs.

3. To filter this dashboard, follow these steps:

   1. Click addFilter.

   2. In the Filter by resource list, choose an option such as project ID, location, routine ID, or job ID.

Supported locations

Python UDFs are supported in all BigQuery multi-region and regional locations.

Pricing

Python UDF charges are billed using the BigQuery Services SKU.

Charges include the following:

• Building or rebuilding the UDF container image. This charge is proportional to the duration required to build the corresponding image with customer code and dependencies.

  • If you're using the Routines API, the latest build duration is in the BuildStatus field. You can also view the build duration in the BuildStatus column in the INFORMATION_SCHEMA.ROUTINES view.
  • To view the total cost of builds per project, you can filter your billing report by using the following:
    • Key: goog-bq-feature-type
    • Value: MANAGED_ROUTINE_BUILD

• Python UDF customers are also charged for the cost of invoking a Python UDF. This charge is proportional to the amount of compute and memory consumed when the Python UDF is invoked.

  • To view Python UDF costs per query, you can query the ExternalServiceCosts field using the Job API. You can also view costs per query by viewing the external_service_costs column in the INFORMATION_SCHEMA.JOBS view and applying the following filter: 'external_service_costs.external_service="MANAGED_ROUTINE_EXECUTION"'.
  • To view the total cost of running Python UDFs per project, you can filter the billing report by using the following:
    • Key: goog-bq-feature-type
    • Value: MANAGED_ROUTINE_EXECUTION

• If Python UDFs result in external or internet network egress, you also see a Premium Tier internet egress charge based on the BigQuery Egress SKUs.

Quotas

See UDF quotas and limits.