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

问题类别 常见问题
GROUP BY
ORDER BY
子查询 在执行MaxCompute SQL过程中,使用NOT IN后面接子查询,子查询返回的结果是上万级别的数据量,但当IN和NOT IN后面的子查询返回的是分区时,返回的数量上限为1000。在必须使用NOT IN的情况下,该如何实现此查询?
交集、并集和补集
JOIN
MAPJOIN
其他

在执行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 not 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方法。

在执行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 partition predicates.
  • 产生原因

    在执行SELECT操作时,如果需要指定分区请使用WHERE子句。使用ON属于非标准用法。

    执行成功的项目设置了允许非标准SQL的行为,即执行了set odps.sql.outerjoin.supports.filters=true命令,该配置会把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的分区配置和使用

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=falseuse_instance_tunnel=true,才能通过instance_tunnel_max_record控制输出记录数。

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

命令示例如下。
select '字段' rlike '[\\x{4e00}-\\x{9fa5}]+';