[[438896]]

本文轉(zhuǎn)載自微信公眾號(hào)「有關(guān)SQL」，作者Lenis 。轉(zhuǎn)載本文請(qǐng)聯(lián)系有關(guān)SQL公眾號(hào)。

前幾天，我們 SQL 大數(shù)據(jù)玩家微信群里，有朋友發(fā)布了一條數(shù)據(jù)校驗(yàn)的題目。覺(jué)得有趣，也有必要總結(jié)下，所以檢索了些論文，結(jié)合平時(shí)工作中的使用，綜合起來(lái)講講，看看自己能不能把這方面講清楚。

數(shù)據(jù)校驗(yàn)，常用在“數(shù)據(jù)搬運(yùn)”的場(chǎng)景中。

比如，把數(shù)據(jù)從源頭抽取到下游，抽取的過(guò)程中，可能還做了一系列的轉(zhuǎn)換，沒(méi)錯(cuò)這就是常說(shuō)的ETL. 細(xì)心的小伙伴，一定會(huì)做好數(shù)據(jù)校驗(yàn)工作，即在源數(shù)據(jù)留下“指紋”。數(shù)據(jù)到了下游，對(duì)比下“指紋”，就能知道，有沒(méi)有漏，有沒(méi)有丟 ，或者有沒(méi)有變異。

再比如，兩個(gè)組同時(shí)抽取一個(gè)數(shù)據(jù)源頭做分析，在最終結(jié)果上，需要對(duì)比一致性，這也是數(shù)據(jù)校驗(yàn)。之前待過(guò)一家公司，財(cái)務(wù)部和營(yíng)運(yùn)部拿的都是ERP 數(shù)據(jù)，一個(gè)組用 Python 算，一個(gè)組用 SQL, 最后兩組算出來(lái)的利潤(rùn)和成本完全不一致。月結(jié)會(huì)上，這兩組人就經(jīng)常因?yàn)閿?shù)據(jù)問(wèn)題而吵架。

再舉個(gè)例子，作為程序員，經(jīng)常去 apache 下載一些開(kāi)源軟件，比如 spark。

下載頁(yè)面會(huì)有個(gè) verify 的提示，意思也就是做下軟件的數(shù)據(jù)校驗(yàn)，防止網(wǎng)絡(luò)丟包，或者文件損壞，被調(diào)包等等現(xiàn)象發(fā)生。

要解決上面這些數(shù)據(jù)校驗(yàn)需求，我有三個(gè)方法：

• 第一，集合對(duì)比
• 第二，哈希
• 第三，隨幀校驗(yàn)碼

集合對(duì)比

這是小數(shù)據(jù)場(chǎng)景最合適的利刃。

舉個(gè)例子，在數(shù)據(jù)倉(cāng)庫(kù)中，用戶表一定不陌生。它的數(shù)量級(jí)不會(huì)很大，通常上萬(wàn)或者十萬(wàn)左右。對(duì)它做數(shù)據(jù)校驗(yàn)時(shí)，使用SQL的 Except 就可以了。

假設(shè)，有兩張用戶表，一張來(lái)自源數(shù)據(jù)庫(kù)，user_source;一張是數(shù)據(jù)倉(cāng)庫(kù)表 user_target. 怎么判斷兩表的數(shù)據(jù)差異呢，最簡(jiǎn)單的方法用 except。

SELECT USER_ID,USER_NAME  
FROM user_source  
 
EXCEPT  
 
SELECT USER_ID,USER_NAME  
FROM user_target 

我想，只要SQL 入門(mén)的朋友，都能寫(xiě)出來(lái)。但用在哪里，就考驗(yàn)平時(shí)對(duì)場(chǎng)景的理解了。這其中的細(xì)節(jié)，要躺平的坑，就不多說(shuō)了，朋友們平時(shí)自己多積累了。

哈希

第一種方法，簡(jiǎn)單粗暴，見(jiàn)效很快。針對(duì)小數(shù)據(jù)量級(jí)，非常高效。但要處理起百萬(wàn)級(jí)數(shù)據(jù)，就會(huì)差點(diǎn)意思。

接下來(lái)要介紹的算法，更高效，它就是哈希。

數(shù)據(jù)庫(kù)廠商都實(shí)現(xiàn)了自己的哈希(Hash)函數(shù)，通過(guò)查詢文檔，這不難掌握。比如：

