Hive sql去重
1、数据背景:首先,公司,2019年4月15日有335150条订单,各条订单中都有唯一的标识ID(order_id)对应一笔交易。
根据交易流程,在系统上报该交易信息时依次会有已下单、已支付、已发货、配送中、已接收、已退货、已退款等多种事务状态,而且每个事务的发生都会在Hive表中生成一条记录。因此,当天实际交易量(唯一order_id)可能只有70000单左右。
2、作业参数:在执行Hive sql作业前,需要评估原始数据量,然后配置Hive中mapper和reducer的执行参数。
set mapred.max.split.size=256000; //每个mapper处理的最大的文件大小,单位为B set mapred.min.split.size.per.node=256000; //节点中可以处理的最小的文件大小,单位为B set mapred.min.split.size.per.rack=256000; //机架中可以处理的最小的文件大小,单位为B set hive.exec.reducers.max=100; //每个任务中最大的reduce数 set hive.exec.reducers.bytes.per.reducer=102400000; //每个reducer处理的数据量,单位B
3、解决方案:
(1)distinct:distinct需要通过大量数据(如果数据集确实很大的话)之间的shuffle比较(混洗)筛选出所有唯一标识的结果集。
select distinct order_id from db.order where dt=‘20190405‘
- 执行日志:
MapReduce Jobs Launched: Stage-Stage-1: Map: 97 Reduce: 1 Cumulative CPU: 383.02 sec HDFS Read: 24407412 HDFS Write: 789899
SUCCESS
Total MapReduce CPU Time Spent: 6 minutes 23 seconds 20 msec
OK
Time taken: 93.535 seconds, Fetched: 71809 row(s)
- 逻辑分析:该Hive sql作业时启动97个mapper拉取了数据库中335150条记录,然后通过1个reducer fetch所有的记录做对比去重,耗时93.535秒。
(2)group by:该方法是通过分组方式获取唯一的组,应用在去重场景下需要将标识字段(order_id)作为分组的 key,即 group by order_id。
select order_id from db.order where dt=‘20190405‘ group by order_id;
- 执行日志:
MapReduce Jobs Launched: Stage-Stage-1: Map: 97 Reduce: 1 Cumulative CPU: 400.63 sec HDFS Read: 24410031 HDFS Write: 789899
SUCCESS
Total MapReduce CPU Time Spent: 6 minutes 40 seconds 630 msec
OK
Time taken: 85.937 seconds, Fetched: 71809 row(s)
- 逻辑分析:该Hive sql作业时启动97个mapper拉取了数据库中335150条记录,然后通过1个reducer fetch所有的记录做对比去重,耗时93.535秒。
(3)row number() over():
select order_id from ( select order_id, row_number over(patition by order_id ) as num from db.order where dt=‘20180415‘; ) order where num=1;
- 执行日志:
MapReduce Jobs Launched: Stage-Stage-1: Map: 97 Reduce: 1 Cumulative CPU: 458.32 sec HDFS Read: 24298434 HDFS Write: 789899
SUCCESS
Total MapReduce CPU Time Spent: 7 minutes 38 seconds 320 msec
OK
Time taken: 100.866 seconds, Fetched: 71809 row(s)
- 逻辑分析:该Hive sql作业时同样启动97个mapper拉取了数据库中335150条记录,然后通过1个reducer fetch所有的记录并对相同分组下的记录做编号,然后取出num=1的记录,共耗时100.866秒。
1.当数据量较小时,优先考虑使用distinct,可读性强,简洁高效;
2.当数据量较大时,推荐使用group by,性能可靠,结合mapper和reducer参数设置后性能优化更佳;
3.row_number() over()窗口函数本身是为分组内排序设计的,通过num=1限制后也可以用作数据去重。