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;