この記事は、BigQuery Advent Calendar 2021の23日目の記事です。8日目の記事も書いているので、このAdvent Calendarで2つ目の記事になります。メタデータ大好き人間なので、今日のネタもメタデータ関連です。
テーブルの最終更新日が知りたい
データ基盤の管理をやっていると、テーブルの最終更新日が知りたい!ということは多いです。具体的には「9時までにデータ転送終わっていないとまずいから、テーブルの最終更新日と時刻を比較して監視を入れておきたい」とかですね。監視はSQLを使ってやるのが簡単で、今年は自社のTech Blogにも実例をいくつか書いていました。
update_atのようなカラムが存在する場合SELECT MAX(update_at) FROM...のようなSQLを書けばいいわけですが、このやり方は以下のように案外できない場合があります。
- 一週間に一回程度の頻度でしかレコードが更新されない
update_atのようなカラムがそもそも存在しないテーブルである
こういったケースにも対応することを考えると、update_atのような具体的なカラムではなくメタデータから最終更新日を知りたくなります。
古の方法: __TABLES__を使う
テーブルの最終更新日を知るために、昔は__TABLES__をよく使っていました。データセット内のテーブルやビューの一覧を出しつつ、最終更新日や行数、物理サイズなどが分かります。
SELECT * FROM `my-project`.source__db.__TABLES__
__TABLE__の例
一見便利に見える__TABLES__ですが、ドキュメントがなかったり、パフォーマンスに問題がある、ということで積極的に使っていくのは今後は避けたほうがよさそうです。
INFORMATIONは便利だけど、テーブルサイズなどのメタデータは入ってない。そういうときは__TABLES__ テーブルが便利だが、こいつは意外とGCPのドキュメントにない...!サポートケースでissue trackerに起票してもらったので、ドキュメント欲しい方はissueにstar付けてくれ!!
— Yasuhisa Yoshida (@syou6162) 2021年9月28日
- https://t.co/AhonkJAT6e
INFORMATION_SCHEMA.PARTITIONSを使おう
では、どうするのがいいか?メタデータと言えばINFORMATION_SCHEMAですが、ぱっと見ドキュメントを見るだけだと__TABLES__に相当するものがなさそうに見えます。が、実はINFORMATION_SCHEMA.PARTITIONSに存在していました...!教えてくれた
id:nii_yanさん、ありがとうございました。
INFORMATION_SCHEMA.PARTITIONSのtotal_billable_bytesではだめですか？名前で騙されますが、パーティション切られてないテーブルのデータも取れます！！
— yu yamada (@nii_yan) 2021年9月28日
あと、プロジェクト単位とかorg単位でデータ量取りたいなってなりますね・・・
おお、これは知らなかったです。というかこの名前だとなかなか気付けないw
— Yasuhisa Yoshida (@syou6162) 2021年9月28日
__TABLES__にしかないと思っていたTOTAL_ROWSやLAST_MODIFIED_TIMEも入っていて、用途的にはINFORMATION_SCHEMAで代替できそうな感じがしますね。ありがとうございます!
ちなみに __TABLES__ はパフォーマンス問題があるから使わせないために今後もドキュメントに書かないというのが公式見解という認識ですね。 INFORMATION_SCHEMA.PARTITIONS の方はそれが一定クリアされているから Private Preview としてリリースされているはずhttps://t.co/fDqp3jvtLi
— _ (@apstndb) 2021年9月28日
実際の出力としては以下のような形になります。ストレージ料金にも関わってくるstorage_tierなどの情報も追加されていて、(名前が分かりにくい以外は)INFORMATION_SCHEMA.PARTITIONSを今後使っていくとよさそうですね。
SELECT * FROM `my-project.source__db.INFORMATION_SCHEMA.PARTITIONS`
来年もメタデータでhappyなデータ基盤生活をしていきたいですね。
