Introduction to routines

This document describes how to choose a routine, which is a resource type thatyou use to create functions or stored procedures in BigQuery.

Supported routines

BigQuery supports the following routines:

How to choose a routine

This section describes factors to consider when choosing a routine and comparesroutines by task.

Factors to consider

To choose a routine, consider the following factors, which are described in thesections for each type of routine:

  • The type of task to implement.
  • The programming language to use.
  • The type of persistence to implement for the routine: temporary or persistent.
  • The type of reuse required for the routine: across single or multiple queries.
  • Performance considerations.
  • Accessing external services.
  • Sharing the routine with users.

Compare routines by task

The following table shows the type of tasks you can perform for each type of routine:

Task

Routine resource type

Create functions that perform general-purpose tasks in BigQuery.

SQL or Javascript UDF

SQL or Javascript UDAF

Create functions that perform general-purpose tasks inBigQuery and that communicate with external Google Cloud systemsusing aCloud resource connection.

Python UDF

Create functions that aggregate data.

UDAFs

Create a table using parameters.

Table functions

Create functions that use languages, libraries, or services that areunsupported in BigQuery. These functions directly integrate withCloud Runfunctions andCloud Run.

Remote functions

Execute multiple statements in one query as a multi-statement query usingprocedurallanguage. You can use a multi-statement query to do the following:

  • Run multiple statements in a sequence, with shared state.
  • Automate management tasks such as creating or dropping tables.
  • Implement complex logic using programming constructs such asIFandWHILE.

Create and call stored procedures for Apache Spark in BigQuery.

Stored procedures

User-defined functions (UDFs)

A UDF lets you create a function by using a SQL expression, JavaScript code, orPython code. UDFs accept columns of input, perform actions on the input, andreturn the result of those actions as a value.

You can define UDFs as either persistent or temporary. You can reuse persistentUDFs across multiple queries, while temporary UDFs only exist in the scope of asingle query.

You can create UDFs for use withcustom masking routines,which return a column's value after applying a UDF to the column. After youcreate the custom masking routine, it's available as amasking rule inCreate data policies.

For more information about UDFs, see the following resources:

Language-based UDFs

  • SQL-based UDFs supporttemplated UDF parameters,which can match more than one argument type when the UDF is called. SQL UDFscan also return the value of ascalar subquery.
  • Javascript-based UDFs let you call code written in JavaScript from a SQLquery.
    • JavaScript UDFs typically consume more slot resources as compared tostandard SQL queries, decreasing job performance.
    • If the function can be expressed in SQL, it's often more optimal to run thecode as a standard SQL query job.
  • Python-based UDFs are built and run on BigQuery managedresources. These UDFs let you implement a function in Python and use it in aSQL query.

Community contributed UDFs

In addition to the UDFs you create, community contributed UDFs are available inthebigquery-public-data.persistent_udfs public dataset and the open sourcebigquery-utils GitHub repository.

User-defined aggregate functions (UDAFs)

A UDAF lets you create an aggregate functionby using an expression that contains SQL or Javascript code. A UDAF acceptscolumns of input, performs a calculation on a group of rows at a time, and thenreturns the result of that calculation as a single value.

UDAFs can't mutate data, talk to external systems, or send logsto Google Cloud Observability or similar applications.

For more information, see the following resources:

SQL UDAFs

SQL UDAFs normally aggregate function parameters across all rows in agroup.However, you can specify a function parameter as non-aggregate by using theNOT AGGREGATE keyword. A non-aggregate function parameter is a scalar functionparameter with a constant value for all rows in a group. SQL UDAFs can containboth aggregate and non-aggregate parameters.

Javascript UDAFs

Javascript UDAFs can include Javascript libraries. The JavaScript functionbody can include custom JavaScript code such as JavaScript global variables andcustom functions.

Because Javascript-based functions typically use more resources, consultingtheseperformance tipscan be helpful.

Javascript UDAFs have some constraints. Onlyspecific typeencodings are allowed,and there arerequirementsfor serialization and deserialization.

