hive窗口函数order by不同引擎对于null的排序规则

浅浅的花香味﹌ 2021-09-25 09:48 385阅读 0赞

### 目录 ###

*  发现问题
 *  分析问题
 *  解决问题

--------------------

# 发现问题 #

今天发现一个问题，在Hive中使用窗口函数对数据进行分组排序时，使用mr引擎和使用presto引擎得到的结果不一致，在这里把发现和解决问题的过程记录一下。

**1. 首先模拟数据：**  
创建表

create table demo_over_order
    (
        room_id  string comment '直播间ID'
       ,user_id  string comment '用户ID'
       ,in_time  string comment '进入时间'
       ,out_time string comment '退出时间'
    )
    comment "直播间进入退出记录" 
    stored as orc;

插入测试数据:

insert overwrite table demo_over_order
    select 'root_001','user_001','2020-10-29 07:00:00','2020-10-29 07:20:00'
    union all
    select 'root_001','user_002','2020-10-29 07:00:00','2020-10-29 07:30:00'
    union all
    select 'root_001','user_003','2020-10-29 07:00:00',null
    ;

查看数据:

hive> select * from demo_over_order as o;
    OK
    o.room_id	o.user_id	o.in_time	o.out_time
    root_001	user_001	2020-10-29 07:00:00	2020-10-29 07:20:00
    root_001	user_002	2020-10-29 07:00:00	2020-10-29 07:30:00
    root_001	user_003	2020-10-29 07:00:00	NULL

其中，user\_001、user\_002记录的退出时间字段都值，而user\_003退出时间字段为null。

**2. 重现问题**  
对于以上模拟的数据，我们需要对直播间id（room\_id）进行分组后，根据退出时间(out\_time)进行排序，取得row\_number。

下面分别使用 mapreduce、presto、tez、sparksql分别进行测试，发现他们在order by out\_time时，对于null的排序规则是不一样的。

使用mr查询:

hive> set hive.execution.engine=mr;
        > set hive.cli.print.header=true;
        > select room_id
        >         ,user_id
        >         ,in_time
        >         ,out_time
        >         ,row_number() over(partition by room_id order by out_time) as r_id
        > from demo_over_order;
    
    room_id	    user_id	    in_time	            out_time	        r_id
    root_001	user_003	2020-10-29 07:00:00	NULL	            1
    root_001	user_001	2020-10-29 07:00:00	2020-10-29 07:20:00	2
    root_001	user_002	2020-10-29 07:00:00	2020-10-29 07:30:00	3

mapreduce把Null值排在了最前面。

使用presto查询:

可以看到，presto中将null值排在了最后面，他和mapreduce的处理逻辑不一样。  
那接下来顺便看看tez和sparksql这两种计算引擎中，又是什么样子的呢。

使用tez查询:

hive> set hive.execution.engine=tez;
        > set hive.cli.print.header=true;
        > select room_id
        >         ,user_id
        >         ,in_time
        >         ,out_time
        >         ,row_number() over(partition by room_id order by out_time) as r_id
        > from demo_over_order;
    room_id	    user_id	    in_time	            out_time        	r_id
    root_001	user_003	2020-10-29 07:00:00	NULL	            1
    root_001	user_001	2020-10-29 07:00:00	2020-10-29 07:20:00	2
    root_001	user_002	2020-10-29 07:00:00	2020-10-29 07:30:00	3

可以看到tez和mapreduce是一样的，将null排在了最前面。

使用sparksql查询:

spark-sql> select room_id
             >         ,user_id
             >         ,in_time
             >         ,out_time
             >         ,row_number() over(partition by room_id order by out_time) as r_id
             > from demo_over_order; 
     
    root_001	user_003	2020-10-29 07:00:00	NULL	            1
    root_001	user_001	2020-10-29 07:00:00	2020-10-29 07:20:00	2
    root_001	user_002	2020-10-29 07:00:00	2020-10-29 07:30:00	3

看样子只有presto中的窗口函数里，order by遇到null值时，排在了最后面。

# 分析问题 #

问题出在哪里呢？

首先还是去官网文档里转一圈看看，果然发现了问题所在。

[prestor官网文档][prestor]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x6NjM2Mw_size_16_color_FFFFFF_t_70]  
对order by的描述中说明了，默认情况下，不管order by的方向不管是要正序排序还是倒序排序，null值都会排在最后面，下面反手就来一个倒序排序的测试一下：

看结果，果然null值还是排在最后。

下面去hive官网文档看下order by 部分

[hive官方文档][hive]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x6NjM2Mw_size_16_color_FFFFFF_t_70 1]  
hive2.10之前：

虽然文档没有明说，但是看样子在hive2.10之前，当asc时，null值都排序最前面，当desc时，null值都排在最后。

hive2.10和之后  
默认还是当asc时，null值都排序最前面，当desc时，null值都排在最后，但是可以设置了对null的排序规则。

总结一下原因就是：

*  presto不管是正序还是倒序，都把null排在最后
 *  hive中默认情况下，认为null值最小。正序排序就排在前面，倒序就排序后面

# 解决问题 #

问题原因找到了，怎么解决窗口函数中的null排序问题呢。以下提供一种思路：order by 的时候，先对排序字段进行处理，先将null值转换为空格。

select room_id
            ,user_id
            ,in_time
            ,out_time
            ,row_number() over(partition by room_id order by coalesce(out_time,'')) as r_id
    from demo_over_order;

这样，不管在presto中还是在hive中，得到的结果都是一致的了。

[prestor]: https://prestodb.io/docs/current/sql/select.html#order-by-clause
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x6NjM2Mw_size_16_color_FFFFFF_t_70]: /images/20210923/2c610edee96e4449b885d19adda43688.png
[hive]: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy#LanguageManualSortBy-SyntaxofOrderBy
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x6NjM2Mw_size_16_color_FFFFFF_t_70 1]: /images/20210923/76152a5319ed40638688ef56e0256121.png