MaxCompute - ODPS重裝上陣　第二彈 - 新的基本數據類型與內建函數

02-03

摘要： MaxCompute（原ODPS）是阿里雲自主研發的具有業界領先水平的分散式大數據處理平台, 尤其在集團內部得到廣泛應用，支撐了多個BU的核心業務。 MaxCompute除了持續優化性能外，也致力於提升SQL語言的用戶體驗和表達能力，提高廣大ODPS開發者的生產力。

作者：振禹

原文：click.aliyun.com/m/40154/

MaxCompute（原ODPS）是阿里雲自主研發的具有業界領先水平的分散式大數據處理平台, 尤其在集團內部得到廣泛應用，支撐了多個BU的核心業務。 MaxCompute除了持續優化性能外，也致力於提升SQL語言的用戶體驗和表達能力，提高廣大ODPS開發者的生產力。

MaxCompute基於ODPS2.0新一代的SQL引擎，顯著提升了SQL語言編譯過程的易用性與語言的表達能力。我們在此推出MaxCompute(ODPS2.0)重裝上陣系列文章

第一彈 - 善用MaxCompute編譯器的錯誤和警告第二彈 - 新的基本數據類型與內建函數第三彈 - 複雜類型第四彈 - CTE，VALUES，SEMIJOIN

上次向您介紹了 [編譯器的易用性改進] https://yq.aliyun.com/articles/225028)，這次向您介紹新的基本數據類型與內建函數

原ODPS只有六種基本數據類型, bigint, double, decimal, string, datetime, boolean。一般用起來也還夠用，但是在某些場景下就不夠了

場景1 一個項目需要將原來在SQL SERVER上面運行的ETL系統，最近因為數據量暴漲，需要遷移到MaxCompute。發現某些表用了VARCHAR，有的用了INT。這些類型也被系統的多處SQL腳本用到還參與了運算。遷移到ODPS上時候，用STRING代替VARCHAR，用BIGINT代替INT ( 注1 )。

遷移完成後發現數據和原有系統對不上，是不是VARCHAR的截斷，INT的溢出行為導致數據不同呢？還是什麼其他原因，面對著現存系統，沒辦法，只好一點點看代碼，跑數據，做分析。本來以為挺輕鬆的項目，花了幾周時間才搞定。。。

場景2 我的項目需要存放二進位數據到表中，因為是語音識別項目，每小段採集的音頻如果作為一個欄位存下去，然後用個UDF處理起來很方便。可是，ODPS沒有BINARY數據類型，好吧，就存成STRING好了。可是編寫寫UDF時候好麻煩，為了存進去，必須將byte[]編碼成string, 讀的時候又必須解碼，代碼寫了一大堆，運行速度也慢了好多。。。

MaxCompute採用基於ODPS2.0的SQL引擎，大幅度擴充了基本類型並提供了配套的內建函數，基本解決了上述問題。

基本類型的擴充

此文中採用MaxCompute Studio作展示，首先，安裝MaxCompute Studio，導入測試MaxCompute項目，創建工程，建立一個新的MaxCompute腳本文件,　如下

運行後，建立另一個文件插入數據，如下：

運行後，可以在MaxCompute Studio的Project Explorer中找到新創建的表，察看錶的詳細信息，並預覽數據，如下圖

可以看到

創建表的時候，首先指定使用MaxCompute新類型系統，因為兼容性的考慮，需要您主動打開這個設定。也可以在MaxCompute Studio中預設指定，如下圖

MaxCompute Studio支持含新類型表數據的導入導出，可參考此ATA文章

如果不使用MaxCompute Studio，可以在腳本中指定，set odps.sql.type.system.odps2=true;。Studio實際上在後台也是使用這個開關來控制是否啟用新類型。odps.sql.type.system.odps2設定為true的時候，除了可以使用新類型，也控制其它方面的一些行為改變。將在相關部分說明。

如果需要在MaxCompute 項目中預設打開，可以聯繫您的項目管理員，在項目模板中設定。

擴充後MaxCompute支持的基本數據類型如下表，新增類型有TINYINT, SMALLINT, INT, FLOAT, VARCHAR, TIMESTAMP, BINARY。

新的隱式轉換規則表如下表 ( 注5 )

|--------------|---------|---------|----------|-------|--------|-------|--------|---------|--------|---------|-----------|--------|

此外，還新增了DECIMAL類型與DATETIME的常量定義方式, 100BD就是數值為100的DECIMAL，datetime 2017-11-11 00:00:00就是個datetime類型的常量。常量定義的方便之處在於可以直接用到values子句和values表中，以後會單獨介紹。

內建函數的擴充

任何編程語言，包括SQL，不管語言本身多強大，如過沒有豐富的函數後者類庫支持，在應用的時候還是會很不方便，MaxCompute配合新數據類型，大大豐富了內建函數，如下：

