Join Issue with Multiple Reducers

[Matt Pestritto <ma...@pestritto.com>]

Hi.

I wanted to ask if anyone has seen the following behavior in Hive.

When I execute a cross join ( join with no ON statement) across multiple
reducers, I only get output  = 1/ <number of reducers>.  E.g.  I have 1
million rows in 1 table and 1 row in another table and do a join with no on
statement.  If this is executed with 5 reducers, I get 200k rows out instead
of 1M.  If I change the join to an outer join, I get 1M rows output but only
1/5 of the rows have values from the table in the join.

select a.col1, b.col1 from tbl1 a join tbl2 b ;
tbl1 has 1M records.
tbl2 has 1 record.
There are only 200K records output if run across 5 reducers.

If I change to an outer join :
select a.col1, b.col1 from tbl1 a left outer join tbl2 b ;
There are 1M records output, but only 200K of them have a value for b.col1.
The others have a null.

My workaround is just running with 1 reducer, but it took me a while to
figure this out.

Thanks.
-Matt

RE: Join Issue with Multiple Reducers

[Namit Jain <nj...@facebook.com>]

Seems little bizarre - but the following is happening:

If there is no ON, the hash is on a null key which I assume should be uniform.
But, for some reason, the keys are uniformly being sprayed across all reducers -
Only 1 reducer gets data for the smaller table, which explains 200k rows for a normal join,
and 200k rows with no nulls in case of outer join.

I believe Zheng's change might have caused it - I forgot the jira, but the one where the
keys were distributed uniformly to support sort by being handled by multiple reducers.

Can you file a JIRA for this ? The correct fix would be to have 1 reducer in case of a Cartesian product.

-namit


From: Matt Pestritto [mailto:matt@pestritto.com]
Sent: Thursday, April 23, 2009 12:44 PM
To: hive-user@hadoop.apache.org
Subject: Join Issue with Multiple Reducers

Hi.

I wanted to ask if anyone has seen the following behavior in Hive.

When I execute a cross join ( join with no ON statement) across multiple reducers, I only get output  = 1/ <number of reducers>.  E.g.  I have 1 million rows in 1 table and 1 row in another table and do a join with no on statement.  If this is executed with 5 reducers, I get 200k rows out instead of 1M.  If I change the join to an outer join, I get 1M rows output but only 1/5 of the rows have values from the table in the join.

select a.col1, b.col1 from tbl1 a join tbl2 b ;
tbl1 has 1M records.
tbl2 has 1 record.
There are only 200K records output if run across 5 reducers.

If I change to an outer join :
select a.col1, b.col1 from tbl1 a left outer join tbl2 b ;
There are 1M records output, but only 200K of them have a value for b.col1.  The others have a null.

My workaround is just running with 1 reducer, but it took me a while to figure this out.

Thanks.
-Matt