技巧一：尽量使用group by来代替distinct

注意：在数据量很大的情况下，先使用group by去重再count()计数，效率要高于count(distinct *)

技巧二：利用窗口函数grouping sets、cube、rollup

1、GROUPING SETS()：在group by查询中，根据不同的维度组合进行聚合，等价于
将不同维度的group by结果集进行union all。聚合规则在括号中进行指定。
注意：聚合结果均在同一列，分类字段用不同列来进行区分

--性别、城市、等级用户分布-- 
SELECT sex,
 city,
 level, 
 count(distinct user_id) 
FROM user_info 
GROUP BY sex,city,level 
GROUPING SETS (sex,city,level);

2、cube：根据group by 维度的所有组合进行聚合

--性别、城市、等级的各种组合的用户分布-- 
SELECT sex, 
 city, 
 level, 
 count(distinct user_id) 
FROM user_info 
GROUP BY sex,city,level 
with cube;

3、rollup：以最左侧的维度为主，进行层级聚合，是cube的子集。

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;

技巧三：union all时可以开启并发执行

参数设置：set hive.exec.parallel=true
可以并行的任务较多时，开启并发执行，可以提高执行效率。

技巧四：利用lateral view进行行转列

user_goods_category 列名

举例

user_name

用户名

category_detail

用户购买过的品类列表，用逗号进行分割

--每个品类的购买用户数--
select b.category,
 count(distinct a.user_name)
from user_goods_category a
lateral view explode(split(category_detail,','))b as category
group by b.category;

split()：字符串分割函数
explode：行转列函数

技巧五：表连接优化

  1. 小表在前，大表在后
    Hive假定查询中最后的一个表是大表，它会将其它表缓存起来，然后扫描
    最后那个表。
  2. 使用相同的连接键
    当对3个或者更多个表进行join连接时，如果每个on子句都使用相同的连接
    键的话，那么只会产生一个MapReduce job。
  3. 尽早的过滤数据
    减少每个阶段的数据量，对于分区表要加分区，同时只选择需要使用到的
    字段。
  4. 逻辑过于复杂时，引入中间表

技巧六：如何解决数据倾斜

数据倾斜的表现：
任务进度长时间维持在99%（或100%），查看任务监控页面，发现只有少量（1个或几个）reduce子任务未完成。因为其处理的数据量和其他reduce差异过大。
数据倾斜的原因与解决办法：
1、空值产生的数据倾斜
解决：如果两个表连接时，使用的连接条件有很多空值，建议在连接条件中增加过滤
例如：on a.user_id=b.user_id and a.user_id is not null
2、大小表连接(其中一张表很大，另一张表非常小)
解决：将小表放到内存里，在map端做Join
3、两个表连接条件的字段数据类型不一致
解决：将连接条件的字段数据类型转换成一致的
例如：on a.user_id=cast(b.user_id as string)