DeltaLake merge匹配列含null值时重复插入行如何处理

问题根因 #

该问题本质是SQL标准中=运算符对NULL值的比较规则导致的：任意值和NULL用=比较都会返回UNKNOWN，不会被判定为匹配条件成立，所以当desk_mirror_name列两边都是NULL时，原有的merge匹配条件失效，触发whenNotMatched的插入逻辑，最终出现重复数据。

解决方案 #

方案1：使用Null安全相等运算符（最推荐）

Spark SQL提供了专为NULL值比较设计的<=>（空安全相等）运算符，当两边值相等或两边均为NULL时返回true，完全符合匹配需求，只需要修改merge语句的匹配条件即可：

DeltaTable.forPath(sparkSession, cleanDataPath)
          .as("target")
          .merge(df.as("source"), 
            // 仅将允许为NULL的列的=替换为<=>
            "target.desk_mirror_name <=> source.desk_mirror_name AND target.price = source.price AND target.valuationdate = source.valuationdate AND target.valuationversion = source.valuationversion")
          .whenMatched()
          // 可选优化：只更新需要修改的字段，比updateAll效率更高也更安全
          .updateExpr(java.util.Collections.singletonMap("valuationtag", "source.valuationtag"))
          .whenNotMatched()
          .insertAll()
          .execute();

如果使用Spark 3.1及以上版本，也可以用可读性更强的标准SQL语法IS NOT DISTINCT FROM替换<=>，效果完全一致：

target.desk_mirror_name IS NOT DISTINCT FROM source.desk_mirror_name

方案2：使用coalesce替换NULL为占位符

如果需要兼容旧版本Spark，也可以用coalesce函数将NULL值替换为业务场景中不可能出现的占位值后再比较：

// 匹配条件修改为
"coalesce(target.desk_mirror_name, '__INTERNAL_NULL_PLACEHOLDER__') = coalesce(source.desk_mirror_name, '__INTERNAL_NULL_PLACEHOLDER__') AND target.price = source.price AND target.valuationdate = source.valuationdate AND target.valuationversion = source.valuationversion"

注意：占位符必须是对应字段业务逻辑中永远不会出现的取值，否则会导致非预期的匹配错误。

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