FAQ about running SQL in MaxCompute-MaxCompute(MaxCompute)-阿里云帮助中心

Frequently asked questions about data types, SQL limits, and other topics related to running SQL statements in MaxCompute.

Category	FAQ
Data types	Can a time-related field in MaxCompute store a date without a time? Why do I get unexpected results when I compare two DOUBLE values for equality in a MaxCompute SQL statement? How do I resolve a precision overflow error for the DECIMAL data type? Newly created MaxCompute projects do not support implicit data type conversion. How can I resolve this? When I query data, an error message appears that says 'XXX type is not enabled in current mode'. How do I resolve this?
SQL limits	How do I handle the 8 MB limit for a single field? When I run a MaxCompute SQL statement, an error message appears that says 'partitions exceeds the specified limit'. How do I resolve this?
SQL job execution	How do I run MaxCompute SQL statements non-interactively? Can I use SQL to transfer a MaxCompute configuration to another Alibaba Cloud account? How do I synchronize table data from a development environment to a production environment? How do I check if a field is empty? Can I call MaxCompute SQL from a Shell node in DataWorks? How do I implement a loop in SQL? How do I call an assignment node in SQL?
SQL job monitoring	How do I view all SQL statements that are run in MaxCompute on a specific day? How do I view the resource usage of an SQL job? How do I view the execution cost of an SQL statement?
SQL job optimization	My SQL job runs slowly. How can I optimize it?

Can a time-related field in MaxCompute store a date without a time?

Yes. Use the DATE data type, which requires MaxCompute V2.0 data types to be enabled. For more information, see 2.0 data type versions.

Why do I get unexpected results when I compare two DOUBLE values for equality in a MaxCompute SQL statement?

The DOUBLE data type in MaxCompute is subject to precision errors. Do not use the equals sign (=) to compare two DOUBLE values directly.

Instead, subtract one value from the other and check whether the absolute difference is small enough to be considered equal.

How do I resolve a precision overflow error for the DECIMAL data type?

You can run the set odps.sql.decimal.odps2=true; command at the session level to enable support for MaxCompute V2.0 data types. The maximum precision for the DECIMAL data type in MaxCompute is 38 digits. Storing data at maximum precision makes overflow errors more likely during processing. Use a smaller precision when possible.

Newly created MaxCompute projects do not support implicit data type conversion. How can I resolve this?

Disable support for MaxCompute V2.0 data types to allow implicit conversion. For more information, see Data type conversion.

When I query data, an error message appears that says 'XXX type is not enabled in current mode'. How do I resolve this?

You can run the set odps.sql.decimal.odps2=true; command at the session level to enable support for MaxCompute V2.0 data types.

How do I handle the 8 MB limit for a single field?

A single field in a MaxCompute table cannot exceed 8 MB. If a field exceeds this limit, split it into multiple smaller fields based on your business needs.

Large fields with complex structures can degrade computing performance. To avoid this, design your data architecture following data warehouse standards:

Archive raw data with complex structures in the Operational Data Store (ODS) layer in a compressed format.
Periodically clean the incremental data in the ODS layer (for example, daily). Split complex fields into multiple simple fields and store them in Common Data Model (CDM) layer tables for easier analysis.

When I run a MaxCompute SQL statement, an error message appears that says 'partitions exceeds the specified limit'. How do I resolve this?

Symptom

When you run a MaxCompute SQL statement, the following error message is returned.

FAILED: ODPS-0010000:System internal error - OTS filtering exception - Ots read range partitions exceeds the specified limit:10000:   tableName:xxxx , please check hive conf key

Cause
A single MaxCompute table supports up to 60,000 partitions, but a single query can access a maximum of 10,000 partitions. This error can occur for the following reasons:
- Reason 1: You did not specify a partition condition.
- Reason 2: You used a field such as a user ID as a partition field, which resulted in too many partitions.
Solution
- Solution for Reason 1: If you did not specify a partition condition, add one.
- Solution for Reason 2: If the partition key column is not suitable and causes too many partitions, consider changing the partition key column.

How do I run MaxCompute SQL statements non-interactively?

You can use Shell to run MaxCompute SQL statements non-interactively:

Use the odps -f filename command to read and process an SQL file.
When you run an SQL statement, the first line of the `filename` file must be SQL to indicate SQL mode.
```
SQL

  select ... from table_name where xxx;                
```
To run a single SQL statement, use the sqltext method in MaxCompute SQL.
```
./odpscmd -e "select * from dual;"             
```
Run odps -help for more information.

Can I use SQL to transfer a MaxCompute configuration to another Alibaba Cloud account?

Yes. Use the Package method. For more information, see Best Practices for Multi-team Collaborative Data Development and Project Management in MaxCompute.

How do I synchronize table data from a development environment to a production environment?

Run the following command in the MaxCompute client.

insert into project.table select * from project_dev.table;

If you do not have read and write permissions on the production table, grant the required permissions to your account. For more information, see MaxCompute permissions.

How do I check if a field is empty?

Use operators in MaxCompute SQL to check whether a field is empty. For more information, see Operators.

Can I call MaxCompute SQL from a Shell node in DataWorks?

No. Shell nodes support only standard Shell syntax, not interactive syntax. To run multiple jobs, use an ODPS SQL node instead. For more information, see Develop an ODPS SQL task.

How do I implement a loop in SQL?

Use a do-while node in DataWorks.

How do I call an assignment node in SQL?

Use a for-each node in DataWorks.

How do I view all SQL statements that are run in MaxCompute on a specific day?

Run the show p -all -limit <number>; command to view the SQL history for all members in a MaxCompute project. For more information, see View instance information.

How do I view the resource usage of an SQL job?

View the resource usage of an SQL job in your bill. For more information, see Analyze the usage details in a MaxCompute bill.

How do I view the execution cost of an SQL statement?

Use the cost sql command to estimate query costs. For more information, see Metering and estimation.

My SQL job runs slowly. How can I optimize it?

Use Logview to identify the cause of a slow SQL job. For more information, see Logview-based diagnosis practices.

For more information about SQL job optimization, see Best practices for compute optimization.