如何在BigQuery中通用比较含STRUCT类型的两张表？

刚好碰到过类似的需求！BigQuery里直接用EXCEPT对比含STRUCT的表确实会报错，因为集合操作不支持直接比较复杂类型。不过我们可以换个思路，把整行转换成可比较的格式来解决这个问题，而且完全不需要手动写列名，通用所有表对，还不受行顺序影响。

1. 判断两张表是否完全相等 #

如果只需要知道两表是否一致，不需要具体差异，可以用行哈希聚合的方式，既高效又简洁：

方案：基于BIT_XOR聚合哈希值 #

BIT_XOR操作的特性是不受行顺序影响，重复行的哈希会相互抵消，再结合总行数对比，就能准确判断两表是否一致：

SELECT
  IF(
    -- 对比两表的聚合哈希值（BIT_XOR不受行顺序影响）
    (SELECT BIT_XOR(SHA256(TO_JSON_STRING(*))) FROM `your-project.your-dataset.table1`) =
    (SELECT BIT_XOR(SHA256(TO_JSON_STRING(*))) FROM `your-project.your-dataset.table2`)
    AND
    -- 先对比总行数，快速排除明显不等的情况
    (SELECT COUNT(*) FROM `your-project.your-dataset.table1`) =
    (SELECT COUNT(*) FROM `your-project.your-dataset.table2`),
    '✅ Tables are identical',
    '❌ Tables are different'
  ) AS comparison_result;

这里用SHA256代替BigQuery内置的HASH函数，是为了降低哈希冲突的概率；如果对性能要求更高，也可以换回HASH，实际冲突概率极低。

2. 找出具体的差异行 #

如果需要看到哪些行不一样，可以把每行转换成JSON字符串，再用EXCEPT ALL来对比，最后把JSON转回原始结构方便查看：

WITH table1_normalized AS (
  SELECT TO_JSON_STRING(*) AS row_json FROM `your-project.your-dataset.table1`
),
table2_normalized AS (
  SELECT TO_JSON_STRING(*) AS row_json FROM `your-project.your-dataset.table2`
)

-- 找出仅存在于Table1的行
SELECT
  'Only in Table1' AS difference_type,
  JSON_PARSE(row_json) AS row_content
FROM table1_normalized
EXCEPT ALL
SELECT
  'Only in Table1' AS difference_type,
  JSON_PARSE(row_json) AS row_content
FROM table2_normalized

UNION ALL

-- 找出仅存在于Table2的行
SELECT
  'Only in Table2' AS difference_type,
  JSON_PARSE(row_json) AS row_content
FROM table2_normalized
EXCEPT ALL
SELECT
  'Only in Table2' AS difference_type,
  JSON_PARSE(row_json) AS row_content
FROM table1_normalized;

这个方法的核心是TO_JSON_STRING(*)：它会把整行（包括嵌套STRUCT、ARRAY等复杂类型）转换成标准JSON字符串，BigQuery可以对这些字符串执行集合操作；最后用JSON_PARSE把字符串转回原始结构，让差异行的内容清晰可读。

关键注意事项 #

• 表结构一致性：这个方法要求两张表的结构完全匹配（字段名、类型、顺序都一致），否则TO_JSON_STRING生成的字符串会不同，导致误判。如果表结构字段顺序不同但内容一致，可以先通过SELECT显式指定字段顺序来对齐。
• 重复行处理：用EXCEPT ALL而不是EXCEPT DISTINCT可以保留重复行的差异（比如Table1有3次某行，Table2有2次，会被识别为差异）；如果只关心行内容是否存在而不管重复次数，换成EXCEPT DISTINCT即可。
• 性能优化：对于超大表，可以考虑先分区或过滤数据再对比，避免全表扫描的开销。

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