BigQuery未使用テーブルをData Lineage APIで検出する監視システム
CyberAgent の ABEMA チームが、Data Lineage API と audit log を組み合わせることで、大規模 BigQuery 環境における未使用テーブルを安全に特定・廃止する運用プロセスを確立した事例である。
キーポイント
課題背景：10 年運用によるデータ蓄積とコスト増
ABEMA のデータ基盤は約 1 万テーブル規模で、新規機能開発に伴うログ増加に対し、使用済みデータの棚卸しが進まず維持コストが膨らんでいた。
解決策：リーフノードと監査ログの連携検出
Data Lineage API で下流依存関係のない「リーフノード」を特定し、audit log のクエリ実績（0 回）と照合することで、未使用テーブルを高精度に抽出する。
運用プロセス：Airflow と Slack を活用した段階的廃止
月 1 回のバッチ実行で候補テーブルを特定し、Slack で通知して関係者の判断を得た上で、枝葉から順次削除する安全なフローを構築した。
Data Lineage API を用いたリーフテーブルの特定
生ログデータのルートテーブルを起点に下流グラフを辿り、実テーブルを持たない末端（リーフノード）のテーブルを検出する。
BI ツールを含む全利用履歴の把握
BigQuery の JOBS ビューでは取得できない Tableau などの SaaS から実行された API 履歴も、audit log を参照することで網羅的に把握可能である。
廃止判断のための安全な期間設定
利用回数の集計期間は1ヶ月に設定されており、長く取ることでテーブル廃止時の安全性を高める設計となっている。
BFSによる下流探索とリーフ判定
LineageClient.search_links を用いて幅優先探索（BFS）を行い、下流リンクを持たないノードを未使用テーブル（リーフ）として特定します。
影響分析・編集コメントを表示
影響分析
この事例は、データウェアハウスが巨大化・複雑化する現代において、手動での棚卸しが不可能な状況下でも、API とログ分析を駆使してコスト削減とガバナンス強化を実現できる具体的な手法を示しています。特に「依存関係の逆算」というアプローチは、大規模組織におけるデータライフサイクル管理（DLM）の標準的なプラクティスとして広く応用可能です。
編集コメント
データ基盤の肥大化は多くの企業が直面する課題ですが、依存関係（リネージ）を可視化する API と監査ログを組み合わせる発想は非常に実用的で参考になります。特に「安全に削除」を最優先した段階的なアプローチは、大規模環境におけるリスク管理の教科書と言えます。
はじめに
ABEMA の Data Platform チームに所属している河野です。
Data Platform では ABEMA の行動ログやマスターデータなど多岐にわたるデータを管理しています。
ABEMA は今年で 10 周年を迎え、日々多くのユーザー様にご利用いただいています。
そのおかげもあり、ログの送信量は 1 日平均 15 億レコードに及びます。
長きにわたり愛用されている ABEMA ですが、それと同様にデータ基盤にも同じ長さの歴史があります。
これまで多くのプロジェクトが試行され、そのたびに新しいデータが作成されてきましたが、それらのデータが今もテーブルとして残り続けています。
そこで今回は、Data Lineage API と audit log を利用し、ABEMA の大規模データ基盤におけるテーブル廃止プロセスを運用する方法を紹介します。
TL;DR
- 課題：10 年運用の約 1 万テーブル規模の BigQuery で、使われていないテーブルが特定できず棚卸しが進まなかった
- 打ち手：Data Lineage API で「リーフノード（下流のないテーブル）」を抽出し、audit log のクエリ実績と突き合わせて未使用テーブルを検出
- 仕組み：Airflow で月 1 回バッチ実行し、候補を Slack に通知して廃止判断につなげる
対象読者
- BigQuery を利用している人
- 分析基盤の大量のテーブルを掃除したい人
- データ基盤管理者
なぜ未使用テーブル監視が必要だったのか
行動ログの種類が増え、維持コストが膨らむ
ABEMA では行動ログの種類が約 50 種類ほどあります。
行動ログの種類は多ければ多いほど分析の幅が広がりますが、その分クライアントエンジニアや QA エンジニアの実装、テストのコストが高まるという問題がありました。
新しい機能を開発するたびに、全てのログが正しく送信されているか確かめる必要があり、そのために労力をかけています。
しかし、その大半は使われていない
ここで問題なのが、行動ログの大半はもう既に使用されていないということです。
新規機能の開発や、新しい分析、検証のためにログが作成されますが、それらのプロジェクトが終わった後に継続的に分析され続けているログはほとんどありません。
そのため、クライアントチームが送ってくれているログが、本当は分析に利用されていないから送らなくても問題にならない、という問題を抱えています。
かといって「利用していない」と言い切れる人はいない
それなら、利用していない行動ログを削除すればいいと思ってしまいますが、ABEMA には約 600 名の社員が在籍しており、もう利用していないと思われるデータを、実は誰かがまだ利用しているということがあります。
社員全員にヒアリングすることは可能ですが、誤って廃止したときにどのようなビジネス影響があるか不明なため、リスクを考慮し確実に廃止できる方法を選択することにしました。
そこで、使っていないテーブルから逆算する
ABEMA のデータウェアハウスでは、行動ログデータは 1 つのデータソースとなるテーブルに集約されています。
そのテーブルから行動ログ別のテーブルに分解し、それぞれのテーブルを加工して分析用のテーブルを作成しています。
分析用のテーブルを廃止するための確実な方法を考えたとき、BigQuery の利用していないテーブル（以下、未利用テーブル）を洗い出していくことで、最終的に行動ログ別のテーブルまで削除でき、最後のこのテーブルの利用者も 0 だったときに行動ログ自体を廃止できると考えました。
リーフノードから刈る
より安全に未利用テーブルを廃止するために、未利用テーブルの定義を、「audit log 上での利用回数が 0」「リネージの最も端のリーフノードである」としました。
リーフノードのテーブルと定義した理由は、下流の依存関係がなくコンソール、もしくは BI ツールによる getData API での取得の 2 種類でしかデータ利用履歴が残らないためです。これらの取得方法はすべて audit log 上で取得可能となっています。
未利用テーブルの廃止において最も重要なのは、安全にテーブルを参照できなくさせることです。
全てのテーブルに対して一律に使用状況を確認し、使用回数が 0 のテーブルでも、その先のテーブルで利用されている場合があるため一概に削除できるとは言えないと考えました。
そこで、枝葉から刈っていくイメージで徐々にテーブルを廃止していき、長いスパンで見て未利用テーブルがなくなっていくように設計しました。
Data Lineage API は、テーブルのリネージ関係を探索し、リーフノードにおけるテーブルを発見するのに使用しています。
リネージ関係を取得するときのルートのテーブルには、生のログデータを保存しているテーブルを指定しています。
幸い、ABEMA の行動ログのデータは最初の 1 つのテーブルから全てスタートしていたため、ここの選定が楽に済みました。
audit log の役割
audit log はクエリの利用回数を調べるのに利用しています。
audit log にした理由は BI ツールからの参照まで知ることができるためです。
BigQuery には JOBS ビュービューを利用して実行されたクエリを分析することができますが、こちらの場合は Tableau などの SaaS 製品が実行した API の履歴までは取ることができませんでした。
このときの、利用回数を調べる遡及期間は 1 か月に設定しています。
この 1 か月という数字に大きな意味はありませんが、なるべく長く取ることで廃止するときの安全性が高くなります。
システム構成
本システムは Managed Service for Apache Airflow（旧 Cloud Composer）上の月次バッチとして動作します。
行動ログのルートテーブルを起点に Data Lineage API で下流グラフを辿ってリーフテーブルを特定し、audit log から集計した直近 1 ヶ月の利用履歴と BigQuery 上で突き合わせ、利用回数が 0 のテーブル（未利用テーブル）を Slack に通知します。
アーキテクチャ全体図
全体は「リーフ検出 → 利用回数の集計 → Slack 通知」という月次のパイプラインですが、本記事では技術的な肝である次の 2 点に絞って解説します。
- リーフテーブルの検出ロジック ── Data Lineage API でどう「末端のテーブル」を見つけるか
- audit log のクエリ方法 ── 「実際に使われたか」をどう測るか
リーフテーブルの検出ロジック
探索の起点は行動ログの生テーブル root_table です。
このテーブルから派生する系譜だけを対象に、下流に実テーブルを持たないノード＝リーフテーブルを特定します。
Data Lineage API はテーブルを bigquery:project.dataset.table という FQN（完全修飾名）で識別します。まずは FQN の組み立てとパースを行うヘルパーです。
_BQ_FQN_PREFIX = "bigquery:"
@dataclass
class TableRef:
"""BigQuery テーブルの識別子。"""
project: str
dataset: str
table: str
def _build_root_fqn(env: str) -> str:
"""環境に応じたルートテーブルの Data Lineage API 用 FQN を構築する。"""
prefix = env_prefix(env, "_")
return f"{_BQ_FQN_PREFIX}abema.abema_sample.root_table"
def _parse_fqn(fqn: str) -> TableRef | None:
"""FQN 文字列から project/dataset/table を抽出する。非 BigQuery アセットは None。"""
if not fqn.startswith(_BQ_FQN_PREFIX):
return None
parts = fqn[len(_BQ_FQN_PREFIX) :].split(".")
if len(parts) != 3:
return None
return TableRef(project=parts[0], dataset=parts[1], table=parts[2])
以下は探索の本体です。
LineageClient.search_links に source（入力側）としてテーブルを渡すと、そのテーブルを起点とする下流リンクが返ります。
これを幅優先探索（BFS）で辿り、テーブルの下流を 1 つも持たないノードをリーフと判定します。
判定時に下流から除外するもの（＝そのノードを「中間ノード」に格上げしない対象）は次のとおりです。
- ビュー（VIEW）：ビューしか下流に持たないテーブルはリーフ扱いにしており、これは audit log 上では VIEW ではなく参照元のテーブルが履歴として残るためです
- 一時データセット（_ 始まり）：クエリ実行時の一時的な中間生成物
- 削除済み（NotFound）／アクセス拒否（Forbidden）：実体を確認できないテーブル
def _traverse_downstream(
lineage_client: LineageClient,
bq_client: bigquery.Client,
root_fqn: str,
) -> list[TableRef]:
"""BFS（幅優先探索）でルートテーブルから下流グラフを探索し、リーフテーブル情報を返す。"""
visited: set[str] = set()
queue: deque[str] = deque([root_fqn])
visited.add(root_fqn)
leaves: list[TableRef] = []
while queue:
current_fqn = queue.popleft()
current_ref = _parse_fqn(current_fqn)
if current_ref is None:
continue
# current_fqn を入力（source）とする下流リンクを検索
request = SearchLinksRequest(
parent=f"projects/{current_ref.project}/locations/us",
source=EntityReference(fully_qualified_name=current_fqn),
)
has_bq_downstream = False
for link in lineage_client.search_links(request=request):
target_fqn = link.target.fully_qualified_name
target_ref = _parse_fqn(target_fqn)
if target_ref is None:
continue
if target_fqn in visited:
has_bq_downstream = True
continue
table_fqn = f"{target_ref.project}.{target_ref.dataset}.{target_ref.table}"
if target_ref.dataset.startswith("_"):
continue # 一時データセット
try:
tbl = bq_client.get_table(table_fqn)
except NotFound:
continue # 削除済み
except Forbidden:
continue # アクセス拒否
if tbl.table_type == "VIEW":
continue # ビュー
has_bq_downstream = True
visited.add(target_fqn)
queue.append(target_fqn)
下流に実テーブルが存在しない場合、リーフノード（ルート自身は除く）
if not has_bq_downstream and current_fqn != root_fqn:
leaves.append(
TableRef(current_ref.project, current_ref.dataset, current_ref.table)
)
return leaves
監査ログのクエリ方法
必ず JSON 形式で返してください。translation フィールドのみ。他のフィールド (technical_terms 等) は一切追加しないこと — 余計なフィールドを書こうとして本文翻訳がトークン上限で打ち切られる事故を防ぐため:
{"translation": "翻訳全文"}
ここでは BigQuery にエクスポート済みの audit log テーブル cloudaudit_googleapis_com_data_access を参照します。
このテーブルは事前に作成しているもので、 https://docs.cloud.google.com/architecture/security-log-analytics?hl=ja を参考に作成することができます。
クエリのポイントは次のとおりです。
- 「利用」の定義：bigquery.tables.getData 権限を伴い、かつ Query / InsertJob のジョブだけを利用とみなす（メタデータ参照などは除外）
- ノイズ除外：セキュリティスキャナ等の自動アクセスは、サービスアカウントで除外しないと「利用あり」と誤判定されてしまう
-- リーフテーブルの利用統計を集計
-- monitoring.leaf_tables と audit log を結合し、各リーフテーブルのクエリ利用回数を取得
SELECT
REGEXP_EXTRACT(protopayload_auditlog.resourcename, r'projects/([^/]+)') AS project,
REGEXP_EXTRACT(protopayload_auditlog.resourcename, r'datasets/([^/]+)') AS dataset,
REGEXP_EXTRACT(protopayload_auditlog.resourcename, r'tables/([^/]+)') AS table_name,
COUNT(*) AS query_count
FROM cloudaudit_googleapis_com_data_access
, UNNEST(protopayload_auditlog.authorizationinfo) AS authorizationinfo
WHERE
timestamp >= TIMESTAMP(DATE '${dt}')
AND timestamp < TIMESTAMP(DATE_ADD(DATE '${dt}', INTERVAL 1 MONTH))
AND protopayload_auditlog.resourcename LIKE 'projects/%/datasets/%/tables/%'
AND authorizationinfo.permission = "bigquery.tables.getData"
AND protopayload_auditlog.methodname IN (
"google.cloud.bigquery.v2.JobService.InsertJob",
"google.cloud.bigquery.v2.JobService.Query"
)
AND protopayload_auditlog.authenticationinfo.principalemail NOT IN (
"（スキャナ用サービスアカウントのメールアドレス）" -- 自動スキャンを除外
)
GROUP BY
1, 2, 3
運用してみた結果
通知が来たタイミングでミーティングを行い、廃止できるかできないか、廃止する場合はどのようなコミュニケーション設計が必要かなどを話し合ったうえで、未利用テーブルへの対応を手動で行っています。
この監視システムにより数十個の未利用テーブルが検知され、廃止可能なテーブルもいくつか検出することができました。
この 10 年間で手を付けていなかったテーブルの廃止プロセスの一歩を踏み出したことで、複雑化したデータ基盤をシンプルにできるという手触りの感覚が得られました。
また、通知された未利用テーブルもメンバーの肌感覚と合致しており、リーフノードのテーブルから廃止していくというプロセス自体は間違っていないと感じています。
今後の展望
今後はこのシステムを利用しながら徐々にテーブル数を減らし、分析に本当に必要なテーブルだけが残った状態を作りたいと思っています。
新しく入ってきた分析者がジョインした初日にすぐに仕事に取りかかれる、シンプルでわかりやすいデータ基盤を提供するために、引き続き廃止運動は続けていこうと思います。
原文を表示
はじめに
ABEMA の Data Platform チームに所属している河野です。
Data Platform ではABEMAの行動ログやマスターデータなど多岐にわたるデータを管理しています。
ABEMAは今年で10周年を迎え、日々多くのユーザー様にご利用いただいています。
そのおかげもあり、ログの送信量は1日平均15億レコードに及びます。
長きにわたり愛用されているABEMAですが、それと同様にデータ基盤にも同じ長さの歴史があります。
これまで多くのプロジェクトが試行され、そのたびに新しいデータが作成されてきましたが、それらのデータが今もテーブルとして残り続けています。
そこで今回は、Data Lineage APIとaudit logを利用し、ABEMAの大規模データ基盤におけるテーブル廃止プロセスを運用する方法を紹介します。
TL;DR
- 課題：10年運用の約1万テーブル規模の BigQuery で、使われていないテーブルが特定できず棚卸しが進まなかった
- 打ち手：Data Lineage API で「リーフノード（下流のないテーブル）」を抽出し、audit log のクエリ実績と突き合わせて未使用テーブルを検出
- 仕組み：Airflow で月1回バッチ実行し、候補を Slack に通知して廃止判断につなげる
対象読者
- BigQueryを利用している人
- 分析基盤の大量のテーブルを掃除したい人
- データ基盤管理者
なぜ未使用テーブル監視が必要だったのか
行動ログの種類が増え、維持コストが膨らむ
ABEMAでは行動ログの種類が約50種類ほどあります。
行動ログの種類は多ければ多いほど分析の幅が広がりますが、その分クライアントエンジニアやQAエンジニアの実装、テストのコストが高まるという問題がありました。
新しい機能を開発するたびに、全てのログが正しく送信されているか確かめる必要があり、そのために労力をかけています。
しかし、その大半は使われていない
ここで問題なのが、行動ログの大半はもう既に使用されていないということです。
新規機能の開発や、新しい分析、検証のためにログが作成されますが、それらのプロジェクトが終わった後に継続的に分析され続けているログはほとんどありません。
そのため、クライアントチームが送ってくれているログが、本当は分析に利用されていないから送らなくても問題にならない、という問題を抱えています。
かといって「利用していない」と言い切れる人はいない
それなら、利用していない行動ログを削除すればいいと思ってしまいますが、ABEMAには約600名の社員が在籍しており、もう利用していないと思われるデータを、実は誰かがまだ利用しているということがあります。
社員全員にヒアリングすることは可能ですが、誤って廃止したときにどのようなビジネス影響があるか不明なため、リスクを考慮し確実に廃止できる方法を選択することにしました。
そこで、使っていないテーブルから逆算する
ABEMAのデータウェアハウスでは、行動ログデータは1つのデータソースとなるテーブルに集約されています。
そのテーブルから行動ログ別のテーブルに分解し、それぞれのテーブルを加工して分析用のテーブルを作成しています。
分析用のテーブルを廃止するための確実な方法を考えたとき、BigQueryの利用していないテーブル（以下、未利用テーブル）を洗い出していくことで、最終的に行動ログ別のテーブルまで削除でき、最後のこのテーブルの利用者も0だったときに行動ログ自体を廃止できると考えました。
リーフノードから刈る
より安全に未利用テーブルを廃止するために、未利用テーブルの定義を、「 audit log 上での利用回数が0」「リネージの最も端のリーフノードである」としました。
リーフノードのテーブルと定義した理由は、下流の依存関係がなくコンソール、もしくは BI ツールによる getData API での取得の2種類でしかデータ利用履歴が残らないためです。これらの取得方法はすべて audit log 上で取得可能となっています。
未利用テーブルの廃止において最も重要なのは、安全にテーブルを参照できなくさせることです。
全てのテーブルに対して一律に使用状況を確認し、使用回数が0のテーブルでも、その先のテーブルで利用されている場合があるため一概に削除できるとは言えないと考えました。
そこで、枝葉から刈っていくイメージで徐々にテーブルを廃止していき、長いスパンで見て未利用テーブルがなくなっていくように設計しました。
Data Lineage APIは、テーブルのリネージ関係を探索し、リーフノードにおけるテーブルを発見するのに使用しています。
リネージ関係を取得するときのルートのテーブルには、生のログデータを保存しているテーブルを指定しています。
幸い、ABEMAの行動ログのデータは最初の1つのテーブルから全てスタートしていたため、ここの選定が楽に済みました。
audit log の役割
audit log はクエリの利用回数を調べるのに利用しています。
audit log にした理由は BI ツールからの参照まで知ることができるためです。
BigQuery には JOBS ビュービューを利用して実行されたクエリを分析することができますが、こちらの場合は Tableau などのSaaS製品が実行したAPIの履歴までは取ることができませんでした。
このときの、利用回数を調べる遡及期間は1か月に設定しています。
この1か月という数字に大きな意味はありませんが、なるべく長く取ることで廃止するときの安全性が高くなります。
システム構成
本システムは Managed Service for Apache Airflow （旧 Cloud Composer）上の月次バッチとして動作します。
行動ログのルートテーブルを起点に Data Lineage API で下流グラフを辿ってリーフテーブルを特定し、audit log から集計した直近 1 ヶ月の利用履歴と BigQuery 上で突き合わせ、利用回数が 0 のテーブル（未利用テーブル）を Slack に通知します。
アーキテクチャ全体図
全体は「リーフ検出 → 利用回数の集計 → Slack 通知」という月次のパイプラインですが、本記事では技術的な肝である次の 2 点に絞って解説します。
- リーフテーブルの検出ロジック ── Data Lineage API でどう「末端のテーブル」を見つけるか
- audit log のクエリ方法 ── 「実際に使われたか」をどう測るか
リーフテーブルの検出ロジック
探索の起点は行動ログの生テーブル root_table です。
このテーブルから派生する系譜だけを対象に、下流に実テーブルを持たないノード＝リーフテーブルを特定します。
Data Lineage API はテーブルを bigquery:project.dataset.table という FQN（完全修飾名）で識別します。まずは FQN の組み立てとパースを行うヘルパーです。
_BQ_FQN_PREFIX = "bigquery:"
@dataclass
class TableRef:
"""BigQuery テーブルの識別子。"""
project: str
dataset: str
table: str
def _build_root_fqn(env: str) -> str:
"""環境に応じたルートテーブルの Data Lineage API 用 FQN を構築する。"""
prefix = env_prefix(env, "_")
return f"{_BQ_FQN_PREFIX}abema.abema_sample.root_table"
def _parse_fqn(fqn: str) -> TableRef | None:
"""FQN 文字列から project/dataset/table を抽出する。非 BigQuery アセットは None。"""
if not fqn.startswith(_BQ_FQN_PREFIX):
return None
parts = fqn[len(_BQ_FQN_PREFIX) :].split(".")
if len(parts) != 3:
return None
return TableRef(project=parts[0], dataset=parts[1], table=parts[2])
以下は探索の本体です。
LineageClient.search_links に source（入力側）としてテーブルを渡すと、そのテーブルを起点とする下流リンクが返ります。
これを幅優先探索（BFS）で辿り、テーブルの下流を 1 つも持たないノードをリーフと判定します。
判定時に下流から除外するもの（＝そのノードを「中間ノード」に格上げしない対象）は次のとおりです。
- ビュー（VIEW）：ビューしか下流に持たないテーブルはリーフ扱いにしており、これは audit log 上では VIEW ではなく参照元のテーブルが履歴として残るためです
- 一時データセット（_ 始まり）：クエリ実行時の一時的な中間生成物
- 削除済み（NotFound）／アクセス拒否（Forbidden）：実体を確認できないテーブル
def _traverse_downstream(
lineage_client: LineageClient,
bq_client: bigquery.Client,
root_fqn: str,
) -> list[TableRef]:
"""BFS でルートテーブルから下流グラフを探索し、リーフテーブル情報を返す。"""
visited: set[str] = set()
queue: deque[str] = deque([root_fqn])
visited.add(root_fqn)
leaves: list[TableRef] = []
while queue:
current_fqn = queue.popleft()
current_ref = _parse_fqn(current_fqn)
if current_ref is None:
continue
# current_fqn を入力（source）とする下流リンクを検索
request = SearchLinksRequest(
parent=f"projects/{current_ref.project}/locations/us",
source=EntityReference(fully_qualified_name=current_fqn),
)
has_bq_downstream = False
for link in lineage_client.search_links(request=request):
target_fqn = link.target.fully_qualified_name
target_ref = _parse_fqn(target_fqn)
if target_ref is None:
continue
if target_fqn in visited:
has_bq_downstream = True
continue
table_fqn = f"{target_ref.project}.{target_ref.dataset}.{target_ref.table}"
if target_ref.dataset.startswith("_"):
continue # 一時データセット
try:
tbl = bq_client.get_table(table_fqn)
except NotFound:
continue # 削除済み
except Forbidden:
continue # アクセス拒否
if tbl.table_type == "VIEW":
continue # ビュー
has_bq_downstream = True
visited.add(target_fqn)
queue.append(target_fqn)
# 下流に実テーブルが無ければリーフ（ルート自身は除く）
if not has_bq_downstream and current_fqn != root_fqn:
leaves.append(
TableRef(current_ref.project, current_ref.dataset, current_ref.table)
)
return leaves
audit log のクエリ方法
ここでは BigQuery にエクスポート済みの audit log テーブル cloudaudit_googleapis_com_data_access を参照します。
このテーブルは事前に作成しているもので、 https://docs.cloud.google.com/architecture/security-log-analytics?hl=ja を参考に作成することができます。
クエリのポイントは次のとおりです。
- 「利用」の定義：bigquery.tables.getData 権限を伴い、かつ Query / InsertJob のジョブだけを利用とみなす（メタデータ参照などは除外）
- ノイズ除外：セキュリティスキャナ等の自動アクセスは、サービスアカウントで除外しないと「利用あり」と誤判定されてしまう
-- リーフテーブルの利用統計を集計
-- monitoring.leaf_tables と audit log を結合し、各リーフテーブルのクエリ利用回数を取得
SELECT
REGEXP_EXTRACT(protopayload_auditlog.resourcename, r'projects/([^/]+)') AS project,
REGEXP_EXTRACT(protopayload_auditlog.resourcename, r'datasets/([^/]+)') AS dataset,
REGEXP_EXTRACT(protopayload_auditlog.resourcename, r'tables/([^/]+)') AS table_name,
COUNT(*) AS query_count
FROM `cloudaudit_googleapis_com_data_access`
, UNNEST(protopayload_auditlog.authorizationinfo) AS authorizationinfo
WHERE
timestamp >= TIMESTAMP(DATE '${dt}')
AND timestamp < TIMESTAMP(DATE_ADD(DATE '${dt}', INTERVAL 1 MONTH))
AND protopayload_auditlog.resourcename LIKE 'projects/%/datasets/%/tables/%'
AND authorizationinfo.permission = "bigquery.tables.getData"
AND protopayload_auditlog.methodname IN (
"google.cloud.bigquery.v2.JobService.InsertJob",
"google.cloud.bigquery.v2.JobService.Query"
)
AND protopayload_auditlog.authenticationinfo.principalemail NOT IN (
"（スキャナ用サービスアカウントのメールアドレス）" -- 自動スキャンを除外
)
GROUP BY
1, 2, 3
運用してみた結果
通知が来たタイミングでミーティングを行い、廃止できるかできないか、廃止する場合はどのようなコミュニケーション設計が必要かなどを話し合ったうえで、未利用テーブルへの対応を手動で行っています。
この監視システムにより数十個の未利用テーブルが検知され、廃止可能なテーブルもいくつか検出することができました。
この10年間で手を付けていなかったテーブルの廃止プロセスの一歩を踏み出したことで、複雑化したデータ基盤をシンプルにできるという手触りの感覚が得られました。
また、通知された未利用テーブルもメンバーの肌感覚と合致しており、リーフノードのテーブルから廃止していくというプロセス自体は間違っていないと感じています。
今後の展望
今後はこのシステムを利用しながら徐々にテーブル数を減らし、分析に本当に必要なテーブルだけが残った状態を作りたいと思っています。
新しく入ってきた分析者がジョインした初日にすぐに仕事に取りかかれる、シンプルでわかりやすいデータ基盤を提供するために、引き続き廃止運動は続けていこうと思います。
関連記事
今日のまとめ
AI日報で今日の重要ニュースをまとめ読み