Hive SQL 最常见优化点!

迷失技术de小猪 Hive 89

Hive的执行依赖于底层的MapReduce的作业,所以在执行效率上都是依赖于MR执行的一个效率。

所以,在学习了解MR原理是必要的,清楚了Hive底层的优化过程,会大大增加Hive的执行效率。Hive对于OLAP类型的应用有很大的局限性,它不适合需要立即返回查询结果的场景。然而,通过实施下面一系列的调优方法,Hive查询的性能会有大幅提高。

1. 启动压缩

压缩可以使磁盘上存储的数据量变小,例如,文本文件格式能够压缩40%甚至更高比例,这样可以通过降低I/O来提高查询速度。除非产生的数据用于外部系统,或者存在格式兼容性问题,建议总是启用压缩。

压缩与解压缩会消耗CPU资源,但Hive产生的MadReduce作业往往是I/O密集型的,因此CPU开销通常不是问题!

接下来,要想启用压缩,那么必须要知道所使用的hive版本所支持的压缩编码方式,下面的set命令可列出可用的编码器:(CDH5.13.1中的Hive)

hive> set io.compression.codecs ;
io.compression.codecs=
org.apache.hadoop.io.compress.DefaultCodec,
org.apache.hadoop.io.compress.GzipCodec,
org.apache.hadoop.io.compress.BZip2Codec,
org.apache.hadoop.io.compress.DeflateCodec,
org.apache.hadoop.io.compress.SnappyCodec,
org.apache.hadoop.io.compress.Lz4Codec,
com.hadoop.compression.lzo.LzoCodec,
com.hadoop.compression.lzo.LzopCodec

一个复杂的Hive查询在提交后,通常被转换为一系列中间阶段的MapReduce作业,Hive引擎将这些作业串联起来完成整个查询。可以将这些中间数据进行压缩。这里所说的中间数据指的是上一个MapReduce作业的输出,这些输出将被下一个MapReduce作业作为输入数据使用。我们可以在hive-site.xml文件中设置hive.exec.compress.intermediate属性以启用中间数据压缩

<property>
    <name>hive.exec.compress.intermediate</name>
    <value>true</value>
    <description>
        ThiscontrolswhetherintermediatefilesproducedbyHivebetween
        multiplemap-reducejobsarecompressed.Thecompressioncodecandotheroptions
aredeterminedfromhadoopconfigvariablesmapred.output.compress*
    </description>
</property>
<property>
    <name>hive.intermediate.compression.codec</name>
    <value>org.apache.hadoop.io.compress.SnappyCodec</value>
    <description/>
</property>
<property>
    <name>hive.intermediate.compression.type</name>
    <value>BLOCK</value>
    <description/>
</property>

也可以在Hive的客户端来进行设置这些属性

hive>set hive.exec.compress.intermediate=true;
hive>set hive.intermediate.compression.codec=org.apache.hadoop.io.compress.SnappyCodec;
hive>set hive.intermediate.compression.type=BLOCK;
hive>

当Hive将输出写入到表中时,输出内容同样可以进行压缩。我们可以设置hive.exec.compress.output属性启用最终输出压缩。

<property>
    <name>hive.exec.compress.output</name>
    <value>true</value>
    </description>
</property>
或者:
hive>set hive.exec.compress.output=true;
hive>set mapreduce.output.fileoutputformat.compress=true;
hive>set mapreduce.output.fileoutputformat.compress.codec=org.apache.hadoop.io.compress.SnappyCodec;
hive>set mapreduce.output.fileoutputformat.compress.type=BLOCK;
hive>

 

2. 优化连接

可以通过配置Map连接和倾斜连接的相关属性提升连接的查询公功能

2.1 自动Map连接

当连接一个大表和一个小表时,自动Map连接是一个非常有用的特性。如果启用了该特性,小表将保存在每个节点的本地缓        存中,并在Map阶段与大表进行连接。开启自动Map连接提供了两个好处。首先,将小表装进缓存将节省每个数据节点上的读取时间。其次,它避免了Hive查询中的倾斜连接,因为每个数据块的连接操作已经在Map阶段完成了。设置下面的属性启用自动Map连接属性。

<property>
    <name>hive.auto.convert.join</name>
    <value>true</value>
