全部产品
存储与CDN 数据库 安全 应用服务 数加·人工智能 数加·大数据基础服务 互联网中间件 视频服务 开发者工具 解决方案 物联网
MaxCompute

修改不兼容SQL实战

更新时间:2017-10-23 23:54:52

MaxCompute 开发团队近期已经完成了 MaxCompute2.0 灰度升级。新升级的版本完全拥抱开源生态,支持更多的语言功能,带来更快的运行速度,同时新版本会执行更严格的语法检测,以致于一些在老编译器下正常执行的不严谨的语法 case 在 MaxCompute2.0 下会报错。

为了使 MaxCompute2.0 灰度升级更加平滑,MaxCompute 框架支持回退机制,如果 MaxCompute2.0 任务失败,会回退到 MaxCompute1.0 执行。回退本身会增加任务 E2E 时延。鼓励大家提交作业之前,手动关闭回退set odps.sql.planner.mode=lot;以避免 MaxCompute 框架回退策略修改对大家造成影响。

MaxCompute 团队会根据线上回退情况,邮件或者钉钉等通知有问题任务的 Owner,请大家尽快完成 SQL 任务修改,否则会导致任务失败。烦请大家仔细 check 以下报错情况,进行自检,以免通知遗漏造成任务失败。

下面列举常见的一些会报错的语法:

group.by.with.star

SELECT * …GROUP BY… 的问题。

旧版 MaxCompute 中,即使 * 中覆盖的列不在 group by key 内,也支持 select * from group by key 的语法,但 MaxCompute2.0 和 Hive 兼容,并不允许这种写法,除非 group by 列表是所有源表中的列。示例如下:

场景一:group by key 不包含所有列

错误写法:

  1. SELECT * FROM t GROUP BY key;

报错信息:

  1. FAILED: ODPS-0130071:[1,8] Semantic analysis exception - column reference t.value should appear in GROUP BY key

正确改法:

  1. SELECT DISTINCT key FROM t;

场景二:group by key 包含所有列

不推荐写法:

  1. SELECT * FROM t GROUP BY key, value; -- t has columns key and value

虽然 MaxCompute2.0 不会报错,但推荐改为:

  1. SELECT DISTINCT key, value FROM t;

bad.escape

错误的 escape 序列问题。

按照 MaxCompute 文档的规定,在 string literal 中应该用反斜线加三位8进制数字表示从 0 到 127 的 ASCII 字符,例如:使用 \001, \002 表示 0,1 等。但目前\01,\0001 也被当作 \001 处理了。

这种行为会给新用户带来困扰,比如需要用 “\0001” 表示 “\000” + “1”,便没有办法实现。同时对于从其他系统迁移而来的用户而言,会导致正确性错误。

注意

\000后面在加数字,如\0001 - \0009\00001的写法可能会返回错误。

MaxCompute2.0 会解决此问题,需要 script 作者将错误的序列进行修改,示例如下:

错误写法:

  1. SELECT split(key, "\01"), value like "\0001" FROM t;

报错信息:

  1. FAILED: ODPS-0130161:[1,19] Parse exception - unexpected escape sequence: 01
  2. ODPS-0130161:[1,38] Parse exception - unexpected escape sequence: 0001

正确改法:

  1. SELECT split(key, "\001"), value like "\001" FROM t;

column.repeated.in.creation

create table 时列名重复的问题。

如果 create table 时列名重复,MaxCompute2.0 将会报错,示例如下:

错误写法:

  1. CREATE TABLE t (a BIGINT, b BIGINT, a BIGINT);

报错信息:

  1. FAILED: ODPS-0130071:[1,37] Semantic analysis exception - column repeated in creation: a

正确改法:

  1. CREATE TABLE t (a BIGINT, b BIGINT);

string.join.double

写 JOIN 条件时,等号的左右两边分别是 String 和 Double 类型。

出现上述情况,旧版 MaxCompute 会把两边都转成 Bigint,但会导致严重的精度损失问题,例如:1.1 = “1” 在连接条件中会被认为是相等的。但 MaxCompute2.0 会与 Hive 兼容转为 Double。

