hive优化小记

一. sql优化

1. 大小表join:

   Hive假定查询中最后的一个表是大表。它会将其它表缓存起来，然后扫描最后那个表。因此通常需要将小表放前面，或者标记哪张表是大表：/*streamtable(table_name) */

   (1)将大表放后头

   (2)过滤掉为空key的值

   (3)空key转换,如果对应的数据不是异常数据，且必须要包含join的结果中,此时我们可以将表a中key为空的字段赋一个随机的值,将数据随机地分到不同的reduce上.

2. 大大表join：SMB Join

   1）创建第二张大表
   create table bigtable2(
       id bigint,
       t bigint,
       uid string,
       keyword string,
       url_rank int,
       click_num int,
       click_url string)
   row format delimited fields terminated by '\t';
   load data local inpath '/opt/module/data/bigtable' into table bigtable2;
   2）测试大表直接JOIN
   insert overwrite table jointable
   select b.id, b.t, b.uid, b.keyword, b.url_rank, b.click_num, b.click_url
   from bigtable a
   join bigtable2 b
   on a.id = b.id;
   测试结果：Time taken: 72.289 seconds
   insert overwrite table jointable
   select b.id, b.t, b.uid, b.keyword, b.url_rank, b.click_num, b.click_url
   from bigtable a
   join bigtable2 b
   on a.id = b.id;
   3）创建分通表1
   create table bigtable_buck1(
       id bigint,
       t bigint,
       uid string,
       keyword string,
       url_rank int,
       click_num int,
       click_url string)
   clustered by(id)
   sorted by(id)
   into 6 buckets
   row format delimited fields terminated by '\t';
   
   load data local inpath '/opt/module/data/bigtable' into table bigtable_buck1;
   4）创建分通表2，分桶数和第一张表的分桶数为倍数关系
   create table bigtable_buck2(
       id bigint,
       t bigint,
       uid string,
       keyword string,
       url_rank int,
       click_num int,
       click_url string)
   clustered by(id)
   sorted by(id)
   into 6 buckets
   row format delimited fields terminated by '\t';
   
   load data local inpath '/opt/module/data/bigtable' into table bigtable_buck2;
   5）设置参数
   set hive.optimize.bucketmapjoin = true;
   set hive.optimize.bucketmapjoin.sortedmerge = true;
   set hive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;
   6）测试 Time taken: 34.685 seconds
   insert overwrite table jointable
   select b.id, b.t, b.uid, b.keyword, b.url_rank, b.click_num, b.click_url
   from bigtable_buck1 s
   join bigtable_buck2 b
   on b.id = s.id;

3. 使用相同的连接键：当对3个或者更多个表进行join连接时，如果每个on子句都使用相同的连接键的话，那么只会产生一个MapReduce job。
4. 尽量尽早地过滤数据：减少每个阶段的数据量，对于分区表要加分区，同时只选择需要使用到的字段。
5. 尽量原子化操作：多个表关联时,避免包含复杂逻辑大sql(因为无法控制中间job)，最好分拆成小段，可以使用中间表来完成复杂的逻辑
6. 使用分，列裁剪，不要使用select  *  如果查询的是分区表，一定要记得带上分区条件
7. where条件写在子查询中：先过滤再关联（最好使用这种笨办法，虽然hive3.0自带谓词下推）
8. 关联条件写在on中，而不是where中：非主表谓词下推情况下，可以理解为where是全部执行完在reduce中进行过滤，on是在关联过程中filter
9. 数据量小时，用in代替join
10. 使用semi join替代in/exists

    示例：-- in / exists 实现
    select a.id, a.name from a where a.id in (select b.id from b);
    select a.id, a.name from a where exists (select id from b where a.id = b.id);
    可以使用join来改写：
    select a.id, a.name from a join b on a.id = b.id;
    应该转换成：
    -- left semi join 实现
    select a.id, a.name from a left semi join b on a.id = b.id;
    
    --semi join
    取出相交的那部分数据，与INNER JOIN有所不同：当b表中存在重复的数据(这里假设有两条)，当使用INNER JOIN 的时候，b表这两条重复数据都会参与关联；但是用LEFT SEMI JOIN时，当a表中的记录，在b表上产生符合条件之后就返回，不会再继续查找b表记录了，所以如果b表有重复，也不会产生重复的多条记录。

