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

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


Copy link to this message
-
回复: different outer join plan between hive 0.9 and hive 0.10
hi navis:
look at the patches in (HIVE-3411, HIVE-4206, HIVE-4212, HIVE-3464),  I understand what you mean by "hive tags rows a filter mask as a short for outer join, which can contain 16 flags. " . I wonder why not choose Long or int which can contain 64/32 tags. Does adding one Long/int in every row cost too much?

--  
wzc1989
已使用 Sparrow (http://www.sparrowmailapp.com/?sig)
在 2013年5月14日星期二,下午2:17,Navis류승우 写道:

> 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] (mailto:[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] (mailto:[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] (mailto:[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] (mailto:[EMAIL PROTECTED])>
> >  
> >  
> > It's a bug (https://issues.apache.org/jira/browse/HIVE-4206).
> >  
> > Thanks for reporting it.
> >  
> > 2013/3/24 wzc <[EMAIL PROTECTED] (mailto:[EMAIL PROTECTED])>: