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 commands with MaxCompute-specific syntax. For detailed syntax requirements and examples of each SQL command, see SQL overview. The following simple examples show how to develop and run SQL commands.

• 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 a sample SQL statement:

  -- 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 or update data in a destination table. For more information, see Insert or overwrite data (INSERT INTO | INSERT OVERWRITE). The following code provides a sample SQL statement:

  -- 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 because it may cause unexpected data duplication. We recommend that you use INSERT OVERWRITE 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 a sample SQL statement:

  -- Enable a full table scan. 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 data from production tables. To obtain these permissions, you must request them in Security Center. For more information about preset database permissions and access control for MaxCompute in DataWorks, see MaxCompute database permission control details. 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 overview. For more information about UDFs, see MaxCompute UDF overview. The following simple examples show how to use SQL functions.

• Built-in functions: Built-in functions are predefined in MaxCompute and can be called directly. Based on the preceding example, you can use the dateadd function to modify the birth column by a specified unit and interval. The following code provides an example of the command:

  -- Enable a full table scan. 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 user-defined function (UDF), you must write the function code, upload the code as a resource, and then register the function. For more information, see Create and use a UDF.

Run the task and view the results

• The run results are displayed in a workbook. You can perform operations in DataWorks, 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, a time difference may exist for certain periods when you run related SQL statements in DataWorks. The difference is 5 minutes and 52 seconds for dates from 1900 to 1928, and 9 seconds for dates before 1900.

• Runtime log: You can click the Runtime Log tab to view the 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 perform operations such as downloading the results. For more information, see Process query results.