1 問(wèn)題概述

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

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

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

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

例：

select * from (--（錯(cuò)誤）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 問(wèn)題分析

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

錯(cuò)誤示例：

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]是指第四行，第八個(gè)字符開始也就是getdate().

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

2 問(wèn)題解決

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

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

2.1 利用執(zhí)行計(jì)劃

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

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ù)類型了。所以，我們可以把長(zhǎng)腳本中的union一段一段的explain，然后截取這部分內(nèi)容，比較多個(gè)schema的不同。

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

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

2.2 其他問(wèn)題

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

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

2) 超長(zhǎng)的SQL段，執(zhí)行計(jì)劃可能有幾百行上千行，找不到最終的output。可以在日志中搜索“output: Screen”這段對(duì)應(yīng)的就是最終的輸出。

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

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

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

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

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

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

2.3 另外一個(gè)方法

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

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

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

祝大家好運(yùn)！