目录
- 发现问题
- 分析问题
- 解决问题
发现问题
今天发现一个问题，在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 3mapreduce把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值时，排在了最后面。
分析问题
问题出在哪里呢？
首先还是去官网文档里转一圈看看，果然发现了问题所在。
对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 部分
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中，得到的结果都是一致的了。
