Spread SheetからBigQueryを参照している箇所を特定する方法

BigQueryのConnected Sheet機能を使うと、BigQueryとSpread Sheetを簡単に連携できてとても便利です。

しかし、その便利さ・気軽さが仇となるケースも存在します。
定期的にBigQueryを参照しているシートが不要になった後もクエリを実行し続け、費用が無駄にかかってしまうことが考えられます。
また、スキャン量やスロット数に問題のあるクエリの監査ログを確認して、実行ユーザーに連絡をとってもConnected Sheet経由のクエリの場合は、身に覚えないと返答されることも多いです。

この記事では、Spead SheetからBigQueryにクエリを実行したログを活用して、どのシートURLからどのクエリが実行されているのかを調査する方法を説明します。

まずGCPの監査ログをCloud LoggingからBigQueryに転送するようなLog Sinkを作成します。

resource "google_bigquery_dataset" "audit_log" {
  project    = "<プロジェクトID>"
  dataset_id = "audit_log"
  location   = "US"
}

resource "google_logging_organization_sink" "audit_log_sink" {
  name   = "audit_log_sink"
  org_id = "<Organization ID>"

  destination      = "bigquery.googleapis.com/${google_bigquery_dataset.audit_log.id}"
  include_children = true
  filter           = "protoPayload.@type=\"type.googleapis.com/google.cloud.audit.AuditLog\""
}

これにより、監査ログがBigQueryに蓄積されます。

次に、この監査ログに対して、以下のクエリを実行すると、どのシートからBigQueryを参照しているのかが分かります。

select
  protopayload_auditlog.authenticationInfo.principalEmail as email,
  resource.labels.project_id,
  REGEXP_EXTRACT(protopayload_auditlog.resourceName, r'^projects/[\w-]+/jobs/([\w-]+)$') as job_id,
  timestamp,
  "https://docs.google.com/spreadsheets/d/" || json_value(protopayload_auditlog.metadataJson, "$.firstPartyAppMetadata.sheetsMetadata.docId") as sheet_url,
  json_value(protopayload_auditlog.metadataJson, "$.jobInsertion.job.jobConfig.labels.sheets_trigger") as trigger,
  json_value(protopayload_auditlog.metadataJson, "$.jobInsertion.job.jobConfig.queryConfig.query") as query,
from `<プロジェクトID>.audit_log.cloudaudit_googleapis_com_data_access_*`
where
protopayload_auditlog.serviceName = "bigquery.googleapis.com" and
json_value(protopayload_auditlog.metadataJson, "$.firstPartyAppMetadata.sheetsMetadata.docId") is not null