DataWorks:Develop an ODPS SQL task

SQL commands

MaxCompute SQL commands use a syntax similar to standard SQL. They support DDL, DML, and DQL statements, along with MaxCompute-specific commands. For more information about syntax requirements and usage examples, see SQL overview.

Create a table

Use the CREATE TABLE statement to create non-partitioned tables, partitioned tables, foreign tables, and clustered tables. For more information, see CREATE TABLE.

-- Create a partitioned table named students.
CREATE TABLE if NOT EXISTS students
( id BIGINT,
  name STRING,
  age BIGINT,
  birth DATE)
partitioned BY (gender STRING);

Insert data

Use INSERT INTO or INSERT OVERWRITE to insert or overwrite data in a destination table. For more information, see Insert or overwrite data (INSERT INTO | INSERT OVERWRITE).

-- Insert data.
INSERT INTO students PARTITION(gender='boy') VALUES (1,'Zhang San',15,DATE '2008-05-15') ;
INSERT INTO students PARTITION(gender='boy') VALUES (2,'Li Si',17,DATE '2006-07-20') ;
INSERT INTO students PARTITION(gender='girl') VALUES (3,'Li Xia',20,DATE '2003-04-20') ;
INSERT INTO students PARTITION(gender='girl') VALUES (4,'Wang Lan',21,DATE '2002-01-08') ;
INSERT INTO students PARTITION(gender='boy') VALUES (5,'Wang Wu',17,DATE '2006-09-12') ;

Query data

Use the SELECT statement to perform nested queries, group queries, sorting, and more. For more information, see SELECT syntax.

-- (Optional) Enable full table scan at the project level. Requires high-level permissions.
-- SETPROJECT odps.sql.allow.fullscan=true;

-- Enable full table scan at the session level. Valid only for the current session.
SET odps.sql.allow.fullscan=true;
-- Query all male students, sorted by ID in ascending order.
SELECT * FROM students WHERE gender='boy' ORDER BY id;

SQL functions

MaxCompute supports built-in functions and user-defined functions (UDFs) for data development and analysis.

• Built-in functions: Preset in MaxCompute and callable directly. For more information, see Built-in functions. The following example uses the dateadd function to shift values in the birth column by one month:

  -- Enable full table scan at the session level. Valid only for the current session.
  SET odps.sql.allow.fullscan=true;
  SELECT id, name, age, birth, dateadd(birth,1,'mm') AS birth_dateadd FROM students;

• User-defined functions (UDFs): Write the function code, upload it as a resource, and register the function. For more information, see MaxCompute UDFs and Create and use a UDF.

Runtime and results

After running a query, results appear in a workbook. Open the results in the workbook, or copy and paste into a local Excel file. For more information, see Task debugging process.

Click the Operational Log tab to view the log in Logview. For more information, see Use Logview V2.0 to view job running information.

The query results for the examples above are as follows:

• Query all male students, sorted by ID in ascending order:

  +------------+------------+------------+------------+------------+
  | id         | name       | age        | birth      | gender     |
  +------------+------------+------------+------------+------------+
  | 1          | Zhang San  | 15         | 2008-05-15 | boy        |
  | 2          | Li Si      | 17         | 2006-07-20 | boy        |
  | 5          | Wang Wu    | 17         | 2006-09-12 | boy        |
  +------------+------------+------------+------------+------------+

• Shift values in the birth column by one month:

  +------------+------------+------------+------------+---------------+
  | id         | name       | age        | birth      | birth_dateadd |
  +------------+------------+------------+------------+---------------+
  | 4          | Wang Lan   | 21         | 2002-01-08 | 2002-02-08    |
  | 3          | Li Xia     | 20         | 2003-04-20 | 2003-05-20    |
  | 2          | Li Si      | 17         | 2006-07-20 | 2006-08-20    |
  | 1          | Zhang San  | 15         | 2008-05-15 | 2008-06-15    |
  | 5          | Wang Wu    | 17         | 2006-09-12 | 2006-10-12    |
  +------------+------------+------------+------------+---------------+

On the query results page, sort results by a specified field and download them. For more information, see Process query results.