11. 浮点数的比较：double和Float格式比较，使用cast函数将数据转换为cast( as Float)
12. 避免笛卡尔积
13. 查看执行计划：EXPLAIN [EXTENDED | DEPENDENCY | AUTHORIZATION] query-sql

二. 用insert into替换union all：

如果union all的部分个数大于2，或者每个union部分数据量大，

应该拆成多个insert into 语句，实际测试过程中，执行时间能提升50%。(同时可以避免bdp无法读取的问题)

三. order by & sort by

order by : 对查询结果进行全局排序消耗时间长,需要set hive.mapred.mode=nostrict

sort by : 局部排序，并非全局有序，提高效率。

四.并行执行优化

Hive会将一个查询转化成一个或者多个阶段。这样的阶段可以是MapReduce阶段、抽样阶段、合并阶段、limit阶段。或者Hive执行过程中可能需要的其他阶段。默认情况下，Hive一次只会执行一个阶段。不过，某个特定的job可能包含众多的阶段，而这些阶段可能并非完全互相依赖的，也就是说有些阶段是可以并行执行的，这样可能使得整个job的执行时间缩短。如果有更多的阶段可以并行执行，那么job可能就越快完成。

通过设置参数hive.exec.parallel值为true，就可以开启并发执行。在共享集群中，需要注意下，如果job中并行阶段增多，那么集群利用率就会增加。

set hive.exec.parallel=true; //打开任务并行执行

set hive.exec.parallel.thread.number=16; //同一个sql允许最大并行度，默认为8。

set hive.exec.parallel=true

五.数据倾斜调优

表现：

任务进度长时间维持在99%（或100%），查看任务监控页面，发现只有少量（1个或几个）reduce子任务未完成。因为其处理的数据量和其他reduce差异过大。单一reduce的记录数与平均记录数差异过大，通常可能达到3倍甚至更多。 最长时长远大于平均时长。

常见的做法，通过参数调优：

是否在Map端进行聚合，默认为True
set hive.map.aggr = true;
在Map端进行聚合操作的条目数目
set hive.groupby.mapaggr.checkinterval = 100000;
有数据倾斜的时候进行负载均衡（默认是false）
set hive.groupby.skewindata = true;
当选项设定为 true，生成的查询计划会有两个MR Job。

当选项设定为true时，生成的查询计划有两个MapReduce任务。

在第一个MapReduce中，map的输出结果集合会随机分布到reduce中，每个reduce做部分聚合操作，并输出结果。

这样处理的结果是，相同的Group By Key有可能分发到不同的reduce中，从而达到负载均衡的目的；

第二个MapReduce任务再根据预处理的数据结果按照Group By Key分布到reduce中（这个过程可以保证相同的Group By Key分布到同一个reduce中），最后完成最终的聚合操作。

但是这个处理方案对于我们来说是个黑盒，无法把控。

数据倾斜潜在原因

（1）key分布不均匀

可以在key上加随机数，或者增加reduceTask数量

1）调整reduce个数方法一
（1）每个Reduce处理的数据量默认是256MB
set hive.exec.reducers.bytes.per.reducer = 256000000
（2）每个任务最大的reduce数，默认为1009
set hive.exec.reducers.max = 1009
（3）计算reducer数的公式
N=min(参数2，总输入数据量/参数1)(参数2 指的是上面的1009，参数1值得是256M)
2）调整reduce个数方法二
在hadoop的mapred-default.xml文件中修改
设置每个job的Reduce个数
set mapreduce.job.reduces = 15;

开启数据倾斜时负载均衡 set hive.groupby.skewindata = true;

（2）业务数据本身的特性(存在热点key)

