HIVE企业级调优

1、Fetch抓取

Fetch抓取是指，Hive中对某些情况的查询可以不必使用MapReduce计算。例如：SELECT * FROM employees;在这种情况下，Hive可以简单地读取employee对应的存储目录下的文件，然后输出查询结果到控制台。

这种简单select肯定不会跑mapreduce。这个就叫fetch抓取。通常某些查询不走mapreduce，则实际上走的就是fetch抓取。

在hive-default.xml.template文件中hive.fetch.task.conversion默认是more（能用就用），老版本hive默认是minimal（能不用就不用），该属性修改为more以后，在全局查找、字段查找、limit查找等都不走mapreduce。

<property>     <name>hive.fetch.task.conversion</name>     <value>more</value>     <description>       Expects one of [none, minimal, more].       Some select queries can be converted to single FETCH task minimizing latency.       Currently the query should be single sourced not having any subquery and should not have any aggregations or distincts (which incurs RS), lateral views and joins.       0. none : disable hive.fetch.task.conversion       1. minimal : SELECT STAR, FILTER on partition columns, LIMIT only       2. more  : SELECT, FILTER, LIMIT only (support TABLESAMPLE and virtual columns)     </description> </property>

我们现在已经发现，查询的速度很慢。尤其是大表。但是建立了数据仓库，是给别人使用的，人家可不知道你的hive很慢，点击查询后就开始读秒了。

将来建表的时候，为了提高查询性能，可以提高范式，一张表弄的比较简单，通过多张表建立联系来执行。可是hive不是这样的，hive为了满足快速查询的条件，会提前把join的工作完成，建立宽表出来（列非常多的表），常用的查询效果在宽表中都能找到。查询的时候select就直接出来了，走fat operator就很快了。至于如何建宽表，这个是需要经验的。宽表会非常占存储空间，

当然more是最好的措施，已经把最好的措施写到代码中了。

案例实操：

1）把hive.fetch.task.conversion设置成none，然后执行最简单的查询语句，都会执行mapreduce程序。

hive (default)> set hive.fetch.task.conversion=none; hive (default)> select * from emp; hive (default)> select ename from emp; hive (default)> select ename from emp limit 3;

2）把hive.fetch.task.conversion设置成more，然后执行查询语句，如下查询方式都不会执行mapreduce程序。

hive (default)> set hive.fetch.task.conversion=more; hive (default)> select * from emp; hive (default)> select ename from emp; hive (default)> select ename from emp limit 3;

1. 1. 1. 本地模式

Hive Job本质上就是mapreduce Job。这个Job跑到yarn上。注意8088是yarn端口。

当然本地跑的肯定比yarn上跑的速度要快。如果一个文件数据就10多M，肯定本地跑的就够了，不需要在yarn上跑。

大多数的Hadoop Job是需要Hadoop提供的完整的可扩展性来处理大数据集的。不过，有时Hive的输入数据量是非常小的。在这种情况下，为查询触发执行任务消耗的时间可能会比实际job的执行时间要多的多。对于大多数这种情况，Hive可以通过本地模式在单台机器上处理所有的任务。对于小数据集，执行时间可以明显被缩短。

用户可以通过设置hive.exec.mode.local.auto的值为true，来让Hive在适当的时候自动启动这个优化，默认是false。

set hive.exec.mode.local.auto=true;  //开启本地mr //设置local mr的最大输入数据量，当输入数据量小于这个值时采用local  mr的方式，默认为134217728，即128M set hive.exec.mode.local.auto.inputbytes.max=50000000; //设置local mr的最大输入文件个数，当输入文件个数小于这个值时采用local mr的方式，默认为4 set hive.exec.mode.local.auto.input.files.max=10;

案例实操：

1）开启本地模式，并执行查询语句

hive (default)> set hive.exec.mode.local.auto=true; hive (default)> select * from emp cluster by deptno; Time taken: 1.328 seconds, Fetched: 14 row(s)

留意这个走了mapreduce，但是速度很快。因为是在本地进行查询。留意job_local。

2）关闭本地模式，并执行查询语句

hive (default)> set hive.exec.mode.local.auto=false; hive (default)> select * from emp cluster by deptno; Time taken: 20.09 seconds, Fetched: 14 row(s)

PS. 本地模式还是可以开一下的。数据量不大就可以有效的减少跟yarn之间的性能损耗。

2、表的优化

（1）小表、大表Join

之前的map join可以把小表保存到本地，用大表来join。

