数据迁移中使用哈希函数做相等性校验

问题

数据迁移是个常见任务，其中最为棘手的是数据合并，即不存在的进行新增，存在的如果发生更新则进行修改（未更新则不修改），不存在的删除。这个任务在构建数据仓库ETL的过程中变得尤为重要。

办法

1. 如果数据源支持CDC（数据变更捕获）那解决办法显而易见。（本篇不做介绍）

2. 全量删除然后新增。（不好的办法，缺点是性能低下，要额外处理数据仓库维度代理键的生成问题，会产生大量不必要的事务日志）

3. 数据源中有last_update_date等类似字段。使用这个字段判断是否数据被修改。（实际情形很多数据源不具有这个字段，本篇也不做介绍）

4. 使用精确的逐个字段的比对方法。（本篇重点介绍，主要针对修改数据的同步）

举例子来说明

先创建一个Staging表，用于原始数据暂存

CREATETABLE[dbo].[Staging_Test](
[Id][int] IDENTITY(1,1)NOT,
[Code_Key][nvarchar](50),--作为原始数据源中的唯一键
[column2][nvarchar](50),
[column3][nvarchar](50),
CONSTRAINT[PK_Staging_Test]PRIMARYKEY CLUSTERED 
(
[Id]ASC
)WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS =ON, ALLOW_PAGE_LOCKS =ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF)ON[PRIMARY]
)ON[PRIMARY]
GO
//插入测试数据
INSERTINTO Staging_Test VALUES('1','a','b')
INSERTINTO Staging_Test VALUES('2','c','d')
INSERTINTO Staging_Test VALUES('3','e','f')

在创建一个真实表，用于存放同步以后的数据。（代码类似，不再赘述）

--先手动做第一次数据同步，因为此时数据表是空的。
INSERTINTO Test(Code_key,Column2,Column3)
select Code_key,Column2,Column3 from Staging_Test

然后我们修改了Staging_Test表中的数据来模拟数据发生了变更

--修改Code_key=2的column2数据
UPDATE Staging_Test
SET column2 ='updated'
where Code_Key =2

现在我们来同步Test表的数据，这里有些偷懒的办法就是只要Key是想等的整行都进行更新。但这种做法有个很大的问题，如果数据量成百上千万，这些数据每次同步时候可能只有几百条数据进行了更新，那下面的代码将产生不必要的海量事务日志，如果在一个并发读取量较大的场景下，会导致表锁，影响读取性能。

UPDATE Test
SET
column2 = s.column2,
column3 = s.column3
from Test as t
INNERJOIN Staging_Test as s on t.Code_Key=s.Code_key

负责任的程序员会进行一个小改进，在链接条件里面增加每一个字段的对比，如果有值发生了更改才执行更新。这避免了无脑更新，但是如果这个表的字段非常多，拼写这种字段对比的代码很无趣，同时也存在性能问题，这段代码的执行速度会显著下降。

UPDATE Test
SET
column2 = s.column2,
column3 = s.column3
from Test as t
INNERJOIN Staging_Test as s on t.Code_Key=s.Code_keyand(IS(t.column2,'')<> IS(s.column2,'')or IS(t.column3,'')<>IS(s.column3,''))

我们想继续改进这段代码，我们想到可以使用哈希函数来对多个字段值生成一段唯一的哈希码，用这个码来取代逐个的字段对比。因为哈希函数的特点就是任何一点更改就会导致生成的哈希码发生变化。当然我现在使用的SQL代码进行演示，所以也会使用SQL里面的哈希函数，以SQLServer为例，能够生成哈希码的函数有两种，CHECKSUM,HASHBYTES。 因为CHECKSUM在微软官方文档中写明不能保证其唯一性，所以在数据对比更新场景下，最好是不用。我们采用唯一性更高的HASHBYTES函数。

--首先需要为Test表增加一个checksum字段varbinary(32)类型，用于存放哈希值（略过）
--然后用所有字段的拼接值用HASHBYTES函数来生成这个哈希值。算法使用MD5（虽然现在已经淘汰，但作为数据校验场景依然可用的）
--更新Staging表的hash字段，可以在Staging表的数据加载完后，一次性进行更新
UPDATE Staging_Test
set[checksum]= HASHBYTES('MD5',CONCAT(Column2,column3))
--更新同步代码可以改为使用checksum字段,并且同步更新check_sum值
UPDATE Test
SET
column2 = s.column2,
column3 = s.column3,
checksum= HASHBYTES('MD5',CONCAT(s.Column2,s.column3))
from Test as t
INNERJOIN Staging_Test as s on t.Code_Key=s.Code_keyand t.checksum<>s.checksum
--插入代码就更简单了。直接判断目标表中是否存在Code_key,不存在责插入。
INSERTINTO Test(Code_Key,Column2,Column3,checksum)
SELECT s.Code_Key,s.Column2,s.Column3,s.checksum
FROM Staging_Test as s
LEFTJOIN Test as t on s.Code_key= t.code_key
where t.code_keyis

总结

• 使用多列的值组合来生成哈希值简化了列对比逻辑，同时提升了性能。因为HASHBYTES只能接受单个varchar，nvarchar值，所以拼接的无聊代码还是存在，有的人为了复用这个逻辑写了自定义函数调用表的元数据来拼接这段代码，最终使用动态SQL拼接方式来进行目标表的更新。

• CHECKSUM可以用在较低长度的字段值校验上，因为他容量小，产的的哈希值可能发生碰撞，即不同的值生成相同的哈希值。这样在要求精度较高的数据仓库比对时就不适用。HASHBYTES可以选择不同算法，MD2、MD4、MD5、SHA、SHA1 或 SHA2 哈希值。虽然MD5已经不推荐，但他的容量有128位（2的128次方），碰撞可能性满足数据校验应该是足够了。

• 数据对比、完整性校验只是哈希值使用的其中的一个场景。还可以为长的可变内容的值生成哈希索引，提高检索效率。