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

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 <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED] <[EMAIL PROTECTED]>
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

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 <[EMAIL PROTECTED]>
To: '[EMAIL PROTECTED]' <[EMAIL PROTECTED]>
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 <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED] <[EMAIL PROTECTED]>
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

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 <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>>
To: '[EMAIL PROTECTED]<mailto:'[EMAIL PROTECTED]>' <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>>
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 <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>>
To: [EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]> <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>>
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

Very cool. What is the non strict option for?

Thanks,
Ranjith

________________________________
From: Ashish Thusoo <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]> <[EMAIL PROTECTED]>
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 <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>>
To: '[EMAIL PROTECTED]<mailto:'[EMAIL PROTECTED]>' <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>>
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 <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>>
To: [EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]> <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>>
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