hive-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jian Fang (JIRA)" <>
Subject [jira] [Commented] (HIVE-5888) group by after join operation product no result when hive.optimize.skewjoin = true
Date Wed, 05 Mar 2014 01:21:43 GMT


Jian Fang commented on HIVE-5888:

We had the same problem for Hive 0.11. Ankit created a JIRA at

Based on Ankit's observation, here is the root cause of the problem:

Hive's Skew join optimization is a physical optimization that changes the operator DAG (At
compile time, Hive first creates a basic operator DAG and then various optimizations optimize
it). After compile time skew join optimization, the skew join related nodes will look like:
(MR job with Reduce Join Operator (Stage-1))->(Conditional Skew Join Task that performs
Map Join (Stage-2)). When Skew Join optimization kicks in at compile time, it sets a flag
"handleSkewJoin" in Stage-1. At run time, Stage-1 performs following (provided "handleSkewJoin"
flag was set):

1. Join unskewed keys through normal MR job.
2. Copies data with skewed keys (from all tables) in a specific directory structure in hdfs.

Stage-2 then picks the skewed data and performs Map Join. The Map Join of the skewed keys
is the real optimization because it saves running reducer which has to copy intermediate data
from mappers.

Hive also has Map Join Optimization and this is the cause of the problem. A normal map-reduce
join is converted to map join if (n-1) small tables can fit in memory. If this happens, at
compile time, after both map join and skew join optimization, nodes will look like: (MR job
with Map Join Operator (Stage-1))->(Conditional Skew Join Task that performs Map Join (Stage-2)).

Now the problem is that Skew Join optimization sets "handleSkewJoin" only for "Reduce Join
Operator" in Stage-1 (it assumes there will be a reducer). So, in case there is "Map Join
Operator", "handleSkewJoin" flag is not set and Stage-1 doesn't copy skewed keys in hdfs.
When Stage-2 runs, it is not able to find skewed key directory and it gets eliminated at run
time. Therefore, no results are displayed. 

I tried to set hive.optimize.skewjoin=true and so that stage-1
would not be converted to a mapjoin to work around this problem. But the reduce phase in stage-1
took an extremely long time. We had 200 reducers, most of them only have 5 or 6 input keys
and all the remaining keys were distributed to two reducers. 

Seems the two reducers created very big RowContainer files on local disk, for example.

-rwxrwxrwx 1 hadoop hadoop 334G Mar  5 00:56 RowContainer6650985529012862786.[129].tmp
-rw-r--r-- 1 hadoop hadoop 2.7G Mar  5 00:56 .RowContainer6650985529012862786.[129].tmp.crc

This behavior is really weird. The inconsistent results caused a lot trouble for us. 

Is there any way to work around this problem? 

> group by after join operation product no result when  hive.optimize.skewjoin = true 
> ------------------------------------------------------------------------------------
>                 Key: HIVE-5888
>                 URL:
>             Project: Hive
>          Issue Type: Bug
>    Affects Versions: 0.12.0
>            Reporter: cyril liao

This message was sent by Atlassian JIRA

View raw message