Azure SQL Data Warehouse十亿级数据插入时如何避免重复行？

针对你遇到的场景——200亿行规模的表、第三方数据源无天然唯一键、偶尔发送重复数据且无法提前判断是否已接收，我整理了几个兼顾性能与可行性的方案，毕竟在超大规模数据仓库里，避免全表扫描是核心原则，不然分分钟拖垮系统：

1. 生成哈希键作为逻辑唯一标识 #

既然没有现成的唯一键，我们可以给每行数据生成一个“指纹”来做逻辑主键，这是最常用的落地方案：

• 先给目标表新增一个hash_key字段，类型选binary(32)（用SHA2_256哈希算法，碰撞概率低到可以忽略）
• 插入前对新数据生成相同的哈希值，再通过等值查询过滤掉已存在的行，示例代码：

  -- 假设新数据存在临时 staging 表，目标表为 target_table
  INSERT INTO target_table (hash_key, col1, col2, ...)
  SELECT s.hash_key, s.col1, s.col2, ...
  FROM (
      SELECT 
          CONVERT(BINARY(32), HASHBYTES('SHA2_256', 
              CONCAT(ISNULL(col1, 'NULL_PLACEHOLDER'), '|', 
                     ISNULL(col2, 'NULL_PLACEHOLDER'), '|', ...)
          )) AS hash_key,
          col1, col2, ...
      FROM staging_table
  ) s
  LEFT JOIN target_table t ON s.hash_key = t.hash_key
  WHERE t.hash_key IS NULL;
  

• 关键优化：给hash_key建哈希索引或者非聚集列存储索引，这样查找重复的速度会非常快，完全不用扫全表。另外要注意处理NULL值，不然CONCAT会直接返回NULL，导致哈希值失效。

2. 临时表+EXCEPT批量去重 #

如果担心哈希碰撞（虽然概率极低），可以用EXCEPT操作符直接比对全字段：

• 先把新数据导入列存储格式的临时staging表（列存储扫描速度远快于行存储）
• 用EXCEPT筛选出目标表中没有的行再插入：

  INSERT INTO target_table (col1, col2, ...)
  SELECT col1, col2, ...
  FROM staging_table
  EXCEPT
  SELECT col1, col2, ...
  FROM target_table;
  

• 注意：这个方案的性能不如哈希键，更适合小批次数据，或者字段组合生成哈希键有困难的场景。记得给staging表和目标表的字段创建统计信息，让查询优化器生成最优计划。

3. 用Azure Synapse数据流做可视化去重 #

如果你的数据管道是基于Azure Synapse的，直接用数据流的去重转换更省心：

• 把第三方数据源接入数据流，在转换步骤中选择所有字段作为去重键，设置保留策略（比如保留最早或最晚到达的行）
• 数据流会在分布式环境中高效完成去重，再写入目标表，不用写复杂SQL，还能自动化调度，适合ETL流程化的场景。

4. 批次标识+增量加载（依赖数据源配合） #

如果能说服第三方给每个发送的批次加一个唯一批次ID，那这就是性能最优的方案：

• 建一个processed_batches表，专门记录已经处理过的批次ID
• 每次接收数据时，先检查批次ID是否在processed_batches里，不在就处理，处理完把批次ID插入该表
• 这个方案完全跳过数据比对，速度最快，但前提是数据源愿意配合改造。

额外优化建议 #

• 列存储索引必开：200亿行的表一定要用聚集列存储索引，不管插入还是查询，性能都会提升几个量级
• 分区表优化：如果数据有时间或其他适合分区的维度，把目标表分区，这样比对重复时只需要扫描对应分区，不用碰全表
• 资源类调整：执行插入前，把SQL DW的资源类调到合适级别（比如xlargerc），确保有足够算力处理大批次数据，避免超时

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