INFORMATION_SCHEMA を使用したルーティン メタデータの取得

INFORMATION_SCHEMA には、ルーティンのメタデータに関する次のビューが含まれます。

  • ROUTINES
  • ROUTINE_OPTIONS
  • PARAMETERS

必要な権限

ROUTINESROUTINE_OPTIONSPARAMETERS にアクセスするには、bigquery.routines.get 権限と bigquery.routines.list 権限が付与されている必要があります。

ROUTINES ビュー

INFORMATION_SCHEMA.ROUTINES ビューにクエリを実行すると、クエリの結果として、データセット内の各ルーティンが 1 行ずつで返されます。

INFORMATION_SCHEMA.ROUTINES ビューのスキーマは次のとおりです。

列名 データ型
SPECIFIC_CATALOG STRING ルーティンが定義されているデータセットを含むプロジェクトの名前
SPECIFIC_SCHEMA STRING ルーティンを含むデータセットの名前
SPECIFIC_NAME STRING ルーティンの名前
ROUTINE_CATALOG STRING ルーティンが定義されているデータセットを含むプロジェクトの名前
ROUTINE_SCHEMA STRING ルーティンを含むデータセットの名前
ROUTINE_NAME STRING ルーティンの名前
ROUTINE_TYPE STRING ルーティンのタイプ:
  • FUNCTION: BigQuery 永続的なユーザー定義関数
  • PROCEDURE: BigQuery ストアド プロシージャ
