Google BigQuery_データセット内のテーブル一覧取得

背景

業務でBigQueryを使うことが多いのだが、その中で「テーブル名の一覧」と「最終更新日時」を知りたいことが多々ある。

テーブル名一覧の取得は言わずもがな頻発する。
最終更新日が気になるケースとしては、具体的にはTableau Cloudにパブリッシュデータソースを作り、BQ内のviewと接続する。Tableau Cloudでデータソースの自動更新をバッチ処理設定しておくケースが該当する。

稀にデータ更新は成功したのに中身のデータが最新の状態になっていないことがあり、BQテーブルの更新タイミングとデータソースの更新タイミングが合致していないことが疑われる。テーブルの更新タイミングを一つずつBQコンソールから確認するのは手間なので、一括で取得したくなるわけだ。

クエリで取得してみる

具体的には以下のクエリを使う。ポイントはアンダーバーが前後に2つずつあること、そしてTABLESは全て大文字なことである。

SELECT *
FROM project.dataset.__TABLES__
ORDER BY table_id

結果は以下のようになる。実際には縦にずらっと並んで出てくる。

出力結果イメージ

これだけでも十分便利なのだが、日時が日付形式で出てこないのでちょっと不満。

日時に変換

テーブル作成日時と最終更新日時の項目は、整数かつ桁数が多い。これはUNIX TIMEと考えるのが妥当だろう。
そしてUNIX TIMEには秒単位とミリ秒単位がある。数えてみると13桁。うん、ミリ秒だ。

てなわけで軽くググって日付変換のCAST文を発見。適用してみる。

SELECT
table_id
,CAST(FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', TIMESTAMP(creation_time), 'Asia/Tokyo') as DATETIME) AS created_at
,CAST(FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', TIMESTAMP(last_modified_time), 'Asia/Tokyo') as DATETIME) AS last_modified_at
,row_count
FROM project.dataset.__TABLES__
ORDER BY table_id

少々長いが、要はタイムスタンプを頑張ってJSTで日時に変換しているだけである。結果は以下のようになる。
これで大分見やすくなった。

日付変換後の出力イメージ

(おわり)