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限制后也可以用作数据去重。

相关推荐