[hive] 맵조인과 셔플조인(Map Join vs Shuffle Join)

hive의 맵조인과 셔플조인의 차이를 확인해 보겠습니다.

 

다음의 조인 쿼리에서 table_a는 14.7G이고, table_b는 5KB입니다. 이 테이블을 조인할 때 각 조인에 따른 성능을 확인해 보면 셔플 조인일 때는 리듀서 단계가 추가되고 맵 조인에 비하여 2배의 시간이 더 걸리는 것을 확인할 수 있습니다.

# table_a와 table_b를 조인하여 join_test 테이블 생성 
# table_a: 14.7 GB
# table_b: 5 KB
CREATE TABLE join_test
AS
select a.deviceid, b.cnty_cd
  from db_a.table_a a, 
       db_b.table_b b
 where a.date = '20191020'
   and a.code = b.code_cd
;

작업 시간

맵조인 작업 시간은 75.14초, 셔플 조인 작업 시간은 130.73초입니다.

# 맵 조인(Map Join)
----------------------------------------------------------------------------------------------
        VERTICES      MODE        STATUS  TOTAL  COMPLETED  RUNNING  PENDING  FAILED  KILLED  
----------------------------------------------------------------------------------------------
Map 1 .......... container     SUCCEEDED     31         31        0        0       0       0  
Map 2 .......... container     SUCCEEDED      1          1        0        0       0       0  
----------------------------------------------------------------------------------------------
VERTICES: 02/02  [==========================>>] 100%  ELAPSED TIME: 75.14 s    
----------------------------------------------------------------------------------------------

# 셔플 조인(Shuffle Join, Merge Join)
----------------------------------------------------------------------------------------------
        VERTICES      MODE        STATUS  TOTAL  COMPLETED  RUNNING  PENDING  FAILED  KILLED  
----------------------------------------------------------------------------------------------
Map 1 .......... container     SUCCEEDED     33         33        0        0       0       0  
Map 3 .......... container     SUCCEEDED      1          1        0        0       0       0  
Reducer 2 ...... container     SUCCEEDED   1009       1009        0        0       0       0  
----------------------------------------------------------------------------------------------
VERTICES: 03/03  [==========================>>] 100%  ELAPSED TIME: 130.73 s   
----------------------------------------------------------------------------------------------

실행 계획 확인

맵 조인

hive (sample_db)> explain CREATE TABLE join_test
                 > AS
                 > select a.deviceid, b.cnty_cd
                 >   from db_a.table_a a, 
                 >        db_b.table_b b
                 >  where a.date = '20191020'
                 >    and a.code = b.code_cd
                 > ;
OK
Plan optimized by CBO.

Vertex dependency in root stage
Map 1 <- Map 2 (BROADCAST_EDGE)

Stage-3
  Stats-Aggr Operator
    Stage-4
      Create Table Operator:
        name:sample_db.join_test
        Stage-2
          Dependency Collection{}
            Stage-1
              Map 1
              File Output Operator [FS_10]
                table:{"name:":"sample_db.join_test"}
                Select Operator [SEL_9] (rows=290865947 width=2073)
                  Output:["_col0","_col1"]
                  Map Join Operator [MAPJOIN_15] (rows=290865947 width=2073)
                    Conds:SEL_2._col1=RS_7.UDFToString(_col0)(Inner),HybridGraceHashJoin:true,Output:["_col0","_col4"]
                  <-Map 2 [BROADCAST_EDGE]
                    BROADCAST [RS_7]
                      PartitionCols:UDFToString(_col0)
                      Select Operator [SEL_5] (rows=513 width=10)
                        Output:["_col0","_col1"]
                        Filter Operator [FIL_14] (rows=513 width=10)
                          predicate:code_cd is not null
                          TableScan [TS_3] (rows=513 width=10)
                            db_b@table_b,b,Tbl:COMPLETE,Col:NONE,Output:["code_cd","cnty_cd"]
                  <-Select Operator [SEL_2] (rows=264423583 width=2073)
                      Output:["_col0","_col1"]
                      Filter Operator [FIL_13] (rows=264423583 width=2073)
                        predicate:code is not null
                        TableScan [TS_0] (rows=264423583 width=2073)
                          db_a@table_a,a,Tbl:COMPLETE,Col:NONE,Output:["deviceid","code"]
        Stage-0
          Move Operator
             Please refer to the previous Stage-1

Time taken: 0.143 seconds, Fetched: 39 row(s)

셔플 조인

hive (sample_db)> explain CREATE TABLE join_test
                 > AS
                 > select a.deviceid, b.cnty_cd
                 >   from db_a.table_a a, 
                 >        db_b.table_b b
                 >  where a.date = '20191020'
                 >    and a.code = b.code_cd
                 > ;
OK
Plan optimized by CBO.

Vertex dependency in root stage
Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 3 (SIMPLE_EDGE)

Stage-3
  Stats-Aggr Operator
    Stage-4
      Create Table Operator:
        name:sample_db.join_test
        Stage-2
          Dependency Collection{}
            Stage-1
              Reducer 2
              File Output Operator [FS_10]
                table:{"name:":"sample_db.join_test"}
                Select Operator [SEL_9] (rows=290865947 width=2073)
                  Output:["_col0","_col1"]
                  Merge Join Operator [MERGEJOIN_15] (rows=290865947 width=2073)
                    Conds:RS_6._col1=RS_7.UDFToString(_col0)(Inner),Output:["_col0","_col4"]
                  <-Map 1 [SIMPLE_EDGE]
                    SHUFFLE [RS_6]
                      PartitionCols:_col1
                      Select Operator [SEL_2] (rows=264423583 width=2073)
                        Output:["_col0","_col1"]
                        Filter Operator [FIL_13] (rows=264423583 width=2073)
                          predicate:code is not null
                          TableScan [TS_0] (rows=264423583 width=2073)
                            db_a@table_a,a,Tbl:COMPLETE,Col:NONE,Output:["deviceid","code"]
                  <-Map 3 [SIMPLE_EDGE]
                    SHUFFLE [RS_7]
                      PartitionCols:UDFToString(_col0)
                      Select Operator [SEL_5] (rows=513 width=10)
                        Output:["_col0","_col1"]
                        Filter Operator [FIL_14] (rows=513 width=10)
                          predicate:code_cd is not null
                          TableScan [TS_3] (rows=513 width=10)
                            db_b@table_b,b,Tbl:COMPLETE,Col:NONE,Output:["code_cd","cnty_cd"]
        Stage-0
          Move Operator
             Please refer to the previous Stage-1

Time taken: 0.146 seconds, Fetched: 42 row(s)

 

두 쿼리의 실행계획을 확인해 보면 셔플조인은 리듀서 단계가 추가되는 것을 확실하게 알 수 있습니다. 하이브 조인의 빠른 처리를 위해서는 되도록 맵조인을 실행하도록 하는 것이 좋습니다.

 

mapjoin vs shufflejoin

맵조인을 위한 설정은 다음과 같습니다.

-- 맵조인 적용을 위한 설정. 기본 10MB로 설정 
set hive.auto.convert.join=true;
set hive.auto.convert.join.noconditionaltask.size=10000000;