背景

データマート（途中の中間テーブルも含む。以後両方合わせてデータマート)の生成を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から依存関係を可視化する方法が紹介されている事例