Home | About | Sematext search-lucene.com search-hadoop.com
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB
 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
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
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB