最近在做大数据处理时, 遇到两个大表 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.uid
- group 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_tb as 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 的数据量, 同时优化没有放之四海而皆准的方法, 一定是结合业务进行的.
来源: https://www.cnblogs.com/duma/p/11186279.html