</property>
<property>
    <name>hive.auto.convert.join.noconditionaltask</name> 
    <value>true</value>
</property>
<property>
    <name>hive.auto.convert.join.noconditionaltask.size</name> 
    <value>10000000</value> 
</property> 
<property>
    <name>hive.auto.convert.join.use.nonstaged</name>
    <value>true</value>
</property> 

-> hive.auto.convert.join:是否启用基于输入文件的大小,将普通连接转化为Map连接的优化机制。
-> hive.auto.convert.join.noconditionaltask:是否启用基于输入文件的大小,将普通连接转化为Map连接的优化机制。假设参与连接的表(或分区)有N个,如果打开这个参数,并且有N-1个表(或分区)的大小总和小于hive.auto.convert.join.noconditionaltask.size参数指定的值,那么会直接将连接转为Map连接。
-> hive.auto.convert.join.noconditionaltask.size:如果hive.auto.convert.join.noconditionaltask是关闭的,则本参数不起作用。否则,如果参与连接的N个表(或分区)中的N-1个的总大小小于这个参数的值,则直接将连接转为Map连接。默认值为10MB。
->hive.auto.convert.join.use.nonstaged:对于条件连接,如果从一个小的输入流可以直接应用于join操作而不需要过滤或者投影,那么不需要通过MapReduce的本地任务在分布式缓存中预存。当前该参数在vectorization或tez执行引擎中不工作。

2.2   倾斜连接

两个大表连接时,会先基于连接键分别对两个表进行排序,然后连接它们。Mapper将特定键值的所有行发送给同一个Reducer。例如,表A的id列有1、2、3、4四个值,表B的id列有1、2、3三个值。查询语句如下:
        
        select A.id
        from A join B on A.id = B.id

一系列Mapper读取表中的数据并基于键值发送给Reducer。如id=1行进入Reducer R1,id=2的行进入Reducer R2等。这些Reducer产生A、B的交集并输出。Reducer R4只从A获取行,不会产生查询结果。

现在假设id=1的数据行是高度倾斜的,则R2和R3会很快完成,而R1需要很长时间,将成为整个查询的瓶颈。配置倾斜连接的相关属性可以有效优化倾斜连接。

<property>
    <name>hive.optimize.skewjoin</name>
    <value>true</value>
</property>
<property>
    <name>hive.skewjoin.key</name>
    <value>100000</value>
</property>
<property>
    <name>hive.skewjoin.mapjoin.map.tasks</name>
    <value>10000</value>
</property>
<property>
    <name>hive.skewjoin.mapjoin.min.split</name>
    <value>33554432</value>
</property>

-> hive.optimize.skewjoin:是否为连接表中的倾斜键创建单独的执行计划。它基于存储在元数据中的倾斜键。在编译时,Hive为倾斜键和其他键值生成各自的查询计划。
-> hive.skewjoin.key:决定如何确定连接中的倾斜键。在连接操作中,如果同一键值所对应的数据行数超过该参数值,则认为该键是一个倾斜连接键。
-> hive.skewjoin.mapjoin.map.tasks:指定倾斜连接中,用于Map连接作业的任务数。该参数应该与hive.skewjoin.mapjoin.min.split一起使用,执行细粒度的控制。
-> hive.skewjoin.mapjoin.min.split:通过指定最小split的大小,确定Map连接作业的任务数。该参数应该与hive.skewjoin.mapjoin.map.tasks一起使用,执行细粒度的控制。

3. 避免使用order by全局排序

Hive中使用order by子句实现全局排序。order by只用一个Reducer产生结果,对于大数据集,这种做法效率很低。如果不需要全局有序,则可以使用sort by子句,该子句为每个reducer生成一个排好序的文件。如果需要控制一个特定数据行流向哪个reducer,可以使用distribute by子句

属于一个dept的数据会分配到同一个reducer进行处理,同一个dept的所有记录按照id、name列排序。最终的结果集是全局有序的。在10.3节还会讨论Hive中几种不同的数据排序方法。

注意:这里要区别出来cluster by 和 distribute by … sort by 的区别!!!

 

4. 启动Tez执行引擎

