BigQuery や Google Workspace のログを使って分析環境を管理する
BI Product チームの野本です。
メルカリでは Google BigQuery 監査ログと Google Workspace ログイベントを BigQuery にエクスポートして、データ分析環境の管理に活用しています。アクセス履歴を詳細に調べられる BigQuery 監査ログは、分析環境のコスト最適化やテーブルの変更時の影響範囲調査などの管理業務にとても便利です。
BigQuery の利用が増えてくると、過剰に高頻度なクエリジョブや、使われずに放置されたテーブルなどにかかる無駄なコストも増大していきます。これらの使われていないジョブやテーブルを抽出し削減することで、BigQuery の計算やデータの保管にかかるコストを削減することができます。
メルカリでは、BigQuery 監査ログ、Google Workspace ログイベントを活用して、分析環境の管理業務に役立てていますので、この記事で実施例を紹介します。
また、メルカリでのINFORMATION_SCHEMA を活用した事例は別記事がありますので、興味ある方は以下もご覧ください。
対象とする読者
データ分析環境の管理方法に興味がある方
組織内の 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 オブジェクトに関するメタデータ情報を提供するシステム定義の読み取り専用ビューです。
INFORMATION_SCHEMA でできること
INFORMATION_SCHEMA を活用するとBigQuery ジョブやテーブルの整理に必要な情報は把握できます。具体的には、以下のようなことなどが確認できます。
処理されたデータサイズ・消費したスロット時間が多いジョブを抽出する
特定のテーブルやビューの利用者を特定する(棚卸しや更新時の影響範囲調査)
毎秒ごとの消費したスロット時間を細かく確認し、混雑具合を把握する
ジョブが主にスロットを使う予約（Reservation）を特定し、Reservation の管理する
高コストなテーブルを抽出する
シンプルで使いやすい 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 NULLGoogle 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 への書き出しを設定するを参考にしてください。
この記事が気に入ったらサポートをしてみませんか？