Home | About | Sematext search-lucene.com search-hadoop.com
 Search Hadoop and all its subprojects:

Switch to Plain View
Hive >> mail # user >> different outer join plan between hive 0.9 and hive 0.10


+
wzc 2013-03-24, 05:03
+
Navis류승우 2013-03-28, 05:47
+
wzc 2013-03-28, 15:21
+
Navis류승우 2013-03-29, 01:34
+
wzc1989 2013-05-13, 16:11
Copy link to this message
-
Re: 回复: different outer join plan between hive 0.9 and hive 0.10
The error message means hive cannot merge joins of 16+ aliases with
outer join  into single stage. It was 8 way originally (HIVE-3411) but
expanded to 16 later.

Check https://issues.apache.org/jira/browse/HIVE-3411 for details.

2013/5/14 wzc1989 <[EMAIL PROTECTED]>:
> This time i cherry-pick HIVE-3464, HIVE-4212, HIVE-4206 and some related
> commits and the above explain result matches in hive 0.9 and hive 0.10,
> thanks!
> But I confuse about this error msg:
>
>  JOINNODE_OUTERJOIN_MORETHAN_16(10142, "Single join node containing outer
> join(s) " +
>       "cannot have more than 16 aliases"),
>
> does this mean in hive0.10 when we have more than 16 outer join the query
> plan will still have some bug?
> I test the sql below and find the explain result still diff between hive 0.9
> and hive 0.10.
>
> explain select
> sum(a.value) val
> from default.test_join a
> left outer join default.test_join b on a.key = b.key
> left outer join default.test_join c on a.key = c.key
> left outer join default.test_join d on a.key = d.key
> left outer join default.test_join e on a.key = e.key
> left outer join default.test_join f on a.key = f.key
> left outer join default.test_join g on a.key = g.key
> left outer join default.test_join h on a.key = h.key
> left outer join default.test_join i on a.key = i.key
> left outer join default.test_join j on a.key = j.key
> left outer join default.test_join k on a.key = k.key
> left outer join default.test_join l on a.key = l.key
> left outer join default.test_join m on a.key = m.key
> left outer join default.test_join n on a.key = n.key
> left outer join default.test_join u on a.key = u.key
> left outer join default.test_join v on a.key = v.key
> left outer join default.test_join w on a.key = w.key
> left outer join default.test_join x on a.key = x.key
> left outer join default.test_join z on a.key = z.key
>
>
> --
> wzc1989
> 已使用 Sparrow
>
> 在 2013年3月29日星期五,上午9:34,Navis류승우 写道:
>
> The problem is mixture of issues (HIVE-3411, HIVE-4209, HIVE-4212,
> HIVE-3464) and still not completely fixed even in trunk.
>
> Will be fixed shortly.
>
> 2013/3/29 wzc <[EMAIL PROTECTED]>:
>
> The bug remains even if I apply the patch in HIVE-4206 :( The explain
> result hasn't change.
>
>
> 2013/3/28 Navis류승우 <[EMAIL PROTECTED]>
>
>
> It's a bug (https://issues.apache.org/jira/browse/HIVE-4206).
>
> Thanks for reporting it.
>
> 2013/3/24 wzc <[EMAIL PROTECTED]>:
>
> Recently we tried to upgrade our hive from 0.9 to 0.10, but found some
> of
> our hive queries almost 7 times slow. One of such query consists
> multiple
> table outer join on the same key. By looking into the query, we found
> the
> query plans generate by hive 0.9 and hive 0.10 are different. Here is
> the
> example:
>
> testcase:
>
> use default;
> create table test_join (
> `key` string,
> `value` string
> );
>
> explain select
> sum(a.value) val
> from default.test_join a
> left outer join default.test_join b on a.key = b.key
> left outer join default.test_join c on a.key = c.key
> left outer join default.test_join d on a.key = d.key
> left outer join default.test_join e on a.key = e.key
> left outer join default.test_join f on a.key = f.key
> left outer join default.test_join g on a.key = g.key
>
>
> the explain of hive 0.9:
>
> STAGE DEPENDENCIES:
>
> Stage-1 is a root stage
>
> Stage-2 depends on stages: Stage-1
>
> Stage-0 is a root stage
>
> ...
>
> Reduce Operator Tree:
>
> Join Operator
>
> condition map:
>
> Left Outer Join0 to 1
>
> Left Outer Join0 to 2
>
> Left Outer Join0 to 3
>
> Left Outer Join0 to 4
>
> Left Outer Join0 to 5
>
> Left Outer Join0 to 6
>
> condition expressions:
>
> 0 {VALUE._col1}
>
> 1
>
> 2
>
> 3
>
> 4
>
> 5
>
> 6
>
> ......
>
>
> while the explain of hive 0.10:
>
> STAGE DEPENDENCIES:
>
> Stage-6 is a root stage
>
> Stage-1 depends on stages: Stage-6
>
> Stage-2 depends on stages: Stage-1
>
> Stage-0 is a root stage
>
> ...
>
> Reduce Operator Tree:
>
> Join Operator
>
> condition map:
>
> Left Outer Join0 to 1
>
> Left Outer Join0 to 2
+
wzc1989 2013-05-14, 05:06
+
Navis류승우 2013-05-14, 06:17
+
wzc1989 2013-07-01, 07:15
+
Navis류승우 2013-07-02, 00:24
+
wzc1989 2013-07-03, 15:27