BigQuery や Google Workspace のログを使って分析環境を管理する

2023年9月5日 11:21

BI Product チームの野本です。
メルカリでは Google BigQuery 監査ログと Google Workspace ログイベントを BigQuery にエクスポートして、データ分析環境の管理に活用しています。アクセス履歴を詳細に調べられる BigQuery 監査ログは、分析環境のコスト最適化やテーブルの変更時の影響範囲調査などの管理業務にとても便利です。
BigQuery の利用が増えてくると、過剰に高頻度なクエリジョブや、使われずに放置されたテーブルなどにかかる無駄なコストも増大していきます。これらの使われていないジョブやテーブルを抽出し削減することで、BigQuery の計算やデータの保管にかかるコストを削減することができます。
メルカリでは、BigQuery 監査ログ、Google Workspace ログイベントを活用して、分析環境の管理業務に役立てていますので、この記事で実施例を紹介します。

また、メルカリでのINFORMATION_SCHEMA を活用した事例は別記事がありますので、興味ある方は以下もご覧ください。

データ基盤は「データで」改善する − 中間テーブル作成の事例

レガシー化したData Pipelineの廃止 ― メルカリのData Architectのお仕事例

対象とする読者

データ分析環境の管理方法に興味がある方
組織内の BigQuery 利用が増え、過剰なジョブや使われないテーブルにかかるコストが増えてきた実感のある方
BigQuery INFORMATION_SCHEMA よりも詳細に分析環境の活用状況を確認したい人

この記事で紹介すること

BigQuery 監査ログを活用して...
- テーブルやビューが参照されているか確認する方法
- どの Google スプレッドシートから BigQuery ジョブが実行されているかを特定する方法
Google Workspace ログイベントを活用して...
- スプレッドシートが使用されているかを確認する方法

BigQuery INFORMATION_SCHEMA ビューでよいのでは？

BigQuery に関するメタデータ情報を確認したい場合は、スキーマはシンプルでログシンク不要で直ぐにクエリが叩ける、みんな大好き INFORMATION_SCHEMA を活用すると思います。INFORMATION_SCHEMA でできることの代表例を記載しますので、これらの用途で事足りるのであれば、INFORMATION_SCHEMA を使用することをお勧めします。

INFORMATION_SCHEMA とは

詳しくは公式ドキュメントをお読みください。

BigQuery INFORMATION_SCHEMA ビューは、BigQuery オブジェクトに関するメタデータ情報を提供するシステム定義の読み取り専用ビューです。

BigQuery INFORMATION_SCHEMA の概要 | BigQuery ドキュメント

INFORMATION_SCHEMA でできること

INFORMATION_SCHEMA を活用するとBigQuery ジョブやテーブルの整理に必要な情報は把握できます。具体的には、以下のようなことなどが確認できます。

JOBS ビュー
- 処理されたデータサイズ・消費したスロット時間が多いジョブを抽出する
- 特定のテーブルやビューの利用者を特定する(棚卸しや更新時の影響範囲調査)
JOBS_TIMELINE ビュー
- 毎秒ごとの消費したスロット時間を細かく確認し、混雑具合を把握する
- ジョブが主にスロットを使う予約（Reservation）を特定し、Reservation の管理する
TABLE_STORAGE ビュー
- 高コストなテーブルを抽出する

シンプルで使いやすい INFORMATION_SCHEMA ですが、ところどころ「もっと詳細が知りたい！」と思う場面と遭遇します。この記事では、その一部の分析方法について記載していきます。

ログを分析する前に

より詳しく調べられる BigQuery 監査ログを分析するためには、ログを BigQuery へエクスポートするなどの事前準備が必要になってきます。エクスポートされていない場合には、文末の「Appendix (準備) ログを BigQuery へエクスポート」を参考にして、 BigQuery 監査ログ、Google Workspace ログイベントを BigQuery にエクスポートしてください。

以下から活用事例を紹介します。

ビューが参照されているかどうかを分析できる

