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

INFORMATION_SCHEMA には、テーブルメタデータ用の以下のビューが含まれています。

テーブルに関するメタデータ用の TABLES および TABLE_OPTIONS
列とフィールドに関するメタデータ用の COLUMNS および COLUMN_FIELD_PATHS

TABLES と TABLE_OPTIONS には、ビューに関する概要情報も含まれています。詳細については、代わりに VIEWS を照会してください。

必要な権限

TABLES と TABLE_OPTIONS については、次の権限が付与されている必要があります。

bigquery.tables.get
bigquery.tables.list
bigquery.routines.get
bigquery.routines.list

COLUMNS と COLUMN_FIELD_PATHS については、次の権限が付与されている必要があります。

bigquery.tables.get
bigquery.tables.list

構文

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

-- Returns metadata for tables in a single dataset.
SELECT * FROM myDataset.INFORMATION_SCHEMA.TABLES;

-- Returns metadata for tables in a region.
SELECT * FROM region-us.INFORMATION_SCHEMA.TABLES;

`TABLES` ビュー

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

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

列名	データ型	値
`TABLE_CATALOG`	`STRING`	データセットを含むプロジェクトのプロジェクト ID
`TABLE_SCHEMA`	`STRING`	`datasetId` とも呼ばれる、テーブルやビューを含むデータセットの名前
`TABLE_NAME`	`STRING`	テーブルまたはビューの名前（`tableId` とも呼ばれる）
`TABLE_TYPE`	`STRING`	テーブルタイプ: `BASE TABLE`: BigQuery テーブル `VIEW`: BigQuery のビュー `MATERIALIZED VIEW`: BigQuery のマテリアライズドビュー `EXTERNAL`: 外部データソースを参照するテーブル
`IS_INSERTABLE_INTO`	`STRING`	`YES` または `NO`（テーブルが DML INSERT ステートメントに対応しているかどうかによる）
`IS_TYPED`	`STRING`	値は常に `NO`
`CREATION_TIME`	`TIMESTAMP`	テーブルの作成時間

例

例 1:

次の例では、mydataset という名前のデータセット内のすべてのテーブルのテーブルメタデータを取得します。このクエリは、将来の使用のために予約されている is_typed を除き、INFORMATION_SCHEMA.TABLES ビューからすべての列を選択します。デフォルトプロジェクト（myproject）にある mydataset 内のすべてのテーブルに対するメタデータが返されます。

mydataset には、次のテーブルが含まれています。

mytable1: 標準の BigQuery テーブル
myview1: BigQuery のビュー

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

クエリを実行するには:

Console

Cloud Console で [BigQuery] ページを開きます。

[BigQuery] ページに移動
[クエリエディタ] ボックスに、次の標準 SQL クエリを入力します。INFORMATION_SCHEMA には標準 SQL 構文を使用する必要があります。標準 SQL は Cloud Console のデフォルトの構文です。
```
SELECT
 * EXCEPT(is_typed)
FROM
 mydataset.INFORMATION_SCHEMA.TABLES
```
注: INFORMATION_SCHEMA ビューの名前では大文字と小文字が区別されます。
[実行] をクリックします。

bq

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

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

bq query --nouse_legacy_sql \
'SELECT
   * EXCEPT(is_typed)
 FROM
   mydataset.INFORMATION_SCHEMA.TABLES'

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

  +----------------+---------------+----------------+------------+--------------------+---------------------+
  | table_catalog  | table_schema  |   table_name   | table_type | is_insertable_into |    creation_time    |
  +----------------+---------------+----------------+------------+--------------------+---------------------+
  | myproject      | mydataset     | mytable1       | BASE TABLE | YES                | 2018-10-29 20:34:44 |
  | myproject      | mydataset     | myview1        | VIEW       | NO                 | 2018-12-29 00:19:20 |
  +----------------+---------------+----------------+------------+--------------------+---------------------+

例 2:

次の例では、INFORMATION_SCHEMA.TABLES ビューからタイプが BASE TABLE のすべてのテーブルを取得します。is_typed 列は除外されます。デフォルトプロジェクト（myproject）にある mydataset のテーブルに対するメタデータが返されます。

クエリを実行するには:

Console

Cloud Console で [BigQuery] ページを開きます。

