INFORMATION_SCHEMA を使用したデータセットのメタデータの取得

INFORMATION_SCHEMA には、データセットのメタデータに関する次のビューが含まれます。

  • SCHEMATA
  • SCHEMATA_OPTIONS

始める前に

このドキュメントの各タスクを実行するために必要な権限をユーザーに与える Identity and Access Management(IAM)のロールを付与します。

必要な権限

データセット メタデータの SCHEMATA ビューおよび SCHEMATA_OPTIONS ビューを取得するには、プロジェクト レベルで bigquery.datasets.get IAM 権限が必要です。

次の IAM 事前定義ロールには、SCHEMATA ビューと SCHEMATA_OPTIONS ビューの取得に必要な権限が含まれています。

  • roles/bigquery.admin
  • roles/bigquery.dataEditor
  • roles/bigquery.dataOwner
  • roles/bigquery.dataViewer

BigQuery のきめ細かい権限の詳細については、役割と権限をご覧ください。

構文

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

リージョン修飾子を指定しない場合、メタデータは us マルチリージョンから取得されます。

-- Returns metadata for datasets in a region.
SELECT * FROM region-us.INFORMATION_SCHEMA.SCHEMATA;

SCHEMATA ビュー

INFORMATION_SCHEMA.SCHEMATA ビューにクエリを実行すると、現在のユーザーがアクセスできるプロジェクトの各データセットが 1 行で返されます。

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

列名 データ型
CATALOG_NAME STRING データセットを含むプロジェクトの名前
SCHEMA_NAME STRING データセットの名前(datasetId ともいいます)
SCHEMA_OWNER STRING 値は常に NULL
CREATION_TIME TIMESTAMP データセットの作成日時
LAST_MODIFIED_TIME TIMESTAMP データセットの最終更新日時
LOCATION STRING データセットの地理的位置
DDL STRING データセットの作成に使用できる CREATE SCHEMA DDL ステートメント
DEFAULT_COLLATION_NAME STRING デフォルトの照合仕様の名前(存在する場合)。それ以外の場合は、NULL です。

Examples

次の例では、今後の使用のために予約されている schema_owner を除き、すべての列を INFORMATION_SCHEMA.SCHEMATA ビューから取得します。デフォルト プロジェクト myproject 内のすべてのデータセットのメタデータが返されます。

デフォルト プロジェクト以外のプロジェクトに対してクエリを実行するには、この形式 `project_id`.INFORMATION_SCHEMA.view でそのプロジェクト ID をデータセットに追加します。例: `myproject`.INFORMATION_SCHEMA.SCHEMATA

SELECT
  * EXCEPT (schema_owner)
FROM
  INFORMATION_SCHEMA.SCHEMATA;

次のような結果になります。読みやすくするために、一部の列は結果から除外されています。

+----------------+---------------+---------------------+---------------------+------------+------------------------------------------+
|  catalog_name  |  schema_name  |    creation_time    | last_modified_time  |  location  |                   ddl                    |
+----------------+---------------+---------------------+---------------------+------------+------------------------------------------+
| myproject      | mydataset1    | 2018-11-07 19:50:24 | 2018-11-07 19:50:24 | US         | CREATE SCHEMA `myproject.mydataset1`     |
|                |               |                     |                     |            | OPTIONS(                                 |
|                |               |                     |                     |            |   location="us"                          |
|                |               |                     |                     |            | );                                       |
+----------------+---------------+---------------------+---------------------+------------+------------------------------------------+
| myproject      | mydataset2    | 2018-07-16 04:24:22 | 2018-07-16 04:24:22 | US         | CREATE SCHEMA `myproject.mydataset2`     |
|                |               |                     |                     |            | OPTIONS(                                 |
|                |               |                     |                     |            |   default_partition_expiration_days=3.0, |
|                |               |                     |                     |            |   location="us"                          |
|                |               |                     |                     |            | );                                       |
+----------------+---------------+---------------------+---------------------+------------+------------------------------------------+
| myproject      | mydataset3    | 2018-02-07 21:08:45 | 2018-05-01 23:32:53 | US         | CREATE SCHEMA `myproject.mydataset3`     |
|                |               |                     |                     |            | OPTIONS(                                 |
|                |               |                     |                     |            |   description="My dataset",              |
|                |               |                     |                     |            |   location="us"                          |
|                |               |                     |                     |            | );                                       |
+----------------+---------------+---------------------+---------------------+------------+------------------------------------------+

