BigQueryで全テーブルのメタ情報を一括で取得する方法

投稿者: | 2019年12月25日

この記事は GMOアドマーケティング Advent Calendar 2019 25日目の記事です。

こんにちは、GMOアドマーケティングのmizkichです。
アドテクなマイクロサービスをGCPのKubernetesで運用しています。

最近、私が扱っているBigQueryのテーブル数が1万を超えてしまいました。
これらのテーブルから、レコード変更があったテーブルだけを抽出する業務があります。

公式に書かれたテーブル最終更新日の取得方法だと、1テーブルあたり2秒ほど掛かります。1万テーブルだと約6時間です。
この取得方法を変更することで、全テーブルの最終更新日を1秒未満で完了できるようになりました。

本記事では、テーブルのメタ情報を取得するための、三つの方法を紹介させて頂きます。


1. bqコマンドでの取得

bqコマンドにはテーブル情報を知る方法が二つあります。

bq lsでは、全テーブルの概要情報を取得することが出来ます。
取れる情報は、「テーブル作成日」「テーブル失効日」だけで、使い道があまりないです。

 

bq showでは、各テーブル個別に詳細情報を取得することが出来ます。
取れる情報は、「テーブル作成日」「テーブル最終更新日」「テーブルバイト数」「90日経過バイト数」「テーブル行数」「スキーマ定義」などです。
目的の「テーブル最終更新日」を取得することが出来ますが、このコマンドの実行にはテーブル名の指定が必須です。

shellスクリプトで一発で取得する方法も試みましたが、bq show一回あたり2秒ほどかかります。
1万テーブルに対するテーブル更新日の取得は、約6時間ほど掛かってしまっていました。


2. INFORMATION_SCHEMAからの取得

特殊なテーブル、INFORMATION_SCHEMAテーブルを使ってもメタ情報を取得することが出来ます。

INFORMATION_SCHEMAには5つのテーブルが有りますが、どのテーブルにもテーブル最終更新日は含まれていません。
テーブル情報に関して取れるのはテーブル作成日のみでした。

 


3. メタテーブルからの取得

__TABLES__を利用すると、bq showにも匹敵する情報を全テーブル一括で取得することが出来ます。
取れる情報は「テーブル作成日」「テーブル最終更新日」「テーブル行数」「テーブルバイト数」です。

 

このSQLを使えば、ミリ秒でテーブル最終更新日の一覧が取得できます。
SELECT * FROM `dataset_name.__TABLES__`;

 

最終更新日はミリ秒で格納されていますので、以下のSQLで年月日に変換できます。

 

過去1週間に変更のあったテーブルだけを抜粋したい場合は、以下のSQLで抽出できます。

 

テーブル名だけをファイル化したい場合などは、以下のbqコマンドと組み合わせると楽にテーブル名だけ抽出できます。

 

ファイルに抽出したテーブルだけをコピーするには、以下のシェルを使いました。

 

複製したテーブルの接尾句(YYYYMMDD)から94日経過でテーブルを削除するには、以下のシェルを使いました。

 


まとめ

__TABLES__の存在を知るまで、BigQueryのテーブル更新日は、テーブル名の接尾句から判断するか、bq showで一つずつ調べるしかありませんでした。

__TABLES__があれば、実際に更新があったかどうかを一瞬で判断することができます。
__TABLES__を使うようになってから、レプリケーション元への更新情報を、漏らさずレプリケーション先へ複製することができるようになりました。

この__TABLES__BigQueryのドキュメントにはどこにも記載が見当たらず、大はまりしました。
他にも隠れコマンドがあるかも知れず、見つかったらまた共有させて頂きます。

 


本日でAdvent Calendar 2019も最終日ですが、ぜひ今後も投稿をウォッチしてください!
今年も一年お疲れ様でした!

 

■エンジニア採用ページ ~福利厚生や各種制度のご案内はこちら~
https://www.gmo-ap.jp/engineer/
■Wantedlyページ ~ブログや求人を公開中!~
https://www.wantedly.com/projects/199431
■エンジニア学生インターン募集中! ~就業型インターンでアドテクの先端技術を体験しよう~
https://hrmos.co/pages/gmo-ap/jobs/0000027