[BigQuery] ページに移動
[クエリエディタ] ボックスに、次の標準 SQL クエリを入力します。INFORMATION_SCHEMA には標準 SQL 構文を使用する必要があります。標準 SQL は Cloud Console のデフォルトの構文です。
```
SELECT
 * EXCEPT(is_typed)
FROM
 mydataset.INFORMATION_SCHEMA.TABLES
WHERE
 table_type="BASE TABLE"
```
注: INFORMATION_SCHEMA ビューの名前では大文字と小文字が区別されます。
[実行] をクリックします。

bq

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

bq query --nouse_legacy_sql \
'SELECT
   * EXCEPT(is_typed)
 FROM
   mydataset.INFORMATION_SCHEMA.TABLES
 WHERE
   table_type="BASE TABLE"'

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

  +----------------+---------------+----------------+------------+--------------------+---------------------+
  | table_catalog  | table_schema  |   table_name   | table_type | is_insertable_into |    creation_time    |
  +----------------+---------------+----------------+------------+--------------------+---------------------+
  | myproject      | mydataset     | mytable1       | BASE TABLE | NO                 | 2018-10-31 22:40:05 |
  +----------------+---------------+----------------+------------+--------------------+---------------------+

`TABLE_OPTIONS` ビュー

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

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

列名	データ型	値
`TABLE_CATALOG`	`STRING`	データセットを含むプロジェクトのプロジェクト ID
`TABLE_SCHEMA`	`STRING`	`datasetId` とも呼ばれる、テーブルやビューを含むデータセットの名前
`TABLE_NAME`	`STRING`	テーブルまたはビューの名前（`tableId` とも呼ばれる）
`OPTION_NAME`	`STRING`	options テーブル内の名前値の 1 つ
`OPTION_TYPE`	`STRING`	オプションテーブルのデータ型の値の 1 つ
`OPTION_VALUE`	`STRING`	オプションテーブルの値オプションの 1 つ

オプションテーブル

`OPTION_NAME`	`OPTION_TYPE`	`OPTION_VALUE`
`partition_expiration_days`	`FLOAT64`	パーティション分割テーブルのすべてのパーティションのデフォルトの存続期間（日数）
`expiration_timestamp`	`FLOAT64`	このテーブルの有効期限
`kms_key_name`	`STRING`	テーブルの暗号化に使用される Cloud KMS 鍵の名前
`friendly_name`	`STRING`	テーブルのわかりやすい名前
`description`	`STRING`	テーブルの説明
`labels`	`ARRAY<STRUCT<STRING, STRING>>`	テーブルのラベルを表す `STRUCT` の配列
`require_partition_filter`	`BOOL`	テーブルに対するクエリでパーティションフィルタが必要かどうか

例

例 1:

次の例では、INFORMATION_SCHEMA.TABLE_OPTIONS ビューにクエリを実行して、デフォルトプロジェクト（myproject）にある mydataset の全テーブルのデフォルトのテーブル有効期限を取得します。

クエリを実行するには:

Console

Cloud Console で [BigQuery] ページを開きます。

[BigQuery] ページに移動
[クエリエディタ] ボックスに、次の標準 SQL クエリを入力します。INFORMATION_SCHEMA には標準 SQL 構文を使用する必要があります。標準 SQL は Cloud Console のデフォルトの構文です。
```
SELECT
 *
FROM
 mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS
WHERE
 option_name="expiration_timestamp"
```
注: INFORMATION_SCHEMA ビューの名前では大文字と小文字が区別されます。
[実行] をクリックします。

bq

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

bq query --nouse_legacy_sql \
'SELECT
   *
 FROM
   mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS
 WHERE
   option_name="expiration_timestamp"'

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

  +----------------+---------------+------------+----------------------+-------------+--------------------------------------+
  | table_catalog  | table_schema  | table_name |     option_name      | option_type |             option_value             |
  +----------------+---------------+------------+----------------------+-------------+--------------------------------------+
  | myproject      | mydataset     | mytable1   | expiration_timestamp | TIMESTAMP   | TIMESTAMP "2020-01-16T21:12:28.000Z" |
  | myproject      | mydataset     | mytable2   | expiration_timestamp | TIMESTAMP   | TIMESTAMP "2021-01-01T21:12:28.000Z" |
  +----------------+---------------+------------+----------------------+-------------+--------------------------------------+

例 2:

次の例では、mydataset 内のすべてのテーブルから、テストデータを含むテーブルを絞り込んでそのメタデータを取得します。このクエリでは、説明に「test」が含まれているテーブルを見つけるために description オプションの値を使用します。mydataset はデフォルトプロジェクト（myproject）にあります。

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

クエリを実行するには:

Console

Cloud Console で [BigQuery] ページを開きます。

[BigQuery] ページに移動
[クエリエディタ] ボックスに、次の標準 SQL クエリを入力します。INFORMATION_SCHEMA には標準 SQL 構文を使用する必要があります。標準 SQL は Cloud Console のデフォルトの構文です。
```
SELECT
 *
FROM
 mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS
WHERE
 option_name="description" AND option_value LIKE "%test%"
```
注: INFORMATION_SCHEMA ビューの名前では大文字と小文字が区別されます。
[実行] をクリックします。

bq

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

bq query --nouse_legacy_sql \
'SELECT
   *
 FROM
   mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS
 WHERE
   option_name="description" AND option_value LIKE "%test%"'

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

  +----------------+---------------+------------+-------------+-------------+--------------+
  | table_catalog  | table_schema  | table_name | option_name | option_type | option_value |
  +----------------+---------------+------------+-------------+-------------+--------------+
  | myproject      | mydataset     | mytable1   | description | STRING      | "test data"  |
  | myproject      | mydataset     | mytable2   | description | STRING      | "test data"  |
  +----------------+---------------+------------+-------------+-------------+--------------+

`COLUMNS` ビュー

INFORMATION_SCHEMA.COLUMNS ビューにクエリを実行すると、クエリ結果として、テーブル内の列（フィールド）ごとに 1 行が表示されます。

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

列名	データ型	値
`TABLE_CATALOG`	`STRING`	データセットを含むプロジェクトのプロジェクト ID
`TABLE_SCHEMA`	`STRING`	`datasetId` とも呼ばれる、テーブルを含むデータセットの名前
`TABLE_NAME`	`STRING`	テーブルまたはビューの名前（`tableId` とも呼ばれる）
`COLUMN_NAME`	`STRING`	列の名前
`ORDINAL_POSITION`	`INT64`	テーブル内の列の 1 から始まるオフセット。_PARTITIONTIME や _PARTITIONDATE などの疑似列の場合、値は `NULL`
`IS_NULLABLE`	`STRING`	`YES` または `NO`（列のモードが `NULL` 値を許可するかどうかによる）
`DATA_TYPE`	`STRING`	列の標準 SQL データ型
`IS_GENERATED`	`STRING`	値は常に `NEVER`
`GENERATION_EXPRESSION`	`STRING`	値は常に `NULL`
`IS_STORED`	`STRING`	値は常に `NULL`
`IS_HIDDEN`	`STRING`	`YES` または `NO`（列が _PARTITIONTIME や _PARTITIONDATE などの疑似列であるかどうかによる）
`IS_UPDATABLE`	`STRING`	値は常に `NULL`
`IS_SYSTEM_DEFINED`	`STRING`	`YES` または `NO`（列が _PARTITIONTIME や _PARTITIONDATE などの疑似列であるかどうかによる）
`IS_PARTITIONING_COLUMN`	`STRING`	`YES` または `NO`（列がパーティショニング列かどうかによる）
`CLUSTERING_ORDINAL_POSITION`	`INT64`	テーブルのクラスタリング列内の列の 1 から始まるオフセット。テーブルがクラスタ化テーブルでない場合、値は `NULL`

例

次の例では、census_bureau_usa データセットにある population_by_zip_2010 テーブルの INFORMATION_SCHEMA.COLUMNS ビューからメタデータを取得しています。このデータセットは、BigQuery の一般公開データセットプログラムの一部です。

クエリ対象のテーブルは別のプロジェクト（bigquery-public-data プロジェクト）にあるため、この形式 `project_id`.dataset.INFORMATION_SCHEMA.view でプロジェクト ID をデータセットに追加します。例: `bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.TABLES

次の列は現時点で今後の使用のために予約されているため、クエリ結果から除外されます。

IS_GENERATED
GENERATION_EXPRESSION
IS_STORED
IS_UPDATABLE

クエリを実行するには:

Console

Cloud Console で [BigQuery] ページを開きます。

[BigQuery] ページに移動
[クエリエディタ] ボックスに、次の標準 SQL クエリを入力します。INFORMATION_SCHEMA には標準 SQL 構文を使用する必要があります。標準 SQL は Cloud Console のデフォルトの構文です。
```
SELECT
 * EXCEPT(is_generated, generation_expression, is_stored, is_updatable)
