You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@pig.apache.org by Yonggang Qiao <yo...@gmail.com> on 2009/08/07 20:44:00 UTC
join multiple dataset on different key sets
Hello there,
Is it possible to do something like this by using one join? Thanks!
select *
from tbl_a, tbl_b, tbl_c
where tbl_a.b = tbl_b.b and tbl_a.c= tbl_c.c
Re: join multiple dataset on different key sets
Posted by Chris Olston <ol...@yahoo-inc.com>.
Yes.
On 8/7/09 1:53 PM, "yonggang.qiao@gmail.com" <yo...@gmail.com>
wrote:
> So after sql layer being added, this use case will be supported automatically?
> Cascading is fine, but I have ~10 star tables...
>
> Sent via BlackBerry by AT&T
>
> -----Original Message-----
> From: Chris Olston <ol...@yahoo-inc.com>
>
> Date: Fri, 07 Aug 2009 13:41:26
> To: <pi...@hadoop.apache.org>
> Subject: Re: join multiple dataset on different key sets
>
>
> Pig Latin is for cases in which the user has a particular ordering in mind
> (same philosophy as raw map-reduce). Later, a SQL layer will be added to Pig
> for cases in which you want to leave such decisions in the hands of an
> automated query optimizer.
>
> -Chris
>
>
> On 8/7/09 1:24 PM, "Dmitriy Ryaboy" <dv...@cloudera.com> wrote:
>
>> Ah, sorry, missed that the predicates were different.
>>
>> Would be cool if there was a way to specify something that matches anything.
>>
>> So you could join A on b, c, B on b, _ignore_, C on c, _ignore_
>>
>> except with cleaner syntax -- maybe even the sql style "A, B, C on (
>> A.b=B.b and A.c=C.c)" .
>>
>> This would still have to be done with 2 MR jobs, but the optimizer
>> would get a crack at optimal ordering.
>>
>> On Fri, Aug 7, 2009 at 12:51 PM, Chris Olston<ol...@yahoo-inc.com> wrote:
>>> No, for this you'd have to use a cascade of two binary join expressions.
>>>
>>> (The predicate for joining tbl_a with tbl_b is not the same as the one for
>>> joining tbl_a with tbl_c.)
>>>
>>> -Chris
>>>
>>>
>>> On 8/7/09 11:56 AM, "Dmitriy Ryaboy" <dv...@cloudera.com> wrote:
>>>
>>>> A join takes an arbitrary number of relations.
>>>>
>>>> So yes.
>>>>
>>>> D = Join A on a, B on b, C on c [PARALLEL n]
>>>>
>>>> you really want to specify the number of reducers using the parallel
>>>> keyword if you are running on a real cluster.
>>>>
>>>> Order maters! Put your smaller relations first.
>>>>
>>>> -D
>>>>
>>>> On Fri, Aug 7, 2009 at 11:44 AM, Yonggang Qiao<yo...@gmail.com>
>>>> wrote:
>>>>> Hello there,
>>>>>
>>>>> Is it possible to do something like this by using one join? Thanks!
>>>>>
>>>>> select *
>>>>> from tbl_a, tbl_b, tbl_c
>>>>> where tbl_a.b = tbl_b.b and tbl_a.c= tbl_c.c
>>>>>
>>>
>>> --
>>> Christopher Olston, Ph.D.
>>> Sr. Research Scientist
>>> Yahoo! Research
>>>
>>>
>>>
>>>
>>>
>
> --
> Christopher Olston, Ph.D.
> Sr. Research Scientist
> Yahoo! Research
>
>
>
>
--
Christopher Olston, Ph.D.
Sr. Research Scientist
Yahoo! Research
Re: join multiple dataset on different key sets
Posted by yo...@gmail.com.
So after sql layer being added, this use case will be supported automatically? Cascading is fine, but I have ~10 star tables...
Sent via BlackBerry by AT&T
-----Original Message-----
From: Chris Olston <ol...@yahoo-inc.com>
Date: Fri, 07 Aug 2009 13:41:26
To: <pi...@hadoop.apache.org>
Subject: Re: join multiple dataset on different key sets
Pig Latin is for cases in which the user has a particular ordering in mind
(same philosophy as raw map-reduce). Later, a SQL layer will be added to Pig
for cases in which you want to leave such decisions in the hands of an
automated query optimizer.
-Chris
On 8/7/09 1:24 PM, "Dmitriy Ryaboy" <dv...@cloudera.com> wrote:
> Ah, sorry, missed that the predicates were different.
>
> Would be cool if there was a way to specify something that matches anything.
>
> So you could join A on b, c, B on b, _ignore_, C on c, _ignore_
>
> except with cleaner syntax -- maybe even the sql style "A, B, C on (
> A.b=B.b and A.c=C.c)" .
>
> This would still have to be done with 2 MR jobs, but the optimizer
> would get a crack at optimal ordering.
>
> On Fri, Aug 7, 2009 at 12:51 PM, Chris Olston<ol...@yahoo-inc.com> wrote:
>> No, for this you'd have to use a cascade of two binary join expressions.
>>
>> (The predicate for joining tbl_a with tbl_b is not the same as the one for
>> joining tbl_a with tbl_c.)
>>
>> -Chris
>>
>>
>> On 8/7/09 11:56 AM, "Dmitriy Ryaboy" <dv...@cloudera.com> wrote:
>>
>>> A join takes an arbitrary number of relations.
>>>
>>> So yes.
>>>
>>> D = Join A on a, B on b, C on c [PARALLEL n]
>>>
>>> you really want to specify the number of reducers using the parallel
>>> keyword if you are running on a real cluster.
>>>
>>> Order maters! Put your smaller relations first.
>>>
>>> -D
>>>
>>> On Fri, Aug 7, 2009 at 11:44 AM, Yonggang Qiao<yo...@gmail.com>
>>> wrote:
>>>> Hello there,
>>>>
>>>> Is it possible to do something like this by using one join? Thanks!
>>>>
>>>> select *
>>>> from tbl_a, tbl_b, tbl_c
>>>> where tbl_a.b = tbl_b.b and tbl_a.c= tbl_c.c
>>>>
>>
>> --
>> Christopher Olston, Ph.D.
>> Sr. Research Scientist
>> Yahoo! Research
>>
>>
>>
>>
>>
--
Christopher Olston, Ph.D.
Sr. Research Scientist
Yahoo! Research
Re: join multiple dataset on different key sets
Posted by Chris Olston <ol...@yahoo-inc.com>.
Pig Latin is for cases in which the user has a particular ordering in mind
(same philosophy as raw map-reduce). Later, a SQL layer will be added to Pig
for cases in which you want to leave such decisions in the hands of an
automated query optimizer.
-Chris
On 8/7/09 1:24 PM, "Dmitriy Ryaboy" <dv...@cloudera.com> wrote:
> Ah, sorry, missed that the predicates were different.
>
> Would be cool if there was a way to specify something that matches anything.
>
> So you could join A on b, c, B on b, _ignore_, C on c, _ignore_
>
> except with cleaner syntax -- maybe even the sql style "A, B, C on (
> A.b=B.b and A.c=C.c)" .
>
> This would still have to be done with 2 MR jobs, but the optimizer
> would get a crack at optimal ordering.
>
> On Fri, Aug 7, 2009 at 12:51 PM, Chris Olston<ol...@yahoo-inc.com> wrote:
>> No, for this you'd have to use a cascade of two binary join expressions.
>>
>> (The predicate for joining tbl_a with tbl_b is not the same as the one for
>> joining tbl_a with tbl_c.)
>>
>> -Chris
>>
>>
>> On 8/7/09 11:56 AM, "Dmitriy Ryaboy" <dv...@cloudera.com> wrote:
>>
>>> A join takes an arbitrary number of relations.
>>>
>>> So yes.
>>>
>>> D = Join A on a, B on b, C on c [PARALLEL n]
>>>
>>> you really want to specify the number of reducers using the parallel
>>> keyword if you are running on a real cluster.
>>>
>>> Order maters! Put your smaller relations first.
>>>
>>> -D
>>>
>>> On Fri, Aug 7, 2009 at 11:44 AM, Yonggang Qiao<yo...@gmail.com>
>>> wrote:
>>>> Hello there,
>>>>
>>>> Is it possible to do something like this by using one join? Thanks!
>>>>
>>>> select *
>>>> from tbl_a, tbl_b, tbl_c
>>>> where tbl_a.b = tbl_b.b and tbl_a.c= tbl_c.c
>>>>
>>
>> --
>> Christopher Olston, Ph.D.
>> Sr. Research Scientist
>> Yahoo! Research
>>
>>
>>
>>
>>
--
Christopher Olston, Ph.D.
Sr. Research Scientist
Yahoo! Research
Re: join multiple dataset on different key sets
Posted by Dmitriy Ryaboy <dv...@cloudera.com>.
Ah, sorry, missed that the predicates were different.
Would be cool if there was a way to specify something that matches anything.
So you could join A on b, c, B on b, _ignore_, C on c, _ignore_
except with cleaner syntax -- maybe even the sql style "A, B, C on (
A.b=B.b and A.c=C.c)" .
This would still have to be done with 2 MR jobs, but the optimizer
would get a crack at optimal ordering.
On Fri, Aug 7, 2009 at 12:51 PM, Chris Olston<ol...@yahoo-inc.com> wrote:
> No, for this you'd have to use a cascade of two binary join expressions.
>
> (The predicate for joining tbl_a with tbl_b is not the same as the one for
> joining tbl_a with tbl_c.)
>
> -Chris
>
>
> On 8/7/09 11:56 AM, "Dmitriy Ryaboy" <dv...@cloudera.com> wrote:
>
>> A join takes an arbitrary number of relations.
>>
>> So yes.
>>
>> D = Join A on a, B on b, C on c [PARALLEL n]
>>
>> you really want to specify the number of reducers using the parallel
>> keyword if you are running on a real cluster.
>>
>> Order maters! Put your smaller relations first.
>>
>> -D
>>
>> On Fri, Aug 7, 2009 at 11:44 AM, Yonggang Qiao<yo...@gmail.com> wrote:
>>> Hello there,
>>>
>>> Is it possible to do something like this by using one join? Thanks!
>>>
>>> select *
>>> from tbl_a, tbl_b, tbl_c
>>> where tbl_a.b = tbl_b.b and tbl_a.c= tbl_c.c
>>>
>
> --
> Christopher Olston, Ph.D.
> Sr. Research Scientist
> Yahoo! Research
>
>
>
>
>
Re: join multiple dataset on different key sets
Posted by Chris Olston <ol...@yahoo-inc.com>.
No, for this you'd have to use a cascade of two binary join expressions.
(The predicate for joining tbl_a with tbl_b is not the same as the one for
joining tbl_a with tbl_c.)
-Chris
On 8/7/09 11:56 AM, "Dmitriy Ryaboy" <dv...@cloudera.com> wrote:
> A join takes an arbitrary number of relations.
>
> So yes.
>
> D = Join A on a, B on b, C on c [PARALLEL n]
>
> you really want to specify the number of reducers using the parallel
> keyword if you are running on a real cluster.
>
> Order maters! Put your smaller relations first.
>
> -D
>
> On Fri, Aug 7, 2009 at 11:44 AM, Yonggang Qiao<yo...@gmail.com> wrote:
>> Hello there,
>>
>> Is it possible to do something like this by using one join? Thanks!
>>
>> select *
>> from tbl_a, tbl_b, tbl_c
>> where tbl_a.b = tbl_b.b and tbl_a.c= tbl_c.c
>>
--
Christopher Olston, Ph.D.
Sr. Research Scientist
Yahoo! Research
Re: join multiple dataset on different key sets
Posted by Dmitriy Ryaboy <dv...@cloudera.com>.
A join takes an arbitrary number of relations.
So yes.
D = Join A on a, B on b, C on c [PARALLEL n]
you really want to specify the number of reducers using the parallel
keyword if you are running on a real cluster.
Order maters! Put your smaller relations first.
-D
On Fri, Aug 7, 2009 at 11:44 AM, Yonggang Qiao<yo...@gmail.com> wrote:
> Hello there,
>
> Is it possible to do something like this by using one join? Thanks!
>
> select *
> from tbl_a, tbl_b, tbl_c
> where tbl_a.b = tbl_b.b and tbl_a.c= tbl_c.c
>
join multiple dataset on different key sets
Posted by Yonggang Qiao <yo...@gmail.com>.
Hello there,
Is it possible to do something like this by using one join? Thanks!
select *
from tbl_a, tbl_b, tbl_c
where tbl_a.b = tbl_b.b and tbl_a.c= tbl_c.c