HIVE优化

Hive优化总结
1、整体架构优化
现在hive的整体框架如下，计算引擎不仅仅支持Map/Reduce，并且还支持Tez、Spark等。根据不同的计算引擎又可以使用不同的资源调度和存储系统。

整体架构优化点：

1、根据不同业务需求进行日期分区，并执行类型动态分区。
相关参数设置：
0.14中默认hive.exec.dynamic.partition=ture

2、为了减少磁盘存储空间以及I/O次数，对数据进行压缩
相关参数设置：
job输出文件按照BLOCK以Gzip方式进行压缩。
3、hive中间表以SequenceFile保存，可以节约序列化和反序列化的时间

相关参数设置：
hive.query.result.fileformat=SequenceFile

4、yarn优化，在此不再展开，后面专门介绍。
2、MR阶段优化
reduce切割算法：
相关参数设置，默认为：
hive.exec.reducers.max=999
hive.exec.reducers.bytes.per.reducer=1G 
reduce task num=min{reducers.max,input.size/bytes.per.reducer}，可以根据实际需求来调整reduce的个数。
3、JOB优化
1、本地执行
默认关闭了本地执行模式，小数据可以使用本地执行模式，加快执行速度。
相关参数设置：
hive.exec.mode.local.auto=true 
默认本地执行的条件是，hive.exec.mode.local.auto.inputbytes.max=128MB， hive.exec.mode.local.auto.tasks.max=4，reduce task最多1个。 性能测试：
数据量（万）     操作     正常执行时间（秒)     本地执行时间（秒）
170     group by     36     16
80     count     34     6

2、mapjoin
默认mapjoin是打开的， hive.auto.convert.join.noconditionaltask.size=10MB
装载到内存的表必须是通过scan的表（不包括group by等操作），如果join的两个表都满足上面的条件，/mapjoin/指定表格不起作用，只会装载小表到内存，否则就会选那个满足条件的scan表。
4、SQL优化
4.1 使用分区剪裁、列剪裁
在SELECT中，只拿需要的列，如果有，尽量使用分区过滤，少用SELECT 。
在分区剪裁中，当使用外关联时，如果将副表的过滤条件写在Where后面，那么就会先全表关联，之后再过滤，比如：
SELECT a.id
FROM lxw1234_a a
left outer join t_lxw1234_partitioned b
ON (a.id = b.url);
WHERE b.day = ‘2015-05-10′
         正确的写法是写在ON后面：
SELECT a.id
FROM lxw1234_a a
left outer join t_lxw1234_partitioned b
ON (a.id = b.url AND b.day = ‘2015-05-10′);
或者直接写成子查询：
SELECT a.id
FROM lxw1234_a a
left outer join (SELECT url FROM t_lxw1234_partitioned WHERE day = ‘2015-05-10′) b
ON (a.id = b.url)
4.2 少用COUNT DISTINCT
数据量小的时候无所谓，数据量大的情况下，由于COUNT DISTINCT操作需要用一个Reduce Task来完成，这一个Reduce需要处理的数据量太大，就会导致整个Job很难完成，一般COUNT DISTINCT使用先GROUP BY再COUNT的方式替换：
SELECT day,
COUNT(DISTINCT id) AS uv
FROM lxw1234
GROUP BY day
可以转换成：
SELECT day,
COUNT(id) AS uv
FROM (SELECT day,id FROM lxw1234 GROUP BY day,id) a
GROUP BY day;
 
虽然会多用一个Job来完成，但在数据量大的情况下，这个绝对是值得的。
4.3 是否存在多对多的关联
只要遇到表关联，就必须得调研一下，是否存在多对多的关联，起码得保证有一个表或者结果集的关联键不重复。
如果某一个关联键的记录数非常多，那么分配到该Reduce Task中的数据量将非常大，导致整个Job很难完成，甚至根本跑不出来。
还有就是避免笛卡尔积，同理，如果某一个键的数据量非常大，也是很难完成Job的。
 
