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

---

发现问题

今天发现一个问题，在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> 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_001 | 2020-10-29 07:00:00 | 2020-10-29 07:20:00 |    1 
 root_001 | user_002 | 2020-10-29 07:00:00 | 2020-10-29 07:30:00 |    2 
 root_001 | user_003 | 2020-10-29 07:00:00 | NULL                |    3 

可以看到，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官网文档

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

-- presto 中desc倒序排序
presto> select room_id
     ->         ,user_id
     ->         ,in_time
     ->         ,out_time
     ->         ,row_number() over(partition by room_id order by out_time desc) as r_id
     -> from demo_over_order;

 room_id  | user_id  |       in_time       |      out_time       | r_id 
----------+----------+---------------------+---------------------+------
 root_001 | user_001 | 2020-10-29 07:00:00 | 2020-10-29 07:20:00 |    1 
 root_001 | user_002 | 2020-10-29 07:00:00 | 2020-10-29 07:30:00 |    2 
 root_001 | user_003 | 2020-10-29 07:00:00 | NULL                |    3 

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

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

hive官方文档

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中，得到的结果都是一致的了。