SQL Server 有 Checksum, Binary_Checksum, HashBytes;

Oracle 有 Ora_Hash.

以下是 SQL Server T-SQL 的 checksum 用例

-- T-SQL Demo 
SELECT user_id 
  ,  user_full_name  
  ,  checksum(user_id,user_full_name) AS row_checksum 
 FROM dbo.user_source 

通過(guò) checksum 計(jì)算出來(lái)的 row_checksum， 它是數(shù)值型。范圍在正負(fù) 21億左右，也就是 Integer 的范圍值。超過(guò)這個(gè)數(shù)，一定會(huì)產(chǎn)生重復(fù)哈希值，這是它唯一的缺陷。

它的缺陷先放一邊，來(lái)看它的優(yōu)點(diǎn)。兩個(gè)數(shù)據(jù)集比較時(shí)，只要一列，就替代了原先對(duì)比兩列的操作。

由于數(shù)值型的存儲(chǔ)空間小，一個(gè) Integer 只需要 4個(gè)字節(jié)，因此作為索引也非常高效。這個(gè)例子中，我們更進(jìn)一步，在兩張表上，加入以 checksum 結(jié)果為字段的索引。

ALTER TABLE dbo.user_source ADD row_checksum INT 
 
CREATE INDEX IDX_ROW_CHECKSUM ON dbo.user_source(row_checksum) 
 
UPDATE SRC  
  SET row_checksum = checksum(user_id,user_full_name) 
FROM dbo.user_source SRC 
 
ALTER TABLE dbo.user_target ADD row_checksum INT 
 
CREATE INDEX IDX_ROW_CHECKSUM_TG ON dbo.user_target(row_checksum) 
 
UPDATE TG 
  SET row_checksum = checksum(user_id,user_full_name) 
FROM dbo.user_target TG 

如此，比較兩表的差異，變得更簡(jiǎn)單，更快捷。

SELECT row_checksum 
FROM dbo.user_source   
EXCEPT  
SELECT row_checksum 
FROM dbo.user_target 

隨幀校驗(yàn)碼

這類(lèi)方法最原始。在通信領(lǐng)域中，經(jīng)常會(huì)遇到它。在兩個(gè)終端傳輸數(shù)據(jù)時(shí)，人們?cè)缇鸵庾R(shí)到通信是不可靠的，因此發(fā)明了很多方法，來(lái)校驗(yàn)數(shù)據(jù)的一致性。

CRC 就是其中一種，隨著時(shí)間的沉淀，它越來(lái)越多的被硬件級(jí)實(shí)現(xiàn)，或者在協(xié)議級(jí)集成。但是軟件上，依然可以用來(lái)做檢驗(yàn)數(shù)據(jù)的操作。

CRC: Cyclic Redundancy Check 循環(huán)冗余校驗(yàn)

https://en.wikipedia.org/wiki/Cyclic_redundancy_check

CRC 校驗(yàn)的原理，簡(jiǎn)單畫(huà)下，是這樣的：

發(fā)送端，在要傳輸?shù)摹疚谋緮?shù)據(jù)】幀上，利用 CRC 函數(shù)，算出【CRC校驗(yàn)碼】，并把這串?dāng)?shù)字附在【文本數(shù)據(jù)】幀上。

數(shù)據(jù)接收方，基于同樣的 CRC 函數(shù)，輸入【文本數(shù)據(jù)】，生成新的校驗(yàn)數(shù)字，和附帶的 CRC 校驗(yàn)碼，做對(duì)比。若有差異，說(shuō)明數(shù)據(jù)有變動(dòng)。

當(dāng)然，原理上，CRC 并不簡(jiǎn)單。需要一系列多項(xiàng)式運(yùn)算，在這里不展開(kāi)了。

從 stackoverflow 看到，有人用 SQL 實(shí)現(xiàn)了 CRC，感受下：

Declare @input as varchar(1000)  
Set @input='This is the CRC test' 
Declare @CRCtable as varchar(3080)   --Location of Edit 
Declare @Index as int 
Declare @crc as BIGINT 
Declare @length as INT 
Declare @i as INT 
Declare @tblval as BIGINT 
Declare @CTindex as int 
Declare @ans as varchar(25)  
 
