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