DataWorks:Develop a MaxCompute script task

Variable syntax

Assign query results to a variable using the := operator:

@<variable_name> := <SELECT statement>;

To assign a constant to a variable and use it as a scalar value, combine SELECT <constant> with a subsequent SELECT * FROM @variable. For example:

@a := SELECT 10; -- Assign the constant 10 to @a. You can also use SELECT col1 FROM t1 to assign a value from a single-row table.
@b := SELECT key, value + (SELECT * FROM @a) FROM t2 WHERE key > 10000;
SELECT * FROM @b;

For more information about this syntax, see Subqueries.

Supported statement types

ODPS Script nodes support SET statements, DML statements, and a subset of DDL statements. DDL statements that display results — such as DESC and SHOW — are not supported.

Result-displaying statements

Write at most one result-displaying statement (such as a SELECT statement) per script. Writing multiple result-displaying statements causes an error.

Error:

SELECT * FROM src1;
SELECT * FROM src2; -- Second result-displaying statement causes an error.

Fix: Keep only one result-displaying SELECT, or avoid using result-displaying SELECT statements in production scripts altogether.

CREATE TABLE AS

Write at most one CREATE TABLE AS statement per script, and place it as the last statement. To keep scripts maintainable, write CREATE TABLE statements and INSERT statements in separate sections.

Script failure behavior

If any statement in a script fails, the entire script fails.

Job generation

MaxCompute generates a job for the script only after all input data is ready.

Write-then-read in the same script

Writing data to a table and then reading from it in the same script causes an error.

Error:

insert overwrite table src2 select * from src where key > 0;
@a := select * from src2; -- Reading from the table written above causes an error.
select * from @a;

Fix: Use a variable to hold the intermediate result instead of writing and reading the same table:

@a := select * from src where key > 0;
insert overwrite table src2 select * from @a;
select * from @a;

IF statement with a BOOLEAN expression

When the IF condition is a BOOLEAN-type expression, the branch is determined at code compilation time. MaxCompute evaluates the condition during compilation and selects the branch before job submission.

set odps.sql.allow.fullscan=true;
set odps.optimizer.cbo.rule.filter.black=LM;
@date := '${var}';
@row TABLE(key int, value bigint); -- Declare a table variable with schema (key int, value bigint).
IF (cast(@date as bigint) % 2 == 0) BEGIN
@row := SELECT key, value from src1;
END ELSE BEGIN
@row := SELECT key, value from src2;
END
INSERT OVERWRITE TABLE dest1 partition(p='${var}') SELECT key, value FROM @row;

The var variable used in the script must be assigned a value in the Scheduling Parameter section of the Properties tab.

When var is 2 (an even number), MaxCompute selects the src1 branch at compile time and inserts rows from src1 into dest1. When var is 3 (an odd number), it inserts rows from src2 instead.

IF statement with a BOOLEAN-type scalar subquery

When the IF condition is a BOOLEAN-type scalar subquery, the branch is determined at job runtime rather than at compilation time. MaxCompute must submit multiple jobs to evaluate the condition.

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

If src contains more than one row where a = '5', the script sets @i to 1 and @t to (1, 2). Otherwise, it sets @i to 2 and @t to (2, 4).

Code-embedded UDFs

Script mode supports embedding Java or Python code directly into an SQL script to implement user-defined functions (UDFs). For details, see Code-embedded UDFs.