阿里云大数据实战记录10：Hive 兼容模式的坑

已于 2023-09-16 17:57:18 修改
阅读量1.5k 收藏 4
点赞数 1
分类专栏： 阿里云大数据 文章标签： 大数据 阿里云 hive
于 2023-09-16 17:54:44 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_45476428/article/details/132921653
版权


文章目录

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

image.png

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');

结果如下：
image.png

6、该场景下，能不能不开启 Hive 兼容模式？

肯定的！查看 MaxCompute 的官方文档可以看到以下片段：

DATE_FORMAT 函数链接：https://help.aliyun.com/zh/maxcompute/user-guide/date-format

image.png

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');

返回结果如下：
image.png

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';

返回结果如下，符合预期。

image.png

【测试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';

结果如下：

image.png

什么情况？也相等？通过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));

结果如下，符合预期。

image.png

【测试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));

结果如下，符合预期。

image.png

【补充测试】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));

返回结果如下，符合预期。

image.png

以上通过间接的方式验证了在 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：如何处理生产环境表单的重复数据

Xin学数据
关注
  • 1
    点赞
  • 踩
  • 4
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
大数据:hive1
01-07
1:什么是hive:由facebook开源的用于解决海量结构化日志的数据统计 2：hive是基于hadoop的数据仓库工具，可以将结构化的数据文件映谢成一张表，并提供类SQL查询功能。 本质：将Hql转化成mpareduce  其基本执行过程：     数据仓库通过sql进行统计分析——–>将SQL语言中常用的操作（select,where,group等）用mapreduce写成很多模板 所有的mapreduce模板封装在hive中—>通过hive框架匹配出相应的mapreduce模板—->运行mapreduece生成相应的分析结果—>result->client—>用户根据业务需求编写相应的
大数据 hive 实战数据
04-07
包含video user数据
Odps执行SQL报错，提示Please set odps.sql.type.system.odps2=true to use it.
最新发布
Huathy的博客
03-06 598
在执行task前，将上面的配置信息增加进hints中。
大数据实验六实验报告：熟悉Hive的基本操作
06-20
“大数据技术原理与应用”课程实验报告 题目：实验六：熟悉Hive的基本操作 姓名：小猪猪 日期：2022/5/15 1、实验环境： 设备名称 LAPTOP-9KJS8HO6 处理器 Intel(R) Core(TM) i5-10300H CPU @ 2.50GHz 2.50 GHz 机带 RAM 16.0 GB (15.8 GB 可用) 主机操作系统 Windows 10 家庭中文版 虚拟机操作系统 ubuntukylin-16.04 Hadoop 版本 3.1.3 JDK 版本 1.8 Java IDE：Eclipse 系统类型 64 位操作系统, 基于 x64 的处理器 笔和触控 没有可用于此显示器的笔或触控输入 2、实验内容与完成情况： 1.创建一个内部表 stocks，字段分隔符为英文逗号，表结构如下所示： stocks 表结构：
【十六】hive常用内置函数之Date Functions
Sid小杰的博客
08-01 2522
Date Functions Return Type Name(Signature) Description Return Type Name(Signature) Description string from_unixtime(bigi...
Python datetime - strftime vs strptime
罗小爬的技术宝书
01-04 281
参考： pythone3/library/datetime.html#datetime.timezone strftime() and strptime() Behavior date, datetime, and time objects all support a strftime(format) method, to create a string representing the time under the control of an explicit for
date_format()的使用
zj23qq的专栏
09-24 1135
String sql = " select * from tjyx.member a where a.DELETED = 0 and DATE_FORMAT(a.BIRTH_DATE, '%m%d')>=DATE_FORMAT('"; sql = sql + start + "', '%m%d') and DATE_FORMAT(a.BIRTH_DATE,'%m%d')<= DATE_FORMA
普通和hive兼容模式下sql的差异
m0_46914845的博客
03-15 972
当输入参数是BIGINT类型的时候，如果BITAND的计算结果是LONG_MIN(-263)，在普通模式下会返回NULL，– 当输入参数是BIGINT类型的时候，如果BITXOR的计算结果是LONG_MIN(-263)，在普通模式下会返回NULL，– 普通模式FIND_IN_SET函数返回值类型为BIGINT，Hive兼容模式FIND_IN_SET函数返回值类型为INT。– 当输入参数是BIGINT类型的时候，如果BITOR的计算结果是LONG_MIN(-263)，在普通模式下会返回NULL，
ODPS double类型转型精度问题总结
云栖技术精选
03-16 2506
简介：ODPS double类型转型精度问题总结 从相差0.0000000000001说起，本文主要是对odps的Double和Decimal的精度使用问题做一个总结。 1. 问题描述 客户开发人员在使用maxcompute对double数据类型求和时出现错误（数据表由oracle数据库抽取到maxcompute, 对应字段类型为number到double），正确的结果是1943.38，但求和结果为1943.3799999999999，结果相差了0.0000000000001，这个...
odps 和 hive兼容 set 语句
feihangyu的博客
10-09 197
关闭严格模式：set odps.sql.udf.strict.mode=false;开启hive兼容：set odps.sql.hive.compatible=true。
大数据-玩转数据-MaxCompute SQL中的日期函数
s_unbo的博客
07-15 2268
假设start=2018-06-04193323.234，end=2018-06-04193323.250，含毫秒的日期不属于标准DATETIME式样，不能直接隐式转换，此处需进行显示转换。假设当前时间为2020-11-17163144，返回2020-11-16163144。将STRING类型常量显式转换为DATETIME类型，返回2005-02-28000000。假设当前时间为2020-11-17163144，返回2020-11-16。...
大数据:hive4:查询
01-07
创建部门表 create table if not exists dept( deptno int, dname string, loc int ) row format delimited fields terminated by '\t'; 创建员工表 create table if not exists emp( empno int, ...
大数据面试二：hive
05-24
Hive面试题汇总，包括了常见高频率的面试问题和答案，例如内外部表、索引、存储、数据倾斜、仓库分层等等
frida的用法--Hook Java代码篇
孩子眼里的钢铁侠&每天进步一点点
11-25 6503
frida是一款方便并且易用的跨平台Hook工具，使用它不仅可以Hook Java写的应用程序，而且还可以Hook原生的应用程序。 1. 准备 frida分客户端环境和服务端环境。在客户端我们可以编写Python代码，用于连接远程设备，提交要注入的代码到远程，接受服务端的发来的消息等。在服务端，我们需要用Javascript代码注入到目标进程，操作内存数据，给客户端发送消息等操作。我们也可以把客户端理解成控制端，服务端理解成被控端。 假如我们要用PC来对Android设备上的某个进程进行操作，那么PC就
Delphi/C++Builder在没有做任何代码修改，重新编译后CRC不一致的解决方案。
无常之语,无心之果(by 僵哥)
06-16 2180
Delphi/C++Builder在没有做任何代码修改，只要重新编译后，再计算CRC，就会与原来不一致的。经同事的朋友查得是时间戳在做怪。解决方案见贴子：http://topic.csdn.net/u/20080613/11/9fa12d6b-473d-4ac9-a091-0b02f4ffcac6.html下面只贴出代码：function FileDateToDateTime_UT
通过学习官方给定JSTL函数来自定义JSTL函数格式化JDK8日期时间
life is wonderful
08-23 1071
需求描述及提出问题 最近几天在写项目后端定义的实体类中的日期时间类型由java.util.Date改变为 JDK8提供的java.time.LocalDateTime或者java.time.LocalDate，那么随着变化的还有前端JSP页面怎么格式化日期时间？使用java.util.Date时，前端JSP页面格式化时间是由官方JSTL提供的L格式化标签，只要在JSP页面引入以下链接： <%@taglib prefix="fmt" uri="http://java.sun.com/jsp/js
第六章：数据加载、存储于文件格式Day12-14
图图酱的专栏
07-31 3024
说明：本文章为Python数据处理学习日志，记录内容为实现书本内容时遇到的错误以及一些与书本不一致的地方，一些简单操作则不再赘述。日志主要内容来自书本《利用Python进行数据分析》，Wes McKinney著，机械工业出版社。
dataWorks常用调优参数
ShiHao_Li的博客
10-09 2249
总结了一下dataWorks常用参数方便同学们使用： ## 查看项目数据类型版本。 setproject; ## 允许全表扫描 set odps.sql.allow.fullscan=true; ##允许整个项目都可全表扫描 setproject odps.sql.allow.fullscan=true; ##文件被合并的最大阈值 set odps.sql.mapper.merge.limit.size=64; ##Map端输入的控制 set odps.sql.mapper.split.size
MaxCompute问答整理之7月
zhaowei121的博客
08-07 645
本文是基于本人对MaxCompute产品的学习进度，再结合开发者社区里面的一些问题，进而整理成文。希望对大家有所帮助。 问题一、DataWorks V2.0简单模式和标准模式的区别？ 公司数仓的数据上云后，在使用MaxCompute计算引擎时，需要一个稳定、可靠的调度系统，将自身数据生产任务（代码）按照所需依赖关系、运行时间来调度运行，那么DataWorks就派上了用场。DataWorks提供简...
大数据：hadoop基础常识hive，hbase，mapreduce，spark
06-06
### 回答1： 大数据是指数据量非常大、处理难度较高的数据集合。Hadoop是一种分布式计算框架，包括Hive、HBase、MapReduce和Spark等基础组件。Hive是一种基于Hadoop的数据仓库工具，可以将结构化数据映射到Hadoop上进行查询和分析。HBase是一种分布式的NoSQL数据库，适用于海量数据的存储和实时读写。MapReduce是一种分布式计算模型，可以将大数据分成小块进行并行处理。Spark是一种快速、通用、可扩展的大数据处理引擎，支持多种编程语言和数据源。这些基础组件共同构成了Hadoop生态系统，为大数据处理提供了强大的支持。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Xin学数据

为你点亮一盏灯，愿你前进无阻。

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值