使用指令碼模式運行SQL - MaxCompute

當面對巨量資料集的ETL任務、自動化定期任務、複雜查詢編排等情境時，可以使用MaxCompute當前SQL引擎支援的指令碼模式（Script Mode SQL）。在指令碼模式下，一個多語句的SQL指令檔將被作為一個整體進行編譯，無需對單個語句進行編譯；提交運行時，SQL指令檔會被整體提交，並產生一個執行計畫，保證只需排隊一次、執行一次，充分利用MaxCompute的資源，在提升工作效率的同時增強資料處理和分析工作流程的靈活性與安全性。

說明

指令碼模式SQL無法使用計量預估完成費用預估，具體費用請以實際費用賬單為準，詳情請參見查看賬單詳情。
同一個指令碼中，引用的表不能超過10000個，如果包含View，會計算建View時引用了多少張表。同一張表出現多次，按出現次數算表數。

Script Mode的SQL語句書寫便利，您只需要按照商務邏輯，用類似於普通程式設計語言的方式書寫，無需考慮如何組織語句。

適用情境

指令碼模式適合用來改寫本來要用層層嵌套子查詢的單個語句，或者因為指令碼複雜性而不得不拆成多個語句的指令碼。
如果多個輸入的資料來源資料準備完成的時間間隔很長（例如一個01:00可以準備好，一個07:00可以準備好），則不適合通過table variable銜接拼裝為一個大的指令碼模式SQL。

文法結構

-- SET 
SET odps.sql.type.system.odps2=true;
[SET odps.stage.reducer.num=xxx;]
[...]
-- 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, var1.select_expr, ... FROM @var1 
        UNION ALL | UNION 
        SELECT [ALL | DISTINCT] var2.select_expr, var2.select_expr, ... FROM @var2;    
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name 
        AS 
        SELECT [ALL | DISTINCT] select_expr, select_expr, ...
        FROM @var4;]
[...]

文法說明

語句類型
指令碼模式支援SET語句、部分DDL語句（不支援結果為屏顯類型的語句如DESC、SHOW）、DML語句。
語句順序指令碼的完整形式是SET → DDL → DML 的固定順序，每個語句類型都可以包含0到多個具體的SQL語句，但是不同類型的語句塊不能混用。
指令碼執行
- 原子性執行：指令碼模式下，任一語句失敗，整個指令碼所有操作均不生效。
- 統一作業：指令碼模式下，只有當所有輸入的資料都準備好並插入成功，才會產生一個作業進行統一資料處理。
變數使用規範
- 使用 @ 標記法宣告變數。
- 指令碼模式下，不支援將table類型變數的值賦值給其他規定了資料類型的變數，樣本如下：
```
@a TABLE (name STRING);
@a:= SELECT 'tom';
@b STRING;
@b:= SELECT * FROM @a;
```
- 指令碼模式下，可以對一個變數賦常量值，然後執行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;
```
關鍵語句限制
- 一個指令碼中最多支援一個屏顯結果的語句（如單獨的SELECT語句），否則會發生報錯。不建議在指令碼中執行屏顯的SELECT語句。
- 一個指令碼中CREATE TABLE AS 語句最多隻能出現一次，且必須是指令碼的最後一條執行語句。推薦將建表與插入操作分開編寫。
- 在同一個指令碼中，暫不支援對同一個表同時做overwrite和into操作，以及同時對transaction表和普通表做DML操作。
- 指令碼模式支援IF語句：IF語句可以使程式根據條件，自動選擇執行邏輯。
  - MaxCompute的IF文法有如下幾種類型。
```
IF (condition) BEGIN
  statement 1
  statement 2
  ...
END

IF (condition) BEGIN
  statements
END ELSE IF (condition2) BEGIN
  statements
END ELSE BEGIN
  statements
END
```
    說明
    BEGIN和END內部只包含1條語句時，關鍵字BEGIN、END可以省略。類似於Java中的代碼塊{ }。
    IF文法中各分支內statements不支援DDL語句，如CREATE TABLE、ALTER TABLE和TRUNCATE TABLE等。
  - IF語句中的Condition類型分為以下兩種：
    BOOLEAN類型的運算式。這種類型的IF ELSE語句可以在編譯階段決定執行哪個分支，樣本如下：
```
-- 
date := '20190101';
@row  TABLE(id STRING); --聲明變數row，其類型為Table，schema為string. 
IF ( CAST(@date  AS BIGINT) % 2 == 0 ) BEGIN 
@row  := SELECT id FROM src1; 
END ELSE BEGIN
@row  := SELECT id FROM src2; 
END
INSERT OVERWRITE TABLE dest SELECT * FROM @row; 
```
    類型為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; 
```
- 指令碼模式下，如果一個表先被寫再被讀，則會發生報錯，如下所示：
```
-- 先被寫再被讀，則會發生報錯
INSERT OVERWRITE TABLE src2 SELECT * FROM src WHERE key > 0;
@a := SELECT * FROM src2;
SELECT * FROM @a;

-- 所以，為避免因表的先寫後讀產生的報錯，應修改SQL指令碼如下:
@a := SELECT * FROM src WHERE key > 0;
INSERT OVERWRITE TABLE src2 SELECT * FROM @a;
SELECT * FROM @a;
```

使用樣本

