MaxCompute:SQL in script mode

Syntax

--set
set odps.sql.type.system.odps2=true;
[set odps.stage.reducer.num=xxx;]
[...]
--ddl
create table table1 xxx;
[create table table2 xxx;]
[...]
--dml
@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 (partcol1=val1, partcol2=val2 ...)]
        SELECT [ALL | DISTINCT] select_expr, select_expr, ...
        FROM @var3;    
[@var4 := SELECT [ALL | DISTINCT] var1.select_expr, var.select_expr, ... FROM @var1 
        UNION ALL | UNION 
        SELECT [ALL | DISTINCT] var1.select_expr, var.select_expr, ... FROM @var2;    
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name 
        AS 
        SELECT [ALL | DISTINCT] select_expr, select_expr, ...
        FROM @var4;]
[...]

Syntax description

• The script mode supports three types of statements: SET statements, DML statements, and some DDL statements. The DDL statements that are used to display results, such as DESC and SHOW, are not supported.

• A complete script file must be a list of SET, DDL, and DML statements in sequence. A script file can contain multiple statements of each type, but statements of the same type must be placed together. You must place the three types of statements in the preceding sequence.

• The at sign (@) declares a variable.

• A script file can contain a maximum of one statement that is used to display results, such as a SELECT statement. If a script file contains more than one such a statement, an error is returned. We recommend that you do not include SELECT statements that are used to display results in the script file.

• A script file can contain a maximum of one CREATE TABLE AS statement and must end with this statement. We recommend that you do not include the statements used to create tables and INSERT statements in the same script command.

• In script mode, if a statement in the script file fails to be executed, all the other statements in the script file also fail.

• In script mode, a job can be generated to process data only after all the input data is prepared and inserted.

• In script mode, you cannot assign the value of a variable of the table type to a variable of the specified data type. Sample code:

  @a table (name string);
  @a:= select 'tom';
  @b string;
  @b:= select * from @a;

• In script mode, if a table is written and then read, the following error is returned:

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

  To prevent this error, you can modify your script file based on the following sample script:

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

• In script mode, the IF statement is supported.

  • The IF statement allows the system to automatically select the execution logic based on specific conditions. MaxCompute supports the following IF syntax:

    IF (condition) BEGIN
      statement 1
      statement 2
      ...
    END
    
    IF (condition) BEGIN
      statements
    END ELSE IF (condition2) BEGIN
      statements
    END ELSE BEGIN
      statements
    END

    Note

    • If the BEGIN and END conditional clauses contain only one statement, you can omit the BEGIN and END conditional clauses. This is similar to the code block { } in Java.

    • The IF statement cannot contain DDL statements, such as CREATE TABLE, ALTER TABLE, and TRUNCATE TABLE.

  • The IF statement can contain the following types of conditions:

    • Expressions of the BOOLEAN type. A BOOLEAN-type expression in the IF ELSE statement determines the branch to execute during code compilation. Sample code:

      @date := '20190101';
      @row TABLE(id STRING); -- Declare the row variable. The row type is TABLE and the fields in the table are of the STRING data type. 
      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; 

    • Scalar subqueries of the BOOLEAN type. A BOOLEAN-type scalar subquery in the IF ELSE statement determines the branch to execute during job running. Therefore, you must submit multiple jobs. Sample code:

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

• You can use the script mode in the following scenarios:

  • A single statement with nested subqueries needs to be rewritten, or a complex script must be split into multiple statements.

  • Data from different data sources are prepared at different time points, and the time gap is large. For example, the data from one data source is prepared at 01:00, and that from another data source is prepared at 07:00. In this case, the table variables are not suitable for packaging the statements of different types in a script file.

  • You must assign a constant value to a variable and execute the SELECT * FROM Variable statement to convert the variable into a scalar value. The scalar value is used together with other columns for calculation. The constant value can also be stored in a table that has only one row. The following statements show an example. For more information about the syntax of the SELECT * FROM Variable statement, see Subqueries.

    @a := SELECT 10; -- Assign the constant value 10 to @a. You can also execute the SELECT col1 FROM t1 statement to store the constant value to table t1 that has only one row. 
    @b := SELECT key,value+(SELECT * FROM @a) FROM t2 WHERE key >10000; -- Use the value of @a with value in table t2 for calculation. 
    SELECT * FROM @b;

Example

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;

Required tools

• MaxCompute Studio. For more information, see What is MaxCompute Studio?

• MaxCompute client (odpscmd). For more information, see Install and configure the MaxCompute client.

• DataWorks console. For more information, see Develop a MaxCompute script task.

Methods to use the script mode

• Use MaxCompute Studio

  Before you use MaxCompute Studio, make sure that MaxCompute Studio is installed, project connections are added, and a MaxCompute SQL script file is created. For more information, see Install IntelliJ IDEA, Manage project connections, and Create a MaxCompute script module.

  After you compile and submit the script file, you can view the graphic execution plan. Although the script file contains multiple statements, the graphic execution plan is shown in a directed acyclic graph (DAG).

• Use the MaxCompute client

  You must use the MaxCompute client of a version later than 0.27 to submit the script file. We recommend that you install the latest version of the MaxCompute client installation package. After you install the latest version of the package, use the -s option to submit the script file.

  Edit the source code file myscript.sql in script code and call odpscmd in the command-line interface (CLI) to run the following command. For more information about how to run the MaxCompute client by using the CLI, see Run the MaxCompute client.

  ..\bin>odpscmd -s myscript.sql

  Note

  Similar to the -f and -e options, the -s option is a command-line option for the MaxCompute client. The -s option is not a command in an interactive environment. If the MaxCompute client (odpscmd) is used in an interactive environment, the script mode and table variables are not supported.

• Use the DataWorks console

  In the DataWorks console, you can create an ODPS Script node. The following figure shows how to create an ODPS Script node.

  After the node is created, use the script mode to edit the script file and click the Run icon in the toolbar to submit the script file to MaxCompute. This way, you can use the LogView URL in the output to view the graphic execution plan and results.

• Use the SDK for Java

  You can create an SQL script file by using the SDK for Java. For more information about the SDK for Java, see SDK for Java. The following sample code provides an example on how to create an SQL script file.

  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 {
  		// The AccessKey pair of an Alibaba Cloud account has permissions on all API operations. If you use an AccessKey pair to call API operations, risks may occur. We recommend that you use a RAM user to call API operations or perform routine O&M. To create a RAM user, log on to the RAM console.
  		// In this example, the AccessKey ID and AccessKey secret are configured as environment variables. You can also save your AccessKey pair in the configuration file based on your business requirements.
  		// We recommend that you do not directly specify the AccessKey ID and AccessKey secret in code to prevent AccessKey pair leaks.
      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_name");
      odps.setEndpoint("your end_point");
  
      String sqlScript = "@a := SELECT * FROM jdbc_test;\n"
                         + "SELECT * FROM @a;";
  
      // Make sure that you add the following configuration to the SQL script file.
      Map<String, String> hints = new HashMap<>();
      hints.put("odps.sql.submit.mode", "script");
  
      Instance instance = SQLTask.run(odps, "your project_name", 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));
      }
    }
  
  }