何がうれしいか : ビューを更新や廃止する際に影響範囲を確認できる！
分析するログ: cloudaudit_googleapis_com_data_access の “referencedViews”
公式ドキュメントはこちら。

以下のクエリで確認できます。

SELECT
  JSON_VALUE_ARRAY(protopayload_auditlog.metadataJson, '$.jobChange.job.jobStats.queryStats.referencedViews') AS referenced_views,
FROM
  `<audit_log_dataset>.cloudaudit_googleapis_com_data_access`

また、ビュー以外にテーブルとルーティンも併せて確認できます。

SELECT
  JSON_VALUE_ARRAY(protopayload_auditlog.metadataJson, '$.jobChange.job.jobStats.queryStats.referencedTables') AS referenced_tables,
  JSON_VALUE_ARRAY(protopayload_auditlog.metadataJson, '$.jobChange.job.jobStats.queryStats.referencedRoutines') AS referenced_routines,
FROM
  `<audit_log_dataset>.cloudaudit_googleapis_com_data_access`

説明のためのデータパイプラインの構成

説明のための以下のデータパイプラインの構成で説明していきます。
"→"は参照を示していて、 View_1 は Source_table_1,2 を参照していて、 Data_mart_table_1 などに参照されています。

INFORMATION_SCHEMA.JOBS ではビューが参照されているわからない

INFORMATION_SCHEMA では、referenced_tables (ジョブによって参照されるテーブルの配列)の情報は格納されているが、参照元のビューの情報がないので確認できません。
以下の様なクエリで、Data_mart_table1 の参照元が Source_table1,2 であることはわかりますが、 View1 を介していることはわかりません。　

SELECT
　　ref.table_id AS referenced_table,
　　destination_table.table_id AS destination_table,
FROM
　　`region-us`.INFORMATION_SCHEMA.JOBS, UNNEST(referenced_tables) as ref
WHERE
　　creation_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY) AND CURRENT_TIMESTAMP()
　　AND job_type = 'QUERY'
　　AND destination_table.table_id = 'Data_mart_table_1'

BigQuery 監査ログの場合

JobStatistics 以下に "referencedViews"の情報が格納されているので、こちらを利用します。

以下の様なクエリで抽出します。
● SQL サンプル

CREATE TEMPORARY FUNCTION DATE_FROM() AS (CURRENT_DATE('Asia/Tokyo')-7);
CREATE TEMPORARY FUNCTION DATE_TO() AS (CURRENT_DATE('Asia/Tokyo')-1);


WITH USING_VIEW AS (
　　SELECT
　　　　JSON_VALUE(protopayload_auditlog.metadataJson, '$.jobChange.job.jobConfig.queryConfig.destinationTable') destinationTable,
　　　　REGEXP_EXTRACT(referencedViews, '^projects/([^/]+)/datasets/[^/]+/tables') AS ref_project,
　　　　REGEXP_EXTRACT(referencedViews, '^projects/[^/]+/datasets/([^/]+)/tables') AS ref_dataset,
　　　　REGEXP_EXTRACT(referencedViews, '^projects/[^/]+/datasets/[^/]+/tables/(.*)$') AS ref_table,
　　FROM `<audit_log_dataset>.cloudaudit_googleapis_com_data_access`
　　,UNNEST(JSON_VALUE_ARRAY(protopayload_auditlog.metadataJson, '$.jobChange.job.jobStats.queryStats.referencedViews')) AS referencedViews
　　WHERE
　　　　DATE(`timestamp`) BETWEEN DATE_FROM() AND DATE_TO()
)
,using_table AS (
　　SELECT
　　　　JSON_VALUE(protopayload_auditlog.metadataJson, '$.jobChange.job.jobConfig.queryConfig.destinationTable') destinationTable,
　　　　REGEXP_EXTRACT(referencedTables, '^projects/([^/]+)/datasets/[^/]+/tables') AS ref_project,
　　　　REGEXP_EXTRACT(referencedTables, '^projects/[^/]+/datasets/([^/]+)/tables') AS ref_dataset,
　　　　REGEXP_EXTRACT(referencedTables, '^projects/[^/]+/datasets/[^/]+/tables/(.*)$') AS ref_table,
　　FROM `<audit_log_dataset>.cloudaudit_googleapis_com_data_access`
　　,UNNEST( JSON_VALUE_ARRAY(protopayload_auditlog.metadataJson, '$.jobChange.job.jobStats.queryStats.referencedTables')) AS referencedTables
　　WHERE
　　　　DATE(`timestamp`) BETWEEN DATE_FROM() AND DATE_TO()
)
,using_view_or_table AS (
　　SELECT * FROM using_view
　　UNION ALL
　　SELECT * FROM using_table
)
SELECT
　　REGEXP_EXTRACT(destinationTable, '^projects/[^/]+/datasets/[^/]+/tables/(.*)$') AS dest_table,
　　ref_table,
FROM using_view_or_table
WHERE
　　REGEXP_EXTRACT(destinationTable, '^projects/[^/]+/datasets/[^/]+/tables/(.*)$') IN ('Data_mart_table_1', 'Data_mart_table_2')
GROUP BY dest_table, ref_table
ORDER BY dest_table, ref_table

