Obtén metadatos de rutina con INFORMATION_SCHEMA

INFORMATION_SCHEMA contiene estas vistas para los metadatos de rutina:

  • ROUTINES
  • ROUTINE_OPTIONS
  • PARAMETERS

Permisos necesarios

En el caso de ROUTINES, ROUTINE_OPTIONS y PARAMETERS, debes tener los permisos bigquery.routines.get y bigquery.routines.list.

Sintaxis

Las consultas de cualquiera de estas vistas deben tener un calificador de región o conjunto de datos.

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

Vista ROUTINES

Cuando consultas la vista INFORMATION_SCHEMA.ROUTINES, los resultados de la consulta contienen una fila por cada rutina de un conjunto de datos.

La vista INFORMATION_SCHEMA.ROUTINES tiene el siguiente esquema:

Nombre de la columna Tipo de datos Valor
SPECIFIC_CATALOG STRING El nombre del proyecto que contiene el conjunto de datos en el que se define la rutina
SPECIFIC_SCHEMA STRING El nombre del conjunto de datos que contiene la rutina
SPECIFIC_NAME STRING El nombre de la rutina
ROUTINE_CATALOG STRING El nombre del proyecto que contiene el conjunto de datos en el que se define la rutina
ROUTINE_SCHEMA STRING El nombre del conjunto de datos que contiene la rutina
ROUTINE_NAME STRING El nombre de la rutina
ROUTINE_TYPE STRING El tipo de rutina:
  • FUNCTION: una función persistente de BigQuery definida por el usuario
  • PROCEDURE: un procedimiento de BigQuery almacenado
DATA_TYPE STRING El tipo de datos que muestra la rutina. NULL si la rutina es un procedimiento almacenado
ROUTINE_BODY STRING La manera en la que se define el cuerpo de la rutina, ya sea SQL o EXTERNAL si la rutina es una función de JavaScript definida por el usuario
ROUTINE_DEFINITION STRING La definición de la rutina
EXTERNAL_LANGUAGE STRING JAVASCRIPT si la rutina es una función de JavaScript definida por el usuario o NULL si la rutina se definió con SQL
IS_DETERMINISTIC STRING YES si se sabe que la rutina es determinista, NO si no lo es o NULL si se desconoce
SECURITY_TYPE STRING El tipo de seguridad de la rutina, siempre NULL
CREATED TIMESTAMP La hora de creación de la rutina
LAST_MODIFIED TIMESTAMP La hora en la que se modificó la rutina por última vez

Ejemplos

Ejemplo 1:

En el ejemplo que se muestra a continuación, se recuperan todas las columnas de la vista INFORMATION_SCHEMA.ROUTINES. Los metadatos que se muestran corresponden a todas las rutinas de mydataset en el proyecto predeterminado: myproject.

mydataset contiene una rutina llamada myroutine1.

Para ejecutar la consulta, haz lo siguiente:

Console

  1. Abre la página de BigQuery en Cloud Console.

    Ir a la página BigQuery

  2. Ingresa la siguiente consulta de SQL estándar en la casilla Editor de consulta. INFORMATION_SCHEMA requiere sintaxis de SQL estándar. SQL estándar es la sintaxis predeterminada en Cloud Console.

    SELECT
     *
    FROM
     mydataset.INFORMATION_SCHEMA.ROUTINES
    
  3. Haz clic en Ejecutar.

bq

Usa el comando query y especifica la sintaxis de SQL estándar con las marcas --nouse_legacy_sql o --use_legacy_sql=false. Se requiere la sintaxis de SQL estándar para las consultas INFORMATION_SCHEMA.

Para ejecutar la consulta, ingresa lo siguiente:

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

El resultado debería ser similar a lo siguiente:

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

Vista ROUTINE_OPTIONS

Cuando consultas la vista INFORMATION_SCHEMA.ROUTINE_OPTIONS, los resultados de la consulta contienen una fila por cada opción de cada rutina en un conjunto de datos.

La vista INFORMATION_SCHEMA.ROUTINE_OPTIONS tiene el siguiente esquema:

