bigquery-udfs

Files (5)

• SKILL.md
• iam-and-quotas.md · references/
• python-udfs.md · references/
• remote-functions.md · references/
• sql-and-js-udfs.md · references/

SKILL.md (first lines)

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

## Usage Attribution

**MUST READ**: Before executing any `gcloud`, REST API, or MCP command, you
MUST read and follow the instructions in
[Usage Attribution](references/attribution.md).

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

    ```bash
    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.googleapis.com`
    -   Additionally for Python UDFs calling APIs (`WITH CONNECTION`) and Remote
        Functions: `bigqueryconnection.googleapis.com` (and `run.googleapis.com`
        for Remote Functions)

    ```bash
    CLOUDSDK_METRICS_REQUEST_ATTRIBUTION="agent-skills/1.0 (skill:bigquery-udfs)" \
    gcloud services enable bigquery.googleapis.com \
      bigqueryconnection.googleapis.com \
      run.googleapis.com --quiet
    ```

3.  **Verify Required IAM Roles**:

    -   **All UDFs**: `roles/bigquery.dataEditor` on the dataset and
        `roles/bigquery.jobUser` on the project.
    -   **When using `WITH CONNECTION` (API-calling Python UDFs & Remote
        Functions)**:
        -   UDF Creator: `roles/bigquery.connectionAdmin` on the project (plus
            `resourcemanager.projects.setIamPolicy`, e.g.,
            `roles/resourcemanager.projectIamAdmin`, when granting project-level
            IAM roles to the connection service account via `GRANT ... ON
            PROJECT`).
        -   UDF Caller: `roles/bigquery.connectionUser` on the connection.

--------------------------------------------------------------------------------

## Core Implementation Patterns

### 1. SQL UDFs (Scalar & Templated)

Use `CREATE OR REPLACE FUNCTION` for persistent UDFs or `CREATE TEMP FUNCTION`
for query-scoped functions. Use `ANY TYPE` for polymorphic parameters:

```sql
CREATE OR REPLACE FUNCTION `{project_id}.{dataset_id}.normalize_phone`(
  raw_phone STRING,
  country_code ANY TYPE
)
RETURNS STRING
AS (
  CONCAT('+', CAST(country_code AS STRING), '-', REGEXP_REPLACE(raw_phone, r'[^0-9]', ''))
);
```

See [SQL and JavaScript UDFs](references/sql-and-js-udfs.md) for aggregate UDFs
(`CREATE AGGREGATE FUNCTION`) and JavaScript Cloud Storage library loading.

### 2. JavaScript UDFs

Use raw triple-quoted strings (`r"""..."""`) to avoid escaping backslashes:

```sql
CREATE OR REPLACE FUNCTION `{project_id}.{dataset_id}.extract_url_query_keys`(
  url_str STRING
)
RETURNS ARRAY<STRING>
DETERMINISTIC
LANGUAGE js
OPTIONS (
  library = ['gs://{bucket_name}/libs/uri-parser.js']
)
AS r"""
  if (!url_str) return [];
  try {
    return URIParser.getQueryKeys(url_str);
  } catch (err) {
    return [];
  }
""";
```

From google/skills, Apache-2.0.

More from google/skills