DataWorks:Develop an ODPS SQL node

SQL commands

MaxCompute SQL commands use a syntax similar to standard SQL. They support Data Definition Language (DDL), Data Manipulation Language (DML), and Data Query Language (DQL) statements, along with commands specific to MaxCompute. 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 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 or update data in a destination table. For more information, see Insert or overwrite data (INSERT INTO | INSERT OVERWRITE). The following code provides an 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 because this 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 example:

  -- 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 data 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 example shows how to use a simple SQL function.

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

  -- 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 UDF, you must write the function code, upload the code as a resource, and register the function. For more information, see Create and use a UDF.

Running and results

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

  Note

  Due to an adjustment to the China time zone information released by the International Organization for Standardization, a time difference may occur for specific periods when you run related SQL statements in DataWorks. This includes a 5-minute and 52-second difference for dates and times from 1900 to 1928, and a 9-second difference for dates and times before 1900.

• Run logs: You can click the Running Log tab to view the Logview details. 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.