1 問題概述

1.1 UNION中隱式類型轉(zhuǎn)換問題

近期參與的一個私有云項(xiàng)目要升級，因?yàn)閙axcompute要升級到更新的版本，對之前的一些SQL寫法有個更高的要求，就引出了這個union隱式轉(zhuǎn)換的問題。運(yùn)維同學(xué)掃描到內(nèi)部的異常是：union.string.meet.non.string。

在ODPS某些模式中在union兩側(cè)對應(yīng)列如果類型不同時會嘗試隱式類型轉(zhuǎn)換，其行為是一邊為string，另一邊為數(shù)字或datetime類型時，轉(zhuǎn)為另一邊的類型（string）。然而絕大多數(shù)的數(shù)據(jù)庫或者開源生態(tài)而言，使用的都不是這種轉(zhuǎn)換規(guī)則，比如hive，mysql等會優(yōu)先轉(zhuǎn)成string。這種不確定的轉(zhuǎn)換規(guī)則有時候會很危險，如用戶從hive往odps遷移時，可能會導(dǎo)致無聲無息的精度損失，語義錯誤等。

ODPS2.0為了安全禁止此隱式類型轉(zhuǎn)換（這也是目前oracle的默認(rèn)行為）,如果需要請使用CAST函數(shù)。（之前好好的，現(xiàn)在要報錯了）所以現(xiàn)在項(xiàng)目組要求腳本作者檢查自己腳本，明確要轉(zhuǎn)到的類型，如果需要加入顯式轉(zhuǎn)換。

例：

select * from (--（錯誤）select a_bigint c1 from t1 union allselect a_string c1 from t2) x;  

-- 如果希望結(jié)果c1為bigint類型（這是目前ODPS的行為），改為

select * from (--（正確）select a_bigint c1 from t1  union all select cast(a_string as bigint) c1 from t2) x; 

-- 如果希望結(jié)果c1為string類型（這是目前HIVE的行為），改為

select * from (--（正確）select cast(a_bigint as string) c1 from t1  union all select a_string c1 from t2) x; 

1.2 問題分析

因?yàn)檫€未升級，目前腳本也不會報錯，maxcompute的異常我們也捕獲不到，改造的壓力有點(diǎn)純靠肉眼識別了，著實(shí)有點(diǎn)難過。

錯誤示例：

select 123 as aa,0 as abfrom xlogunion ALL select getdate() as aa,0 as abfrom xlog;FAILED: ODPS-0130241:[4,8] Illegalunion operation - type mismatch for column 0 of UNION, left is BIGINT while right is DATETIME

--注釋：這里的[4,8]是指第四行，第八個字符開始也就是getdate().

那怎么去快速的定位到是哪個字段呢？我翻了一下后臺檢索出來的上百個腳本，腳本代碼在500-1000行之間居多，union 的數(shù)量在單個腳本中少則三五個，多的有二十幾個。呆了一早上，毫無進(jìn)展。

2 問題解決

簡單的思考了一下，要想獲得Union的兩個表數(shù)據(jù)類型是否對齊，就得看下原來表結(jié)構(gòu)中的數(shù)據(jù)類型，目標(biāo)表結(jié)構(gòu)的數(shù)據(jù)類型，還需看一下代碼找到SQL邏輯執(zhí)行后的數(shù)據(jù)類型，這樣才能找到哪些字段數(shù)據(jù)類型不一致。

于是按照這個思路開始看，第一個腳本的代碼就1000多行，union的表字段數(shù)量也是100多個，union還有6個。直接懵了，完全肉眼無法識別。一早上就這么過去了，不但一個沒有搞定，還把自己搞煩了。

2.1 利用執(zhí)行計劃

一抽莫展之際，突然想到了執(zhí)行計劃。MaxCompute的執(zhí)行計劃，雖然會不會剛好會展示輸出的數(shù)據(jù)類型呢？答案：會的。

