Manage routines

In BigQuery, routines are a resource type that includes the following:

Stored procedures.
User-defined functions (UDFs), including remote functions.
Table functions.

This document describes tasks that are common to all routine types in BigQuery.

Permissions

To execute a routine, you must have the bigquery.routines.get permission on the dataset that contains the routine.

By default, you also need permission to access any resources that the routine references, such as tables or views. For UDFs and table functions, you can authorize the function to access those resources on the caller's behalf. For more information, see Authorized functions.

Create a routine

To create a routine, you must have the bigquery.routines.create permission.

SQL

Depending on the routine type, run one of the following DDL statements:

API

Call the routines.insert method with a defined Routine resource.

List routines

To list the routines in a dataset, you must have the bigquery.routines.get and bigquery.routines.list permissions.

Console

In the Google Cloud console, open the BigQuery page.

Go to BigQuery
In the Explorer panel, expand your project and select a dataset.
Expand the Routines list.

SQL

Query the INFORMATION_SCHEMA.ROUTINES view:

In the Google Cloud console, go to the BigQuery page.

Go to BigQuery

In the query editor, enter the following statement:

SELECT
  COLUMN_LIST
FROM
   { DATASET | REGION }.INFORMATION_SCHEMA.ROUTINES;

Click Run.

For more information about how to run queries, see Run an interactive query.

Replace the following:

COLUMN_LIST: a comma-separated list of columns from the INFORMATION_SCHEMA.ROUTINES view.
DATASET: the name of a dataset in your project.
REGION: a region qualifier.

Example:

SELECT
  routine_name, routine_type, routine_body
FROM
  mydataset.INFORMATION_SCHEMA.ROUTINES;

+------------------+----------------+--------------+
|   routine_name   |  routine_type  | routine_body |
+------------------+----------------+--------------+
| AddFourAndDivide | FUNCTION       | SQL          |
| create_customer  | PROCEDURE      | SQL          |
| names_by_year    | TABLE FUNCTION | SQL          |
+------------------+----------------+--------------+

bq

Use the bq ls command with the --routines flag:

bq ls --routines DATASET

Replace the following:

DATASET: the name of a dataset in your project.

Example:

bq ls --routines mydataset

         Id              Routine Type        Language    Creation Time    Last Modified Time
------------------ ----------------------- ---------- ----------------- --------------------
 AddFourAndDivide   SCALAR_FUNCTION         SQL        05 May 01:12:03   05 May 01:12:03
 create_customer    PROCEDURE               SQL        21 Apr 19:55:51   21 Apr 19:55:51
 names_by_year      TABLE_VALUED_FUNCTION   SQL        01 Sep 22:59:17   01 Sep 22:59:17

API

Call the routines.list method with the dataset ID.

View the body of a routine

To view the body of a routine, you must have the bigquery.routines.get permission.

Console

In the Google Cloud console, open the BigQuery page.

Go to BigQuery
In the Explorer panel, expand your project and select a dataset.
Expand the Routines list.
Select the routine. The body of the routine is listed under Routine query.

SQL

Select the routine_definition column of the INFORMATION_SCHEMA.ROUTINES view:

In the Google Cloud console, go to the BigQuery page.

Go to BigQuery

In the query editor, enter the following statement:

SELECT
  routine_definition
FROM
  { DATASET | REGION }.INFORMATION_SCHEMA.ROUTINES
WHERE
  routine_name = ROUTINE_NAME;

Click Run.

For more information about how to run queries, see Run an interactive query.

Replace the following:

DATASET: the name of a dataset in your project.
REGION: a region qualifier.
ROUTINE_NAME: the name of the routine.

Example:

SELECT
  routine_definition
FROM
  mydataset.INFORMATION_SCHEMA.ROUTINES
WHERE
  routine_name = 'AddFourAndDivide';

+--------------------+
| routine_definition |
+--------------------+
| (x + 4) / y        |
+--------------------+

bq

Use the bq show command with the --routine flag:

bq show --routine DATASET.ROUTINE_NAME

Replace the following:

DATASET: the name of a dataset in your project.
ROUTINE_NAME: the name of the routine.

Example:

bq show --routine mydataset.AddFourAndDivide

         Id           Routine Type     Language             Signature             Definition     Creation Time    Last Modified Time
 ------------------ ----------------- ---------- ------------------------------- ------------- ----------------- --------------------
  AddFourAndDivide   SCALAR_FUNCTION   SQL        (x INT64, y INT64) -> FLOAT64   (x + 4) / y   05 May 01:12:03   05 May 01:12:03

API

Call the routines.get method with the dataset ID and the name of the routine. The body of the routine is returned in the Routine object.

Delete a routine

To delete a routine, you must have the bigquery.routines.delete permission.

Console

In the Google Cloud console, open the BigQuery page.

Go to BigQuery
In the Explorer panel, expand your project and select a dataset.
Expand the Routines list.
Select the routine.
Expand the Actions option and click Delete.
Type "delete" in the dialog, then click Delete to confirm.

SQL

Depending on the routine type, run one of the following DDL statements:

Example:

DROP FUNCTION IF EXISTS mydataset.AddFourAndDivide

bq

Use the bq rm command with the --routine flag:

bq rm --routine DATASET.ROUTINE_NAME

Replace the following:

DATASET: the name of a dataset in your project.
ROUTINE_NAME: the name of the routine.

Example:

bq rm --routine mydataset.AddFourAndDivide

API

Call the routines.delete method with the dataset ID and the name of the routine.