BigQueryの履歴からテーブルの参照があるか確認する

Author:

taketsuru

2024-06-26

はじめに

こんにちは。株式会社 Belong で Data Platform チーム所属の taketsuru です。

長いこと BigQuery を運用しているとこのテーブルってどこから参照されているんだっけ？ということがあります。

思いきって削除したくても社内外から問い合わせが来そうで削除できないという方もいるのではないでしょうか。

今回は BigQuery の履歴からテーブルの参照があるか確認する方法について書いていきます。

timee さんのブログを参考にさせていただきました。

BigQuery の履歴

弊社はプロジェクト全体の監査ログを SRE チームが管理しています。

上記のブログみたいなことやりたいと相談したら dataset をもらいました。

有用な情報が得られることが分かったので将来的には自前で管理したいところです。

また、timee さんブログとはログのフォーマットが異なり参照先のスプレッドシート情報までは取れなかったです。

timee さんブログではこちらの形式でしたが、弊社はこちらの形式でした。

記載を見ると弊社のログ形式が古いらしく、ログ保存側で設定の変更や更新が必要かもしれません。

ひとまずデータを眺めてそれっぽいクエリを作成しました。

スプレッドシートもしくは Looker studio から参照されているテーブルを抽出するクエリ例です。

audit_data カラムが AuditData 型になります。

SELECT
  timestamp AS ts,
  audit_data.authenticationInfo.principalEmail AS user,
  "looker_studio" as refer_type,
  ( SELECT array_agg(value) FROM unnest(audit_data.servicedata_v1_bigquery.jobQueryResponse.job.jobConfiguration.labels) WHERE key = "looker_studio_report_id" ) as resource_id,
  ( SELECT array_agg(distinct resource) FROM unnest(audit_data.authorizationInfo) where resource like "%datasets%" ) AS info
FROM `project.dataset.bigquery_auditlog`
WHERE
  exists ( SELECT 1 FROM unnest(audit_data.servicedata_v1_bigquery.jobQueryResponse.job.jobConfiguration.labels) WHERE key = "requestor" AND value = "looker_studio" )
UNION ALL
SELECT
  timestamp AS ts,
  audit_data.authenticationInfo.principalEmail AS user,
  "spread_sheet" as refer_type,
  null as resource_id,
  ( SELECT array_agg(distinct resource) FROM unnest(audit_data.authorizationInfo) where resource like "%datasets%" ) AS info
FROM `project.dataset.bigquery_auditlog`
WHERE
  exists ( SELECT 1 FROM unnest(audit_data.authenticationInfo.serviceAccountDelegationInfo) WHERE firstPartyPrincipal.serviceMetadataJson = "{\"service\":\"sheets\"}" )
ORDER BY ts

こんな結果が得られます。

suggest one line chat

いつ誰がどのテーブルを見たかが追えるので、参照があるかどうかの確認には十分です。

Looker studio の場合は looker_studio_report_id からダッシュボードの UUID が取得できます。

dbt にも補助的な機能はある

とはいえ都度都度クエリするのは確実とはいえ手間ではあります。

参照の有無とかではなくどこで参照しているか簡単なメモを残しておく需要もあると思います。

そういう時は dbt exposure です。

テーブルメタデータ定義の yml に exposure という項目を記載することで dbt docs generate で生成されるドキュメント、リネージグラフに記載されます。

公式はこちら。

以下は定義を抜粋したものです。


version: 2

exposures:
  - name: <string_with_underscores>
    [description](/reference/resource-properties/description): <markdown_string>
    type: {dashboard, notebook, analysis, ml, application}
    url: <string>
    maturity: {high, medium, low}  # Indicates level of confidence or stability in the exposure
    [tags](/reference/resource-configs/tags): [<string>]
    [meta](/reference/resource-configs/meta): {<dictionary>}
    owner:
      name: <string>
      email: <string>

    depends_on:
      - ref('model')
      - ref('seed')
      - source('name', 'table')
      - metric('metric_name')

    label: "Human-Friendly Name for this Exposure!"
    [config](/reference/resource-properties/config):
      enabled: true | false

  - name: ... # declare properties of additional exposures

とりあえず owner が便利だと感じました。

リネージ内のあるテーブルやソースに不具合があった時、リネージグラフ内から報告先が取得できるのは便利です。

意外だったのが config で、enabled を false にするとリネージグラフから消えます。

グレーアウトとかを想像してたので予想外でした。

こういった情報の粒度はチームで統一しないとブレがちですが公式のフォーマットがあると統一感を保てて良いですね。

参考にさせていただいた timee さんは自動生成まで至っているらしく見習いたく思います。

まとめ

今回は BigQuery の履歴からテーブルの参照があるか確認する方法について書いていきました。

不要なリソースの削除は後回しにしがちですが積み重なると明確な負債になります。

本件に関しては運用側で意識していても参照側が突然増えることもあるので、定期的に確認することをお勧めします。

弊社 Belong では一緒にサービスを育てる仲間を募集しています。もし弊社に興味を持っていただけたら https://entrancebook.belonginc.dev/ をご覧いただけたら幸いです。