MaxCompute:SQL in script mode

When to use script mode

Script mode fits two main scenarios:

• Complex single-statement rewrites — replace deeply nested subqueries with a sequence of readable table variable assignments.

• Multi-statement pipelines — combine statements that logically belong together into one atomic job, so they either all succeed or all roll back.

If data from multiple data sources is prepared at long intervals (for example, one is ready at 01:00 and another at 07:00), do not use a table variable to assemble a large SQL script in script mode. In that case, keep the statements in separate jobs.

Script structure

A script in script mode follows a fixed structure. The sections must appear in this order: SET → DDL → DML. Each section can be empty, but sections cannot be interleaved.

-- 1. SET: configure session-level properties
SET odps.sql.type.system.odps2=true;
[SET odps.stage.reducer.num=xxx;]

-- 2. DDL: create tables (optional)
CREATE TABLE table1 ...;

-- 3. DML: declare variables and insert results
@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 (...)]
    SELECT [ALL | DISTINCT] select_expr, select_expr, ... FROM @var3;

-- Additional patterns
[@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 ... FROM @var4;]

Execution semantics

Table variables

Declare table variables with @ and assign results using :=:

@var1 := SELECT * FROM src WHERE key > 0;

Key rules:

• In script mode, you cannot assign a table type variable to another variable with a specified data type. For example, the following is not allowed:

  @a TABLE (name STRING);
  @a := SELECT 'tom';
  @b STRING;
  @b := SELECT * FROM @a;

• A variable can hold a constant value. Use SELECT * FROM @var to convert it into a scalar for arithmetic with other columns:

  @a := SELECT 10;                                         -- assign constant 10
  @b := SELECT key, value + (SELECT * FROM @a) FROM t2;   -- use @a as scalar
  SELECT * FROM @b;

Restrictions

Read-after-write — problem and fix:

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

-- Fix: write to a variable, then use the variable for both the insert and the read.
@a := SELECT * FROM src WHERE key > 0;
INSERT OVERWRITE TABLE src2 SELECT * FROM @a;
SELECT * FROM @a;

IF statements

Script mode supports conditional branching. MaxCompute supports two IF syntaxes:

-- Single branch
IF (condition) BEGIN
  statement1
  statement2
END

-- Multi-branch
IF (condition) BEGIN
  statements
END ELSE IF (condition2) BEGIN
  statements
END ELSE BEGIN
  statements
END

If a branch contains only one statement, BEGIN and END are optional (similar to { } in Java). DDL statements such as CREATE TABLE, ALTER TABLE, and TRUNCATE TABLE are not supported inside IF branches.

The condition can be one of two types:

Boolean expression — the branch is resolved at compile time:

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

Boolean scalar subquery — the branch is resolved at runtime, so MaxCompute may submit multiple jobs:

@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

The following script joins and unions data from three source tables, then inserts results into two destination tables. All statements compile as a single DAG and execute 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;

Run scripts in supported tools

Script mode is available in MaxCompute Studio, the MaxCompute client (odpscmd), DataWorks, and the Java and Python SDKs.

MaxCompute Studio

Before running a script in MaxCompute Studio, complete the setup:

1. Install IntelliJ IDEA

2. Add a project connection

3. Create a MaxCompute Script module

After you compile and run the script, MaxCompute Studio displays the execution plan. Although the script contains multiple statements, the plan renders as a single DAG.

..\bin>odpscmd -s myscript.sql

DataWorks

In DataWorks, create an ODPS Script node. Edit the script in the node editor, then click the Run icon to submit it to MaxCompute. View the execution plan and results from the Logview URL in the output panel.

For setup details, see Create an ODPS Script node.

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 {
    // Store credentials in environment variables. Do not hard-code AccessKey pairs.
    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>");
    odps.setEndpoint("<your-endpoint>");

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

    // Required: enable script mode
    Map<String, String> hints = new HashMap<>();
    hints.put("odps.sql.submit.mode", "script");

    Instance instance = SQLTask.run(odps, "<your-project>", 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));
    }
  }

}

import os
from odps import ODPS

# Store credentials in environment variables. Do not hard-code AccessKey pairs.
o = ODPS(
    os.environ["ALIBABA_CLOUD_ACCESS_KEY_ID"],
    os.environ["ALIBABA_CLOUD_ACCESS_KEY_SECRET"],
    "<your-project>",
    "<your-endpoint>"
)

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

# Required: enable script mode
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])