數學函數
log2, log10, bin, hex, unhex, degrees, radians, sign, e, pi, factorial, cbrt, shiftleft, shiftright, shiftrightunsigned
日期函數
unix_timestamp, year, quarter, month, day, dayofmonth, hour, minute, second, millisecond, nanosecond, from_utc_timestamp, current_timestamp, add_months, last_day, next_day, months_between
字元串函數
concat_ws, lpad, rpad, replace, soundex, substring_index, base64, unbase64
聚合函數

corr

這些函數大部分與Hive的內建函數兼容，用法可以直接參考Hive的文檔。與Hive不同的是MaxCompute提供的這些函數都是用本地代碼實現的高效版本。

新增的TIMESTAMP數據類型支持納秒級別的精度，與之配合，新增了MaxCompute特有的millisecond, nanosecond函數，可以取出TIMESTAMP, DATETIME的毫秒部分與TIMESTAMP的納秒部分。

如本系列上一篇中提到的，MaxCompute支持新的強制轉換寫法，例如，要強制bigint變數為轉換為string，可以直接寫string(a_bigint), 和寫成cast(a_bigint as string)是等效的。具體用哪種形式完全取決於您的偏好。

需要注意的是所有用來支持新類型的函數，例如current_timestamp，也需要設定set odps.sql.type.system.odps2=true;，否則會報告編譯錯誤。

分區類型的擴充

分區類型的支持也進行了擴充，目前分區類型支持TINYINT, SMALLINT, INT, BIGINT, VARCHAR與STRING ( 注6 )

另外原ODPS在動態分區的時候，如果分區列的類型與對應SELECT列表中的列的類型不嚴格一致，會報錯。MaxCompute支持隱式類型轉換

例如:

可以看到分區列p的值為從timestamp類型隱含轉換而來。

使用UDF

目前，MaxCompute2.0的JAVA UDF已經支持了新類型，Python UDF會儘快實現。JAVA UDF使用新類型的方法如下：

UDAF和UDTF通過@Resolve註解來獲取signature，MaxCompute2.0支持在註解中使用新類型，如 @Resolve("smallint->varchar(10)")
UDF通過反射分析evaluate來獲取signature，此時max compute內置類型與JAVA類型符合一一映射關係

需要注意的是這裡，array類型對應的java類型是List，而不是數組

小結

MaxCompute大大擴充了基本數據類型與內建函數，可以更好的適應豐富的應用場景。不過，很多比較複雜的場景僅使用基本類型仍然很麻煩，請期待MaxCompute重裝上陣下一篇，複雜類型的支持 ！

標註

注1

對於INT常量，如果超過INT取值範圍，會轉為BIGINT，如果超過BIGINT取值範圍，會轉為DOUBLE
在原ODPS下，因為歷史原因，SQL腳本中的所有int類型都被轉換為bigint，例如

為了與ODPS原有模式兼容，MaxCompute在沒有設定odps.sql.type.system.odps2為true的情況下，仍然保留此轉換，但是會報告一個警告提示int被當作bigint處理了，如果您的腳本有此種情況，建議全部改寫為bigint，避免混淆。

注2 VARCHAR類型常量可通過STRING常量的隱式轉換表示
注3 STRING常量支持連接, 例如abc xyz會解析為abcxyz，不同部分可以寫在不同行上
注4 受底層系統限制，目前調用current_timestamp還達不到納秒精度，例如

meta_dev>set odps.sql.type.system.odps2=true;select nanosecond(current_timestamp());n

輸出為類似

+------+n| _c0 |n+------+n| 877000000 |n+------+n

Timestamp常量與外部數據導入可以支持納秒精度。

注5 在原ODPS下，因為歷史原因，DOUBLE可以隱式的轉換為BIGINT，這個轉換潛在可能有數據丟失，一般資料庫系統都不允許。為了與ODPS原有模式兼容，MaxCompute在沒有設定odps.sql.type.system.odps2為true的情況下，仍然允許此轉換，但是會報告警告；在設定odps.sql.type.system.odps2為true的情況下，不允許此隱式類型轉換。
注6 在原ODPS下，因為歷史原因，雖然可以指定分區類型為BIGINT，但是除了表的schema表示其為BIGINT,　任何其他情況都被處理為STRING。例如:

create table parttest (a bigint) partitioned by (pt bigint);ninsert into parttest partition(pt) select 1, 2 from dual;ninsert into parttest partition(pt) select 1, 10 from dual;nselect * from parttest where pt >= 2;n

返回的結果只有一行，因為10被按照字元串和2比，沒能返回。為了與ODPS原有模式兼容，MaxCompute在沒有設定odps.sql.type.system.odps2為true的情況下，仍然如此處理；在設定odps.sql.type.system.odps2為true的情況下，BIGINT類型的分區嚴格按照BIGINT類型處理。

更多技術乾貨敬請關注云棲社區知乎機構號：阿里云云棲社區 - 知乎

MaxCompute - ODPS重裝上陣 第二彈 - 新的基本數據類型與內建函數

基本類型的擴充

內建函數的擴充

分區類型的擴充

使用UDF

小結

標註

MaxCompute - ODPS重裝上陣　第二彈 - 新的基本數據類型與內建函數