[
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)
