如何低成本验证两个仅行序可能不同的BigQuery表是否一致？

先明确你的疑问：OFFSET确实会触发全表扫描 #

BigQuery中使用LIMIT 1 OFFSET N时，数据库必须扫描到第N+1行才能返回结果，哪怕只取1行，也会遍历前面所有行，完全达不到避免全表扫描的目的。

高效的一致性校验方法（无需全表处理） #

1. 元数据+统计特征快速排查 #

先从元数据层面快速排除明显不一致的情况，这类查询仅访问元数据或做近似聚合，不会扫描全表数据：

• 对比表结构：通过INFORMATION_SCHEMA.COLUMNS检查列名、类型、顺序是否一致

  SELECT column_name, data_type, ordinal_position
  FROM `project.dataset.INFORMATION_SCHEMA.COLUMNS`
  WHERE table_name = 'table1'
  EXCEPT DISTINCT
  SELECT column_name, data_type, ordinal_position
  FROM `project.dataset.INFORMATION_SCHEMA.COLUMNS`
  WHERE table_name = 'table2'
  

  若结果非空，直接判定表结构不一致。
• 对比核心统计值：
  • 总行数：直接查询元数据的row_count（注意元数据可能有1-2小时延迟）

    SELECT
      (SELECT row_count FROM `project.dataset.__TABLES__` WHERE table_id = 'table1') AS t1_rows,
      (SELECT row_count FROM `project.dataset.__TABLES__` WHERE table_id = 'table2') AS t2_rows
    

  • 列级近似哈希：用APPROX_HASH_AGG生成基于采样的列哈希值，对比两张表结果

    SELECT
      APPROX_HASH_AGG(col1) AS col1_hash,
      APPROX_HASH_AGG(col2) AS col2_hash
      -- 按需添加其他列
    FROM `project.dataset.table1`
    EXCEPT DISTINCT
    SELECT
      APPROX_HASH_AGG(col1) AS col1_hash,
      APPROX_HASH_AGG(col2) AS col2_hash
      -- 按需添加其他列
    FROM `project.dataset.table2`
    

    结果为空则说明列的近似哈希一致，数据大概率匹配。

2. 随机采样对比（真正避免全表扫描） #

放弃OFFSET，改用以下两种随机采样方式，BigQuery会直接读取采样块或随机选行，无需遍历全表：

• 按百分比采样：用TABLESAMPLE SYSTEM指定采样比例，直接读取对应存储块

  SELECT * FROM `project.dataset.table1` TABLESAMPLE SYSTEM (0.1 PERCENT)
  EXCEPT DISTINCT
  SELECT * FROM `project.dataset.table2` TABLESAMPLE SYSTEM (0.1 PERCENT)
  

  结果为空则采样行全部匹配，结合统计特征可完成sanity check。
• 固定行数随机采样：用RAND()随机选取固定行数，BigQuery会优化采样逻辑

  SELECT * FROM `project.dataset.table1` ORDER BY RAND() LIMIT 1000
  EXCEPT DISTINCT
  SELECT * FROM `project.dataset.table2` ORDER BY RAND() LIMIT 1000
  

  选取1000行左右的样本对比，足够做快速校验。

3. 分块哈希全量校验（低成本100%确认） #

如果需要100%确认一致，又想降低扫描成本，可以按分块哈希对比：将表按主键或哈希值分块，对比每个块的哈希，发现不一致可提前终止（BigQuery虽可能扫完所有块，但聚合后对比的成本远低于全表逐行对比）

WITH t1_blocks AS (
  SELECT
    MOD(FARM_FINGERPRINT(CAST(primary_key AS STRING)), 100) AS block_id,
    TO_HEX(SHA256(STRING_AGG(TO_JSON_STRING(t), '' ORDER BY primary_key))) AS block_hash
  FROM `project.dataset.table1` t
  GROUP BY block_id
),
t2_blocks AS (
  SELECT
    MOD(FARM_FINGERPRINT(CAST(primary_key AS STRING)), 100) AS block_id,
    TO_HEX(SHA256(STRING_AGG(TO_JSON_STRING(t), '' ORDER BY primary_key))) AS block_hash
  FROM `project.dataset.table2` t
  GROUP BY block_id
)
SELECT * FROM t1_blocks EXCEPT DISTINCT SELECT * FROM t2_blocks

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