Develop a MaxCompute SQL task - DataWorks - Alibaba Cloud ...

DataWorks provides ODPS SQL nodes so that you can schedule MaxCompute SQL tasks on a regular basis, integrate ODPS SQL nodes with other types of nodes, and then jointly schedule the nodes. MaxCompute SQL tasks can process terabytes of data in distributed scenarios that do not require real-time processing by using the SQL-like syntax. This topic describes the precautions on and guidance for the development of MaxCompute SQL tasks in DataWorks.

Prerequisites

An ODPS SQL node is created. For more information, see Create and manage MaxCompute nodes .

Background information

MaxCompute SQL is used to process and query data in MaxCompute. MaxCompute SQL supports common SQL statements, such as SELECT, INSERT, UPDATE, and DELETE statements, and specific MaxCompute syntax and functions. You can use MaxCompute SQL to write SQL-like statements to query and process data, without the need to write complex data processing logic. For more information about the SQL syntax, see Overview of MaxCompute SQL .

Limits

The following table describes the limits on the development of MaxCompute SQL tasks in DataWorks.

Item	Description
Use of comments	You cannot separately add comments to a SET or USE statement.
	You cannot use semicolons (;) in comments.
	You cannot add comments to the end of a complete statement. If a semicolon (;) is added to the end of an SQL statement, the SQL statement is considered complete.
Execution of SQL statements	You cannot separately use a SET or USE statement in the code of an ODPS SQL node. They must be executed with other SQL statements.
SQL development	The code of an ODPS SQL node can be a maximum of 128 KB in size and can contain a maximum of 200 SQL statements.
Result query	You can use only statements that start with SELECT or WITH to return formatted result sets. A maximum of 10,000 rows of result data can be displayed.

Precautions

The sequence in which the SET and USE statements are executed in the code of an ODPS SQL node varies based on the environment in DataWorks.

DataStudio: The SET and USE statements are combined in the node code and are executed before you execute other SQL statements.
Scheduling environment: All statements are executed in sequence.

Sample code that is defined in an ODPS SQL node:

set a=b;
create table name1(id string);
set c=d;
create table name2(id string);

The following table describes the sequences of executing the preceding statements in different environments.

SQL statement	DataStudio	Scheduling environment
First SQL statement	`set a=b; set c=d; create table name1(id string);`	`set a=b; create table name1(id string);`
Second SQL statement	`set a=b; set c=d; create table name2(id string);`	`set c=d; create table name2(id string);`

Simple code editing example

SQL statements

The MaxCompute SQL syntax is similar to the standard SQL syntax. DDL, DML, and DQL statements and MaxCompute-specific statements are supported. For information about the syntax requirements and usage examples of each SQL statement, see Overview of MaxCompute SQL . The following example shows how to write and execute SQL statements.

```
-- Create a partitioned table named test1.
create table if not exists students
( id BIGINT,
  name STRING,
  age BIGINT,
  birth DATE)
partitioned by (gender STRING); 
```
```
-- Insert data.
INSERT INTO students partition(gender='boy') VALUES (1,'John',15,DATE '2008-05-15') ;
INSERT INTO students partition(gender='boy') VALUES (2,'Jack',17,DATE '2006-07-20') ;
INSERT INTO students partition(gender='girl') VALUES (3,'Alice',20,DATE '2003-04-20') ;
INSERT INTO students partition(gender='girl') VALUES (4,'Lily',21,DATE '2002-01-08') ;
INSERT INTO students partition(gender='boy') VALUES (5,'Bob',17,DATE '2006-09-12') ;
```
```
-- Enable the full table scan feature, which is only valid for the current session.
set odps.sql.allow.fullscan=true; 
-- Query the information about all boys and sort the information by ID in ascending order.
select * from students where gender='boy' order by id;
```

```
-- Enable the full table scan feature, which is only valid for the current session.
set odps.sql.allow.fullscan=true; 
select id, name, age, birth, dateadd(birth,1,'mm') as birth_dateadd from students;
```

+------------+------------+------------+------------+------------+
| id         | name         | age        | birth      | gender        |
+------------+------------+------------+------------+------------+
| 1          | John         | 15         | 2008-05-15 | boy        |
| 2          | Jack         | 17         | 2006-07-20 | boy        |
| 5          | Bob          | 17         | 2006-09-12 | boy        |
+------------+------------+------------+------------+------------+

+------------+------------+------------+------------+---------------+
| id         | name         | age        | birth      | birth_dateadd |
+------------+------------+------------+------------+---------------+
| 4          | Lily         | 21         | 2002-01-08 | 2002-02-08    |
| 3          | Alice        | 20         | 2003-04-20 | 2003-05-20    |
| 2          | Jack         | 17         | 2006-07-20 | 2006-08-20    |
| 1          | John         | 15         | 2008-05-15 | 2008-06-15    |
| 5          | Bob          | 17         | 2006-09-12 | 2006-10-12    |
+------------+------------+------------+------------+---------------+

Prerequisites

Background information

Limits

Precautions

Simple code editing example

SQL statements

SQL functions

Running results and returned data

Advanced code editing examples