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
+
Navis류승우 2013-05-14, 04:26
+
wzc1989 2013-05-14, 05:06
Copy link to this message
-
Re: 回复: different outer join plan between hive 0.9 and hive 0.10
Navis류승우 2013-05-14, 06:17
In short, hive tags rows a filter mask as a short for outer join,
which can contain 16 flags. (see HIVE-3411, plz)

I'll survey for a solution.

2013/5/14 wzc1989 <[EMAIL PROTECTED]>:
> "hive cannot merge joins of 16+ aliases with outer join into single stage."
> In our use case we use one table full outer join all other table to produce
> one big table, which may exceed 16 outer join limits and will be split into
> multi stage under hive 0.10.
> It become very slow under hive 0.10 while we run such query well under hive
> 0.9.
> I believe it's due to the diff of query plan. I wonder why hive 0.10 cannot
> merge join 16+ aliases into single stage while hive 0.9 doesn't have such
> issue. could you explain this or give me some hint?
>
> Thanks!
>
> --
> wzc1989
> 已使用 Sparrow
>
> 在 2013年5月14日星期二,下午12:26,Navis류승우 写道:
>
> 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
+
wzc1989 2013-07-01, 07:15
+
Navis류승우 2013-07-02, 00:24
+
wzc1989 2013-07-03, 15:27