hive sql数据倾斜——大表join小表如何使用map join

Map Join是Hive的一种优化操作,其适用于小表JOIN大表的场景,由于表的JOIN操作是在Map端且在内存进行的,所以其并不需要启动Reduce任务也就不需要经过shuffle阶段,从而能在一定程度上节省资源提高JOIN效率

一、Map Join原理

MAPJION会把小表全部读入内存中,在map阶段直接拿另外一个表的数据和内存中表数据做匹配,而普通的equality join则是类似于mapreduce模型中的file join,需要先分组,然后再reduce端进行连接,使用的时候需要结合着场景;由于mapjoin是在map是进行了join操作,省去了reduce的运行,效率也会高很多。这样就不会由于数据倾斜导致某个reduce上落数据太多而失败。于是原来的sql可以通过使用hint的方式指定join时使用mapjoin

Hive Map Join

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.
仍然以9.1中的HQL来说吧,假设a表为一张大表,b为小表,并且hive.auto.convert.join=true,那么Hive在执行时候会自动转化为MapJoin

Map Join基本架构:

在这里插入图片描述
如图中的流程,首先是Task A,它是一个Local Task(在客户端本地执行的Task),负责扫描小表b的数据,将其转换成一个HashTable的数据结构,并写入本地的文件中,之后将该文件加载到DistributeCache中,该HashTable的数据结构可以抽象为:

keyvalue
126
234

在这里插入图片描述
图中红框圈出了执行Local Task的信息。
接下来是Task B,该任务是一个没有ReduceMR,启动MapTasks扫描大表a,在Map阶段,根据a的每一条记录去和DistributeCache中b表对应的HashTable关联,并直接输出结果。
由于MapJoin没有Reduce,所以由Map直接输出结果文件,有多少个Map Task,就有多少个结果文件。

二、Map Join的使用场景:

  1. 关联操作中有一张表非常小
  2. 不等值的链接操作

mapjoin还有一个很大的好处是能够进行不等连接的join操作,如果将不等条件写在where中,那么mapreduce过程中会进行笛卡尔积,运行效率特别低,这是由于equality join(不等值join操作有 >、<、like等如:a.x < b.y或者a.x like b.y) 需要在reduce端进行不等值判断,map端只能过滤掉where中等值连接时候的条件,如果使用mapjoin操作,在map的过程中就完成了不等值的join操作,效率会高很多。

例子:

select 
	A.a,
	A.b 
from A 
join B 
where A.a>B.a

三、Map Join使用方法:

方法一:

Hive0.11前,必须使用MAPJOIN来标记显示地启动该优化操作,由于其需要将小表加载进内存所以要注意小表的大小

SELECT/*+MAPJOIN(smalltable)*/
		big.key AS key,
		big.value AS value
FROM smalltable small
JOIN bigtable big
ON small.key=big.key
方法二:

Hive0.11后,Hive默认启动该优化,也就是不在需要显示的使用MAPJOIN标记,其会在必要的时候触发该优化操作将普通JOIN转换成MapJoin,可以通过以下两个属性来设置该优化的触发时机

hive.auto.convert.join

默认值为true,自动开户MAPJOIN优化

hive.mapjoin.smalltable.filesize

默认值为2500000(25M),通过配置该属性来确定使用该优化的表的大小,如果表的大小小于此值就会被加载进内存中

注意:使用默认启动该优化的方式如果出现默名奇妙的BUG(比如MAPJOIN并不起作用),就将以下两个属性置为fase手动使用MAPJOIN标记来启动该优化

hive.auto.convert.join=false(关闭自动MAPJOIN转换操作)

hive.ignore.mapjoin.hint=false(不忽略MAPJOIN标记)

对于以下查询是不支持使用方法二(MAPJOIN标记)来启动该优化的

SELECT/*+MAPJOIN(smallTableTwo)*/
      idOne,
      idTwo,
      value
FROM
  (SELECT/*+MAPJOIN(smallTableOne)*/
         idOne,
         idTwo,
         value
    FROM bigTable 
    JOIN smallTableOne 
    on (bigTable.idOne= smallTableOne.idOne)
  ) firstjoin
JOIN smallTableTwo 
ON(firstjoin.idTwo=smallTableTwo.idTwo)

但是,如果使用的是方法一即没有MAPJOIN标记则以上查询语句将会被作为两个MJ执行,进一步的,如果预先知道表大小是能够被加载进内存的,则可以通过以下属性来将两个MJ合并成一个MJ

hive.auto.convert.join.noconditionaltask:Hive在基于输入文件大小的前提下将普通JOIN转换成MapJoin,并是否将多个MJ合并成一个
hive.auto.convert.join.noconditionaltask.size:多个MJ合并成一个MJ时,其表的总的大小须小于该值,同时hive.auto.convert.join.noconditionaltask必须为true

四、具体案例

正例一:

遇到一个hive的问题,如下hive sql:

select t1.a,
	   t1.b 
from table t1 
join table2 t2  
  on t1.a=t2.a 
 and t1.datecol=20110802

该语句中B表有30亿行记录,t1表只有100行记录,而且t2表中数据倾斜特别严重,有一个key上有15亿行记录,在运行过程中特别的慢,而且在reduece的过程中遇有内存不够而报错。

在sql开启mapjoin后如下:

select /*+ mapjoin(t1)*/ 
   t1.a,
   t1.b 
from table t1 
join table2 t2  
on t1.a=t2.a 
and f.ftime=20110802

再运行发现执行的效率比以前的写法高了好多,时间由80分钟优化至10分钟内。

五、使用说明

  • 使用MAPJOIN时,在引用小表或子查询时,需要引用别名。
  • MAPJOIN支持小表为子查询。
  • LEFT OUTER JOIN的左表必须是大表。
  • RIGHT OUTER JOIN的右表必须是大表。
  • INNER JOIN的左表或右表均可以作为大表。
  • FULL OUTER JOIN不能使用MAPJOIN。
  • MAPJOIN中,可以使用不等值连接或者OR连接多个条件。您可以通过不写ON语句而通过MAPJOIN ON 1 = 1的形式,实现笛卡尔乘积的计算,例如SELECT /* + MAPJOIN(a) */ a.id FROM shop a JOIN table_name b ON 1=1,但此操作可能带来数据量膨胀问题。
  • MAPJOIN中最多支持指定128张小表,否则报语法错误。MAPJOIN中多个小表用逗号隔开,例如/*+MAPJOIN(a,b,c)*/
  • 如果使用MAPJOIN,则小表占用的总内存不得超过512 MB。由于hdfs文件采用的是压缩存储,因此小表在被加载到内存后,数据大小会急剧膨胀。此处的512 MB是指加载到内存后的空间大小。

六、拓展:Hive Sql Common Join

Hive中的Join可分为Common JoinReduce阶段完成join)和Map JoinMap阶段完成join)。本文简单介绍一下两种join的原理和机制。

Hive Common Join

如果不指定MapJoin或者不符合MapJoin的条件,那么Hive解析器会将Join操作转换成Common Join,即:在Reduce阶段完成join.整个过程包含Map、Shuffle、Reduce阶段。

Hive Common Join架构

在这里插入图片描述

Map阶段

读取源表的数据,Map输出时候以Join on条件中的列为key,如果Join有多个关联键,则以这些关联键的组合作为key;
Map输出的valuejoin之后所关心的(select或者where中需要用到的)列;同时在value中还会包含表的Tag信息,用于标明此value对应哪个表;按照key进行排序

Shuffle阶段

根据key的值进行hash,并将key/value按照hash值推送至不同的reduce中,这样确保两个表中相同的key位于同一个reduce

Reduce阶段

根据key的值完成join操作,期间通过Tag来识别不同表中的数据。
以下面的HQL为例,图解其过程:

SELECT 
   a.id,
   a.dept,
   b.age 
FROM a join b 
ON (a.id = b.id);

在这里插入图片描述

看了这个图,应该知道如何使用MapReduce进行join操作了吧。
参考:[一起学Hive]之十-Hive中Join的原理和机制

  • 10
    点赞
  • 61
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 3
    评论
Hive 中,处理大表小表join 操作需要考虑以下几个方面: 1. 调整 join 的顺序:将小表放在 join 语句的左侧,这样可以减少数据的传输量,提高查询性能。 2. 使用 bucketing 和 sorting:对大表进行 bucketing 和 sorting,可以加快查询速度,减少数据的传输量。同时,如果小表也进行了 bucketing 和 sorting,那么 join 的效率会更高。 3. 使用 Map Join:如果小表可以全部加载到内存中,就可以使用 Map Join,在 Map 阶段将小表加载到内存中,然后在 Reduce 阶段进行 join 操作。这种方式可以避免大量的数据传输和磁盘 I/O,提高查询性能。 4. 使用 Spark SQL:如果 Hive 的查询性能不能满足需求,可以考虑使用 Spark SQL 进行查询。Spark SQL 可以更好地利用内存和硬件资源,提高查询性能。 5. 调整硬件资源:如果以上方法都不能满足需求,可以考虑增加硬件资源,例如增加节点数量、增加内存和 CPU 等,以提高查询性能。 以下是一个实例: 假设有两张表 A 和 B,其中 A 是一个大表,B 是一个小表。A 和 B 都已经进行了 bucketing 和 sorting。现在需要将它们进行 join 操作。 可以按照以下方式处理: ``` -- 将小表放在左侧,调整 join 的顺序 SELECT /*+ MAPJOIN(b) */ a.key, a.value, b.value FROM A a JOIN B b ON a.key = b.key; ``` 上述语句中,使用MAPJOIN 提示,让 Hive小表 B 加载到内存中,然后在 Map 阶段进行 join 操作。这样可以避免大量的数据传输和磁盘 I/O,提高查询性能。
评论 3
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

扫地增

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值