全部產品
Search
文件中心

MaxCompute:SELECT TRANSFORM

更新時間:Feb 28, 2024

select transform文法允許您啟動一個指定的子進程,將輸入資料按照一定的格式通過標準輸入至子進程,並且通過解析子進程的標準輸出擷取輸出資料。select transform讓您無需編寫UDF,即可實現MaxCompute SQL對其他指令碼語言的支援。

功能介紹

select transform與UDTF在不同情境下的效能不同。經過多種情境對比測試,資料量較小時,大多數情境下select transform有優勢,而資料量大時UDTF有優勢。select transform的開發更加簡便,更適合於Ad Hoc(即席查詢)。

select transform不僅僅是語言支援的擴充。一些簡單的功能,例如AWK、Python、Perl、Shell都支援直接在命令中寫指令碼,而不需要專門編寫指令檔、上傳資源等,開發過程更簡單。對於複雜的功能,您可以上傳指令檔來執行,請參見調用Python指令碼使用樣本調用Java指令碼使用樣本

UDTF與select transform的優勢對比如下。

類型

select transform

UDTF

資料類型

子進程基於標準輸入和標準輸出傳輸資料,所有資料都當做STRING類型處理,因此select transform比UDTF多了一步類型轉換。

UDTF的輸出結果和輸入參數支援多種資料類型。

資料轉送

資料轉送依賴於作業系統的管道,而管道的緩衝僅有4 KB且不能設定。select transform讀空或管道寫滿會導致進程被掛起。

資料的傳輸通過更底層的系統調用來讀寫,效率比Java高。

無管道緩衝限制。

常量參數傳輸

常量參數需要傳輸。

常量參數可以不用傳輸。

線程

子進程和父進程是兩個進程,如果計算佔比較高,資料輸送量較小,select transform可以利用伺服器的多核特性。

單線程。

效能

select transform支援的部分工具,例如AWK是Native代碼實現的。理論上,與Java相比,使用select transform會更有效能優勢。

效能不高。

使用限制

由於MaxCompute計算叢集上未部署PHP和Ruby,所以不支援調用這兩種指令碼。

命令格式

select transform(<arg1>, <arg2> ...) 
[(row format delimited (fields terminated by <field_delimiter> (escaped by <character_escape>)) (null defined as <null_value>))]
using '<unix_command_line>' 
(resources '<res_name>' (',' '<res_name>')*)
[(as <col1>, <col2> ...)]
(row format delimited (fields terminated by <field_delimiter> (escaped by <character_escape>)) (null defined as <null_value>))
  • select transform關鍵字:必填。可以用mapreduce關鍵字替換,語義是完全一樣的。為使文法更清晰,推薦您使用select transform

  • arg1,arg2...:必填。指定輸入資料。其格式和select語句類似。預設格式下,參數的各個運算式結果在隱式轉換成STRING類型後,用\t拼接,輸入到子進程中。

  • row format子句:可選。允許自訂輸入輸出的格式。

    文法中有兩個row format子句,第一個子句指定輸入資料的格式,第二個子句指定輸出資料的格式。預設情況下使用\t作為資料行分隔符號,\n作為行分隔字元,使用\N表示NULL。

    說明
    • field_delimitercharacter_escape只接受一個字元。如果指定的是字串,則以第一個字元為準。

    • MaxCompute支援Hive指定格式的文法,例如inputRecordReaderoutputRecordReaderSerDe等,但您需要開啟Hive相容模式才能使用。開啟檔案為在SQL語句前加set語句set odps.sql.hive.compatible=true;。Hive支援的文法詳情請參見Hive文檔

    • 如果使用Hive的inputRecordReaderoutputRecordReader等自訂類,可能會降低執行效能。

  • using子句:必填。指定要啟動的子進程的命令。

    • 大多數的MaxCompute SQL命令中using子句指定的是資源(Resources),但此處使用using子句指定啟動子進程的命令。使用using子句是為了和Hive的文法相容。

    • using子句的格式和Shell文法類似,但並非真的啟動Shell來執行,而是直接根據命令的內容建立子進程。因此,很多Shell的功能不能使用,例如輸入輸出重新導向、管道、迴圈等。如果有需要,Shell本身也可以作為子進程命令來使用。

  • resources子句:可選。允許指定子進程能夠訪問的資源,支援以下兩種方式指定資源:

    • 使用resources子句指定資源。例如using 'sh foo.sh bar.txt' resources 'foo.sh','bar.txt'

    • 使用MaxCompute屬性指定資源。在SQL語句前使用set odps.sql.session.resources=foo.sh,bar.txt;來指定資源。

      此配置是全域配置,即整個SQL中所有的select transform都可以訪問此資源。多個資源檔之間使用英文逗號(,)分隔。

  • as子句:可選。指定輸出資料行。例如as(col1 bigint, col2 boolean)

    • 輸出資料行可以不指定資料類型,預設為STRING類型。例如as(col1, col2)

    • 由於輸出資料實際是解析子進程標準輸出擷取的,如果指定的資料不是STRING類型,系統會隱式調用cast函數進行轉換,轉換過程有可能出現運行異常。

    • 輸出資料行的資料類型不支援部分指定,例如as(col1, col2 bigint)

    • 關鍵字as可以省略,此時預設標準輸出資料中第一個\t之前的欄位為Key,後面的部分全部為Value,相當於as(key, value)

