1 問題概述

1.1 UNION中隱式類型轉換問題

近期參與的一個私有云項目要升級，因為maxcompute要升級到更新的版本，對之前的一些SQL寫法有個更高的要求，就引出了這個union隱式轉換的問題。運維同學掃描到內部的異常是：union.string.meet.non.string。

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

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

例：

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

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

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

-- 如果希望結果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 問題分析

因為還未升級，目前腳本也不會報錯，maxcompute的異常我們也捕獲不到，改造的壓力有點純靠肉眼識別了，著實有點難過。

錯誤示例：

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 的數量在單個腳本中少則三五個，多的有二十幾個。呆了一早上，毫無進展。

2 問題解決

簡單的思考了一下，要想獲得Union的兩個表數據類型是否對齊，就得看下原來表結構中的數據類型，目標表結構的數據類型，還需看一下代碼找到SQL邏輯執行后的數據類型，這樣才能找到哪些字段數據類型不一致。

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

2.1 利用執行計劃

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

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)。這就是我們可以利用的數據類型了。所以，我們可以把長腳本中的union一段一段的explain，然后截取這部分內容，比較多個schema的不同。

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

這樣就肉眼可視的發現其實union中兩段SQL的字段aa是不同的。

2.2 其他問題

其他相關的一些問題：

1) 執行計劃中的max_pt()函數無法在開發環境使用，因為開發環境沒有分區，這個函數會直接報錯。要么刪除、注釋這個函數，要么在表前面增加生產環境前綴。

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

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

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

5) 日期轉換，因為string到日期轉換的格式化類型不是能猜出來的，建議實際看一下數據格式，不要猜測。否則只能線上運行后，報錯才能排查出問題。

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

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

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

2.3 另外一個方法

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

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

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

祝大家好運！