この記事は GMOアドマーケティング Advent Calendar 2019 25日目の記事です。
こんにちは、GMOアドマーケティングのmizkichです。
アドテクなマイクロサービスをGCPのKubernetesで運用しています。
最近、私が扱っているBigQueryのテーブル数が1万を超えてしまいました。
これらのテーブルから、レコード変更があったテーブルだけを抽出する業務があります。
公式に書かれたテーブル最終更新日の取得方法だと、1テーブルあたり2秒ほど掛かります。1万テーブルだと約6時間です。
この取得方法を変更することで、全テーブルの最終更新日を1秒未満で完了できるようになりました。
本記事では、テーブルのメタ情報を取得するための、三つの方法を紹介させて頂きます。
1. bqコマンドでの取得
bqコマンドにはテーブル情報を知る方法が二つあります。
bq lsでは、全テーブルの概要情報を取得することが出来ます。
取れる情報は、「テーブル作成日」「テーブル失効日」だけで、使い道があまりないです。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
{ "creationTime": "1566967981721", "expirationTime": "1574730400000", "id": "project_name:dataset_name.test_table_20190824", "kind": "bigquery#table", "tableReference": { "datasetId": "dataset_name", "projectId": "project_name", "tableId": "test_table_20190824" }, "type": "TABLE" } |
bq showでは、各テーブル個別に詳細情報を取得することが出来ます。
取れる情報は、「テーブル作成日」「テーブル最終更新日」「テーブルバイト数」「90日経過バイト数」「テーブル行数」「スキーマ定義」などです。
目的の「テーブル最終更新日」を取得することが出来ますが、このコマンドの実行にはテーブル名の指定が必須です。
shellスクリプトで一発で取得する方法も試みましたが、bq show一回あたり2秒ほどかかります。
1万テーブルに対するテーブル更新日の取得は、約6時間ほど掛かってしまっていました。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
{ "creationTime": "1488521917130", "etag": "iWAIAQcvcJRL4J+XsqnkjQ==", "id": "project_name:dataset_name.test_table_20170222", "kind": "bigquery#table", "lastModifiedTime": "1488521917130", "location": "US", "numBytes": "365275773", "numLongTermBytes": "365275773", "numRows": "3108253", "schema": { "fields": [ { "mode": "REQUIRED", "name": "id", "type": "INTEGER" }, //中略 ] }, "selfLink": "https://bigquery.googleapis.com/bigquery/v2/projects/project_name/datasets/dataset_name/tables/table_20170222", "tableReference": { "datasetId": "dataset_name", "projectId": "project_name", "tableId": "table_20170222" }, "type": "TABLE" } |
2. INFORMATION_SCHEMAからの取得
特殊なテーブル、INFORMATION_SCHEMAテーブルを使ってもメタ情報を取得することが出来ます。
INFORMATION_SCHEMAには5つのテーブルが有りますが、どのテーブルにもテーブル最終更新日は含まれていません。
テーブル情報に関して取れるのはテーブル作成日のみでした。
|
1 2 |
SELECT * FROM `INFORMATION_SCHEMA.SCHEMATA`; -- データセットの名称、作成日、最終更新日 |
|
1 2 |
SELECT * FROM `INFORMATION_SCHEMA.SCHEMATA_OPTIONS`; -- データセットの名称、オプション設定(失効日時設定など)。 |
|
1 2 |
SELECT * FROM `dataset_name.INFORMATION_SCHEMA.TABLES`; -- テーブルの名称、作成日 |
|
1 2 |
SELECT * FROM `dataset_name.INFORMATION_SCHEMA.TABLE_OPTIONS`; -- テーブルの名称、オプション設定(失効日時設定など)。 |
|
1 2 |
SELECT * FROM `dataset_name.INFORMATION_SCHEMA.VIEWS`; -- ビューの名称、作成日? |
3. メタテーブルからの取得
__TABLES__を利用すると、bq showにも匹敵する情報を全テーブル一括で取得することが出来ます。
取れる情報は「テーブル作成日」「テーブル最終更新日」「テーブル行数」「テーブルバイト数」です。
このSQLを使えば、ミリ秒でテーブル最終更新日の一覧が取得できます。
SELECT * FROM `dataset_name.__TABLES__`;
最終更新日はミリ秒で格納されていますので、以下のSQLで年月日に変換できます。
|
1 2 3 |
SELECT table_id , TIMESTAMP_MILLIS(last_modified_time) AS last_modified FROM `dataset_name.__TABLES__`; |
過去1週間に変更のあったテーブルだけを抜粋したい場合は、以下のSQLで抽出できます。
|
1 2 3 4 |
SELECT table_id , TIMESTAMP_MILLIS(last_modified_time) AS last_modified FROM `dataset_name.__TABLES__` WHERE last_modified_time >= UNIX_MILLIS(TIMESTAMP_SUB(CURRENT_TIMESTAMP, INTERVAL 7 DAY)); |
テーブル名だけをファイル化したい場合などは、以下のbqコマンドと組み合わせると楽にテーブル名だけ抽出できます。
|
1 2 3 4 5 6 7 |
bq query --format=prettyjson --nouse_legacy_sql \ "SELECT table_id \ , TIMESTAMP_MILLIS(last_modified_time) AS last_modified \ FROM `old_project_id:old_dataset_name.__TABLES__` \ WHERE last_modified_time >= UNIX_MILLIS(TIMESTAMP_SUB(CURRENT_TIMESTAMP, INTERVAL 7 DAY)) \ ORDER BY table_id" \ | jq -r '.[].table_id' > ./table_list.txt |
ファイルに抽出したテーブルだけをコピーするには、以下のシェルを使いました。
|
1 2 3 |
cat ./table_list.txt| sed -e 's/^.*$/bq cp -f --project_id new_project_id old_project_id:old_dataset_name\.& new_dataset_name\.&/g' > ./copy_command_list.sh chmod 750 ./copy_command_list.sh ./copy_command_list.sh |
複製したテーブルの接尾句(YYYYMMDD)から94日経過でテーブルを削除するには、以下のシェルを使いました。
|
1 2 3 |
cat ./table_list.txt | sed -ne 's/^.*_\([0-9]\{8\}\)$/bq --location=US update --expiration $((8121600 - (`date +%s` - `date -j -f ''%Y%m%d'' \1 +%s`))) --project_id new_project_id new_dataset_name\.&/p' > ./set_expiration.sh chmod 750 ./set_expiration.sh ./set_expiration.sh |
まとめ
__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
Full Stack Engineer @ GMO AD Marketing, Inc.
DMP(Data Management Platform)の開発運用担当してます。
(Java x Ruby x Kubernetes x BigQuery x Hazelcast x etc)
マイブームは犬と車と登山とスノボ。