調用Shell命令使用樣本

假設通過Shell命令產生50行資料,值是從1到50,輸出為data欄位。直接將Shell命令作為transform資料輸入。命令樣本如下:

select transform(script) using 'sh' as (data) 
from (
        select  'for i in `seq 1 50`; do echo $i; done' as script
      ) t
;
--等效於如下語句。
select transform('for i in `seq 1 50`; do echo $i; done') using 'sh' as (data);

返回結果如下:

+------------+
| data       |
+------------+
| 1          |
| 2          |
| 3          |
| 4          |
| 5          |
| 6          |
| 7          |
| 8          |
| 9          |
| 10         |
| 11         |
| 12         |
| 13         |
| 14         |
| 15         |
| 16         |
| 17         |
| 18         |
| 19         |
| 20         |
| 21         |
| 22         |
| 23         |
| 24         |
| 25         |
| 26         |
| 27         |
| 28         |
| 29         |
| 30         |
| 31         |
| 32         |
| 33         |
| 34         |
| 35         |
| 36         |
| 37         |
| 38         |
| 39         |
| 40         |
| 41         |
| 42         |
| 43         |
| 44         |
| 45         |
| 46         |
| 47         |
| 48         |
| 49         |
| 50         |
+------------+

調用Python命令使用樣本

假設通過Python命令產生50行資料,值是從1到50,輸出為data欄位。直接將Python命令作為transform資料輸入。命令樣本如下:

select transform(script) using 'python' as (data) 
from (
        select  'for i in xrange(1, 51):  print i;' as script
      ) t
;
--等效於如下語句。
select transform('for i in xrange(1, 51):  print i;') using 'python' as (data);

返回結果如下:

+------------+
| data       |
+------------+
| 1          |
| 2          |
| 3          |
| 4          |
| 5          |
| 6          |
| 7          |
| 8          |
| 9          |
| 10         |
| 11         |
| 12         |
| 13         |
| 14         |
| 15         |
| 16         |
| 17         |
| 18         |
| 19         |
| 20         |
| 21         |
| 22         |
| 23         |
| 24         |
| 25         |
| 26         |
| 27         |
| 28         |
| 29         |
| 30         |
| 31         |
| 32         |
| 33         |
| 34         |
| 35         |
| 36         |
| 37         |
| 38         |
| 39         |
| 40         |
| 41         |
| 42         |
| 43         |
| 44         |
| 45         |
| 46         |
| 47         |
| 48         |
| 49         |
| 50         |
+------------+

調用AWK命令使用樣本

建立一張測試表,假設通過AWK命令將測試表的第二列原樣輸出,輸出為data欄位。直接將AWK命令作為transform資料輸入。命令樣本如下:

--建立測試表。
create table testdata(c1 bigint,c2 bigint);
--測試表中插入測試資料。 
insert into table testdata values (1,4),(2,5),(3,6); 
--執行select transform語句。 
select transform(*) using "awk '//{print $2}'" as (data) from testdata;

返回結果如下:

+------------+
| data       |
+------------+
| 4          |
| 5          |
| 6          |
+------------+

調用Perl命令使用樣本

建立一張測試表,假設通過Perl命令將測試表的資料原樣輸出,輸出為data欄位。直接將Perl命令作為transform資料輸入。命令樣本如下:

--建立測試表。
create table testdata(c1 bigint,c2 bigint);
--測試表中插入測試資料。 
insert into table testdata values (1,4),(2,5),(3,6); 
--執行select transform語句。 
select transform(testdata.c1, testdata.c2) using "perl -e 'while($input = <STDIN>){print $input;}'" from testdata;