使用Tez执行引擎代替传统的MapReduce引擎会大幅提升Hive查询的性能。在安装好Tez后,配置hive.execution.engine属性指定执行引擎。

<property>
    <name>hive.execution.engine</name>
    <value>tez</value>
    <description></description>
</property>

 

5. 优化limit操作

默认时limit操作仍然会执行整个查询,然后返回限定的行数。在有些情况下这种处理方式很浪费,因此可以通过设置下面的属性避免此行为。

<property>
    <name>hive.limit.optimize.enable</name>
    <value>true</value>
</property>
<property>
    <name>hive.limit.row.max.size</name>
    <value>100000</value>
</property>
<property>
    <name>hive.limit.optimize.limit.file</name>
    <value>10</value>
</property>
<property>
    <name>hive.limit.optimize.fetch.max</name>
    <value>50000</value>
</property>

说明:
-> hive.limit.optimize.enable:是否启用limit优化。当使用limit语句时,对源数据进行抽样。
-> hive.limit.row.max.size:在使用limit做数据的子集查询时保证的最小行数据量。
-> hive.limit.optimize.limit.file:在使用limit做数据子集查询时,采样的最大文件数。
-> hive.limit.optimize.fetch.max:使用简单limit数据抽样时,允许的最大行数。

 

6. 启用并行执行

每条HiveQL语句都被转化成一个或多个执行阶段,可能是一个MapReduce阶段、采样阶段、归并阶段、限制阶段等。默认时,Hive在任意时刻只能执行其中一个阶段。如果组成一个特定作业的多个执行阶段是彼此独立的,那么它们可以并行执行,从而整个作业得以更快完成。通过设置下面的属性启用并行执行。

<property>
    <name>hive.exec.parallel</name>
    <value>true</value>
</property>
<property>
    <name>hive.exec.parallel.thread.number</name>
    <value>8</value>
</property>
-> hive.exec.parallel:是否并行执行作业。
-> hive.exec.parallel.thread.number:最多可以并行执行的作业数。

 

7. 启用严格模式

Hive提供了一个严格模式,可以防止用户执行那些可能产生负面影响的查询。通过设置下面的属性启用MapReduce严格模式。

<property>
    <name>hive.mapred.mode</name>
    <value>strict</value>
</property>

严格模式禁止3种类型的查询:
-> 对于分区表,where子句中不包含分区字段过滤条件的查询语句不允许执行
-> 对于使用了order by子句的查询,要求必须使用limit子句,否则不允许执行
-> 限制笛卡尔积查询
使用单一Reduce执行多个group by
        通过为group by操作开启单一reduce任务属性,可以将一个查询中的多个group by操作联合在一起发送给单一MapReduce作业。

<property>
    <name>hive.multigroupby.singlereducer</name>
    <value>true</value>
    <description></description>
</property>

 

 

 

8. 控制并行Reduce任务

 Hive通过将查询划分成一个或多个MapReduce任务达到并行的目的。确定最佳的mapper个数和reducer个数取决于多个变量,例如输入的数据量以及对这些数据执行的操作类型等。如果有太多的mapper或reducer任务,会导致启动、调度和运行作业过程中产生过多的开销,而如果设置的数量太少,那么就可能没有充分利用好集群内在的并行性。对于一个Hive查询,可以设置下面的属性来控制并行reduce任务的个数

<property>
    <name>hive.exec.reducers.bytes.per.reducer</name>
    <value>256000000</value>
</property>
<property>
    <name>hive.exec.reducers.max</name>
    <value>1009</value>
</property>

-> hive.exec.reducers.bytes.per.reducer:每个reducer的字节数,默认值为256MB。Hive是按照输入的数据量大小来确定reducer个数的。例如,如果输入的数据是1GB,将使用4个reducer。
-> hive.exec.reducers.max:将会使用的最大reducer个数。

 

注意:不断清晰MR流程,以及HIVE转化为MR的过程,才能更好的去优化!

回复

我来回复
  • 暂无回复内容

免费咨询
免费咨询
扫码关注
扫码关注
联系站长

站长Johngo!

大数据和算法重度研究者!

持续产出大数据、算法、LeetCode干货,以及业界好资源!

2022012703491714

微信来撩,免费咨询:xiaozhu_tec

分享本页
返回顶部