Set @CRCtable='0000000000, 1996959894, 3993919788, 2567524794, 0124634137, 1886057615, 3915621685, 2657392035, 0249268274, 2044508324, 3772115230, 2547177864, 0162941995, 2125561021, 3887607047, 2428444049, 0498536548, 1789927666, 4089016648, 2227061214, 0450548861, 1843258603, 4107580753, 2211677639, 0325883990, 1684777152, 4251122042, 2321926636, 0335633487, 1661365465, 4195302755, 2366115317, 0997073096, 1281953886, 3579855332, 2724688242, 1006888145, 1258607687, 3524101629, 2768942443, 0901097722, 1119000684, 3686517206, 2898065728, 0853044451, 1172266101, 3705015759, 2882616665, 0651767980, 1373503546, 3369554304, 3218104598, 0565507253, 1454621731, 3485111705, 3099436303, 0671266974, 1594198024, 3322730930, 2970347812, 0795835527, 1483230225, 3244367275, 3060149565, 1994146192, 0031158534, 2563907772, 4023717930, 1907459465, 0112637215, 2680153253, 3904427059, 2013776290, 0251722036, 2517215374, 3775830040, 2137656763, 0141376813, 2439277719, 3865271297, 1802195444, 0476864866, 2238001368, 4066508878, 1812370925, 0453092731, 2181625025, 4111451223, 1706088902, 0314042704, 2344532202, 4240017532, 1658658271, 0366619977, 2362670323, 4224994405, 1303535960, 0984961486, 2747007092, 3569037538, 1256170817, 1037604311, 2765210733, 3554079995, 1131014506, 0879679996, 2909243462, 3663771856, 1141124467, 0855842277, 2852801631, 3708648649, 1342533948, 0654459306, 3188396048, 3373015174, 1466479909, 0544179635, 3110523913, 3462522015, 1591671054, 0702138776, 2966460450, 3352799412, 1504918807, 0783551873, 3082640443, 3233442989, 3988292384, 2596254646, 0062317068, 1957810842, 3939845945, 2647816111, 0081470997, 1943803523, 3814918930, 2489596804, 0225274430, 2053790376, 3826175755, 2466906013, 0167816743, 2097651377, 4027552580, 2265490386, 0503444072, 1762050814, 4150417245, 2154129355, 0426522225, 1852507879, 4275313526, 2312317920, 0282753626, 1742555852, 4189708143, 2394877945, 0397917763, 1622183637, 3604390888, 2714866558, 0953729732, 1340076626, 3518719985, 2797360999, 1068828381, 1219638859, 3624741850, 2936675148, 0906185462, 1090812512, 3747672003, 2825379669, 0829329135, 1181335161, 3412177804, 3160834842, 0628085408, 1382605366, 3423369109, 3138078467, 0570562233, 1426400815, 3317316542, 2998733608, 0733239954, 1555261956, 3268935591, 3050360625, 0752459403, 1541320221, 2607071920, 3965973030, 1969922972, 0040735498, 2617837225, 3943577151, 1913087877, 0083908371, 2512341634, 3803740692, 2075208622, 0213261112, 2463272603, 3855990285, 2094854071, 0198958881, 2262029012, 4057260610, 1759359992, 0534414190, 2176718541, 4139329115, 1873836001, 0414664567, 2282248934, 4279200368, 1711684554, 0285281116, 2405801727, 4167216745, 1634467795, 0376229701, 2685067896, 3608007406, 1308918612, 0956543938, 2808555105, 3495958263, 1231636301, 1047427035, 2932959818, 3654703836, 1088359270, 0936918000, 2847714899, 3736837829, 1202900863, 0817233897, 3183342108, 3401237130, 1404277552, 0615818150, 3134207493, 3453421203, 1423857449, 0601450431, 3009837614, 3294710456, 1567103746, 0711928724, 3020668471, 3272380065, 1510334235, 0755167117, ' 
Set @crc = 0xFFFFFFFF 
Set @length = LEN(@input)   
Set @i = 1 
 
While @i <= @length 
Begin 
Set @index = ((@crc & 0xff) ^ ASCII(SUBSTRING(@input, @i, 1)))         
Set @CTindex = (@index * 12) + 1 
Set @ans=substring(@CRCtable,@CTindex,10  )   
Set @tblval = convert(bigint,@ans)  
Set @crc = (@crc / 256) ^ @tblval      
Set @i = @i + 1 
End 
Set @crc = ~@crc   
 
