精华内容
下载资源
问答
  • hive大小表join优化性能

    万次阅读 2018-12-12 20:18:37
    摘要: MAPJOIN 当一个表和一个或多个小表JOIN时,最好使用MAPJOIN,性能比普通的JOIN要快很多。 另外,MAPJOIN 还能解决数据倾斜的问题。 MAPJOIN的基本原理是:在小数据量情况下,SQL会将用户指定的小表全部...

    摘要: MAPJOIN 当一个大表和一个或多个小表做JOIN时,最好使用MAPJOIN,性能比普通的JOIN要快很多。 另外,MAPJOIN 还能解决数据倾斜的问题。 MAPJOIN的基本原理是:在小数据量情况下,SQL会将用户指定的小表全部加载到执行JOIN操作的程序的内存中,从而加快JOIN的执行速度。

    1、小、大表 join

    在小表和大表进行join时,将小表放在前边,效率会高。hive会将小表进行缓存。

    2、mapjoin

    使用mapjoin将小表放入内存,在map端和大表逐一匹配。从而省去reduce。

    样例:

    select /*+MAPJOIN(b)*/ a.a1,a.a2,b.b2 from tablea a JOIN tableb b ON a.a1=b.b1
    
    缓存多张小表:
    select /*+MAPJOIN(b,c)*/ a.a1,a.a2,b.b2 from tablea a JOIN tableb b ON a.a1=b.b1 JOIN tbalec c on a.a1=c.c1
    
    mapjoin的join发生在map阶段,join的join发生在reduce阶段,mapjoin可以提高效率

    使用

    方法一:

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

    SELECT /*+ MAPJOIN(smalltable)*/  .key,value
    FROM smalltable JOIN bigtable ON smalltable.key = bigtable.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

    MAPJOIN

    当一个大表和一个或多个小表做JOIN时,最好使用MAPJOIN,性能比普通的JOIN要快很多。 另外,MAPJOIN 还能解决数据倾斜的问题。
    MAPJOIN的基本原理是:在小数据量情况下,SQL会将用户指定的小表全部加载到执行JOIN操作的程序的内存中,从而加快JOIN的执行速度。
    使用MAPJOIN时,需要注意:

    • LEFT OUTER JOIN的左表必须是大表;
    • RIGHT OUTER JOIN的右表必须是大表;
    • INNER JOIN左表或右表均可以作为大表;
    • FULL OUTER JOIN不能使用MAPJOIN;
    • MAPJOIN支持小表为子查询;
    • 使用MAPJOIN时需要引用小表或是子查询时,需要引用别名;
    • 在MAPJOIN中,可以使用不等值连接或者使用OR连接多个条件;
    • 目前ODPS在MAPJOIN中最多支持指定6张小表,否则报语法错误;
    • 如果使用MAPJOIN,则所有小表占用的内存总和不得超过512M(解压后的逻辑数据量)。

    MAPJOIN 判定逻辑:

    同时满足下面2个条件:
    1) Join阶段 max(join instance 运行时间) > 10分钟 && max( join instance 运行时间 ) > 2 * avg( join instance 运行时间 )
    2) 参与join 的最小表数据量小于100M (解压前的逻辑数据量)

    MAPJOIN 内存自定义设置:

    set odps.sql.mapjoin.memory.max=512
    设置mapjoin时小表的最大内存,默认512,单位M,[128,2048]之间调整

    举例

    这个例子比较综合,既涉及到了数据倾斜问题,又涉及到当“小表”不是很小时(>512M)如何利用mapjoin.
    场景:

      select * from log a
      left outer join users b
      on a.user_id = b.user_id;

    日志表(log)通常来说是记录数比较多的,但用户表(users)也不小,600W+ 的记录,把 users 分发到所有的 map 上也是个不小的开销,而且 map join 不支持这么大的小表。如果用普通的 join,又会碰到数据倾斜的问题。
    解决方法:

      select /*+mapjoin(b)*/
      * 
      from log a
      left outer join (
        select  /*+mapjoin(c)*/
        d.*
        from ( select distinct user_id from log ) c
        join users d
        on c.user_id = d.user_id
        ) b
      on a.user_id = b.user_id;

    这种解决方法的前提场景是:每日的会员uv不会太多,即 log 表中的 count(distinct user_id) 不会太大。

    展开全文
  • Hive SQL 小表Join 原理与实操

    多人点赞 2021-08-07 23:23:05
    在数据开发时,经常遇到需要将表某些暗文翻译成明文,正常的做法是将暗文与明文通过维度建立起映射关系,一般在数据分析时通过与维度进行join 关联...本文就是通过介绍map join 解决小表join 运行过慢的问题。

    一、案例演示

    1)没有使用map join 

    
    -- a 表是大表,数据量是百万级别
    -- b 表是小表,数据量在百级别
    select 
    	a.field1 as field1,
    	b.field2 as field2,
    	b.field3 as field3
    from a left join b
    	on a.field1 = b.field1;

    运行时间为:总耗时:08:10:03

    2) 使用map join 优化后

    
    -- a 表是大表,数据量是百万级别
    -- b 表是小表,数据量在百级别
    -- 特别说明:mapjion括号中的b就是指定哪张表为小表
    select 
    	 /*+mapjoin(b)*/
    	a.field1 as field1,
    	b.field2 as field2,
    	b.field3 as field3
    from a left join b
    	on a.field1 = b.field1;

     运行时间为:总耗时:00:12:39

    通过对比,map join大大提升了SQL执行效率,节省分析时间


     二、map join是如何解决数据小表与大表join优化?

    背景说明:A表有百万级、千万级、亿级,B表2万行及2万以内的记录,而且A表中数据倾斜特别严重,有一个key上有30%或40%以上记录,在运行中特别的慢,而且在reduce的过程中遇到内存不够而报错。

    这个问题属于典型的小表与join优化问题,考虑使用mapjoin 进行优化

    mapjoin的原理: MapJoin 会把小表全部读入内存中,在map阶段直接拿另外一个表的数据和内存中表数据做匹配,由于在map是进行了join操作,省去了reduce 阶段,运行的效率就会高很多

    为了更方便理解,mapjoin原理大白话:小表复制到各个节点上,并加载到内存中;大表分片,与小表完成连接操作。

    这样就不会由于数据倾斜导致某个reduce上落的数据太多而失败。于是原来的sql 可以通过使用hint 的方式指定join 时使用 mapjoin,示例如下:

    
    select 
    	/*+mapjoin(small_table)*/
    	big_table.a, small_table.b
    from big_table left join small_table
    	on big_table.a = small_table.a;

     添加了hint 显示明确将small_table 读入内存中,通过此方式运行的话效率比以前的写法高了很多。

    那么问题来了,什么样的表应该被视为小表small_table?

           参与连接的小表的行数,以不超过2万条为宜,大小不超过25M.


     三、map join原理

    Hive 整个过程分为Map、Shuffle、Reduce三大阶段

    简单的说,Hive中的Join 可分为Common Join(Reduce阶段完成join)和 Map Join (Map阶段完成join)。

    A、Map阶段

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

    B、Shuffle阶段

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

    C、Reduce阶段

            根据key的值完成join操作,期间通过Tag来识别不同表中的数据。


    1)Hive Common Join

    如果不指定Map Join或者不符合MapJoin的条件,那么Hive解析器会将Join操作转换成Common Join,即:在Reduce阶段完成join. 即:需要通过map、shuffle、reduce三个大的阶段


     2)Hive Map Join

    2.1 什么是Map Join?

            MapJoin 顾名思义,就是在Map阶段进行表之间的连接。而不需要进入到Reduce阶段才进行连接。这样就节省了在Shuffle阶段时要进行的大量数据传输。从而起到了优化作业的作用。

    2.2 MapJoin的原理:

           通过情况下,要连接的各个表里面的数据会分布在不同的Map中进行处理。即同一个Key对应的Value可能存在不同的Map中。这样就必段等到Reduce中去连接。要使MapJoin能够顺利进行,那就必段满足这样的条件:除了一份表的数据分布在不同的Map中外,其他连接的表的数据必须在每个Map中有完整的拷贝。

    2.3 MapJoin适用的场景:

           通过上面分析你会发现,并不是所有的场景都适合用MapJoin。它通常会用在如下的一些情景:在二个要连接的表中,有一个很大,有一个很小,这个小表可以存放在内存中而不影响性能。这样我们就把小表文件复制到每一个Map任务的本地,再让Map把文件读取内存中待用。

    2.4 MapJoin的实现方法:

           1)在Map-Reduce的驱动程序中使用静态方法,DistributedCache.addCacheFile() 增加要拷贝的小表文件。JobTracker在作业启动之前会获取这个URI列表,并将相应的文件拷贝到各个TaskTracker的本地磁盘上。

            2)在Map类的setup方法中使用DistributedCache.getLocalCacheFiles() 方法获取文件目录,并使用标准的文件读写API读取相应的文件。

    2.5 Hive 内置提供的优化机制之一就包括MapJoin

            A、在Hive v0.7之前,需要使用hint 提示 /*+mapjoin(tableNameA,tableNameB,...)*/ 才会执行MapJoin。

            B、Hive v0.7之后的版本已经不需要给出MapJoin的指示就进行优化。它是通过如下配置参数来控制的:

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

    Hive 还提供另外一个参数,就是:表文件的大小作为开启和关闭MapJoin的阈值。

    hive> set hive.mapjoin.smalltable.filesize=25000000 即25M


     文章最后,给大家推荐一些受欢迎的技术博客链接

    1. JAVA相关的深度技术博客链接
    2. Flink 相关技术博客链接
    3. Spark 核心技术链接
    4. 设计模式 —— 深度技术博客链接
    5. 机器学习 —— 深度技术博客链接
    6. Hadoop相关技术博客链接
    7. 超全干货--Flink思维导图,花了3周左右编写、校对
    8. 深入JAVA 的JVM核心原理解决线上各种故障【附案例】
    9. 请谈谈你对volatile的理解?--最近小李子与面试官的一场“硬核较量”
    10. 聊聊RPC通信,经常被问到的一道面试题。源码+笔记,包懂
    11. 深入聊聊Java 垃圾回收机制【附原理图及调优方法】

    欢迎扫描下方的二维码或 搜索 公众号“大数据高级架构师”,我们会有更多、且及时的资料推送给您,欢迎多多交流!

                                               

           

    展开全文
  • 小表表进行join时,将小表放在前边,效率会高。hive会将小表进行缓存。 2、mapjoin 使用mapjoin小表放入内存,在map端和表逐一匹配。从而省去reduce。 样例: SELECT /*+MAPJOIN(b)*/ --将小表放入...

    真正让你明白Hive调优系列3:笛卡尔乘积,小表join大表,Mapjoin等问题

    0.Hive中的优化分类

         真正想要掌握Hive的优化,要熟悉相关的MapReduce,Yarn,hdfs底层源码,明晰Hive的底层执行流程。真正让你明白Hive调优系列,会征对下面分类逐一分析演示。

    大类1:参数优化

    1. 文件输入前看是否需要map前合并小文件
    2. 控制map个数,根据实际需求确认每个map的数据处理量,split的参数等
    3. Map输出是否需要启动压缩,减少网络传输,OOM处理等
    4. 控制redcue个数,控制每个reduce的吞吐量,OOM处理等
    5. 是否将common-join转换成map-join处理策略
    6. 文件输出是否需要启动小文件合并策略
    7. 其他相关参数的配置:如严格模式,JVM重用,列剪切等

    大类2:开发中优化

    1. 数据倾斜,这个是Hive优化的重头戏。出现的原因是因为出现了数据的重新分发和分布,启动了redcue。Hive中数据倾斜分类:group by ,count(distinct)以及join产生的数据倾斜(当然一些窗口函数中用了partition by一会造成数据倾斜)
    2. join相关的优化:分类大表join大表,小表join大表的优化
    3. 代码细节优化分类比如去重用group by替代distinct ;多表关联,先进行子查询后再进行关联;表关联时一定要在子查询里过滤掉NULL值,避免数据倾斜;不要对一个表进行重复处理,多使用临时表,尽量做到一次处理多次使用等等,

    1.笛卡尔乘积与小表join大表

     Hive 设定为严格模式(hive.mapred.mode=strict)时,不允许在 HQL 语句中出现笛卡尔积, 这实际说明了 Hive 对笛卡尔积支持较弱。因为找不到 Join key,Hive 只能使用 1 个 reducer 来完成笛卡尔积。

    需求1一个小表join大表,且两个表特殊的是笛卡尔乘积(on true/on 1=1)。小表的数据量2Mb,大表的数据是4Gb左右。实际开发中该段代码跑了3个小时左右

    drop table if exists FDM_TMP.TMP_FSA_MULTI_PATH_FUNL_ANALYSE_RSLT_D_21_${hivevar:statis_date};
    CREATE TABLE IF NOT EXISTS FDM_TMP.TMP_FSA_MULTI_PATH_FUNL_ANALYSE_RSLT_D_21_${hivevar:statis_date}
    stored as rcfile as
    SELECT T1.ACCT_NO                                              
           ,T1.PAGE_ID                                                 
           ,T1.PAGE_NAME                                               
           ,T1.PAGE_URL                 
           ,T1.TRMNL_TYPE                                              
           ,T1.DEV_ID 
           ,T0.PATH_ID                                               
           ,T0.UBA_HRCHY        
           ,T0.UBA_HRCHY_LO     
           ,T0.TRANS_CYCLE
           ,T0.TRANS_RATE_CALC
           ,T0.CUS_GROUP_NO
           ,T1.SYS_TYPE
      from fdm_tmp.tmp_fsa_multi_path_funl_analyse_rslt_d_01_${hivevar:statis_date} t0   ---小表大概2Mb左右
     inner join FDM_DPA.FSA_MULTI_PATH_FUNL_VISIT_URL_HIS_D t1           ----大表大概3.4G
        on 1=1                                             ----------笛卡尔乘积
       and t0.comp_cond_type='10010201'  --等于
       and t0.path_cond_type = '60020204'         --页面名称
       and t0.UBA_HRCHY= '1' --第一层
     where t1.stat_date<='${statisdate}'
       and t1.stat_date>=t0.trans_cycle  --已将转换周期转换成对应的起始日期
       and (t0.Page_Name = t1.page_name  or t1.page_id =t0.page_name)
     group by t1.acct_no           
              ,t1.Page_ID          
              ,t1.Page_Name        
              ,t1.page_url         
              ,t1.TRMNL_TYPE           
              ,t1.Dev_ID 
              ,t0.path_id              
              ,t0.UBA_HRCHY      
              ,t0.UBA_HRCHY_LO 
              ,t0.trans_cycle
              ,t0.trans_rate_calc
              ,T0.CUS_GROUP_NO
              ,t1.SYS_TYPE
    ;

    优化使用:配置如下参数,使用mapjoin替代common join.当然这里因为group by的原因还是会启动reduce进行去重。但是整体从4个小时优化到1.5小时。一般来说小表join大表一般配置下面四个参数就差不多,当然官方还提供了其他的参数共配置。Hive官网参数配置

    1. set hive.auto.convert.join = true ;   -- hive是否自动根据文件量大小,选择将common join转成map join 。hive 0.10 版本后的默认值 true。
    2. set  hive.mapjoin.smalltable.filesize =25000000 ;大表小表判断的阈值,如果表的大小小于该值25Mb,则会被判定为小表。则会被加载到内存中运行,将commonjoin转化成mapjoin。一般这个值也就最多几百兆的样子。
    3. set  hive.auto.convert.join.noconditionaltask = true;  翻译官网的解释:是否启用基于输入文件的大小,将普通连接转化为Map连接的优化机制。假设参与连接的表(或分区)有N个,如果打开这个参数,并且有N-1个表(或分区)的大小总和小于hive.auto.convert.join.noconditionaltask.size参数指定的值,那么会直接将连接转为Map连接。(说人话:默认值:true,当将普通的join转化为普通的mapjoin时,是否将多个mapjoin转化为一个mapjoin,主要针对多个小表join大表的情形)

    1. set  hive.auto.convert.join.noconditionaltask.size =10000000; 翻译官网:如果hive.auto.convert.join.noconditionaltask是关闭的,则本参数不起作用。否则,如果参与连接的N个表(或分区)中的N-1个 的总大小小于这个参数的值,则直接将连接转为Map连接。默认值为10MB。(说人话:将多个mapjoin转化为一个mapjoin时,其小表总和的最大值,所以这个条件比单独启动一个mapjon的参数set  hive.mapjoin.smalltable.filesize更加严格。

    尖叫提示:

    1.一般遇到小表join大表,不管是多少个小表,把小表写在前面,开启mapjon,同时适当地调大上面的参数,Mapjoin几乎是解决小表join大表(包括笛卡尔乘积)的最好方式。尤其对于笛卡尔乘积的小表join大表来说,性能差别天壤之别。

    2.所谓的mapjoin优化就是在Map阶段完成join工作,而不是像通常的common join在Reduce阶段按照join的列值进行分发数据到每个Reduce上进行join工作。前面我们知道,没有数据分发分布也就不会有数据倾斜的存在。实际上所谓的mapjoin并不是像有些人说的那样只是将小表加载到内存然后跟大表join那么简单,如果那样照样会有reduce的产生,也不会快那么多。而是会将所有的小表全量复制到每个map任务节点,然后再将小表缓存在每个map节点的内存里与大表进行join工作。所以这解释了为啥小表的大小的不能太大的原因,否则复制分发太多反而得不偿失。一般这个值也就几百兆吧。像我们公司每个map的分配的内存才2G,堆内存才1.5G,你要是搞个1个G的小表,直接很容易OOM报错了。

    3.在0.7.0版本之前:需要在sql中使用 /*+ MAPJOIN(smallTable) */ 来开启mapjoin,而后则Hive会自动通过配置的参数来判断是否开启mapjoin。

    4.对于小表join大表的笛卡尔乘积,还可以通过规避的方法避免:具体比如给 Join的两个表都增加一列Join key原理很简单:将小表扩充一列join key,并将小表的总数复制数倍,join key 各不相同,比如第一次为1,复制一次joinkey为2,依次类推;将大表扩充一列join key 为随机数,这个随机数为小表里的joinkey的随机值,如1-5的随机值。这样就实现了将一个大表拆分几分同时处理,而且这样小表扩充了几倍,大表就被对应地分成几份处理。这种方式也可以提高笛卡尔乘积小表join大表的性能。

    2.笛卡尔乘积:大表join大表

           大表join大表一般调优有四种方式具体参考其他博客,但是对于笛卡尔乘积来说,如果是小join大,开启mapjoin性能还不算太差,但要是大join大的笛卡尔乘积那是真可怕。

    1.首先要尽量避免笛卡尔乘积,比如HQL无法支持循环,遍历等缺陷,这种情况遇到笛卡尔乘积的可以考虑用spark来替代,或者用UDF来解决,这是首选方案,其他几乎没有更好的处理方案了。

    优化的三种方式

    1、小表 join 大表

    在小表和大表进行join时,将小表放在前边,效率会高。hive会将小表进行缓存。

    2、mapjoin

    使用mapjoin将小表放入内存,在map端和大表逐一匹配。从而省去reduce。

    样例:

    SELECT
    	/*+MAPJOIN(b)*/   --将小表放入内存
    	a.a1,
    	a.a2,
    	b.b2
    FROM
    	tablea a --大表
    JOIN tableb b --小表
    ON
    	a.a1 = b.b1

    这里会有个问题,大表left join小表,大表会出现很多小表的字段,但是其中内容为NULL

    记得过滤。

    规范一点,应该小表left join大表(注意是left、right、inner),然后将小表加入mapjoin中。

    map join 概念:将其中做连接的小表(全量数据)分发到所有 MapTask 端进行 Join,从 而避免了 reduceTask,前提要求是内存足以装下该全量数据。 

    • 使用map join解决小表关联大表造成的数据倾斜问题。这个方法使用的频率很高。
    • 每个MapTask载入了大表的一个数据块做处理,载入小表的所有数据做处理,省去了ReduceTask,避免了分区不均,提高了效率。
    • 大表放硬盘,小表放内存。

     hive:使用map join解决大小表关联造成的数据倾斜_dd1296的博客-CSDN博客

    如果两张都是大表,能不能使用mapjoin?

    可以。
    把其中一张大表切分成小表,然后分别 map join。(其实不太懂)

    SELECT
    	/*+mapjoin(x)*/
    	*
    FROM
    	log a --大表
    LEFT JOIN
    	(--大表切分成的小表
    		SELECT
    			/*+mapjoin(c)*/
    			d.*
    		FROM
    			(
    				SELECT DISTINCT user_id FROM log
    			)
    			c
    		JOIN users d
    		ON
    			c.user_id = d.user_id
    	)
    	x ON a.user_id = x.user_id;

    3、set配置

    (没啥用)在0.7版本号后。也能够用配置来自己主动优化

    set hive.auto.convert.join=true;
    展开全文
  • Map Join是Hive的一种优化操作,其适用于小表JOIN大表的场景,由于表的JOIN操作是在Map端且在内存进行的,所以其并不需要启动Reduce任务也就不需要经过shuffle阶段,从而能在一定程度上节省资源提高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的原理和机制

    展开全文
  • 经常看到一些Hive优化的建议中说当小表表做关联时,把小表写在前面,这样可以使Hive的关联速度更快,提到的原因都是说因为小表可以先放到内存中,然后表的每条记录再去内存中检测,最终完成关联查询。...
  • hive小表join提升运行效率

    千次阅读 2017-06-23 13:45:52
    问题描述:一小表 1000 row 一表 60w row 方案一: 在运行的时候发现会自动转为map join 本以为会很快,但是只起了一个map ,join 的计算量 : 单机计算6 亿次,结果一直map 0% 最后运行 1800s  方案二: 采用...
  • hive中大表join小表情况

    千次阅读 2019-02-15 18:55:33
    和join相关的优化主要分为mapjoin可以解决的优化(即大表join小表)和mapjoin无法解决的优化(即大表join大表),前者相对容易解决,后者较难,比较麻烦。  首先介绍大表join小表优化。以销售明细表为例来说明大表...
  • hive join 优化 --小表join大

    万次阅读 2014-10-25 21:49:25
    小表表进行join时,将小表放在前边,效率会高,hive会将小表进行缓存。 2、mapjoin 使用mapjoin小表放入内存,在map端和表逐一匹配,从而省去reduce。 例子: select /*+MAPJOIN(b)*/ a.a1,a.a2,b.b2 from...
  • hive join优化一:表关联小表 两个table的join的时候,如果单纯的使用MR的话会消耗大量的内存,浪费磁盘的IO,幅度的影响性能。 在大小表join的时候,即一个比较小的表和一个较的表joining,如果使用mapjoin...
  • Hive优化-大表join大表优化

    千次阅读 2020-08-10 15:13:33
     如果Hive优化实战2中mapjoin中小表dim_seller很大呢?比如超过了1GB大小?这种就是大表join大表的问题。首先引入一个具体的问题场景,然后基于此介绍各自优化方案。  5.1、问题场景  问题场景如下:  A表为...
  • 1、关联查询时,有一个较的key比较集中 key的分布不均就导致在分区时,某一个或几个分区的数量过多 2、使用group by但没有用聚合函数,导致维度过,某值的数量过多 那么我们需要在使用group by时注意一定要...
  • Hive小表表关联(join)的性能分析

    万次阅读 多人点赞 2018-06-30 11:14:07
    经常看到一些Hive优化的建议中说当小表表做关联时,把小表写在前面,这样可以使Hive的关联速度更快,提到的原因都是说因为小表可以先放到内存中,然后表的每条记录再去内存中检测,最终完成关联查询。...
  • 我 相 信 这 么 优秀 的 你已 经 置 顶了 我作者:王晓伟从事大数据相关开发,曾经为多个开源框架如Hive、Yarn、Pig、Tez贡献代码。◇◆◇◆◇场景如果某个key下记录数远超其他key,在join或group的时候可能会导致...
  • hivejoin小表

    千次阅读 2018-08-03 15:24:51
    小表表进行join时,将小表放在前边,效率会高。hive会将小表进行缓存。 2、mapjoin 使用mapjoin小表放入内存,在map端和表逐一匹配。从而省去reduce。 样例: select /*+MAPJOIN(b)*/ a.a1,a.a2,b.b2...
  • 用户轨迹工程的性能瓶颈一直是etract_track_info,其中耗时大户主要在于trackinfo与pm_info进行左关联的环节,trackinfo与pm_info两张均为GB级别,左关联代码块如下: [SQL] 1 2 3 ...
  • 0.Hive中的优化分类 真正想要掌握Hive的优化,要熟悉相关的MapReduce,Yarn,hdfs底层源码,明晰Hive的底层执行流程。真正让你明白Hive调优系列,会征对下面分类逐一介绍。... 是否将common-join转换成...
  • Hive大表JOIN优化

    千次阅读 2018-08-29 09:06:25
    用户轨迹工程的性能瓶颈一直是etract_track_info,其中耗时大户主要在于trackinfo与pm_info进行左关联的环节,trackinfo与pm_info两张均为GB级别,左关联代码块如下: [SQL] 纯文本查看 复制代码 fr...
  • 开启map join, 然后设置合适的split的大小,来增加到合适的mapper数量
  • [Hive]Hive数据倾斜(大表join大表

    万次阅读 多人点赞 2015-05-12 10:23:36
    Hive数据倾斜(大表join大表)的现象、思路以及解决方案
  • hive 三种join实现

    2021-03-07 23:37:21
    众所周知,hive 提供了三种join方式,common join/map join/ smb join,那么如何选择最合适的join 类型?1. common join是最常见的join 类型,需要执行shuffle操作,根据join条件对数据进行重新分布,shuffle操作...
  • Hive MapJoin小表表)

    千次阅读 2019-04-04 14:49:13
    MapJoin是Hive的一种优化操作,其适用于小表JOIN大表的场景,由于表的JOIN操作是在Map端且在内存进行的,所以其并不需要启动Reduce任务也就不需要经过shuffle阶段,从而能在一定程度上节省资源提高JOIN效率 ...
  • Flink Sql Hive表Join

    千次阅读 2021-03-11 14:23:07
    Fink 1.12 Hive表Join
  • 将key相对分散,并且数据量小...实际测试发现:新版的hive已经对小表JOIN大表和大表JOIN小表进行了优化。小表放在左边和右边已经没有明显区别。 案例实操 测试大表JOIN小表小表JOIN大表的效率 1、建大表、小表和...
  • hive中,(启用Map join时) 表left join小表,加载从右向左,所以小表会加载进内存,存储成map键值对,通过表驱动小表,来进行join,即表中的join字段作为key 来获取value进行join。 在MySQL中,left join...
  • Hive数据倾斜(大表join大表

    千次阅读 2017-07-10 11:08:12
    用户轨迹工程的性能瓶颈一直是etract_track_info,其中耗时大户主要在于trackinfo与pm_info进行左关联的环节,trackinfo与pm_info两张均为GB级别,左关联代码块如下: from trackinfo a left outer join pm_info...
  • Hive Join详解

    千次阅读 2020-12-20 04:43:31
    Join原理Hive执行引擎会将HQL“翻译”成为MapReduce任务,如果多张表使用同一列做Join,将被“翻译”成一个MapReduce任务,否则会被“翻译”成多个MapReduce任务例如:以下将被“翻译”成1个MapReduce任务SELECT ...
  • Hive的三种Join方式

    2021-02-27 14:39:57
    select /*+ MAPJOIN(time_dim)*/ count(1)fromstore_salesjointime_dimon (ss_... b.y 或者 a.x like b.y等)这种操作如果直接使用join的话语法不支持不等于操作,hive语法解析会直接抛出错误如果把不等于写到where里...
  • Hive连接优化(join优化)

    千次阅读 2018-11-15 15:05:17
    left join,right join,inner join,full join之间的区别 参考 备注:转载的 转载地址:https://www.cnblogs.com/lijingran/p/9001302.html 下面是转载内容 https://www.cnblogs.com/assasion/p/7768931.html ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 42,947
精华内容 17,178
关键字:

hive小表join大表