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è)诖送瞥鯩axCompute(ODPS2.0)重裝上陣系列文章

• 第一彈 -?善用MaxCompute編譯器的錯(cuò)誤和警告
• 第二彈 -?新的基本數(shù)據(jù)類型與內(nèi)建函數(shù)
• 第三彈 -?復(fù)雜類型
• 第四彈 -?CTE，VALUES，SEMIJOIN
• 第五彈 -?SELECT TRANSFORM
• 第六彈 -?User Defined Type
• 第七彈 -?Grouping Set, Cube and Rollup
• 第八彈 -?動(dòng)態(tài)類型函數(shù)

MaxCompute自定義函數(shù)的參數(shù)和返回值不夠靈活，是數(shù)據(jù)開發(fā)過程中時(shí)常被提及的問題。Hive 提供給了 GenericUDF 的方式，通過調(diào)用一段用戶代碼，讓用戶來根據(jù)參數(shù)類型決定返回值類型。MaxCompute 出于性能、安全性等考慮，沒有支持這種方式。但是MaxCompute也提供了許多方式，讓您能夠靈活地自定義函數(shù)。

• 場(chǎng)景1
  需要實(shí)現(xiàn)一個(gè)UDF，可以接受任意類型的輸入，但是MaxCompute的UDF不支持泛型，要做一個(gè)接受任何類型的函數(shù)，就必須為每種類型都寫一個(gè)evaluate函數(shù)。
• 場(chǎng)景2
  MaxCompute的UDAF和UDTF使用@Resolve的注解來指定輸入輸出類型，無法重載。要做一個(gè)接受多種類型的自定義功能，就需要定義多個(gè)不同的函數(shù)。
• 場(chǎng)景3
  MaxCompute支持了參數(shù)化視圖，能夠把一些公共的SQL提出來。參數(shù)化視圖的表值參數(shù)要求輸入表的列數(shù)和類型與視圖定義時(shí)完全一致，如果想要寫一個(gè)能夠接受具有相似特征的不同的表的視圖，還無法定義出來。

本文帶大家一起看看MaxCompute對(duì)這些大家關(guān)心的問題都做了哪些改進(jìn)。

參數(shù)化視圖

問題

參數(shù)化視圖是MaxCompute自己設(shè)計(jì)的一種視圖。允許用戶定義參數(shù)，從而能夠大大視圖代碼的復(fù)用率。很多用戶都利用這一功能，將一些公共SQL提取到視圖中，形成公共SQL代碼池。

參數(shù)化視圖在聲明過程中具有局限性：參數(shù)類型，長度都是固定的。尤其是參數(shù)化視圖允許傳入表值參數(shù)，表值參數(shù)要求形參與實(shí)參在列的個(gè)數(shù)和類型上都一致。這一點(diǎn)限制了許多使用場(chǎng)景，如下面的例子：

CREATE VIEW paramed_view (@a TABLE(key bigint)) AS SELECT @a.* FROM @a JOIN dim on a.key = dim.key;

這個(gè)例子封裝了一段使用dim表來過濾輸入表的邏輯，本來這個(gè)是個(gè)通用的邏輯，任何包含key這一列的表，都可以用來做輸入表。但是由于定義視圖時(shí)只能確定輸入中包含key列，因此聲明的參數(shù)類型只包含這一列。導(dǎo)致了視圖的調(diào)用者傳遞的表參數(shù)必須只能有一列，而返回的數(shù)據(jù)集也只包含一列，這顯然與這個(gè)視圖的設(shè)計(jì)初衷不合。

改進(jìn)

最新的MaxCompute版本對(duì)參數(shù)化視圖做了一些改進(jìn)，可以大大提升參數(shù)化視圖定義的靈活性。

首先，參數(shù)化視圖的參數(shù)可以使用ANY關(guān)鍵字，表示任意類型。如

CREATE VIEW paramed_view (@a ANY) AS SELECT * FROM src WHERE case when @a is null then key1 else key2 end = key3;

這里定義的視圖，第一個(gè)參數(shù)可以接受任意類型。注意ANY類型不能參與如?'+',?'AND'?之類的需要明確類型才能做的運(yùn)算。ANY類型更多是在TABLE參數(shù)中做passthrough列，如

CREATE VIEW paramed_view (@a TABLE(name STRING, id ANY, age BIGINT)) AS SELECT * FROM @a WHRER name = 'foo' and age < 25;-- 調(diào)用示例 SELECT * FROM param_view((SELECT name, id, age from students));

