如何高效统计BigQuery表的更新与删除操作数量及涉及表？

以下是几种高效替代关联快照查询的方案，按实用性排序：

1. 利用INFORMATION_SCHEMA.TABLE_CHANGES视图 #

BigQuery内置的INFORMATION_SCHEMA.TABLE_CHANGES视图专门跟踪表的DML（UPDATE/DELETE/INSERT）变更历史，无需额外配置即可使用。

示例查询： #

SELECT
  table_catalog,
  table_schema,
  table_name,
  operation_type,
  COUNT(*) AS operation_count
FROM
  `your-project-id`.`your-dataset`.INFORMATION_SCHEMA.TABLE_CHANGES
WHERE
  operation_type IN ('UPDATE', 'DELETE')
  AND change_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY) AND CURRENT_TIMESTAMP()
GROUP BY
  table_catalog, table_schema, table_name, operation_type
ORDER BY
  operation_count DESC;

注意事项： #

• 仅支持标准SQL表，不支持视图、外部表等类型；
• 变更历史默认保留7天，如需更长时间需联系GCP支持调整；
• 建议限定时间段查询，避免全量扫描带来的性能损耗。

2. 配置Cloud Audit Logs导出并分析 #

通过GCP的Cloud Audit Logs捕获所有BigQuery的DML操作，将日志导出到专属BQ表后，即可高效统计各表的更新/删除操作。

步骤： #

1. 在GCP控制台开启BigQuery的数据访问审计日志，指定导出到目标BQ数据集；
2. 日志会以cloudaudit.googleapis.com/data_access为前缀的分区表存储；
3. 查询日志表统计操作：

SELECT
  REGEXP_EXTRACT(JSON_VALUE(protoPayload.resourceName), r'tables/(.+)') AS table_name,
  CASE
    WHEN REGEXP_CONTAINS(JSON_VALUE(protoPayload.serviceData.jobCompletedEvent.job.jobConfiguration.query.query), r'^\s*UPDATE') THEN 'UPDATE'
    WHEN REGEXP_CONTAINS(JSON_VALUE(protoPayload.serviceData.jobCompletedEvent.job.jobConfiguration.query.query), r'^\s*DELETE') THEN 'DELETE'
  END AS operation_type,
  COUNT(*) AS operation_count
FROM
  `your-project-id.your-audit-dataset.cloudaudit_googleapis_com_data_access_*`
WHERE
  protoPayload.methodName = 'jobservice.jobcompleted'
  AND timestamp BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY) AND CURRENT_TIMESTAMP()
GROUP BY
  table_name, operation_type
HAVING
  operation_type IS NOT NULL;

优势： #

• 覆盖所有BQ操作场景，包括手动执行、程序调用的DML；
• 日志保留期限可自定义（最长365天）；
• 可扩展分析操作发起者、执行时长等更多维度信息。

3. 给表添加元数据字段跟踪变更 #

如果能控制表的DML语句，可以在表中新增元数据字段，每次操作时自动记录操作类型和时间，后续直接查询统计。

示例： #

1. 给目标表添加跟踪字段：

ALTER TABLE `your-project-id.your-dataset.your-table`
ADD COLUMN operation_type STRING,
ADD COLUMN operation_timestamp TIMESTAMP;

2. 执行UPDATE时更新字段：

UPDATE `your-project-id.your-dataset.your-table`
SET
  col1 = 'new_value',
  operation_type = 'UPDATE',
  operation_timestamp = CURRENT_TIMESTAMP()
WHERE
  id = 123;

3. 用MERGE模拟带日志的DELETE：

MERGE INTO `your-project-id.your-dataset.your-table` t
USING (SELECT id FROM ... WHERE condition) s
ON t.id = s.id
WHEN MATCHED THEN
  UPDATE SET operation_type = 'DELETE', operation_timestamp = CURRENT_TIMESTAMP();

-- 可选：将标记为DELETE的行迁移到归档表
INSERT INTO `your-project-id.your-dataset.archive-table`
SELECT * FROM `your-project-id.your-dataset.your-table` WHERE operation_type = 'DELETE';
DELETE FROM `your-project-id.your-dataset.your-table` WHERE operation_type = 'DELETE';

4. 统计操作：

SELECT
  table_name,
  operation_type,
  COUNT(*) AS operation_count
FROM
  `your-project-id.your-dataset.your-table`
WHERE
  operation_type IN ('UPDATE', 'DELETE')
  AND operation_timestamp BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY) AND CURRENT_TIMESTAMP()
GROUP BY
  table_name, operation_type;

优势： #

• 自定义程度高，可扩展添加操作人、操作ID等更多跟踪字段；
• 查询效率极高，无需跨表关联或扫描历史快照。

4. 优化时间旅行查询（替代全量快照关联） #

如果必须使用快照对比，可利用BigQuery的时间旅行特性优化查询，避免全量关联的低效问题：

-- 统计最近24小时内被更新的行数
SELECT
  'UPDATE' AS operation_type,
  COUNT(*) AS count
FROM
  `your-project-id.your-dataset.your-table` t1
JOIN
  `your-project-id.your-dataset.your-table` FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR) t2
ON t1.id = t2.id
WHERE
  t1 != t2

UNION ALL

-- 统计最近24小时内被删除的行数
SELECT
  'DELETE' AS operation_type,
  COUNT(*) AS count
FROM
  `your-project-id.your-dataset.your-table` FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR) t2
LEFT JOIN
  `your-project-id.your-dataset.your-table` t1
ON t1.id = t2.id
WHERE
  t1.id IS NULL;

注意： #

• 仅适合小表或短时间段对比，大表仍可能存在性能瓶颈；
• 时间旅行默认保留7天，可延长至最多90天。

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