不推荐写法:

  1. SELECT * FROM t1 JOIN t2 ON t1.double_value = t2.string_value;

warning 信息:

  1. WARNING:[1,48] implicit conversion from STRING to DOUBLE, potential data loss, use CAST function to suppress

推荐改法:

  1. select * from t1 join t2 on t.double_value = cast(t2.string_value as double);

除以上改法外,也可使用用户期望的其他转换方式。

window.ref.prev.window.alias

Window Function 引用同级 Select List 中的其他 Window Function Alias 的问题。

示例如下:

如果 rn 在 t1 中不存在,错误写法如下:

  1. SELECT row_number() OVER (PARTITION BY c1 ORDER BY c1) rn,
  2. row_number() OVER (PARTITION by c1 ORDER BY rn) rn2
  3. FROM t1;

报错信息:

  1. FAILED: ODPS-0130071:[2,45] Semantic analysis exception - column rn cannot be resolved

正确改法:

  1. SELECT row_number() OVER (PARTITION BY c1 ORDER BY rn) rn2
  2. FROM
  3. (
  4. SELECT c1, row_number() OVER (PARTITION BY c1 ORDER BY c1) rn
  5. FROM t1
  6. ) tmp;

select.invalid.token.after.star

select * 后面接 alias 的问题。

Select 列表里面允许用户使用 * 代表选择某张表的全部列,但 * 后面不允许加 alias(即使 * 展开之后只有一列也不允许),新一代编译器将会对类似语法进行报错,示例如下:

错误写法:

  1. select * as alias from dual;

报错信息:

  1. FAILED: ODPS-0130161:[1,10] Parse exception - invalid token 'as'

正确改法:

  1. select * from dual;

agg.having.ref.prev.agg.alias

有 Having 的情况下,Select List 可以出现前面 Aggregate Function Alias 的问题。示例如下:

错误写法:

  1. SELECT count(c1) cnt,
  2. sum(c1) / cnt avg
  3. FROM t1
  4. GROUP BY c2
  5. HAVING cnt > 1;

报错信息:

  1. FAILED: ODPS-0130071:[2,11] Semantic analysis exception - column cnt cannot be resolved
  2. ODPS-0130071:[2,11] Semantic analysis exception - column reference cnt should appear in GROUP BY key

其中 s、cnt 在源表 t1 中都不存在,但因为有 HAVING,旧版 MaxCompute 并未报错,MaxCompute2.0 则会提示 column cannot be resolve,并报错。

正确改法:

  1. SELECT cnt, s, s/cnt avg
  2. FROM
  3. (
  4. SELECT count(c1) cnt,
  5. sum(c1) s
  6. FROM t1
  7. GROUP BY c2
  8. HAVING count(c1) > 1
  9. ) tmp;

order.by.no.limit

ORDER BY 后没有 LIMIT 语句的问题。

MaxCompute 默认 order by 后需要增加 limit 限制数量,因为 order by 是全量排序,没有 limit 时执行性能较低。示例如下:

错误写法:

  1. select * from (select *
  2. from (select cast(login_user_cnt as int) as uv, '3' as shuzi
  3. from test_login_cnt where type = 'device' and type_name = 'mobile') v
  4. order by v.uv desc) v
  5. order by v.shuzi limit 20;

报错信息:

  1. FAILED: ODPS-0130071:[4,1] Semantic analysis exception - ORDER BY must be used with a LIMIT clause

正确改法:

在子查询 order by v.uv desc 中增加 limit。

另外,MaxCompute1.0 对于 view 的检查不够严格。比如在一个不需要检查 LIMIT 的 Projec(odps.sql.validate.orderby.limit=false)中,创建了一个 View:

  1. CREATE VIEW dual_view AS SELECT id FROM dual ORDER BY id;

若访问此 View:

  1. SELECT * FROM dual_view;

MaxCompute1.0 不会报错,而 MaxCompute2.0 会报如下错误信息:

  1. FAILED: ODPS-0130071:[1,15] Semantic analysis exception - while resolving view xdj.xdj_view_limit - ORDER BY must be used with a LIMIT clause

