HiveSQL优化技巧

技巧1：用group by替换distinct。
原有写法：

SELECT distinct user_name
FROM user_trade
WHERE dt>'0';

优化写法：

SELECT user_name
FROM user_trade
WHERE dt>'0'
GROUP BY user_name;

使用group by可以看到运行时间减少许多。
注意：在极大的数据量（较多重复值）时，可以先group by去重，再count（）计数效率高于直接count（distinct **）。

技巧2：利用窗口函数grouping sets，cube，rollup。
1.grouping sets：在group by查询中，根据不同的维度组合进行聚合，等价于将不同维度的group by结果集进行union all。聚合规则在括号中进行指定。
示例：想知道用户的性别分布，城市分布，等级分布，该怎么写。
通常写法：

--性别分布-
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);

注意：聚合结果均在同一列，分类字段用不同列来进行区分。

求用户的性别分布以及每个性别的城市分布：

SELECT sex,
       city,
       count(distinct user_id) 
FROM user_info 
GROUP BY sex,city 
GROUPING SETS (sex,(sex,city));

2.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
以左侧的维度为主，进行层级聚合，是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;

技巧3：换个思路解题
能达到同一种效果的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

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

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

示例：每个品类的购买用户数。

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;

拓展：
列转行函数：concat_ws(’,’,collect_set(column))

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

技巧7：如何解决数据倾斜
数据倾斜的表现：任务进度长时间维持在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)