大佬教程收集整理的这篇文章主要介绍了Hive:select count(distinct)优化以及hive.groupby.skewindata,大佬教程大佬觉得挺不错的,现在分享给大家,也给大家做个参考。
原文链接:https://juejin.cn/post/6926536667877048333
数据分析师小A接到需求,需要统计当日各个省份20岁以下的日活跃用户数(去重统计user_id,即UV)
现有一个Hive表存储着用户行为数据:user_behaviour_trace_info
列 | 描述 |
---|---|
user_id | 用户id |
nickname | 昵称 |
age | 年龄 |
province | 省份 |
url | 访问地址 |
access_time | 访问时间 |
device_id | 用户手机设备id |
小A很顺其自然的写出这段SQL:
SELEct
province,
count(disTinct user_id) as uv
from
user_behaviour_trace_info
where
access_time = '今天' and age < 20
group by
province
复制代码
立马提交SQL开始执行任务,一顿操作猛如虎,一看时长十点五(小时)
心想不愧是用户行为数据,数据量居然这么大?那让我们看看任务各个Task的执行耗时:
我们可以观察到
任务执行时间长,MR Task 平均耗时短,极有可能是出现了数据倾斜!
那我们继续看看Map Task的执行情况,按Map Task耗时倒序排序
接下来看看Reduce阶段,按Reduce Task耗时倒序排序
好家伙,有一个Reduce Task执行了10个小时,另个一执行了近2小时,其余Reduce Task的执行时间很短。
说好了大家一起干活,最终却只有我一个人扛下了所有?
我们先要弄明白 Hive 是如何执行这段 SQL 的
Hive SQL 最终要转化成 MapReducer 任务,在逻辑上可以细分为三个阶段:
简而言之:SQL 中的 Group By 字段会决定某条数据最终落在哪一个 Reducer 上处理。
下文将 group by 的字段称之为 group_by_column
那么,对于刚刚那段SQL,group_by_column 是 province,同一个 province 的数据会分配给同一个 Reducer,在 Reduce 阶段,对 user_id 进行去重统计
然鹅,我国共有34个省级行政区域,一个 Reducer 处理一个省的数据,最多也只能有 34 个 Reducer 同时处理数据
当然,多个省份还可能落在同一个 Reducer 中
我们先来分析一下最初那段SQL的本质:
Reduce 阶段任务最重,执行了去重和计数两个操作:
SQL任务慢的原因是:同一个 province 的数据全部由同一个 Reducer 处理
那么优化思路就@R_365_9772@了:将去重和计数两个操作分开,并且用多个Task同时计数,最终再汇总所有Task的计数数据
其实 Hive 早就考虑到这个场景,并且贴心的提供了 hive.groupby.skewindata 参数。
当 hive.groupby.skewindata = true 时,Hive 会将原来的一个 MaReduce 阶段转化成两个 MapReduce 阶段:
这样的描述看起来有点云里雾里,那不妨让我们自己通过手动优化来更加深入的理解这个参数。手动优化的思路和原理和 hive.groupby.skewindata = true 是一致的
我们先实现第一步,在每个 province 中,对 user_id 进行去重
那么我们可以写出这段SQL
SELEct
province,
user_id,
cast(rand() * 1000 as int) as random_key -- 随机数,作用稍后解释~
from
user_behaviour_trace_info
where
access_time = '今天' and age < 20 -- uv统计条件
and user_id is not null -- count(disTinC)不统计null值
group by -- 对数据进行去重
province,
user_id
复制代码
聪明的同学已经看出来了,这里除了对数据进行去重外,还多了一个随机数字段。这个随机数字段是用来做什么的呢,继续往下看你就知道了~
数据去重完毕后,只需要统计每个 province 的 user_id 个数就能得到对应 province 的 uv 指标!
由上文提到,group_by_column 决定了数据怎么分发给 Reducer
同一个 group_by_column 的数据会分配给同一个 Reducer
那么我们该如何让多个 Reducer 同时计算某个 province 的 user_id 个数呢?这里就可以使用去重阶段“多出来”的 随机数 random_key !
SELEct
province,
random_key,
sum(1) as partial_uv -- 对 user_id 进行计数,是局部聚合结果
from (
SELEct -- 子查询是第一步SQL:数据去重
province,
user_id,
cast(rand() * 1000 as int) as random_key
from
user_behaviour_trace_info
where
access_time = '今天' and age < 20
and user_id is not null
group by
province,
user_id
) t1
group by -- 对随机数也进行 group by,让多个 Reducer 一起统计数据
province,
random_key
复制代码
使用组合键 "province + random_key" 进行 group by,同一个 province 的数据会随机分发给多个 Reducer
每个 Reducer 对 user_id 进行计数,获得局部聚合结果
任务执行过程如下:
第一步 + 第二步 就相当于是 hive.groupby.skewindata = true 时的一阶段Mapreduce
在第二步中,我们已经将同一个 province 的 user_id 分成多个部分,并且统计出了每个部分的 user_id 数量(partial_uv)
那么接下来,我们只要对局部聚合结果进行简单的相加就可以了
最终SQL如下:
SELEct
province,
sum(partial_aggregation) as uv -- 最终聚合结果就是 count(disTinct user_id)
from (
SELEct -- 第二步SQL:打散数据,计算局部聚合结果
province,
random_key,
sum(1) as partial_uv
from ( -- 第一步SQL:数据去重
SELEct
province,
user_id,
cast(rand() * 1000 as int) as random_key
from
user_behaviour_trace_info
where
access_time = '今天' and age < 20
and user_id is not null
group by
province,
user_id
) t1
group by
province,
random_key
) t2
group by
province
复制代码
hive.groupby.skewindata 对 count(disTinct) 的优化是有限制的,当 hive.groupby.skewindata = true 时,SQL只能对一个列进行 count(disTinct),否则会抛出异常:
Caused by: org.apache.hadoop.hive.ql.parse.SemanticException: DISTinCT on different columns not supported with skew in data
复制代码
其实这很容易理解,在刚刚的手动优化过程中,我们能够很容易发现,这个方法不能同时对多个列进行 去重+计数 得出各自的 count(disTinct) 值
主要原因:无法在某一个维度里,同时对多个列进行去重
count(disTinct)优化方案不能直接套用在计算多列的情况上,但可以采用分治的思想,对每个列单独计算 count(disTinct),然后再将结果进行合并
现在有个需求,需要按省份分别去重统计当日 user_id 和 device_id 的去重数量,要求用户年龄为20岁以下
优化前SQL:
SELEct
province,
count(disTinct user_id) as uv,
count(disTinct device_id) as dv
from
user_behaviour_trace_info
where
access_time = '今天' and age < 20
group by
province
复制代码
值得注意的是,最外层 SELECT 使用 COALESCE() 是因为:在单独计算某个 count(disTinct) 时,可能因为添加了统计条件(年龄小于20岁),而导致 province 没有对应的取值,left join 时指标为 null
虽然写起来比原SQL要麻烦些,但效率吊打原SQL不知道多少倍
笔者曾经将一个9小时耗时的任务,通过该方法优化至15分钟~
以上是大佬教程为你收集整理的Hive:select count(distinct)优化以及hive.groupby.skewindata全部内容,希望文章能够帮你解决Hive:select count(distinct)优化以及hive.groupby.skewindata所遇到的程序开发问题。
如果觉得大佬教程网站内容还不错,欢迎将大佬教程推荐给程序员好友。
本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ:384754419,请注明来意。