generated.column.name.multi.window

使用自动生成的 alias 的问题。

旧版 MaxCompute 会为 Select 语句中的每个表达式自动生成一个 alias,这个 alias 会最后显示在 console 上。但是,它并不承诺这个 alias 的生成规则,也不承诺这个 alias 的生成规则会保持不变,所以不建议用户使用自动生成的 alias。

MaxCompute2.0 会对使用自动生成 alias 的情况给予警告,由于牵涉面较广,暂时无法直接给予禁止。

对于某些情况,MaxCompute 的不同版本间生成的 alias 规则存在已知的变动,但因为已有一些线上作业依赖于此类 alias,这些查询在 MaxCompute 版本升级或者回滚时可能会失败,存在此问题的用户,请修改您的查询,对于感兴趣的列,显式地指定列的别名。示例如下:

不推荐写法:

  1. SELECT _c0 FROM (SELECT count(*) FROM dual) t;

建议改法:

  1. SELECT c FROM (SELECT count(*) c FROM dual) t;

non.boolean.filter

使用了非 boolean 过滤条件的问题。

MaxCompute 不允许布尔类型与其他类型之间的隐式转换,但旧版 MaxCompute 会允许用户在某些情况下使用 Bigint 作为过滤条件。MaxCompute2.0 将不再允许,如果您的脚本中存在这样的过滤条件,请及时修改。示例如下:

错误写法:

  1. select id, count(*) from dual group by id having id;

报错信息:

  1. FAILED: ODPS-0130071:[1,50] Semantic analysis exception - expect a BOOLEAN expression

正确改法:

  1. select id, count(*) from dual group by id having id <> 0;

post.select.ambiguous

在 order by、 cluster by、 distribute by、sort by 等语句中,引用了名字冲突的列的问题。

旧版 MaxCompute 中,系统会默认选取 Select 列表中的后一列作为操作对象,MaxCompute2.0 将会进行报错,请及时修改。示例如下:

错误写法:

  1. select a, b as a from t order by a limit 10;

报错信息:

  1. FAILED: ODPS-0130071:[1,34] Semantic analysis exception - a is ambiguous, can be both t.a or null.a

正确改法:

  1. select a as c, b as a from t order by a limit 10;

本次推送修改会包括名字虽然冲突但语义一样的情况,虽然不会出现歧义,但是考虑到这种情况容易导致错误,作为一个警告,希望用户进行修改。

duplicated.partition.column

在 query 中指定了同名的 partition 的问题。

旧版 MaxCompute 在用户指定同名 partition key 时并未报错, 而是后一个的值直接覆盖了前一个,容易产生混乱。MaxCompute2.0 将会对此情况进行报错,示例如下:

