百亿级数据处理优化 - 好文

最近在做大数据处理时，遇到两个大表 join 导致数据处理太慢（甚至算不出来）的问题。我们的数仓基于阿里的 ODPS，它与 Hive
类似，所以这篇文章也适用于使用 Hive 优化。处理优化问题，一般是先指定一些常用的优化参数，但是当设置参数仍然不奏效的时候，我们就要结合具体的业务，在
SQL 上做优化了。为了不增加大家的阅读负担，我会简化这篇文章的业务描述。

问题

这是一个离线数据处理的问题。在这个业务中有两张表，表结构及说明如下：
user_article_tb 表：

字段解释：

uid: 用户标识，itemid：文章id，dur: 阅读文章时长，如果大于 0 代表阅读了文章，等于 0 代表没有点击文章

dt：天分区，每天 55 亿条记录
user_profile_tb 表：字段解释：
uid：用户标识，gender：性别，F 代表女，M 代表男，age:年龄，city：城市
dt：天分区字段，这是一张总表，每天存储全量用户画像属性，最新数据十亿级别
需求是这样的：计算 7 天中，女性用户在每篇文章上的 ctr （最终会按照降序进行截断）。直接写 SQL 很容易，如下：
select 　　itemid 　　, count(if(dur > 0, 1, null)) / count(1) ctr from 　　( 　　
select uid, itemid, dur 　　from user_article_tb 　　where dt>='20190701' and dt<='
20190707' 　　) data_tb 　　join 　　( 　　　　select * 　　　　from user_profile_tb 　　　　where
dt='20190707' --最新的日期　　　　and gender='F' 　　) profile_tb 　　on 　　　　data_tb.uid =
profile_tb.uidgroup by 　　itemid order by ctr desc limit 50000 ;
那么问题来了：

* 对于 user_article_tb 来说，7天的数据量将近 400 亿条记录，还需要 join 一张十亿级别的画像表。这个数据量基本上就跑不出来了
* 像这种探索性质的需求，经常会变化。假设需求变成计算男性或者计算一二线城市用户的呢？可能又需要重跑整个数据，既要付出时间成本又要付出高昂的资源成本
解决

我们一一解决上面提到的两个问题。先考虑第一个，既然 join
的两张表太大了，我们能不能尝试把表变小呢。答案是肯定的，对于画像表来说显然是没办法缩小了，但是对于 user_artitle_tb
是可以的。我们可以按照表的分区字段 dt 用每天的数据分别 join 画像表，将结果再按天存储在一张临时表里面。这样每天就是十亿级别的数据
join，基本可以解决问题。但是每天的数据仍有多余的 join，比如：某天的数据中 uid = 00001 的用户，一天看了 1000
篇文章，那这个用户就需要多 join 999 次。在我们的业务中一个用户一天看文章的数量 > 10 是很普遍的，因此多余 join 的情况还是比较严重的。

针对上面提到的多余 join 的情况，最彻底的解决方法就是把 user_article_tb 表变成 uid 粒度的，跟画像表一样。我们将 7
天的数据转换成 uid 粒度的 SQL 如下：
insert overwrite table user_article_uid_tb as select uid, wm_concat(':',
concat_ws(',', itemid, dur)) item_infos from 　　( 　　　select * 　　　　from
user_article_tb 　　where dt >= '20190701' and dt <= '20190707' 　　) tmp group by
uid
从上面 SQL 可以看到，我们首先将 7 天的数据按照 uid 做 group by 操作，构造 item_infos。因为我们的是计算
ctr，所以我们可以按照 uid 粒度对表做转换，并且 item_infos 字段包含什么是要根据业务需求做选择。每天不到 1 亿 uid，7天汇总的 uid
不到 10 亿，两张 uid 粒度的表进行 join 就会快很多。

至此，多余 join 的问题得到了解决，再来看看第二个问题。这个问题其实就是我们维度建模理论中所说的宽表，为了避免统计不同维度时频繁 join
维表，我们可以在上游数据将常用的维度提前关联起来，形成一张大宽表。下游数据可以直接用从而减少 join。以我们的问题为例，SQL 如下：
create table user_profile_article_uid_tb as select data_tb.uid , item_infos ,
gender , age , city 　　-- 其他维度字段 from 　　( 　　 select uid, item_infos 　　from
user_article_uid_tb 　　) data_tb 　　join 　　( 　　select uid, gender, age, city 　　　　
from user_profile_tb 　　　　where dt='20190707' --最新的日期　　) profile_tb 　　on
　　　　data_tb.uid= profile_tb.uid
;
这样，上面提到的两个问题就都解决了。最终我们的需求：女性用户每篇文章的 ctr 计算如下：
select itemid , count(if(dur > 0, 1, null)) / count(1) ctr from 　　( 　　　　select
　　　　　　split(item_info, ',')[0] itemid 　　　, split(item_info, ',')[1] dur 　　　　
from user_profile_article_uid_tb 　　　　lateral view explode(split(item_infos, ':'
)) item_tbas item_info 　　) tmp group itemid order by ctr desc limit 50000
参数优化
mapreduce.map.memory.mb mapreduce.reduce.memory.mb mapred.reduce.tasks
这些参数设置是比较通用的选项，当这些选项不能够达到最优的效果时，需要从业务上进行优化。

小结

这篇文章主要介绍了在 ODPS 或 Hive 上，百亿级数据规模的 join 优化。核心思想就是减少 join
的数据量，同时优化没有放之四海而皆准的方法，一定是结合业务进行的。

欢迎关注公众号「渡码」，一起见证成长

热门工具换一换