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 the syntax requirements and usage examples of each SQL command, see SQL overview. The following example shows how to develop and run a simple SQL command.

• Create a table

  You can use the CREATE TABLE statement to create non-partitioned tables, partitioned tables, foreign tables, and clustered tables. For more information, see CREATE TABLE. The following code provides an SQL example:

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

  You can use the INSERT INTO or INSERT OVERWRITE statement to insert data into or overwrite data in a destination table. For more information, see Insert or overwrite data (INSERT INTO | INSERT OVERWRITE). The following code provides an SQL example:

  -- 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') ;

  Important

  Avoid using the INSERT INTO statement to insert data. This statement may cause unexpected data duplication. We recommend that you use the INSERT OVERWRITE statement instead. For more information, see Insert or overwrite data.

• Query data

  You can use the SELECT statement to perform operations such as nested queries, group queries, and sorting. For more information, see SELECT syntax. The following code provides an SQL example:

  -- (Optional) Enable full table scan at the project level. This operation requires high-level permissions.
  -- SETPROJECT odps.sql.allow.fullscan=true;  
  
  -- Enable full table scan at the session level. This setting is valid only for the current session.
  SET odps.sql.allow.fullscan=true; 
  -- Query the information of all male students and sort the results by ID in ascending order.
  SELECT * FROM students WHERE gender='boy' ORDER BY id;

  Note

  By default, Resource Access Management (RAM) users do not have permissions to query production tables. To obtain these permissions, you must request them from Security Center. For more information about the preset database permissions and access control for MaxCompute in DataWorks, see Details of MaxCompute database permission control. For more information about how to grant permissions using MaxCompute commands, see Manage user permissions using commands.

SQL functions

MaxCompute supports built-in functions and user-defined functions (UDFs) for data development and analysis. For more information about built-in functions, see Built-in functions. For more information about UDFs, see MaxCompute UDFs. The following examples show how to use SQL functions.

• Built-in functions: Built-in functions are preset in MaxCompute and can be called directly. For example, based on the preceding example, you can use the dateadd function to change the values in the birth column by a specified unit and interval. The following code provides a command example:

  -- Enable full table scan at the session level. This setting is 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): To use a UDF, you must write the function code, upload it as a resource, and register the function. For more information, see Create and use a UDF.

Runtime and results

• The run results are displayed in a workbook. You can open the results in a workbook, or copy and paste the content into a local Excel file. For more information, see Task debugging process.

  Note

  Due to an adjustment in the China time zone information released by the International Organization for Standardization, when you run related SQL statements in DataWorks, a time difference may exist for certain periods: a difference of 5 minutes and 52 seconds for dates and times between 1900 and 1928, and a difference of 9 seconds for dates and times before 1900.

• Operational log: You can click the Operational Log tab to view the log in Logview. For more information, see Use Logview V2.0 to view job running information.

• Results:

  • Query the information of all male students and sort the results 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        |
    +------------+------------+------------+------------+------------+

  • Change the values in the birth column by a specified unit and interval.

    +------------+------------+------------+------------+---------------+
    | 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, you can sort the results by a specified field and download the results. For more information, see Process query results.