DATA_TYPE STRING ルーティンが返すデータ型。NULL - ルーティンがストアド プロシージャである場合
ROUTINE_BODY STRING ルーティンが JavaScript ユーザー定義関数である場合のルーティン本文の定義方法(SQL または EXTERNAL
ROUTINE_DEFINITION STRING ルーティンの定義
EXTERNAL_LANGUAGE STRING JAVASCRIPT - ルーティンが JavaScript ユーザー定義関数である場合。または、 NULL - ルーティンが SQL で定義されている場合。
IS_DETERMINISTIC STRING YES - ルーティンが決定的であることがわかっている場合。決定的でない場合は NO、不明である場合は NULL
SECURITY_TYPE STRING ルーティンのセキュリティ タイプ(常に NULL
CREATED TIMESTAMP ルーティンの作成時間
LAST_MODIFIED TIMESTAMP ルーティンの最終更新日時

例 1:

次の例では、INFORMATION_SCHEMA.ROUTINES ビューからすべての列を取得しています。デフォルト プロジェクト(myproject)にある mydataset 内のすべてのルーティンに対するメタデータが返されます。

mydataset には、myroutine1 という名前のルーティンが含まれます。

クエリを実行するには:

Console

  1. Cloud Console で BigQuery ウェブ UI を開きます。

    Cloud Console に移動する

  2. [クエリエディタ] ボックスに、次の標準 SQL クエリを入力します。INFORMATION_SCHEMA には標準 SQL 構文を使用する必要があります。標準 SQL は Cloud Console のデフォルトの構文です。

    SELECT
     *
    FROM
     mydataset.INFORMATION_SCHEMA.ROUTINES
    
  3. [実行] をクリックします。

コマンドライン

query コマンドで、--nouse_legacy_sql または --use_legacy_sql=false フラグを使用して標準 SQL 構文を指定します。INFORMATION_SCHEMA クエリには標準 SQL 構文を使用する必要があります。

クエリを実行するには、次のように入力します。

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

結果は次のようになります。

  +------------------+-----------------+---------------+-----------------+----------------+--------------+--------------+-----------+--------------+--------------------+-------------------+------------------+---------------+-----------------------------+-----------------------------+
  | 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 ビュー

INFORMATION_SCHEMA.ROUTINE_OPTIONS ビューにクエリを実行すると、クエリの結果として、データセット内の各ルーティンのオプションごとに 1 行が表示されます。

INFORMATION_SCHEMA.ROUTINE_OPTIONS ビューのスキーマは次のとおりです。

列名 データ型
SPECIFIC_CATALOG STRING オプションが定義されているルーティンを含むプロジェクトの名前
SPECIFIC_SCHEMA STRING オプションが定義されているルーティンを含むデータセットの名前
SPECIFIC_NAME STRING ルーティンの名前
OPTION_NAME STRING オプション テーブル内のいずれか 1 つの名前の値
OPTION_TYPE STRING オプション テーブル内のいずれか 1 つのデータ型の値
OPTION_VALUE STRING オプション テーブル内のいずれか 1 つの値オプション
オプション テーブル
OPTION_NAME OPTION_TYPE OPTION_VALUE
description STRING ルーティンの説明(定義されている場合)
library ARRAY ルーティン内で参照されているライブラリの名前JavaScript UDF にのみ適用されます。

例 1:

次の例では、INFORMATION_SCHEMA.ROUTINE_OPTIONS ビューにクエリを実行して、デフォルト プロジェクト(myproject)にある mydataset の全ルーティンのルーティン オプションを取得します。

クエリを実行するには:

Console

  1. Cloud Console で BigQuery ウェブ UI を開きます。

    Cloud Console に移動する

  2. [クエリエディタ] ボックスに、次の標準 SQL クエリを入力します。INFORMATION_SCHEMA には標準 SQL 構文を使用する必要があります。標準 SQL は Cloud Console のデフォルトの構文です。

    SELECT
     *
    FROM
     mydataset.INFORMATION_SCHEMA.ROUTINE_OPTIONS
    
  3. [実行] をクリックします。

コマンドライン

query コマンドで、--nouse_legacy_sql または --use_legacy_sql=false フラグを使用して標準 SQL 構文を指定します。INFORMATION_SCHEMA クエリには標準 SQL 構文を使用する必要があります。

クエリを実行するには、次のように入力します。

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

結果は次のようになります。

  +-------------------+------------------+---------------+----------------------+---------------+------------------+
  | 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 ビュー

INFORMATION_SCHEMA.PARAMETERS ビューにクエリを実行すると、クエリ結果として、データセット内の各ルーティンのパラメータごとに 1 行が表示されます。

INFORMATION_SCHEMA.PARAMETERS ビューのスキーマは次のとおりです。

列名 データ型
SPECIFIC_CATALOG STRING パラメータを含むルーティンが定義されているデータセットを含むプロジェクトの名前
SPECIFIC_SCHEMA STRING パラメータが定義されているルーティンを含むデータセットの名前
SPECIFIC_NAME STRING パラメータが定義されているルーティンの名前
ORDINAL_POSITION STRING パラメータの位置(開始値 1)、または戻り値の 0
PARAMETER_MODE STRING パラメータのモード(INOUTINOUT、または NULL
IS_RESULT STRING パラメータがどの関数(YES または NO)の結果であるか
PARAMETER_NAME STRING パラメータの名前
DATA_TYPE STRING パラメータのタイプ(任意の型として定義されている場合は ANY TYPE
PARAMETER_DEFAULT STRING SQL リテラル値としてのパラメータのデフォルト値(常に NULL
IS_AGGREGATE STRING 集計パラメータであるかどうかにかかわらず、常に NULL です

例 1:

次の例では、INFORMATION_SCHEMA.PARAMETERS ビューからすべてのパラメータを取得しています。デフォルト プロジェクト(myproject)にある mydataset のルーティンに関するメタデータが返されます。

クエリを実行するには:

Console

  1. Cloud Console で BigQuery ウェブ UI を開きます。

    Cloud Console に移動する

  2. [クエリエディタ] ボックスに、次の標準 SQL クエリを入力します。INFORMATION_SCHEMA には標準 SQL 構文を使用する必要があります。標準 SQL は Cloud Console のデフォルトの構文です。

    SELECT
     * EXCEPT(is_typed)
    FROM
     mydataset.INFORMATION_SCHEMA.PARAMETERS
    WHERE
     table_type="BASE TABLE"
    
  3. [実行] をクリックします。

コマンドライン

query コマンドで、--nouse_legacy_sql または --use_legacy_sql=false フラグを使用して標準 SQL 構文を指定します。INFORMATION_SCHEMA クエリには標準 SQL 構文を使用する必要があります。

クエリを実行するには、次のように入力します。

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

結果は次のようになります。

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