SQL查询语法与实践FAQ-云原生大数据计算服务 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写入数据时进行局部排序。

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

不影响，因为GROUP BY对数据量无限制。

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

产生原因
- 原因一：执行计划超过了底层架构限制的1024 KB，导致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;
```
产生原因
左外连接会返回table1的所有数据，即使在table2中找不到匹配项。如果table2中有重复ID，会导致返回的结果条数增加。如下所示：
假设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中是否有重复数据。命令示例如下：

select id, count() as cnt from table2 group by id having cnt>1 limit 10;

如果不希望执行笛卡尔积，可以改写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子句是非标准用法。
成功的项目设置了set odps.sql.outerjoin.supports.filters=false命令，将ON子句中的条件转换为过滤条件，兼容Hive语法但不符合SQL标准。
解决措施
建议将分区过滤条件置于WHERE子句。

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

分区剪裁条件置于WHERE语句中时，分区剪裁会生效。
置于ON语句中时，从表的分区裁剪会生效，但主表不会生效即会全表扫描。

更多分区裁剪信息，请参见分区剪裁合理性评估。

如何用MAPJOIN缓存多张小表？

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中的大表和小表是根据表占用空间大小区分的。系统会将小表加载到内存中，加快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，以提高可读性和执行效率。详情请参见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