Hive优化

最新推荐文章于 2022-06-18 11:46:19 发布

小箱

最新推荐文章于 2022-06-18 11:46:19 发布

阅读量236

点赞数

分类专栏： Hive

Hive 专栏收录该内容

18 篇文章 1 订阅

订阅专栏

1. hive group by distinct区别以及性能比较

https://blog.csdn.net/xiaoshunzi111/article/details/68484426

2. 用insert into替换union all

3. order by & sort by

执行计划是什么

执行计划代表HiveSQL会转化成怎么样的MapReduce作业。也是优化HiveSQL根本依据。

HiveSQL的优化本质是对MapReduce作业的优化。不管使用的引擎是mr、tez还是spark都是一样的。

执行计划初步解析

抽象语法树AST（Abstract Syntax Tree）

是源代码的抽象语法结构的树状表现形式。

Hive使用Antlr实现SQL的词法和语法解析生成AST。

QueryBlock

QueryBlock是一条SQL最基本的组成单元，包括三个部分：输入源，计算过程，输出。简单来讲一个QueryBlock就是一个子查询。

Operator

Hive最终生成的MapReduce任务，Map阶段和Reduce阶段均由OperatorTree组成。逻辑操作符，就是在Map阶段或者Reduce阶段完成单一特定的操作。

基本的操作符包括TableScanOperator，FilterOperator，JoinOperator，GroupByOperator，ReduceOutputOperator，FileOutputOperator。

Operator在Map Reduce阶段之间的数据传递都是一个流式的过程。每一个Operator对一行数据完成操作后之后将数据传递给childOperator计算。

逻辑层优化器、物理层优化器

SimpleFetchOptimizer、MapJoinProcessor、GroupByOptimizer、PredicatePushDown等等

MapReduce任务生成步骤

AST>>QueryBlock>>Operator Tree>>MapReduce Job

查看执行计划：

EXPLAIN [EXTENDED|DEPENDENCY|AUTHORIZATION] query

Explain select
    b.pid,
    count(distinct a.wid)
from default.saas_uc_user_info b
join d_extra.dim_shp_saas_pid_info_ext a
on a.id=b.pid
join default.t_march_merchant_info c
on b.wid=c.wid
group by b.pid

数据倾斜

map/reduce程序执行时，reduce节点大部分执行完毕，但是有一个或者几个reduce节点运行很慢，导致整个程序的处理时间很长，这是因为某一个key的条数比其他key多很多（有时是百倍或者千倍之多），这条key所在的reduce节点所处理的数据量比其他节点就大很多，从而导致某几个节点迟迟运行不完，此称之为数据倾斜。

特殊情况：map端“数据倾斜”

num_map_tasks = max[${mapred.min.split.size},min(${dfs.block.size},${mapred.max.split.size})]

通过调整max可以起到调整map数的作用，减小max可以增加map数，增大max可以减少map数。需要提醒的是，直接调整mapred.map.tasks这个参数是没有效果的。

数据量大不是问题，数据倾斜是个问题。

倾斜的原因：

使map的输出数据更均匀的分布到reduce中去，是我们的最终目标。由于Hash算法的局限性，按key Hash会或多或少的造成数据倾斜。大量经验表明数据倾斜的原因是人为的建表疏忽或业务逻辑可以规避的。

解决思路:

Hive的执行是分阶段的，map处理数据量的差异取决于上一个stage的reduce输出，所以如何将数据均匀的分配到各个reduce中，就是解决数据倾斜的根本。

典型的业务场景

NULL值产生的数据倾斜（或其他默认业务值。比如常见的空值,0,1,-1,-99等业务默认值。）

例：如日志中，常会有信息丢失的问题，比如电商日志中的store_id，如果取其中的 store_id 和门店表中的store_id 关联，会碰到数据倾斜的问题。

解决办法：

1.写成两段union all在一起

2.给空值做转换join条件写成

A left join B
On
case when A.store_id= null then concat('hive',rand()) else A.store_id end
= 
B.store_id

园园的例子：

drop table temp.qyy_test_1 ;

create table temp.qyy_test_1 as

select

logdate , cuid, max(wid) wid

from datacleanup.mdpath

where logdate >= '2017-05-01' and logdate <= '2017-05-20'

group by logdate, cuid;

drop table temp.qyy_test_2 ;

create table temp.qyy_test_2 as

select

t1.logdate,

t1.cuid,

t2.wid

from temp.qyy_test_1 t1

left join d_extra.dm_user_wid_msg t2 on ( t1.wid = t2.wid );

drop table temp.qyy_test_3 ;

create table temp.qyy_test_3 as

select

t1.logdate,

t1.cuid,

t2.wid

from temp.qyy_test_1 t1

left join d_extra.dm_user_wid_msg t2

