摘要：?MaxCompute（原ODPS）是阿里云自主研發(fā)的具有業(yè)界領(lǐng)先水平的分布式大數(shù)據(jù)處理平臺(tái), 尤其在集團(tuán)內(nèi)部得到廣泛應(yīng)用，支撐了多個(gè)BU的核心業(yè)務(wù)。 MaxCompute除了持續(xù)優(yōu)化性能外，也致力于提升SQL語言的用戶體驗(yàn)和表達(dá)能力，提高廣大ODPS開發(fā)者的生產(chǎn)力。
MaxCompute（原ODPS）是阿里云自主研發(fā)的具有業(yè)界領(lǐng)先水平的分布式大數(shù)據(jù)處理平臺(tái), 尤其在集團(tuán)內(nèi)部得到廣泛應(yīng)用，支撐了多個(gè)BU的核心業(yè)務(wù)。 MaxCompute除了持續(xù)優(yōu)化性能外，也致力于提升SQL語言的用戶體驗(yàn)和表達(dá)能力，提高廣大ODPS開發(fā)者的生產(chǎn)力。
MaxCompute基于ODPS2.0新一代的SQL引擎，顯著提升了SQL語言編譯過程的易用性與語言的表達(dá)能力。我們?cè)诖送瞥?b>MaxCompute(ODPS2.0)重裝上陣系列文章
第一彈 - 善用MaxCompute編譯器的錯(cuò)誤和警告
第二彈 - 新的基本數(shù)據(jù)類型與內(nèi)建函數(shù)
上次向您介紹了復(fù)雜類型，從本篇開始，向您介紹MaxCompute在SQL語言DML方面的改進(jìn)
場(chǎng)景1?
_需要寫一個(gè)復(fù)現(xiàn)的SQL， 從多個(gè)表中讀取數(shù)據(jù)，有些之間做Join，有些之間做Union，生成中間數(shù)據(jù)又要Join， 最后需要輸出多張表，最后寫成了n層嵌套的子查詢，自己都看不懂了。而且同樣的查詢，在不同的子查詢中有重復(fù)。為了維護(hù)方便，把復(fù)雜的語句拆成多個(gè)語句，但是發(fā)現(xiàn)每個(gè)語句都需要單獨(dú)提交，排隊(duì)，并且要將中間結(jié)果寫到本來不需要的臨時(shí)表，在后面的語句中再讀出來，慢了好多。。。
場(chǎng)景2
正在開發(fā)新項(xiàng)目，需要給一個(gè)小數(shù)據(jù)表準(zhǔn)備些基本數(shù)據(jù)，但是沒有INSERT ... VALUES 語句，沒辦法把數(shù)據(jù)和創(chuàng)建表的DDL放在一起維護(hù)，只好另用一些腳本，調(diào)用ODPS命令行準(zhǔn)備數(shù)據(jù)。。。
場(chǎng)景3
想測(cè)試一個(gè)新寫的UDF，只寫SELECT myudf('123');會(huì)報(bào)錯(cuò)，還必須創(chuàng)建一個(gè)dual表，里面加一行數(shù)據(jù)，好麻煩。如果測(cè)試UDAF，還要在測(cè)試表里面準(zhǔn)備多行數(shù)據(jù)，每次測(cè)試不同的輸入都要修改表內(nèi)容或者創(chuàng)建新表，如果有個(gè)辦法不用創(chuàng)建表也能不同的數(shù)據(jù)組合測(cè)試我的UDF就好了。。。
場(chǎng)景4
遷移一個(gè)原來在Oracle上面的ETL系統(tǒng)，發(fā)現(xiàn)用了?WHERE EXISTS( SELECT ...)?和?WHERE IN (SELECT ...)?這類的語句，可是發(fā)現(xiàn)ODPS在這方面支持不完整，還要手工將這些半連接的語句轉(zhuǎn)換為普通JOIN，再過濾。。。
MaxCompute采用基于ODPS2.0的SQL引擎，對(duì)DML進(jìn)行了大幅擴(kuò)充，提高了易用性和兼容性，基本解決了上述問題。
Common Table Expression (CTE)
MaxCompute支持SQL標(biāo)準(zhǔn)的CTE。能夠提高SQL語句的可讀性與執(zhí)行效率。
此文中采用MaxCompute Studio作展示，首先，安裝MaxCompute Studio，導(dǎo)入測(cè)試MaxCompute項(xiàng)目，創(chuàng)建工程，建立一個(gè)新的MaxCompute腳本文件, 如下
可以看到，頂層的union兩側(cè)各為一個(gè)join，join的左表是相同的查詢。通過寫子查詢的方式，只能重復(fù)這段代碼。
使用CTE的方式重寫以上語句
可以看到，a對(duì)應(yīng)的子查詢只需要寫一次，在后面重用，CTE的WITH字句中可以指定多個(gè)子查詢，像使用變量一樣在整個(gè)語句中反復(fù)重用。除了重用外，也不必再反復(fù)嵌套了。
編譯此腳本，可以觀察執(zhí)行計(jì)劃如下
其中M1, M2, M4三個(gè)分布式任務(wù)分別對(duì)應(yīng)對(duì)應(yīng)三個(gè)輸入表，雙擊M2可以看到中具體執(zhí)行的DAG（在DAG中再次雙擊可以返回），如下
可以看到對(duì)src讀后進(jìn)行過濾的DAG。對(duì)src的讀取與過濾在整個(gè)執(zhí)行計(jì)劃中只需要一次 (?注1?)。
VALUES
創(chuàng)建一個(gè)新的文件，如下：
執(zhí)行后在，MaxCompute Project Explorer中可以找到新創(chuàng)建的表，并看到values中的數(shù)據(jù)已經(jīng)插入到表中，如下：
有的時(shí)候表的列很多，準(zhǔn)備數(shù)據(jù)的時(shí)候希望只插入部分列的數(shù)據(jù)，此時(shí)可以用插入列表功能
執(zhí)行后，MaxCompute Project Explorer中找到目標(biāo)表，并看到values中的數(shù)據(jù)已經(jīng)插入，如下：
對(duì)于在values中沒有制定的列，可以看到取缺省值為NULL。插入列表功能不一定和VALUES一起用，對(duì)于INSERT INTO ... SELECT..., 同樣可以使用。
INSERT... VALUES... 有一個(gè)限制，values必須是常量，但是有的時(shí)候希望在插入的數(shù)據(jù)中進(jìn)行一些簡(jiǎn)單的運(yùn)算，這個(gè)時(shí)候可以使用MaxCompute的VALUES TABLE功能，如下：
其中的VALUES (...), (...) t (a, b), 相當(dāng)于定義了一個(gè)名為t，列為a, b的表，類型為(a string, b string)，其中的類型從VALUES列表中推導(dǎo)。這樣在不準(zhǔn)備任何物理表的時(shí)候，可以模擬一個(gè)有任意數(shù)據(jù)的，多行的表，并進(jìn)行任意運(yùn)算。
實(shí)際上，VALUES表并不限于在INSERT語句中使用，任何DML語句都可以使用。
還有一種VALUES表的特殊形式
selectabs(-1),length('abc'),getdate();
也就是可以不寫from語句，直接執(zhí)行SELECT，只要SELECT的表達(dá)式列表不用任何上游表數(shù)據(jù)就可以。其底層實(shí)現(xiàn)為從一個(gè)1行，0列的匿名VALUES表選取。這樣，在希望測(cè)試一些函數(shù)，比如自己的UDF等，就再也不用手工創(chuàng)建DUAL表了。
SEMI JOIN
MaxCompute支持SEMI JOIN（半連接）。SEMI JOIN中，右表只用來過濾左表的數(shù)據(jù)而不出現(xiàn)在結(jié)果集中。支持的語法包括LEFT SEMI JOIN，LEFT ANTI JOIN，(NOT) IN SUBQUERY，(NOT) EXISTS
LEFT SEMI JOIN
返回左表中的數(shù)據(jù)，當(dāng)join條件成立，也就是mytable1中某行的id在mytable2的所有id中出現(xiàn)過，此行就保留在結(jié)果集中
例如:
SELECT * from mytable1 a LEFT SEMI JOIN mytable2 b on a.id=b.id;
只會(huì)返回mytable1中的數(shù)據(jù)，只要mytable1的id在mytable2的id中出現(xiàn)過
LEFT ANTI JOIN
返回左表中的數(shù)據(jù)，當(dāng)join條件不成立，也就是mytable1中某行的id在mytable2的所有id中沒有出現(xiàn)過，此行就保留在結(jié)果集中
例如:
SELECT*frommytable1 aLEFTANTIJOINmytable2 bona.id=b.id;
只會(huì)返回mytable1中的數(shù)據(jù)，只要mytable1的id在mytable2的id沒有出現(xiàn)過
IN SUBQUERY/NOT IN SUBQUERY
IN SUBQUERY與LEFT SEMI JOIN類似。
例如:
SELECT*frommytable1whereidin(selectidfrommytable2);
等效于
SELECT*frommytable1 aLEFTSEMIJOINmytable2 bona.id=b.id;
原有ODPS也支持IN SUBQUERY，但是不支持correlated條件，MaxCompute支持
例如:
SELECT*frommytable1whereidin(selectidfrommytable2wherevalue= mytable1.value);
其中子查詢中的where value = mytable1.value就是一個(gè)correlated條件，原有ODPS對(duì)于這種既引用了子查詢中源表，由引用了外層查詢?cè)幢淼谋磉_(dá)式時(shí)，會(huì)報(bào)告錯(cuò)誤。MaxCompute支持這種用法，這樣的過濾條件事實(shí)上構(gòu)成了SEMI JOIN中的ON條件的一部分。
對(duì)于NOT IN SUBQUERY，類似于LEFT ANTI JOIN，但是有一點(diǎn)顯著不同
例如:
SELECT*frommytable1whereidnotin(selectidfrommytable2);
如果mytable2中的所有id都不為NULL，則等效于
SELECT*frommytable1 aLEFTANTIJOINmytable2 bona.id=b.id;
但是，如果mytable2中有任何為NULL的列，則 not in表達(dá)式會(huì)為NULL，導(dǎo)致where條件不成立，無數(shù)據(jù)返回，此時(shí)與LEFT ANTI JOIN不同。
原有ODPS也支持[NOT] IN SUBQUERY不作為JOIN條件，例如出現(xiàn)在非WHERE語句中，或者雖然在WHERE語句中，但無法轉(zhuǎn)換為JOIN條件。MaxCompute仍然支持這種用法，但是此時(shí)因?yàn)闊o法轉(zhuǎn)換為SEMI JOIN而必須實(shí)現(xiàn)啟動(dòng)一個(gè)單獨(dú)的作業(yè)來運(yùn)行SUBQUERY，所以不支持correlated條件。
例如:
SELECT*frommytable1whereidin(selectidfrommytable2)ORvalue>0;
因?yàn)閃HERE中包含了OR，導(dǎo)致無法轉(zhuǎn)換為SEMI JOIN，會(huì)單獨(dú)啟動(dòng)作業(yè)執(zhí)行子查詢
另外在處理分區(qū)表的時(shí)候，也會(huì)有特殊處理
SELECT*fromsales_detailwheredsin(selectdtfromsales_date);
其中的ds如果是分區(qū)列，則select dt from sales_date?會(huì)單獨(dú)啟動(dòng)作業(yè)執(zhí)行子查詢，而不會(huì)轉(zhuǎn)化為SEMIJOIN，執(zhí)行后的結(jié)果會(huì)逐個(gè)與ds比較，sales_detail中ds值不在返回結(jié)果中的分區(qū)不會(huì)讀取，保證分區(qū)裁剪仍然有效。
EXISTS SUBQUERY/NOT EXISTS SUBQUERY
當(dāng)SUBQUERY中有至少一行數(shù)據(jù)時(shí)候，返回TRUE，否則FALSE。NOT EXISTS的時(shí)候則相反。目前只支持含有correlated WHERE條件的子查詢。EXISTS SUBQUERY/NOT EXISTS SUBQUERY實(shí)現(xiàn)的方式是轉(zhuǎn)換為L(zhǎng)EFT SEMI JOIN或者LEFT ANTI JOIN
例如:
SELECT*frommytable1whereexists(select*frommytable2whereid= mytable1.id);`
等效于
SELECT*frommytable1 aLEFTSEMIJOINmytable2 bona.id=b.id;
而
SELECT*frommytable1wherenotexists(select*frommytable2whereid= mytable1.id);`
則等效于
SELECT*frommytable1 aLEFTANTIJOINmytable2 bona.id=b.id;
其他改進(jìn)
MaxCompute支持UNION [DISTINCT] - 其中DISTINCT為忽略
SELECT*FROMsrc1UNIONSELECT*FROMsrc2;
執(zhí)行的效果相當(dāng)于
SELECTDISTINCT*FROM(SELECT*FROMsrc1UNIONALLSELECT*FROMsrc2) t;
支持IMPLICIT JOIN
SELECT*FROMtable1, table2WHEREtable1.id = table2.id;
執(zhí)行的效果相當(dāng)于
SELECT*FROMtable1JOINtable2ONtable1.id = table2.id;
此功能主要是方便從其他數(shù)據(jù)庫系統(tǒng)遷移，對(duì)于信貸買，我們還是推薦您使用JOIN，明確表示意圖
支持新的SELECT語序
在一個(gè)完整的查詢語句中，例如
SELECTkey,max(value)FROMsrc tWHEREvalue>0GROUPBYkeyHAVINGsum(value) >100ORDERBYkeyLIMIT100;
實(shí)際上的邏輯執(zhí)行順序是 FROM->WHERE->GROUY BY->HAVING->SELECT->ORDER BY->LIMIT，前一個(gè)是后一個(gè)的輸入，與標(biāo)準(zhǔn)的書寫語序?qū)嶋H并不相同。很多容易混淆的問題，都是由此引起的。例如order by中只能引用select列表中生成的列，而不是訪問FROM的源表中的列。HAVING可以訪問的是 group by key和聚合函數(shù)。SELECT的時(shí)候，如果有GROUP BY，就只能訪問group key和聚合函數(shù)，而不是FROM中源表中的列。
MaxCompute支持以執(zhí)行順序書寫查詢語句，例如上面的語句可以寫為
FROMsrc tWHEREvalue >0GROUPBYkeyHAVING sum(value) >100SELECTkey, max(value)ORDERBYkeyLIMIT100;
書寫順序和執(zhí)行順序一致，就不容易混淆了。這樣有一個(gè)額外的好處，在MaxCompute Studio中寫SQL語句的時(shí)候，會(huì)有智能提示的功能，如果是SELECT在前，書寫select列表的表達(dá)式的時(shí)候，因?yàn)镕ROM還沒有寫，MaxCompute Studio沒辦法知道可能訪問那些列，也就不能做提示。如下
需要先寫好FROM，再回頭寫SELECT列表，才能提示。如下
如果使用上述以FROM起始的方式書寫，則可以自然而然的根據(jù)上下文進(jìn)行提示。如下
支持頂層UNION
ODPS1.0不支持頂層UNION。ODPS2.0可以支持，例如
SELECT*FROMsrcUNIONALLSELECT*FROMsrc;
UNION后LIMIT的語義變化。
大部分DBMS系統(tǒng)中，如MySQL，Hive等，UNION后如果有CLUSTER BY, DISTRIBUTE BY, SORT BY, ORDER BY或者LIMIT子句，其作用于與前面所有UNION的結(jié)果，而不是UNION的最后一路。ODPS2.0在set odps.sql.type.system.odps2=true;的時(shí)候，也采用此行為。例如:
setodps.sql.type.system.odps2=true;SELECTexplode(array(1,3))AS(a)UNIONALLSELECTexplode(array(0,2,4))AS(a)ORDERBYaLIMIT3;
返回
a
0
1
2
小節(jié)
MaxCompute大大擴(kuò)充了DML語句的支持，在易用性，兼容性和性能方面，可以更好的滿足您的需求。對(duì)于SQL比較熟悉的專家會(huì)發(fā)現(xiàn)，上述功能大部分是標(biāo)準(zhǔn)的SQL支持的功能。MaxCompute會(huì)持續(xù)提升與標(biāo)準(zhǔn)SQL和業(yè)界常用產(chǎn)品的兼容性。
除此之外，針對(duì)MaxCompute用戶的特點(diǎn)，也就是需要在非常復(fù)雜的業(yè)務(wù)場(chǎng)景下，支持對(duì)己大量數(shù)據(jù)的處理，MaxCompute提供了特有的腳本模式和參數(shù)化視圖，將在下一次為您介紹。
標(biāo)注
注1
是否合并或者分裂子查詢，是由ODPS2.0的基于代價(jià)的優(yōu)化器 (CBO)做出決定的，SQL本身的書寫方式，不管是CTE還是子查詢，并不能確保物理執(zhí)行計(jì)劃的合并或者分裂。