上面的視圖接受一個(gè)表值參數(shù)，但是并不關(guān)心這個(gè)表的第二列，那么這個(gè)列可以直接定義為ANY類型。參數(shù)化視圖在調(diào)用時(shí)，每次都會(huì)根據(jù)輸入?yún)?shù)的實(shí)際類型重新推算返回值類型。比如上面的視圖，當(dāng)輸入的表是?TABLE(c1 STRING, c2 DOUBLE, c3 BIGINT)，那么輸出的數(shù)據(jù)集的第二列也會(huì)自動(dòng)變成DOUBLE類型，讓視圖的調(diào)用者可以使用任何可用于DOUBLE類型的操作來操作這一列。

需要注意的一點(diǎn)是，我們用CREATE VIEW創(chuàng)建了視圖后，可以用DESC來獲取視圖的描述，這個(gè)描述中會(huì)包含視圖的返回類型信息。但是由于視圖的返回類型是在調(diào)用的時(shí)候重新推算的，重新推算出來的類型可能與創(chuàng)建視圖時(shí)推導(dǎo)出來的不一致。一個(gè)例子就是上面的ANY類型。

在ANY之外，參數(shù)化視圖中的表值參數(shù)還支持了*，表示任意多個(gè)列。這個(gè)?*?可以帶類型，也可以使用ANY類型。如

CREATE VIEW paramed_view (@a TABLE(key STRING, * ANY), @b TABLE(key STRING, * STRING)) AS SELECT a.* FROM @a JOIN @b ON a.key = b.key; -- 調(diào)用示例 SELECT name, address FROM param_view((SELECT school, name, age, address FROM student), school) WHERE age < 20;

上面這個(gè)視圖接受兩個(gè)表值參數(shù)，第一個(gè)表值參數(shù)第一列是string類型，后面可以是任意多個(gè)任意類型的列，而第二個(gè)表值參數(shù)的第一列是string，后面可以是任意多個(gè)STRING類型的列。這其中有幾點(diǎn)需要注意:

• 變長部分必須要寫在表值參數(shù)定義的最后面，即在?*?的后面不允許再有其他列。這也間接導(dǎo)致了一個(gè)表值參數(shù)中最多只有一個(gè)變長列列表。
• 由于變長部分必須在最后，有的時(shí)候輸入表的列不一定是按照這種順序排列的，這時(shí)候需要對(duì)輸入表的列做一定重排，可以以subquery作為參數(shù)（參考上面的例子），注意subquery外面要加一層括號(hào)。
• 由于表值參數(shù)中變長部分沒有名字，因此在視圖定義過程中沒辦法獲得對(duì)這部分?jǐn)?shù)據(jù)的引用，也就沒有辦法對(duì)這些數(shù)據(jù)做運(yùn)算。這個(gè)限制是特意設(shè)置的，如果需要對(duì)變長部分的數(shù)據(jù)做運(yùn)算，需要把要運(yùn)算的列聲明在定長部分，而編譯器會(huì)對(duì)調(diào)用時(shí)傳入的參數(shù)進(jìn)行檢查。
• 雖然不能對(duì)變長部分做運(yùn)算，但是?SELECT *?這種通配符的使用依舊可以將變長部分的列傳遞出去，如上面的例子在paramed_view中將?@a?的所有列返回，雖然創(chuàng)建視圖的時(shí)候，a中只有key這一列，但是調(diào)用視圖的時(shí)候，編譯器推算出@a中還包含了name, age, address，因此視圖返回的數(shù)據(jù)集中也包含這三列，而視圖的調(diào)用者也可以對(duì)著三列進(jìn)行操作（如?WHERE age < 20）。
• 表值參數(shù)的列與視圖聲明時(shí)指定的定長列部分不一定完全一致。如果名字不一樣，編譯器會(huì)自動(dòng)做重命名，如果類型不一樣，編譯器會(huì)做隱式轉(zhuǎn)換（不能隱式轉(zhuǎn)換則會(huì)報(bào)錯(cuò)）。

上面提到的第4點(diǎn)非常有用，一方面保證了調(diào)用視圖是輸入?yún)?shù)的靈活性，另一方面又不降低數(shù)據(jù)的信息量。好好利用能夠很大程度上增加公共代碼的復(fù)用率。

下面是一個(gè)調(diào)用示例。該例子使用的視圖是：

