【SQL】数据倾斜 SQL 改写

欢迎点击此处关注公众号。

数据倾斜有很多种处理方式，几乎是面试必考题了。今天总结一下可能数据倾斜的 SQL 以及如何改写。

1.求 UV

UV 就是独立访客，经常求的指标，因为要 count distinct，所以非常容易倾斜。

例如：一个表 tb 有两个字段，app_id，user_id，app_id 代表了不同的 app，求每个 app 的 UV。

普通的 SQL：

select
	app_id,
	count(distinct user_id)
from tb
group by app_id;
1
2
3
4
5

根据 group by 的执行原理，app_id 相同的会发到同一个 reduce 中处理。如果某一个 app_id 数据量很大（比如抖音远大于西瓜视频），那么就会产生倾斜。

因此，可以分为两个阶段，一个阶段通过 group by 对 app_id 和 user_id 进行去重，第二个阶段再计算 UV。

select
	app_id,
	sum(uv) as uv
from (
	select
    	app_id,
    	user_id,
    	1 as uv
    from tb
    group by app_id, user_id
) a
group by app_id;
1
2
3
4
5
6
7
8
9
10
11
12

2.求 PV

PV 和 UV 类似，同样需要 group by 之后聚合，但是不需要去重，具有可加性，因此可以分散计算，再相加。

例如，对于 user_id 为 1，2，2，3，计算 PV 时可以 1，2 计算一次，结果是 2，然后 2，3 计算一次，结果是 2，最后 PV = 2 + 2 = 4，结果正确。而 UV 的结果是 3，不能分散计算。

普通的 SQL：

select
	app_id,
	count(user_id)
from tb
group by app_id;
1
2
3
4
5

根据 group by 的执行原理，app_id 相同的会发到同一个 reduce 中处理。如果某一个 app_id 数据量很大（比如抖音远大于西瓜视频），那么就会产生倾斜。

因此，可以分为两个阶段，一个阶段加随机数把 app_id 分散聚合一次，第二个阶段再聚合。

select
	substring_index(tmp_app_id,"_",1) app_id,
	sum(pv) as pv
from (
	select
    	concat(app_id,"_",round(rand()*10)) tmp_app_id,
    	count(user_id) as pv
    from tb
    group by tmp_app_id
) a
group by app_id;
1
2
3
4
5
6
7
8
9
10
11

3.join

根据 join 的执行原理，join on 的 key 存在某一个值数量特别多会发生倾斜。比如日志表和商品表关联：

select * 
from logs a join items b
on a.item_id = b.item_id;
1
2
3

分配到热门商品的 reducer 就会很慢，因为热门商品的行为日志肯定是最多的。

这个时候就会用到加随机数的方法，也就是在 join 的时候增加一个随机数，随机数的取值范围 n 相当于将 item 给分散到 n 个 reducer：

select a.*, b.*
from (
    select
    	*,
    	cast(rand()*10 as int) as r_id
    from logs
) a
join (
    select
    	*,
    	r_id
    from items 
	lateral view explode(range_list(1,10)) rl as r_id
)b
on a.item_id = b.item_id and a.r_id = b.r_id;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

4.空值

例如埋点日志经常丢失 user_id，存在大量空值。所以空值到一个 reduce 中发生倾斜。

解决方法：空值赋新的 key 值。

select *
from logs a left join users b
on
	case
        when a.user_id is null then concat(‘hive’,rand() )
        else a.user_id
        end
    = b.user_id;
1
2
3
4
5
6
7
8