【Hive】count(distinct column) 为何效率低？

旧城等待， 2022-09-11 15:12 166阅读 0赞

### count（distinct column）为何效率低？ ###

*  问题？
 *  解决办法
 *  count(distinct colume) 的原理

# 问题？ #

> 统计一个按天分区，每天都有百亿条数据条的hive表中account字段的非重用户数（大概两千万）。后来又更改为按id字段分别统计每个id的用户数。

很简单，直接`count(distinct account)`这个句子。然后写上了一行查询完成！然后等待了四个小时，map反着跑就知道肯定有问题。。  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5LiA5Liq5YaZ5rm_55qE56iL5bqP54y_size_20_color_FFFFFF_t_70_g_se_x_16]  
Hive SQL 基于的mapreduce是并行计算，百亿的数据可不是平时测试时的mysql里的几百条数据。

这么想来应该是map和reduce的内存不够，

set mapreduce.map.memory.mb=48192;
    set mapreduce.reduce.memory.mb=48192;

执行语句

select count（distinct account） from...where...

继续mapreduce，三个小时后报错`error in shuffle in fetcher#3. shuffle`过程又出问题了。  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5LiA5Liq5YaZ5rm_55qE56iL5bqP54y_size_20_color_FFFFFF_t_70_g_se_x_16 1]  
reducer只有1？ 那还怎么并行？果断

set mapred.reduce.tasks=1000；

又进行查询，发现`reducer`还是1，为什么？

*  原来因为加入`distinct`，map阶段不能用combine消重，数据输出为（key，value）形式然后在reduce阶段进行消重。

重点是，Hive在处理COUNT这种“`全聚合(full aggregates)`”计算时，它会忽略用户指定的Reduce Task数，而强制使用1。  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5LiA5Liq5YaZ5rm_55qE56iL5bqP54y_size_20_color_FFFFFF_t_70_g_se_x_16 2]

# 解决办法 #

转换为子查询，转化为`两个mapreduce任务`，先select distinct的字段，然后在count()，这样去重就会分发到不同的reduce块，count依旧是一个reduce但是只需要计数即可。

select count（*） from （select distinct account form tablename where...）as t;

这样大概半小时可以得到结果。

> 后来需求改变为对这个表按account的类型（字段名为id）统计每个类型的account非重复数。

如果按照上述方法，在查询条件添加 `where id=..`，这样每个查询都需要半小时，效率很低。

优化方法：利用`gourp by 按 id`，account分组，存入一个临时表，只需要对临时表进行统计即可

insert overwrite table temp select id，account，count(1) as num from tablename group by id，account；

这样temp表里的数据直接就是非重数据，并且按 id升序排序，按 id筛选 count(\*)即可。 sum(num)也可统计总数。

# count(distinct colume) 的原理 #

SELECT COUNT( DISTINCT id ) FROM TABLE_NAME WHERE ...;

count（distinct id）从执行计划上面来看：

*  只有一个reducer任务（即使你设置reducer任务为100个，实际上也没有用），所有的id都会聚集到同一个reducer任务进行去重然后在聚合，这非常容易造成数据倾斜.

该怎么解决这个问题呢？ 实际上解决方法非常巧妙：

我们利用Hive对嵌套语句的支持，将原来一个MapReduce作业转换为两个作业，在第一阶段选出全部的非重复id，在第二阶段再对这些已消重的id进行计数。

这样在第一阶段我们可以通过增大Reduce的并发数，并发处理Map输出。

在第二阶段，由于id已经消重，因此`COUNT(*)`操作在Map阶段不需要输出原id数据，只输出一个合并后的计数即可。这样即使第二阶段Hive强制指定一个Reduce Task，极少量的Map输出数据也不会使单一的Reduce Task成为瓶颈。改进后的SQL语句如下：

SELECT COUNT(*) FROM (SELECT DISTINCT id FROM TABLE_NAME WHERE … ) as t;

这个解决方案有点类似于`set hive.groupby.skew.indata`参数的作用！  
实际测试：

select count(distinct dept_num)
    from emp_ct
     
       
    select count(*)
    from (
        select distinct dept_num
        from emp_ct
    )

[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5LiA5Liq5YaZ5rm_55qE56iL5bqP54y_size_20_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/cca504f2828d4ea0992309ca94d005e0.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5LiA5Liq5YaZ5rm_55qE56iL5bqP54y_size_20_color_FFFFFF_t_70_g_se_x_16 1]: /images/20220828/f13dc61cbd084f9bbd18f56feebfca89.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5LiA5Liq5YaZ5rm_55qE56iL5bqP54y_size_20_color_FFFFFF_t_70_g_se_x_16 2]: /images/20220828/6e0d267c61c9401f902ce37f10f88e8a.png