Getting routine metadata using INFORMATION_SCHEMA

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.

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
    
  3. Click Run.

Command Line

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
    
  3. Click Run.

Command Line

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"
    
  3. Click Run.

Command Line

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

Оцените, насколько информация на этой странице была вам полезна:

Оставить отзыв о...

Текущей странице
Нужна помощь? Обратитесь в службу поддержки.