DataWorks:Develop a MaxCompute SQL task

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 table

  You can execute the CREATE TABLE statement to create a non-partitioned table, a partitioned table, an external table, or a clustered table. For more information, see CREATE TABLE. Sample SQL statement:

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

  You can execute the INSERT INTO or INSERT OVERWRITE statement to insert data into a destination table. For more information, see Insert or update data into a table or a static partition (INSERT INTO and INSERT OVERWRITE). Sample SQL statements:

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

  Important

  The INSERT INTO statement may result in unexpected data duplication. We recommend that you execute the INSERT OVERWRITE statement, instead of the INSERT INTO statement. For more information, see Insert or update data into a table or a static partition (INSERT INTO and INSERT OVERWRITE).

• Query data

  You can execute SELECT statements to perform operations, such as nested queries, sorting, and queries by group. For more information, see SELECT syntax. Sample SQL statements:

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

  Note

  By default, RAM users do not have the required permissions to query MaxCompute tables in the production environment. If you want to query MaxCompute tables in the production environment as a RAM user, you must request the required permissions for the RAM user in Security Center in the DataWorks console. For information about the permissions of built-in workspace-level roles on MaxCompute data and how to manage the permissions, see Manage permissions on data in a MaxCompute compute engine instance. For information about how to grant permissions to users by running commands, see Manage user permissions by using commands.

SQL functions

MaxCompute allows you to use built-in functions and User-defined functions (UDFs) for data development and analysis. For information about built-in functions, see Overview of built-in functions. For information about UDFs, see Overview of UDFs. The following example shows how to use SQL functions.

• Built-in functions

  MaxCompute provides a large number of built-in functions. You can directly call the built-in functions. Based on the example in the preceding section, you can use the dateadd function to change data in the birth column based on a specified unit and a specified interval. Sample statements:

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

• UDFs

  To use a UDF, you must write the code, upload the code as a resource, and register the function. For more information, see Create and use a MaxCompute function.

Running results and returned data

• Running results are displayed in a workbook. You can analyze the running results, view or manage the running results in the workbook, or copy the running results to an Excel file on your on-premises machine. For more information, see Debugging procedure.

  Note

  Due to the adjustment made by the International Organization for Standardization (ISO) on the UTC+8 time zone, differences exist between the actual time and the output time when you execute related SQL statements in DataWorks. In a year from 1900 to 1928, the time difference is 352 seconds. Before the year of 1900, the time difference is 9 seconds.

• Run logs: You can click the Runtime Log tab to view the run logs in Logview. For more information, see Use Logview V2.0 to view job information.

• Returned data:

  • Query the information about all boys and sort the information by ID in ascending order.

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

  • Change data in the birth column based on a specified unit and a specified interval.

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

  On the Result tab, you can sort result data by specific field and perform operations such as download on the result data. For more information, see Process query results.