MaxCompute SQL node

Limitations

The following limitations apply to SQL development in the MaxCompute SQL node:

Notes

• Make sure that the account used to run MaxCompute SQL tasks has the required permissions on the corresponding MaxCompute project. For more information, see DataWorks On MaxCompute permission control and MaxCompute authorization.

• MaxCompute SQL task execution relies on quota resources. If your task takes a long time to run, go to the MaxCompute console to check quota resource consumption and make sure that sufficient resources are available for task execution. For more information, see View quota resource consumption.

• When you develop MaxCompute SQL node tasks, special parameters such as OSS paths must be enclosed in double quotation marks. Missing quotation marks may cause task parsing exceptions, which lead to task execution failures.

• When you run keyword-related statements (SET, USE) in different environments on DataWorks, the execution order varies. 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.

• When the primary purpose of a task is to create a new table (for example, DROP TABLE followed by CREATE TABLE AS SELECT), the MaxCompute engine performs a metadata pre-check before execution. If the table already exists, an error is returned directly. For initial table creation, use CREATE TABLE IF NOT EXISTS. For subsequent data writes, use INSERT OVERWRITE TABLE ... SELECT ... instead.

• You can control the SQL execution mode by setting the odps.task.sql.realtime parameter. Set the value to true to enable Online mode for real-time execution, or set the value to false to force Offline mode. Place this parameter at the beginning of your SQL code, before the business SQL statements. Example:

  SET odps.task.sql.realtime=true;
  -- Your business SQL statements
  SELECT * FROM my_table;

• Directly modifying a column data type from STRING to complex types such as STRUCT is not supported. If error ODPS-0130071 is returned, check whether the field type conversion complies with the semantic rules and avoid unsupported type conversion operations.

Create a MaxCompute SQL node

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

Develop a MaxCompute SQL node

On the MaxCompute SQL node editing page, perform the following 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 columns
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 and other clauses are omitted here. Complete them based on your actual business requirements.
;

-- (Optional) Enable full table scan at the project level. This operation requires elevated permissions.
-- SETPROJECT odps.sql.allow.fullscan=true;  
-- Enable full table scan at the session level. This setting is effective 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. Configure the relevant parameters in the Run Configuration panel on the right side of the node editing page.

   Parameter	Description
   Compute resource	Select the MaxCompute compute resource that you have associated with the workspace.
   Compute quota	Select the compute quota to provide the required compute resources (CPU and memory) for compute jobs. If no compute quota is available, click Create Compute Quota in the drop-down list and create a quota on the MaxCompute console. For more information, see Create a compute quota.
   Resource group	Select a scheduling resource group that has passed the connectivity test with the compute resource. For more information, see Resource groups for scheduling.

2. In the parameter dialog on the toolbar, select the MaxCompute data source that you have created, and then click Run to run the MaxCompute SQL task.

View results

• Results are displayed in a spreadsheet format. You can perform operations in DataWorks, open the results in a spreadsheet, or copy and paste the content to a local Excel file.

  Note

  Due to changes in the China time zone information released by the International Organization for Standardization (ISO), date display discrepancies may occur for certain time periods when you run related SQL statements through DataWorks: a difference of 5 minutes and 52 seconds for dates between 1900 and 1928, and a difference of 9 seconds for dates before 1900.

• Runtime log: On the tab of the results, click the Logview link to view the logs. For more information, see LogView.

• Sort results: On the results page, click the drop-down menu on the corresponding column header, select ascending or descending order in the Sort section, and then click confirm to sort the results.

• View BLOB columns: MaxCompute supports the BLOB data type for storing binary objects such as images and audio files. In the results, double-click a cell of this data type to preview the content in the Current Field Value dialog (images are rendered directly; text is displayed in read-only mode). You can switch between Blob View, Text View, and JSON View by using the buttons at the bottom of the dialog.

• Scientific notation display: In the DataWorks data development environment, numeric values in query results may be displayed in scientific notation. This is a frontend display issue and does not affect the actual data values. To ensure that numeric values are displayed as expected, use the CAST function in your SQL to manually convert the data type. For example, use CAST(column_name AS STRING) to convert a numeric value to a string for display. You can also verify data accuracy in the DataAnalysis module.

Next steps

• Configure schedule settings: If nodes in the project directory need to be periodically scheduled, configure the Scheduling Policy and related scheduling properties in the Scheduling Settings panel on the right side of the node.

• Deploy a node: If a task needs to be deployed to the production environment, click the icon on the page to initiate the deployment process. Nodes in the project directory are periodically scheduled only after they are deployed to the production environment.

Appendix 1: SQL execution order in different environments

When you run keyword-related statements (SET, USE) in different DataWorks environments for a MaxCompute SQL node, the execution order varies.

• Running in Data Studio: All keyword statements (SET, USE) in the current task code are merged and prepended to all SQL statements.

• Running in the scheduling environment: Statements are executed in the order they are written.

Assume that the following code is defined in a node.

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

The execution order varies by environment 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: Lakehouse practices

To read from and write to DLF data tables in MaxCompute SQL tasks, use MaxCompute external projects (External Project). External projects enable real-time access to metadata and data by mapping DLF catalogs. They delegate permission management to DLF and support metadata access and read/write operations on DLF-managed data stored in OSS. For more information, see MaxCompute external projects.

FAQ

Q: Must the destination table of an auto triggered task be a partitioned table?

A: No. The destination table of an auto triggered task does not have to be a partitioned table. If you do not specify partition fields when you create the table, each run of INSERT OVERWRITE overwrites the entire non-partitioned table.

Q: How do I handle the MaxCompute error ODPS-0429311?

A: This error may be related to both the input parameters and how the node SQL is written. A common cause is that multiple tasks concurrently execute DDL operations on the same table, causing a metadata conflict. Try adjusting the relevant settings (for example, set the concurrency parameter to false) and verify whether the issue is resolved.

References

For more MaxCompute SQL task examples, see the following topics:

• Key differences from standard SQL and their solutions

• JOIN operations in MaxCompute SQL

• Migrate JSON data from OSS to MaxCompute

• Unified batch and real-time analytics on GitHub events

• Convert IP addresses to geolocations with MaxCompute UDFs

• Build a MaxCompute data lakehouse by using DataWorks and DLF

• SQL query optimization

• Analyze top N billing accounts and time-consuming jobs in MaxCompute

• Policy-based permission management for users assigned built-in roles

• Plan resources and select a resource package

• Implement capabilities provided by the GROUP_CONCAT function

• Merge small files