CREATE VIEW paramed_view (@a TABLE(key STRING, * ANY), @b TABLE(key STRING, * STRING)) AS SELECT a.* FROM @a JOIN @b ON a.key = b.key;

在MaxCompute Studio中調(diào)用，可以享受語法高亮和錯(cuò)誤提示等功能。執(zhí)行的調(diào)用代碼如下：

執(zhí)行的狀態(tài)圖如下：

放大執(zhí)行過程仔細(xì)觀察，圖中可以發(fā)現(xiàn)幾點(diǎn)有意思的地方：

上述執(zhí)行輸出的結(jié)果如下：

+------+---------+ | name | address | +------+---------+ | 小明 | 杭州 | +------+---------+

其他用法

經(jīng)常有用戶誤用參數(shù)化視圖，將參數(shù)化視圖的參數(shù)當(dāng)做是宏替換參數(shù)來使用。這里說明一下。參數(shù)化視圖實(shí)際上是函數(shù)調(diào)用，而不是宏替換。如下面的例子：

CREATE VIEW paramed_view(@a TABLE(key STRING, value STRING), @b STRING) AS SELECT * FROM @a ORDER BY @b;-- 調(diào)用示例 select * from paramed_view(src, 'key');

上面的例子中，用戶的期望是?ORDER BY @b?被宏替換為?ORDER BY key，即根據(jù)輸入?yún)?shù)，決定了按照key列做排序。然而，實(shí)際上參數(shù)@b是作為一個(gè)值來傳遞的，ORDER BY @b?相當(dāng)于?ORDER BY 'key'，即 ORDER BY一個(gè)字符串常量('key')而不是一列。要想實(shí)現(xiàn)"讓調(diào)用者決定排序列"這一功能，可以考慮下述做法。

CREATE VIEW orderByFirstCol(@a TABLE(columnForOrder ANY, * ANY)) AS SELECT `(columnForOrder)?+.+` FROM (SELECT * FROM @a ORDER BY columnForOrder) t;-- 調(diào)用示例 select * from orderByFirstCol((select key, * from src));

上面的例子，要求調(diào)用者將要排序的列放在第一列，于是在調(diào)用的時(shí)候使用子查詢將src的需要排序的列抽取到最前面。視圖返回的?(columnForOrder)?+.+?是一個(gè)正則通配符，匹配columnForOrder之外的所有列，列表達(dá)式使用正則表達(dá)式可參考SELECT語法介紹>列表達(dá)式關(guān)于正則表達(dá)式的說明。

UDF：函數(shù)重載方式

問題

MaxCompute 的 UDF 使用重載 evalaute 方法的方式來重載函數(shù)，如下面的UDF定義了兩個(gè)重載，當(dāng)輸入是 String 類型時(shí)，輸出String類型，輸入是BIGINT類型時(shí)，輸出DOUBLE類型。

public UDFClass extends UDF {public String evaluate(String input) { return input + "123"; }public Double evaluate(Long input) { return input + 123.0; } }

這種方式固然能解決一些問題，但有一定的局限性。比如不支持泛型，要做一個(gè)接受任何類型的函數(shù)，就必須為每種類型都寫一個(gè)evaluate函數(shù)。有的時(shí)候重載甚至是不能實(shí)現(xiàn)的，比如ARRAY 和 ARRAY 的重載是做不到的。

public UDFClass extends UDF {public String evaluate(List<Long> input) { return input.size(); }// 這里會(huì)報(bào)錯(cuò)，因?yàn)樵趈ava類型擦除后，這個(gè)函數(shù)和 String evaluate(List<Long> input) 的參數(shù)是一樣的public Double evaluate(List<Double> input) { input.size(); } // UDF 不支持下面這種定義方式public String evaluate(List<Object> input) { return input.size(); } }

PYTHON UDF 或 UDTF 在不提供 Resolve 注解（annotation）的時(shí)候，會(huì)根據(jù)參數(shù)個(gè)數(shù)決定輸入?yún)?shù)，也支持變長，因此非常靈活。但也因?yàn)檫^于靈活，編譯器無法靜態(tài)找到某些錯(cuò)誤。比如

class Substr(object):def evaluate(self, a, b):return a[b:];

