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 reference a routine in a SQL query, you must have the bigquery.routines.get permission. To grant access to routines you can grant an IAM role with the bigquery.routines.get permission on the dataset or on the individual routine. Granting access at the dataset level gives the principal access to all routines in the dataset. For more information, see Control access to resources with IAM.

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 left pane, click Explorer:

If you don't see the left pane, click Expand left pane to open the pane.
In the Explorer pane, expand your project, click Datasets, and then select a dataset.
Click the Routines tab.

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 left pane, click Explorer:
In the Explorer pane, expand your project, click Datasets, and then select a dataset.
Click the Routines tab.
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 left pane, click Explorer:
In the Explorer pane, expand your project, click Datasets, and then select a dataset.
Click the Routines tab.
Select the routine.
In the details pane, 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.