Using the SQL-like syntax, ODPS SQL nodes can process terabytes of data in distributed processing scenarios that do not require real-time processing.
Prerequisites
Background information
Generally, it takes a long time from preparing to committing a job. You can use ODPS SQL nodes to process thousands to tens of thousands of transactions. ODPS SQL nodes are online analytical processing (OLAP) applications designed to deal with large amounts of data.
ODPS SQL nodes have the following limits:
- You cannot use SET statements, USE statements, or SQL alias statements independently
in the code of an ODPS SQL node. They must be executed together with other SQL statements.
For example, you can use a SET statement together with a CREATE TABLE statement.
set a=b; create table name(id string);
- You cannot add comments to statements that contain keywords, including SET statements,
USE statements, and SQL alias statements, in the code of an ODPS SQL node. For example,
the following comment is not allowed:
create table name(id string); set a=b; // Comment. create table name1(id string);
- The running of an ODPS SQL node during workflow development and the scheduled running
of an ODPS SQL node have the following differences:
- Running during workflow development: combines all the statements that contain keywords, including SET statements, USE statements, and SQL alias statements, in the node code and executes them before executing other SQL statements.
- Scheduled running: executes all SQL statements in sequence.
set a=b; create table name1(id string); set c=d; create table name2(id string);
The following table describes the differences between the two running modes for the preceding SQL statements.SQL statement Running during workflow development Scheduled running 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);
- You must specify a scheduling parameter in the format of
key=value
. Do not add any spaces before or after the equation mark (=). Examples:time = {yyyymmdd hh:mm:ss} // Incorrect format. a =b // Incorrect format.
- If you use keywords such as bizdate and date as scheduling parameters, you must specify the values in the format of yyyymmdd. If you want to use other time formats, do not use the preceding keywords as scheduling
parameters. Example:
bizdate=201908 // Incorrect format.
- You can only use statements that start with SELECT, READ, or WITH to query the result data for a node during workflow development. Otherwise, no results are returned.
- Separate multiple SQL statements with semicolons (;) and place them in different lines.
- Incorrect example
create table1;create table2
- Correct example
create table1; create table2;
- Incorrect example
- If extension functions in MaxCompute V2.0 use new data types, you must add
set odps.sql.type.system.odps2=true;
before the SQL statements that use the functions, and commit and execute the code together with the SQL statements.