将key相对分散，并且数据量小的表放在join的左边，这样可以有效减少内存溢出错误发生的几率；再进一步，可以使用map join让小的维度表（1000条以下的记录条数）先进内存。在map端完成reduce。

实际测试发现：新版的hive已经对小表JOIN大表和大表JOIN小表进行了优化。小表放在左边和右边已经没有明显区别。

案例实操

1）．需求

测试大表JOIN小表和小表JOIN大表的效率

2）．建大表、小表和JOIN后表的语句

# 创建大表 create table bigtable(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by '\t'; # 创建小表 create table smalltable(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by '\t'; # 创建join后表的语句 create table jointable(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by '\t';

3）．分别向大表和小表中导入数据

hive (default)> load data local inpath '/export/servers/datas/bigtable' into table bigtable; hive (default)>load data local inpath '/export/servers/datas/smalltable' into table smalltable;

4）．关闭mapjoin功能（默认是打开的）

set hive.auto.convert.join = false;

5）．执行小表JOIN大表语句

insert overwrite table jointable select b.id, b.time, b.uid, b.keyword, b.url_rank, b.click_num, b.click_url from smalltable s left join bigtable  b on b.id = s.id;

Time taken: 35.921 seconds

No rows affected (44.456 seconds)

不开local是30多秒，开了是10多秒。可以关了试试

set hive.exec.mode.local.auto=false;

6）．执行大表JOIN小表语句

insert overwrite table jointable select b.id, b.time, b.uid, b.keyword, b.url_rank, b.click_num, b.click_url from bigtable  b left join smalltable  s on s.id = b.id;

Time taken: 34.196 seconds

No rows affected (26.287 seconds)

排除了误差，小表join大表和大表join小表其实差不多。就是因为已经优化过了。

（2） 大表Join大表

明显的情况是数据倾斜。数据经过shuffle后，需要重新分配处理的数据。Map是处理固定大小的数据，经过shuffle后数据分配方式就变了，按照key的hash值来分配。Mapjoin最大的应用场景，是应用的表是小表。2张表Join中有1张是小表，3张表join中有2张小表。大表join大表走不了map join，只能走reduce join。

假设我们join的字段是pid，就是key。这些key可能会出现什么情况。

1）．空KEY过滤

假设有10W条数据，前面9W条都非空，后面1W条全部都是null。这是很有可能出现的情况。比如有1W个不同的key，有些没有这个属性，就都是null。（幸福的人有各自的幸福，不幸的人有相同的不幸）。因此该字段null值比其他值多很多。于是在join的时候空key多很多，就会出现数据倾斜。一般情况下join的时候空key都没有用， 不需要join。因此在join之前可以先完成空key过滤。

有时join超时是因为某些key对应的数据太多，而相同key对应的数据都会发送到相同的reducer上，从而导致内存不够。此时我们应该仔细分析这些异常的key，很多情况下，这些key对应的数据是异常数据，我们需要在SQL语句中进行过滤。例如key对应的字段为空，操作如下：

案例实操

（1）配置历史服务器

配置mapred-site.xml（在hadoop目录下的）

<property>     <name>mapreduce.jobhistory.address</name>     <value>hadoop101:10020</value> </property> <property>     <name>mapreduce.jobhistory.webapp.address</name>     <value>hadoop101:19888</value> </property>

启动历史服务器

sbin/mr-jobhistory-daemon.sh start historyserver

这个可以看mapred执行的历史任务的。

查看jobhistory

http://hadoop101:19888/jobhistory

点击history

点successful那里。

（2）创建原始数据表、空id表、合并后数据表

// 创建原始表 create table ori(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by '\t'; // 创建空id表 create table nullidtable(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by '\t'; // 创建join后表的语句 create table jointable(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by '\t';

（3）分别加载原始数据和空id数据到对应表中

hive (default)> load data local inpath '/export/servers/datas/ori' into table ori; hive (default)> load data local inpath '/export/servers/datas/nullid' into table nullidtable;

接下来开始join。

（4）测试不过滤空id

hive (default)> insert overwrite table jointable select n.* from nullidtable n left join ori o on n.id = o.id;

Time taken: 42.038 seconds

Time taken: 37.284 seconds

（5）测试过滤空id

hive (default)> insert overwrite table jointable select n.* from (select * from nullidtable where id is not null ) n   left join ori o on n.id = o.id;

Time taken: 31.725 seconds

Time taken: 28.876 seconds

注意比前面快了一点。

可以通过http://hadoop101:8088/cluster 比较一下。

点击History进去看看。注意Reduce只有一个

