MaxCompute:Script mode SQL

Script mode supports two execution modes.

Statement limitations

• Result-displaying statements: A script can contain only one statement that produces screen output, such as a standalone SELECT statement. Including more than one causes an error. Avoid using these types of statements in scripts.

• CREATE TABLE AS: You can use this statement only once per script, and it must be the last executable statement. We recommend creating the table first and then inserting data.

• Mixed write modes: You cannot use both OVERWRITE and INTO on the same table within the same script. DML operations on transactional tables and standard tables cannot be mixed.

• read-after-write: In normal mode, writing to and then reading from a table in the same script causes an error. To resolve this issue, either use a table variable or switch to step-by-step execution mode. The step-by-step execution mode supports the read-after-write operation, but not for transactional tables or partitioned tables. For a detailed example, see Example 3: Read-after-write in normal mode.

Variables

-- Declare a variable by using @. Supported types include TABLE and any MaxCompute data type.
@var1 <type>
-- Assign a value by using :=
@var1 := <select_statement>

IF statements

In script mode, use IF statements to control the execution flow based on conditions.

-- Single branch
IF (condition) BEGIN
  statements
END
-- Multi-branch
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;

Example 1: Basic example

The following script joins and unions data from three source tables, and then inserts the results into two destination tables. All statements are compiled into a single DAG and executed atomically.

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;

Example 2: Read-after-write in step-by-step mode

The following script enables the step-by-step execution mode to perform a read-after-write operation in the same script.

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;

Example 3: Read-after-write in normal mode

In normal mode, writing to and then reading from the same table in a script causes an error. This example demonstrates the error and two possible solutions.

Data preparation

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

Error example (read-after-write in normal mode)

INSERT OVERWRITE TABLE src2 SELECT * FROM src WHERE key > 0;
@a := SELECT * FROM src2;   -- Error: src2 is written to and then read from in the same script.
SELECT * FROM @a;

Solution 1: Enable step-by-step execution mode

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

Solution 2: Rewrite the SQL to use a table variable

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

Example 4: Create and delete a temporary table

The following script creates a temporary table to cache the intermediate results of a JOIN, reads from it twice, and then drops it.

-- Enable step-by-step execution mode (required for temporary tables).
SET odps.sql.step.script.mode=true;
DROP TABLE IF EXISTS foo_t1;
CREATE TABLE foo_t1(a BIGINT, b BIGINT);
-- Create a temporary table by using a 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 the temporary table (must be the last statement in the script).
DROP TABLE t;