BigQuery の WITH RECURSIVE を使って INFORMATION_SCHEMA から関連テーブル抽出する

Published in

DeNAデータ分析ブログ

7 min readFeb 16, 2022

こんにちは。 DeNA データエンジニアの野本です。

この記事では、先日(2022-02-02)にプレビューリリースされました WITH RECURSIVEを使って JOB 履歴の INFORMATION_SCHEMA.JOBS_BY_PROJECT から関連するテーブルを抽出する方法を記載します。

この記事を3行で言うと

WITH RECURSIVE の機能を使って INFORMATION_SCHEMA.JOBS_BY_PROJECT を分析してみました。

2. 1. の結果、参照しているなど関連あるテーブルのみを洗い出せました。

3. 2. の洗い出したテーブル情報を元に依存関係のあるテーブルを可視化もできます。

用語の説明

聞き馴染みのない方もいると思いますので、簡単に用語(BigQuery の機能)について簡単に説明します。最新情報や詳細は GCP の公式ドキュメントをご確認ください。

WITH RECURSIVE とは

公式のドキュメントにあるように再帰的な SQL を発行するための機能です。(公式ドキュメントを参照の際は、まだ日本語ドキュメントはないので、言語を英語でご確認ください。)

A recursive common table expression (CTE) contains a recursive subquery and a name associated with the CTE.

INFORMATION_SCHEMA.JOBS_BY_PROJECT とは

BigQuery ジョブに関するメタデータです。これを分析することで BigQuery の使われ方を把握することができます。

WITH RECURSIVE を触ってみた

実現したいこと

テーブル間の繋がりを把握したい。

皆さんは、とあるテーブルがどういったテーブルから集計されているか、または、とあるテーブルがどう使われているかを把握したいケースなどありませんか？私は日々あります！例えば、業務で使用しているレポートの値がおかしく、参照されているテーブルを調査したりですとか、元ログの仕様が変わるので使用しているテーブルやレポートへの影響調査などです。

検証のゴール

検証のために、以下の様なシンプルなデータパイプラインを準備し、これに関連する BigQuery のジョブだけを抽出することを目指しました。

Table3 は Table1と2を参照し、Table5は Table3 と 4を参照しているデータパイプラインです。

検証1

まずは、シンプルな疑似テーブルを準備して検証を始めました。

準備したテーブル: `test_lineage.sample`

+--------+--------+
| Input  | Output |
+--------+--------+
| Table1 | Table３ |
| Table2 | Table３ |
| Table３ | Table5 |
| Table４ | Table5 |
+--------+--------+

Table1 を源流に持つテーブルを抽出するために、以下のような SQL を作成、実行しました。 WITH RECURSIVE の中で、自身 T1 を呼び出す事ができるため、再帰的なクエリを発行でき、参照の依存関係がある行だけを抽出できます。

作成した SQLの例

SQL の結果

+--------+--------+
| Input  | Output |
+--------+--------+
| Table1 | Table３ |
| Table３ | Table5 |
+--------+--------+

期待通り、 Table1 -> Table3 と Table3->Table5 の行のみを抽出することができました。

検証2

検証1と同じ要領で実際の INFORMATION_SCHEMA.JOBS_BY_PROJECT から関連するテーブルのジョブ履歴のみを抽出することを目指します。

INFORMATION_SCHEMA.JOBS_BY_PROJECT のスキーマの詳細などは更新ドキュメントを参考ください。今回は以下のカラムを使って抽出を行っていきます。

destination_table: Destination table foｔr results (RECORD 型)
referenced_tables: Array of tables referenced by the job (RECORD 型)

検証１の図では、 dataset を省略していましたが、実際には dataset_id.table_id で検索する必要があります。今回検証のために dataset: test_lineage を準備し、以下の様なパイプラインを実行しました。

いざ、実際にFROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT に対して WITH RECURSIVE の SQL を発行しようとすると以下の2つの制約により実行できませんでした。

Struct type is not supported in the WITH RECURSIVE output.
WITH is not allowed inside WITH RECURSIVE.

これを回避するために、一旦実テーブル化し、 destination_table と referenced_table をフラット化したものを準備することにしました。DeNA では日次で実テーブル化していたので、そこからフラット化するビューを準備することにしました。

フラット化するビュー

この INFORMATION_SCHEMA.JOBS_BY_PROJECT をフラット化したビューには、プロジェクト内で実行されたジョブ履歴が含まれています。今回はtest_lineage.Table5 が上流のテーブルを抽出するための、以下のような SQL を作成、実行しました。

作成した SQL

はい。期待通りに関連するテーブルの JOB 情報のみを抽出することができました。

また、用途に応じて、上記の SQL に query や user_email といったカラムを SELECT に追加すればどういった SQL を誰が実行しているかも把握することが出来ます。

可視化

抽出したリストを元にお好みの方法にてテーブルの依存関係の図を生成するれば OK です。

上記の可視化の例は、 Streamlit でサクッと作ったモックですが、弊社ではこの様な可視化ツールを作成し、普段のデータパイプラインの開発・保守業務で活用することを検討しています。

まとめ

WITH RECURSIVE の機能を使って INFORMATION_SCHEMA.JOBS_BY_PROJECT を分析することで簡易的にテーブルの依存関係を把握することができます。