Obtenir des métadonnées de routine à l'aide de INFORMATION_SCHEMA

INFORMATION_SCHEMA contient les vues suivantes pour les métadonnées de routine :

  • ROUTINES
  • ROUTINE_OPTIONS
  • PARAMETERS

Autorisations requises

Vous devez disposer des autorisations bigquery.routines.get et bigquery.routines.list pour les vues ROUTINES, ROUTINE_OPTIONS et PARAMETERS.

Vue ROUTINES

Lorsque vous interrogez la vue INFORMATION_SCHEMA.ROUTINES, les résultats de la requête contiennent une ligne pour chaque routine d'un ensemble de données.

La vue INFORMATION_SCHEMA.ROUTINES présente le schéma suivant :

Nom de la colonne Type de données Valeur
SPECIFIC_CATALOG STRING Nom du projet contenant l'ensemble de données dans lequel la routine est définie
SPECIFIC_SCHEMA STRING Nom de l'ensemble de données contenant la routine
SPECIFIC_NAME STRING Nom de la routine
ROUTINE_CATALOG STRING Nom du projet contenant l'ensemble de données dans lequel la routine est définie
ROUTINE_SCHEMA STRING Nom de l'ensemble de données contenant la routine
ROUTINE_NAME STRING Nom de la routine
ROUTINE_TYPE STRING Type de routine :
  • FUNCTION : fonction BigQuery persistante définie par l'utilisateur
  • PROCEDURE : procédure BigQuery stockée
DATA_TYPE STRING Type de données renvoyé par la routine. NULL si la routine est une procédure stockée
ROUTINE_BODY STRING Mode de définition du corps de la routine, SQL ou EXTERNAL si la routine est une fonction JavaScript définie par l'utilisateur
ROUTINE_DEFINITION STRING Définition de la routine
EXTERNAL_LANGUAGE STRING JAVASCRIPT si la routine est une fonction JavaScript définie par l'utilisateur ou NULL si la routine a été définie avec SQL
IS_DETERMINISTIC STRING YES si la routine est identifiée comme déterministe, NO dans le cas contraire, ou NULL si elle est inconnue
SECURITY_TYPE STRING Type de sécurité de la routine, toujours NULL
CREATED TIMESTAMP Date/Heure de création de la routine
LAST_MODIFIED TIMESTAMP Date/Heure de la dernière modification de la routine

Exemples

Exemple 1 :

L'exemple suivant récupère toutes les colonnes de la vue INFORMATION_SCHEMA.ROUTINES. Les métadonnées renvoyées concernent toutes les routines de mydataset dans votre projet par défaut (myproject).

mydataset contient une routine nommée myroutine1.

Pour exécuter la requête :

Console

  1. Ouvrez l'UI Web de BigQuery dans Cloud Console.

    Accéder à Cloud Console

  2. Saisissez la requête SQL standard suivante dans la zone Éditeur de requête. INFORMATION_SCHEMA requiert la syntaxe SQL standard. Le langage SQL standard est la syntaxe par défaut dans Cloud Console.

    SELECT
     *
    FROM
     mydataset.INFORMATION_SCHEMA.ROUTINES
    
  3. Cliquez sur Exécuter.

Ligne de commande

Exécutez la commande query, puis spécifiez la syntaxe SQL standard à l'aide de l'option --nouse_legacy_sql ou --use_legacy_sql=false. La syntaxe SQL standard est requise pour les requêtes INFORMATION_SCHEMA.

Pour exécuter la requête, saisissez :

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

Les résultats doivent se présenter sous la forme suivante :

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

Vue ROUTINE_OPTIONS

Lorsque vous interrogez la vue INFORMATION_SCHEMA.ROUTINE_OPTIONS, les résultats de la requête contiennent une ligne pour chaque option de chaque routine d'un ensemble de données.

La vue INFORMATION_SCHEMA.ROUTINE_OPTIONS présente le schéma suivant :

Nom de la colonne Type de données Valeur
SPECIFIC_CATALOG STRING Nom du projet contenant la routine dans laquelle l'option est définie
SPECIFIC_SCHEMA STRING Nom de l'ensemble de données contenant la routine dans laquelle l'option est définie
SPECIFIC_NAME STRING Nom de la routine
OPTION_NAME STRING Une des valeurs de nom figurant dans la table d'options
OPTION_TYPE STRING Une des valeurs de type de données figurant dans la table d'options
OPTION_VALUE STRING Une des options de valeur figurant dans la table d'options
Table d'options
OPTION_NAME OPTION_TYPE OPTION_VALUE
description STRING Description de la routine, si elle est définie
library ARRAY Noms des bibliothèques référencées dans la routine. Applicable uniquement aux fonctions JavaScript définies par l'utilisateur

Exemples

Exemple 1 :

L'exemple suivant récupère les options de toutes les routines dans l'ensemble de données mydataset de votre projet par défaut (myproject) en interrogeant la vue INFORMATION_SCHEMA.ROUTINE_OPTIONS.

Pour exécuter la requête :

Console

  1. Ouvrez l'UI Web de BigQuery dans Cloud Console.

    Accéder à Cloud Console

  2. Saisissez la requête SQL standard suivante dans la zone Éditeur de requête. INFORMATION_SCHEMA requiert la syntaxe SQL standard. Le langage SQL standard est la syntaxe par défaut dans Cloud Console.

    SELECT
     *
    FROM
     mydataset.INFORMATION_SCHEMA.ROUTINE_OPTIONS
    
  3. Cliquez sur Exécuter.

Ligne de commande

Exécutez la commande query, puis spécifiez la syntaxe SQL standard à l'aide de l'option --nouse_legacy_sql ou --use_legacy_sql=false. La syntaxe SQL standard est requise pour les requêtes INFORMATION_SCHEMA.

Pour exécuter la requête, saisissez :

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

Les résultats doivent se présenter sous la forme suivante :

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

Vue PARAMETERS

Lorsque vous interrogez la vue INFORMATION_SCHEMA.PARAMETERS, les résultats de la requête contiennent une ligne pour chaque paramètre de chaque routine d'un ensemble de données.

La vue INFORMATION_SCHEMA.PARAMETERS présente le schéma suivant :

Nom de la colonne Type de données Valeur
SPECIFIC_CATALOG STRING Nom du projet qui contient l'ensemble de données dans lequel la routine contenant le paramètre est définie
SPECIFIC_SCHEMA STRING Nom de l'ensemble de données contenant la routine dans laquelle le paramètre est défini
SPECIFIC_NAME STRING Nom de la routine dans laquelle le paramètre est défini
ORDINAL_POSITION STRING Position en base 1 du paramètre, ou 0 pour la valeur renvoyée
PARAMETER_MODE STRING Mode du paramètre : IN, OUT, INOUT ou NULL
IS_RESULT STRING Indique si le paramètre est le résultat de la fonction, YES ou NO
PARAMETER_NAME STRING Nom du paramètre
DATA_TYPE STRING Type du paramètre, ANY TYPE s'il est défini comme n'importe quel type
PARAMETER_DEFAULT STRING Valeur par défaut du paramètre en tant que valeur littérale SQL, toujours NULL
IS_AGGREGATE STRING Indique s'il s'agit d'un paramètre d'agrégation, toujours NULL

Exemples

Exemple 1 :

L'exemple suivant récupère tous les paramètres de la vue INFORMATION_SCHEMA.PARAMETERS. Les métadonnées renvoyées concernent les routines de mydataset dans votre projet par défaut (myproject).

Pour exécuter la requête :

Console

  1. Ouvrez l'UI Web de BigQuery dans Cloud Console.

    Accéder à Cloud Console

  2. Saisissez la requête SQL standard suivante dans la zone Éditeur de requête. INFORMATION_SCHEMA requiert la syntaxe SQL standard. Le langage SQL standard est la syntaxe par défaut dans Cloud Console.

    SELECT
     * EXCEPT(is_typed)
    FROM
     mydataset.INFORMATION_SCHEMA.PARAMETERS
    WHERE
     table_type="BASE TABLE"
    
  3. Cliquez sur Exécuter.

Ligne de commande

Exécutez la commande query, puis spécifiez la syntaxe SQL standard à l'aide de l'option --nouse_legacy_sql ou --use_legacy_sql=false. La syntaxe SQL standard est requise pour les requêtes INFORMATION_SCHEMA.

Pour exécuter la requête, saisissez :

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

Les résultats doivent se présenter sous la forme suivante :

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