4.4 合理使用MapJoin
MapJoin通常用于一个很小的表和一个大表进行join的场景，具体小表有多小，由参数hive.mapjoin.smalltable.filesize来决定，该参数表示小表的总大小，默认值为25000000字节，即25M。
Hive0.7之前，需要使用hint提示 /+ mapjoin(table) */才会执行MapJoin,否则执行Common Join，但在0.7版本之后，默认自动会转换Map Join，由参数hive.auto.convert.join来控制，默认为true.
仍然以9.1中的HQL来说吧，假设a表为一张大表，b为小表，并且hive.auto.convert.join=true,那么Hive在执行时候会自动转化为MapJoin。 
4.5 合理使用Union All
对同一张表的union all 要比multi insert快的多。
原因是hive本身对这种union all做过优化，即只扫描一次源表；
而多重insert也只扫描一次，但应为要insert到多个分区，所以做了很多其他的事情，导致消耗的时间非常长；
4.6 并行执行Job
用过oracle rac的应该都知道parallel的用途。
并行执行的确可以大的加快任务的执行速率，但不会减少其占用的资源。
在hive中也有并行执行的选项。
set hive.exec.parallel=true; //打开任务并行执行
set hive.exec.parallel.thread.number=16; //同一个sql允许最大并行度，默认为8。
对于同一个SQL产生的JOB,如果不存在依赖的情况下，将会并行启动JOB
4.7 使用本地MR
如果在hive中运行的sql本身数据量很小，那么使用本地mr的效率要比提交到Hadoop集群中运行快很多。
set hive.exec.mode.local.auto=true; //开启本地mr
//设置local mr的最大输入数据量,当输入数据量小于这个值的时候会采用local mr的方式
set hive.exec.mode.local.auto.inputbytes.max=50000000;
//设置local mr的最大输入文件个数,当输入文件个数小于这个值的时候会采用local mr的方式
set hive.exec.mode.local.auto.tasks.max=10;
当这三个参数同时成立时候，才会采用本地mr
4.8 合理使用动态分区
hive.exec.dynamic.partition
默认值：false
是否开启动态分区功能，默认false关闭。
使用动态分区时候，该参数必须设置成true;
hive.exec.dynamic.partition.mode
默认值：strict
动态分区的模式，默认strict，表示必须指定至少一个分区为静态分区，nonstrict模式表示允许所有的分区字段都可以使用动态分区。
一般需要设置为nonstrict
hive.exec.max.dynamic.partitions.pernode
默认值：100
在每个执行MR的节点上，最大可以创建多少个动态分区。
该参数需要根据实际的数据来设定。
比如：源数据中包含了一年的数据，即day字段有365个值，那么该参数就需要设置成大于365，如果使用默认值100，则会报错。
hive.exec.max.dynamic.partitions
默认值：1000
在所有执行MR的节点上，最大一共可以创建多少个动态分区。
同上参数解释。
hive.exec.max.created.files
默认值：100000
整个MR Job中，最大可以创建多少个HDFS文件。
一般默认值足够了，除非你的数据量非常大，需要创建的文件数大于100000，可根据实际情况加以调整。
hive.error.on.empty.partition
默认值：false
当有空分区生成时，是否抛出异常。
一般不需要设置。
4.9 避免数据倾斜
数据倾斜是Hive开发中对性能影响的一大杀手。
症状：
任务迚度长时间维持在99%（或100%）;
查看任务监控页面，发现只有少量（1个或几个）reduce子任务未完成。
本地读写数据量很大。
导致数据倾斜的操作：
GROUP BY, COUNT DISTINCT, join
原因：
key分布不均匀
业务数据本身特点
这里列出一些常用的数据倾斜解决办法：
1.使用COUNT DISTINCT和GROUP BY造成的数据倾斜：
存在大量空值或NULL，或者某一个值的记录特别多，可以先把该值过滤掉，在最后单独处理:
SELECT CAST(COUNT(DISTINCT imei)+1 AS bigint)
FROM lxw1234 where pt = ‘2012-05-28′
AND imei <> ‘lxw1234′ ;
比如某一天的IMEI值为’lxw1234’的特别多，当我要统计总的IMEI数，可以先统计不为’lxw1234’的，之后再加1.
多重COUNT DISTINCT
通常使用UNION ALL + ROW_NUMBER() + SUM + GROUP BY来变通实现。
2.使用JOIN引起的数据倾斜
关联键存在大量空值或者某一特殊值，如”NULL”
空值单独处理，不参与关联；
空值或特殊值加随机数作为关联键；
不同数据类型的字段关联
转换为同一数据类型之后再做关联
4.10 控制Map数和Reduce数
如何合并小文件，减少map数？ 
假设一个SQL任务：
Select count(1) from popt_tbaccountcopy_mes where pt = ‘2012-07-04’;
该任务的inputdir  /group/p_sdo_data/p_sdo_data_etl/pt/popt_tbaccountcopy_mes/pt=2012-07-04
共有194个文件，其中很多是远远小于128m的小文件，总大小9G，正常执行会用194个map任务。
Map总共消耗的计算资源： SLOTS_MILLIS_MAPS= 623,020
我通过以下方法来在map执行前合并小文件，减少map数：
set mapred.max.split.size=100000000;
set mapred.min.split.size.per.node=100000000;
set mapred.min.split.size.per.rack=100000000;
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;
再执行上面的语句，用了74个map任务，map消耗的计算资源：SLOTS_MILLIS_MAPS= 333,500
对于这个简单SQL任务，执行时间上可能差不多，但节省了一半的计算资源。
大概解释一下，100000000表示100M, set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;这个参数表示执行前进行小文件合并，
前面三个参数确定合并文件块的大小，大于文件块大小128m的，按照128m来分隔，小于128m,大于100m的，按照100m来分隔，把那些小于100m的（包括小文件和分隔大文件剩下的），
进行合并,最终生成了74个块。
如何适当的增加map数？
当input的文件都很大，任务逻辑复杂，map执行非常慢的时候，可以考虑增加Map数，来使得每个map处理的数据量减少，从而提高任务的执行效率。
假设有这样一个任务：
Select data_desc,
count(1),
count(distinct id),
sum(case when …),
sum(case when …),
sum(…)
from a group by data_desc
如果表a只有一个文件，大小为120M，但包含几千万的记录，如果用1个map去完成这个任务，肯定是比较耗时的，这种情况下，我们要考虑将这一个文件合理的拆分成多个，
这样就可以用多个map任务去完成。
set mapred.reduce.tasks=10;
create table a_1 as
select * from a
distribute by rand(123);
这样会将a表的记录，随机的分散到包含10个文件的a_1表中，再用a_1代替上面sql中的a表，则会用10个map任务去完成。
每个map任务处理大于12M（几百万记录）的数据，效率肯定会好很多。
看上去，貌似这两种有些矛盾，一个是要合并小文件，一个是要把大文件拆成小文件，这点正是重点需要关注的地方，
根据实际情况，控制map数量需要遵循两个原则：使大数据量利用合适的map数；使单个map任务处理合适的数据量；
 