这里没有分区，就不涉及数据倾斜的问题。当然这里是数据量太少的缘故。当数据量大，有多个Reduce并行的时候，会发现不过滤空key的话，时间比较长。因为所有空key都去往同一个reduce。

真正的空key过滤是为了防止数据倾斜的。数据倾斜的前提是要分区，我们这里根本没有分区，所以就没有数据倾斜。为什么变快了，是因为空key被过滤导致数据量少了。真正的过滤空key能看到并行几个reduce中有一个的时间变短了。

2）．空key转换

有时空key我们想要保留。不能随便过滤掉。但是如果都扔到一个reduce中就会很慢，怎么办？可以考虑给它赋随机值。这个值导致它分布到不同的reduce中，就可以避免数据倾斜了。

有时虽然某个key为空对应的数据很多，但是相应的数据不是异常数据，必须要包含在join的结果中，此时我们可以表a中key为空的字段赋一个随机的值，使得数据随机均匀地分不到不同的reducer上。例如：

案例实操：

不随机分布空null值：

（1）设置5个reduce个数

set mapreduce.job.reduces = 5;

（2）JOIN两张表

insert overwrite table jointable select n.* from nullidtable n left join ori b on n.id = b.id;

结果：如图所示，可以看出来，出现了数据倾斜，某些reducer的资源消耗远大于其他reducer。

其实也不太明显。有一个18s比其他的稍微多消耗一点时间。空key的hash值是0，所以应该是进入到0号的reduce。（第一个）

随机分布空null值

（1）设置5个reduce个数

set mapreduce.job.reduces = 5;

（2）JOIN两张表

insert overwrite table jointable select n.* from nullidtable n full join ori o on case when n.id is null then concat('hive', rand()) else n.id end = o.id;

拼接了一个随机数进去。这样拼起来还是join不到，因为空key谁都join不到。但此时的区别在于：这些key会均匀的分布到多个reduce当中。

结果：如图所示，可以看出来，消除了数据倾斜，负载均衡reducer的资源消耗

当然速度是慢了。但是慢的很均匀。

将来在处理数据倾斜问题的时候，可以什么方法都试试。哪个管用就用哪个。

（3）MapJoin（小表join大表）

MapJoin发生在小表join大表中。如果发生了MapJoin，会把小表加载到内存，然后join大表。

如果不指定MapJoin或者不符合MapJoin的条件，那么Hive解析器会将Join操作转换成Common Join，即：在Reduce阶段完成join。容易发生数据倾斜。可以用MapJoin把小表全部加载到内存在map端进行join，避免reducer处理。

1）．开启MapJoin参数设置

（1）设置自动选择Mapjoin

set hive.auto.convert.join = true; 默认为true

（2）大表小表的阈值设置（默认25M以下认为是小表）：

set hive.mapjoin.smalltable.filesize=25000000;

2）．MapJoin工作机制，如图所示

案例实操：

set hive.auto.convert.join = false; 默认为true

先来个小表JOIN大表

insert overwrite table jointable select b.id, b.time, b.uid, b.keyword, b.url_rank, b.click_num, b.click_url from smalltable s join bigtable  b on s.id = b.id;

现在是5个reduce大概50多秒。

set mapreduce.job.reduces=-1;

默认1个reduce大概30多秒。

（1）开启Mapjoin功能

set hive.auto.convert.join = true; 默认为true

（2）执行小表JOIN大表语句

insert overwrite table jointable select b.id, b.time, b.uid, b.keyword, b.url_rank, b.click_num, b.click_url from smalltable s join bigtable  b on s.id = b.id;

Time taken: 24.594 seconds

注意这里reduce数量是0。mapjoin大概20多秒搞定。走mapjoin肯定会快点的，因为它是在内存中操作的。

（3）执行大表JOIN小表语句

insert overwrite table jointable select b.id, b.time, b.uid, b.keyword, b.url_rank, b.click_num, b.click_url from bigtable  b join smalltable  s on s.id = b.id;

Time taken: 24.315 seconds

（4） Group By

经过shuffle，会出现数据重洗，然后把数据分到不同的reducer中。如果数据量很大的情况下，肯定要把数据分到不同的reducer中处理。但是有几个东西要慎用：比如DISTINCT。

DISTINCT，是去重。如果使用DISTINCT会把所有数据放到一个reducer中处理，如果要去重，可以考虑使用Group By。Group By会把不同的数据分到不同的reducer中。但Group By也有风险，当数据过大会出现数据倾斜。

默认情况下，Map阶段同一Key数据分发给一个reduce，当一个key数据过大时就倾斜了。

