BigQuery で INFORMATION_SCHEMA から CREATE TABLE 文が取得できるようになりました！
こんにちは、みかみです。
ついにきました！
INFORMATION_SCHEMA の TABLES ビューから、既存テーブルの CREATE TABLE 文が取得できるようになりました！
何がうれしいの？
システムで使用しているデータベースのテーブル定義は CREATE TABLE 構文で SQL ファイルに記載して、コード管理するのが一般的ではないかと思います。
BigQuery の既存テーブルのテーブル定義は、これまでも JSON 形式では取得することができましたが、CREATE TABLE 構文の SQL として欲しい場合、手動で作成する必要がありました。
「テーブルにデータ入れるなら、どうせ初めに手動でテーブル作成しないといけないんじゃないの？」と思われるかもしれんせんが、BigQuery では CSV などのファイルからフォーマットを自動検出してデータをロードする時に自動でテーブルを作成することも可能です。
自動作成ではテーブルやカラムの論理名の付与やパーティショニングなどはしてくれないので、実際にシステムで利用するテーブルは作成しなおす必要があると思いますが、カラムの物理名やデータ型が定義済みのベースとなるテーブル定義が SQL で取得できれば、テーブルを作成＆管理するのがだいぶ楽になるのです。
ということで、2021/04/07 現在まだプレビューですが、INFORMATION_SCHEMA から DDL が取得できるようになったこの BigQuery のアップデート、待ってました！ｖ
JSON 形式のテーブル定義を取得
論理名やパーティショニング、クラスタリングや有効期限などの設定された、以下のテーブルのスキーマ情報を取得してみます。
JSON 形式のテーブルスキーマの情報は、bq show --schema コマンドで取得することができます。
Cloud Shell から以下のコマンドで、テーブルスキーマを JSON ファイルに出力しました。
bq show --schema --format=prettyjson dataset_1.table_A1_cp > schema_table_A1_cp.json
出力した JSON ファイルの中身を見てみます。
[
{
"description": "STRING\u578b\u30ab\u30e9\u30e0",
"mode": "REQUIRED",
"name": "col_string",
"type": "STRING"
},
{
"description": "INTEGER\u578b\u30ab\u30e9\u30e0",
"mode": "REQUIRED",
"name": "col_integer",
"type": "INTEGER"
},
{
"description": "DATE\u578b\u30ab\u30e9\u30e0",
"name": "col_date",
"type": "DATE"
},
{
"description": "DATETIME\u578b\u30ab\u30e9\u30e0",
"name": "col_datetime",
"type": "DATETIME"
},
{
"description": "TIMESTAMP\u578b\u30ab\u30e9\u30e0",
"name": "col_timestamp",
"type": "TIMESTAMP"
},
{
"description": "RECORD\u578b\u30ab\u30e9\u30e0",
"fields": [
{
"description": "\u30ec\u30b3\u30fc\u30c9A",
"mode": "REPEATED",
"name": "a",
"type": "STRING"
},
{
"description": "\u30ec\u30b3\u30fc\u30c9B",
"name": "b",
"type": "BOOLEAN"
}
],
"name": "col_record",
"type": "RECORD"
}
]
カラムの物理名やデータ型、NOT NULL 制約の情報は取得できましたが、論理名はユニコードになっているので、 標準 SQL のテーブル定義に慣れていると、可読性は良くありません。。
また、パーティショニングやクラスタリング、有効期限などその他のテーブル情報は、別途 bq show コマンドでテーブル名を指定して確認する必要があります。
mikami_yuki@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq show dataset_1.table_A1_cp
Table cm-da-mikami-yuki-258308:dataset_1.table_A1_cp
Last modified Schema Total Rows Total Bytes Expiration Time Partitioning Clustered Fields Labels
----------------- ------------------------------------ ------------ ------------- ----------------- -------------------------------------------------------- ------------------------- ----------------
07 Apr 09:26:16 |- col_string: string (required) 0 0 31 Dec 14:59:59 MONTH (field: col_timestamp, expirationMs: 7776000000) col_string, col_integer type:partition
|- col_integer: integer (required) sample:table_a
|- col_date: date
|- col_datetime: datetime
|- col_timestamp: timestamp
+- col_record: record
| |- a: string (repeated)
| |- b: boolean
やはり、テーブル定義をコード管理する場合、このままのテーブル情報だと扱いにくそうです。。。
INFORMATION_SCHEMA から SQL のテーブル定義を取得
同じテーブルのテーブル定義を、INFORMATION_SCHEMA から取得してみます！
※2021/04/07 現在、日本語の INFORMATION_SCHEMA.TABLES ビューのドキュメントには、まだ DDL カラムの記載はありませんでした。
BigQuery 管理コンソールのクエリエディタから、以下の SQL を実行しました。
SELECT table_name, ddl FROM `cm-da-mikami-yuki-258308`.dataset_1.INFORMATION_SCHEMA.TABLES WHERE table_name = 'table_A1_cp' ;
取得した DDL は以下です。
CREATE TABLE `cm-da-mikami-yuki-258308.dataset_1.table_A1_cp`
(
col_string STRING NOT NULL OPTIONS(description="STRING型カラム"),
col_integer INT64 NOT NULL OPTIONS(description="INTEGER型カラム"),
col_date DATE OPTIONS(description="DATE型カラム"),
col_datetime DATETIME OPTIONS(description="DATETIME型カラム"),
col_timestamp TIMESTAMP OPTIONS(description="TIMESTAMP型カラム"),
col_record STRUCT<a ARRAY<STRING> OPTIONS(description="レコードA"), b BOOL OPTIONS(description="レコードB")> OPTIONS(description="RECORD型カラム")
)
PARTITION BY TIMESTAMP_TRUNC(col_timestamp, MONTH)
CLUSTER BY col_string, col_integer
OPTIONS(
partition_expiration_days=90.0,
expiration_timestamp=TIMESTAMP "2021-12-31T14:59:59.000Z",
description="table_Aから作成",
labels=[("sample", "table_a"), ("type", "partition")]
);
カラムの物理名や論理名、データ型や NOT NULL 定義はもちろん、パーティショニングやクラスタリング、データの有効期限やラベル情報など、SQL でテーブル定義を取得することができました！
これならこのままコードとして管理できますし、既存テーブルの一部を変更して新しいテーブルを作成する場合や、CREATE TABLE 構文でパーティショニング定義する場合どう書くんだっけ？ な時にも参照できるので便利です！
なお、カラム名を指定せず SELECT * で参照すると、ddl カラム値は取得できませんでした。
また、GCS に配置したファイルなどの外部データを参照する外部テーブルの DDL は、やはり取得できませんでした。
INFORMATION_SCHEMA の TABLES では、テーブルだけでなくビューの情報も参照できます。
確認したところ、ビューの場合にも TABLES.DDL カラムで CREATE VIEW 文が参照できました。
これでテーブルやビューの管理がずいぶん楽になりますｖ
まとめ（所感）
既存テーブルの DDL を取得できるようになった今回のアップデート、きましたねｖ
標準 SQL に対応しているとはいえ、一部利用できない SQL 構文もあり、bq コマンドと併用しないと若干使いにくい感が否めなかった BigQuery ですが、
ここのところ ALTER 構文などの標準 SQL 関連のアップデートも続いており、ユーザーとしてはうれしい限りです！
今後もさらなるアップデートに期待してます！