指令碼模式SQL樣本如下。

CREATE TABLE IF NOT EXISTS dest(key STRING, value BIGINT) PARTITIONED BY (d STRING);
CREATE TABLE IF NOT EXISTS dest2(key STRING, value BIGINT) PARTITIONED  BY (d STRING);
@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 PARTITION (d='20171111') SELECT * FROM @f;
@g := SELECT e.key,c.value FROM @e JOIN @c ON e.key=c.key;
INSERT OVERWRITE TABLE dest2 PARTITION (d='20171111') SELECT * FROM @g;

工具支援

支援通過MaxCompute Studio、MaxCompute用戶端（odpscmd）、DataWorks以及SDK使用SQL指令碼模式。使用方式如下。

通過MaxCompute Studio使用指令碼模式。

使用MaxCompute Studio指令碼模式，首先請保證MaxCompute Studio完成安裝、添加專案連結、建立MaxCompute SQL指令檔，詳情請參見安裝IntelliJ IDEA、管理專案串連、建立MaxCompute Script Module。

指令碼編譯後提交運行，查看執行計畫圖。雖然指令碼上是多個語句，但執行計畫圖是同一個DAG圖。

通過MaxCompute用戶端（odpscmd）使用指令碼模式。

您需要使用0.27以上版本的odpscmd提交指令碼。建議您安裝最新版本MaxCompute用戶端安裝包。安裝後，請使用-s參數提交指令碼。

編輯指令碼模式的源碼myscript.sql檔案，在系統命令列視窗調用odpscmd執行如下命令。更多通過系統命令列視窗運行MaxCompute用戶端的操作，請參見運行MaxCompute用戶端。

..\bin>odpscmd -s myscript.sql

說明

-s為odpscmd的命令列選項，類似於-f、-e，而非互動環境中的命令。odpscmd的互動環境中暫不支援指令碼模式與表變數。

通過DataWorks使用指令碼模式。

在DataWorks中可以建立指令碼模式的節點ODPS Script，樣本如下。指令碼節點

在此節點中進行指令碼模式編輯，編輯完成後單擊工具列的運行表徵圖，提交指令碼到MaxCompute執行。從輸出資訊的Logview URL中可以查看執行計畫圖和結果。

通過SDK使用指令碼模式。

在Java/Python SDK中可以直接執行一個SQL指令碼，Java SDK詳情請參見Java SDK介紹，Python SDK詳情請參見Python SDK介紹。程式碼範例如下。

Java

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.aliyun.odps.Instance;
import com.aliyun.odps.Odps;
import com.aliyun.odps.OdpsException;
import com.aliyun.odps.account.Account;
import com.aliyun.odps.account.AliyunAccount;
import com.aliyun.odps.data.Record;
import com.aliyun.odps.task.SQLTask;

public class SdkTest {

  public static void main(String[] args) throws OdpsException {
		// 阿里雲帳號AccessKey擁有所有API的存取權限，風險很高。強烈建議您建立並使用RAM使用者進行API訪問或日常營運，請登入RAM控制台建立RAM使用者
		// 此處以把AccessKey 和 AccessKeySecret 儲存在環境變數為例說明。您也可以根據業務需要，儲存到設定檔裡
		// 強烈建議不要把 AccessKey 和 AccessKeySecret 儲存到代碼裡，會存在密鑰泄漏風險
    Account account = new AliyunAccount(System.getenv("ALIBABA_CLOUD_ACCESS_KEY_ID"), System.getenv("ALIBABA_CLOUD_ACCESS_KEY_SECRET"));
    Odps odps = new Odps(account);
    odps.setDefaultProject("your project_name");
    odps.setEndpoint("your end_point");

    String sqlScript = "@a := SELECT * FROM jdbc_test;\n"
                       + "SELECT * FROM @a;";

    //一定要加這一行配置
    Map<String, String> hints = new HashMap<>();
    hints.put("odps.sql.submit.mode", "script");

    Instance instance = SQLTask.run(odps, "your project_name", sqlScript, hints, null);
    instance.waitForSuccess();

    List<Record> recordList = SQLTask.getResult(instance);
    for (Record record : recordList) {
      System.out.println(record.get(0));
      System.out.println(record.get(1));
    }
  }

}

Python

import os
from odps import ODPS


# 阿里雲帳號AccessKey擁有所有API的存取權限，風險很高。強烈建議您建立並使用RAM使用者進行API訪問或日常營運，請登入RAM控制台建立RAM使用者
# 此處以把AccessKey 和 AccessKeySecret 儲存在環境變數為例說明。您也可以根據業務需要，儲存到設定檔裡
# 強烈建議不要把 AccessKey 和 AccessKeySecret 儲存到代碼裡，會存在密鑰泄漏風險
o = ODPS(
    os.environ["ALIBABA_CLOUD_ACCESS_KEY_ID"],
    os.environ["ALIBABA_CLOUD_ACCESS_KEY_SECRET"],
    "your project_name",
    "your end_point"
)

sql_script = """
@a := SELECT * FROM jdbc_test;
SELECT * FROM @a;
"""

# 一定要加這一行配置
hints = {"odps.sql.submit.mode", "script"}
instance = o.execute_sql(sql_script, hints=hints)

with instance.open_reader() as reader:
    for rec in reader:
        print(rec[0], rec[1])