on ( if (t1.wid > 0,t1.wid, cast(ceiling(rand() * -65535) as bigint)) = t2.wid ) ;

结论：

如果关联不上的KEY数据太多，可使用rand()将这种数据均匀分布到各个reducer中。

count distinct

set hive.map.aggr=true;

sum,count,max,min等UDAF，不怕数据倾斜问题,hadoop在map端的汇总合并优化，使数据倾斜不成问题。

此语句非常容易产生数据倾斜，因为其执行的MapReduce是以GroupBy分组,再对distinct列排序,然后输出交给Reduce.

问题就在这里，相比其它GroupBy聚合统计，count(distinct)少一个关键步骤(Map的预计算，在Map端提前做一次聚合再将聚合结果交给Reduce)

当Map直接将全部数据交给Reduce后，如果数据的分组本身不平衡(存在大量值为NULL或空的记录，比如及格,80%以上及格数据)，会造成某一些Reduce处理太过多的数据。

解决方式：

如果是仅计算count distinct，可以不用处理，直接过滤，在最后结果中加1；或者拆成count(1) from (select distinct)。

如果还有其他计算需要进行group by，可以先将值为空的记录单独处理，再和其他计算结果进行union all。

set hive.groupby.skewindata=true

只能支持一个distinct。

生成的查询计划有两个MapReduce 任务。

eg: 园园的例子

使用Distinct会将所有order_no都shuffle到一个reducer中，这就导致了数据倾斜。

而Group By会启动457个reducer，将数据均匀的分布到多个CPU上执行。这样速度就会快很多。

group by

Select

wid , count(*)

From datacleanup.mdpath

Where logdate = ‘2017-05-01’

Group by wid ;

以上脚本也会产生数据倾斜，因为wid 为 null or -1的记录很多。

解决办法：

set hive.groupby.skewindata=true;

set hive.groupby.mapaggr.checkinterval=100000 ;

当某个KEY的数据量超过这个值时， hive就会产生一个新的reducer去处理。

不同数据类型关联产生数据倾斜

场景：用户表中user_id字段为int，log表中user_id字段既有string数据也有int数据。当按照user_id进行两个表的Join操作时，默认的Hash操作会按int型的id来进行分配，这样会导致所有string类型id的记录都分配到一个Reducer中。

解决方法：把数字类型转换成字符串类型。

Join操作产生数据倾斜

大表和小表Join

产生原因：Hive在进行join时，按照join的key进行分发，而在join左边的表的数据会首先读入内存，如果左边表的key相对分散，读入内存的数据会比较小，join任务执行会比较快；而如果左边的表key比较集中，而这张表的数据量很大（也容易发生OOM错误），那么数据倾斜就会比较严重，而如果这张表是小表，则还是应该把这张表放在join左边。

解决方式：使用mapjoin。

此Join 操作在 Map 阶段完成，不再需要Reduce，也就不需要经过Shuffle过程，从而能在一定程度上节省资源提高JOIN效率。

可以变相支持不等连接。

在0.7.0版本之前：需要在sql中使用 /*+ MAPJOIN(smallTable) */ ；

例：SELECT /*+ MAPJOIN(b) */

a.key, a.value

FROM a JOIN b

ON a.key = b.key;

在0.7.0版本之后：可以set hive.auto.convert.join=true;

其他相关参数：

set hive.mapjoin.smalltable.filesize=100000000; 小表最大大小

set hive.auto.convert.join.noconditionaltask=true; 合并多个MJ为一个

set hive.auto.convert.join.noconditionaltask.size=894435328; 多个MJ的小表总大小，如总大小小于此值则合并。

小技巧

1.只取需要的字段

2.表先过滤

总结：

1.数据量大不是问题，数据倾斜是个问题。

2. jobs数比较多的作业运行效率相对比较低，比如即使有几百行的表，如果多次关联多次汇总，产生十几个jobs，耗时很长。原因是map reduce作业初始化的时间是比较长的。（tez做的事情）

3. sum,count,max,min等UDAF，不怕数据倾斜问题,hadoop在map端的汇总合并优化，使数据倾斜不成问题。

4.数据倾斜是导致效率大幅降低的主要原因，可以采用多一次 Map/Reduce 的方法，避免倾斜。

----Credit to Peiqing

优化意识及思路

MR流程图

WordCount

方法论：

1.收集数据：explain查看执行计划

2.定位瓶颈：
1）查看执行日志，定位哪个Stage（Job）时间长
2）查看Job日志，定位Map阶段慢还是Reduce阶段慢

3.诊断问题：
1）资源不足pending
2）数据倾斜
3）reduce数太少
4）...

4.解决瓶颈：对症下药，蛇打七寸

如何优化？

