[jira] [Commented] (HIVE-5888) group by after join operation product no result when hive.optimize.skewjoin = true

["Jian Fang (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/HIVE-5888?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13920354#comment-13920354 ] 

Jian Fang commented on HIVE-5888:
---------------------------------

We had the same problem for Hive 0.11. Ankit created a JIRA at https://issues.apache.org/jira/browse/HIVE-6520.

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 hive.auto.convert.join=false 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: https://issues.apache.org/jira/browse/HIVE-5888
>             Project: Hive
>          Issue Type: Bug
>    Affects Versions: 0.12.0
>            Reporter: cyril liao
>




--
This message was sent by Atlassian JIRA
(v6.2#6252)