1.問題現(xiàn)象

隨著信創(chuàng)的持續(xù)推進(jìn)，我們對(duì)接的數(shù)據(jù)庫類型越來越多，而不同的數(shù)據(jù)庫，對(duì)空值和空串的處理方式不同，所以在跨數(shù)據(jù)庫類型進(jìn)行數(shù)據(jù)同步時(shí)，往往會(huì)因?yàn)檫@些差異而報(bào)錯(cuò)，比如某客戶的信創(chuàng)環(huán)境，在使用 datax 推送數(shù)據(jù)到 oceanbase（oracle 模式）時(shí)，就遇到了如下報(bào)錯(cuò)：ORA-01400: cannot insert Null into ‘(columnA)’.

2.問題原因

該場(chǎng)景的數(shù)據(jù)鏈路如下：上游微服務(wù)A 將數(shù)據(jù)落地都數(shù)據(jù)庫A中，該數(shù)據(jù)庫A 在非信創(chuàng)場(chǎng)景是 Oracle，在信創(chuàng)場(chǎng)景是 GoldenDB，然后下游某數(shù)據(jù)分析平臺(tái)B 使用 datax 將數(shù)據(jù)庫A中的數(shù)據(jù)采集到大數(shù)據(jù)HIVE并進(jìn)行加工處理，最后再使用 datax 將大數(shù)據(jù) HIVE 中的數(shù)據(jù)同步到下游的數(shù)據(jù)庫B，該數(shù)據(jù)庫B使用的是 oceanbase（oracle 模式）。 整個(gè)鏈路上，有幾個(gè)技術(shù)要點(diǎn)如下：

• 不同數(shù)據(jù)庫對(duì)空串和空值的處理方式不同，有的認(rèn)為空串就是空值，有的則認(rèn)為空串和空值不同；
• ORACLE派系，比如oracle/ob-oracle/tdh-inceptor，認(rèn)為空串就是空值：當(dāng)用戶通過SQL插入空值或空串時(shí)，數(shù)據(jù)庫計(jì)算層會(huì)將空串和空值都視為空值，最終保存到存儲(chǔ)層的文件系統(tǒng)中的內(nèi)容，空值和空串是相同的；
• ORALCE外的其它派系，比如mysql/pg/apache hive，認(rèn)為空串和空值不同：當(dāng)用戶通過SQL插入空值或空串時(shí)，數(shù)據(jù)庫計(jì)算層不會(huì)將空串視為空值，最終保存在存儲(chǔ)層的文件系統(tǒng)中的內(nèi)容，空值和空串是不同的；
• 業(yè)務(wù)上聲明表結(jié)構(gòu)時(shí)，限制了所有字段包括該 columnA 字段非空(null)；
• 大多數(shù)數(shù)據(jù)庫都有自己的空值處理函數(shù)，該函數(shù)在 ORACLE中是 nvl(), mysql中是 ifnull(),postgresql中是coalesce();
• 對(duì)于認(rèn)為空串就是空值的ORACLE派系，其自身的空值處理函數(shù)nvl/ifnull/coalesce，可以有效地將空串和空值，都轉(zhuǎn)換為指定的值比如空格SPACE;
• 對(duì)于認(rèn)為空串和空值不同的ORACLE外的其它派系，其自身的空值處理函數(shù)nvl/ifnull/coalesce，只能將空值轉(zhuǎn)換為指定的值比如空格SPACE，而無法有效地對(duì)空串進(jìn)行轉(zhuǎn)換；

在信創(chuàng)環(huán)境下,微服務(wù)A使用的數(shù)據(jù)庫A是 goldendb，而數(shù)據(jù)分析平臺(tái)B使用的數(shù)據(jù)庫B是 oceanbase（oracle 模式），此時(shí)會(huì)出現(xiàn)上述問題，其原因是：

