BigQuery is widely used for analyzing large datasets with SQL. It provides powerful functions for filtering, aggregating, joining, and transforming data without requiring developers to manage database servers.
However, some data-processing requirements are difficult to express using SQL alone.
Consider an application that processes customer reviews, product descriptions, financial records, or scientific measurements. The data team may need specialized text processing, mathematical calculations, statistical operations, or machine learning libraries.
Python is often a better choice for these operations because of its extensive ecosystem of libraries.
Traditionally, integrating Python processing into a BigQuery workflow could require exporting data, running Python code on virtual machines or containers, and loading the processed results back into BigQuery.
This creates additional infrastructure, data movement, and operational overhead.
BigQuery Managed Python User-Defined Functions (UDFs) provide another approach. They allow developers to write custom Python functions and invoke them directly from GoogleSQL queries. BigQuery manages the underlying execution environment, container builds, and deployment.
This makes it possible to combine SQL-based analytics with Python-based processing without maintaining a separate Python execution platform.
Understanding Managed Python UDFs in BigQuery
A User-Defined Function is a reusable function that developers create to extend the capabilities of a database query.
BigQuery already supports SQL and JavaScript UDFs. Managed Python UDFs add Python as another option.
A Python UDF contains a Python function that BigQuery executes when a query calls it.
For example, suppose a customer analytics team stores product prices in a BigQuery table and needs to calculate a discount-adjusted price using custom Python logic.
Instead of extracting the data into a separate Python application, the team can define a Python function and call it directly from SQL.
The architecture looks like this:
BigQuery Dataset
|
v
SQL Query
|
v
Managed Python UDF
|
v
BigQuery Managed Execution
|
v
Python Function Executes
|
v
Result Returned to SQL
|
v
Query Results
The important difference is that developers do not need to create a separate virtual machine, Kubernetes deployment, or Cloud Run service just to execute a custom Python function.
BigQuery manages the execution infrastructure.
The developer focuses on the Python logic and the SQL query that invokes it.
Why Use Python UDFs Instead of Exporting Data?
Consider a company that processes millions of product descriptions.
The business wants to normalize text, remove unwanted characters, and classify descriptions before running analytics.
A traditional architecture might look like this:
BigQuery Table
|
v
Export Data
|
v
Python Application
|
v
Process Data
|
v
Upload Processed Data
|
v
BigQuery Table
|
v
Analytics Query
This approach can work, but it introduces several responsibilities.
The team must maintain the Python execution environment, manage dependencies, configure authentication, handle data transfers, monitor processing jobs, and manage failures between systems.
With a managed Python UDF, the processing logic can run as part of a BigQuery query.
BigQuery Table
|
v
GoogleSQL Query
|
v
Managed Python UDF
|
v
Transformed Results
|
v
BigQuery Output
The data-processing workflow becomes simpler because the custom Python logic executes on BigQuery-managed resources.
This is particularly useful when the processing operation is closely connected to analytical queries.
However, Python UDFs are not a replacement for every Python data-processing workload. Large application workflows, long-running processes, and tasks that require persistent state may still be better suited to dedicated compute services.
Creating Your First Managed Python UDF
The easiest way to understand managed Python UDFs is to create a simple function and invoke it from a SQL query.
First, open the BigQuery console and select the project and dataset where the function should be created.
A Python UDF is a persistent function, not a temporary function.
The following example creates a function that calculates a discounted product price.
CREATE OR REPLACE FUNCTION
`my-project.analytics.calculate_discount`(
price FLOAT64,
discount_percent FLOAT64
)
RETURNS FLOAT64
LANGUAGE python
OPTIONS (
runtime_version = 'python-3.11',
entry_point = 'calculate'
)
AS r'''
def calculate(price, discount_percent):
if price is None or discount_percent is None:
return None
if price < 0 or discount_percent < 0 or discount_percent > 100:
raise ValueError("Invalid price or discount percentage")
return price * (1 - discount_percent / 100)
''';
Replace my-project and analytics with your actual Google Cloud project and BigQuery dataset.
There are several important parts of this statement.
CREATE OR REPLACE FUNCTION creates the persistent Python UDF or replaces an existing definition.
The function signature defines the input and output types.
LANGUAGE python tells BigQuery that the function body contains Python code.
OPTIONS specifies the Python runtime and the Python function that BigQuery should execute.
The entry_point value must match the name of the function defined inside the Python code.
The AS clause contains the Python implementation.
Once the function is created, call it from a normal SQL query:
SELECT
product_id,
product_price,
calculate_discount(product_price, 15)
AS discounted_price
FROM
`my-project.analytics.products`;
BigQuery executes the Python function for the supplied values and returns the results as part of the query.
The same UDF can be reused in multiple queries without rewriting the Python implementation.
Using Python Packages Without Managing a Custom Container
One of the biggest advantages of managed Python UDFs is access to the Python package ecosystem.
Developers can use supported third-party packages from PyPI without building and maintaining their own execution container.
For example, a data science team may need SciPy for mathematical calculations.
With a traditional Python deployment, the team would need to install the dependency, build an environment, package the application, and deploy it somewhere.
A managed Python UDF can declare the dependency directly in its SQL definition.
Consider calculating the area of a circle using SciPy:
CREATE OR REPLACE FUNCTION
`my-project.analytics.calculate_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):
if radius is None:
return None
if radius < 0:
raise ValueError("Radius cannot be negative")
return scipy.constants.pi * radius * radius
""";
The packages option specifies the external Python dependency.
The version is explicitly pinned to make the environment more predictable.
When BigQuery creates the function, it builds a managed container using the Python code and declared dependencies.
The developer does not need to write a Dockerfile, configure a container registry, or deploy a separate Python service.
This is useful for applications that need libraries such as NumPy, SciPy, pandas, and other supported Python packages.
Only packages available in compatible wheel distributions are supported. If a dependency requires an unsupported native build process, it may not work in the managed environment.
Also, package versions should be pinned rather than relying on whichever version happens to be latest when the function is rebuilt.
Understanding How BigQuery Builds Python UDF Containers
Although developers do not manage the containers directly, BigQuery still needs an execution environment for the Python code.
When a Python UDF is created or updated, BigQuery builds a container image based on its managed runtime and the specified dependencies.
The process includes:
- Preparing the Python execution environment.
- Installing declared dependencies.
- Building the container image.
- Making the execution environment available for queries.
The first query that invokes a newly created UDF may need to wait for the container build to complete.
If the function has no external dependencies, the initial build is typically completed in less than a minute. Dependencies can increase the build time.
Developers can inspect the build status through BigQuery Studio.
Open the dataset containing the function and navigate to the Routines tab.
Select the Python UDF to view its build status, build duration, and image size.
The status can be one of the following:
| Build status | Meaning |
|---|
| IN_PROGRESS | BigQuery is building the execution container |
| SUCCEEDED | The container build completed successfully |
| FAILED | The build encountered an error |
Build information is also available through INFORMATION_SCHEMA.ROUTINES.
For example:
SELECT
build_status.*
FROM
`my-project.analytics.INFORMATION_SCHEMA.ROUTINES`;
This is useful when managing several Python UDFs across development and production datasets.
The team can inspect build failures without manually accessing the underlying container infrastructure.
Processing Multiple Rows With Vectorized Python UDFs
A standard scalar Python UDF processes individual input values.
This is straightforward for simple operations, but calling Python logic repeatedly for large datasets can introduce processing overhead.
BigQuery also supports vectorized Python UDFs.
Instead of processing one row at a time, a vectorized function receives a batch of rows.
Developers can implement vectorized UDFs using pandas or Apache Arrow.
Using pandas for vectorized processing
Consider a dataset containing product prices and discount percentages.
The application needs to calculate discounted prices for many records.
A vectorized Python UDF can process a DataFrame containing multiple rows.
CREATE OR REPLACE FUNCTION
`my-project.analytics.calculate_discount_batch`(
price FLOAT64,
discount_percent FLOAT64
)
RETURNS FLOAT64
LANGUAGE python
OPTIONS (
runtime_version = 'python-3.11',
entry_point = 'vectorized_discount'
)
AS r'''
import pandas as pd
def vectorized_discount(df: pd.DataFrame):
return df['price'] * (
1 - df['discount_percent'] / 100
)
''';
The important difference is that the Python function receives a pandas DataFrame rather than individual scalar arguments.
The DataFrame contains columns corresponding to the function parameters.
The function returns a pandas Series or a single-column DataFrame with the same number of rows as the input.
The function can then be called like a normal UDF:
SELECT
product_id,
calculate_discount_batch(
price,
discount_percent
) AS discounted_price
FROM
`my-project.analytics.products`;
BigQuery determines the batch size automatically unless the max_batching_rows option is specified.
Vectorization can improve performance for suitable workloads because operations can be performed on batches instead of repeatedly invoking scalar Python logic.
However, it is not guaranteed to be faster for every function. The result depends on the operation, batch size, memory consumption, and data distribution.
Using Apache Arrow
Apache Arrow provides another option for vectorized processing.
It is designed for efficient columnar data representation and is useful when working with large datasets.
An Arrow-based UDF receives a RecordBatch containing columns of equal length.
For example:
CREATE OR REPLACE FUNCTION
`my-project.analytics.multiply_vectorized`(
x FLOAT64,
y FLOAT64
)
RETURNS FLOAT64
LANGUAGE python
OPTIONS (
runtime_version = 'python-3.11',
entry_point = 'vectorized_multiply'
)
AS r"""
import pyarrow as pa
import pyarrow.compute as pc
def vectorized_multiply(batch: pa.RecordBatch):
x = batch.column('x')
y = batch.column('y')
result = pc.multiply(x, y)
return pa.RecordBatch.from_arrays(
[result],
names=['product']
)
""";
The returned RecordBatch must contain exactly one column with the same number of rows as the input batch.
For applications already using Apache Arrow, this approach can provide an efficient way to integrate custom processing with BigQuery.
Choose pandas when its DataFrame APIs make the implementation easier to understand. Choose Arrow when columnar operations and data-processing efficiency are important.
Calling External Services From Python UDFs
Managed Python UDFs can also interact with external services.
For example, a data-processing workflow may need to translate product descriptions, enrich customer records, or call a specialized service.
However, Python UDFs do not automatically have unrestricted network access.
If a Python UDF is created without a Cloud resource connection, outbound network access is blocked.
To access supported Google Cloud services or external services, the function must use a Cloud resource connection through the WITH CONNECTION clause.
The connection provides an identity that the Python code can use to access the target service.
Consider a function that translates text using Cloud Translation.
First, create a Cloud resource connection in the same location as the BigQuery resources.
Then grant the connection's service account the required Cloud Translation permissions.
The function can use the connection as follows:
CREATE OR REPLACE FUNCTION
`my-project.analytics.translate_to_spanish`(
input_text STRING
)
RETURNS STRING
LANGUAGE python
WITH CONNECTION
`my-project.us-central1.translation_connection`
OPTIONS (
entry_point = 'translate_text',
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
def translate_text(input_text):
if input_text is None:
return None
client = translate.TranslationServiceClient()
response = client.translate_text(
request={
"parent": "projects/my-project/locations/us-central1",
"contents": [input_text],
"target_language_code": "es",
"mime_type": "text/plain"
},
retry=Retry(),
timeout=30
)
return response.translations[0].translated_text
""";
Replace the project ID, dataset, Region, and connection name with the values from your environment.
The WITH CONNECTION clause tells BigQuery to execute the function using the configured Cloud resource connection.
The connection's service account must have permission to access the target service.
For external API calls, always configure reasonable timeouts. A slow or unavailable external service should not cause a query to wait indefinitely.
Also consider the cost and latency of calling an external service for many rows. A Python UDF that invokes an API repeatedly can become expensive and slow even when the infrastructure is managed.
Managing IAM Permissions for Python UDFs
Managed execution does not eliminate the need for IAM configuration.
There are two different permission requirements: creating a Python UDF and invoking an existing Python UDF.
Permissions required to create a UDF
A developer who creates or updates a Python UDF typically needs:
- BigQuery Data Editor on the target dataset.
- BigQuery Job User on the project.
- BigQuery Connection Admin on the project when managing Cloud resource connections.
The exact permissions can also be provided through appropriately configured custom roles.
Permissions required to invoke a UDF
A developer who uses an existing Python UDF typically needs:
- BigQuery User on the query project.
- BigQuery Data Viewer on the dataset containing the UDF.
- BigQuery Connection User when the function uses a Cloud resource connection.
The permissions should be granted according to the developer's responsibilities.
For example, analysts who only execute queries should not automatically receive permission to modify production routines.
Separating function ownership from function usage makes it easier to manage access in larger data teams.
Understanding the Limitations of Managed Python UDFs
Managed Python UDFs remove infrastructure-management work, but they still have technical limitations.
Understanding these restrictions before adopting the feature helps avoid unexpected problems.
Python runtime support
The currently supported Python runtime is Python 3.11.
Developers cannot select an arbitrary Python version for a managed UDF.
Applications that depend on a newer language runtime should verify compatibility before choosing this execution model.
Temporary functions are not supported
Python UDFs must be created as persistent functions.
Unlike temporary SQL or JavaScript UDFs, they cannot be created as temporary Python functions.
They are stored as routines inside a BigQuery dataset.
Materialized views are not supported
Python UDFs cannot be used with materialized views.
If the application requires materialized-view-based query acceleration, keep the transformation in supported SQL expressions or use another processing architecture.
Query results are not cached
BigQuery assumes that Python UDF results are non-deterministic.
As a result, query results that call Python UDFs are not cached.
Repeatedly running the same query may therefore execute the Python function again.
This is an important consideration for frequently accessed analytical workloads.
Resource limits
Python UDF execution containers can be configured up to 4 vCPUs and 16 GiB of memory.
This is sufficient for many analytical transformations, but it may not be suitable for workloads requiring large amounts of memory or specialized compute resources.
Unsupported data types
The current Python UDF implementation does not support the BigQuery JSON, RANGE, INTERVAL, and GEOGRAPHY data types.
Review the supported data-type mappings before creating functions that work with complex BigQuery schemas.
Other restrictions include limitations involving customer-managed encryption keys, assured workloads, default connections, and VPC networks.
For applications with strict security requirements, verify the complete feature limitations before choosing managed Python UDFs.
Understanding Security and Network Isolation
Python UDFs execute on BigQuery-managed resources, and their network access is controlled.
Without a Cloud resource connection, outbound network traffic is blocked.
When a connection is used, the function accesses supported services through the connection's service account.
This helps avoid embedding service account keys or API credentials directly inside Python code.
For organizations using VPC Service Controls, Python UDFs inherit the perimeter of the project running the query.
Within that perimeter, functions without a Cloud resource connection remain network-isolated.
Functions using a connection can access supported Google Cloud services according to the configured perimeter rules, but unrestricted public internet access is not provided.
This behavior is important for organizations processing sensitive customer or financial data.
Avoid logging complete input records, personal information, authentication tokens, or other sensitive values.
Logging is disabled by default for Python UDFs. When debugging is necessary, logging can be enabled for a query session:
SET @@udf.properties =
JSON '{"enableDebugOutput": true}';
Python logging calls can then write diagnostic information to Cloud Logging.
Use logging carefully because users with access to those logs may see information that they cannot otherwise access directly through BigQuery tables.
Monitoring Python UDF Builds and Execution Costs
Managed Python UDFs reduce infrastructure-management work, but they are not free.
BigQuery charges for building or rebuilding the UDF container and for executing Python UDFs.
Execution costs depend on the compute and memory consumed by the function.
External network calls may also introduce additional network egress charges.
Developers can inspect container build information through the INFORMATION_SCHEMA.ROUTINES view.
For example:
SELECT
routine_name,
build_status.build_state,
build_status.build_duration_seconds,
build_status.image_size_bytes
FROM
`my-project.analytics.INFORMATION_SCHEMA.ROUTINES`
WHERE
routine_type = 'FUNCTION';
For query-level execution costs, BigQuery exposes external service cost information through INFORMATION_SCHEMA.JOBS.
SELECT
job_id,
creation_time,
external_service_costs
FROM
`region-us`.INFORMATION_SCHEMA.JOBS
WHERE
external_service_costs.external_service =
'MANAGED_ROUTINE_EXECUTION'
ORDER BY
creation_time DESC;
Replace region-us with the appropriate BigQuery location.
These views help teams understand which queries invoke managed Python functions and how much execution cost they introduce.
A useful practice is to compare the cost of a Python UDF with an equivalent SQL implementation before using it across large analytical datasets.
If SQL can perform the transformation efficiently, keeping the operation in SQL may be simpler and less expensive.
When Should You Use Managed Python UDFs?
Managed Python UDFs are useful when a data-processing requirement needs Python functionality but still belongs inside a BigQuery analytical workflow.
Common examples include:
- Advanced text normalization and extraction.
- Mathematical and scientific calculations.
- Specialized data transformations.
- Custom classification logic.
- Data preparation using supported Python libraries.
- Calling supported external services through Cloud resource connections.
They are less suitable for long-running application processes, workflows that require persistent state, or operations that need unrestricted network access.
For large batch-processing jobs that need independent orchestration, a separate Python application or Cloud Run job may be a better choice.
For simple calculations and transformations, native SQL functions should remain the first option.
The objective is to use Python where it provides meaningful value rather than moving every SQL operation into a Python function.
Summary
BigQuery Managed Python UDFs allow developers to execute custom Python code directly inside SQL queries without maintaining separate Python infrastructure.
BigQuery manages the execution containers, dependency installation, builds, and deployment. Developers can create persistent Python functions, use supported PyPI packages, and invoke them from GoogleSQL.
Vectorized UDFs using pandas or Apache Arrow provide additional options for processing batches of rows. Cloud resource connections also make it possible to access supported Google Cloud services and external services securely.
However, the feature has important limitations, including its supported Python runtime, lack of temporary functions, restrictions on materialized views and data types, and the absence of query-result caching for Python UDF calls.
It also introduces execution and container-build costs that should be monitored.
For data teams that need specialized Python processing alongside BigQuery analytics, managed Python UDFs provide a practical way to extend SQL without taking responsibility for a separate Python deployment environment.
Join the conversation! Your thoughts help the community grow.