返回結果如下:

+------------+------------+
| key        | value      |
+------------+------------+
| 1          | 4          |
| 2          | 5          |
| 3          | 6          |
+------------+------------+

調用Python指令碼使用樣本

  1. 準備Python檔案,指令檔名為myplus.py,命令樣本如下。

    #!/usr/bin/env python
    import sys
    line = sys.stdin.readline()
    while line:
        token = line.split('\t')
        if (token[0] == '\\N') or (token[1] == '\\N'):
            print '\\N'
        else:
            print str(token[0]) +'\t' + str(token[1])
        line = sys.stdin.readline()
  2. 將該Python指令檔添加為MaxCompute資源(Resource)。

    add py ./myplus.py -f;
    說明

    您也可通過DataWorks控制台進行新增資源操作,請參見建立並使用MaxCompute資源

  3. 使用select transform文法調用資源。

    --建立測試表。
    create table testdata(c1 bigint,c2 bigint);
    --測試表中插入測試資料。 
    insert into table testdata values (1,4),(2,5),(3,6); 
    --執行select transform語句。 
    select 
    transform (testdata.c1, testdata.c2) 
    using 'python myplus.py' resources 'myplus.py' 
    as (result1,result2) 
    from testdata;
    --等效於如下語句。
    set odps.sql.session.resources=myplus.py;
    select transform (testdata.c1, testdata.c2) 
    using 'python myplus.py' 
    as (result1,result2) 
    from testdata;

    返回結果如下:

    +------------+------------+
    | result1    | result2    |
    +------------+------------+
    | 1          | 4          |
    |            | NULL       |
    | 2          | 5          |
    |            | NULL       |
    | 3          | 6          |
    |            | NULL       |
    +------------+------------+

調用Java指令碼使用樣本

  1. 準備好JAR檔案,指令檔名為Sum.jar,Java程式碼範例如下。

    package com.aliyun.odps.test;
    import java.util.Scanner;
    public class Sum {
        public static void main(String[] args) {
            Scanner sc = new Scanner(System.in);
            while (sc.hasNext()) {
                String s = sc.nextLine();
                String[] tokens = s.split("\t");
                if (tokens.length < 2) {
                    throw new RuntimeException("illegal input");
                }
                if (tokens[0].equals("\\N") || tokens[1].equals("\\N")) {
                    System.out.println("\\N");
                }
                System.out.println(Long.parseLong(tokens[0]) + Long.parseLong(tokens[1]));
            }
        }
    }
  2. 將JAR檔案添加為MaxCompute的資源。

    add jar ./Sum.jar -f;
  3. 使用select transform文法調用資源。

    --建立測試表。
    create table testdata(c1 bigint,c2 bigint); 
    --測試表中插入測試資料。
    insert into table testdata values (1,4),(2,5),(3,6); 
    --執行select transform語句。
    select transform(testdata.c1, testdata.c2) using 'java -cp Sum.jar com.aliyun.odps.test.Sum' resources 'Sum.jar' as cnt from testdata;
    --等效於如下語句。
    set odps.sql.session.resources=Sum.jar; 
    select transform(testdata.c1, testdata.c2) using 'java -cp Sum.jar com.aliyun.odps.test.Sum' as cnt from testdata;

    返回結果如下:

    +-----+
    | cnt |
    +-----+
    | 5   |
    | 7   |
    | 9   |
    +-----+
說明

Java和Python雖然有現成的UDTF架構,但是用select transform編寫更簡單,不需要額外依賴以及沒有格式要求,甚至可以實現直接使用離線指令碼。Java和Python離線指令碼的實際路徑,可以從JAVA_HOMEPYTHON_HOME環境變數中得到。

串聯使用樣本

select transform還可以串聯使用。例如使用distribute bysort by對輸入資料做預先處理。命令樣本如下:

select transform(key, value) using '<cmd2>' from 
(
    select transform(*) using '<cmd1>' from 
    (
        select * from testdata distribute by c2 sort by c1 
    ) t distribute by key sort by value 
) t2;

cmd1cmd2為要啟動的子進程的命令。

或使用mapreduce關鍵字。

@a := select * from data distribute by col2 sort by col1;
@b := map * using 'cmd1' distribute by col1 sort by col2 from @a;
reduce * using 'cmd2' from @b;