• 微服務(wù)A將數(shù)據(jù)寫入數(shù)據(jù)庫A GoldenDB 時(shí)，雖然使用了數(shù)據(jù)庫空值處理函數(shù)ifnull(), 但由于 goldendb認(rèn)為空串（””）不是空值（null），所以實(shí)際插入到 goldendb 中的 columnA 字段值也可能包含空串；
• 此后數(shù)據(jù)分析平臺(tái)B 經(jīng)過 datax 采集和 HIVE SQL 處理后，會(huì)嘗試通過 datax 將columnA字段的空串（””）同步到數(shù)據(jù)庫B即 ob-oracle, 而此時(shí)由于 ob-oralce 認(rèn)為空串（””）就是空值(null)，又由于業(yè)務(wù)中聲明表結(jié)構(gòu)時(shí)限制了該columnA 字段非空(null)，所以此時(shí) ob-oracle數(shù)據(jù)庫就報(bào)錯(cuò)了； 非信創(chuàng)環(huán)境下數(shù)據(jù)庫A 使用 oracle，不會(huì)出現(xiàn)上述問題，其原因是：
• 微服務(wù)A 將數(shù)據(jù)寫入數(shù)據(jù)庫A 即 ORACLE 時(shí),使用了數(shù)據(jù)庫空值處理函數(shù)nvl(), 由于oracle 認(rèn)為空串（””）就是空值(null)，所以當(dāng) columnA 字段包含空串時(shí)，實(shí)際插入到 oracle 中的 columnA 字段值，也被轉(zhuǎn)換為了空格SPACE，此后數(shù)據(jù)分析平臺(tái)B 經(jīng)過datax采集和HIVE SQL 處理后，columnA 字段值仍是空格SPACE，所以最終通過 datax同步到數(shù)據(jù)分析平臺(tái)B庫即 ob-oracle 時(shí)，由于字段columnA 的值是空格SPACE，而不是空串（””）也不是空值(null)，ob-oracle自然也就不會(huì)報(bào)錯(cuò)了；
• 概括起來，該問題的根本原因是：不同數(shù)據(jù)庫對(duì)空串和空值的處理方式不同，ORACLE派系認(rèn)為空串就是空值，非ORACLE派系認(rèn)為空串不是空值，當(dāng)數(shù)據(jù)鏈路上混合使用不同數(shù)據(jù)庫時(shí)，單純通過數(shù)據(jù)庫自身的空值處理函數(shù) nvl/ifnull/coalesce，不足以有效將空串也處理為空格SPACE，當(dāng)業(yè)務(wù)聲明表結(jié)構(gòu)時(shí)限制字段非空(null)，如果上游使用的是非ORACLE派系的數(shù)據(jù)庫且包含空串，此時(shí)將空串插入到下游ORACLE派系的數(shù)據(jù)庫時(shí)，就會(huì)報(bào)上述 “ORA-01400: cannot insert Null“ 的錯(cuò)誤。

3.解決方案：數(shù)據(jù)分析應(yīng)用側(cè)的改動(dòng)或上游微服務(wù)側(cè)的改動(dòng)

• 數(shù)據(jù)分析平臺(tái)B 使用 datax 將數(shù)據(jù)庫A中的數(shù)據(jù)采集到大數(shù)據(jù)HIVE后，在HIVE中使用SQL進(jìn)行加工處理時(shí)，僅僅使用數(shù)據(jù)庫自身的空值處理函數(shù) nvl/ifnull/coalesce 對(duì)空值進(jìn)行處理（HIVE 中的空值處理函數(shù)是 nvl()），不能有效將空串也處理為空格 SPACE，所以可以通過 length() 函數(shù)來判斷字段內(nèi)容是否為空串，并將空串也轉(zhuǎn)換為空格SPACE，也就是說，數(shù)據(jù)分析平臺(tái)B 在 HIVE SQL 中使用 nvl()和length()進(jìn)行雙重保護(hù)，將上游的空值和空串都處理為空格了，此后插入下游ORACLE派系數(shù)據(jù)庫時(shí)，就沒有問題了；
• 該問題更優(yōu)的解決方案應(yīng)該是，在數(shù)據(jù)鏈路的上層，由微服務(wù)A進(jìn)行處理，避免空串向下游流轉(zhuǎn)，從而在源頭側(cè)，從根本上避免空串帶來的問題，其具體實(shí)現(xiàn)方式，跟 HIVE SQL 類似，使用數(shù)據(jù)庫自身的空值處理函數(shù) nvl/ifnull/coalesce，結(jié)合 length() 函數(shù)，對(duì)空值和空串進(jìn)行雙重保護(hù)，將上游的空值和空串都處理為其它值比如 SPACE 空格,此后插入下游ORACLE派系數(shù)據(jù)庫時(shí)，就沒有問題了；

4.技術(shù)背景

• 不同數(shù)據(jù)庫對(duì)空串和空值的處理方式不同，有的認(rèn)為空串就是空值，有的則認(rèn)為空串和空值不同；
• ORACLE派系，比如oracle/ob-oracle/tdh-inceptor，認(rèn)為空串就是空值：當(dāng)用戶通過SQL插入空值或空串時(shí)，數(shù)據(jù)庫計(jì)算層會(huì)將空串和空值都視為空值，最終保存到存儲(chǔ)層的文件系統(tǒng)中的內(nèi)容，空值和空串是相同的；
• ORALCE外的其它派系，比如mysql/pg/apache hive，認(rèn)為空串和空值不同：當(dāng)用戶通過SQL插入空值或空串時(shí)，數(shù)據(jù)庫計(jì)算層不會(huì)將空串視為空值，最終保存在存儲(chǔ)層的文件系統(tǒng)中的內(nèi)容，空值和空串是不同的；
• 對(duì)于認(rèn)為空串就是空值的ORACLE派系，其自身的空值處理函數(shù)nvl/ifnull/coalesce，可以有效地將空串和空值，都轉(zhuǎn)換為指定的值比如空格SPACE;
• 對(duì)于認(rèn)為空串和空值不同的ORACLE外的其它派系，其自身的空值處理函數(shù)nvl/ifnull/coalesce，只能將空值轉(zhuǎn)換為指定的值比如空格SPACE，而無法有效地對(duì)空串進(jìn)行轉(zhuǎn)換；
• 上述數(shù)據(jù)庫空值處理函數(shù)，ORACLE中是 nvl(), mysql中是 ifnull(),postgresql中是coalesce();
• 當(dāng)數(shù)據(jù)鏈路上混合使用不同數(shù)據(jù)庫，跨數(shù)據(jù)庫進(jìn)行數(shù)據(jù)同步時(shí)，由于不同數(shù)據(jù)庫對(duì)空串和空值的處理方式不同，ORACLE派系認(rèn)為空串就是空值，非ORACLE派系認(rèn)為空串不是空值，所以當(dāng)業(yè)務(wù)上聲明表結(jié)構(gòu)時(shí)限制字段非空(null)，如果上游使用的是非ORACLE派系的數(shù)據(jù)庫且包含空串，此時(shí)將空串插入到下游ORACLE派系的數(shù)據(jù)庫時(shí)，就會(huì)報(bào)上述 “ORA-01400: cannot insert Null“ 的錯(cuò)誤；
• 為確保跨數(shù)據(jù)庫進(jìn)行數(shù)據(jù)同步的健壯性，僅通過數(shù)據(jù)庫空值處理函數(shù) nvl/ifnull/coalesce 對(duì)空值進(jìn)行保護(hù)是不足夠的，還需要通過 length() == 0 對(duì)空串進(jìn)行保護(hù)：即使用 nvl/ifnull/coalesce 和length() 對(duì)空值和空串進(jìn)行雙重保護(hù)，將空值和空串都轉(zhuǎn)換為空格SPACE;
• 在大數(shù)據(jù)領(lǐng)域，由于TDH inceptor/apache hive 是存算分離的，情況更為復(fù)雜：應(yīng)用既可以通過SQL對(duì)表數(shù)據(jù)進(jìn)行增刪改查，也可以繞過 SQL直接使用底層文件系統(tǒng)中特定文件格式的API, 比如 apache ORC 的API，直接讀寫操作數(shù)據(jù)庫底層存儲(chǔ)系統(tǒng)中的 ORC 文件；通過這兩種方式插入空串時(shí)，底層實(shí)際存儲(chǔ)到文件系統(tǒng)如 ORC 文件中的數(shù)據(jù)內(nèi)容，可能并不一致；通過這兩種方式插入空值時(shí)，底層實(shí)際存儲(chǔ)到文件系統(tǒng)如 ORC 文件中的數(shù)據(jù)內(nèi)容，也可能并不一致；必要時(shí)需要通過 SQL 和 hive --orcfiledump等命令，對(duì)比通過SQL查詢層查詢表數(shù)據(jù)，和通過 orc api或命令直接查詢底層存儲(chǔ)系統(tǒng)中文件數(shù)據(jù)，二者的差異。

5 相關(guān)語句

//相關(guān) JAVA 語句
PreparedStatement pstmt = conn.prepareStatement(sql))
pstmt.setNull(2, java.sql.Types.VARCHAR);	//通過 pstmt.setNUll 方法賦空值
pstmt.setString(2, ""); //插入空字符串 - 直接賦值 ""
String s1 = null; pstmt.setString(2, s1);//插入空值-通過JAVA 語句，對(duì)對(duì)象賦值 null
String s2 = "";pstmt.setString(2, s2);//插入空字符串-通過JAVA 語句，對(duì)對(duì)象賦值 ""
//相關(guān) SQL 語句
INSERT INTO test1118 VALUES (' ');
INSERT INTO test1118 VALUES ('');
INSERT INTO test1118 VALUES (NULL);
select '',null,length(''),length(null) from dual; //oracle
select nvl(NULL,'b') from dual; //oracle
select IFNULL(NULL,'b') //mysql
SELECT COALESCE(NULL,'a') //pg
SELECT s1,length(s1),nvl(s1,'a') FROM test1118;//oracle
SELECT s1,length(s1),IFNULL(s1,'a') FROM test1118;//mysql
SELECT s1,length(s1),COALESCE(s1,'a') FROM test1118;//pg

圖片

oracle

mysql

postgresql