RetroSearch Browse

Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://cloud.google.com/bigquery/docs/routines below:

Manage routines | BigQuery | Google Cloud

Stay organized with collections Save and categorize content based on your preferences.

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.

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 play_circle 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          |
+------------------+----------------+--------------+

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 play_circle 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        |
+--------------------+

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 more_vert 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

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.

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

Last updated 2025-08-11 UTC.

[[["Easy to understand","easyToUnderstand","thumb-up"],["Solved my problem","solvedMyProblem","thumb-up"],["Other","otherUp","thumb-up"]],[["Hard to understand","hardToUnderstand","thumb-down"],["Incorrect information or sample code","incorrectInformationOrSampleCode","thumb-down"],["Missing the information/samples I need","missingTheInformationSamplesINeed","thumb-down"],["Other","otherDown","thumb-down"]],["Last updated 2025-08-11 UTC."],[[["BigQuery routines include stored procedures, user-defined functions (UDFs), and table functions, each with specific creation and management methods."],["Executing, listing, and viewing the body of a routine requires specific permissions such as `bigquery.routines.get`, `bigquery.routines.list`, and the user needs to have permissions to access any resources referenced by the routine, such as tables or views."],["Routines can be created, viewed, listed, and deleted using SQL DDL statements, the `bq` command-line tool, or through the BigQuery API using a method call."],["To list routines, you can use the Google Cloud console, query the `INFORMATION_SCHEMA.ROUTINES` view, use the `bq ls` command with the `--routines` flag, or call the `routines.list` API method."],["To view the body of a routine you can use the Google Cloud console, query the `INFORMATION_SCHEMA.ROUTINES` view, use the `bq show` command with the `--routine` flag, or use the `routines.get` method."]]],[]]

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4