数据仓库技术及应用（Hive调优）

一、Hive性能调优使用工具

HiveSQL是一种声明式语言，用户提交的交互式查询，Hive会将其转换成MR任务。

常用调优工具

1.EXPLAIN

Hive提供EXPLAIN命令显示查询语句的执行计划，通过显示信息可以了解Hive如何将查询转换为MR。

语法及释义：

#EXTENDED:提供执行计划关于操作的额外信息，比如文件路径
#DEPENDENCY:提供JSON格式的输出，包括查询所依赖的表和分区的列表
#AUTHORIZATION：提供所有需要授权的实体，包括查询的输入输出和认证失败
EXPLAIN [EXTENDED|DEPENDENCY|AUTHORIZATION] query

**EXPLAIN输出包括三个部分 **

• 查询的抽象语法树
• 执行计划中不同stage之间的依赖关系
• 每个stage的描述信息，主要显示了操作与数据的对应关系

2.ANALYZE

ANALYZE关键字可以搜集表的数值统计信息，用于执行计划选择的参考。

语法：

# noscan参数表示不会扫描文件数据，统计速度会更快
ANALYZE TABLE tablename [PARTITION(col1[=val1], col2[=val2],…)]
COMPUTE STATISTICS [noscan]

统计字段含义
统计字段说明numPartitions分区个数numFiles文件个数totalSizeHDFS存储空间大小rawDataSize原始数据大小（未压缩）numRows行数

二、优化MapTask和ReduceTask个数

1.MapTask数量对Hive的影响

• MapTask数量过大，会造成Map阶段输出文件太小，产生过多小文件，对HDFS造成压力，创建Map开销大；
• MapTask数量太小，文件处理或查询并行度低，Job执行时间过长，性能不高。

2.设置MapTask数量

• 减少MapTask数量可以通过合并目录下的文件实现；
• 增加MapTask数量可以通过增加其依赖的前一个Job的Reduce个数，产生更多的文件来增加。

ReduceTask数量的设定极大影响任务执行效率 。

默认情况，Hive会计算确定Reduce个数。

# 每个Reduce任务处理的数据量，默认是1G
hive.exec.reducer.bytes.per.reducer
# 每个任务最大的Reduce个数，默认是999
hive.exec.reducer.max

# 用户自定义调整Reduce个数
hive.exec.reducer.bytes.per.reducer

三、Hive Job优化

1.使用本地模式运行任务

• 适合待处理的数据很小的情况
• 启动分布式数据处理是一种开销
• 完全启动分布式模式的时间较长

2.设置本地模式运行任务

# 临时设置方式。注意，作业必须满足条件才能本地模式运行，作业总输# 入大小必须低于2中设置的值，#Map任务总数必须小于属性3中设置的# 值，Reduce任务总数必须是1个或者0个
1. hive>set hive.exec.mode.local.auto=true;--default false
2. hive>set hive.exec.mode.local.auto.inputbytes.max=50000000;
3. hive>set hive.exec.mode.local.auto.input.files.max=5;--default 4

3.JVM重用

• 适合轻量级的作业
• 启动JVM的过程是一种开销
• 通过共享JVM来重用JVM以串行方式运行MR Job

**设置JVM重用运行MR Job **

#设置需在hive-site.xml中添加属性设置，该默认值是1，JVM重用只 #能在同一个job中的Map和Reduce任务起作用，对于不同Job的任务，仍然是运行在独立的JVM。
hive>set mapred.job.reuse.jvm.num.tasks=5;

4.并行执行

• Hive会将一个查询转换成一个或多个stage，按顺序执行；
• 将这些stage并行执行可以缩短整个Job的执行时间。

设置并行执行

<property>
<name>hive.exec.parallel</name>
<value>true</value>
</property>
<property>
<name>hive.exec.parallel.thread.number</name>
<value>16</value>
</property>

5.推测执行

• 适合负载不均衡，同一Job的不同Task运行速度不同的情况；
• Hadoop采用推测执行机制，将执行慢的Task加入黑名单，并为这样的Task启动备份任务，选择先完成的计算结果。

**设置推测执行 **

#需在$HADOOP_HOME/conf/mapred-site.xml中添加属性设置
mapred.map.tasks.speculative.execution设置为true
mapred.reduce.tasks.speculative.execution设置为true

6.合并小文件

• Hive实际存储数据是在HDFS上，小文件过多将影响性能
• 通过合并Map和Reduce的结果文件可避免这种影响

**设置合并小文件 **

#是否合并Map输出文件
hive.merge.mapfile属性设置为true
#是否合并Reduce输出文件
hive.merge.mapredfiles属性设置为true
#合并文件的大小
hive.merge.size.per.task属性设置为256*1000*1000

四、Hive Query优化

1.列裁剪

• Hive查询时只查询需要使用的列，效率会更高
• 列裁剪相关属性为hive.optimize.cp,默认值为true

2.分区裁剪

当有需求对目标表的某个分区数据进行查询时

# 分区裁剪的配置
<property>
    <name>hive.optimize.pruner</name>
    <value>true</value>
</property>

3.JOIN

• 将小表或子查询放在Join操作符左边，可以减少内存溢出错误发生的概率
• 对小表连接时可使用MapJoin，在Map阶段完成Join操作，避免发生shuffle，提高Join操作的效率

**Join优化设置 **

#自动使用MapJoin优化，默认为true
hive.auto.convert.join  
#通过该属性这是使用MAPJOIN优化表的大小，如果表的大小小于该设置值就会被加载进内存中，默认值是250M
hive.mapjoin.smalltable.filesize

4.GROUP BY操作

在Map端进行部分聚合操作，在Reduce端得出最终结果

设定在Map端进行Group by操作

<property>
    <name>hive.map.aggr</name>
    <value>true</value>
</property>
#设定map端进行聚合的条目数
<property>
    <name>hive.groupby.mapaggr.checkinierval</name>
    <value>100000</value>
</property>

五、设置压缩

1.数据压缩

• MR性能瓶颈主要在于网络I/O和磁盘I/O
• 采用数据压缩是减少数据量、提高性能的很好的方式

**常用数据压缩方法 **
压缩算法是否支持拆分Hive自带压缩率压缩/解压缩速度gzip否是很高比较快lzo是是比较高很快snappy否是比较高很快bizp2是否最高慢

2.配置压缩

中间数据压缩，在MR的shuffle阶段对Map端产生的中间结果数据进行压缩

#激活hive中间数据压缩，默认为False
hive.exec.compress.intermediate属性设置为true

最终数据压缩，可控制对最终输出内容的压缩

#激活hive最终数据压缩，默认为False
hive.compress.output属性设置为true

六、SQL本身的优化

1. 只select需要的列，避免select *
2. where条件写在子查询中，先过滤再关联
3. 关联条件写在on中，而不是where中
4. 数据量大时，用group by代替count distinct
5. 数据量小时，用in代替join
6. 避免笛卡尔积
7. join时大表放后面，使用相同的连接键
8. 严格格式

Hive.mapred.mode，分 nonstrict，strict，默认是nonstrict，
如果设置为strict，对三种情况限制：
（1）分区表必须加分区。
（2）order by 必须使用limit
（3）存在笛卡尔积