MaxCompute最佳实践:修改不兼容SQL实战-问答-阿里云开发者社区-阿里云

开发者社区> 问答> 正文

MaxCompute最佳实践:修改不兼容SQL实战

行者武松 2017-10-24 14:40:33 6146


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. separator:String类型常量,分隔符。其他类型或非常量将引发异常。

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。如 1in (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)
SQL 移动开发 分布式计算 资源调度 编译器 MaxCompute HIVE
分享到
取消 提交回答
全部回答(0)
大数据
使用钉钉扫一扫加入圈子
+ 订阅

大数据计算实践乐园,近距离学习前沿技术

推荐文章
相似问题