Compare UDFs and UDAFs

Choosing a UDF rather than choosing a UDAF depends on the specific task that youare trying to perform.

  • To perform a calculation or transformation on individual data values, use aUDF.
  • To do the same on groups of data values, use a UDAF.

For example, if you want to calculate the average of a column of numbers, thenuse a UDAF. If you want to convert a column of strings to uppercase, then use aUDF.

UDFs and UDAFs have the following similarities:

UDFs and UDAFs have the following differences:

Attribute

UDFs

UDAFs

Definition

User-defined functions (UDFs) accept columns of input, perform actions onthe input, and return the result of those actions as a value.

User-defined aggregate functions (UDAFs) accept columns of input, perform acalculation on a group of rows at a time, and then return the result of thatcalculation as a single value.

Languages supported

SQL, Javascript, and Python

SQL and Javascript

Persistence

  • Can be temporary or persistent.
  • You can use persistent UDFs across multiple queries.
  • You can use temporary UDFs for only a single query.
  • Python UDFs can only be persistent, not temporary.
  • Can be temporary or persistent.
  • You can use persistent UDAFs across multiple queries.
  • You can use temporary UDAFs for only a single query, script, session orprocedure.
  • Persistent UDAFs are safe to call when they are shared between owners.

Arguments and data types

UDFs accept parameter values that conform to GoogleSQLfor BigQuerydatatypes. Some SQL types have a direct mapping to JavaScripttypes, but others don't. Seesupported typesfor Javascript.

For a SQL UDF, parameter values can beANY TYPE,which can match more than one argument type when the function iscalled.

Only Javascript UDFs have a determinism specifierthat provides a hint to BigQuery as to whether the query resultcan be cached.

SQL and Javascript UDAFs accept parameters values that conform toGoogleSQL for BigQuerydata types.

Function parameters can be aggregate or non-aggregate.

Usage

UDFs are commonly used for data cleaning, transformation, andvalidation.

UDAFs are commonly used for calculating summary statistics, such asaverages, sums, and counts.

Table functions

A table function, also called a table-valued function (TVF), is a UDF thatreturns a table. You can use a table function anywhere that you can use a table.Table functions behave similarly to views, but a table function can takeparameters.

You can do the following with table functions:

  • Pass in multiple parameters.
  • Call a table function in any context where a table is valid.
  • Join the output from a table function with another table.
  • Use a table function in asubquery.

For more information about table functions, seeTable functions,Limitations, andQuotas and limits.

Remote functions

Remote functions enable you to implement your function in languages other thanSQL and Javascript, or enable you to use libraries or services that are notsupported in BigQuery UDFs.

A BigQuery remote function integrates your Google SQL function withCloud Run functionsandCloud Run using anysupported language, and then invokes those functions from Google SQL queries.

The following tasks are examples of what you can do with remote functions:

Creating a remote function requires the following steps:

  1. Create the HTTP endpoint in Cloud Run functions orCloud Run.
  2. Create a remote function in BigQuery using theCLOUD_RESOURCE connection type.
  3. Use the remote function in a query just like any other UDFfor BigQuery.

For more information about remote functions, seeRemotefunctions,Limitations, andQuotas andlimits.

Stored procedures

A SQL stored procedure is a collection of statements that can be called fromother queries or other stored procedures. You name and store a procedure in aBigQuery dataset.

Stored procedures support procedural language statements, which let you dothings like define variables and implement control flow. You can learn moreabout procedural language statements in theProcedural languagereference.

A stored procedure can do the following:

  • Take input arguments and return values as output.
  • Access or modify data across multiple datasets by multiple users.
  • Contain amulti-statement query.

Some stored procedures are built into BigQuery and don't need tobe created. These are called system procedures, and you can learn more about themin theSystem procedures reference.

Stored procedures forSpark inBigQuery are alsosupported. These procedures havequotas and limits.

To learn more about stored procedures, seeSQLstored procedures.

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-12-15 UTC.