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

は、

INFORMATION_SCHEMA は、データセット、テーブル、ビューに関するメタデータへのアクセスを提供する一連のビューです。

INFORMATION_SCHEMA.TABLESINFORMATION_SCHEMA.TABLE_OPTIONS ビューにクエリを実行し、プロジェクト内のテーブルとビューに関するメタデータを取得できます。INFORMATION_SCHEMA.COLUMNSINFORMATION_SCHEMA.COLUMN_FIELD_PATHS ビューにクエリを実行し、テーブル内の列(フィールド)に関するメタデータを取得することもできます。

TABLESTABLE_OPTIONS ビューには、ビューに関する概要情報も含まれています。詳細情報を取得するには、INFORMATION_SCHEMA.VIEWS ビューにクエリを実行します。

必要な権限

テーブルに関する情報を取得するユーザーには、データセットに対する READER 役割が割り当てられているか、bigquery.tables.get 権限を含むプロジェクト レベルの IAM 役割が割り当てられている必要があります。プロジェクト レベルで bigquery.tables.get 権限が付与されているユーザーは、そのプロジェクト内のすべてのテーブルに関する情報を取得できます。事前定義されているプロジェクト レベルの IAM 役割のうち、bigquery.jobUserbigquery.user 以外の役割には、bigquery.tables.get 権限が含まれています。

また、bigquery.user 役割が割り当てられているユーザーには bigquery.datasets.create 権限もあります。そのため、bigquery.user 役割が割り当てられているユーザーは、自分が作成した任意のデータセット内のテーブルに関する情報を取得できます。bigquery.user 役割に割り当てられているユーザーがデータセットを作成すると、そのユーザーにはそのデータセットへの OWNER アクセス権が付与されます。データセットへの OWNER アクセス権により、ユーザーはそのデータセットとその中のすべてのテーブルを完全に制御できます。

BigQuery での IAM 役割と権限の詳細については、アクセス制御をご覧ください。データセット レベルの役割の詳細については、データセットに対する基本の役割をご覧ください。

TABLES ビュー

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

INFORMATION_SCHEMA.TABLES ビューにクエリを実行する場合は、データセット修飾子を使用する必要があります。クエリを送信するユーザーには、テーブルまたはビューを含むデータセットへのアクセス権が必要です。

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

列名 データ型
TABLE_CATALOG STRING データセットを含むプロジェクトの名前
TABLE_SCHEMA STRING テーブルまたはビューを含むデータセットの名前datasetId
TABLE_NAME STRING テーブルまたはビューの名前tableId
TABLE_TYPE STRING 次のテーブルタイプ:
IS_INSERTABLE_INTO STRING YES または NO(テーブルが DML INSERT 文をサポートしているかどうかによって変わります)
IS_TYPED STRING 値は常に NO です。
CREATION_TIME TIMESTAMP テーブルの作成時間

例 1:

次の例では、将来用に予約されている is_typed を除き、すべての列を INFORMATION_SCHEMA.TABLES ビューから取得します。デフォルト プロジェクト(myproject)にある mydataset のすべてのテーブルに対するメタデータが返されます。

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

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

INFORMATION_SCHEMA.TABLES ビューにクエリを実行する場合は、データセット修飾子を使用する必要があります。クエリを送信するユーザーには、テーブルを含むデータセットへのアクセス権が必要です。

デフォルト プロジェクト以外のプロジェクトにクエリを実行する場合は、次の形式でプロジェクト ID をデータセットに追加します。`[PROJECT_ID]:[DATASET].INFORMATION_SCHEMA.[VIEW]`。例: `myproject:mydataset.INFORMATION_SCHEMA.TABLES`

クエリを実行するには:

Console

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

    BigQuery ウェブ UI に移動

  2. [Query editor] ボックスに、次の標準 SQL クエリを入力します。 INFORMATION_SCHEMA では標準の SQL 構文が必要です。標準 SQL は GCP Console のデフォルトの構文です。

    SELECT
     * EXCEPT(is_typed)
    FROM
     `mydataset.INFORMATION_SCHEMA.TABLES`
    
  3. [Run] をクリックします。

コマンドライン

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 のテーブルに対するメタデータが返されます。

INFORMATION_SCHEMA.TABLES ビューにクエリを実行する場合は、データセット修飾子を使用する必要があります。クエリを送信するユーザーには、テーブルを含むデータセットへのアクセス権が必要です。

