文章目录
- 1、前言
- 2、什么是 Hive 兼容模式?
- 3、为什么要开启 Hive 模式?
- 4、有什么副作用?
- 5、如何开启 Hive 兼容模式?
- 6、该场景下,能不能不开启 Hive 兼容模式?
- 7、为什么不是`DATE_FORMAT(datetime, string)`?
- 8、小结
1、前言
今天在开发一个表单的时候,MaxCompute 抛给我一个错误:
SQL Runtime Unretryable Error: ODPS-0121125:[xx,xx] Unsupported operation - function signature DATE_FORMAT(string, string) is not supported in current mode, please set odps.sql.hive.compatible=true to use it
什么意思呢?就是告诉我 MaxCompute 不支持这个语法DATE_FORMAT(string, string)
,但是如果我还是想用的话可以加上一个配置:set odps.sql.hive.compatible=true
,这样就可以使用以上语法。
这个报错原本的 SQL 可以抽象为:
SELECT DATE_FORMAT(FROM_UNIXTIME(1672538400),'yyyyMMdd')
也就是FROM_UNIXTIME(1672538400)
返回结果被当作 STRING 数据类型处理了。而DATE_FORMAT(string, string)
需要加上set odps.sql.hive.compatible=true
才能正常工作。
2、什么是 Hive 兼容模式?
那么这个配置(set odps.sql.hive.compatible=true
)是什么意思呢?
这个配置就是开启 Hive 兼容模式,使得在 MaxCompute SQL 中可以使用 Hive SQL 的函数语法。
3、为什么要开启 Hive 模式?
因为有一些函数的用法 MaxCompute 不支持,或者有差异,要开启 Hive 模式才能使用。
比如说上面报错:DATE_FORMAT()
函数如果传入的参数是 STRING 类型,则需要开启 Hive 兼容模式才可以使用。否则就报错。
如上,MaxCompute 中,使用DATE_FORMAT()
函数时,传入的参数不支持 STRING 类型,DATE_FORMAT(string,string)
在 Hive SQL 才被支持,所以需要通过开启 Hive 兼容模式来使用它。
4、有什么副作用?
当然!肯定会有副作用,因为 Hive 模式和非 Hive 模式的一些函数的返回值是不同的。
比如说FROM_UNIXTIME()
函数,它在 Hive 兼容模式下的返回值为 STRING 类型,而在 ODPS 1.0 和 ODPS 2.0 数据类型版本的返回值为 DATETIME 类型。
这会有什么影响呢?如果你使用过 MaxCompute 进行数据开发,一定不会陌生:MaxCompute 在数据类型一致性这方面要求会比较苛刻,绝大多数场景下数据类型不一致会无法判断。
这时,要么抛出错误,要么直接返回空值,前者还容易处理,进行显性调整数据类型即可,后者就有点摸不着头脑,需要逐步排查。
所以,返回数据类型改变了,最大的影响就是取不到数据,也正因为这个原因,开发的表单一定要进行数据校验,避免翻车。
说个题外话,数据类型一致性这点在 MySQL 上几乎是看不见的,因为 MySQL 会帮我们进行隐式转换,不需要我们另外处理,所以在 MySQL 上,基本不需要太过担心数据类型的问题,正因为这点它特别适合小白入门~~
更多的“副作用”可参考下图:
参考链接:https://help.aliyun.com/zh/maxcompute/user-guide/hive-compatible-data-type-edition
5、如何开启 Hive 兼容模式?
了解了 Hive 的正面作用和副作用之后,还是决定使用,就可以打开 Hive 开关尽情“享用”啦!
设置开关语法:
set odps.sql.hive.compatible=true; -- 打开Hive兼容模式。
使用方法,放在 SQL前面,和 SQL 一起执行,每一个设置命令作为一个独立的语句,用;
结尾:
set odps.sql.hive.compatible=true; --打开Hive兼容模式
SELECT xxx FROM xxx;
为了保证无差异,把 ODPS 2.0 的设置一同加上。
set odps.sql.type.system.odps2=true; --打开MaxCompute 2.0数据类型。
set odps.sql.decimal.odps2=true; --打开Decimal 2.0数据类型。
set odps.sql.hive.compatible=true; --打开Hive兼容模式。
SELECT xxx FROM xxx;
所以,开启 Hive 兼容模式最终得到的解决方案如下:
set odps.sql.type.system.odps2=true; --打开MaxCompute 2.0数据类型。
set odps.sql.decimal.odps2=true; --打开Decimal 2.0数据类型。
set odps.sql.hive.compatible=true; --打开Hive兼容模式。
SELECT FROM_UNIXTIME(1672538400),DATE_FORMAT(FROM_UNIXTIME(1672538400),'yyyyMMdd');
结果如下:
6、该场景下,能不能不开启 Hive 兼容模式?
肯定的!查看 MaxCompute 的官方文档可以看到以下片段:
DATE_FORMAT 函数链接:https://help.aliyun.com/zh/maxcompute/user-guide/date-format
MaxCompute 的DATE_FORMAT()
函数总共支持4个类型的参数:DATE、DATETIME、TIMESTAMP 和 STRING。其中三个:DATE、DATETIME 和 STRING类型只能在 Hive 兼容模式下使用,还有一个:TIMESTAMP 可以在非 Hive 兼容模式下使用。
那么使用CAST(FROM_UNIXTIME(1672538400)AS TIMESTAMP)
将数据类型转化为 TIMESTAMP 便可!
set odps.sql.type.system.odps2=true; --打开MaxCompute 2.0数据类型。
set odps.sql.decimal.odps2=true; --打开Decimal 2.0数据类型。
set odps.sql.hive.compatible=false; --关闭Hive兼容模式。
select FROM_UNIXTIME(1672538400),DATE_FORMAT(CAST(FROM_UNIXTIME(1672538400) AS TIMESTAMP),'yyyyMMdd');
返回结果如下:
7、为什么不是DATE_FORMAT(datetime, string)
?
在上面的描述过程中,不知道你是否留意到,其实还有一个问题没有解决?
FROM_UNIXTIME()
函数,它在 Hive 兼容模式下的返回值为 STRING 类型,而在 ODPS 1.0 和 ODPS 2.0 数据类型版本的返回值为 DATETIME 类型。但是我在没有开启 Hive 兼容模式下,返回的报错却是 MaxCompute 不支持这个语法DATE_FORMAT(string, string)
,而不是DATE_FORMAT(datetime, string)
。
那么实际上非 Hive 兼容模式是返回 STRING 还是 DATETIME 呢?
为了回答这个疑问,我做了一个验证:测试验证 FROM_UNIXTIME()
在不同的模式下返回的数据类型。
说明:
- 时间戳
1672538400
转换为时间格式是2023-01-01 10:00:00
DATE()
是 ODPS 2.0 才有的语法,需要打开 ODPS 2.0 开关,有的项目直接设置打开 ODPS2,但为了保险起见,这里再设置一下,你可以查看项目的相关配置是否有开启,如果开启则不需要再设置。- 查文档可知:
FROM_UNIXTIME()
在 Hive 兼容模式下返回 STRING,在非 Hive 兼容模式下返回 DATETIME;
【测试1】开启 Hive 兼容模式,FROM_UNIXTIME(1672538400)
是否返回 STRING 类型?
set odps.sql.type.system.odps2=true; --打开MaxCompute 2.0数据类型。
set odps.sql.decimal.odps2=true; --打开Decimal 2.0数据类型。
set odps.sql.hive.compatible=true; --打开Hive兼容模式。
SELECT FROM_UNIXTIME(1672538400),FROM_UNIXTIME(1672538400)='2023-01-01 10:00:00';
返回结果如下,符合预期。
【测试2】关闭 Hive 兼容模式,FROM_UNIXTIME(1672538400)
是否返回 DATETIME 类型?
set odps.sql.type.system.odps2=true; --打开MaxCompute 2.0数据类型。
set odps.sql.decimal.odps2=true; --打开Decimal 2.0数据类型。
set odps.sql.hive.compatible=false; --关闭Hive兼容模式。
SELECT FROM_UNIXTIME(1672538400),FROM_UNIXTIME(1672538400)='2023-01-01 10:00:00';
结果如下:
什么情况?也相等?通过FROM_UNIXTIME(1672538400)='2023-01-01 10:00:00'
判断进行了隐式转换?既然不管等号右边是 STRING 还是 DATATIME,都返回 true。改个方式,使用DATE()
辅助判断。
说明:DATE()
函数传入的值是时间格式'yyyy-MM-dd'
的字符串或者 DATETIME 类型才会返回日期值,如果是时间格式'yyyy-MM-dd hh:mi:ss'
的字符串,返回空值。
【测试3】改用DATE()
函数辅助判断:开启 Hive 兼容模式,DATE(FROM_UNIXTIME(1672538400))
是否返回空值?
set odps.sql.type.system.odps2=true; --打开MaxCompute 2.0数据类型。
set odps.sql.decimal.odps2=true; --打开Decimal 2.0数据类型。
set odps.sql.hive.compatible=true; --关闭Hive兼容模式。
SELECT FROM_UNIXTIME(1672538400),DATE(FROM_UNIXTIME(1672538400));
结果如下,符合预期。
【测试4】改用DATE()
函数辅助判断:关闭 Hive 兼容模式,DATE(FROM_UNIXTIME(1672538400))
是否返回日期?
set odps.sql.type.system.odps2=true; --打开MaxCompute 2.0数据类型。
set odps.sql.decimal.odps2=true; --打开Decimal 2.0数据类型。
set odps.sql.hive.compatible=false; --关闭Hive兼容模式。
SELECT FROM_UNIXTIME(1672538400),DATE(FROM_UNIXTIME(1672538400));
结果如下,符合预期。
【补充测试】DATE('2023-01-01 10:00:00')
和DATE(CAST('2023-01-01 10:00:00' as datetime))
返回的结果是否是空值和时间字段?
set odps.sql.type.system.odps2=true; --打开MaxCompute 2.0数据类型。
SELECT DATE('2023-01-01 10:00:00'),DATE(CAST('2023-01-01 10:00:00' AS DATETIME));
返回结果如下,符合预期。
以上通过间接的方式验证了在 MaxCompute 中,FROM_UNIXTIME()
在 Hive 兼容模式下返回 STRING,在非 Hive 兼容模式下返回 DATETIME。
非 Hive 兼容模式下,FROM_UNIXTIME(1672538400)
确实是返回的是 DATETIME 类型,那么就是 DATETIME 类型传入DATE_FORMAT()
时,会被转换为 STRING 类型进行处理。
8、小结
解决 MaxCompute 不支持这个语法DATE_FORMAT(string, string)
的方法本文提供了两种:
- 方法1:开启 Hive 兼容模式
set odps.sql.type.system.odps2=true; --打开MaxCompute 2.0数据类型。
set odps.sql.decimal.odps2=true; --打开Decimal 2.0数据类型。
set odps.sql.hive.compatible=true; --打开Hive兼容模式。
SELECT FROM_UNIXTIME(1672538400),DATE_FORMAT(FROM_UNIXTIME(1672538400),'yyyyMMdd');
- 方法2:显性修改传入
FROM_UNIXTIME(1672538400)
返回的数据类型
set odps.sql.type.system.odps2=true; --打开MaxCompute 2.0数据类型。
set odps.sql.decimal.odps2=true; --打开Decimal 2.0数据类型。
set odps.sql.hive.compatible=false; --关闭Hive兼容模式。
select FROM_UNIXTIME(1672538400),DATE_FORMAT(CAST(FROM_UNIXTIME(1672538400) AS TIMESTAMP),'yyyyMMdd');
另外,传递给DATE_FORMAT()
的参数如果是 DATETIME 类型,会被隐性转换为 STRING 处理。
往期回顾:
阿里云大数据实战记录9:MaxCompute RAM 用户与授权
阿里云大数据实战记录8:拆开 json 的每一个元素,一行一个
阿里云大数据实战记录7:如何处理生产环境表单的重复数据