Nombre de la columna Tipo de datos Valor
SPECIFIC_CATALOG STRING El nombre del proyecto que contiene la rutina en la que se define la opción
SPECIFIC_SCHEMA STRING El nombre del conjunto de datos que contiene la rutina en la que se define la opción
SPECIFIC_NAME STRING El nombre de la rutina
OPTION_NAME STRING Uno de los valores de nombre de la tabla de opciones
OPTION_TYPE STRING Uno de los valores de tipo de datos en la tabla de opciones
OPTION_VALUE STRING Una de las opciones de valor en la tabla de opciones
Tabla de opciones
OPTION_NAME OPTION_TYPE OPTION_VALUE
description STRING La descripción de la rutina, si está definida
library ARRAY Los nombres de las bibliotecas a las que se hace referencia en la rutina. Solo aplicable para las UDF en JavaScript

Ejemplos

Ejemplo 1:

En el ejemplo que sigue, se recuperan las opciones de rutina de todas las rutinas en mydataset dentro del proyecto predeterminado (myproject) mediante una consulta a la vista INFORMATION_SCHEMA.ROUTINE_OPTIONS.

Para ejecutar la consulta, haz lo siguiente:

Console

  1. Abre la página de BigQuery en Cloud Console.

    Ir a la página BigQuery

  2. Ingresa la siguiente consulta de SQL estándar en la casilla Editor de consulta. INFORMATION_SCHEMA requiere sintaxis de SQL estándar. SQL estándar es la sintaxis predeterminada en Cloud Console.

    SELECT
     *
    FROM
     mydataset.INFORMATION_SCHEMA.ROUTINE_OPTIONS
    
  3. Haz clic en Ejecutar.

bq

Usa el comando query y especifica la sintaxis de SQL estándar con las marcas --nouse_legacy_sql o --use_legacy_sql=false. Se requiere la sintaxis de SQL estándar para las consultas INFORMATION_SCHEMA.

Para ejecutar la consulta, ingresa lo siguiente:

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

El resultado debería ser similar a lo siguiente:

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

Vista PARAMETERS

Cuando consultas la vista INFORMATION_SCHEMA.PARAMETERS, los resultados de la consulta contienen una fila por cada parámetro de cada rutina en el conjunto de datos.

La vista INFORMATION_SCHEMA.PARAMETERS tiene el siguiente esquema:

Nombre de la columna Tipo de datos Valor
SPECIFIC_CATALOG STRING El nombre del proyecto que contiene el conjunto de datos en el que se define la rutina que tiene el parámetro
SPECIFIC_SCHEMA STRING El nombre del conjunto de datos que contiene la rutina en la que se define el parámetro
SPECIFIC_NAME STRING El nombre de la rutina en la que se define el parámetro
ORDINAL_POSITION STRING La posición con base 1 del parámetro, o 0 para el valor que se muestra
PARAMETER_MODE STRING El modo del parámetro, ya sea IN, OUT, INOUT o NULL
IS_RESULT STRING Si el parámetro es el resultado de la función, YES o NO
PARAMETER_NAME STRING El nombre del parámetro
DATA_TYPE STRING El tipo del parámetro será ANY TYPE si se lo define como cualquier tipo
PARAMETER_DEFAULT STRING El valor predeterminado del parámetro como un valor literal de SQL, siempre NULL
IS_AGGREGATE STRING Si este es un parámetro agregado, siempre NULL

Ejemplos

Ejemplo 1:

En el ejemplo que sigue, se recuperan todos los parámetros de la vista INFORMATION_SCHEMA.PARAMETERS. Los metadatos que se muestran corresponden a las rutinas de mydataset en el proyecto predeterminado: myproject.

Para ejecutar la consulta, haz lo siguiente:

Console

  1. Abre la página de BigQuery en Cloud Console.

    Ir a la página BigQuery

  2. Ingresa la siguiente consulta de SQL estándar en la casilla Editor de consulta. INFORMATION_SCHEMA requiere sintaxis de SQL estándar. SQL estándar es la sintaxis predeterminada en Cloud Console.

    SELECT
     * EXCEPT(is_typed)
    FROM
     mydataset.INFORMATION_SCHEMA.PARAMETERS
    WHERE
     table_type="BASE TABLE"
    
  3. Haz clic en Ejecutar.

bq

Usa el comando query y especifica la sintaxis de SQL estándar con las marcas --nouse_legacy_sql o --use_legacy_sql=false. Se requiere la sintaxis de SQL estándar para las consultas INFORMATION_SCHEMA.

Para ejecutar la consulta, ingresa lo siguiente:

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

Los resultados deberían verse de la siguiente manera:

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