并不是所有的聚合操作都需要在Reduce端完成，很多聚合操作都可以先在Map端进行部分聚合，最后在Reduce端得出最终结果。

1）．开启Map端聚合参数设置

（1）是否在Map端进行聚合，默认为True

set hive.map.aggr = true

（2）在Map端进行聚合操作的条目数目

set hive.groupby.mapaggr.checkinterval = 100000

（3）有数据倾斜的时候进行负载均衡（默认是false）

set hive.groupby.skewindata = true

当选项设定为 true，生成的查询计划会有两个MR Job。第一个MR Job中，Map的输出结果会随机分布到Reduce中，每个Reduce做部分聚合操作，并输出结果，这样处理的结果是相同的Group By Key有可能被分发到不同的Reduce中，从而达到负载均衡的目的（就是先看看有没有数据倾斜，决定一下将来有数据倾斜怎么办）；第二个MR Job再根据预处理的数据结果按照Group By Key分布到Reduce中（这个过程可以保证相同的Group By Key被分布到同一个Reduce中）（这个才真正达到负载均衡的目的），最后完成最终的聚合操作。

我们直接执行一个任务，可能会出现数据倾斜。把上面几个选项打开后，会先预执行一遍。看看有没有数据倾斜，有则自动做一个负载均衡。然后再执行这个任务。

这个优化措施默认情况下是关闭的。因为数据量少了这东西不仅没有用，还会出现反效果，因为任务时间很短。

hive (default)> set mapreduce.job.reduces=5;

hive (default)> select deptno from emp group by deptno; Stage-Stage-1: Map: 1  Reduce: 5   Cumulative CPU: 23.68 sec   HDFS Read: 19987 HDFS Write: 9 SUCCESS Total MapReduce CPU Time Spent: 23 seconds 680 msec OK deptno 10 20 30

优化以后

hive (default)> set hive.groupby.skewindata = true;

hive (default)> select deptno from emp group by deptno; Stage-Stage-1: Map: 1  Reduce: 5   Cumulative CPU: 28.53 sec   HDFS Read: 18209 HDFS Write: 534 SUCCESS Stage-Stage-2: Map: 1  Reduce: 5   Cumulative CPU: 38.32 sec   HDFS Read: 15014 HDFS Write: 9 SUCCESS Total MapReduce CPU Time Spent: 1 minutes 6 seconds 850 msec OK deptno 10 20 30

70秒，反而更慢了。因为多执行了一个mapreduce（第一个mapreduce是预演，第二个mapreduce才是真正执行）。

但是在数据量大的时候，这个风险是值得的。但这个优化具体是正优化还是负优化，取决于实际的情景。如果数据倾斜非常的严重，这样是值得的；如果数据倾斜非常不严重，这样是非常不值得的。

（5）Count(Distinct) 去重统计

数据量小的时候无所谓，数据量大的情况下，由于COUNT DISTINCT的全聚合操作，即使设定了reduce task个数，set mapred.reduce.tasks=100；hive也只会启动一个reducer。，这就造成一个Reduce处理的数据量太大，导致整个Job很难完成，一般COUNT DISTINCT使用先GROUP BY再COUNT的方式替换：

案例实操

1）．创建一张大表

