如何获取BigQuery表的最后一次基于消费的访问日期（排除数据插入/创建操作）

嘿，我之前刚好处理过类似的需求，分享几个亲测有效的方法给你参考：

一、通过BigQuery Audit Logs精准捕获所有消费操作 #

这是最全面的方案，因为Audit Logs能覆盖你提到的所有消费场景——包括内部查询、导出到其他存储、API读取等，甚至第三方工具访问的记录也能抓到。不过前提是你得先给项目开启Data Access Audit Logs（针对BigQuery服务），之后就可以在Cloud Logging里写查询过滤目标操作：

1. 过滤规则思路：

   • 排除插入/创建类操作：比如CreateTable、InsertJob里的LOAD写入、CREATE TABLE AS SELECT这类写入型任务；
   • 保留消费类操作：只读查询、数据导出任务、BigQuery Storage API的读取会话创建（这是很多外部工具读取数据的方式）。

2. Cloud Logging查询示例：

   resource.type="bigquery_resource"
   logName:"projects/[你的项目ID]/logs/cloudaudit.googleapis.com%2Fdata_access"
   (
     protoPayload.methodName:"google.cloud.bigquery.v2.JobService.InsertJob"
     AND (
       protoPayload.serviceData.jobInsertRequest.job.configuration.query.destinationTable IS NULL
       OR protoPayload.serviceData.jobInsertRequest.job.configuration.export IS NOT NULL
     )
   )
   OR protoPayload.methodName:"google.cloud.bigquery.storage.v1.BigQueryReadService.CreateReadSession"
   

   这个查询会筛选出：没有写入目标表的只读查询、数据导出任务、以及通过Storage API发起的读取请求。之后你可以按表分组，提取每个表对应的最新时间戳，就是最后一次消费访问的日期。

二、INFORMATION_SCHEMA与Audit Logs互补使用 #

你提到的INFORMATION_SCHEMA.JOBS_BY_PROJECT/JOBS_BY_ORGANIZATION虽然没法覆盖外部依赖，但能快速获取BigQuery内部的查询访问记录，刚好可以和Audit Logs的结果互补，避免遗漏：

比如先从INFORMATION_SCHEMA里提取内部只读查询的访问记录：

SELECT
  referenced_table.dataset_id,
  referenced_table.table_id,
  MAX(start_time) AS last_internal_query_access
FROM
  `[你的区域]`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
CROSS JOIN UNNEST(referenced_tables) AS referenced_table
WHERE
  job_type = 'QUERY'
  AND state = 'DONE'
  -- 排除写入类查询
  AND NOT REGEXP_CONTAINS(query, r'CREATE\s+TABLE\s+AS\s+SELECT|INSERT\s+INTO|MERGE\s+INTO')
GROUP BY
  referenced_table.dataset_id, referenced_table.table_id

之后把这个结果和Audit Logs里得到的外部访问记录合并，取每个表的最大时间值，就能得到完整的最后消费访问日期。

几个需要注意的点 #

• 记得检查Audit Logs的保留期限：默认是30天，如果要查X个月的历史数据，得提前把日志导出到Cloud Storage或BigQuery做长期存储；
• 区分元数据访问和实际数据消费：比如GetTable操作可能只是查看表结构，不算消费，要结合后续操作判断（比如跟着CreateReadSession才算实际读取数据）；
• 如果有跨项目访问的情况，要确保你有对应项目的Audit Logs查看权限，不然会遗漏跨项目的消费记录。

备注：内容来源于stack exchange，提问作者Yong Jin Lee