BigQuery Python UDFs Go GA: SQL Meets Python Power

BigQuery just got a whole lot friendlier for Pythonistas. Google Cloud has announced the general availability of Managed Python User-Defined Functions (UDFs) in BigQuery, letting you run custom Python code directly inside your SQL queries without spinning up a single container. It’s like inviting a capybara into your swamp—everything just works better together.

Why This Matters

SQL is great for structured data, but complex logic, scientific computing, or ML workflows can be a pain. Previously, you’d have to manage custom images, containers, and extra compute services just to run a simple Python helper. Now, BigQuery handles all that serverless infrastructure—compilation, image building, security patching, deployment, and execution—so you can focus on the fun stuff.

Core Benefits

• Flexibility: Tap into the vast Python ecosystem—NumPy, SciPy, pandas, scikit-learn—directly in your SQL SELECT statements.
• Tight API Integration: Clean and enrich data in real time by calling external web APIs or Google Cloud services like Cloud Translation or Gemini.
• Fully Managed & Serverless: BigQuery auto-scales to billions of rows, no infrastructure to manage.

Code Example: Clean HTML with BeautifulSoup

Here’s a Python UDF that strips HTML tags from StackOverflow answers:

CREATE OR REPLACE FUNCTION `your_project.your_dataset.clean_html`(html_content STRING)
RETURNS STRING
LANGUAGE python
OPTIONS (
  runtime_version = 'python-3.11',
  entry_point = 'strip_tags',
  packages = ['beautifulsoup4>=4.12.0']
) AS '''
from bs4 import BeautifulSoup

def strip_tags(html_content):
    if not html_content:
        return ""
    soup = BeautifulSoup(html_content, "html.parser")
    return soup.get_text(separator=" ")
''';

Query it like this:

SELECT id, `your_project.your_dataset.clean_html`(body) AS cleaned_answer_body
FROM `bigquery-public-data.stackoverflow.posts_answers`
LIMIT 100;

Advanced Capabilities

Vectorized Processing with PyArrow

Process columns in bulk using PyArrow RecordBatches, boosting performance up to 10x for data-intensive calculations.

Configurable Container Resources

Provision up to 16 GB memory and 4 vCPUs per function for heavy-duty ML data prep.

Customizable Concurrency

Configure up to 1,000 concurrent requests per container for cost-effective scale-out.

Streaming Logs & Real-Time Metrics

Debug and monitor with direct links to CPU, memory, and concurrency metrics in Cloud Monitoring.

Billing

Python UDFs are billed under the BigQuery Services SKU, eligible for committed-use discounts (CUDs). Track costs via INFORMATION_SCHEMA.JOBS and billing labels.

Getting Started

Check out the product documentation and try the public dataset with a tokenizer UDF. Or dive into the code lab for advanced scenarios.

Bring your Python workflows out of isolation and into the heart of your data warehouse. For more on Google Cloud tools, check out our Google Cloud review.

Original announcement published on Google Cloud.