错误写法一:

  1. insert overwrite table partition (ds = '1', ds = '2'select ... ;

实际上,在运行时 ds = ‘1’ 被忽略。

正确改法:

  1. insert overwrite table partition (ds = '2'select ... ;

错误写法二:

  1. create table t (a bigint, ds string) partitioned by (ds string);

正确改法:

  1. create table t (a bigint) partitioned by (ds string);

order.by.col.ambiguous

Select list 中 alias 重复,之后的 Order by 子句引用到重复的 alias 的问题。

错误写法:

  1. SELECT id, id
  2. FROM dual
  3. ORDER BY id;

正确改法:

  1. SELECT id, id id2
  2. FROM dual
  3. ORDER BY id;

需要去掉重复的 alias,Order by 子句再进行引用。

in.subquery.without.result

colx in subquery 没有返回任何结果,则 colx 在源表中不存在的问题。

错误写法:

  1. SELECT * FROM dual
  2. WHERE not_exist_col IN (SELECT id FROM dual LIMIT 0);

报错信息:

  1. FAILED: ODPS-0130071:[2,7] Semantic analysis exception - column not_exist_col cannot be resolved

ctas.if.not.exists

目标表语法错误问题。

如果目标表已经存在,旧版 MaxCompute 不会做任何语法检查,MaxCompute2.0 则会做正常的语法检查,这种情况会出现很多错误信息,示例如下:

错误写法:

  1. CREATE TABLE IF NOT EXISTS dual
  2. AS
  3. SELECT * FROM not_exist_table;

报错信息:

  1. FAILED: ODPS-0130131:[1,50] Table not found - table meta_dev.not_exist_table cannot be resolved

worker.restart.instance.timeout

旧版 MaxCompute UDF 每输出一条记录,便会触发一次对分布式文件系统的写操作,同时会向 Fuxi 发送心跳,如果 UDF 10 分钟没有输出任何结果,会得到如下错误提示:

  1. FAILED: ODPS-0123144: Fuxi job failed - WorkerRestart errCode:252,errMsg:kInstanceMonitorTimeout, usually caused by bad udf performance.

MaxCompute2.0 的 Runtime 框架支持向量化,一次会处理某一列的多行来提升执行效率。但向量化可能导致原来不会报错的语句(2 条记录的输出时间间隔不超过 10 分钟),因为一次处理多行,没有及时向 Fuxi 发送心跳而导致 timeout。

遇到这个错误,建议首先检查 UDF 是否有性能问题,每条记录需要数秒的处理时间。如果无法优化 UDF 性能,可以尝试手动设置 batch row 大小来绕开(默认为1024):

  1. set odps.sql.executionengine.batch.rowcount=16;

divide.nan.or.overflow

旧版 MaxCompute 不会做除法常量折叠的问题。

比如如下语句,旧版 MaxCompute 对应的物理执行计划如下:

  1. EXPLAIN
  2. SELECT IF(FALSE, 0/0, 1.0)
  3. FROM dual;
  4. In Task M1_Stg1:
  5. Data source: meta_dev.dual
  6. TS: alias: dual
  7. SEL: If(False, Divide(UDFToDouble(0), UDFToDouble(0)), 1.0)
  8. FS: output: None

由此可以看出,IF 和 Divide 函数仍然被保留,运行时因为 IF 第一个参数为 false,第二个参数 Divide 的表达式不需要求值,所以不会出现除零异常。

而 MaxCompute2.0 则支持除法常量折叠,所以会报错。如下所示:

错误写法:

  1. SELECT IF(FALSE, 0/0, 1.0)
  2. FROM dual;

报错信息:

  1. FAILED: ODPS-0130071:[1,19] Semantic analysis exception - encounter runtime exception while evaluating function /, detailed message: DIVIDE func result NaN, two params are 0.000000 and 0.000000

除了上述的 nan,还可能遇到 overflow 错误,比如:

错误写法:

  1. SELECT IF(FALSE, 1/0, 1.0)
  2. FROM dual;

报错信息:

  1. FAILED: ODPS-0130071:[1,19] Semantic analysis exception - encounter runtime exception while evaluating function /, detailed message: DIVIDE func result overflow, two params are 1.000000 and 0.000000

正确改法:

建议去掉 /0 的用法,换成合法常量。

CASE WHEN 常量折叠也有类似问题,比如:CASE WHEN TRUE THEN 0 ELSE 0/0,MaxCompute2.0 常量折叠时所有子表达式都会求值,导致除0错误。

CASE WHEN 可能涉及更复杂的优化场景,比如:

  1. SELECT CASE WHEN key = 0 THEN 0 ELSE 1/key END
  2. FROM (
  3. SELECT 0 AS key FROM src
  4. UNION ALL
  5. SELECT key FROM src) r;

优化器会将除法下推到子查询中,转换类似于:

  1. M (
  2. SELECT CASE WHEN 0 = 0 THEN 0 ELSE 1/0 END c1 FROM src
  3. UNION ALL
  4. SELECT CASE WHEN key = 0 THEN 0 ELSE 1/key END c1 FROM src) r;

报错信息:

  1. FAILED: ODPS-0130071:[0,0] Semantic analysis exception - physical plan generation failed: java.lang.ArithmeticException: DIVIDE func result overflow, two params are 1.000000 and 0.000000

其中 UNION ALL 第一个子句常量折叠报错,建议将 SQL 中的 CASE WHEN 挪到子查询中,并去掉无用的 CASE WHEN 和去掉/0用法:

  1. SELECT c1 END
  2. FROM (
  3. SELECT 0 c1 END FROM src
  4. UNION ALL
  5. SELECT CASE WHEN key = 0 THEN 0 ELSE 1/key END) r;

small.table.exceeds.mem.limit

旧版 MaxCompute 支持 Multi-way Join 优化,多个 Join 如果有相同 Join Key,会合并到一个 Fuxi Task 中执行,比如下面例子中的 J4_1_2_3_Stg1:

  1. EXPLAIN
  2. SELECT t1.*
  3. FROM t1 JOIN t2 ON t1.c1 = t2.c1
  4. JOIN t3 ON t1.c1 = t3.c1;

旧版 MaxCompute 物理执行计划:

  1. In Job job0:
  2. root Tasks: M1_Stg1, M2_Stg1, M3_Stg1
  3. J4_1_2_3_Stg1 depends on: M1_Stg1, M2_Stg1, M3_Stg1
  4. In Task M1_Stg1:
  5. Data source: meta_dev.t1
  6. In Task M2_Stg1:
  7. Data source: meta_dev.t2
  8. In Task M3_Stg1:
  9. Data source: meta_dev.t3
  10. In Task J4_1_2_3_Stg1:
  11. JOIN: t1 INNER JOIN unknown INNER JOIN unknown
  12. SEL: t1._col0, t1._col1, t1._col2
  13. FS: output: None

如果增加 MapJoin hint,旧版 MaxCompute 物理执行计划不会改变。也就是说对于旧版 MaxCompute 优先应用 Multi-way Join 优化,并且可以忽略用户指定 MapJoin hint。

  1. EXPLAIN
  2. SELECT /*+mapjoin(t1)*/ t1.*
  3. FROM t1 JOIN t2 ON t1.c1 = t2.c1
  4. JOIN t3 ON t1.c1 = t3.c1;

旧版 MaxCompute 物理执行计划同上。

MaxCompute2.0 Optimizer 会优先使用用户指定的 MapJoin hint,对于上述例子,如果 t1 比较大的话,会遇到类似错误:

  1. FAILED: ODPS-0010000:System internal error - SQL Runtime Internal Error: Hash Join Cursor HashJoin_REL small table exceeds, memory limit(MB) 640, fixed memory used …, variable memory used

对于这种情况,如果 MapJoin 不是期望行为,建议去掉 MapJoin hint。

sigkill.oom

同 small.table.exceeds.mem.limit,如果用户指定了 MapJoin hint,并且用户本身所指定的小表比较大。在旧版 MaxCompute 下有可能被优化成 Multi-way Join 从而成功。但在 MaxCompute2.0 下,用户可能通过设定 odps.sql.mapjoin.memory.max 来避免小表超限的错误,但每个 MaxCompute worker 有固定的内存限制,如果小表本身过大,则 MaxCompute worker 会由于内存超限而被杀掉,错误类似于:

  1. Fuxi job failed - WorkerRestart errCode:9,errMsg:SigKill(OOM), usually caused by OOM(out of memory).

这里建议您去掉 MapJoin hint,使用 Multi-way Join。

wm_concat.first.argument.const

聚合函数 中关于 WM_CONCAT 的说明,一直要求 WM_CONCAT 第一个参数为常量,旧版 MaxCompute 检查不严格,比如源表没有数据,就算 WM_CONCAT 第一个参数为 ColumnReference,也不会报错。

  1. 函数声明:
  2. string wm_concat(string separator, string str)
  3. 参数说明:
  4. separatorString类型常量,分隔符。其他类型或非常量将引发异常。

MaxCompute2.0,会在 plan 阶段便检查参数的合法性,假如 WM_CONCAT 的第一个参数不是常量,会立即报错。示例如下:

错误写法:

  1. SELECT wm_concat(value, ',') FROM src GROUP BY value;

报错信息:

  1. FAILED: ODPS-0130071:[0,0] Semantic analysis exception - physical plan generation failed: com.aliyun.odps.lot.cbo.validator.AggregateCallValidator$AggregateCallValidationException: Invalid argument type - The first argument of WM_CONCAT must be constant string.

pt.implicit.convertion.failed

srcpt 是一个分区表,并有两个分区:

  1. CREATE TABLE srcpt(key STRING, value STRING) PARTITIONED BY (pt STRING);
  2. ALTER TABLE srcpt ADD PARTITION (pt='pt1');
  3. ALTER TABLE srcpt ADD PARTITION (pt='pt2');

对于以上 SQL,String 类型 pt 列 IN INT 类型常量,都会转为 Double 进行比较。即使 Project 设置了 odps.sql.udf.strict.mode=true,旧版 MaxCompute 不会报错,所有 pt 都会过滤掉,而 MaxCompute2.0 会直接报错。示例如下:

错误写法:

  1. SELECT key FROM srcpt WHERE pt IN (1, 2);

报错信息:

  1. FAILED: ODPS-0130071:[0,0] Semantic analysis exception - physical plan generation failed: java.lang.NumberFormatException: ODPS-0123091:Illegal type cast - In function cast, value 'pt1' cannot be casted from String to Double.

建议避免 String 分区列和 INT 类型常量比较,将 INT 类型常量改成 String 类型。

having.use.select.alias

SQL 规范定义 Group by + Having 子句是 Select 子句之前阶段,所以 Having 中不应该使用 Select 子句生成的 Column alias,示例如下:

错误写法:

  1. SELECT id id2 FROM DUAL GROUP BY id HAVING id2 > 0;

报错信息:

  1. FAILED: ODPS-0130071:[1,44] Semantic analysis exception - column id2 cannot be resolved
  2. ODPS-0130071:[1,44] Semantic analysis exception - column reference id2 should appear in GROUP BY key

其中 id2 为 Select 子句中新生成的 Column alias,不应该在 Having 子句中使用。

dynamic.pt.to.static

MaxCompute2.0 动态分区某些情况会被优化器转换成静态分区处理,示例如下:

  1. INSERT OVERWRITE TABLE srcpt PARTITION(pt) SELECT id, 'pt1' FROM dual;

会被转化成

  1. INSERT OVERWRITE TABLE srcpt PARTITION(pt='pt1') SELECT id FROM dual;

如果用户指定的分区值不合法,比如错误的使用了’${bizdate}’,MaxCompute2.0 语法检查阶段便会报错。详情请参见 MaxCompute 分区值定义说明

错误写法:

  1. INSERT OVERWRITE TABLE srcpt PARTITION(pt) SELECT id, '${bizdate}' FROM dual LIMIT 0;

报错信息:

  1. FAILED: ODPS-0130071:[1,24] Semantic analysis exception - wrong columns count 2 in data source, requires 3 columns (includes dynamic partitions if any)

旧版 MaxCompute 因为 LIMIT 0,SQL 最终没有输出任何数据,动态分区不会创建,所以最终不报错。

lot.not.in.subquery

In subquery 中 null 值的处理问题。

在标准 SQL 的 IN 运算中,如果后面的值列表中出现 null,则返回值不会出现 false,只可能是 null 或者 true。如 1 in (null, 1, 2, 3) 为 true,而 1 in (null, 2, 3) 为 null,null in (null, 1, 2, 3) 为 null。同理 not in 操作在列表中有 null 的情况下,只会返回 false 或者 null,不会出现 true。

MaxCompute2.0 会用标准的行为进行处理,收到此提醒的用户请注意检查您的查询,IN 操作中的子查询中是否会出现空值,出现空值时行为是否与您预期相符,如果不符合预期请做相应的修改。示例如下:

  1. select * from t where c not in (select accepted from c_list);

若 accepted 中不会出现 null 值,则此问题可忽略。若出现空值,则 c not in (select accepted from c_list) 原先返回 true,则新版本返回 null。

正确改法:

  1. select * from t where c not in (select accepted from c_list where accepted is not null)
本文导读目录