一个Hive查询生成多个Map Reduce Job，一个Map Reduce Job又有Map,Spill,Shuffle,Sort,Reduce等多个阶段，所以针对Hive查询的优化可以大致分为针对MR中单个步骤的优化（其中又会有细分），针对MR全局的优化，和针对整个查询（多MR Job）的优化。。。

Map阶段的优化

Map阶段的优化，主要是确定合适的Map数

num_Map_tasks = ${input.size} /max[${Mapred.min.split.size},min(${dfs.block.size},${Mapred.max.split.size})]

mapred.min.split.size 指的是数据的最小分割单元大小。

mapred.max.split.size 指的是数据的最大分割单元大小。

dfs.block.size 指的是HDFS设置的数据块大小。

需要提醒的是，直接调整 mapred.map.tasks 这个参数是没有效果的。

Reduce阶段的优化

Reduce阶段的优化，主要是确定合适的Reduce数

与Map优化不同的是，Reduce优化时，可以直接设置mapred.reduce.tasks参数从而直接指定Reduce的个数。当然直接指定Reduce个数虽然比较方便，但是不利于自动扩展。

num_Reduce_tasks = min[${hive.exec.reducers.max},(${input.size} / ${ hive.exec.reducers.bytes.per.reducer})]

hive.exec.reducers.max --reduce数的上限值

Map与Reduce之间的优化

Spill 与 Sort

io.sort.mb

io.sort.factor

Copy

mapred.reduce.slowstart.completed.maps

tasktracker.http.threads

mapred.reduce.parallel.copies

文件格式的优化

文件格式

1. 压缩比例

2. 查询时间

Textfile

Rcfile

parquet

Orcfile

小文件合并

https://blog.csdn.net/yfkiss/article/details/8590486

1.Map输入合并小文件

2.输出合并

执行模式

本地模式（数据量小时）

分布模式（数据量大时）

//开启本地模式

set hive.exec.mode.local.auto = true

//job的最大map数

hive.exec.mode.local.auto.tasks.max ？

//job的最大输入数据量, 一般 = dfs.block.size

hive.exec.mode.local.auto.inputbytes.max

JVM重用

JVM重用正常情况下，MapReduce启动的JVM在完成一个task之后就退出了，但是如果任务花费时间很短，又要多次启动JVM的情况下（比如对很大数据量进行计数操作），JVM的启动时间就会变成一个比较大的overhead。

在这种情况下，可以使用jvm重用的参数： mapred.job.reuse.jvm.num.tasks = 5;

他的作用是让一个jvm运行多次任务之后再退出。这样一来也能节约不少JVM启动时间。

三大经典join算法

Nested join

Hash join

Sort merge join

Join算法

处理分布式join，一般有两种方法:

replication join：把其中一个表复制到所有节点，这样另一个表在每个节点上面的分片就可以跟这个完整的表join了；

repartition join：把两份数据按照join key进行hash重分布，让每个节点处理hash值相同的join key数据，也就是做局部的join。

这两种方式在M/R Job中分别对应了Map side join和Reduce side join

Map-side join(小表复制的代价会好过大表Shuffle的代价)

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.

法一：hint，语法是/*+MapJOIN (tbl)*/，其中tbl就是你想要做replication的表

法二：

hive.auto.convert.join

hive.mapjoin.smalltable.filesize

Broadcast hash Join

bucket Map join(当小表内存里放不下时)

原理：

两个join表在join key上都做hash bucket，这样数据就会按照join key做hash bucket。

小表依然复制到所有节点，Map join的时候，小表的每一组bucket加载成hashtable，与对应的一个大表bucket做局部join，这样每次只需要加载部分hashtable就可以了。

要点：

1.和map join一起工作（ hive.optimize.bucketmapjoin = true;）

2.所有要join的表都必须做了分桶(bucket) , 大表的桶个数是小表桶个数的整数倍.

3.做了bucket的列必须=join的列

4.往bucket表里查数据时，必须hive.enforce.bucketing=true

Sort merge bucket Map join

当两个表的join key都具有唯一性的时候（也就是可做主键），还可以进一步做Sort merge bucket Map join。做法还是两边要做hash bucket，而且每个bucket内部要进行排序。这样一来当两边bucket要做局部join的时候，只需要用类似merge Sort算法中的merge操作一样把两个bucket顺序遍历一遍即可完成，这样甚至都不用把一个bucket完整的加载成hashtable，这对性能的提升会有很大帮助。

在bucket Map join的基础上加上下面的设置即可：

hive.optimize.bucketmapjoin.sortedmerge = true;

hive.input.format = org.apache.Hadoop.Hive.ql.io.BucketizedHiveInputFormat;

Join总结

一般Map join的优化效果已经很明显了。

如果小表不能完全放内存，但是小表相对大表的size量级差别也非常大的时候也可以试试bucket Map join，不过其hash table分发的过程会浪费不少时间，需要评估下是否能够比Reduce join更高效。

