1.問題現(xiàn)象

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

2.問題原因

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

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

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

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

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

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

4.技術(shù)背景

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