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

guduadmin118小时前

文章目录

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