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

2023-09-16 17:54:44


文章目录

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：如何处理生产环境表单的重复数据

更多推荐

外包“混”了2年，我只认真做了5件事，如今顺利拿到字节 Offer...

前言是的，我一家外包公司工作了整整两年时间，在入职这家公司前，也就是两年前，我就开始规划了我自己的人生，所以在两年时间里，我并未懈怠。现如今，我已经跳槽到了字节，顺利拿下offer。自己的情况很普通，本科文凭，没有背景，分享这次我的经历，想鼓励和我同样起点的人字节面试题（技术部分）1.linux基本语句2.http/h

Unity SteamVR 开发教程：SteamVR Input 输入系统（2.x 以上版本）

文章目录📕前言📕教程说明📕导入SteamVR插件📕SteamVRInput窗口⭐action.json文件⭐窗口面板⭐SteamVR_Input目录📕SteamVR动作的类型⭐Boolean⭐Single⭐Vector2⭐Vector3⭐Pose⭐Skeleton⭐Vibration📕动作和按键绑定窗口Bi

使用 YCSB 和 PE 进行 HBase 性能压力测试

HBase主要性能压力测试有两个，一个是HBase自带的PE，另一个是YCSB，先简单说一个两者的区别。PE是HBase自带的工具，开箱即用，使用起来非常简单，但是PE只能按单个线程统计压测结果，不能汇总整体压测数据，更重要的是，PE没有YCSB的预设模板（Workload）功能，测试场景单一，相较而言，YCSB要强大

51单片机项目（12）——基于51单片机的智能台灯设计

本次设计的功能如下：首先使用PCF8591芯片，实现了ADDA转换，AD采集的是光敏电阻的信息，光照强度越强，电压越小，AD采集到的数值越小。同时将AD采集的数字量作为DA输出时的输入量，模拟输出端接了一个LED，用以指示输出模拟量的大小，输出模拟量越大，LED就越亮。所以这一部分的工作过程如下：当光照强度太弱时，AD

【C++】构造函数初始化列表 ② ( 构造函数 为 初始化列表 传递参数 | 类嵌套情况下 的 构造函数 / 析构函数 执行顺序 )

文章目录一、构造函数为初始化列表传递参数1、构造函数参数传递2、代码示例-构造函数参数传递二、类嵌套情况下的构造函数/析构函数执行顺序1、构造函数/析构函数执行顺序2、代码示例-构造函数执行顺序一、构造函数为初始化列表传递参数1、构造函数参数传递构造函数初始化列表还可以使用构造函数中的参数;借助构造函数中的参数列表,可

C++零基础教程（C++中的类1）

文章目录前言一、初始化列表二、类中的const成员三、析构函数四、临时对象总结前言本篇文章我们继续来讲解C++中的类。一、初始化列表初始化列表是在C++类的构造函数中使用的一种特殊语法。它允许在对象创建时对成员变量进行初始化。通常，在构造函数的函数体中，我们会使用赋值操作符（=）来对成员变量进行初始化。然而，初始化列表

计算机竞赛 机器视觉的试卷批改系统 - opencv python 视觉识别

文章目录0简介1项目背景2项目目的3系统设计3.1目标对象3.2系统架构3.3软件设计方案4图像预处理4.1灰度二值化4.2形态学处理4.3算式提取4.4倾斜校正4.5字符分割5字符识别5.1支持向量机原理5.2基于SVM的字符识别5.3SVM算法实现6算法测试7系统实现8最后0简介🔥优质竞赛项目系列，今天要分享的是

SpringBoot + Prometheus + Grafana 打造可视化监控

SpringBoot+Prometheus+Grafana打造可视化监控文章目录SpringBoot+Prometheus+Grafana打造可视化监控常见的监控组件搭配安装Prometheus安装Grafana搭建SpringBoot项目引入依赖示例:监控SpringBoot内置Tomcat线程池的情况grafana

从实时监控到智能洞察：Grafana 和 CnosDB 的无限潜力

在今天的数字化世界中，监控系统对于维护应用程序和基础设施的稳定性至关重要。本文将介绍如何使用Grafana和CnosDB构建强大的监控体系，以便实时监视性能、发现问题并采取及时的措施。CnosDB已正式上架Grafana插件市场Grafana：开源监控和仪表工具Grafana是一款流行的开源监控和仪表工具，它提供了丰富

传统制造型企业如何实现数字化转型？

