DataWorks:MaxCompute SQL node

Limitations

The MaxCompute SQL node has the following limitations for SQL development:

Usage notes

• Ensure that the account used to run the MaxCompute SQL task has the required permissions on the corresponding MaxCompute project. For details, see DataWorks On MaxCompute permission control and MaxCompute permissions.

• MaxCompute SQL tasks consume computing quota. If a task runs for a long time, go to the MaxCompute console to monitor quota consumption and ensure that sufficient resources are available to run the task. For more information, see Computing resources-quota management.

• When you develop MaxCompute SQL node tasks, special parameters such as Object Storage Service (OSS) addresses must be enclosed in double quotation marks (""). If you do not add quotation marks, the task may fail to parse and run.

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

• In some extreme cases, such as a server power outage or a primary/secondary switchover, DataWorks may not be able to completely terminate related MaxCompute tasks. In this situation, go to the corresponding MaxCompute project to terminate the job.

Create MaxCompute SQL node

For instructions, see Create a MaxCompute SQL node.

Develop MaxCompute SQL node

On the node editing page, perform the following development operations.

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

-- Compare DDL fields.
INSERT OVERWRITE TABLE dws_user_info_all_di PARTITION (dt='${workflow.var}')
SELECT COALESCE(a.uid, b.uid) AS uid
    , b.gender
    , b.age_range
    , b.zodiac
    , a.region
    , a.device
    , a.identity
    , a.method
    , a.url
    , a.referer
    , a.time
-- The FROM/JOIN clauses are omitted here. Add them based on your business logic.
;

-- (Optional) Enable full table scan at the project level. This operation requires high privileges.
-- SETPROJECT odps.sql.allow.fullscan=true;  
-- Enable full table scan at the session level. This 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 MaxCompute SQL node

1. In the node editor, configure the parameters in the Run Configuration pane on the right.

   Parameter	Description
   Compute engine	Select the MaxCompute compute engine that is bound to your workspace.
   Computing quota	Select a computing quota that you created. The quota provides the required computing resources, such as CPU and memory, 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 can connect to the compute engine. 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 run the MaxCompute SQL task.

View results

• You can open the results in a spreadsheet or copy the content to a local file.

  Note

  Due to a historical time zone adjustment for China, SQL queries in DataWorks may return results with a time offset for specific periods. The offset 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 run logs, click the LogView link on the tab. For more information, see View job information by using Logview 2.0.

• To sort the results, click the drop-down arrow in a column header, select an ascending or descending order from the Sort list, and then click OK.

• To view BLOB fields, double-click a BLOB cell in the run results. MaxCompute supports the BLOB data type for storing binary objects such as images and audio files. In the Current Field Value window that appears, you can preview the content. Images are rendered directly, and text is displayed in read-only format. You can switch between Blob View, Text View, and JSON View by using the buttons at the bottom of the window.

Next steps

• Node scheduling configuration: If a node in the project directory needs to run periodically, you need to set the Scheduling Policy and configure related scheduling properties in the Scheduling Settings section on the right side of the node.

• Publishing a node: If you need to publish a task to the production environment for execution, 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 1: 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 DataWorks environment.

• In Data Studio: All SET and USE statements in your code are consolidated and executed before other SQL statements.

• In Operation Center (for scheduling and O&M): 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);

Appendix 2: Data lakehouse practices

To read data from or write data to Data Lake Formation (DLF) tables in a MaxCompute SQL task, you can use a MaxCompute External Project. An External Project allows real-time access to metadata and data by mapping a DLF catalog. It delegates permission management to DLF and supports accessing and manipulating metadata for data stored in DLF-managed OSS. For more information, see Paimon DLF External Project.

Related topics

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

• JOIN operations in MaxCompute SQL

• Migrate JSON data from OSS to MaxCompute

• Integrated offline and real-time data processing based on a public GitHub event dataset

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

• MaxCompute data lakehouse practice based on data ingestion into DLF

• SQL tuning

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

• Implement the GROUP_CONCAT function

• Merge small files