この様に、Data_mart_table_1 の参照元が View_1 を介していることはわかります。
また、Data_mart_table_2 のように複数のビューを介していることもわかります。

どのスプレッドシートから BigQuery ジョブが実行されているかを特定できる

何がうれしいか :

スプレッドシートのデータコネクタで参照されているテーブルなどが更新や廃止される場合に影響範囲として抽出できる
高コストなクエリに対応するスプレッドシートを特定できる

分析するログ: コネクテッドシートのログイベントを表示する

コネクテッドシートが BigQuery データにアクセスすると、Cloud Audit Logs にエントリが記録されます。Google Cloud コンソールのログエクスプローラを使用して、これらのログを分析できます。
各スプレッドシートには、一意の ID が含まれています（スプレッドシートの URL で確認できます）。BigQueryAuditMetadata 形式のログエントリには、BigQuery データアクセスリクエストの送信元となるスプレッドシートの ID が含まれています。コネクテッドシートのログエントリを確認する手順は次のとおりです。

コネクテッドシートのログイベントを表示する

INFORMATION_SCHEMA.JOBS では、どのスプレッドシートから実行されたかどうかわからない

はい。残念ながらわかりません。

BigQuery 監査ログの場合

cloudaudit_googleapis_com_data_access 以下の ”protoPayload.metadata.firstPartyAppMetadata.sheetsMetadata.docId” の中にスプレッドシートの id の情報が格納されています。これを抽出するクエリの例が以下です。
● SQL サンプル

SELECT
  "https://docs.google.com/spreadsheets/d/" || JSON_VALUE(protopayload_auditlog.metadataJson, "$.firstPartyAppMetadata.sheetsMetadata.docId") AS url
FROM
  `<audit_log_dataset>.cloudaudit_googleapis_com_data_access`
WHERE
  DATE(timestamp) = "YYYY-MM-DD"
  AND JSON_VALUE(protopayload_auditlog.metadataJson, "$.firstPartyAppMetadata.sheetsMetadata.docId") IS NOT NULL

Google Workspace ログイベントでスプレッドシートが使用されているか確認する

何がうれしいか : スプレッドシートの利用状況をもとに、クエリジョブの実行頻度を減らすべきかや、高頻度に実行するジョブのパフォーマンスを改善すべきか、などが判断できる。
分析するログ: ドライブのログイベント

エクスポートされたテーブル

スキーマ
日付(イベントが発生した日時): time_usec,
アクター(操作を行ったユーザーのメールアドレス): email,
イベント名: event_name,
ドキュメント ID: drive.doc_id,
ドキュメントの種類: drive.doc_type,

以下は、過去30日でクエリが実行されているが、アクセスがない Sheet を抽出する例です。
● SQL サンプル