SELECT @crc as CRC32, CONVERT(VARBINARY(4), @crc) as CRC32Hex 

摘自：https://stackoverflow.com/questions/331157/does-sql-server-checksum-calculate-a-crc-if-not-how-can-i-get-ms-sql-to-calcula

這么做，當(dāng)然是可以的，但代價(jià)偏高。

前面講 checksum 時(shí)，提到它的缺陷。在超大的數(shù)據(jù)量下，它計(jì)算出來(lái)的哈希值，會(huì)有重合。這，是算法界常遇到的問(wèn)題。即，不同的值，可能有相同的哈希值。CRC 也逃不掉。

因此，下面介紹防撞率更高的一種方法，MD5。

MD5: Message-Digest Algorithm

https://baike.baidu.com/item/MD5/212708?fr=aladdin

下面是一個(gè)例子，分別用 CRC32/MD5 對(duì)天池競(jìng)賽公開(kāi)的數(shù)據(jù)集，做了比較。兩者都可以完美地識(shí)別出相同的記錄數(shù)，采用同樣的參數(shù)格式，對(duì)需要進(jìn)行對(duì)比的列，計(jì)算出校驗(yàn)碼。

use Demo  
 
select 
  user_id, 
  item_id, 
  behavior_type, 
  item_category, 
md5(concat(user_id, item_id, behavior_type, item_category)) as row_md5, 
crc32(concat(user_id, item_id, behavior_type, item_category)) as row_crc32 
from 
  tianchi_mobile__user tmu 
order by 
  user_id asc , 
  item_id asc 
limit 10 ; 

CRC32 計(jì)算出的是整數(shù)型校驗(yàn)碼，而MD5計(jì)算出來(lái)的是十六進(jìn)制的字符串。由此可見(jiàn)，MD5 能容錯(cuò)的數(shù)據(jù)范圍更大，防撞率更高。

無(wú)論是通過(guò) CRC 還是 MD5算法，總有概率上產(chǎn)出兩個(gè)相同的值。因此我們并不能僅僅憑借最后兩個(gè)輸出值相等，就判定兩個(gè)輸入值就一定相等。但反推是成立的，只要兩個(gè)輸出值不等，它們的輸入值就一定不等。

SQL Server 也有這樣的解決方案，比如 hashbytes. 由于 checksum返回的是 int 數(shù)據(jù)類(lèi)型，因此在處理小數(shù)據(jù)量時(shí)，速度會(huì)有優(yōu)勢(shì)。但防撞率不高。

如果數(shù)據(jù)量極大，超過(guò)22億，那么很難逃過(guò)重合的命運(yùn)。此時(shí)SQL Server 提供了 hashbytes 來(lái)生成重合率更小的 hash 值， 除了 MD5外，還能生成 SHA128, SHA512 這樣支持更寬范圍數(shù)據(jù)的標(biāo)準(zhǔn)。

花絮

我很想講講，最近寫(xiě)文章的心得。就像這篇文章一樣，寫(xiě)作尤其寫(xiě)一個(gè)不熟悉的領(lǐng)域，其實(shí)要花很大的功夫。

從開(kāi)始布局思考，搜集論文資料，看懂 CRC/MD5/SHA 的原理，到最終構(gòu)思文章結(jié)構(gòu)，用自認(rèn)為還算通俗的文字寫(xiě)出來(lái)。期間對(duì)心理的考驗(yàn)特別大。這就像我的對(duì)面是個(gè)大怪獸，除了幾十倍大于我，我還找不出任何攻擊點(diǎn)。

我唯一能依靠的，就是死摳細(xì)節(jié)，寫(xiě)下來(lái)一個(gè)個(gè)遇到的小問(wèn)題，翻閱各種手頭資料，比如微信讀書(shū)，極客時(shí)間，還有知網(wǎng)論文等，去求證，去看別人寫(xiě)的例子。以求能完美回答我自己提出的問(wèn)題。

最后還要修改文章結(jié)構(gòu)，力求文字表述清爽，言簡(jiǎn)意賅，流暢閱讀。 

在最終出篇前，加梗，加料，反復(fù)修改。多少次，面對(duì)精心寫(xiě)完的段落，也是哽咽地刪掉。不得不說(shuō)，寫(xiě)作就像磨咖啡豆一樣，使勁碾壓自己，才能綻放香氣。最終成杯的那一刻，苦盡甘來(lái)!一切都是值得的