在MaxCompute中执行DQL操作过程中的常见问题_云原生大数据计算服务 MaxCompute(MaxCompute)-阿里云帮助中心

本文为您介绍在MaxCompute中执行DQL操作过程中的常见问题。

问题类别	常见问题
GROUP BY	在执行MaxCompute SQL过程中，报错Repeated key in GROUP BY，如何解决？在执行MaxCompute SQL过程中，报错Expression not in GROUP BY key，如何解决？对表A执行GROUP BY生成表B，表B比表A的行数少，但表B的物理存储量是表A的10倍，是什么原因造成的？使用GROUP BY分组查询100亿条数据会不会影响性能？GROUP BY对数据量有没有限制？
ORDER BY	MaxCompute查询得到的数据是根据什么排序的？ MaxCompute是否支持ORDER BY FIELD NULLS LAST语法？执行MaxCompute SQL过程中，报错ORDER BY must be used with a LIMIT clause，如何解决？
子查询	在执行MaxCompute SQL过程中，使用NOT IN后面接子查询，子查询返回的结果是上万级别的数据量，但当IN和NOT IN后面的子查询返回的是分区时，返回的数量上限为1000。在必须使用NOT IN的情况下，该如何实现此查询？
交集、并集和补集	如何合并两个没有任何关联关系的表？在执行UNION ALL操作时，报错ValidateJsonSize error，如何解决？
JOIN	在执行JOIN操作时，报错Both left and right aliases encountered in JOIN，如何解决？在执行JOIN操作时，报错Maximum 16 join inputs allowed，如何解决？在执行JOIN操作时，发现JOIN结果数据条数比原表多，如何解决？在执行JOIN操作时，分区裁剪条件放在ON中分区裁剪会生效，还是放在WHERE中才会生效？
MAPJOIN	如何用MAPJOIN缓存多张小表？ MAPJOIN中的大表和小表是否可以互换位置？
其他	MaxCompute SQL设置过滤条件后，报错提示输入的数据超过100 GB，如何解决？ MaxCompute SQL中模糊查询的WHERE条件是否支持正则表达式？如果只同步100条数据，如何在过滤条件WHERE中通过LIMIT实现？如何能提高查询效率？分区设置能调整吗？ MaxCompute SQL支持WITH AS语句吗？如何将一行数据拆分为多行数据？在客户端的odps_config.ini文件中设置use_instance_tunnel=false，instance_tunnel_max_record=10，为什么Select还是能输出很多记录？如何用正则表达式判断字段是否为中文？

在执行MaxCompute SQL过程中，报错Repeated key in GROUP BY，如何解决？

问题现象

在执行MaxCompute SQL过程中，返回报错如下。

FAILED: ODPS-0130071:Semantic analysis exception - Repeated key in GROUP BY。

产生原因
SELECT DISTINCT后不能跟常量。
解决措施
将SQL拆分为两层，内层处理没有常量的DISTINCT逻辑，外层加入常量数据。

在执行MaxCompute SQL过程中，报错Expression not in GROUP BY key，如何解决？

问题现象

执行MaxCompute SQL时，返回报错如下。

FAILED: ODPS-0130071:Semantic analysis exception - Expression not in GROUP BY key : line 1:xx ‘xxx’

产生原因
不支持直接引用非GROUP BY的列。详情请参见GROUP BY分组查询（col_list）。
解决措施
修改SQL语句，确保在GROUP BY子句中，SELECT查询的列，必须是GROUP BY中的列或聚合函数（例如SUM或COUNT）加工过的列。

对表A执行GROUP BY生成表B，表B比表A的行数少，但表B的物理存储量是表A的10倍，是什么原因造成的？

数据在MaxCompute中是列式压缩存储的，如果同一列的前后数据的内容是相似的，压缩比会比较高。当odps.sql.groupby.skewindata=true打开时，使用SQL写入数据，数据比较分散，压缩比较小。如果希望数据的压缩比较高，您可以在使用SQL写入数据时进行局部排序。

