You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@pig.apache.org by Raj hadoop <ra...@gmail.com> on 2013/04/25 04:39:50 UTC
Joining 3 tables in Pig
Hi,
I have a where condition in sql query like below
Table1.col1=Table2.col3 and Table2.col2=Table3.col1 and
Table3.col3=Table1.col2
In Pig,
Can i write like below
A= Table1
B=Table2
C=Table3
Joins = join A by col1,B by col3 and B by col2,C by col1 and C by col3,A
by cole
Please help me.
Raj
Re: Joining 3 tables in Pig
Posted by Russell Jurney <ru...@gmail.com>.
You can do a 3-way inner join on the same keys, but if they are on
different keys (or a different type of join), you must do two joins.
Russell Jurney http://datasyndrome.com
On Apr 24, 2013, at 9:53 PM, Bill Graham <bi...@gmail.com> wrote:
> You'll need to do a 3-way join in two steps:
>
> A = load 'foo' as (col1, col2);
> B = load 'bar' as (col1);
> C = load 'bat' as (col1);
>
> AB = join A by col1, B by col1;
> ABC = join AB by A.col2, C by col1;
>
>
> On Wed, Apr 24, 2013 at 8:07 PM, j.barrett Strausser <
> j.barrett.strausser@gmail.com> wrote:
>
>> Have you tried it? If so, what was the result? Did you try
>>
>> DESCRIBE Joins;
>> ILLUSTRATE Joins;
>> EXPLAIN Joins:
>> DUMP Joins;
>>
>> If you haven't tried, that would be the first thing to do.
>>
>> http://pig.apache.org/docs/r0.11.1/basic.html#join-inner
>>
>> -b
>>
>>
>>
>> On Wed, Apr 24, 2013 at 10:39 PM, Raj hadoop <ra...@gmail.com> wrote:
>>
>>> Hi,
>>>
>>> I have a where condition in sql query like below
>>>
>>>
>>> Table1.col1=Table2.col3 and Table2.col2=Table3.col1 and
>>> Table3.col3=Table1.col2
>>>
>>> In Pig,
>>>
>>> Can i write like below
>>>
>>> A= Table1
>>> B=Table2
>>> C=Table3
>>>
>>> Joins = join A by col1,B by col3 and B by col2,C by col1 and C by col3,A
>>> by cole
>>>
>>> Please help me.
>>>
>>> Raj
>>
>>
>>
>> --
>>
>>
>> https://github.com/bearrito
>> @barrettsmash
>
>
>
> --
> *Note that I'm no longer using my Yahoo! email address. Please email me at
> billgraham@gmail.com going forward.*
Re: Joining 3 tables in Pig
Posted by Raj hadoop <ra...@gmail.com>.
Thank you so much Bill. It helped me alot.
On Thu, Apr 25, 2013 at 10:22 AM, Bill Graham <bi...@gmail.com> wrote:
> You'll need to do a 3-way join in two steps:
>
> A = load 'foo' as (col1, col2);
> B = load 'bar' as (col1);
> C = load 'bat' as (col1);
>
> AB = join A by col1, B by col1;
> ABC = join AB by A.col2, C by col1;
>
>
> On Wed, Apr 24, 2013 at 8:07 PM, j.barrett Strausser <
> j.barrett.strausser@gmail.com> wrote:
>
> > Have you tried it? If so, what was the result? Did you try
> >
> > DESCRIBE Joins;
> > ILLUSTRATE Joins;
> > EXPLAIN Joins:
> > DUMP Joins;
> >
> > If you haven't tried, that would be the first thing to do.
> >
> > http://pig.apache.org/docs/r0.11.1/basic.html#join-inner
> >
> > -b
> >
> >
> >
> > On Wed, Apr 24, 2013 at 10:39 PM, Raj hadoop <ra...@gmail.com>
> wrote:
> >
> > > Hi,
> > >
> > > I have a where condition in sql query like below
> > >
> > >
> > > Table1.col1=Table2.col3 and Table2.col2=Table3.col1 and
> > > Table3.col3=Table1.col2
> > >
> > > In Pig,
> > >
> > > Can i write like below
> > >
> > > A= Table1
> > > B=Table2
> > > C=Table3
> > >
> > > Joins = join A by col1,B by col3 and B by col2,C by col1 and C by
> col3,A
> > > by cole
> > >
> > > Please help me.
> > >
> > > Raj
> > >
> >
> >
> >
> > --
> >
> >
> > https://github.com/bearrito
> > @barrettsmash
> >
>
>
>
> --
> *Note that I'm no longer using my Yahoo! email address. Please email me at
> billgraham@gmail.com going forward.*
>
Re: Joining 3 tables in Pig
Posted by Bill Graham <bi...@gmail.com>.
You'll need to do a 3-way join in two steps:
A = load 'foo' as (col1, col2);
B = load 'bar' as (col1);
C = load 'bat' as (col1);
AB = join A by col1, B by col1;
ABC = join AB by A.col2, C by col1;
On Wed, Apr 24, 2013 at 8:07 PM, j.barrett Strausser <
j.barrett.strausser@gmail.com> wrote:
> Have you tried it? If so, what was the result? Did you try
>
> DESCRIBE Joins;
> ILLUSTRATE Joins;
> EXPLAIN Joins:
> DUMP Joins;
>
> If you haven't tried, that would be the first thing to do.
>
> http://pig.apache.org/docs/r0.11.1/basic.html#join-inner
>
> -b
>
>
>
> On Wed, Apr 24, 2013 at 10:39 PM, Raj hadoop <ra...@gmail.com> wrote:
>
> > Hi,
> >
> > I have a where condition in sql query like below
> >
> >
> > Table1.col1=Table2.col3 and Table2.col2=Table3.col1 and
> > Table3.col3=Table1.col2
> >
> > In Pig,
> >
> > Can i write like below
> >
> > A= Table1
> > B=Table2
> > C=Table3
> >
> > Joins = join A by col1,B by col3 and B by col2,C by col1 and C by col3,A
> > by cole
> >
> > Please help me.
> >
> > Raj
> >
>
>
>
> --
>
>
> https://github.com/bearrito
> @barrettsmash
>
--
*Note that I'm no longer using my Yahoo! email address. Please email me at
billgraham@gmail.com going forward.*
Re: Joining 3 tables in Pig
Posted by "j.barrett Strausser" <j....@gmail.com>.
Have you tried it? If so, what was the result? Did you try
DESCRIBE Joins;
ILLUSTRATE Joins;
EXPLAIN Joins:
DUMP Joins;
If you haven't tried, that would be the first thing to do.
http://pig.apache.org/docs/r0.11.1/basic.html#join-inner
-b
On Wed, Apr 24, 2013 at 10:39 PM, Raj hadoop <ra...@gmail.com> wrote:
> Hi,
>
> I have a where condition in sql query like below
>
>
> Table1.col1=Table2.col3 and Table2.col2=Table3.col1 and
> Table3.col3=Table1.col2
>
> In Pig,
>
> Can i write like below
>
> A= Table1
> B=Table2
> C=Table3
>
> Joins = join A by col1,B by col3 and B by col2,C by col1 and C by col3,A
> by cole
>
> Please help me.
>
> Raj
>
--
https://github.com/bearrito
@barrettsmash