背景
データマート（途中の中間テーブルも含む。以後両方合わせてデータマート)の生成をBigQueryJobで行なっているようなBigQueryのデータ基盤を想定。
業務アプリケーションの改修で、あるテーブルのカラムが削除/編集されたり、テーブル自体が削除されたりする場合、データ基盤管理者としてはその改修がどのデータマートに影響があるのかを判断する必要がある。このデータマートとテーブルの依存関係をドキュメントとして残しておいてもいいが、ドキュメントの場合はメンテナンスが大変。
今回は、BigQueryの INFORMATION_SCHEMA.JOBS_BY_PROJECTから、データマートと依存テーブルのマッピングを生成する。これによって、過去180日に実際に生成されたデータマートが依存しているテーブルを明らかにすることができる。
INFORMATION_SCHEMA を使用したジョブ メタデータの取得
やること
以下のSQLの実行結果でテーブル作成するだけ。過去180日に実行されたBigQueryJobのdestination_tableのあるものから依存テーブルのマッピングテーブルを生成する。
WITH table_with_repeat as (
SELECT
destination_table.table_id as destination_table,
REGEXP_EXTRACT_ALL(query, "(?:FROM|JOIN)[\\s \\n]+`(.+?)`") as tables,
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE job_type = "QUERY"
AND state = "DONE"
AND destination_table.table_id not like "anon%"
)
SELECT
twr.destination_table,
t as table_name
FROM table_with_repeat as twr, UNNEST(tables) as t
これで嬉しいこと
どのデータマートがどのテーブルに依存しているが簡易的なSQLですぐにわかること。
例えば、業務アプリケーションのテーブル users が削除された場合を想定する。その場合、以下のようなSQLで、users のテーブルに依存しているデータマートがわかる。
SELECT destination_table FROM mapping_table WHERE table_name LIKE "%users%"
さらに、githubのPRをデイリーで自動取得しスキーマ変更の有無をチェックできる( pythonでgithub apiを呼び出してPull Request 情報を取得する )。
これを組み合わせると、
- 業務アプリのPRにスキーマ変更がある場合はSlackに通知
- 変更対象テーブルがデータマートに影響が無いかを依存マッピングテーブルで調査
というフローが可能になる。
参考
GitHubで管理されたデータマート構築基盤の紹介
データマート生成用のSQLから依存関係を可視化する方法が紹介されている事例