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

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

  • ROUTINES
  • ROUTINE_OPTIONS
  • PARAMETERS

必要な権限

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

構文

これらのビューに対するクエリには、データセット修飾子またはリージョン修飾子を指定する必要があります。

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

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] ページを開きます。

    [BigQuery] ページに移動

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

    SELECT
 *
FROM
 mydataset.INFORMATION_SCHEMA.ROUTINES

  3. [実行] をクリックします。

bq

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 options テーブル内の名前値の 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] ページを開きます。

    [BigQuery] ページに移動

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

    SELECT
 *
FROM
 mydataset.INFORMATION_SCHEMA.ROUTINE_OPTIONS

  3. [実行] をクリックします。

bq

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 パラメータのモード（IN、OUT、INOUT、または 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] ページを開きます。

    [BigQuery] ページに移動

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

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

  3. [実行] をクリックします。

bq

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