Help us understand the problem. What is going on with this article?

BigQueryデータ基盤のテーブル依存関係を管理する

背景

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

munaita_
フリーランスのデータエンジニアです。 データ基盤構築や、ビッグデータを扱うシステム構築が得意です。 新卒サイバーエージェントからフリーランス。 広告/小売/教育など幅広いドメインのデータビジネスを経験しています。
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした