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

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

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

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

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

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

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

   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 Google Cloud or online services in Python code

A Python UDF accesses a Google Cloud 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 Google Cloud console, go to the BigQuerypage.

   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 Google Cloud services that support VPC Service Controls. Outbound traffic to any destination other than restricted.googleapis.com is blocked.

Configure Python UDFs to access Google Cloud services securely within VPC Service Controls

To access Google Cloud 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 BigQuerypage.

   Go to BigQuery

2. Click Job history.

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

4. On the Query job detailspage, 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 explorerpage.

   Go to Metrics explorer

2. Click Select a metric, and in the Filterfield, 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 Filterfield choose a resource type such as query_job_id or routine_id .

   2. In the Valuefield, 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 Dashboardspage.

   Go to Dashboards

2. Click the BigQuery Managed Routine Query Monitoringdashboard.

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

3. To filter this dashboard, follow these steps:

   1. Click add Filter.

   2. In the Filter by resourcelist, 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 .