DataWorks為您提供ODPS Script節點,其SQL開發模式是MaxCompute基於2.0的SQL引擎提供的指令碼開發模式。本文為您介紹ODPS Script節點的使用。
前提條件
已建立ODPS Script節點,詳情請參見建立並管理MaxCompute節點。
背景資訊
MaxCompute當前的SQL引擎支援指令碼模式SQL(Script Mode SQL),在指令碼模式下編譯指令碼時,一個多語句的SQL指令檔將被作為一個整體進行編譯,無需對單個語句進行編譯,適合用於改寫需要層層嵌套子查詢的單個語句,或因為指令碼複雜性而不得不拆成多個語句的指令碼。在提交運行時,SQL指令檔會被整體提交,並產生一個執行計畫,保證只需排隊一次、執行一次,讓您能充分利用MaxCompute的資源,詳情請參見SQL指令碼模式。在DataWorks中,您可通過ODPS Script節點實現MaxCompute SQL指令碼模式開發工作單位代碼,並調度其他作業的整合操作。
適用情境
指令碼模式的適用情境如下:
指令碼模式適合用來改寫需要層層嵌套子查詢的單個語句,或者因為指令碼複雜性而不得不拆成多個語句的指令碼。
如果多個輸入的資料來源資料準備完成的時間間隔很長(例如一個01:00可以準備好,一個07:00可以準備好),則不適合通過table variable銜接拼裝為一個大的指令碼模式SQL。
指令碼模式下,您可以對一個變數賦常量值,然後執行
SELECT * FROM 變數
語句轉化為標量與其它列進行計算。常量值也可以存放在一個單行的表中,命令樣本如下。轉化文法請參見子查詢(SUBQUERY)。@a := SELECT 10; --對@a賦值常量10,或者賦值存在一個單行表t1中,SELECT col1 FROM t1。 @b := SELECT key,value+(SELECT * FROM @a) FROM t2 WHERE key >10000; --t2表中value值與@a中的值進行計算。 SELECT * FROM @b;
文法結構
一個ODPS Script指令碼的完整形式為SET語句>DDL語句>DML語句,每種類型語句都可以有0到多個語句,但不同類型的語句不能混合。多個語句以@開始,表示變數串連。文法如下:
--SET語句
set odps.sql.type.system.odps2=true;
[set odps.stage.reducer.num=***;]
[...]
--DDL語句
create table table1 xxx;
[create table table2 xxx;]
[...]
--DML語句
@var1 := SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table3
[WHERE where_condition];
@var2 := SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table4
[WHERE where_condition];
@var3 := SELECT [ALL | DISTINCT] var1.select_expr, var2.select_expr, ...
FROM @var1 join @var2 on ...;
INSERT OVERWRITE|INTO TABLE [PARTITION (partcol1=val1, partcol2=val2 ...)]
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM @var3;
[@var4 := SELECT [ALL | DISTINCT] var1.select_expr, var.select_expr, ... FROM @var1
UNION ALL | UNION
SELECT [ALL | DISTINCT] var1.select_expr, var.select_expr, ... FROM @var2;
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
AS
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM var4;]
使用限制
ODPS Script節點的使用限制如下:
指令碼模式支援SET語句、部分DDL語句(結果是屏顯類型的語句除外,例如
desc
、show
)和DML語句。
一個指令碼,目前最多支援一個螢幕顯示結果的語句(例如單獨的Select語句),否則會報錯。不建議您在指令碼中執行螢幕顯示的Select語句。
一個指令碼,目前最多支援一個
Create table as
語句,並且必須是最後一句。建議將建表語句和Insert語句分開寫。指令碼模式下,如果有一個語句失敗,整個指令碼的語句都不會執行成功。
指令碼模式下,只有所有輸入的資料都準備完成,才會產生一個作業進行資料處理。
指令碼模式下,如果一個表被寫入後又被讀取,會報錯。
insert overwrite table src2 select * from src where key > 0; @a := select * from src2; select * from @a;
為避免先寫後讀,可以進行如下修改。
@a := select * from src where key > 0; insert overwrite table src2 select * from @a; select * from @a;
編輯代碼:簡單樣本
MaxCompute Script Mode的SQL編譯較為簡單,您只需要按照商務邏輯,用類似於普通程式設計語言的方式進行編譯,無需考慮如何組織語句。以下以一個簡單樣本為您介紹ODPS Script節點的使用。
create table if not exists dest(key string , value bigint) ;
create table if not exists dest2(key string,value bigint ) ;
@a := select * from src where value >0;
@b := select * from src2 where key is not null;
@c := select * from src3 where value is not null;
@d := select a.key,b.value from @a left outer join @b on a.key=b.key and b.value>0;
@e := select a.key,c.value from @a inner join @c on a.key=c.key;
@f := select * from @d union select * from @e union select * from @a;
insert overwrite table dest select * from @f;
@g := select e.key,c.value from @e join @c on e.key=c.key;
insert overwrite table dest2 SELECT * from @g;
本樣本中需要用到的三張表src、src1、src2,建表語句如下:
--建立表
create table if not EXISTS src(key string ,value BIGINT);
insert into src values ('1',11) ;
insert into src values ('1',11) ;
create table if not EXISTS src2(key string ,value BIGINT);
insert into src2 values ('1',22);
insert into src2 values ('2',22);
create table if not EXISTS src3(key string ,value BIGINT);
insert into src3 values ('1',33);
insert into src3 values ('3',33);
編輯代碼:進階樣本
MaxCompute指令碼模式支援IF語句,IF語句可以使程式根據條件,自動選擇執行邏輯。MaxCompute的IF文法根據Condition類型分為BOOLEAN類型,以及BOOLEAN的Scalar SubQuery。
IF條件為BOOLEAN類型運算式
這種類型的
IF ELSE
語句可以在編譯階段決定執行哪個分支,樣本如下:--資料處理 set odps.sql.allow.fullscan=true; set odps.optimizer.cbo.rule.filter.black=LM; @date := '${var}'; @row TABLE(key int,value bigint); --聲明變數row,其類型為Table,schema為string. IF ( cast(@date as bigint) % 2 == 0 ) BEGIN @row := SELECT key,value from src1; END ELSE BEGIN @row := SELECT key,value from src2; END INSERT OVERWRITE TABLE dest1 partition(p='${var}') SELECT key,value FROM @row;
說明代碼中定義了名為var的變數,您需要在調度參數配置處為var變數進行賦值。
IF條件為BOOLEAN的Scalar SubQuery
這種類型的
IF ELSE
語句在編譯階段無法決定執行哪個分支,在運行時才能決定。因此,需要提交多個作業,樣本如下:@i bigint; @t table(id bigint, value bigint); IF ((SELECT count(*) FROM src WHERE a = '5') > 1) BEGIN @i := 1; @t := select @i, @i*2; END ELSE BEGIN @i := 2; @t := select @i, @i*2; END select id, value from @t;
嵌入式UDF開發
此外,您也可以通過將Java或Python代碼嵌入SQL指令碼的方式,使用MaxCompute指令碼模式實現代碼嵌入式UDF(Embedded UDF)開發,詳情請參見UDF(嵌入式)。
後續步驟
當您完成當前節點的任務開發後,通常您可進行以下操作。
調度配置:配置節點的周期性調度屬性。任務需要周期性調度運行時,您需要設定節點後續實際運行過程中的重跑屬性、調度依賴關係等,操作詳情請參見任務調度屬性配置概述。
任務調試:對當前節點的代碼進行測試回合,確認代碼邏輯符合預期,操作詳情請參見任務調試流程。
任務發布:完成所有開發相關操作後,您需要將所有任務節點進行發布,發布後節點即會根據調度配置結果進行周期性運行,操作詳情請參見發布任務。