如何检查与监控2个数据集下20张BigQuery表的使用情况以降低成本

1. 使用BigQuery INFORMATION_SCHEMA查询历史访问记录 #

直接通过BigQuery内置的系统视图可以快速获取表的查询详情，包括查询人、扫描字节数、查询时间等信息。以下是针对指定数据集和表的查询示例：

SELECT
  user_email AS 查询人,
  start_time AS 查询时间,
  total_bytes_processed AS 扫描字节数,
  query AS 查询语句,
  job_id AS 任务ID
FROM
  `your-project-id`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  -- 过滤涉及目标表的查询，可批量指定20张表的完整路径
  REGEXP_CONTAINS(query, r'`your-project-id`.`dataset-1`.`table-1`|`your-project-id`.`dataset-2`.`table-2`')
  -- 限定查询时间范围（示例为过去3个月）
  AND start_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 3 MONTH)
  -- 只统计查询类任务
  AND job_type = 'QUERY'
ORDER BY
  start_time DESC;

替换your-project-id、dataset-1/dataset-2以及具体表名即可，也可用IN子句批量指定表路径。

2. 利用Cloud Audit Logs获取更全面的访问日志 #

如果需要覆盖权限变更、表修改等更完整的操作记录，可开启BigQuery审计日志并导出分析：

1. 在Google Cloud控制台开启BigQuery的数据访问日志和管理员活动日志；
2. 将审计日志导出到指定的BigQuery数据集；
3. 执行以下查询分析表的访问情况：

SELECT
  protopayload_auditlog.authenticationInfo.principalEmail AS 操作人,
  timestamp AS 操作时间,
  protopayload_auditlog.methodName AS 操作类型,
  protopayload_auditlog.resourceName AS 目标资源,
  protopayload_auditlog.metadataJson AS 操作详情
FROM
  `your-audit-log-dataset.cloudaudit_googleapis_com_data_access_*`
WHERE
  -- 过滤目标数据集下的表
  protopayload_auditlog.resourceName LIKE '%dataset-1.%' OR protopayload_auditlog.resourceName LIKE '%dataset-2.%'
  -- 限定时间范围
  AND timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 3 MONTH)
  -- 筛选查询类操作
  AND protopayload_auditlog.methodName = 'jobservice.jobcompleted'
ORDER BY
  timestamp DESC;

3. 设置监控告警实现持续跟踪 #

为避免手动重复查询，可通过Cloud Monitoring搭建长期监控机制：

• 创建自定义仪表盘，添加bigquery.googleapis.com/table/query_count、bigquery.googleapis.com/job/query/total_bytes_processed等指标，按表维度拆分，直观查看各表的访问频率和数据扫描量；
• 配置告警策略，当某张表连续N天（如90天）无查询记录时，触发邮件或短信通知，及时识别闲置表。

内容的提问来源于stack exchange，提问作者Sana