This topic describes the possible causes of the error code ODPS-0130071: Semantic analysis exception and provides solutions.
Error Message 1: the number of input partition columns (n) doesn't equal to table's partition columns (m)
Sample
FAILED: ODPS-0130071:[m,n] Semantic analysis exception - the number of input partition columns (n) doesn't equal to table's partition columns (m)
Description
The table into which data is inserted is a partitioned table. The table has m partition fields, but n (n < m) partition key columns are specified in the SQL statement that is executed to insert data. As a result, data fails to be written to a specific partition, and an error is returned.
Solutions
Modify the SQL statement to ensure that the partition key columns specified in the statement match the partition fields in the table.
Examples
-- Create a table.
CREATE TABLE if NOT EXISTS mf_sale_detail
(
shop_name STRING,
customer_id STRING,
total_price DOUBLE
)
partitioned BY
(
sale_date string,
region string
);
-- Error: The target table has two partition levels, but the PARTITION clause is either missing or incomplete.
INSERT overwrite TABLE mf_sale_detail
VALUES ('s1','c1',100.1),('s2','c2',100.2),('s3','c3',100.3);
FAILED: ODPS-0130071:[1,24] Semantic analysis exception - the number of input partition columns (0) doesn't equal to table's partition columns (2)
-- Correct: Specify all partitions in the PARTITION clause.
INSERT overwrite TABLE mf_sale_detail PARTITION(sale_date = '2013', region = 'china')
VALUES ('s1', 'c1', 100.1), ('s2', 'c2', 100.2), ('s3', 'c3', 100.3);
OK
-- Correct: Use dynamic partitioning.
INSERT overwrite TABLE mf_sale_detail PARTITION(sale_date = '2013', region)
VALUES ('s1', 'c1', 100.1, 'china'), ('s2', 'c2', 100.2, 'china'), ('s3', 'c3', 100.3, 'china');
OKError Message 2: expect equality expression (i.e., only use '=' and 'AND') for join condition without mapjoin hint
Sample
ODPS-0130071:[m,n] Semantic analysis exception - expect equality expression (i.e., only use '=' and 'AND') for join condition without mapjoin hint
Description
In MaxCompute SQL, the sort-merge join is used as a physical algorithm for join operations. The join condition must include an equivalent expression. In actual query operations, shuffling is performed based on the columns of the left and right tables in the equivalent expression.
Solutions
Make sure that the join condition contains an equivalent expression.
Add a map join hint.
NoteIf the ON condition contains only a non-equivalent expression, a large amount of data may be generated after a join operation. As a result, the query operation takes a longer time.
Examples
-- Error: The join condition contains only a non-equality expression.
SELECT t1. *
FROM src t1
JOIN src t2
ON t1.value > t2.value;
FAILED: ODPS-0130071:[4,4] Semantic analysis exception - expect equality expression (i.e., only use '=' and 'AND') for join condition without mapjoin hint
-- Correct: The join condition includes an equality expression (t1.key = t2.key) involving columns from both tables.
SELECT t1. *
FROM src t1
JOIN src t2
ON t1.key = t2.key AND t1.value > t2.value;
-- Correct: Add a mapjoin hint.
SELECT /*+mapjoin(t1)*/ t1. *
FROM src t1
JOIN src t2
ON t1.value > t2.value;Error Message 3: insert into HASH CLUSTERED table/partition xxx is not current supported
Sample
ODPS-0130071:[m,n] Semantic analysis exception - insert into HASH CLUSTERED table/partition xxx is not current supported
Description
The INSERT INTO statement cannot be used to write data to a clustered table.
Solutions
Change the table to a common table.
Change the INSERT INTO statement to the INSERT OVERWRITE statement.
Examples
-- Create a clustered table.
CREATE TABLE sale_detail_hashcluster
(
shop_name STRING,
total_price decimal,
customer_id BIGINT
)
clustered BY(customer_id)
sorted BY(customer_id)
INTO 1024 buckets;
-- Error: Using INSERT INTO with a clustered table.
INSERT INTO sale_detail_hashcluster
VALUES ('a', 123, 'id123');
FAILED: ODPS-0130071:[1,13] Semantic analysis exception - insert into HASH CLUSTERED table/partition meta.sale_detail_hashcluster is not current supported
-- Correct: Use INSERT INTO with a common table.
CREATE TABLE sale_detail
(
shop_name STRING,
total_price decimal,
customer_id BIGINT
);
INSERT INTO sale_detail
VALUES ('a', 123, 'id123');
-- Correct: Use INSERT OVERWRITE with the clustered table.
INSERT overwrite TABLE sale_detail_hashcluster
VALUES ('a', 123, 'id123');Error Message 4: should appear in GROUP BY key
Sample
ODPS-0130071:[m,n] Semantic analysis exception - column reference xx.yy should appear in GROUP BY key
Description
The GROUP BY clause aggregates data in the input table based on the specified key. The following conclusions are obtained after the aggregate operation is performed:
For the column that corresponds to the aggregate key, you can directly obtain output values. You can also call common functions (non-aggregate functions) to further process and calculate data in the column.
For the columns that do not correspond to the aggregate key, you cannot directly obtain output values. You must call aggregate functions such as SUM, COUNT, and AVG to calculate the aggregate results.
Solutions
For the columns that do not correspond to the aggregate key, call aggregate functions such as SUM, COUNT, AVG, and ANY_VALUE to calculate the aggregate result.
Examples of query statements
-- Error: Column `c` is not in the GROUP BY key and is not used in an aggregate function.
SELECT a, sum(b), c
FROM VALUES (1L, 2L, 3L) AS t(a, b, c)
GROUP BY a;
-- Error message is returned.
FAILED: ODPS-0130071:[1,19] Semantic analysis exception - column reference t.c should appear in GROUP BY key
-- Correct: Use the aggregate function any_value() on column `c`.
SELECT a, sum(b), any_value(c)
FROM VALUES (1L, 2L, 3L) AS t(a, b, c)
GROUP BY a;Error Message 5: Invalid partition value
Sample
ODPS-0130071:[m,n] Semantic analysis exception - Invalid partition value: 'xxx'
Description
The value of a partition field is invalid. The values of partition fields in a MaxCompute table must comply with the following rules:
The value of a partition field cannot contain double-byte characters such as Chinese characters. The value must be 1 to 128 bytes in length, and can contain letters, digits, and supported special characters. The value must start with a letter.
Allowed characters include spaces, colons (:), underscores (_), dollar signs ($), number signs (#), periods (.), exclamation marks (!), and at signs (@). The behavior of other characters, such as escape characters like
\t,\n, and/, is undefined.
Solutions
Change the value of the partition field to a valid value.
Examples of query statements
-- Create a table.
CREATE TABLE mc_test
(
a bigint
)
partitioned BY
(
ds string
);
-- Error: The partition value '${today}' is invalid.
ALTER TABLE mc_test ADD PARTITION(ds = '${today}');
-- Error message is returned.
FAILED: ODPS-0130071:[1,40] Semantic analysis exception - Invalid partition value: '${today}'
-- Correct: Change the partition value to a valid one, such as '20221206'.
ALTER TABLE mc_test ADD PARTITION(ds='20221206');Error Message 6: only oss external table support msck repair syntax
Sample
ODPS-0130071:[m,n] Semantic analysis exception - only oss external table support msck repair syntax
Description
Only Object Storage Service (OSS) external tables support the MSCK REPAIR TABLE statement. For more information, see ORC external tables.
Method 1 (Recommended): Automatically discover and add partitions.
With this method, MaxCompute automatically adds missing partitions to an OSS external table based on the directory structure in OSS. This is useful for backfilling a large number of historical partitions at once.
msck repair TABLE <mc_oss_extable_name> ADD partitions [ WITH properties (key:VALUE, key: VALUE ...)];Note
This method is not suitable for adding incremental partitions, especially when an OSS directory contains a large number of partitions (for example, more than 1,000). This is because when the number of new partitions is much smaller than the number of existing partitions, frequently running the
msckcommand results in a large number of repetitive scans of the OSS directory and metadata update requests. This significantly reduces the command's execution efficiency. Therefore, for scenarios that require adding incremental partitions, we recommend that you use Method 2.Method 2: Manually add partitions.
This method is recommended for adding new partitions periodically after the initial historical partitions are loaded. Create the new partitions before running your data write jobs. After a partition is created, the external table can read the latest data from the corresponding OSS directory without needing to refresh the partition.
ALTER TABLE < mc_oss_extable_name >ADD PARTITION (< col_name >= < col_value >)[ ADD PARTITION (< col_name >= < col_value >)...][location URL];The values of col_name and col_value must match the directory names of the partition data files. For example, assume the following OSS directory structure: col_name corresponds to
direction, and col_value corresponds toN, NE, S, SW, W. Oneadd partitionstatement corresponds to one subdirectory, and multiple OSS subdirectories require multipleadd partitionstatements.
Example
Create a directory named
demo8in OSS. Then, create two partition folders in the directory and place the corresponding files in them.Partition folder:
$pt1=1/$pt2=2. File name:demo8-pt1.txt.Partition folder:
$pt1=3/$pt2=4. File name:demo8-pt2.txt.
Create an external table and specify the
ptfields.If the partition column names in the OSS external table do not match the OSS directory structure, specify the directory.
--Create an external table
create external table mf_oss_spe_pt (id int, name string)
partitioned by (pt1 string, pt2 string)
stored as TEXTFILE
location "oss://oss-cn-beijing-internal.aliyuncs.com/mfoss*******/demo8/";
--Specify the partition fields
MSCK REPAIR TABLE mf_oss_spe_pt ADD PARTITIONS
with PROPERTIES ('odps.msck.partition.column.mapping'='pt1:$pt1,pt2:$pt2');
--Query data
select * from mf_oss_spe_pt where pt1=1 and pt2=2;
--Result
+------------+------------+------------+------------+
| id | name | pt1 | pt2 |
+------------+------------+------------+------------+
| 1 | kyle | 1 | 2 |
| 2 | nicole | 1 | 2 |
+------------+------------+------------+------------+
--Query data
select * from mf_oss_spe_pt where pt1=3 and pt2=4;
+------------+------------+------------+------------+
| id | name | pt1 | pt2 |
+------------+------------+------------+------------+
| 3 | john | 3 | 4 |
| 4 | lily | 3 | 4 |
+------------+------------+------------+------------+--The mapping between MaxCompute partitions and OSS directories is as follows:
--pt1=8-->8
--pt2=8-->$pt2=8
--Add a partition
alter table mf_oss_spe_pt add partition (pt1=8,pt2=8)
location 'oss://oss-cn-beijing-internal.aliyuncs.com/mfosscostfee/demo8/8/$pt2=8/';
--Disable commit mode and insert data
set odps.sql.unstructured.oss.commit.mode=false;
insert into mf_oss_spe_pt partition (pt1=8,pt2=8) values (1,'tere');
--Query data
set odps.sql.unstructured.oss.commit.mode=false;
select * from mf_oss_spe_pt where pt1=8 and pt2=8;
+------+------+-----+-----+
| id | name | pt1 | pt2 |
+------+------+-----+-----+
| 1 | tere | 8 | 8 |
+------+------+-----+-----+Solutions
You must create an OSS external table as described in the documentation before you can execute the MSCK REPAIR command.
Examples of query statements
--Create a common table
CREATE TABLE mc_test
(
a BIGINT
)
partitioned BY
(
ds string
);
--Error. A common table cannot perform the msck repair operation.
msck TABLE mc_test ADD partitions;
FAILED: ODPS-0130071:[1,12] Semantic analysis exception - only oss external table support msck repair syntax
Error Message 7: column xx in source has incompatible type yy with destination column zz, which has type ttt
Sample
ODPS-0130071:[m,n] Semantic analysis exception - column xx in source has incompatible type yy with destination column zz, which has type ttt
Description
When data is inserted into a table, the data type of the destination table must match the data type of the inserted data, or the data type of the inserted data can be implicitly converted into the data type of the destination table. Otherwise, an error is returned.
Solutions
Modify the query statement to ensure that the data type of the inserted data matches the data type of the destination table.
Examples of query statements
-- Create a table.
odps> CREATE TABLE mc_test
(
a datetime
);
-- Error: The data types are incompatible.
odps> INSERT overwrite TABLE mc_test
VALUES (1L);
FAILED: ODPS-0130071:[2,9] Semantic analysis exception - column __value_col0 in source has incompatible type BIGINT with destination column a, which has type DATETIME
-- Correct: Insert data of the correct type.
odps> INSERT overwrite TABLE mc_test
VALUES (datetime '2022-12-06 14:23:45');Error Message 8: function datediff cannot match any overloaded functions with (STRING, STRING, STRING), candidates are BIGINT DATEDIFF(DATE arg0, DATE arg1, STRING arg2); BIGINT DATEDIFF(DATETIME arg0, DATETIME arg1, STRING arg2); BIGINT DATEDIFF(TIMESTAMP arg0, TIMESTAMP arg1, STRING arg2); INT DATEDIFF(DATE arg0, DATE arg1); INT DATEDIFF(STRING arg0, STRING arg1); INT DATEDIFF(TIMESTAMP arg0, TIMESTAMP arg1)
Sample
ODPS-0130071:[m,n] Semantic analysis exception - function datediff cannot match any overloaded functions with (STRING, STRING, STRING), candidates are BIGINT DATEDIFF(DATE arg0, DATE arg1, STRING arg2); BIGINT DATEDIFF(DATETIME arg0, DATETIME arg1, STRING arg2); BIGINT DATEDIFF(TIMESTAMP arg0, TIMESTAMP arg1, STRING arg2); INT DATEDIFF(DATE arg0, DATE arg1); INT DATEDIFF(STRING arg0, STRING arg1); INT DATEDIFF(TIMESTAMP arg0, TIMESTAMP arg1)
Description
The data types of the input parameters of the function DATEDIFF do not match. A common type mismatch issue is caused because implicit data type conversions are not allowed after the MaxCompute V2.0 data type edition is enabled.
Solutions
Use one of the following methods:
Add
set odps.sql.type.system.odps2=false;before the SQL query and run them together. This disables data type 2.0 and enables implicit type conversion.Change the data types of the input parameters.
Error Message 9: The role not exists: acs:ram::xxxxxx:role/aliyunodpsdefaultrole
Sample
ODPS-0130071:[1,1] Semantic analysis exception - external table checking failure, error message: java.lang.RuntimeException: {"RequestId":"A7BFAD2F-8982-547A-AB5E-93DAF5061FBD","HostId":"sts.aliyuncs.com","Code":"EntityNotExist.Role","Message":"The role not exists: acs:ram::xxxxxx:role/aliyunodpsdefaultrole. ","Recommend":"https://next.api.aliyun.com/troubleshoot?q=EntityNotExist.Role&product=Sts"}
Description
When you create an OSS external table, you need to specify a RAM role that is used to access OSS. In this case, a non-existing RAM role is used. As a result, role authentication fails.
Solutions
Modify the value of the odps.properties.rolearn parameter. The format for the Alibaba Cloud Resource Name (ARN) is acs:ram::<UID>:role/<Role>.
Take note of the following information:
UID: a 16-digit number.
Role: the name of a role that is configured in the Resource Access Management (RAM) console.
Examples
'odps.properties.rolearn'='acs:ram::189xxxxxxx76:role/aliyunpaiaccessingossrole'
To obtain the ARN, go to the Roles page. On the page, click the role name to view its details.
In the RAM console, on the Identity Management > Roles page, find the AliyunPAIAccessingOSSRole role in the list of roles.
On the role details page, in the Basic Information section, find the ARN field. Click the copy button to its right to get the ARN value.
Error Message 10: encounter runtime exception while evaluating function MAX_PT
Sample
FAILED: ODPS-0130071:[33,26] Semantic analysis exception - encounter runtime exception while evaluating function MAX_PT, detailed message: null
Description
When an SQL statement is executed, the partition that is specified by max_pt is changed. As a result, an error is returned due to data inconsistency.
Solutions
Do not execute SQL statements in which max_pt is specified for new partitions.
Enable the system to rerun a configuration task if the error is reported for the configuration task.
Error Message 11: column xxx cannot be resolved
Sample
ODPS-0130071:[73,12] Semantic analysis exception - column xxx cannot be resolved
Description
The xxx column does not exist in the table.
Solutions
Check the SQL script and update xxx to a valid column name.
Error Message 12: evaluate function in class XX for user defined function YY does not match annotation ZZ
Sample
FAILED: ODPS-0130071:[1,8] Semantic analysis exception - evaluate function in class test.MyPlus for user defined function my_plus does not match annotation bigint->bigint
Description
The code of the Java or Python user-defined function (UDF) does not comply with UDF writing standards. The information in the function signature annotation is not the same as that in the code.
Solutions
Modify the UDF code to ensure that the information in the function signature annotation is the same as that in the code.
Examples of query statements
-- The following is an example of an incorrect Python UDF. The implementation has two input parameters, but the annotation specifies only one.
from odps.udf import annotate
@annotate("bigint->bigint")
class MyPlus(object):
def evaluate(self, arg0, arg1):
if None in (arg0, arg1):
return None
return arg0 + arg1
-- The following is another example of an incorrect Python UDF. The evaluate function is missing the self parameter.
from odps.udf import annotate
@annotate("bigint,bigint->bigint")
class MyPlus(object):
def evaluate(arg0, arg1):
if None in (arg0, arg1):
return None
return arg0 + arg1
-- The following is the correct Python UDF.
from odps.udf import annotate
@annotate("bigint,bigint->bigint")
class MyPlus(object):
def evaluate(self, arg0, arg1):
if None in (arg0, arg1):
return None
return arg0 + arg1Error Message 13: Vpc white list: , Vpc id: vpc-xxxx is not allowed to access
Sample
FAILED: ODPS-0130071:[0,0] Semantic analysis exception - physical plan generation failed: com.aliyun.odps.lot.cbo.plan.splitting.impl.vpc.AliHyperVpcRuntimeException: Vpc white list: , Vpc id: vpc-xxxx is not allowed to access.Contact project owner to set allowed accessed vpc id list.=
Description
When you access services in a VPC by using an external table, the error message appears due to invalid VPC configurations.
Solutions
Configure an IP address whitelist in the VPC and ensure that MaxCompute can access services in the VPC from an IP address in the whitelist. For more information, see the "Access over a VPC (dedicated connection)" section in Network connection process.
Error Message 14: Semantic analysis exception - physical plan generation failed
Sample
FAILED: ODPS-0130071:[0,0] Semantic analysis exception - physical plan generation failed: com.aliyun.odps.common.table.na.NativeException: kNotFound:The role_arn you provide not exists in HOLO auth service. Please check carefully.
Description
When you access an Alibaba Cloud service, the service-linked role (SLR) that is linked to the service is not assigned to your Alibaba Cloud account. As a result, you cannot access the data of the service by using the SLR.
Solutions
Click here to assign the SLR to your Alibaba Cloud account.