MaxCompute：SQL指令碼模式

指令碼模式支援兩種執行模式。

語句限制

• 屏顯語句：單個指令碼中僅允許包含一條有屏顯結果輸出的語句（如單獨的 SELECT語句），超出將觸發報錯。建議避免在指令碼中使用此類語句

• CREATE TABLE AS：每個指令碼僅允許一次，且必須是最後一條可執行語句。建議先建立表再插入資料。

• 混合寫入模式：同一指令碼中不支援同一張表同時使用 OVERWRITE 和 INTO。事務表和普通表的 DML 操作不能混合。

• 先寫後讀：普通模式下對錶先寫入再讀取會報錯。解決方案：使用表變數替代，或切換為逐步執行模式。逐步執行模式下支援先寫後讀，但事務表和分區表不支援。詳細樣本請參見樣本 3：普通模式下的先寫後讀。

變數

-- 使用 @ 聲明表變數，支援TABLE類型，或任意Maxcompute資料類型
@var1 <type>
-- 使用 := 賦值：
@var1 := <select_statement>

IF 語句

指令碼模式下，支援IF語句：IF語句可以使程式根據條件，自動選擇執行邏輯。

-- 單分支
IF (condition) BEGIN
  statements
END
-- 多分支
IF (condition) BEGIN
  statements
END ELSE IF (condition2) BEGIN
  statements
END ELSE BEGIN
  statements
END

@date := '20190101';
@row TABLE(id 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;

@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;

樣本 1：基礎樣本

以下指令碼對三張源表進行 JOIN 和 UNION 操作，將結果插入兩張目標表。所有語句編譯為一個 DAG，原子性執行。

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;

樣本 2：逐步執行模式下的先寫後讀

以下指令碼開啟逐步執行模式，實現在同一指令碼中先寫入表再讀取。

SET odps.sql.step.script.mode=true;
DROP TABLE IF EXISTS foo_t1;
DROP TABLE IF EXISTS foo_t2;
CREATE TABLE foo_t1(a STRING) LIFECYCLE 1;
CREATE TABLE foo_t2(a STRING) LIFECYCLE 1;
@x := SELECT 'hello, world' AS a;
INSERT OVERWRITE TABLE foo_t1 SELECT * FROM @x;
INSERT INTO foo_t2 SELECT * FROM foo_t1 UNION ALL SELECT * FROM foo_t1;
SELECT * FROM foo_t2;

樣本 3：普通模式下的先寫後讀

普通模式下在同一指令碼中先寫入表再讀取會報錯。以下樣本展示該錯誤及兩種解決方案。

資料準備

CREATE TABLE src(key BIGINT, value BIGINT) LIFECYCLE 1;
CREATE TABLE src2(key BIGINT, value BIGINT) LIFECYCLE 1;
INSERT INTO src VALUES(1, 2), (3, 3);

報錯樣本（普通模式下先寫後讀）

INSERT OVERWRITE TABLE src2 SELECT * FROM src WHERE key > 0;
@a := SELECT * FROM src2;   -- 報錯：src2 在同一指令碼中先被寫入再被讀取
SELECT * FROM @a;

解決方案 1：開啟逐步執行模式

SET odps.sql.step.script.mode=true;
INSERT OVERWRITE TABLE src2 SELECT * FROM src WHERE key > 0;
@a := SELECT * FROM src2;
SELECT * FROM @a;

解決方案 2：改寫 SQL，使用表變數避免先寫後讀

@a := SELECT * FROM src WHERE key > 0;
INSERT OVERWRITE TABLE src2 SELECT * FROM @a;
SELECT * FROM @a;

樣本 4：建立和刪除暫存資料表

以下指令碼建立暫存資料表緩衝 JOIN 中間結果，讀取兩次後刪除。

-- 開啟逐步執行模式（暫存資料表必須）
SET odps.sql.step.script.mode=true;
DROP TABLE IF EXISTS foo_t1;
CREATE TABLE foo_t1(a BIGINT, b BIGINT);
-- 通過 JOIN 建立暫存資料表
CREATE TEMPORARY TABLE t AS
SELECT t1.a AS a, t2.d AS b FROM
  (SELECT 1 a, 2 b) t1
JOIN
  (SELECT 1 c, 10 d) t2
ON t1.a = t2.c;
INSERT INTO foo_t1 SELECT * FROM t UNION ALL SELECT * FROM t;
SELECT * FROM foo_t1;
-- 刪除暫存資料表（必須放在指令碼末尾）
DROP TABLE t;