join的数据输入比较大，且长尾是热点值导致的，可以对热点值和非热点值分别进行处理，再合并数据。

（3）建表时考虑不周

（4）某些SQL语句本身就有数据倾斜

（5）空key处理：

将为空的key转变为字符串加随机数或纯随机数，将因空值而造成倾斜的数据分布到多个Reducer.

注：对于异常值如果不需要的话，最好是提前在where条件里过滤掉，这样可以使计算量大大减少

（7）count(distinct ) 优化：替换为select * from tb group by...

情形：某特殊值过多

后果：处理此特殊值的reduce耗时；只有一个reduce任务

解决方式：count distinct时，将值为空的情况单独处理，比如可以直接过滤空值的行，在最后结果中加1。如果还有其他计算，需要进行group by ,可以先将空值的记录单独处理，再和其他计算结果进行union。

（8）不同数据类型关联产生数据倾斜： 关联字段数据类型不同导致的数据倾斜，此时可以利用cast进行类型转换，统一字段类型进行关联

（9）mapjoin(map端执行join）

启动方式一：(自动判断）

set.hive.auto.convert.join=true;

hive.mapjoin.smalltable.filesize 默认值是25mb

小表小于25mb自动启动mapjoin

启动方式二：(手动）

select /+mapjoin(A)/ f.a,f.b from A t join B f on (f.a=t.a)

mapjoin支持不等值条件

reduce join不支持在ON条件中不等值判断

    如果是由于小表数据量过大，无法进入内存，此时还可能出现内存溢出问题，进而导致任务报错

    解决方式：-- 关闭map端join

    set hive.auto.convert.join=false;

六.小文件优化

影响：

从HIVE角度来看的话呢，小文件越多，map的个数也会越多，每一个map都会开启一个JVM虚拟机，每个虚拟机都要创建任务，执行任务，这些流程都会造成大量的资源浪费，严重影响性能；

在HDFS中小文件的产生有三个地方，map输入，map输出，reduce输出，每个小文件约占150byte，如果小文件过多则会占用大量的内存。这样namenode内存容量严重制约了集群的发展。

解决方案

6.1、从小文件的产生途径解决：

1）使用sequencefile作为表存储形式，不要使用textfile，在一定程度上可以减少小文件；

2）减少reduce的个数（减少生成分区数量）；

3）少用动态分区，使用distribute by分区。

6.2、对已经存在的小文件做出的解决方案：

1）使用Hadoop archive把小文件进行归档

Hadoop Archive简称HAR，是一个高效地将小文件放入HDFS块中的文件存档工具，它能够将多个小文件打包成一个HAR文件，这样在减少namenode内存使用的同时，仍然允许对文件进行透明的访问

Hadoop的归档文件格式也是解决小文件问题的方式之一。而且Hive提供了原生支持：

#用来控制归档是否可用

set hive.archive.enabled=true;

#通知Hive在创建归档时是否可以设置父目录

set hive.archive.har.parentdir.settable=true;

#控制需要归档文件的大小

set har.partfile.size=1099511627776;

#使用以下命令进行归档

ALTER TABLE A ARCHIVE PARTITION(dt='2020-12-24', hr='12');

#对已归档的分区恢复为原文件

ALTER TABLE A UNARCHIVE PARTITION(dt='2020-12-24', hr='12');

2）使用hive自带的concatenate命令自动合并小文件

#对于非分区表

alter table A concatenate;

#对于分区表

alter table B partition(day=20201224) concatenate;

注意： 

1、concatenate 命令只支持 RCFILE 和 ORC 文件类型。 

2、使用concatenate命令合并小文件时不能指定合并后的文件数量，但可以多次执行该命令。 

3、当多次使用concatenate后文件数量不在变化，这个跟参数 mapreduce.input.fileinputformat.split.minsize=256mb 的设置有关，可设定每个文件的最小size。

-- 设置map输入的小文件合并

set mapred.max.split.size = 256000000;

-- 一个节点上split文件的大小(这个值决定了多个DataNode上的文件是否需要合并)

set mapred.min.splite.size.per.node = 100000000;

-- 一个交换机下split文件得大小(这个值决定了多个交换机上的文件是否需要合并)

set mapred.min.split.size.per.rack = 100000000;

-- 执行Map前进行小文件合并

set hive.input.format = org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;

 设置map输出和reduce输出进行合并的相关参数：

-- 设置map端输出进行合并，默认为true

set hive.merge.mapfiles = true;

-- 设置reduce端输出进行合并，默认为false

set hive.merge.mapredfiles = true;

-- 设置合并文件的大小

set hive.merge.size.per.task = 25610001000;

-- 当输出文件的平均大小小于该值时，启动一个独立的MapReduce任务进行文件merge

set hive.merge.smallfiles.avgsize = 16000000;

HIVE常用配置项

-- 设置作业名

set mapred.job.name = SOR_EVT_TBL_FB_CUST (${hivevar:statis_date});

-- 每个Map最大输入大小

set mapred.max.split.size = 300000000;

-- 一个节点上split的至少的大小

set mapred.min.split.size.per.node = 100000000;

-- 一个交换机下split的至少的大小

set mapred.min.split.size.per.rack = 100000000;

-- 每个Map最小输入大小

set mapred.min.split.size = 100000000;

-- 执行Map前进行小文件合并

set hive.input.format = org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;

-- hive自动根据sql，选择使用common join或者map join

-- 当这个值设置为true并且小表的大小

set hive.auto.convert.join = false;

-- 设置小表的大小

set hive.mapjoin.smalltable.filesize = 25000000;

-- 在只有map的作业结束时合并小文件，默认开启true

set hive.merge.mapfiles = true;

-- 在一个map/reduce作业结束后合并小文件，默认false

set hive.merge.mapredfiles = false;

-- 合并后每个文件的大小，默认256MB

set hive.merge.size.per.task = 300000000;

-- 当输出文件平均大小小于该值，启动新job合并文件

set hive.merge.smallfiles.avgsize=16000000;

七.Mapper与Reducer数量的优化

折衷：数量太大，会导致任务的启动、调度和运行过程的开销太大；数量太小，无法很好地利用集群的并发特性。

Hive会在接收到查询任务后，根据输入数据的大小评估所需要的reducer数量，但这个过程需要时间开销。默认的hive.exec.reducers.bytes.per.reducer是1GB，也可以改变这个值。

如何自己评估输入数据的大小？

[edward@etl02 ~]$ hadoop dfs -count /user/media6/fracture/ins/* |tail -4

1 82614608737 hdfs://.../user/media6/fracture/ins/hit_date=20120118

1 72742992546 hdfs://.../user/media6/fracture/ins/hit_date=20120119

1 172656878252 hdfs://.../user/media6/fracture/ins/hit_date=20120120

1 2 362657644hdfs://.../user/media6/fracture/ins/hit_date=20120121

注：当在执行Hadoop任务时，特别是hadoop-streaming脚本，如果只有mapper而没有reducer的话，可以将reducer数量设为0，这可以作为解决数据倾斜的一种方法！

启用压缩

# hive的查询结果输出是否进行压缩

set hive.exec.compress.output=true;

# MapReduce Job的结果输出是否使用压缩

set mapreduce.output.fileoutputformat.compress=true;

八.谓词下推优化

Hive中的 Predicate Pushdown 简称谓词下推，简而言之，就是在不影响结果的情况下，尽量将过滤条件提前或下推到join之前进行。谓词下推后，过滤条件在map端执行，减少了map端的输出，降低了数据在集群上传输的量，节约了集群的资源，也提升了任务的性能。

配置项为hive.optimize.ppd，默认为true。

案例实操：

1）打开谓词下推优化属性
hive (default)> set hive.optimize.ppd = true; #谓词下推，默认是true

2）查看先关联两张表，再用where条件过滤的执行计划
hive (default)> explain select o.id from bigtable b join bigtable o  on o.id = b.id where o.id <= 10;

3）查看子查询后，再关联表的执行计划
hive (default)> explain select b.id from bigtable b
join (select id from bigtable where id <= 10) o on b.id = o.id;

九.其他参数设置

设置jvm的重用

set jvm重用可以使得jvm实例在同一个job中重新使用N次

mapred.job.reuse.jvm.num.tasks=10;

关闭推测执行

压缩和文件存储格式，压缩使用snappy 文件存储格式使用ORC

中间压缩就是处理hive查询的多个job之间的数据，对于中间压缩，最好选择一个节省CPU耗时的压缩方式

set hive.exec.compress.intermediate=true;

set hive.intermediate.compression.codec=org.apache.hadoop.io.compress.SnappyCodec;

set hive.intermediate.compression.type=BLOCK;

hive查询最终的输出也可以压缩

set hive.exec.compress.output=true;

set mapred.output.compression.codec=org.apache.hadoop.io.compress.GzipCodec;

set mapred.output.compression.type=BLOCK;

动态的分区调整，就是以第一个表的分区规则,来对应第二个表的分区规格,将第一个表的所有分区,全部拷贝到第二个表中,第二个表在加载数据的时候,不需要指定分区了,直接用第一个表的分区即可.

(1)开启动态分区功能

(2)设置为非严格模式

(3)在所有执行mr的节点上,最大一共可以创建多少个动态分区

(4)在每个执行mr的节点上,最大可以创建多少个动态分区

(5)整个mr job中,最大可以创建多少个hdfs文件

(6)当有空分区生成是,是否抛出异常,一般不需要设置

十，其他优化

（1）多重模式

如果你碰到一堆SQL，并且这一堆SQL的模式还一样。都是从同一个表进行扫描，做不同的逻辑。有可优化的地方：如果有n条SQL，每个SQL执行都会扫描一次这张表。

insert .... select id,name,sex, age from student where age > 17;
insert .... select id,name,sex, age from student where age > 18;
insert .... select id,name,sex, age from student where age > 19;
-- 隐藏了一个问题：这种类型的SQL有多少个，那么最终。这张表就被全表扫描了多少次
insert int t_ptn partition(city=A). select id,name,sex, age from student where city= A;
insert int t_ptn partition(city=B). select id,name,sex, age from student where city= B;
insert int t_ptn partition(city=c). select id,name,sex, age from student where city= c;

修改为：
from student
insert int t_ptn partition(city=A) select id,name,sex, age where city= A
insert int t_ptn partition(city=B) select id,name,sex, age where city= B
如果一个 HQL 底层要执行 10 个 Job，那么能优化成 8 个一般来说，肯定能有所提高，多重插入就是一个非常实用的技能。一次读取，多次插入，有些场景是从一张表读取数据后，要多次利用。

(2) CBO优化

join的时候表的顺序的关系：前面的表都会被加载到内存中。后面的表进行磁盘扫描

select a.*, b.*, c.* from a join b on a.id = b.id join c on a.id = c.id;

Hive 自 0.14.0 开始，加入了一项 "Cost based Optimizer" 来对 HQL 执行计划进行优化，这个功能通过 "hive.cbo.enable" 来开启。在 Hive 1.1.0 之后，这个 feature 是默认开启的，它可以 自动优化 HQL中多个 Join 的顺序，并选择合适的 Join 算法。

CBO，成本优化器，代价最小的执行计划就是最好的执行计划。传统的数据库，成本优化器做出最优化的执行计划是依据统计信息来计算的。

Hive 的成本优化器也一样，Hive 在提供最终执行前，优化每个查询的执行逻辑和物理执行计划。这些优化工作是交给底层来完成的。根据查询成本执行进一步的优化，从而产生潜在的不同决策：如何排序连接，执行哪种类型的连接，并行度等等。

要使用基于成本的优化（也称为 CBO），请在查询开始设置以下参数：

set hive.cbo.enable=true;
set hive.compute.query.using.stats=true;
set hive.stats.fetch.column.stats=true;
set hive.stats.fetch.partition.stats=true;