SCHEMATA_OPTIONS ビュー

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

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

列名 データ型
CATALOG_NAME STRING データセットを含むプロジェクトの名前
SCHEMA_NAME STRING データセットの名前(datasetId ともいいます)
OPTION_NAME STRING options テーブル内の名前値の 1 つ
OPTION_TYPE STRING オプション テーブル内のデータ型の値の 1 つ
OPTION_VALUE STRING オプション テーブル内の値のオプションの 1 つ

オプション テーブル
OPTION_NAME OPTION_TYPE OPTION_VALUE
default_table_expiration_days FLOAT64 データセット内のすべてのテーブルのデフォルトの有効期間(日数)
friendly_name STRING わかりやすいデータセット名
description STRING データセットの説明
labels ARRAY<STRUCT<STRING, STRING>> データセットのラベルを表す STRUCT の配列

例 1:

次の例では、INFORMATION_SCHEMA.SCHEMATA_OPTIONS ビューにクエリを実行して、デフォルト プロジェクト(myproject)内のすべてのデータセットのデフォルトのテーブル有効期限を取得します。

デフォルト プロジェクト以外のプロジェクトに対してクエリを実行するには、この形式 `project_id`.INFORMATION_SCHEMA.view でそのプロジェクト ID をデータセットに追加します。例: `myproject`.INFORMATION_SCHEMA.SCHEMATA_OPTIONS

SELECT
  *
FROM
  INFORMATION_SCHEMA.SCHEMATA_OPTIONS
WHERE
  option_name = 'default_table_expiration_days';

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

  +----------------+---------------+-------------------------------+-------------+---------------------+
  |  catalog_name  |  schema_name  |          option_name          | option_type |    option_value     |
  +----------------+---------------+-------------------------------+-------------+---------------------+
  | myproject      | mydataset3    | default_table_expiration_days | FLOAT64     | 0.08333333333333333 |
  | myproject      | mydataset2    | default_table_expiration_days | FLOAT64     | 90.0                |
  | myproject      | mydataset1    | default_table_expiration_days | FLOAT64     | 30.0                |
  +----------------+---------------+-------------------------------+-------------+---------------------+
  

例 2:

次の例では、INFORMATION_SCHEMA.SCHEMATA_OPTIONS ビューにクエリを実行して、デフォルト プロジェクト(myproject)のすべてのデータセットのラベルを取得します。

デフォルト プロジェクト以外のプロジェクトに対してクエリを実行するには、この形式 `project_id`.INFORMATION_SCHEMA.view でそのプロジェクト ID をデータセットに追加します。例: `myproject`.INFORMATION_SCHEMA.SCHEMATA_OPTIONS

SELECT
  *
FROM
  INFORMATION_SCHEMA.SCHEMATA_OPTIONS
WHERE
  option_name = 'labels';

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

  +----------------+---------------+-------------+---------------------------------+------------------------+
  |  catalog_name  |  schema_name  | option_name |          option_type            |      option_value      |
  +----------------+---------------+-------------+---------------------------------+------------------------+
  | myproject      | mydataset1    | labels      | ARRAY<STRUCT<STRING, STRING>>   | [STRUCT("org", "dev")] |
  | myproject      | mydataset2    | labels      | ARRAY<STRUCT<STRING, STRING>>   | [STRUCT("org", "dev")] |
  +----------------+---------------+-------------+---------------------------------+------------------------+