デフォルト プロジェクト以外のプロジェクトにクエリを実行する場合は、次の形式でプロジェクト ID をデータセットに追加します。`[PROJECT_ID]:[DATASET].INFORMATION_SCHEMA.[VIEW]`。例: `myproject:mydataset.INFORMATION_SCHEMA.TABLES`

クエリを実行するには:

Console

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

    BigQuery ウェブ UI に移動

  2. [Query editor] ボックスに、次の標準 SQL クエリを入力します。 INFORMATION_SCHEMA では標準の SQL 構文が必要です。標準 SQL は GCP Console のデフォルトの構文です。

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

コマンドライン

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`
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 ビューにクエリを実行する場合は、データセット修飾子を使用する必要があります。クエリを送信するユーザーには、テーブルまたはビューを含むデータセットへのアクセス権が必要です。

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

列名 データ型
TABLE_CATALOG STRING データセットを含むプロジェクトの名前
TABLE_SCHEMA STRING テーブルまたはビューを含むデータセットの名前datasetId
TABLE_NAME STRING テーブルまたはビューの名前tableId
OPTION_NAME STRING options テーブル内の名前値の 1 つ
OPTION_TYPE STRING options テーブルのデータ型値の 1 つ
OPTION_VALUE STRING options テーブルの値オプションの 1 つ
options テーブル
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 の配列

例 1:

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

INFORMATION_SCHEMA.TABLE_OPTIONS ビューにクエリを実行する場合は、データセット修飾子を使用する必要があります。クエリを送信するユーザーには、テーブルを含むデータセットへのアクセス権が必要です。

デフォルト プロジェクト以外のプロジェクトにクエリを実行する場合は、次の形式でプロジェクト ID をデータセットに追加します。`[PROJECT_ID]:[DATASET].INFORMATION_SCHEMA.[VIEW]`。例: `myproject:mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS`

クエリを実行するには:

Console

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

    BigQuery ウェブ UI に移動

  2. [Query editor] ボックスに、次の標準 SQL クエリを入力します。 INFORMATION_SCHEMA では標準の SQL 構文が必要です。標準 SQL は GCP Console のデフォルトの構文です。

    SELECT
     *
    FROM
     `mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS`
    WHERE
     option_name="expiration_timestamp"
    
  3. [Run] をクリックします。

コマンドライン

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

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

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)内にあります。

デフォルト プロジェクト以外のプロジェクトにクエリを実行する場合は、次の形式でプロジェクト ID をデータセットに追加します。`[PROJECT_ID]:[DATASET].INFORMATION_SCHEMA.[VIEW]`。例: `myproject:mydataset.INFORMATION_SCHEMA.SCHEMATA_OPTIONS`

クエリを実行するには:

Console

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

    BigQuery ウェブ UI に移動

  2. [Query editor] ボックスに、次の標準 SQL クエリを入力します。 INFORMATION_SCHEMA では標準の SQL 構文が必要です。標準 SQL は GCP Console のデフォルトの構文です。

    SELECT
     *
    FROM
     `mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS`
    WHERE
     option_name="description" AND option_value LIKE "%test%"
    
  3. [Run] をクリックします。

コマンドライン

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

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

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 ビューにクエリを実行する場合は、データセット修飾子を使用する必要があります。クエリを送信するユーザーには、テーブルを含むデータセットへのアクセス権が必要です。

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

列名 データ型
TABLE_CATALOG STRING データセットを含むプロジェクトの名前
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 STRING テーブルのクラスタリング列内の列の 1 から始まるオフセット。テーブルがクラスタ化テーブルではない場合、値は NULL

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

このテーブルは bigquery-public-data プロジェクトにあるため、次の形式でプロジェクト ID をデータセットに追加します。`[PROJECT_ID]:[DATASET].INFORMATION_SCHEMA.[VIEW]`。例: `bigquery-public-data:census_bureau_usa.INFORMATION_SCHEMA.TABLES`

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

  • IS_GENERATED
  • GENERATION_EXPRESSION
  • IS_STORED
  • IS_UPDATABLE

INFORMATION_SCHEMA.COLUMNS ビューにクエリを実行する場合は、データセット修飾子を使用する必要があります。クエリを送信するユーザーには、テーブルを含むデータセットへのアクセス権が必要です。

クエリを実行するには:

Console

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

    BigQuery ウェブ UI に移動

  2. [Query editor] ボックスに、次の標準 SQL クエリを入力します。 INFORMATION_SCHEMA では標準の SQL 構文が必要です。標準 SQL は GCP 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"
    
  3. [Run] をクリックします。

コマンドライン

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

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

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

INFORMATION_SCHEMA.COLUMN_FIELD_PATHS ビューにクエリを実行すると、クエリ結果には、RECORD(または STRUCT)列内にネストされた列ごとに 1 行が含まれます。

INFORMATION_SCHEMA.COLUMN_FIELD_PATHS ビューに対するクエリには、データセット修飾子が必要です。クエリを送信するユーザーには、テーブルを含むデータセットへのアクセス権が必要です。

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

列名 データ型
TABLE_CATALOG STRING データセットを含むプロジェクトの名前
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 の一般公開データセット プログラムの一部です。

このテーブルは bigquery-public-data プロジェクトにあるため、次の形式でプロジェクト ID をデータセットに追加します。`[PROJECT_ID]:[DATASET].INFORMATION_SCHEMA.[VIEW]`。例: `bigquery-public-data:github_repos.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS`

次のように、commits テーブルにはネストされた列とネストされて繰り返された列があります。

  • author: ネストされた RECORD
  • committer: ネストされた RECORD
  • trailer: ネストされて繰り返された RECORD
  • difference: ネストされて繰り返された RECORD

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

INFORMATION_SCHEMA.COLUMN_FIELD_PATHS ビューに対するクエリには、データセット修飾子が必要です。クエリを送信するユーザーには、テーブルを含むデータセットへのアクセス権が必要です。

クエリを実行するには:

Console

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

    BigQuery ウェブ UI に移動

  2. [Query editor] ボックスに、次の標準 SQL クエリを入力します。 INFORMATION_SCHEMA では標準の SQL 構文が必要です。標準 SQL は GCP 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"
    
  3. [Run] をクリックします。

コマンドライン

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

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

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_catalogtable_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.TABLESTABLE_OPTIONSCOLUMNS ビューにクエリを実行して、デフォルト プロジェクト(myproject.mydataset)内の mydataset のテーブルに関するメタデータを取得します。このデフォルト プロジェクトには次の 2 つのテーブルがあります。

  • mytable1: github_repos 一般公開データセットの commits テーブルと同じスキーマを使用します。
  • mytable2: census_bureau_usa 一般公開データセットの population_by_zip_2010 テーブルと同じスキーマを使用します。

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

デフォルト プロジェクト以外のプロジェクトにクエリを実行する場合は、次の形式でプロジェクト ID をデータセットに追加します。`[PROJECT_ID]:[DATASET].INFORMATION_SCHEMA.[VIEW]`。例: `myproject:mydataset.INFORMATION_SCHEMA.SCHEMATA_OPTIONS`

クエリを実行するには:

Console

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

    BigQuery ウェブ UI に移動

  2. [Query editor] ボックスに、次の標準 SQL クエリを入力します。 INFORMATION_SCHEMA では標準の SQL 構文が必要です。標準 SQL は GCP 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>
    ) 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>
    ) 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 MakeColumnList(
      columns ARRAY>
    ) AS (
      IFNULL(
        CONCAT(
          '(\n',
          (SELECT STRING_AGG(CONCAT('  ', column_name, ' ', data_type), ',\n')
           FROM UNNEST(columns)),
          '\n)\n'
        ),
        ''
      )
    );
    
    CREATE TEMP FUNCTION MakeOptionList(
      options ARRAY>
    ) 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_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
    

コマンドライン

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

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

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_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_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 MakeColumnList(
  columns ARRAY<STRUCT<column_name STRING, data_type STRING, is_partitioning_column STRING, clustering_ordinal_position INT64>>
) AS (
  IFNULL(
    CONCAT(
      '(\n',
      (SELECT STRING_AGG(CONCAT('  ', column_name, ' ', data_type), ',\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_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,                                                                                                                                                 |
|   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"                                                                                                       |
| )                                                                                                                                                                 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+

次のステップ

このページは役立ちましたか?評価をお願いいたします。

フィードバックを送信...

ご不明な点がありましたら、Google のサポートページをご覧ください。