二、    控制hive任务的reduce数：
1.    Hive自己如何确定reduce数：
reduce个数的设定极大影响任务执行效率，不指定reduce个数的情况下，Hive会猜测确定一个reduce个数，基于以下两个设定：
hive.exec.reducers.bytes.per.reducer（每个reduce任务处理的数据量，默认为1000^3=1G）
hive.exec.reducers.max（每个任务最大的reduce数，默认为999）
计算reducer数的公式很简单N=min(参数2，总输入数据量/参数1)
即，如果reduce的输入（map的输出）总大小不超过1G,那么只会有一个reduce任务；
如：select pt,count(1) from popt_tbaccountcopy_mes where pt = ‘2012-07-04′ group by pt;
/group/p_sdo_data/p_sdo_data_etl/pt/popt_tbaccountcopy_mes/pt=2012-07-04 总大小为9G多，因此这句有10个reduce
2.    调整reduce个数方法一：
调整hive.exec.reducers.bytes.per.reducer参数的值；
set hive.exec.reducers.bytes.per.reducer=500000000; （500M）
select pt,count(1) from popt_tbaccountcopy_mes where pt = ‘2012-07-04′ group by pt; 这次有20个reduce
3.    调整reduce个数方法二；
set mapred.reduce.tasks = 15;
select pt,count(1) from popt_tbaccountcopy_mes where pt = ‘2012-07-04′ group by pt;这次有15个reduce
4.    reduce个数并不是越多越好；
同map一样，启动和初始化reduce也会消耗时间和资源；
另外，有多少个reduce,就会有多少个输出文件，如果生成了很多个小文件，那么如果这些小文件作为下一个任务的输入，则也会出现小文件过多的问题；
5.    什么情况下只有一个reduce；
很多时候你会发现任务中不管数据量多大，不管你有没有设置调整reduce个数的参数，任务中一直都只有一个reduce任务；
其实只有一个reduce任务的情况，除了数据量小于hive.exec.reducers.bytes.per.reducer参数值的情况外，还有以下原因：
a)    没有group by的汇总，比如把select pt,count(1) from popt_tbaccountcopy_mes where pt = ‘2012-07-04′ group by pt; 写成 select count(1) from popt_tbaccountcopy_mes where pt = ‘2012-07-04′;
这点非常常见，希望大家尽量改写。
b)    用了Order by
c)    有笛卡尔积
通常这些情况下，除了找办法来变通和避免，我暂时没有什么好的办法，因为这些操作都是全局的，所以hadoop不得不用一个reduce去完成；
同样的，在设置reduce个数的时候也需要考虑这两个原则：使大数据量利用合适的reduce数；使单个reduce任务处理合适的数据量；

4.11 中间结果压缩
中间Lzo,最终Gzip
 
1.set mapred.output.compress = true;  
2.set mapred.output.compression.codec = org.apache.hadoop.io.compress.GzipCodec;  
3.set mapred.output.compression.type = BLOCK;  
4.  
5.set mapred.compress.map.output = true;  
6.set mapred.map.output.compression.codec = org.apache.hadoop.io.compress.LzoCodec;  
7.  
8.  
9.set hive.exec.compress.output = true;  
10.set hive.exec.compress.intermediate = true;  
11.set hive.intermediate.compression.codec = org.apache.hadoop.io.compress.LzoCodec;  
 
中间Lzo,最终结果不压缩
 
1.set mapred.output.compress = true;  
2.set mapred.output.compression.codec = org.apache.hadoop.io.compress.LzoCodec;  
3.set mapred.output.compression.type = BLOCK;  
4.  
5.set mapred.compress.map.output = true;  
6.set mapred.map.output.compression.codec = org.apache.hadoop.io.compress.LzoCodec;  
7.  
8.  
9.set hive.exec.compress.intermediate = true;  
10.set hive.intermediate.compression.codec = org.apache.hadoop.io.compress.LzoCodec;

4.12 其他
在MapReduce的WEB界面上，关注Hive Job执行的情况；
了解HQL -> MapReduce的过程；
HQL优化其实也是MapReduce的优化，作为分布式计算模型，其最核心的地方就是要确保每个节点上分布的数据均匀，才能最大程度发挥它的威力，否则，某一个不均匀的节点就会拖后腿。