传统制造企业可以通过采用技术和数据驱动的方法来实现数字化转型，以改善运营、提高效率并在快速发展的行业中保持竞争力。以下是他们可以遵循的步骤和策略：1.评估和战略制定：评估当前状态：首先评估制造流程、技术基础设施和数据管理系统的当前状态。确定需要改进的领域。定义目标：明确定义数字化转型的目的和目标。你想实现什么目标？它可

7年阿里测试经验之谈 —— 用UI自动化测试实现元素定位

随着IT行业的发展，产品愈渐复杂，web端业务及流程更加繁琐，目前UI测试仅是针对单一页面，操作量大。为了满足多页面功能及流程的需求及节省工时，设计了这款UI自动化测试程序。旨在提供接口，集成到蜗牛自动化测试框架，方便用例的设计。目前，在自动化测试的实际应用中，接口自动化测试被广泛使用，但UI自动化测试也并不会被替代。

热文推荐
  1. pdf转换成word，这里有几个不错的方法

      pdf转换成word怎么转？大家都知道，在电脑进行各种文件格式转换中，PDF转换为Word文档的需求量应该是最大的。在我们的日常工作中，经常需要将PDF转换为Word格式。为什么要将pdf文件转换成word，相信大家也都应该知道的，因为pdf文件不易编辑的特点，导致大家相对pdf文件内容进行修改

  2. 计算机竞赛 深度学习 python opencv 实现人脸年龄性别识别

    文章目录0前言1项目课题介绍2关键技术2.1卷积神经网络2.2卷积层2.3池化层2.4激活函数：2.5全连接层3使用tensorflow中keras模块实现卷积神经网络4Keras介绍4.1Keras深度学习模型4.2Keras中重要的预定义对象4.3Keras的网络层构造5数据集处理训练5.1分为年龄、性别5.2性别

  3. 【React】React入门

    目录一、何为React二、React与传统MVC的关系三、React的特性1、声明式编程①、实现标记地图2、高效灵活3、组件式开发(Component)①、函数式组件②、类组件（有状态组件）③、一个组件该有的特点4、单向式响应的数据流四、虚拟DOM1、传统DOM更新①、举例讲解2、虚拟DOM①、举例讲解五、创建Reac

  4. 2022年统计用区划代码表SQL 第二部分02

    行政区划代码为国家公布的六位县级以上行政区划代码行政区编码的用途：APP里做城市级联选择根据身份证前六位获取用户所在城市区县370786昌邑市370800济宁市370811任城区370812兖州区百度高德等接口通常都会返回adcode字段(行政区编码)根据行政区编码可以查询天气数据例如请求天气API接口,传参adcod

  5. java面试题

    java面试题java基础面试题1.hashcode和equals如何使用2.==和equals的区别3.重写和重载的区别4.代理的几种实现方式5.String、StringBuffer、StringBuilder区别及使用场景6.怎样声明一个类不会被继承，什么场景下会用7.自定义异常在生产中如何应用8.java面向对

  6. GaussDB（DWS）云原生数仓技术解析：湖仓一体，体验与大数据互联互通

    文章目录前言一、关于数据仓库需求场景分类二、数据仓库线下部署场景2.1、线下部署场景介绍及优劣势说明2.2、线下部署场景对应的客户需求三、数据仓库公有云部署场景3.1、公有云部署场景介绍及优劣势说明3.2、公有云部署场景对应的客户需求四、为何重视数据共享（含湖仓一体）？4.1、传统数据共享业务场景4.2、数据共享（含湖

  7. Flutter插件的制作和发布

    Flutter制作插件有两种方式（以下以android和ios为例）：目录1.直接在主工程下的android和ios项目内写插件代码：2.创建独立FlutterPlugin项目，制作各端插件后，再引入项目：1.创建FlutterPlugin：2.FlutterPlugin创建完成：3.使用androidstudio打开

  8. Mybatis框架学习

    什么是mybatis？mybatis是一款用于持久层的、轻量级的半自动化ORM框架，封装了所有jdbc操作以及设置查询参数和获取结果集的操作，支持自定义sql、存储过程和高级映射mybatis用来干什么？用于处理java和数据库的交互使用mybatis的好处与JDBC相比，减少了50%以上的代码量。MyBatis灵活，

  9. 从零开始的PICO开发教程（4）-- VR世界 射线传送、旋转和移动

    从零开始的PICO开发教程（4）--VR世界射线传送、旋转和移动文章目录从零开始的PICO开发教程（4）--VR世界射线传送、旋转和移动一、前言1、大纲二、VR射线移动功能实现与解析1、区域传送（1）新建XROrigin（2）新建一个用于传送的地面（3）将XROrigin设置为可传送的Provider并赋值给可传送地面