explainselect 123 as aa,0 as abfrom xlog;Job Queueing... job0 is root jobIn Job job0:root Tasks: M1In Task M1: Data source: mujiao.xlog    TS: mujiao.xlog        SEL: 123L aa, 0L ab            FS: output: Screen                schema:                  aa (bigint)                  ab (bigint)OKexplainselect getdate() as aa,0 as abfrom xlog;;Job Queueing...job0 is root jobIn Job job0:root Tasks: M1In Task M1: Data source: mujiao.xlog    TS: mujiao.xlog        SEL: 1655965081824 aa, 0L ab            FS: output: Screen                schema:                  aa (datetime)                  ab (bigint)OK

我們看到在FS:output:Screen 下面是schema:aa(bigint),ab(bigint)。這就是我們可以利用的數(shù)據(jù)類型了。所以，我們可以把長腳本中的union一段一段的explain，然后截取這部分內(nèi)容，比較多個schema的不同。

schema1:        schema2: aa (bigint)     aa (datetime)   ab (bigint)     ab (bigint)

這樣就肉眼可視的發(fā)現(xiàn)其實(shí)union中兩段SQL的字段aa是不同的。

2.2 其他問題

其他相關(guān)的一些問題：

1) 執(zhí)行計劃中的max_pt()函數(shù)無法在開發(fā)環(huán)境使用，因?yàn)殚_發(fā)環(huán)境沒有分區(qū)，這個函數(shù)會直接報錯。要么刪除、注釋這個函數(shù)，要么在表前面增加生產(chǎn)環(huán)境前綴。

2) 超長的SQL段，執(zhí)行計劃可能有幾百行上千行，找不到最終的output?？梢栽谌罩局兴阉鳌皁utput: Screen”這段對應(yīng)的就是最終的輸出。

3) 太多的字段，肉眼無法判斷哪些類型不一樣的時候，建議在excel中來比較，利用excel的篩選能力，逐個數(shù)據(jù)類型篩選比較。

4) 執(zhí)行計劃在特別的情況下可能出不來，使用create table as創(chuàng)建一個臨時表來識別SQL輸出的數(shù)據(jù)類型，然后再desc表結(jié)構(gòu)。不過每個字段都要給一個名稱，在create table的時候，還有null這種寫法也是需要cast后給一個明確的數(shù)據(jù)類型。

5) 日期轉(zhuǎn)換，因?yàn)閟tring到日期轉(zhuǎn)換的格式化類型不是能猜出來的，建議實(shí)際看一下數(shù)據(jù)格式，不要猜測。否則只能線上運(yùn)行后，報錯才能排查出問題。

6) 對于Null值，可以cast(null as datetime)、cast(null as double)給字段賦值。

即便這些都可以，對于數(shù)百個長達(dá)幾百行的腳本來說，這項(xiàng)工作都足以讓你煩躁不安失去耐心。建議研發(fā)同學(xué)還是勞逸結(jié)合，再就是日后把這個工作變成一個習(xí)慣。一大段SQL的union，就直接先explain，別等報錯一個一個看心煩。

最后，你會發(fā)現(xiàn)這一切的緣由還是我們的基礎(chǔ)工作沒有做好。既然是union一起的數(shù)據(jù)字段，理論數(shù)據(jù)類型和值域是一模一樣的，怎么會出這種問題。標(biāo)準(zhǔn)化的數(shù)據(jù)應(yīng)該是日期就是日期，數(shù)值就是數(shù)值，字符就是字符，不會數(shù)值存儲成字符、日期存儲成字符。顯然，現(xiàn)在的痛苦還是來源于之前的工作缺失，做好每一步，后面會越來越輕松。

2.3 另外一個方法

后來跟研發(fā)同學(xué)要到了一個可以讓warning信息顯示出來的提示。

setodps.compiler.warning.disable=false;sql running .....WARNING:[4,8] implicit conversion from bigint to datetime,use cast function to suppress

這個warning會讓所有的隱式轉(zhuǎn)換都拋出來，在現(xiàn)場環(huán)境中，明顯比我實(shí)際按照explain的方法判斷出來的要多很多。這兩種方法，在實(shí)際使用中該如何使用，大家可以自行判斷。

祝大家好運(yùn)！