公式ドキュメントにない方法で、BigQueryにあるデータセット内の全てのテーブル・ビューの情報を取得してみた
はじめに
9月から新しい案件を担当することになり、実務としては初めてGCP(Google Cloud Platform)を使ってます。
これを機に、私用の個人アカウントでもGCPを触って練習し始めるようになりました。
適当にデータセットを作ってテーブルやビュー、永続関数を作ってみたところ、案の定データセット内がごちゃごちゃに。
BigQueryのデータセットにあるデータの情報を楽に取得する方法を知りたいと思うようになりました。
参考：公式ドキュメントに記載してある方法
まず最初に、公式ドキュメント*1に書いてある方法を簡単にまとめます。
以下のクエリを実行すると、指定したデータセット内にある全てのテーブルとビューに関する情報を取得することができます。
-- mydataset：データセット名 SELECT * FROM mydataset.INFORMATION_SCHEMA.TABLES
この方法で取得できるのは以下の項目です。
欲しい情報が全て取得できなかった
最終更新日時やテーブルの行数、テーブルのデータサイズの情報も欲しいのですが、上記のクエリでは取得できません。
というわけで、何か別の方法があるばず...！と思い、公式ドキュメントで探したのですが見つからず...
最終更新日時はまだしも、行数とデータサイズが取得できないということはなさそうだと思い、色々試しました。
本題
色々やり方を模索していると、ある1つの方法を発見しました。
SELECT * FROM mydataset.__TABLES__
このクエリを実行すると、以下の情報を取得できます。
- project_id：データセットを含むプロジェクトの名前
- dataset_id：テーブルまたはビューを含むデータセットの名前
- table_id：テーブルまたはビューの名前
- creation_time：作成日時
- last_modified_time：最終更新日時
- row_count：行数*2
- size_bytes：データサイズ（byte）*3
- type：テーブルタイプ（1、2、3、4のいずれか）
ここで、取得できる情報について補足説明をします。
まず、このクエリで取得できる作成日時と最終更新日時ですが、ミリ秒までを表示しているUnix Timestampで格納されています。
そのため、このまま出力しても日時が分からないので、変換する必要があります。
次に、テーブルタイプについてですが、このクエリで実行すると数値で取得します。
以下が、その数値とテーブルタイプの対応表です。*4
- 1：BASE TABLE
- 2：VIEW
- 3：EXTERNAL
- 4：OTHER*5
以上の補足説明を踏まえて、分かりやすい状態でテーブル・ビューの情報を取得できるよう、自分なりに改良しました。
size_bytesだとデータサイズが大きいときに分かりづらいと思うので、単位がMBとGBで表示されるsize_mbとsize_gbの列も足してあります。
改良したクエリが以下のものです。
-- mydataset：データセット名 SELECT * EXCEPT(type, creation_time, last_modified_time), ROUND(SAFE_DIVIDE(size_bytes, (1000*1000)),1) as size_mb, ROUND(SAFE_DIVIDE(size_bytes, (1000*1000*1000)),2) as size_gb, CASE WHEN type = 1 THEN 'BASE TABLE' WHEN type = 2 THEN 'VIEW' WHEN type = 3 THEN 'EXTERNAL' WHEN type = 4 THEN 'OTHER' END type, TIMESTAMP_MILLIS(creation_time) AS creation_time, TIMESTAMP_MILLIS(last_modified_time) AS last_modified_time FROM mydataset.__TABLES__
追記
ブログ公開後に、MBは1024*1024、GBは1024*1024*1024ではないのか、というご質問が複数の方からありましたのでご回答します！
上記のクエリでは、国際電気標準会議(IEC)に則り、MBは1000*1000、GBは1000*1000*1000で計算しています。
今回、MBとGBを採用している理由は、MiBとGiBより多くの方に普及しているためです。
※ MiBが1024*1024、GiBが1024*1024*1024
おわりに
テーブルの情報は、クエリを実行しなくても1つずつ確認することはできます。
しかし、データセット内のテーブル・ビューの数が膨大で一覧表みたいな形で取得したい場合に上記のクエリが使えるかと思います。
__TABLES__は公式ドキュメントには記載されていないので、この記事が私みたいにテーブル・ビューの最終更新日時やデータサイズなどを含めた情報の一覧表を出力したい方の手助けができれば幸いです。
