This topic provides answers to some frequently asked questions about SQL statements in MaxCompute, such as questions related data types and limits on SQL.

Category FAQ
Data types
Limits on SQL
Operations to run SQL jobs
Operations to view SQL jobs
Operations to tune SQL jobs How do I improve the efficiency of job operation when SQL jobs run slowly?

Does MaxCompute support time fields whose values do not include the hour, minute, and second parts?

Yes, MaxCompute supports time fields whose values do not include the hour, minute, and second parts. The time fields can be of the DATE data type. To use the DATE data type, you must enable the MaxCompute V2.0 data type edition. For more information about the MaxCompute V2.0 data type edition, see MaxCompute V2.0 data type edition.

Why does the result of equivalent comparison between values of the DOUBLE type during the execution of MaxCompute SQL statements fail to meet the expectation?

The values of the DOUBLE type in MaxCompute have different precisions. We recommend that you do not use an equal sign (=) to compare two values of the DOUBLE type.

To resolve this issue, subtract the two values of the DOUBLE type and take the absolute value. If the absolute value is extremely small, the two values of the DOUBLE type are considered equal.

How do I resolve the precision overflow issue of values of the DECIMAL data type?

You can run the set odps.sql.decimal.odps2=true; command at the session level to enable the MaxCompute V2.0 data type edition. The maximum length supported by MaxCompute for the DECIMAL data type is 38 bits. However, if data is stored based on the maximum length during business processing, data overflow may occur. To avoid data overflow, we recommend that you decrease the maximum length of the DECIMAL data type.

What do I do if the newly created MaxCompute project does not support implicit conversions between data types?

To support implicit data type conversions, you must disable the MaxCompute V2.0 data type edition. For more information about data type conversions, see Type conversions.

What do I do if the "XXXtypeisnotenabled incurrentmode" error message appears when I query data?

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

What do I do if the size of a field exceeds 8 MB?

The size of a single field in a MaxCompute table cannot exceed 8 MB due to the storage limit. If the size of a field is greater than 8 MB, we recommend that you split the field into multiple fields. You can design the specific splitting logic based on the characteristics of your business to ensure that the size of each field does not exceed 8 MB.

Large fields with complex structures significantly affect the computing performance during data development and analysis. We recommend that you design your data architecture based on the following data warehouse specifications to avoid large fields:
  • When you archive raw data with a complex structure at an operational data store (ODS) layer, compress the data.
  • Perform data cleansing on the incremental data at the ODS layer on a regular basis, such as every day. Split complex fields into multiple simple fields and store the fields in tables at the common data model (CDM) layer for easy data collection and analysis of data.

What do I do if the "partitions exceeds the specified limit" error message appears when I execute a MaxCompute SQL statement?

  • Problem description

    When I execute a MaxCompute SQL statement, the following error message appears: 

    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 MaxCompute table can contain up to 60,000 partitions. However, you can query only up to 10,000 partitions at the same time. Common causes:
    • Cause 1: No partition condition is specified.
    • Cause 2: A field similar to user ID is used as the partition field. As a result, excessive partitions are generated.
  • Solutions
    • Solution to Cause 1: Specify a partition condition.
    • Solution to Cause 2: Change the partition field.

How do I execute MaxCompute SQL in non-interactive mode?

In the operating system, you can execute MaxCompute SQL in non-interactive mode by using shell commands.
  • Use odps -f filename to read and process SQL files.
    The first row of the file specified by the filename parameter is SQL, which indicates that the SQL mode is enabled. Sample statement: 
    SQL

  select ... from table_name where xxx;
  • If you execute only one SQL statement, you can use the sqltext method in MaxCompute SQL. Sample statement: 
    ./odpscmd -e "select * from dual;"

    You can use odps -help to obtain more information.

Can I transfer MaxCompute configurations from an Alibaba Cloud account to another Alibaba Cloud account by using SQL statements?

No, you cannot transfer MaxCompute configurations from an Alibaba Cloud account to another Alibaba Cloud account by using SQL statements. If you want to transfer MaxCompute configurations from an Alibaba Cloud account to another Alibaba Cloud account, you can use package authorization. For more information about package authorization, see Best practices of MaxCompute multi-team data development project management.

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

Run the following command on the MaxCompute client: 
insert into project.table select * from project_dev.table;
If you do not have read and write permissions on tables in the production environment, you must complete account authorization. For more information about authorization, see Permissions.

How do I determine whether a field is empty?

You can use the operators of MaxCompute SQL to determine whether a field is empty. For more information about the operators of MaxCompute SQL, see Operators.

Can I use Shell nodes of DataWorks to call MaxCompute SQL?

No, you cannot use Shell nodes of DataWorks to call MaxCompute SQL. Shell nodes support only standard shell syntax. Shell nodes do not support interactive syntax. If a large number of jobs exist, you can use ODPS SQL nodes to run jobs. For more information about ODPS SQL nodes, see Create an ODPS SQL node.

How do I run loops in SQL?

You can use do-while nodes of DataWorks to run loops in SQL.

How do I call assignment nodes in SQL?

You can use for-each nodes of DataWorks to call assignment nodes in SQL.

How do I view all SQL statements that are executed on a daily basis in a MaxCompute project?

You can run the show p -all -limit <number>; command to view the historical SQL information of all members in the MaxCompute project. For more information about how to view instance information, see View instance information.

How do I view the amount of resources consumed by an SQL job?

You can view the amount of resources consumed by SQL jobs in your bills. For more information about how to analyze MaxCompute bills, see Analyze MaxCompute bills.

How do I estimate the cost of executing an SQL job?

You can run the cost sql command to estimate the cost of executing an SQL job. For more information about the cost sql command, see Cost estimation.

How do I improve the efficiency of job operation when SQL jobs run slowly?

You can use Logview to diagnose SQL jobs that run slowly. For more information, see Use Logview to diagnose jobs that run slowly.

For more information about how to optimize SQL jobs, see Best practices for computing optimization.