BigQuery で INFORMATION_SCHEMA から CREATE TABLE 文が取得できるようになりました!

2021.04.07

こんにちは、みかみです。

ついにきました! INFORMATION_SCHEMATABLES ビューから、既存テーブルの CREATE TABLE 文が取得できるようになりました!

何がうれしいの?

システムで使用しているデータベースのテーブル定義は CREATE TABLE 構文で SQL ファイルに記載して、コード管理するのが一般的ではないかと思います。

BigQuery の既存テーブルのテーブル定義は、これまでも JSON 形式では取得することができましたが、CREATE TABLE 構文の SQL として欲しい場合、手動で作成する必要がありました。

「テーブルにデータ入れるなら、どうせ初めに手動でテーブル作成しないといけないんじゃないの?」と思われるかもしれんせんが、BigQuery では CSV などのファイルからフォーマットを自動検出してデータをロードする時に自動でテーブルを作成することも可能です。

自動作成ではテーブルやカラムの論理名の付与やパーティショニングなどはしてくれないので、実際にシステムで利用するテーブルは作成しなおす必要があると思いますが、カラムの物理名やデータ型が定義済みのベースとなるテーブル定義が SQL で取得できれば、テーブルを作成&管理するのがだいぶ楽になるのです。

ということで、2021/04/07 現在まだプレビューですが、INFORMATION_SCHEMA から DDL が取得できるようになったこの BigQuery のアップデート、待ってました!v

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_SCHEMATABLES では、テーブルだけでなくビューの情報も参照できます。 確認したところ、ビューの場合にも TABLES.DDL カラムで CREATE VIEW 文が参照できました。

これでテーブルやビューの管理がずいぶん楽になりますv

まとめ(所感)

既存テーブルの DDL を取得できるようになった今回のアップデート、きましたねv

標準 SQL に対応しているとはいえ、一部利用できない SQL 構文もあり、bq コマンドと併用しないと若干使いにくい感が否めなかった BigQuery ですが、 ここのところ ALTER 構文などの標準 SQL 関連のアップデートも続いており、ユーザーとしてはうれしい限りです!

今後もさらなるアップデートに期待してます!

参考