CREATE TEMPORARY FUNCTION START_DATE() AS (CURRENT_DATE('Asia/Tokyo')-30);
CREATE TEMPORARY FUNCTION END_DATE() AS (CURRENT_DATE('Asia/Tokyo'));

SELECT
  TIMESTAMP_MICROS(MAX(time_usec)) AS latest_event_time_at,
  COUNT(distinct email) AS sheet_access_uu,
  ARRAY_AGG(distinct email) AS sheet_users,
  COUNTIF(event_name = 'view') AS view_event_count, -- view された回数
  COUNTIF(event_name = 'connected_sheets_query') AS connected_sheets_query_event_count, --　query が実行回数
  doc_id,
  'https://docs.google.com/spreadsheets/d/' || doc_id || '/' AS url
FROM
  `<google Workspace ログイベントの google sheets access データ>`
WHERE
  DATE(_PARTITIONTIME, 'Asia/Tokyo') BETWEEN START_DATE() AND END_DATE()
  AND drive.doc_type = "spreadsheet" AND event_type = "access" -- スプレッドシートに関連するログに絞る
GROUP BY doc_id
HAVING connected_sheets_query_event_count > 0 -- query が実行されているもののみを抽出
AND view_event_count = 0 --　一度も view されていないもの
ORDER BY event_count_without_connected_sheets_query

どういう時に役立つか

定期実行で更新されているデータコネクタが実際に使用されているかを確認し、無駄な定期実行の停止や更新頻度の見直しに活用できます。
INFORMATION_SCHEMA や BigQuery 監査ログだけではジョブが実行されていることはわかっても、実際に使用されているかどうかまではわからなかったので便利です。

まとめ

BigQuery 監査ログ、Google Workspace ログイベントについて以下のような活用例を紹介しました。
BigQuery 監査ログ

ビューが参照されているかどうかを分析する方法
どのスプレッドシートから BigQuery ジョブが実行されているかを特定する方法

Google Workspace ログイベント

Google Workspace のログイベントでスプレッドシートが使用されているか確認する方法

是非 BigQuery にエクスポートして、データ分析環境の管理に活用していきましょう！

Appendix (準備) ログを BigQuery へエクスポート

この記事では、BigQuery 監査ログ、 Google Workspace ログイベントを BigQuery で分析できる前提で記載しています。そのため、BigQuery にエクスポートしていない場合には、Google Cloud 組織全体や Google Workspace の管理者権限を持っている方に BigQuery へのエクスポートを依頼しましょう！

BigQuery 監査ログのエクスポート

BigQuery の監査ログパイプラインを活用した使用状況分析の「BigQuery へのエクスポート」を参考にしてください。
組織レベルで集約シンクを作成し、BigQuery シンクにログを転送するなどの設定が必要です。

Google Workspace のログイベントのエクスポート

BigQuery での Google Workspace のログイベントとレポートのサービスログの BigQuery への書き出しを設定するを参考にしてください。

BigQuery や Google Workspace のログを使って分析環境を管理する

対象とする読者

この記事で紹介すること

BigQuery INFORMATION_SCHEMA ビューでよいのでは？

INFORMATION_SCHEMA とは

INFORMATION_SCHEMA でできること

ログを分析する前に

ビューが参照されているかどうかを分析できる

説明のためのデータパイプラインの構成

INFORMATION_SCHEMA.JOBS ではビューが参照されているわからない

BigQuery 監査ログの場合

どのスプレッドシートから BigQuery ジョブが実行されているかを特定できる

INFORMATION_SCHEMA.JOBS では、どのスプレッドシートから実行されたかどうかわからない

BigQuery 監査ログの場合

Google Workspace ログイベントでスプレッドシートが使用されているか確認する

エクスポートされたテーブル

どういう時に役立つか

まとめ

Appendix (準備) ログを BigQuery へエクスポート

BigQuery 監査ログのエクスポート

Google Workspace のログイベントのエクスポート

いいなと思ったら応援しよう！