2019/02/04 に GCP の BigQuery に、メタ情報 INFORMATION_SCHEMAと、ALTER TABLEが追加されました。
INFORMATION_SCHEMA
データセット一覧を取得するには下記のようにします。
SELECT
* EXCEPT(schema_owner)
FROM
INFORMATION_SCHEMA.SCHEMATA
結果は以下のようになります。
+----------------+---------------+---------------------+---------------------+-----------------+
| catalog_name | schema_name | creation_time | last_modified_time | location |
+----------------+---------------+---------------------+---------------------+-----------------+
| myproject | mydataset1 | 2018-11-07 19:50:24 | 2018-11-07 19:50:24 | US |
| myproject | mydataset2 | 2018-07-16 04:24:22 | 2018-07-16 04:24:22 | US |
| myproject | mydataset3 | 2018-02-07 21:08:45 | 2018-05-01 23:32:53 | asia-northeast1 |
+----------------+---------------+---------------------+---------------------+-----------------+
テーブル一覧を取得するには下記のようにします。
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 |
+----------------+---------------+----------------+------------+--------------------+---------------------+
カラム情報取得は下記のようにします。
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_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 |
+------------------------+-------------+------------------+-------------+-----------+-----------+-------------------+------------------------+-----------------------------+
さらなる詳細は下記ドキュメントを参照してください。
https://cloud.google.com/bigquery/docs/information-schema-intro
INFORMATION_SCHEMA の料金
INFORMATION_SCHEMAからデータを取得すると、最低でも10MB分のクエリコストがかかります。なぜならキャッシュされないからだそうですが、メタ情報でお金取るんかい! って感じです。ま、10MB分で0.00022円ですけどね。
テーブルの行数とサイズ
INFORMATION_SCHEMA に、テーブルの行数やサイズ情報は含まれていません。これまで通りメタテーブル [データセット名].__TABLES__ を参照する必要があります。
下記のような感じで、サイズが大きいデータセットやテーブルの確認ができるかと思っていたのですが、できないようです。残念。
SELECT dataset_id, sum(size_bytes) INFORMATION_SCHEMA group by dataset_id ORDER BY sum(size_bytes) DESC
ALTER TABLE
ALTER TABLE は作成済テーブルの情報を更新する DDL 文です。
MySQL・PostgreSQL・Oracleのように、作成済テーブル名変更・カラム追加・型変更ができるようになったかと期待したのですが、残念ながらそのようなものではありませんでした。
設定できるのは下記です。かなり限定されていますね。
- テーブル・パーティションの有効期限 (expiration) 設定
- 必須パーティションフィルタ (required partition filter) 設定
- KMSキー名設定
- 説明文 (description) 設定
- ラベル設定
具体的には下記のように ALTER TABLE 文を実行します。下記はテーブルに有効期限を設定し、説明文を更新しています。
#standardSQL
ALTER TABLE mydataset.mytable
SET OPTIONS (
expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 7 DAY),
description="Table that expires seven days from now"
)
詳細情報は下記を参照してください。
https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language