You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@pig.apache.org by sonia gehlot <so...@gmail.com> on 2011/02/16 23:09:56 UTC
Multi column Left join in Pig
Hi All,
I am new to Hadoop and I started exploring Pig since last month. I have few
question I have to replicate some SQL query to Pig that has left join for
example:
select blah, blah
From
page_events pe
Left Join page_events pe_pre
on pe.day = pe_pre.day
And pe.session_id = pe_pre.session_id
And pe.page_seq_num = pe_pre.page_seq_num + 1
So I wanted to confirm is this is the right and only way to do multi column
join in Pig? Or we can do this in some other way?
join1_pe_pre = JOIN page_events BY day LEFT OUTER, page_events_pre BY day ;
join2_pe_pre = JOIN join1_pe_pre BY session_id LEFT OUTER, page_events_pre
BY session_id ;
join3_pe_pre = JOIN join2_pe_pre BY page_seq_num LEFT OUTER, page_events_pre
BY page_seq_num +1 ;
Thanks for your help.
Sonia
Re: Multi column Left join in Pig
Posted by sonia gehlot <so...@gmail.com>.
Ok thanks
On Wed, Feb 16, 2011 at 3:35 PM, Ramesh, Amit <am...@amazon.com> wrote:
>
> No, joins are only possible on fields common to all the aliases in the
> join.
>
>
> On 2/16/11 2:56 PM, "sonia gehlot" <so...@gmail.com> wrote:
>
> > Thank you very much Amit.
> >
> > One more question in the same way if I want to join multiple tables
> >
> >> select blah, blah
> >> From
> >> page_events pe
> > Left Join referrer ref
> > on ref.id = pe.id
> >> Left Join page_events pe_pre
> >> on pe.day = pe_pre.day
> >> And pe.session_id = pe_pre.session_id
> >> And pe.page_seq_num = pe_pre.page_seq_num + 1
> >
> > Can we do this in one statement in Pig?
> >
> > Thanks again for your help.
> >
> > Sonia
> >
> > On Wed, Feb 16, 2011 at 2:40 PM, Ramesh, Amit <am...@amazon.com>
> wrote:
> >
> >>
> >> You can just do:
> >>
> >> join_pe_pre = JOIN page_events BY (day, session_id, page_seq_num) LEFT
> >> OUTER, page_events_pre BY (day, session_id, page_seq_num + 1);
> >>
> >> Amit
> >>
> >>
> >> On 2/16/11 2:09 PM, "sonia gehlot" <so...@gmail.com> wrote:
> >>
> >>> Hi All,
> >>>
> >>> I am new to Hadoop and I started exploring Pig since last month. I have
> >> few
> >>> question I have to replicate some SQL query to Pig that has left join
> for
> >>> example:
> >>>
> >>> select blah, blah
> >>> From
> >>> page_events pe
> >>> Left Join page_events pe_pre
> >>> on pe.day = pe_pre.day
> >>> And pe.session_id = pe_pre.session_id
> >>> And pe.page_seq_num = pe_pre.page_seq_num + 1
> >>>
> >>> So I wanted to confirm is this is the right and only way to do multi
> >> column
> >>> join in Pig? Or we can do this in some other way?
> >>>
> >>> join1_pe_pre = JOIN page_events BY day LEFT OUTER, page_events_pre BY
> >> day ;
> >>>
> >>> join2_pe_pre = JOIN join1_pe_pre BY session_id LEFT OUTER,
> >> page_events_pre
> >>> BY session_id ;
> >>>
> >>> join3_pe_pre = JOIN join2_pe_pre BY page_seq_num LEFT OUTER,
> >> page_events_pre
> >>> BY page_seq_num +1 ;
> >>>
> >>> Thanks for your help.
> >>>
> >>> Sonia
> >>
> >>
>
>
Re: Multi column Left join in Pig
Posted by "Ramesh, Amit" <am...@amazon.com>.
No, joins are only possible on fields common to all the aliases in the join.
On 2/16/11 2:56 PM, "sonia gehlot" <so...@gmail.com> wrote:
> Thank you very much Amit.
>
> One more question in the same way if I want to join multiple tables
>
>> select blah, blah
>> From
>> page_events pe
> Left Join referrer ref
> on ref.id = pe.id
>> Left Join page_events pe_pre
>> on pe.day = pe_pre.day
>> And pe.session_id = pe_pre.session_id
>> And pe.page_seq_num = pe_pre.page_seq_num + 1
>
> Can we do this in one statement in Pig?
>
> Thanks again for your help.
>
> Sonia
>
> On Wed, Feb 16, 2011 at 2:40 PM, Ramesh, Amit <am...@amazon.com> wrote:
>
>>
>> You can just do:
>>
>> join_pe_pre = JOIN page_events BY (day, session_id, page_seq_num) LEFT
>> OUTER, page_events_pre BY (day, session_id, page_seq_num + 1);
>>
>> Amit
>>
>>
>> On 2/16/11 2:09 PM, "sonia gehlot" <so...@gmail.com> wrote:
>>
>>> Hi All,
>>>
>>> I am new to Hadoop and I started exploring Pig since last month. I have
>> few
>>> question I have to replicate some SQL query to Pig that has left join for
>>> example:
>>>
>>> select blah, blah
>>> From
>>> page_events pe
>>> Left Join page_events pe_pre
>>> on pe.day = pe_pre.day
>>> And pe.session_id = pe_pre.session_id
>>> And pe.page_seq_num = pe_pre.page_seq_num + 1
>>>
>>> So I wanted to confirm is this is the right and only way to do multi
>> column
>>> join in Pig? Or we can do this in some other way?
>>>
>>> join1_pe_pre = JOIN page_events BY day LEFT OUTER, page_events_pre BY
>> day ;
>>>
>>> join2_pe_pre = JOIN join1_pe_pre BY session_id LEFT OUTER,
>> page_events_pre
>>> BY session_id ;
>>>
>>> join3_pe_pre = JOIN join2_pe_pre BY page_seq_num LEFT OUTER,
>> page_events_pre
>>> BY page_seq_num +1 ;
>>>
>>> Thanks for your help.
>>>
>>> Sonia
>>
>>
Re: Multi column Left join in Pig
Posted by sonia gehlot <so...@gmail.com>.
Thank you very much Amit.
One more question in the same way if I want to join multiple tables
> select blah, blah
> From
> page_events pe
Left Join referrer ref
on ref.id = pe.id
> Left Join page_events pe_pre
> on pe.day = pe_pre.day
> And pe.session_id = pe_pre.session_id
> And pe.page_seq_num = pe_pre.page_seq_num + 1
Can we do this in one statement in Pig?
Thanks again for your help.
Sonia
On Wed, Feb 16, 2011 at 2:40 PM, Ramesh, Amit <am...@amazon.com> wrote:
>
> You can just do:
>
> join_pe_pre = JOIN page_events BY (day, session_id, page_seq_num) LEFT
> OUTER, page_events_pre BY (day, session_id, page_seq_num + 1);
>
> Amit
>
>
> On 2/16/11 2:09 PM, "sonia gehlot" <so...@gmail.com> wrote:
>
> > Hi All,
> >
> > I am new to Hadoop and I started exploring Pig since last month. I have
> few
> > question I have to replicate some SQL query to Pig that has left join for
> > example:
> >
> > select blah, blah
> > From
> > page_events pe
> > Left Join page_events pe_pre
> > on pe.day = pe_pre.day
> > And pe.session_id = pe_pre.session_id
> > And pe.page_seq_num = pe_pre.page_seq_num + 1
> >
> > So I wanted to confirm is this is the right and only way to do multi
> column
> > join in Pig? Or we can do this in some other way?
> >
> > join1_pe_pre = JOIN page_events BY day LEFT OUTER, page_events_pre BY
> day ;
> >
> > join2_pe_pre = JOIN join1_pe_pre BY session_id LEFT OUTER,
> page_events_pre
> > BY session_id ;
> >
> > join3_pe_pre = JOIN join2_pe_pre BY page_seq_num LEFT OUTER,
> page_events_pre
> > BY page_seq_num +1 ;
> >
> > Thanks for your help.
> >
> > Sonia
>
>
Re: Multi column Left join in Pig
Posted by "Ramesh, Amit" <am...@amazon.com>.
You can just do:
join_pe_pre = JOIN page_events BY (day, session_id, page_seq_num) LEFT
OUTER, page_events_pre BY (day, session_id, page_seq_num + 1);
Amit
On 2/16/11 2:09 PM, "sonia gehlot" <so...@gmail.com> wrote:
> Hi All,
>
> I am new to Hadoop and I started exploring Pig since last month. I have few
> question I have to replicate some SQL query to Pig that has left join for
> example:
>
> select blah, blah
> From
> page_events pe
> Left Join page_events pe_pre
> on pe.day = pe_pre.day
> And pe.session_id = pe_pre.session_id
> And pe.page_seq_num = pe_pre.page_seq_num + 1
>
> So I wanted to confirm is this is the right and only way to do multi column
> join in Pig? Or we can do this in some other way?
>
> join1_pe_pre = JOIN page_events BY day LEFT OUTER, page_events_pre BY day ;
>
> join2_pe_pre = JOIN join1_pe_pre BY session_id LEFT OUTER, page_events_pre
> BY session_id ;
>
> join3_pe_pre = JOIN join2_pe_pre BY page_seq_num LEFT OUTER, page_events_pre
> BY page_seq_num +1 ;
>
> Thanks for your help.
>
> Sonia