一个SQL语句的分析
SELECT a.Key, SUM(a.Cnt) AS Cnt
FROM (
SELECT Key, COUNT(*) AS Cnt
FROM TableName
GROUP BY Key,
CASE
WHEN Key = 'KEY001' THEN Hash(Random()) % 50
ELSE 0
END
) a
GROUP BY a.Key;
这个SQL其实是一个解决数据倾斜的SQL
先看里面的select语句,里面的select语句其实是根据key进行分组,但是这个key对应的数据存在数据倾斜,key=KEY001的数据占了整份数据的90%,所以直接针对key进行分组肯定会出现数据倾斜,应该计算效率,所以在这里就实现了曲线救国,先把key=KEY001的数据打散,分成50份,进行局部聚合
最后再通过外面的select进行全局的聚合,这样就可以显著提高计算效率
Hive数据倾斜的解决方案
可能会触发Hive数据倾斜的几种情况
关键字 | 情形 | 后果 |
---|---|---|
join | 其中一个表较小,但是key集中 | 分发到某一个或几个reduce |
大表与小表,但是分桶的判断字段0值或空值过多 | 这些空值都由一个reduce处理 | |
group by | group by维度过小,处理的数量过多 | 处理某值的reduce非常多 |
count distinct | 某特殊值过多 | 处理此特殊值的reduce非常多 |
原因
- key分布不均匀
- 业务数据本身的特性
- 建表时考虑不周
- 某些SQL语句本身就有数据倾斜
表现
- 任务进度长时间维持在99%(或100%),查看任务监控页面,发现只有少量(1个或几个)reduce子任务未完成。因为其处理的数据量和其他reduce差异过大。
- 单一reduce的记录数与平均记录数差异过大,通常可能达到3倍甚至更多。 最长时长远大于平均时长。
数据倾斜的解决方案
参数调节:
hive.map.aggr=true
hive.groupby.skewindata=true
有数据倾斜的时候进行负载均衡,当选项设定为 true,生成的查询计划会有两个 MR Job。第一个 MRJob 中,Map 的输出结果集合会随机分布到 Reduce 中,每个 Reduce 做部分聚合操作,并输出结果,这样处理的结果是相同的 Group By Key 有可能被分发到不同的 Reduce 中,从而达到负载均衡的目的;第二个 MR Job 再根据预处理的数据结果按照 Group By Key 分布到 Reduce 中(这个过程可以保证相同的Group By Key 被分布到同一个 Reduce 中),最后完成最终的聚合操作。
SQL语句调节:
大小表Join:使用map join让小的维度表(1000条以下的记录条数) 先进内存。在map端完成reduce.
大表Join大表:把空值的key变成一个字符串加上随机数,把倾斜的数据分到不同的reduce上,由于null值关联不上,处理后并不影响最终结果。
count distinct大量相同特殊值
count distinct时,将值为空的情况单独处理,如果是计算count distinct,可以不用处理,直接过滤,在最后结果中加1。如果还有其他计算,需要进行group by,可以先将值为空的记录单独处理,再和其他计算结果进行union。
针对去重求和的需求还可以这样做:采用sum() group by的方式来替换count(distinct)完成计算。
特殊情况特殊处理:在业务逻辑优化效果的不大情况下,有些时候是可以将倾斜的数据单独拿出来处理。最后union回去。
Hue的使用
Hue-非技术人员操作Hive的利器 官方测试地址
Hive中的存储格式及压缩格式
![](/i/l/?n=23&i=blog/1681090/202304/1681090-20230424210220125-507419879.png)
![](/i/l/?n=23&i=blog/1681090/202304/1681090-20230424211019664-1510597653.png)
参考
HDFS存储格式及压缩算法
一文搞懂Hive存储格式及压缩格式,太清晰了!