Getting routine metadata using INFORMATION_SCHEMA

Beta

This feature is covered by the Pre-GA Offerings Terms of the Google Cloud Platform Terms of Service. Pre-GA features may have limited support, and changes to pre-GA features may not be compatible with other pre-GA versions. For more information, see the launch stage descriptions.

INFORMATION_SCHEMA contains these views for routine metadata:

• ROUTINES
• ROUTINE_OPTIONS
• PARAMETERS

Required permissions

For ROUTINES, ROUTINE_OPTIONS, and PARAMETERS, you must be granted the bigquery.routines.get and bigquery.routines.list permissions.

Syntax

Queries against any of these views must have a dataset or region qualifier.

-- Returns metadata for routines in a single dataset.
SELECT * FROM myDataset.INFORMATION_SCHEMA.ROUTINES;

-- Returns metadata for routines in a region.
SELECT * FROM region-us.INFORMATION_SCHEMA.ROUTINES;

ROUTINES view

When you query the INFORMATION_SCHEMA.ROUTINES view, the query results contain one row for each routine in a dataset.

The INFORMATION_SCHEMA.ROUTINES view has the following schema:

Column name	Data type	Value
SPECIFIC_CATALOG	STRING	The name of the project that contains the dataset where the routine is defined
SPECIFIC_SCHEMA	STRING	The name of the dataset that contains the routine
SPECIFIC_NAME	STRING	The name of the routine
ROUTINE_CATALOG	STRING	The name of the project that contains the dataset where the routine is defined
ROUTINE_SCHEMA	STRING	The name of the dataset that contains the routine
ROUTINE_NAME	STRING	The name of the routine
ROUTINE_TYPE	STRING	The routine type: FUNCTION: A BigQuery persistent user-defined function PROCEDURE: A BigQuery stored procedure
DATA_TYPE	STRING	The data type that the routine returns. NULL if the routine is a stored procedure
ROUTINE_BODY	STRING	How the body of the routine is defined, either SQL or EXTERNAL if the routine is a JavaScript user-defined function
ROUTINE_DEFINITION	STRING	The definition of the routine
EXTERNAL_LANGUAGE	STRING	JAVASCRIPT if the routine is a JavaScript user-defined function or NULL if the routine was defined with SQL
IS_DETERMINISTIC	STRING	YES if the routine is known to be deterministic, NO if it is not, or NULL if unknown
SECURITY_TYPE	STRING	Security type of the routine, always NULL
CREATED	TIMESTAMP	The routine's creation time
LAST_MODIFIED	TIMESTAMP	The routine's last modification time

Examples

Example 1:

The following example retrieves all columns from the INFORMATION_SCHEMA.ROUTINES view. The metadata returned is for all routines in mydataset in your default project — myproject.

mydataset contains a routine named myroutine1.

To run the query:

Console

1. Open the BigQuery web UI in the Cloud Console.

   Go to the Cloud Console

2. Enter the following standard SQL query in the Query editor box. INFORMATION_SCHEMA requires standard SQL syntax. Standard SQL is the default syntax in the Cloud Console.

   SELECT
    *
   FROM
    mydataset.INFORMATION_SCHEMA.ROUTINES
   

   Note: INFORMATION_SCHEMA view names are case-sensitive.

3. Click Run.

bq

Use the query command and specify standard SQL syntax by using the --nouse_legacy_sql or --use_legacy_sql=false flag. Standard SQL syntax is required for INFORMATION_SCHEMA queries.

To run the query, enter:

bq query --nouse_legacy_sql \
'SELECT
   *
 FROM
   mydataset.INFORMATION_SCHEMA.ROUTINES'

The results should look like the following:

  +------------------+-----------------+---------------+-----------------+----------------+--------------+--------------+-----------+--------------+--------------------+-------------------+------------------+---------------+-----------------------------+-----------------------------+
  | specific_catalog | specific_schema | specific_name | routine_catalog | routine_schema | routine_name | routine_type | data_type | routine_body | routine_definition | external_language | is_deterministic | security_type |           created           |         last_altered        |
  +------------------+-----------------+---------------+-----------------+----------------+--------------+--------------+-----------+--------------+--------------------+-------------------+------------------+---------------+-----------------------------+-----------------------------+
  | myproject        | mydataset       | myroutine1    | myproject       | mydataset      | myroutine1   | FUNCTION     | NULL      | SQL          | x + 3              | NULL              | NULL             | NULL          | 2019-10-03 17:29:00.235 UTC | 2019-10-03 17:29:00.235 UTC |
  +------------------+-----------------+---------------+-----------------+----------------+--------------+--------------+-----------+--------------+--------------------+-------------------+------------------+---------------+-----------------------------+-----------------------------+
  

ROUTINE_OPTIONS view

When you query the INFORMATION_SCHEMA.ROUTINE_OPTIONS view, the query results contain one row for each option of each routine in a dataset.

The INFORMATION_SCHEMA.ROUTINE_OPTIONS view has the following schema:

Column name	Data type	Value
SPECIFIC_CATALOG	STRING	The name of the project that contains the routine where the option is defined
SPECIFIC_SCHEMA	STRING	The name of the dataset that contains the routine where the option is defined
SPECIFIC_NAME	STRING	The name of the routine
OPTION_NAME	STRING	One of the name values in the options table
OPTION_TYPE	STRING	One of the data type values in the options table
OPTION_VALUE	STRING	One of the value options in the options table

Options table

OPTION_NAME	OPTION_TYPE	OPTION_VALUE
description	STRING	The description of the routine, if defined
library	ARRAY	The names of the libraries referenced in the routine. Only applicable to JavaScript UDFs

Examples

Example 1:

The following example retrieves the routine options for all routines in mydataset in your default project (myproject) by querying the INFORMATION_SCHEMA.ROUTINE_OPTIONS view.

To run the query:

Console

1. Open the BigQuery web UI in the Cloud Console.

   Go to the Cloud Console

2. Enter the following standard SQL query in the Query editor box. INFORMATION_SCHEMA requires standard SQL syntax. Standard SQL is the default syntax in the Cloud Console.

   SELECT
    *
   FROM
    mydataset.INFORMATION_SCHEMA.ROUTINE_OPTIONS
   

   Note: INFORMATION_SCHEMA view names are case-sensitive.

3. Click Run.

bq

Use the query command and specify standard SQL syntax by using the --nouse_legacy_sql or --use_legacy_sql=false flag. Standard SQL syntax is required for INFORMATION_SCHEMA queries.

To run the query, enter:

bq query --nouse_legacy_sql \
'SELECT
   *
 FROM
   mydataset.INFORMATION_SCHEMA.ROUTINE_OPTIONS'

The results should look like the following:

  +-------------------+------------------+---------------+----------------------+---------------+------------------+
  | specific_catalog  | specific_schema  | specific_name |     option_name      | option_type   | option_value     |
  +-------------------+------------------+---------------+----------------------+---------------+------------------+
  | myproject         | mydataset        | myroutine1    | description          | STRING        | "a description"  |
  | myproject         | mydataset        | myroutine2    | library              | ARRAY<STRING> | ["a.js", "b.js"] |
  +-------------------+------------------+---------------+----------------------+---------------+------------------+
  

PARAMETERS view

When you query the INFORMATION_SCHEMA.PARAMETERS view, the query results contain one row for each parameter of each routine in a dataset.

The INFORMATION_SCHEMA.PARAMETERS view has the following schema:

Column name	Data type	Value
SPECIFIC_CATALOG	STRING	The name of the project that contains the dataset in which the routine containing the parameter is defined
SPECIFIC_SCHEMA	STRING	The name of the dataset that contains the routine in which the parameter is defined
SPECIFIC_NAME	STRING	The name of the routine in which the parameter is defined
ORDINAL_POSITION	STRING	The 1-based position of the parameter, or 0 for the return value
PARAMETER_MODE	STRING	The mode of the parameter, either IN, OUT, INOUT, or NULL
IS_RESULT	STRING	Whether the parameter is the result of the function, either YES or NO
PARAMETER_NAME	STRING	The name of the parameter
DATA_TYPE	STRING	The type of the parameter, will be ANY TYPE if defined as an any type
PARAMETER_DEFAULT	STRING	The default value of the parameter as a SQL literal value, always NULL
IS_AGGREGATE	STRING	Whether this is an aggregate parameter, always NULL

Examples

Example 1:

The following example retrieves all parameters from the INFORMATION_SCHEMA.PARAMETERS view. The metadata returned is for routines in mydataset in your default project — myproject.

To run the query:

Console

1. Open the BigQuery web UI in the Cloud Console.

   Go to the Cloud Console

2. Enter the following standard SQL query in the Query editor box. INFORMATION_SCHEMA requires standard SQL syntax. Standard SQL is the default syntax in the Cloud Console.

   SELECT
    * EXCEPT(is_typed)
   FROM
    mydataset.INFORMATION_SCHEMA.PARAMETERS
   WHERE
    table_type="BASE TABLE"
   

   Note: INFORMATION_SCHEMA view names are case-sensitive.

3. Click Run.

bq

Use the query command and specify standard SQL syntax by using the --nouse_legacy_sql or --use_legacy_sql=false flag. Standard SQL syntax is required for INFORMATION_SCHEMA queries.

To run the query, enter:

bq query --nouse_legacy_sql \
'SELECT
   *
 FROM
   mydataset.INFORMATION_SCHEMA.PARAMETERS
 WHERE
   table_type="BASE TABLE"'

The results should look like the following:

  +-------------------+------------------+---------------+------------------+----------------+-----------+----------------+-----------+-------------------+--------------+
  | specific_catalog  | specific_schema  | specific_name | ordinal_position | parameter_mode | is_result | parameter_name | data_type | parameter_default | is_aggregate |
  +-------------------+------------------+---------------+------------------+----------------+-----------+----------------+-----------+-------------------+--------------+
  | myproject         | mydataset        | myroutine1    | 0                | NULL           | YES       | NULL           | INT64     | NULL              | NULL         |
  | myproject         | mydataset        | myroutine1    | 1                | NULL           | NO        | x              | INT64     | NULL              | NULL         |
  +-------------------+------------------+---------------+------------------+----------------+-----------+----------------+-----------+-------------------+--------------+