FAQ
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

Search Discussions

  • Ramesh, Amit at Feb 16, 2011 at 10:40 pm
    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" 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
  • Sonia gehlot at Feb 16, 2011 at 10:57 pm
    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 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" 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
  • Ramesh, Amit at Feb 16, 2011 at 11:36 pm
    No, joins are only possible on fields common to all the aliases in the join.

    On 2/16/11 2:56 PM, "sonia gehlot" 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 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" 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
  • Sonia gehlot at Feb 16, 2011 at 11:49 pm
    Ok thanks
    On Wed, Feb 16, 2011 at 3:35 PM, Ramesh, Amit 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" 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 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" 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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupuser @
categoriespig, hadoop
postedFeb 16, '11 at 10:10p
activeFeb 16, '11 at 11:49p
posts5
users2
websitepig.apache.org

2 users in discussion

Sonia gehlot: 3 posts Ramesh, Amit: 2 posts

People

Translate

site design / logo © 2021 Grokbase