一个SQL语句分析

统计每个key出现的次数，其中key=KEY001占了整份数据的90%

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耗时

原因：
1)、key分布不均匀
2)、业务数据本身的特性
3)、建表时考虑不周
4)、某些SQL语句本身就有数据倾斜
表现：
任务进度长时间维持在99%（或100%），查看任务监控页面，发现只有少量（1个或几个）reduce子任务未完成。因为其处理的数据量和其他reduce差异过大。
单一reduce的记录数与平均记录数差异过大，通常可能达到3倍甚至更多。最长时长远大于平均时长。
数据倾斜的解决方案

参数调节

hive.map.aggr=true
Map 端部分聚合，相当于Combiner

hive.groupby.skewindata=true
有数据倾斜的时候进行负载均衡，当选项设定为 true，生成的查询计划会有两个 MR Job。第一个 MR Job 中，Map 的输出结果集合会随机分布到 Reduce 中，每个 Reduce 做部分聚合操作，并输出结果，这样处理的结果是相同的 Group By Key 有可能被分发到不同的 Reduce 中，从而达到负载均衡的目的；第二个 MR Job 再根据预处理的数据结果按照 Group By Key 分布到 Reduce 中（这个过程可以保证相同的 Group By Key 被分布到同一个 Reduce 中），最后完成最终的聚合操作。
SQL语句调节：

大小表Join

1、小、大表 join
在小表和大表进行join时，将小表放在前边，效率会高。hive会将小表进行缓存。
2、mapjoin
使用mapjoin将小表放入内存，在map端和大表逐一匹配。从而省去reduce。
样例：

select /*+MAPJOIN(b)*/ a.a1,a.a2,b.b2 from tablea a JOIN tableb b ON a.a1=b.b1

缓存多张小表：

select /*+MAPJOIN(b,c)*/ a.a1,a.a2,b.b2 from tablea a JOIN tableb b ON a.a1=b.b1 JOIN tbalec c on a.a1=c.c1

mapjoin的join发生在map阶段，join的join发生在reduce阶段，mapjoin可以提高效率

https://shentu.run/archives/h-i-v-e-shu-ju-qing-xie