BigQueryのAuditLogからジョブの参照カラムの情報を抽出する

[takegue]

こういう感じのクエリを書けばよい

select 
  metadata.`@type` as type
  , metadata.tableDataRead.reason
  , JSON_VALUE_ARRAY(metadata.tableDataRead.fields) as fields
  , *
from `bqmake._auditlog.cloudaudit_googleapis_com_data_access` as c
left join unnest([struct(
   safe.parse_json(protopayload_auditlog.metadataJson) as metadata
)])
where 
  date(timestamp) >= date(current_date() - interval 1 day)
  and resource.type = 'bigquery_dataset'
order by timestamp desc

[takegue]

version 2

with datasource as (
  select
    metadata.`@type` as type
    , c.timestamp
    , resource
    , protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent
    , JSON_VALUE_ARRAY(metadata.tableDataRead.fields) as fields
    , string(metadata.tableDataRead.jobName) as jobName
  from `bqmake._auditlog.cloudaudit_googleapis_com_data_access` as c
  left join unnest([struct(
    safe.parse_json(protopayload_auditlog.metadataJson) as metadata
  )])
  where
    date(timestamp) >= date(current_date() - interval 1 day)
)
, tableReadData as (
  select regexp_extract(jobName, '[^/]+$') as jobId, to_json_string(fields) as fields from datasource
  where resource.type = 'bigquery_dataset'
)
, jobCompleteEvent as (
  select
    timestamp
    , jobCompletedEvent.job.jobName.jobId as jobId
    , struct(
       jobCompletedEvent.job.jobConfiguration as config
       , jobCompletedEvent.job.jobStatistics as statistics
    ) as job
    , jobCompletedEvent as _raw
  from datasource
  where jobCompletedEvent.eventName = 'query_job_completed'
)

select *
from jobCompleteEvent
left join tableReadData using(jobId)
order by timestamp desc

[takegue]

• tableReadDataのフィールドは基本的に audit logと同一Project内のリソースの参照が埋め込まれる
  • 外部のプロジェクトのデータセットの参照情報は得られない
• CTEで参照が付くものではなく、実行解析のクエリのフィールド
• エラーが起きると、参照フィールドは得ることができない