上面的函數(shù)接受兩個(gè)參數(shù)，從實(shí)現(xiàn)上看，第一個(gè)參數(shù)需要是STRING類型，第二個(gè)參數(shù)應(yīng)該是整形。而這個(gè)限制需要用戶在調(diào)用時(shí)自己去把握。即使用戶傳錯(cuò)了參數(shù)，編譯器也沒有辦法報(bào)錯(cuò)。同時(shí)，這種方式定義的UDF返回值類型只能是STRING，不夠靈活。

改進(jìn)

要解決上面的問題?？梢钥紤]使用UDT。 UDT經(jīng)常被簡(jiǎn)單在調(diào)用JDK中的方法的時(shí)候使用，比如?java.util.Objects.toString(x)?將任何對(duì)象 x 轉(zhuǎn)成STRING類型。但是在自定義函數(shù)方面同樣也有很好的用途。 UDT支持泛型，支持類繼承，支持變長等功能，讓定義函數(shù)更方便。如下面的例子：

public class UDTClass {// 這個(gè)函數(shù)接受一個(gè)數(shù)值類型（可以是 TINYINT, SMALLINT, INT, BIGINT, FLOAT, DOUBLE 以及任何以Number為基類的UDT)，返回DOUBLEpublic static Double doubleValue(Number input) {return input.doubleValue();}// 這個(gè)方法，接受一個(gè)數(shù)值類型參數(shù)和一個(gè)任意類型的參數(shù)，返回值類型與第二個(gè)參數(shù)的類型相同public static <T extends Number, R> R nullOrValue(T a, R b) {return a.doubleValue() > 0 ? b : null;}// 這個(gè)方法接受一個(gè)任意元素類型的array或List，返回BIGINTpublic static Long length(java.util.List<? extends Object> input) {return input.size();}// 注意這個(gè)在不做強(qiáng)制轉(zhuǎn)換的情況下參數(shù)只能接受 UDT 的 java.util.Map<Object, Object> 對(duì)象。如果需要傳入任何map對(duì)象，比如 map<bigint,bigint> 可以考慮:// 1. 定義函數(shù)時(shí)使用java.util.Map<? extends Object, ? extends Object>// 2. 調(diào)用時(shí)強(qiáng)轉(zhuǎn)，比如 UDTClass.mapSize(cast(mapObj as java.util.Map<Object, Object>))public static Long mapSize(java.util.Map<Object, Object> input) {return input.size();} }

UDT 能夠提供靈活的函數(shù)定義方式。但是有的時(shí)候UDF 需要通過?com.aliyun.odps.udf.ExecutionContext（在setup方法中傳入）來獲取一些上下文?，F(xiàn)在UDT也可以通過?com.aliyun.odps.udt.UDTExecutionContext.get()?方法來或者這樣的一個(gè) ExecutionContext 對(duì)象。

Aggregator 與 UDTF：Annotation方式

問題

MaxCompute 的 UDAF 和 UDTF 使用Resolve注解來決定函數(shù)Signature。比如下面的方式定義了一個(gè)UDTF，該UDTF接受一個(gè)BIGINT參數(shù)，返回DOUBLE類型。

@com.aliyun.odps.udf.annotation.Resolve("BIGINT->DOUBLE") public class UDTFClass extends UDTF {... }

這種方式的局限性很明顯，輸入?yún)?shù)和輸出參數(shù)都是固定的，沒辦法重載。

改進(jìn)

MaxCompute對(duì)Resolve注解的語法做了許多擴(kuò)展，現(xiàn)在能夠支持一定的靈活性。

