DataWorks:MaxCompute SQL node

Limits

The following limits apply when you use a MaxCompute SQL node:

Notes

• Make sure that the account used to run the MaxCompute SQL task has the required permissions for the corresponding MaxCompute project. For more information, see Access control for DataWorks on MaxCompute and MaxCompute permissions.

• The execution of MaxCompute SQL tasks depends on quota resources. If a task runs for a long time, go to the MaxCompute console to check the quota consumption and ensure that sufficient resources are available to run the task. For more information, see Computing resources - Quota management.

• When you develop a MaxCompute SQL node task, if the task involves special parameters such as an OSS address, you must enclose them in double quotation marks (""). Otherwise, the task may fail to be parsed and run.

• The execution order of statements that contain keywords such as SET and USE varies depending on the environment in DataWorks. For more information, see Appendix: SQL execution order in different environments.

• In some extreme cases, such as an unexpected server power outage or an active/standby switchover, DataWorks may fail to completely stop the associated MaxCompute task processes. In this case, go to the corresponding project in the MaxCompute computing resource to stop the job.

Create a MaxCompute SQL node

For information about how to create a MaxCompute SQL node, see Create a MaxCompute SQL node.

Develop a MaxCompute SQL node

On the node editing page, follow these steps to develop the node.

-- 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.
INSERT OVERWRITE students PARTITION(gender='boy') VALUES (1,'ZhangSan',15,DATE '2008-05-15') ;

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

Debug a MaxCompute SQL node

1. On the right side of the node editing page, configure the parameters in the Debug Configuration section.

   Parameter	Description
   Computing resource	Select the MaxCompute computing resource that you attached.
   Computing quota	Select a quota that you created. The quota provides the computing resources (CPU and memory) required for the computing job. If no computing quota is available, click Create Computing Quota in the drop-down list to go to the MaxCompute console and configure a quota.
   Resource group	Select a scheduling resource group that has passed the connectivity test with the computing resource. For more information, see Network connection solutions.

2. In the parameter dialog box on the toolbar, select the MaxCompute data source you created and click Run to execute the MaxCompute SQL task.

View the results

• The execution results are displayed in a workbook. You can open the results in the workbook, perform other operations in DataWorks, or copy and paste the content to a local Excel file.

  Note

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

• To view the running logs, click the LogView link on the tab in the running results. For more information, see Use Logview V2.0 to view job running information.

• To sort the results on the results page, click the drop-down arrow for a field, select ascending or descending order from the Sort list, and then click OK.

What to do next

• Schedule the node: To schedule a node in the project folder, set the Scheduling Policy and configure its scheduling properties in the Schedule section on the right side of the node.

• Publish the node: If the task needs to be run in the production environment, click the icon to publish the task to the production environment. A node in the project folder can be run on a schedule only after it is published to the production environment.

Appendix: SQL execution order in different environments

The execution order of statements that contain keywords such as SET and USE in a MaxCompute SQL node varies depending on the execution environment in DataWorks.

• In DataStudio: All statements that contain keywords such as SET and USE in the current task code are merged and executed before all other SQL statements.

• In the scheduling environment: The statements are executed in the order in which they are written.

Assume that the following code is defined in the node.

SET a=b;
CREATE TABLE name1(id string);
SET c=d;
CREATE TABLE name2(id string);

The execution order in different environments is as follows:

SET a=b;
SET c=d;
CREATE TABLE name1(id string);

SET a=b;
CREATE TABLE name1(id string);

SET a=b;
SET c=d;
CREATE TABLE name2(id string);

SET c=d;
CREATE TABLE name2(id string);

References

For more examples of MaxCompute SQL tasks, see the following topics:

• JOIN operations in MaxCompute SQL

• Migrate JSON data from OSS to MaxCompute

• Example of integrated offline and real-time data processing based on a public GitHub event dataset

• Convert an IPv4 or IPv6 address to a location using a MaxCompute UDF

• SQL tuning

• Manage permissions for users with built-in roles based on policies

• Implement the GROUP_CONCAT function

• Merge small files