FROM
 `bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.COLUMNS
WHERE
 table_name="population_by_zip_2010"
```
注: INFORMATION_SCHEMA ビューの名前では大文字と小文字が区別されます。
[実行] をクリックします。

bq

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

bq query --nouse_legacy_sql \
'SELECT
   * EXCEPT(is_generated, generation_expression, is_stored, is_updatable)
 FROM
   `bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.COLUMNS
 WHERE
   table_name="population_by_zip_2010"'

結果は次のようになります。読みやすくするために table_catalog と table_schema は結果から除外されます。

+------------------------+-------------+------------------+-------------+-----------+-----------+-------------------+------------------------+-----------------------------+
|       table_name       | column_name | ordinal_position | is_nullable | data_type | is_hidden | is_system_defined | is_partitioning_column | clustering_ordinal_position |
+------------------------+-------------+------------------+-------------+-----------+-----------+-------------------+------------------------+-----------------------------+
| population_by_zip_2010 | zipcode     |                1 | NO          | STRING    | NO        | NO                | NO                     |                        NULL |
| population_by_zip_2010 | geo_id      |                2 | YES         | STRING    | NO        | NO                | NO                     |                        NULL |
| population_by_zip_2010 | minimum_age |                3 | YES         | INT64     | NO        | NO                | NO                     |                        NULL |
| population_by_zip_2010 | maximum_age |                4 | YES         | INT64     | NO        | NO                | NO                     |                        NULL |
| population_by_zip_2010 | gender      |                5 | YES         | STRING    | NO        | NO                | NO                     |                        NULL |
| population_by_zip_2010 | population  |                6 | YES         | INT64     | NO        | NO                | NO                     |                        NULL |
+------------------------+-------------+------------------+-------------+-----------+-----------+-------------------+------------------------+-----------------------------+

`COLUMN_FIELD_PATHS` ビュー

クエリ結果には、RECORD（または STRUCT）列内でネストされた列ごとに 1 行が表示されます。

INFORMATION_SCHEMA.COLUMN_FIELD_PATHS ビューにクエリを実行すると、クエリ結果として、RECORD（または STRUCT）列内でネストされた列ごとに 1 行が表示されます。

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

列名	データ型	値
`TABLE_CATALOG`	`STRING`	データセットを含むプロジェクトのプロジェクト ID
`TABLE_SCHEMA`	`STRING`	`datasetId` とも呼ばれる、テーブルを含むデータセットの名前
`TABLE_NAME`	`STRING`	テーブルまたはビューの名前（`tableId` とも呼ばれる）
`COLUMN_NAME`	`STRING`	列の名前
`FIELD_PATH`	`STRING`	RECORD 列または STRUCT 列内でネストされた列のパス
`DATA_TYPE`	`STRING`	列の標準 SQL データ型
`DESCRIPTION`	`STRING`	列の説明

例

次の例では、github_repos データセットにある commits テーブルの INFORMATION_SCHEMA.COLUMN_FIELD_PATHS ビューからメタデータを取得しています。このデータセットは、BigQuery の一般公開データセットプログラムの一部です。

commits テーブルには、以下のネストされた列と、ネストされた繰り返し列があります。

author: ネストされた RECORD 列
committer: ネストされた RECORD 列
trailer: ネストされた繰り返しの RECORD 列
difference: ネストされた繰り返しの RECORD 列

クエリでは、author 列と difference 列に関するメタデータを取得します。

クエリを実行するには:

Console

Cloud Console で [BigQuery] ページを開きます。

[BigQuery] ページに移動
[クエリエディタ] ボックスに、次の標準 SQL クエリを入力します。INFORMATION_SCHEMA には標準 SQL 構文を使用する必要があります。標準 SQL は Cloud Console のデフォルトの構文です。
```
SELECT
 *
FROM
 `bigquery-public-data`.github_repos.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
WHERE
 table_name="commits"
 AND column_name="author"
 OR column_name="difference"