使用GROUP BY分组查询100亿条数据会不会影响性能？GROUP BY对数据量有没有限制？

使用GROUP BY分组查询100亿条数据不会影响性能。GROUP BY对数据量无限制。GROUP BY分组查询详情请参见GROUP BY分组查询（col_list）。

MaxCompute查询得到的数据是根据什么排序的？

MaxCompute中表的读取是无序的。如果您没有进行自定义设置，查询获取的结果也是无序的。

如果您对数据的顺序有要求，需要对数据进行排序。例如，在SQL中需要加上order by xx limit n对数据进行排序。

如果您需要对数据进行全排序，只需要将limit面的n设置为数据总条数+1即可。

说明

海量数据的全排序，对性能的影响非常大，而且很容易造成内存溢出问题，请尽量避免执行该操作。

MaxCompute是否支持ORDER BY FIELD NULLS LAST语法？

MaxCompute支持此语法。MaxCompute支持的语法请参见与其他SQL语法的差异。

执行MaxCompute SQL过程中，报错ORDER BY must be used with a LIMIT clause，如何解决？

问题现象

执行MaxCompute SQL过程中，返回报错如下。

FAILED: ODPS-0130071:[1,27] Semantic analysis exception - ORDER BY must be used with a LIMIT clause, please set odps.sql.validate.orderby.limit=false to use it.

产生原因
ORDER BY需要对单个执行节点做全局排序，所以默认带LIMIT限制，避免误用导致单点处理大量数据。
解决措施
如果您的使用场景确实需要ORDER BY放开LIMIT限制，可以通过如下两种方式实现：
- Project级别：设置setproject odps.sql.validate.orderby.limit=false;关闭order by必须带limit的限制。
- Session级别：设置set odps.sql.validate.orderby.limit=false;关闭order by必须带limit的限制，需要与SQL语句一起提交。
  说明
  如果关闭order by必须带limit的限制，在单个执行节点有大量数据排序的情况下，资源消耗或处理时长等性能表现会受到影响。

更多ORDER BY信息，请参见ORDER BY全局排序（order_condition）。

在执行MaxCompute SQL过程中，使用NOT IN后面接子查询，子查询返回的结果是上万级别的数据量，但当IN和NOT IN后面的子查询返回的是分区时，返回的数量上限为1000。在必须使用NOT IN的情况下，该如何实现此查询？

您可以使用left outer join命令查询。

select * from a where a.ds not in (select ds from b);
改成如下语句。
select a.* from a left outer join (select distinct ds from b) bb on a.ds=bb.ds where bb.ds is null;

如何合并两个没有任何关联关系的表？

您可以通过union all运算完成纵向合并。横向合并可以通过row_number函数实现，两个表都新加一个ID列，进行ID关联，然后取两张表的字段。更多信息，请参见并集或ROW_NUMBER。

在执行UNION ALL操作时，报错ValidateJsonSize error，如何解决？

问题现象

执行包含200个UNION ALL的SQL语句select count(1) as co from client_table union all ...时，返回报错如下。