hive (default)> create table bigtable(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by '\t';

2）．加载数据

hive (default)> load data local inpath '/export/servers/datas/bigtable' into table  bigtable;

3）．设置5个reduce个数

set mapreduce.job.reduces = 5;

4）．执行去重id查询

hive (default)> select count(distinct id) from bigtable; Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 7.12 sec   HDFS Read: 120741990 HDFS Write: 7 SUCCESS Total MapReduce CPU Time Spent: 7 seconds 120 msec OK c0 100001 Time taken: 23.607 seconds, Fetched: 1 row(s)

留意刚才设置的reduce还是5各个。但是只要是去重，都会扔到1个reduce中去做。

这里数据量10万条还算少，好办。

5）．采用GROUP by去重id

hive (default)> select count(id) from (select id from bigtable group by id) a; Stage-Stage-1: Map: 1  Reduce: 5   Cumulative CPU: 17.53 sec   HDFS Read: 120752703 HDFS Write: 580 SUCCESS Stage-Stage-2: Map: 1  Reduce: 1   Cumulative CPU: 4.29 sec   HDFS Read: 9409 HDFS Write: 7 SUCCESS Total MapReduce CPU Time Spent: 21 seconds 820 msec OK _c0 100001 Time taken: 50.795 seconds, Fetched: 1 row(s)

可以先在里面进行一次group by，在外面再count(id)。这个比上面的肯定是慢的，但慢的原因是数据量太少了。

这里是5个reduce任务。去重的工作都会放到这个里面去完成。

时间慢很大，但是慢是因为数据量太小了。当数据量大的时候，全局去重一定要非常的谨慎，因为性能会非常低。

虽然会多用一个Job来完成，但在数据量大的情况下，这个绝对是值得的。

（6）笛卡尔积

尽量避免笛卡尔积，join的时候不加on条件，或者无效的on条件，Hive只能使用1个reducer来完成笛卡尔积。

将来在工作中会发现：工作中的hive一定会开严格模式，开了这个模式后有几个操作时做不了的，第一个就是笛卡儿积。另一个就是查询分区数据不带分区，也会告诉你：严格模式下，查询分区数据不能不带分区，为了防止新手手滑，导致整个组的任务都执行不了。

（7） 行列过滤

列处理：在SELECT中，只拿需要的列，如果有，尽量使用分区过滤，少用SELECT *。这样的效率比较低。因为将来主要是列存储，作为列存储，SELECT一列的效率比较高，因此用哪个字段就拿哪个字段。

行处理：在分区剪裁中，当使用外关联时，如果将副表的过滤条件写在Where后面，那么就会先全表关联，之后再过滤，比如：

案例实操：

1）．测试先关联两张表，再用where条件过滤

hive (default)> select o.id from bigtable b join ori o on o.id = b.id where o.id <= 10;

Time taken: 34.406 seconds, Fetched: 100 row(s)

由于这里是先join再执行where。这样是做一个全表扫描。

可以选择下面这样做。

2）．通过子查询后，再关联表

hive (default)> select b.id from bigtable b join (select id from ori where id <= 10 ) o on b.id = o.id;

Time taken: 30.058 seconds, Fetched: 100 row(s)

这里是先过滤，再join。好处是join的数据量少了（因为先过滤过了）。

（8）动态分区调整

关系型数据库中，对分区表Insert数据时候，数据库自动会根据分区字段的值，将数据插入到相应的分区中，Hive中也提供了类似的机制，即动态分区(Dynamic Partition)，只不过，使用Hive的动态分区，需要进行相应的配置。

1）．开启动态分区参数设置

（1）开启动态分区功能（默认true，开启）

set hive.exec.dynamic.partition=true;

（2）设置为非严格模式（动态分区的模式，默认strict，表示必须指定至少一个分区为静态分区，nonstrict模式表示允许所有的分区字段都可以使用动态分区。）

set hive.exec.dynamic.partition.mode=nonstrict;

（3）在所有执行MR的节点上，最大一共可以创建多少个动态分区。默认1000

set hive.exec.max.dynamic.partitions=1000;

（4）在每个执行MR的节点上，最大可以创建多少个动态分区。该参数需要根据实际的数据来设定。比如：源数据中包含了一年的数据，即day字段有365个值，那么该参数就需要设置成大于365，如果使用默认值100，则会报错。

set hive.exec.max.dynamic.partitions.pernode=366;

（5）整个MR Job中，最大可以创建多少个HDFS文件。默认100000

set hive.exec.max.created.files=100000;

（6）当有空分区生成时，是否抛出异常。一般不需要设置。默认false

set hive.error.on.empty.partition=false;

2）．案例实操

需求：将dept表中的数据按照地区（loc字段），插入到目标表dept_partition的相应分区中。

（1）创建目标分区表

hive (default)> create table dept_partition(id int, name string) partitioned by (location int) row format delimited fields terminated by '\t';

这里字段是两个，还有1个分区字段。文件是3个字段。因为文件中第3个字段会作为分区。

（2）设置动态分区

hive (default)> set hive.exec.dynamic.partition.mode = nonstrict;

设置成非严格模式，默认是严格模式（strict）。如果不设置成非严格模式，下面这句无法执行。

hive (default)> insert into table dept_partition partition(location) select deptno, dname, loc from dept;

这里从dept表中选出了3个字段，插入到了dept_partition表中。注意指明了partition(location)。所以dept的3个字段中，最后1个会变成location。

这个动态分区，好处是不用一个一个分区自己导。动态分区是全自动匹配的。

为什么要非严格模式，是因为这个很容易出问题，所以这个使用要谨慎。

（3）查看目标分区表的分区情况

hive (default)> show partitions dept_partition;

思考：目标分区表是如何匹配到分区字段的？

（9）分桶

（10）分区

详细请看后面

分区是某种情况下起到一定索引的作用。减小了数据扫描量，加快了查询的效率。