• 參數(shù)列表中可以使用星號(hào)（'*')，表示接受任意長度的，任意類型的輸入?yún)?shù)。比如?@Resolve('double,*->String')，接受第一個(gè)是double，后接任意類型，任意個(gè)數(shù)的參數(shù)列表。這里需要UDF的作者在代碼里面自己去判斷輸入的個(gè)數(shù)和類型，然后做出相應(yīng)的動(dòng)作（可以對(duì)比 C 語言里面的 printf 函數(shù)來理解）。注意星號(hào)用在返回值列表中時(shí)，表示的是不同的含義，在后續(xù)第三點(diǎn)中說明。
• 參數(shù)列表中可以使用 ANY 關(guān)鍵字，表示任意類型的參數(shù)。比如?@Resolve('double,any->string')，接受第一個(gè)是double，第二個(gè)任意類型的參數(shù)列表。注意，ANY在返回值列表中不能使用，也不能在復(fù)雜類型的子類型中使用(如不能寫ARRAY)。
• UDTF的返回值可以使用星號(hào)，表示返回任意多個(gè)string類型。這里需要注意，返回值的個(gè)數(shù)并非真的是任意多個(gè)，而是與調(diào)用函數(shù)時(shí)給出的alias個(gè)數(shù)有關(guān)。比如@Resolve("ANY,ANY->DOUBLE,*")，調(diào)用方式是?UDTF(x, y) as (a, b, c)，這里as后面給出了三個(gè)alias (a, b, c)，編譯器會(huì)認(rèn)定a為double類型（annotation中返回值第一列的類型是給定的），b，c為string類型，而因?yàn)檫@里給出了三個(gè)返回值，所以UDTF在forward的時(shí)候，也一定要forward長度為3的數(shù)組，否則會(huì)出現(xiàn)運(yùn)行時(shí)錯(cuò)誤。注意這個(gè)錯(cuò)誤是無法在編譯時(shí)給出的，因此通常需要UDTF的作者與調(diào)用者互相溝通好，調(diào)用者在SQL中給出alias個(gè)數(shù)的時(shí)候，一定要按照UDTF的需要來寫。由于Aggregator返回值個(gè)數(shù)固定是1，所以這個(gè)功能對(duì)UDAF無意義。

用一個(gè)例子來說明。如下UDTF：

import com.aliyun.odps.udf.UDFException; import com.aliyun.odps.udf.UDTF; import com.aliyun.odps.udf.annotation.Resolve; import org.json.JSONException; import org.json.JSONObject;@Resolve("STRING,*->STRING,*") public class JsonTuple extends UDTF {private Object[] result = null;@Overridepublic void process(Object[] input) throws UDFException {if (result == null) {result = new Object[input.length];}try {JSONObject obj = new JSONObject((String)input[0]);for (int i = 1; i < input.length; i++) {// 返回值要求變長部分都是STRINGresult[i] = String.valueOf(obj.get((String)(input[i])));}result[0] = null;} catch (JSONException ex) {for (int i = 1; i < result.length; i++) {result[i] = null;}result[0] = ex.getMessage();}forward(result);} }

這個(gè)UDTF的返回值個(gè)數(shù)會(huì)根據(jù)輸入?yún)?shù)的個(gè)數(shù)來決定。輸出參數(shù)的第一個(gè)是一個(gè)JSON文本，后面是需要從JSON中解析的key。返回值第一個(gè)是解析JSON過程中的出錯(cuò)信息，如果沒有出錯(cuò)，則后續(xù)根據(jù)輸入的key依次輸出從json中解析出來的內(nèi)容。使用示例如下。

-- 根據(jù)輸入?yún)?shù)的個(gè)數(shù)定制輸出alias個(gè)數(shù) SELECT my_json_tuple(json, ’a‘, 'b') as exceptions, a, b FROM jsons;-- 變長部分可以一列都沒有 SELECT my_json_tuple(json) as exceptions, a, b FROM jsons;-- 下面這個(gè)SQL會(huì)出現(xiàn)運(yùn)行時(shí)錯(cuò)誤，因?yàn)閍lias個(gè)數(shù)與實(shí)際輸出個(gè)數(shù)不符 -- 注意編譯時(shí)無法發(fā)現(xiàn)這個(gè)錯(cuò)誤 SELECT my_json_tuple(json, 'a', 'b') as exceptions, a, b, c FROM jsons;

上面雖然做出了許多擴(kuò)展，但是這些擴(kuò)展并不一定能滿足所有的需求。這時(shí)候依然可以考慮使用UDT。UDT也是可以用來實(shí)現(xiàn)Aggregator和UDTF的功能的。詳細(xì)可以參考UDT示例文檔，“聚合操作的實(shí)現(xiàn)示例” 及 “表值函數(shù)的實(shí)現(xiàn)示例” 的內(nèi)容。

總結(jié)

MaxCompute自定義函數(shù)的函數(shù)原型不夠靈活，在數(shù)據(jù)開發(fā)過程中帶來諸多不便利，本文列舉了各種函數(shù)定義方式存在的問題與解決方案，希望對(duì)大家有幫助，同時(shí)也告訴大家MaxCompute一直在努力為大家提供更好的服務(wù)。

原文鏈接
本文為云棲社區(qū)原創(chuàng)內(nèi)容，未經(jīng)允許不得轉(zhuǎn)載。

總結(jié)

以上是生活随笔為你收集整理的ODPS2.0重装上阵，优化提升SQL语言表达能力的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。