而Sort merge bucket Map join虽然性能不错，但是把数据做成bucket本身也需要时间，另外其发动条件比较特殊，就是两边join key必须都唯一

并行

场景：当需要执行多个子查询union all或者join操作的时候

并行执行的确可以大的加快任务的执行速率，但不会减少其占用的资源。

//打开任务并行执行

set hive.exec.parallel=true;

//同一个sql允许最大并行度，默认为8。

set hive.exec.parallel.thread.number=16;

数据倾斜

group by造成的倾斜

set hive.map.aggr=true

join造成的倾斜

set hive.optimize.skewjoin= true;

set hive.skewjoin.key= 阀值;

原理：

特殊值单独join转化成map join

union all ------------------------------------------------------------

非特殊值转化成没有倾斜的普通join

Left semi join

以下2个语句，结果是否一样

select a.id

from tmp.aaa a inner join tmp.bbb b

on a.id=b.id;

select a.id

from tmp.aaa a left semi join tmp.bbb b

on a.id=b.id;

insert overwrite table tmp.aaa
select '1'
union all
select '2'
union all 
select '3’
; 
insert overwrite table tmp.bbb
select ‘2’
；


---------

insert overwrite table tmp.bbb
select '2'
union all
select '2'
;



CREATE TABLE tmp.aaa (
  `id` string
)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.orc.OrcSerde' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'

实现IN/EXISTS子查询

SELECT A.*

FROM A WHERE A.KEY IN

(SELECT B.KEY FROM B WHERE B.VALUE > 100);

等同于:

SELECT A.*

FROM A LEFT SEMI JOIN B

ON (A.KEY = B.KEY and B.VALUE > 100);

Left semi join 总结

1，select和where语句不能引用到右表里的字段（横向不可扩展字段）

2，不会增加主（左）表的行数（纵向无副作用）

3，一旦匹配, 立即退出

sort by

Order by 实现全局排序，一个reduce实现，效率低
Sort by 实现部分有序，单个reduce输出的结果是有序的，效率高，通常和DISTRIBUTE BY关键字一起使用（DISTRIBUTE BY关键字可以指定map 到 reduce端的分发key）
CLUSTER BY col1 等价于DISTRIBUTE BY col1 SORT BY col1

分区消除（裁剪）

以下2句哪个效率高？

select a.id ,b.id

from tmp.aaa a inner join tmp.bbb b

on a.id=b.id and a.id=1

select a.id ,b.id

from tmp.aaa a inner join tmp.bbb b

on a.id=b.id

where a.id=1

通常是 a.id写在on里效率高，但是如果a.id是一个分区字段呢？

等价改写

需求：查询日志中同时访问过页面a和页面b的用户数量

面向明细

select count(*)

from (select wid from logs where pagename = 'a' group by wid) a

join (select wid from logs where pagename = 'b' group by wid) b

on a. wid = b.wid;

2个求子查询的job，一个用于关联的job，还有一个计数的job，一共有4个job

面向集合

select count(*) from (

select wid

from logs group by wid

having count(case when pagename = 'a' then 1 end) *count(case when pagename = 'b' then 1 end) > 0

) t;
只需要用两个job就能跑完（1个子查询的group by， 1个count）

打个比方，你去一个会场里找对象，条件是1.7以上+30岁以下+肤白貌美，面向明细的做法就是先转一圈，挑出1.7以上的，再转一圈，挑出30岁以下的。。。面向集合的做法就是只转一圈，挑出同时满足的所有条件的。

job的启动是有成本的，我们尽可能在启动一个job时，让它多干活。

等价改写的陷阱

以下2句的结果是否一样？

select *

from tmp.ccc

where id1=1 or id2=1

select *

from tmp.ccc

where id1=1

union all

select *

from tmp.ccc

where id2=1

准备数据：

CREATE TABLE tmp.ccc (
  `id1` string,
  `id2` string
)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.orc.OrcSerde' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'  
;


insert overwrite table tmp.ccc
select '1' ,'0'
union all
select '2' ,'1'
union all
select '3' ,'2';

insert overwrite table tmp.ccc
select '1' ,'1'
union all
select '2' ,'0'
union all
select '3' ,'2';

答案：union all 会产生重复的行

-----Credit to Xunbi

ref:

https://www.cnblogs.com/sandbank/p/6408762.html

https://blog.csdn.net/qq_26442553/article/details/80866723

https://www.cnblogs.com/smartloli/p/4356660.html

https://www.jianshu.com/p/6a9a52550f3e

小箱

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Hive优化

1. hive group by distinct区别以及性能比较https://blog.csdn.net/xiaoshunzi111/article/details/684844262. 用insert into替换union all3. order by &amp; sort by 执行计划是什么执行计划代表HiveSQL会转化成怎么样的MapReduce作业。也是...
复制链接

扫一扫