Google BigQuery如何统计数据集内所有表的每日记录数

你当前单表统计每日行数的逻辑可正常使用，针对数据集下批量表的统计需求，按不同场景可选择以下方案：

针对存在time字段的表批量统计 #

不需要逐个编写单表查询语句，直接用动态SQL+通配符表能力一次性完成统计，逻辑如下：

1. 通过系统元数据视图自动筛选出所有包含time字段的表，避免查询不存在该字段的表报错
2. 用EXECUTE IMMEDIATE拼接通配符查询，一次性返回所有符合条件的表的每日行数统计结果
   完整代码如下：

EXECUTE IMMEDIATE FORMAT("""
SELECT
  _TABLE_SUFFIX AS table_name,
  CAST(time AS DATE) AS day,
  COUNT(*) AS row_per_day
FROM `dataset_id.*`
WHERE _TABLE_SUFFIX IN (%s)
GROUP BY 1,2
ORDER BY table_name, day
""", (
  SELECT STRING_AGG("'" || table_name || "'")
  FROM `dataset_id.INFORMATION_SCHEMA.COLUMNS`
  WHERE column_name = 'time'
));

• 说明：该查询会自动跳过无time字段的表，统计逻辑和你当前单表查询完全一致，不需要手动维护表清单。

针对不存在time字段的表的统计方案 #

没有行级时间字段的表，原生不存储历史行的写入时间，__TABLES__元数据仅保留当前最新的总行数，无法直接回溯历史每日的行数，可根据需求选择以下两种落地方式：

• 需支持历史回溯+未来统计：开启BigQuery审计日志
  开启数据访问审计日志后，系统会记录所有对表的写入、修改操作，可以基于日志解析每次写入的行数，回溯历史任意时间点的表总行数，也可满足后续每日统计需求。
• 仅需未来每日统计：配置定时快照任务
  从当前节点开始配置每日定时运行的SQL任务，留存所有表的每日行数快照，后续直接查询快照表即可得到每日统计结果，相关SQL参考：

  -- 首次运行创建快照表
  CREATE TABLE IF NOT EXISTS `dataset_id.daily_table_row_snapshot` (
    stat_date DATE,
    table_name STRING,
    total_row_count INT64
  );
  
  -- 每日定时调度运行，写入当日快照
  INSERT INTO `dataset_id.daily_table_row_snapshot`
  SELECT
    CURRENT_DATE() AS stat_date,
    table_id AS table_name,
    row_count AS total_row_count
  FROM `dataset_id.__TABLES__`;
  

注意：该方案仅能统计任务上线后的每日行数，无法回溯任务上线前的历史数据。

分区表高效统计方案 #

如果你的表均为按天分区的分区表（分区字段为time或默认_PARTITIONTIME），不需要扫描全表数据，直接查询分区元数据即可拿到每日分区行数，统计效率更高、查询成本更低：

SELECT
  table_name,
  DATE(PARSE_TIMESTAMP('%Y%m%d', partition_id)) AS day,
  total_rows AS row_per_day
FROM `dataset_id.INFORMATION_SCHEMA.PARTITIONS`
WHERE partition_id NOT IN ('__NULL__', '__UNPARTITIONED__')

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