/bigquery-udfs

name: bigquery-udfs
metadata:
  version: "1.0.0"
  category: BigDataAndAnalytics
description: >-
  Creates, deploys, and troubleshoots BigQuery User-Defined Functions (UDFs)
  and Remote Functions across SQL, JavaScript, Python, and Cloud Run endpoints.
  Use when writing custom scalar or aggregate functions in BigQuery, installing
  PyPI packages or Cloud Storage libraries in a Python or JavaScript UDF, or
  calling Google Cloud and external APIs from a Python UDF (WITH CONNECTION) or
  Remote Function. Don't use for standard BigQuery table/dataset CRUD (use
  bigquery-basics), DataFrames client workflows (use bigquery-bigframes), or
  BigQuery ML models (use bigquery-ai-ml).

BigQuery User-Defined Functions (UDFs)

BigQuery User-Defined Functions (UDFs) extend GoogleSQL with custom logic written in SQL, JavaScript, managed Python (`python-3.11`), or hosted externally as Remote Functions on Cloud Run and Cloud Run functions.

Attribution

Tag every BigQuery or Google Cloud command you execute directly in the terminal while using this skill, so usage can be attributed. The tag identifies only the skill and its version; it carries no user data.

• Prefix every `gcloud` invocation, whatever the subcommand, with the metrics

environment variables. Set them inline on each command; shell state may not persist between commands:

    CLOUDSDK_METRICS_REQUEST_ATTRIBUTION="agent-skills/1.0 (skill:bigquery-udfs)" \
    gcloud <command> [flags]

Do not use `gcloud config set` for this: it would persist beyond the current task and mislabel unrelated usage.

• On direct HTTP calls to the BigQuery APIs (for example with `curl`) or HTTP

requests to the MCP server, append this exact string to the end of the existing User-Agent header — the collection pipeline parses the `agent-skills/<version>` and `skill:<name>` tokens, so any rewording breaks attribution:

    User-Agent: <existing_user_agent> agent-skills/1.0 (skill:bigquery-udfs)

• **Constraint for Generated Code**: The tracking prefix is strictly for

commands you execute directly in the terminal. Do **NOT** include this tracking prefix in generated SQL, scripts, Terraform templates, or code provided to the user for their own repositories.

Choosing the Right UDF Type

Always select the lightest-weight runtime that satisfies the requirement, because BigQuery optimizes native SQL expressions inline while external runtimes incur serialization and container startup overhead:

UDF Type | Connection Required? | When to Use | Key Constraints :--------------------------------------------- | :------------------------- | :------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | :-------------- **SQL UDF** (Default) | No | Composable expressions, regex, struct/array manipulation, aggregate functions (`CREATE AGGREGATE FUNCTION`), or polymorphic `ANY TYPE` parameters. | Lowest latency; zero external dependencies; supports `TEMP` or persistent functions. **JavaScript UDF** | No | Custom string/JSON logic or bundling `.js` libraries from Cloud Storage (`gs://`). | Runs in V8 sandbox; `INT64` unsupported as input (pass as `FLOAT64` or `STRING`). Max 32 KB inline code, 1 MB per external resource. Supports `TEMP` or persistent functions. **Managed Python UDF (Offline)** | **No** | Pure compute using Python 3.11, PyPI wheels (`packages=[...]`), Cloud Storage `.py` modules (`library=[...]`), or vectorized Pandas/PyArrow batches. | Persistent functions only (`CREATE TEMP FUNCTION` unsupported). Outbound network blocked. Max container size: 4 vCPU / 16 GiB. `JSON`, `RANGE`, `INTERVAL`, `GEOGRAPHY` unsupported. **Managed Python UDF (`WITH CONNECTION`)** | **Yes** (`CLOUD_RESOURCE`) | Invoking Google Cloud APIs (e.g., Cloud Translation, Gemini Enterprise Agent Platform) or online HTTP endpoints directly from Python inside BigQuery containers. | Requires `WITH CONNECTION`. Outbound traffic restricted to `restricted.googleapis.com` in VPC-SC perimeters (VPC networks unsupported). Max container size: 4 vCPU / 16 GiB. **Remote Function (`REMOTE WITH CONNECTION`)** | **Yes** (`CLOUD_RESOURCE`) | Calling external APIs or microservices when you need non-Python runtimes (Go, Java, Node.js), private VPC network access, GPUs, or custom Cloud Run scaling. | Requires Cloud Run HTTPS endpoint implementing BigQuery JSON batch contract and `roles/run.invoker`. Max input 5 MB per row; response limit 15 MB (Cloud Run) / 10 MB (1st gen).

Prerequisites & Setup

Ensure the Google Cloud project has an active billing account attached (see [BigQuery Pricing](https://docs.cloud.google.com/bigquery/pricing)):

1. **Install and Authenticate Google Cloud SDK**: Install the [Google Cloud SDK](https://docs.cloud.google.com/sdk/docs/install) and authenticate:

    gcloud auth login

Pass `--project={project_id}` and `--region={region}` (or `--location` for `bq`) explicitly on each command instead of running `gcloud config set`, which persists beyond the task. Default to `us-central1` when the user has not specified a region (see [BigQuery Locations](https://docs.cloud.google.com/bigquery/docs/locations.md.txt)).

2. **Enable Required APIs**:

• For SQL, JavaScript, and offline Python UDFs: `bigquery.goog