```
注: INFORMATION_SCHEMA ビューの名前では大文字と小文字が区別されます。
[実行] をクリックします。

bq

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

bq query --nouse_legacy_sql \
'SELECT
   *
 FROM
   `bigquery-public-data`.github_repos.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
 WHERE
   table_name="commits"
   AND column_name="author"
   OR column_name="difference"'

結果は次のようになります。読みやすくするために table_catalog と table_schema は結果から除外されます。

  +------------+-------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
  | table_name | column_name |     field_path      |                                                                      data_type                                                                      | description |
  +------------+-------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
  | commits    | author      | author              | STRUCT<name STRING, email STRING, time_sec INT64, tz_offset INT64, date TIMESTAMP>                                                                  | NULL        |
  | commits    | author      | author.name         | STRING                                                                                                                                              | NULL        |
  | commits    | author      | author.email        | STRING                                                                                                                                              | NULL        |
  | commits    | author      | author.time_sec     | INT64                                                                                                                                               | NULL        |
  | commits    | author      | author.tz_offset    | INT64                                                                                                                                               | NULL        |
  | commits    | author      | author.date         | TIMESTAMP                                                                                                                                           | NULL        |
  | commits    | difference  | difference          | ARRAY<STRUCT<old_mode INT64, new_mode INT64, old_path STRING, new_path STRING, old_sha1 STRING, new_sha1 STRING, old_repo STRING, new_repo STRING>> | NULL        |
  | commits    | difference  | difference.old_mode | INT64                                                                                                                                               | NULL        |
  | commits    | difference  | difference.new_mode | INT64                                                                                                                                               | NULL        |
  | commits    | difference  | difference.old_path | STRING                                                                                                                                              | NULL        |
  | commits    | difference  | difference.new_path | STRING                                                                                                                                              | NULL        |
  | commits    | difference  | difference.old_sha1 | STRING                                                                                                                                              | NULL        |
  | commits    | difference  | difference.new_sha1 | STRING                                                                                                                                              | NULL        |
  | commits    | difference  | difference.old_repo | STRING                                                                                                                                              | NULL        |
  | commits    | difference  | difference.new_repo | STRING                                                                                                                                              | NULL        |
  +------------+-------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+

高度な例

次の例では、INFORMATION_SCHEMA.TABLES、TABLE_OPTIONS、COLUMNS の各ビューに対してクエリを実行し、デフォルトプロジェクト（myproject）内の mydataset に含まれるテーブルに関するメタデータを取得します。mydataset には 2 つのテーブルがあります。

mytable1: 一般公開データセット github_repos にある commits テーブルと同じスキーマを使用
mytable2: 一般公開データセット census_bureau_usa にある population_by_zip_2010 テーブルと同じスキーマを使用

この結果は、ユーザー定義関数でテーブルの再作成に必要な DDL ステートメントを作成するときに使用します。その後、クエリ結果で DDL ステートメントを使用して、mydataset にテーブルを再作成します。

クエリを実行するには:

Console

Cloud Console で [BigQuery] ページを開きます。

[BigQuery] ページに移動

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

CREATE TEMP FUNCTION MakePartitionByExpression(
  column_name STRING, data_type STRING
) AS (
  IF(
    column_name = '_PARTITIONTIME',
    'DATE(_PARTITIONTIME)',
    IF(
      data_type = 'TIMESTAMP',
      CONCAT('DATE(', column_name, ')'),
      column_name
    )
  )
);

CREATE TEMP FUNCTION MakePartitionByClause(
  columns ARRAY<STRUCT<column_name STRING, data_type STRING, is_nullable STRING,  is_partitioning_column STRING, clustering_ordinal_position INT64>>
) AS (
  IFNULL(
    CONCAT(
      'PARTITION BY ',
      (SELECT MakePartitionByExpression(column_name, data_type)
       FROM UNNEST(columns) WHERE is_partitioning_column = 'YES'),
      '\n'),
    ''
  )
);

CREATE TEMP FUNCTION MakeClusterByClause(
  columns ARRAY<STRUCT<column_name STRING, data_type STRING, is_nullable STRING,  is_partitioning_column STRING, clustering_ordinal_position INT64>>
) AS (
  IFNULL(
    CONCAT(
      'CLUSTER BY ',
      (SELECT STRING_AGG(column_name, ', ' ORDER BY clustering_ordinal_position)
        FROM UNNEST(columns) WHERE clustering_ordinal_position IS NOT NULL),
      '\n'
    ),
    ''
  )
);

CREATE TEMP FUNCTION MakeNullable(data_type STRING, is_nullable STRING)
AS (
  IF(not STARTS_WITH(data_type, 'ARRAY<') and is_nullable = 'NO', ' NOT NULL', '')
);

CREATE TEMP FUNCTION MakeColumnList(
  columns ARRAY<STRUCT<column_name STRING, data_type STRING, is_nullable STRING,  is_partitioning_column STRING, clustering_ordinal_position INT64>>
) AS (
  IFNULL(
    CONCAT(
      '(\n',
      (SELECT STRING_AGG(CONCAT('  ', column_name, ' ', data_type,  MakeNullable(data_type, is_nullable)), ',\n')
       FROM UNNEST(columns)),
      '\n)\n'
    ),
    ''
  )
);

CREATE TEMP FUNCTION MakeOptionList(
  options ARRAY<STRUCT<option_name STRING, option_value STRING>>
) AS (
  IFNULL(
    CONCAT(
      'OPTIONS (\n',
      (SELECT STRING_AGG(CONCAT('  ', option_name, '=', option_value), ',\n') FROM UNNEST(options)),
      '\n)\n'),
    ''
  )
);

WITH Components AS (
  SELECT
    CONCAT('`', table_catalog, '.', table_schema, '.', table_name, '`') AS table_name,
    ARRAY_AGG(
      STRUCT(column_name, data_type, is_nullable, is_partitioning_column, clustering_ordinal_position)
      ORDER BY ordinal_position
    ) AS columns,
    (SELECT ARRAY_AGG(STRUCT(option_name, option_value))
     FROM mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS AS t2
     WHERE t.table_name = t2.table_name) AS options
  FROM mydataset.INFORMATION_SCHEMA.TABLES AS t
  LEFT JOIN mydataset.INFORMATION_SCHEMA.COLUMNS
  USING (table_catalog, table_schema, table_name)
  WHERE table_type = 'BASE TABLE'
  GROUP BY table_catalog, table_schema, t.table_name
)
SELECT
  CONCAT(
    'CREATE OR REPLACE TABLE ',
    table_name,
    '\n',
    MakeColumnList(columns),
    MakePartitionByClause(columns),
    MakeClusterByClause(columns),
    MakeOptionList(options))
FROM Components

bq

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

QUERY_TEXT=$(cat <<ENDQUERY
CREATE TEMP FUNCTION MakePartitionByExpression(
  column_name STRING, data_type STRING
) AS (
  IF(
    column_name = '_PARTITIONTIME',
    'DATE(_PARTITIONTIME)',
    IF(
      data_type = 'TIMESTAMP',
      CONCAT('DATE(', column_name, ')'),
      column_name
    )
  )
);
CREATE TEMP FUNCTION MakePartitionByClause(
  columns ARRAY<STRUCT<column_name STRING, data_type STRING, is_nullable STRING,  is_partitioning_column STRING, clustering_ordinal_position INT64>>
) AS (
  IFNULL(
    CONCAT(
      'PARTITION BY ',
      (SELECT MakePartitionByExpression(column_name, data_type)
       FROM UNNEST(columns) WHERE is_partitioning_column = 'YES'),
      '\n'),
    ''
  )
);
CREATE TEMP FUNCTION MakeClusterByClause(
  columns ARRAY<STRUCT<column_name STRING, data_type STRING, is_nullable STRING,  is_partitioning_column STRING, clustering_ordinal_position INT64>>
) AS (
  IFNULL(
    CONCAT(
      'CLUSTER BY ',
      (SELECT STRING_AGG(column_name, ', ' ORDER BY clustering_ordinal_position)
        FROM UNNEST(columns) WHERE clustering_ordinal_position IS NOT NULL),
      '\n'
    ),
    ''
  )
);
CREATE TEMP FUNCTION MakeNullable(data_type STRING, is_nullable STRING)
AS (
  IF(not STARTS_WITH(data_type, 'ARRAY<') and is_nullable = 'NO', ' NOT NULL', '')
);
CREATE TEMP FUNCTION MakeColumnList(
  columns ARRAY<STRUCT<column_name STRING, data_type STRING, is_nullable STRING,  is_partitioning_column STRING, clustering_ordinal_position INT64>>
) AS (
  IFNULL(
    CONCAT(
      '(\n',
      (SELECT STRING_AGG(CONCAT('  ', column_name, ' ', data_type,  MakeNullable(data_type, is_nullable)), ',\n')
       FROM UNNEST(columns)),
      '\n)\n'
    ),
    ''
  )
);
CREATE TEMP FUNCTION MakeOptionList(
  options ARRAY<STRUCT<option_name STRING, option_value STRING>>
) AS (
  IFNULL(
    CONCAT(
      'OPTIONS (\n',
      (SELECT STRING_AGG(CONCAT('  ', option_name, '=', option_value), ',\n') FROM UNNEST(options)),
      '\n)\n'),
    ''
  )
);
WITH Components AS (
  SELECT
    CONCAT('\`', table_catalog, '.', table_schema, '.', table_name, '\`') AS table_name,
    ARRAY_AGG(
      STRUCT(column_name, data_type, is_nullable, is_partitioning_column, clustering_ordinal_position)
      ORDER BY ordinal_position
    ) AS columns,
    (SELECT ARRAY_AGG(STRUCT(option_name, option_value))
     FROM mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS AS t2
     WHERE t.table_name = t2.table_name) AS options
  FROM mydataset.INFORMATION_SCHEMA.TABLES AS t
  LEFT JOIN mydataset.INFORMATION_SCHEMA.COLUMNS
  USING (table_catalog, table_schema, table_name)
  WHERE table_type = 'BASE TABLE'
  GROUP BY table_catalog, table_schema, t.table_name
)
SELECT
  CONCAT(
    'CREATE OR REPLACE TABLE ',
    table_name,
    '\n',
    MakeColumnList(columns),
    MakePartitionByClause(columns),
    MakeClusterByClause(columns),
    MakeOptionList(options))
FROM Components
ENDQUERY
)
bq query --nouse_legacy_sql "$QUERY_TEXT"

出力は次のようになります。

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                                f0_                                                                                            |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE OR REPLACE TABLE `myproject.mydataset.population_by_zip_2010`                                                                                                          |
| (                                                                                                                                                                             |
|   zipcode STRING NOT NULL,                                                                                                                                                    |
|   geo_id STRING,                                                                                                                                                              |
|   minimum_age INT64,                                                                                                                                                          |
|   maximum_age INT64,                                                                                                                                                          |
|   gender STRING,                                                                                                                                                              |
|   population INT64                                                                                                                                                            |
| )                                                                                                                                                                             |
| OPTIONS (                                                                                                                                                                     |
|   expiration_timestamp=TIMESTAMP "2019-04-17T02:10:32.055Z"                                                                                                                   |
| )                                                                                                                                                                             |
| CREATE OR REPLACE TABLE `myproject.mydataset.commits`                                                                                                                         |
| (                                                                                                                                                                             |
|   commit STRING,                                                                                                                                                              |
|   tree STRING,                                                                                                                                                                |
|   parent ARRAY<STRING>,                                                                                                                                                 |
|   author STRUCT<name STRING, email STRING, time_sec INT64, tz_offset INT64, date TIMESTAMP>,                                                                            |
|   committer STRUCT<name STRING, email STRING, time_sec INT64, tz_offset INT64, date TIMESTAMP>,                                                                         |
|   subject STRING,                                                                                                                                                             |
|   message STRING,                                                                                                                                                             |
|   trailer ARRAY<STRUCT<key STRING, value STRING, email STRING>>,                                                                                                  |
|   difference ARRAY<STRUCT<old_mode INT64, new_mode INT64, old_path STRING, new_path STRING, old_sha1 STRING, new_sha1 STRING, old_repo STRING, new_repo STRING>>, |
|   difference_truncated BOOL,                                                                                                                                                  |
|   repo_name ARRAY<STRING>,                                                                                                                                              |
|   encoding STRING                                                                                                                                                             |
| )                                                                                                                                                                             |
| OPTIONS (                                                                                                                                                                     |
|   expiration_timestamp=TIMESTAMP "2019-04-17T03:12:03.248Z"                                                                                                                   |
| )                                                                                                                                                                             |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

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

必要な権限

構文

TABLES ビュー

例

例 1:

Console

bq

例 2:

Console

bq

TABLE_OPTIONS ビュー

オプション テーブル

例

例 1:

Console

bq

例 2:

Console

bq

COLUMNS ビュー

例

Console

bq

COLUMN_FIELD_PATHS ビュー

例

Console

bq

高度な例

Console

bq

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

`TABLES` ビュー

`TABLE_OPTIONS` ビュー

オプションテーブル

`COLUMNS` ビュー

`COLUMN_FIELD_PATHS` ビュー