DataWorks:MaxCompute SQL node

Limitations

The following limitations apply when you use a MaxCompute SQL node to develop SQL tasks:

Usage notes

• Ensure the account that runs the MaxCompute SQL task has the required permissions on the corresponding MaxCompute project. For more information, see DataWorks On MaxCompute permission control and MaxCompute permissions.

• MaxCompute SQL task execution consumes quota resources. If a task runs for a long time, go to the MaxCompute console to monitor resource consumption and ensure sufficient quota is available. For more information, see Compute Resources-Quota Management.

• Enclose special parameters, such as an OSS address, in double quotation marks (""). Otherwise, parsing may fail and cause the task to fail.

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

• In rare cases, such as an abnormal server power-off or a primary/secondary failover, DataWorks may fail to terminate the associated MaxCompute task processes. If this happens, go to the associated MaxCompute project and terminate the jobs manually.

Create a MaxCompute SQL node

For more 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, perform the following development tasks.

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

-- (Optional) Enable a full table scan at the project level. This operation requires high privileges. 
-- SETPROJECT odps.sql.allow.fullscan=true;  

-- Enable a full table scan at the session level. This is valid only for the current session.
SET odps.sql.allow.fullscan=true; 
-- Query information about 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 Run Configuration pane.

   Parameter	Description
   Computing resource	Select the MaxCompute computing resource that you have bound.
   Computing quota	Select a computing quota that you created. The quota provides the computing resources such as CPU and memory that are 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 created MaxCompute data source and click Run to execute the MaxCompute SQL task.

View results

• Execution results are displayed in a workbook within DataWorks. From there, you can open the results or copy and paste the content into a local spreadsheet.

  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.

• View running logs: On the running results tab , click the LogView link. For more information, see Use Logview 2.0 to view job running information.

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

Next steps

• Configure scheduling properties for a node: To run a node periodically, configure its scheduling properties, including Scheduling Policies, in the Schedule pane.

• Node Publishing: To deploy a task to the production environment, click the icon to start the publishing process. Nodes in the project directory are periodically scheduled only after they are published to the production environment.

Appendix: SQL execution in different environments

The execution order of statements that contain keywords, such as SET and USE, in a MaxCompute SQL node varies based on the DataWorks environment in which they are run.

• When run in Data Studio: All keyword statements 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 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);

Related documents

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