Hivesql常用优化技巧

首先介绍下什么是HIve？
1.基于Hadoop的开源的数据仓库工具，用于处理海量海量结构化数据。
2.Hive把HDFS中的结构化的数据文件映射成数据表。
3.Hive通过HiveSQL进行解析和转换，最终映射成一系列在hadoop上运行的mapreduce任务，通过执行这些任务完成分析和处理。
4.HiveSql和Mysql一样，都遵循着SQL的标准，因此它们很多语句都是一样的。

一、先复习下SQL的语句的结构

SELECT  *     （必须）
FROM    		表（数据源）
WHERE  			条件
GROUP BY  	    字段
HAVING   		条件
ORDER BY  	    字段(排序 默认 ASC)
LIMIT 			限制数据条数

注意：sql对大小写一般不敏感，一般命令用大写，表名，字段用小写

二、 SQL语句的执行顺序

第一步：确定数据源
FROM
JOIN
ON 
第二步：过滤数据
WHERE 
GROUP BY (开始使用SELECT 中的别名，后面的语句中都可以使用)
avg，sum.......
HAVING
第三步：查询数据
SELECT
第四步：显示数据
DISTINCT
ORDER BY 
LIMIT

三、优化技巧

技巧一：列裁剪和分区裁剪
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 a
sort by a
limit 10;

技巧三：去重技巧 --用group by 来代替distinct

-- 取出user_trade表中全部支付用户
-- 原有写法
SELECT distinct user_name
FROM user_trade
WHERE dt>'0';
--测试时长 43 s
-- 优化写法
SELECT user_name
FROM user_trade
WHERE dt>'0'
GROUP BY user_name;
--测试时长 29 s

注意：在极大的数据量(且很多重复值)时，可以先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 指定分组的维度 聚合结果均在同一列，分类字段用不同列来区分
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;

技巧五 换个思路解题
条条大路通罗马，写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;

时间对比：
未开并发执行 103 s
开启并发执行 64 s

技巧七 表连接优化

1.小表在前，大表在后
Hive假定查询中最后的一个表是大表，它会将其它表缓存起来，然后扫描最后那个表。

2.使用相同的连接键
当对3个或者更多个表进行join连接时，如果每个on子句都使用相同的连接键的话，那么只会产生一个MapReduce job。
3.尽早的过滤数据
减少每个阶段的数据量，对于分区表要加分区，同时只选择需要使用到的字段。

技巧八 遵循严格模式

所谓严格模式，就是强制不允许用户执行3种有风险的HiveSQL语句，一旦执行会直接报错。
1.查询分区表时不限定分区列的语句。
2.两表join产生了笛卡尔积的语句。
3.要order by来排序但没有指定limit的语句。
要开启严格模式，需要将参数hive.mapred.mode设为strict。