FAILED: build/release64/task/fuxiWrapper.cpp(344): ExceptionBase: Submit fuxi Job failed, {
    "ErrCode": "RPC_FAILED_REPLY",
    "ErrMsg": "exception: ExceptionBase:build/release64/fuxi/fuximaster/fuxi_master.cpp(1018): ExceptionBase: StartAppFail: ExceptionBase:build/release64/fuxi/fuximaster/app_master_mgr.cpp(706): ExceptionBase: ValidateJsonSize error: the size of compressed plan is larger than 1024KB\nStack

产生原因
- 原因一：SQL语句转化为执行计划后，超过了底层架构限制的1024 KB，导致SQL执行报错。执行计划的长度与SQL语句长度没有直接换算关系，暂时无法预估。
- 原因二：由于分区量过大导致执行计划超过限制。
- 原因三：由于小文件比较多导致SQL运行失败。
解决措施
- 原因一的解决措施：对于过长的SQL语句，建议拆分成多次运行，避免触发长度限制。
- 原因二的解决措施：如果分区过大，需要调整分区个数，详情请参见分区。
- 原因三的解决措施：如果是由于小文件较多导致，请参见小文件优化及作业诊断常见问题。

在执行JOIN操作时，报错Both left and right aliases encountered in JOIN，如何解决？

问题现象

执行MaxCompute SQL过程中，返回报错如下。

FAILED: ODPS-0130071:Semantic analysis exception - Both left and right aliases encountered in JOIN : line 3:3 ‘xx’: . I f you really want to perform this join, try mapjoin

产生原因
- 原因一：SQL关联条件ON中包含非等值连接，例如table1.c1>table2.c3。
- 原因二：SQL中JOIN条件的某一侧数据来自两张表，例如table1.col1 = concat(table1.col2,table2.col3)。
解决措施
- 原因一的解决措施：修改SQL语句，关联条件需要为等值连接。
  说明
  如必须使用非等值连接，可以增加mapjoin hint，详情请参见ODPS-0130071。
- 原因二的解决措施：如果其中一张表比较小，您可以使用MAPJOIN方法。

在执行JOIN操作时，报错Maximum 16 join inputs allowed，如何解决？

问题现象

在执行MaxCompute SQL过程中，返回报错如下。

FAILED: ODPS-0123065:Join exception - Maximum 16 join inputs allowed

产生原因
MaxCompute SQL最多支持6张小表的MAPJOIN，并且连续JOIN的表不能超过16张。
解决措施
将部分小表JOIN成一张临时表作为输入表，减少输入表的个数。

在执行JOIN操作时，发现JOIN结果数据条数比原表多，如何解决？

问题现象
执行如下MaxCompute SQL语句后，查询返回结果的条数大于table1的数据条数。
```
select count(*) from table1 a left outer join table2 b on a.ID = b.ID;
```
产生原因
示例SQL是table1通过ID字段和table2的ID字段做左外关联，所以会出现以下情况：
- 如果table2表中找不到关联数据，table1也会返回一条数据。
- 如果table1找不到但是table2能找到关联数据，则不返回结果。
- 如果table1和table2都能找到关联数据，该关联逻辑和普通的内关联一样。如果同样的ID字段在table2中能找到数据，返回结果为table1和table2的笛卡尔积。
假设table1的数据如下。
id
values
1
a
1
b
2
c
假设table2的数据如下。
id
values
1
A
1
B
3
D
执行select count(*) from table1 a left outer join table2 b on a.ID = b.ID;命令返回的结果如下。
id1
values1
id2
values2
1
b
1
B
1
b
1
A
1
a
1
B
1
a
1
A
2
c
NULL
NULL
- id=1的数据两边都有，执行笛卡尔积，返回4条数据。
- id=2的数据只有table1有，返回了1条数据。
- id=3的数据只有table2有，table1里没数据，不返回数据。
解决措施
确认出现数据条数增加是否是因为table2的数据导致。命令示例如下。此处增加limit 10是考虑到如果table2中的数据条数很多，会刷屏。如果只是确认问题，验证前几条数据即可。
```
select id, count() as cnt from table2 group by id having cnt>1 limit 10;
```
如果是在重复的情况下不希望执行笛卡尔积，希望有类似SQL里IN的功能，可以改写SQL为如下语句。
```
select * from table1 a left outer join (select distinct id from table2) b on a.id = b.id;
```

在执行JOIN操作时，已经指定了分区条件，为何提示禁止全表扫描？

问题现象

在两个项目里执行如下同一段代码，一个项目中成功，一个项目中失败。

select t.stat_date 
from fddev.tmp_001 t  
left outer join (select '20180830' as ds from fddev.dual ) t1 
on t.ds = 20180830
group by t.stat_date;

失败报错如下。

Table(fddev，tmp_001) is full scan with all partisions,please specify partitions predicates.

产生原因
在执行SELECT操作时，如果需要指定分区请使用WHERE子句。使用ON属于非标准用法。
执行成功的项目设置了允许非标准SQL的行为，即执行了set odps.sql.outerjoin.supports.filters=false命令，该配置会把ON里的条件转换为过滤条件，可用于兼容Hive语法，但不符合SQL标准。
解决措施
建议将分区过滤条件置于WHERE子句。

在执行JOIN操作时，分区裁剪条件放在ON中分区裁剪会生效，还是放在WHERE中才会生效？

如果分区剪裁条件置于WHERE语句中，分区剪裁会生效。如果分区剪裁条件置于ON语句中，从表的分区剪裁会生效，主表的分区剪裁不会生效即会全表扫描。更多分区裁剪信息，请参见分区剪裁合理性评估。

如何用MAPJOIN缓存多张小表？

您可以在MAPJOIN中填写表的别名。

假设项目中存在一张表iris，表数据如下。

+——————————————————————————————————————————+

| Field           | Type       | Label | Comment                                     |
+——————————————————————————————————————————+

| sepal_length    | double     |       |                                             |

| sepal_width     | double     |       |                                             |

| petal_length    | double     |       |                                             |

| petal_width     | double     |       |                                             |

| category        | string     |       |                                             |

+——————————————————————————————————————————+

使用MAPJOIN实现缓存小表的命令示例如下。

select 
  /*+ mapjoin(b,c) */
  a.category,
  b.cnt as cnt_category,
  c.cnt as cnt_all
from iris a
join
(
  select count() as cnt,category from iris group by category
) b
on a.category = b.category
join 
(
  select count(*) as cnt from iris
) c;

MAPJOIN中的大表和小表是否可以互换位置？

MAPJOIN中的大表和小表是根据表占用空间Size大小区分的。

系统会将您指定的小表全部加载到执行JOIN操作的程序的内存中，继而加快JOIN的执行速度。如果将大表和小表互换位置，系统不会报错，但是性能会变差。

MaxCompute SQL设置过滤条件后，报错提示输入的数据超过100 GB，如何解决？

先过滤分区，再取数据。取数据后，再过滤其他非分区字段。输入表的大小是取决于过滤分区过滤后，过滤其他字段前表的大小。

MaxCompute SQL中模糊查询的WHERE条件是否支持正则表达式？

支持，例如select * from user_info where address rlike '[0-9]{9}';，表示查找9位数字组成的ID。

如果只同步100条数据，如何在过滤条件WHERE中通过LIMIT实现？

LIMIT不支持在过滤条件中使用。您可以先使用SQL筛选出100条数据，再执行同步操作。

如何能提高查询效率？分区设置能调整吗？

当利用分区字段对表进行分区时，新增分区、更新分区和读取分区数据均不需要做全表扫描，可以提高处理效率。详情请参见表操作。

MaxCompute SQL支持WITH AS语句吗？

支持，MaxCompute支持SQL标准的CTE，提高SQL语句的可读性与执行效率。更多信息，请参见COMMON TABLE EXPRESSION（CTE）。

如何将一行数据拆分为多行数据？

Lateral View和表生成函数（例如Split和Explode）结合使用，可以将一行数据拆成多行数据，并对拆分后的数据进行聚合。

在客户端的odps_config.ini文件中设置use_instance_tunnel=false，instance_tunnel_max_record=10，为什么Select还是能输出很多记录？

需要修改use_instance_tunnel=false为use_instance_tunnel=true，才能通过instance_tunnel_max_record控制输出记录数。

如何用正则表达式判断字段是否为中文？

命令示例如下。

select '字段' rlike '[\\x{4e00}-\\x{9fa5}]+';

id1	values1	id2	values2
1	b	1	B
1	b	1	A
1	a	1	B
1	a	1	A
2	c	NULL	NULL

id	values
1	a
1	b
2	c

id	values
1	A
1	B
3	D