大家好呀，这节课学习 HiveSQL 的常用优化技巧。由于 Hive 主要用来处理非常大的数据，运行过程由于通常要经过 MapReduce 的过程，因此不像 MySQL 一样很快出结果。而使用不同方法写出来的 HiveSQL 语句执行效率也是不一样的，因此为了减少等待的时间，提高服务器的运行效率，我们需要在 HiveSQL 的语句上进行一些优化。
本节课的主要内容：
引言
1、技巧一：列裁剪和分区裁剪
（1）列裁剪
（2）分区裁剪
2、技巧二：排序技巧——sort by代替order by
3、技巧三：去重技巧——用group by来替换distinct
4、技巧四：聚合技巧——grouping sets、cube、rollup
（1）grouping sets
（2）cube
（3）rollup
5、技巧五：换个思路解题
6、技巧六：union all时可以开启并发执行
7、技巧七：表连接优化
8、技巧八：遵循严格模式
引言
Hive 作为大数据领域常用的数据仓库组件，在平时设计和查询时要特别注意效率。影响Hive效率的几乎从不是数据量过大，而是数据倾斜、数据冗余、job 或 I/O 过多、MapReduce 分配不合理等等。对 Hive 的调优既包含对HiveSQL 语句本身的优化，也包含 Hive 配置项和 MR 方面的调整。
技巧一：列裁剪和分区裁剪
1、列裁剪
列裁剪就是在查询时只读取需要的列。当列很多或者数据量很大时，如果select 所有的列或者不指定分区，导致的全表扫描和全分区扫描效率都很低。Hive中与列裁剪优化相关的配置项是 hive.optimize.cp，默认是 true。
2、分区裁剪
分区裁剪就是在查询时只读需要的分区。Hive中与分区裁剪优化相关的则是 hive.optimize.pruner，默认是 true。
技巧二：排序技巧——sort by 代替 order by
HiveSQL中的 order by 与其他 SQL 语言中的功能一样，就是将结果按某个字段全局排序，这会导致所有map端数据都进入一个 reduce 中，在数据量大时可能会长时间计算不完。
如果使用 sort by，那么就会视情况启动多个 reducer 进行排序，并且保证每个 reducer 内局部有序。为了控制 map 端数据分配到 reduce 的 key，往往还要配合 distribute by 一同使用。如果不加 distribute by 的话，map 端数据就会随机分配给 reducer。
-- 未优化写法
select a,
b,
c
from table
where xxx
order by a
limit 10;
-- 优化写法
select a,
b,
c
from table
where xxx
distribute by user_id
sort by a
limit 10;
这里需要解释一下，distribute by 和 sort by 结合使用是如何相较于 order by 提升运行效率的。
假如我们要对一张很大的用户信息表按照年龄进行分组，优化前的写法是直接 order by age。使用 distribute by 和 sort by 结合进行优化的时候，sort by 后面还是 age 这个排序字段，distribute by 后面选择一个没有重复值的均匀字段，比如 user_id。
这样做的原因是，通常用户的年龄分布是不均匀的，比如20岁以下和50岁以上的人非常少，中间几个年龄段的人又非常多，在 Map 阶段就会造成有些任务很大，有些任务很小。那通过 distribute by 一个均匀字段，就可以让系统均匀地进行“分桶”，对每个桶进行排序，最后再组合，这样就能从整体上提升 MapReduce 的效率。
技巧三：去重技巧——用 group by 来替换 distinct
取出 user_trade 表中全部支付用户：
-- 原有写法
SELECT distinct user_name
FROM user_trade
WHERE dt>'0';
-- 优化写法
SELECT user_name
FROM user_trade
WHERE dt>'0'
GROUP BY user_name;
原有写法的执行时长：
优化写法的执行时长：
考虑对之前的案例进行优化：
-- 在2019年购买后有退款的用户
--优化前
SELECT a.user_name
FROM
(SELECT distinct user_name
FROM user_trade
WHERE year(dt)=2019)a
JOIN
(SELECT distinct user_name
FROM user_refund
WHERE year(dt)=2019)b
on a.user_name=b.user_name;
-- 优化写法：
SELECT a.user_name
FROM
(SELECT user_name
FROM user_trade
WHERE year(dt)=2019
GROUP BY user_name)a
JOIN
(SELECT user_name
FROM user_refund
WHERE year(dt)=2019
GROUP BY user_name)b
on a.user_name=b.user_name;
注意： 在极大的数据量（且很多重复值）时，可以先 group by 去重，再count() 计数，效率高于直接 count(distinct **) 。
技巧四：聚合技巧——grouping sets、cube、rollup
1、grouping sets
如果我们想知道用户的性别分布、城市分布、等级分布，你会怎么写？
通常写法：
--性别分布--
SELECT sex,
count(distinct user_id)
FROM user_info
GROUP BY sex;
--城市分布--
SELECT city,
count(distinct user_id)
FROM user_info
GROUP BY city;
--等级分布--
SELECT level,
count(distinct user_id)
FROM user_info
GROUP BY level;
缺点：要分别写三次SQL，需要执行三次，重复工作，且费时。
那该怎么优化呢？
-- 性别、城市、等级用户分布
SELECT sex,
city,
level,
count(distinct user_id)
FROM user_info
GROUP BY sex,city,level
GROUPING SETS (sex,city,level);
-- grouping sets指定分组的维度
注意：这个聚合结果相当于纵向地堆在一起了（Union all），分类字段用不同列来进行区分，也就是每一行数据都包含 4 列，前三列是分类字段，最后一列是聚合计算的结果。
GROUPING SETS()：在 group by 查询中，根据不同的维度组合进行聚合，等价于将不同维度的 group by 结果集进行 union all。聚合规则在括号中进行指定。
拓展
如果我们想知道用户的性别分布以及每个性别的城市分布，你会怎么写？
-- 性别分布
SELECT sex,
count(distinct user_id)
FROM user_info
GROUP BY sex;
-- 每个性别的城市分布
SELECT sex,
city,
count(distinct user_id)
FROM user_info
GROUP BY sex,
city;
那该怎么优化呢？
-- 性别、性别&城市的用户分布
SELECT sex,
city,
count(distinct user_id)
FROM user_info
GROUP BY sex,city
GROUPING SETS (sex,(sex,city));
注意： 第二列为NULL的，就是性别的用户分布，其余有城市的均为每个性别的城市分布。
2、cube
cube：根据 group by 维度的所有组合进行聚合
-- 性别、城市、等级的各种组合的用户分布
SELECT sex,
city,
level,
count(distinct user_id)
FROM user_info
GROUP BY sex,city,level
GROUPING SETS (sex,city,level,(sex,city),(sex,level),(city,level),(sex,city,level));
-- 优化写法
-- 性别、城市、等级的各种组合的用户分布--
SELECT sex,
city,
level,
count(distinct user_id)
FROM user_info
GROUP BY sex,city,level
with cube;
注意：跑完数据后，整理很关键！！！
3、rollup
rollup：以最左侧的维度为主，进行层级聚合，是cube的子集。
如果我想同时计算出，每个月的支付金额，以及每年的总支付金额，该怎么办？
-- 优化前的写法
SELECT a.dt,
sum(a.year_amount),
sum(a.month_amount)
FROM
(SELECT substr(dt,1,4) as dt,
sum(pay_amount) year_amount,
0 as month_amount
FROM user_trade
WHERE dt>'0'
GROUP BY substr(dt,1,4)
UNION ALL
SELECT substr(dt,1,7) as dt,
0 as year_amount,
sum(pay_amount) as month_amount
FROM user_trade
WHERE dt>'0'
GROUP BY substr(dt,1,7)
) a
GROUP BY a.dt;
那应该如何优化呢？
-- 优化写法
SELECT year(dt) as year,
month(dt) as month,
sum(pay_amount)
FROM user_trade
WHERE dt>'0'
GROUP BY year(dt),
month(dt)
with rollup;
-- 相当于grouping sets(year,(year,month))
技巧五：换个思路解题
条条大路通罗马，写SQL亦是如此，能达到同样效果的SQL有很多种，要学会思路转换，灵活应用。
来看一个我们之前做过的案例：
--在2017年和2018年都购买的⽤户--
SELECT a.user_name
FROM
(SELECT distinct user_name
FROM user_trade
WHERE year(dt)=2017)a
JOIN
(SELECT distinct user_name
FROM user_trade
WHERE year(dt)=2018)b on
a.user_name=b.user_name;
有没有别的写法呢？
-- 方式一
SELECT a.user_name
FROM
(SELECT user_name,
count(distinct year(dt)) as year_num
FROM user_trade
WHERE year(dt) in (2017,2018)
GROUP BY user_name) a
WHERE a.year_num=2;
-- 方式二
SELECT user_name,
count(distinct year(dt)) as year_num
FROM user_trade
WHERE year(dt) in (2017,2018)
GROUP BY user_name
having count(distinct year(dt))=2;
技巧六：union all 时可以开启并发执行
Hive 中互相没有依赖关系的 job 间是可以并行执行的，最典型的就是
多个子查询union all。在集群资源相对充足的情况下，可以开启并
行执行。参数设置：set hive.exec.parallel=true;
-- 每个用户的支付和退款金额汇总
SELECT a.user_name,
sum(a.pay_amount),
sum(a.refund_amount)
FROM
( SELECT user_name,
sum(pay_amount) as pay_amount,
0 as refund_amount
FROM user_trade
WHERE dt>'0'
GROUP BY user_name
UNION ALL
SELECT user_name,
0 as pay_amount,
sum(refund_amount) as refund_amount
FROM user_refund
WHERE dt>'0'
GROUP BY user_name
) a
GROUP BY a.user_name;
时间对比：
技巧七：表连接优化
- 小表在前，大表在后
Hive假定查询中最后的一个表是大表，它会将其它表缓存起来，然后扫描最后那个表。 - 使用相同的连接键
当对3个或者更多个表进行join连接时，如果每个on子句都使用相同的连接键的话，那么只会产生一个MapReduce job。 - 尽早的过滤数据
减少每个阶段的数据量，对于分区表要加分区，同时只选择需要使用到的字段。
技巧八：遵循严格模式
所谓严格模式，就是强制不允许用户执行3种有风险的 HiveSQL 语句，一旦执行会直接报错。
- 查询分区表时不限定分区列的语句（例如 where dt）。
- 两表 join 产生了笛卡尔积的语句（不写连接条件就会产生笛卡尔积）。
- 要 order by 来排序但没有指定 limit 的语句。
要开启严格模式，需要将参数 hive.mapred.mode 设为 strict。
好啦，这节课的内容就是这些。以上优化技巧需要大家在平时的练习和使用中有意识地去注意自己的语句，不断改进，就能掌握最优的写法。
