You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Abhinov Agarwal <ab...@gmail.com> on 2011/05/02 05:50:34 UTC
Cross join in Hive.
Hi,
I need to take a cross join of a big table with itself, is it possible to do
it using Hive ?
E.g.
Set :
1
2
3
Result :
1,1
1,2
1,3
2,1
2,2
2,3
3,1
3,2
3,3
This would also do :
1,2
1,3
2,3
In fact the second one is what I want. I know cross join is not supported in
Hive, any other way to obtain this.
Regards,
Abhinav Agarwal
Re: Cross join in Hive.
Posted by "Raghunath, Ranjith" <Ra...@usaa.com>.
Very cool. What is the non strict option for?
Thanks,
Ranjith
________________________________
From: Ashish Thusoo <at...@fb.com>
To: <us...@hive.apache.org> <us...@hive.apache.org>
Sent: Mon May 02 16:00:39 2011
Subject: Re: Cross join in Hive.
you could probably just say (1 = 1) in the on clause for the join.
set hive.mapred.mode=nonstrict;
select ... from T1 join T2 on (1 = 1);
Ashish
On May 1, 2011, at 10:27 PM, Raghunath, Ranjith wrote:
Forgot to mention....the condition for the inner join should be the column set to 1 in the first table to be equal to the same column in the other table.
Thanks,
Ranjith
________________________________
From: Raghunath, Ranjith <Ra...@usaa.com>>
To: 'user@hive.apache.org<ma...@hive.apache.org>' <us...@hive.apache.org>>
Sent: Mon May 02 00:21:57 2011
Subject: Re: Cross join in Hive.
I haven't tested this out but plan to in 6 hours. Add an extra column and set it to 1 in both tables. Perform an inner join between the two tables.
Thanks,
Ranjith
________________________________
From: Abhinov Agarwal <ab...@gmail.com>>
To: user@hive.apache.org<ma...@hive.apache.org> <us...@hive.apache.org>>
Sent: Sun May 01 22:50:34 2011
Subject: Cross join in Hive.
Hi,
I need to take a cross join of a big table with itself, is it possible to do it using Hive ?
E.g.
Set :
1
2
3
Result :
1,1
1,2
1,3
2,1
2,2
2,3
3,1
3,2
3,3
This would also do :
1,2
1,3
2,3
In fact the second one is what I want. I know cross join is not supported in Hive, any other way to obtain this.
Regards,
Abhinav Agarwal
Re: Cross join in Hive.
Posted by Ashish Thusoo <at...@fb.com>.
you could probably just say (1 = 1) in the on clause for the join.
set hive.mapred.mode=nonstrict;
select ... from T1 join T2 on (1 = 1);
Ashish
On May 1, 2011, at 10:27 PM, Raghunath, Ranjith wrote:
Forgot to mention....the condition for the inner join should be the column set to 1 in the first table to be equal to the same column in the other table.
Thanks,
Ranjith
________________________________
From: Raghunath, Ranjith <Ra...@usaa.com>>
To: 'user@hive.apache.org<ma...@hive.apache.org>' <us...@hive.apache.org>>
Sent: Mon May 02 00:21:57 2011
Subject: Re: Cross join in Hive.
I haven't tested this out but plan to in 6 hours. Add an extra column and set it to 1 in both tables. Perform an inner join between the two tables.
Thanks,
Ranjith
________________________________
From: Abhinov Agarwal <ab...@gmail.com>>
To: user@hive.apache.org<ma...@hive.apache.org> <us...@hive.apache.org>>
Sent: Sun May 01 22:50:34 2011
Subject: Cross join in Hive.
Hi,
I need to take a cross join of a big table with itself, is it possible to do it using Hive ?
E.g.
Set :
1
2
3
Result :
1,1
1,2
1,3
2,1
2,2
2,3
3,1
3,2
3,3
This would also do :
1,2
1,3
2,3
In fact the second one is what I want. I know cross join is not supported in Hive, any other way to obtain this.
Regards,
Abhinav Agarwal
Re: Cross join in Hive.
Posted by "Raghunath, Ranjith" <Ra...@usaa.com>.
Forgot to mention....the condition for the inner join should be the column set to 1 in the first table to be equal to the same column in the other table.
Thanks,
Ranjith
________________________________
From: Raghunath, Ranjith <Ra...@usaa.com>
To: 'user@hive.apache.org' <us...@hive.apache.org>
Sent: Mon May 02 00:21:57 2011
Subject: Re: Cross join in Hive.
I haven't tested this out but plan to in 6 hours. Add an extra column and set it to 1 in both tables. Perform an inner join between the two tables.
Thanks,
Ranjith
________________________________
From: Abhinov Agarwal <ab...@gmail.com>
To: user@hive.apache.org <us...@hive.apache.org>
Sent: Sun May 01 22:50:34 2011
Subject: Cross join in Hive.
Hi,
I need to take a cross join of a big table with itself, is it possible to do it using Hive ?
E.g.
Set :
1
2
3
Result :
1,1
1,2
1,3
2,1
2,2
2,3
3,1
3,2
3,3
This would also do :
1,2
1,3
2,3
In fact the second one is what I want. I know cross join is not supported in Hive, any other way to obtain this.
Regards,
Abhinav Agarwal
Re: Cross join in Hive.
Posted by "Raghunath, Ranjith" <Ra...@usaa.com>.
I haven't tested this out but plan to in 6 hours. Add an extra column and set it to 1 in both tables. Perform an inner join between the two tables.
Thanks,
Ranjith
________________________________
From: Abhinov Agarwal <ab...@gmail.com>
To: user@hive.apache.org <us...@hive.apache.org>
Sent: Sun May 01 22:50:34 2011
Subject: Cross join in Hive.
Hi,
I need to take a cross join of a big table with itself, is it possible to do it using Hive ?
E.g.
Set :
1
2
3
Result :
1,1
1,2
1,3
2,1
2,2
2,3
3,1
3,2
3,3
This would also do :
1,2
1,3
2,3
In fact the second one is what I want. I know cross join is not supported in Hive, any other way to obtain this.
Regards,
Abhinav Agarwal