You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Ramasubramanian Narayanan <ra...@gmail.com> on 2012/12/17 13:30:47 UTC
Join not working in HIVE
Hi,
We are using Hive 0.7.0 and we are getting error while using "<=" along
with "join"...
Same query is working fine if we use "=".
*Working Query :*
hive> select count(A1.id) as LVL, A2.id, A2.code, A2.short_name, A2.lft,
A2.rgt from vprd A1 join vprd A2 on (A1.lft = A2.lft and A1.rgt = A2.rgt)
group by A2.id, A2.code, A2.short_name, A2.lft, A2.rgt;
Ended Job = job_201212162216_0019
OK
1 1 4 Treasury Service 1 1000000
1 2 2 Root 2 1000
1 3 Z CKC 2 1001
1 4 A Treasury Service 2001 3000
1 5 OOAQ CODE CASH MANAGEMENT 3 100
1 6 YP00 JPMC Treasury 101 200
1 7 432 Treasury Service 1002 1100
1 ID CODE SHORT_NAME LFT RGT
Time taken: 22.234 seconds
*Getting Error in the below Query :*
*
*
hive> select count(A1.id) as LVL, A2.id, A2.code, A2.short_name, A2.lft,
A2.rgt from vprd A1 join vprd A2 on (A1.lft <= A2.lft and A1.rgt >=
A2.rgt) group by A2.id, A2.code, A2.short_name, A2.lft, A2.rgt;;
*FAILED: Error in semantic analysis: Line 1:110 Both left and right aliases
encountered in JOIN lft*
Please let me know what is the reason for this error.. Is there any way to
make it work?
regards,
Rams
Re: Join not working in HIVE
Posted by Alexander Pivovarov <ap...@gmail.com>.
Hive supports only equi-join
I recommend you to read some hive manual before use it. (e.g.
http://hive.apache.org/docs/r0.9.0/language_manual/joins.html
https://cwiki.apache.org/Hive/languagemanual-joins.html)
on the first sentence it says "Only equality joins, outer joins, and left
semi joins are supported in Hive"
Hive has certain limitations. it also supports map side join, semi join.
You'd better know what they are.
On Mon, Dec 17, 2012 at 6:18 AM, Philip Tromans
<ph...@gmail.com>wrote:
> Hive doesn't support theta joins. Your best bet is to do a full cross join
> between the tables, and put your range conditions into the WHERE clause.
> This may or may not work, depending on the respective sizes of your tables.
>
> The fundamental problem is that parallelising a theta (or range) join via
> Map-Reduce is not trivial, and Hive has no support for it.
>
> Cheers,
>
> Phil.
>
>
> On 17 December 2012 13:55, Nitin Pawar <ni...@gmail.com> wrote:
>
>> can you explain your needs? may be there is another alternate way
>> a query is not of much help
>>
>>
>>
>>
>> On Mon, Dec 17, 2012 at 7:17 PM, Ramasubramanian Narayanan <
>> ramasubramanian.narayanan@gmail.com> wrote:
>>
>>> Hi,
>>>
>>> We are trying to build a tree structure in a table.. hence we have the
>>> left and right limits...
>>> Can't use where clause in that..
>>>
>>> regards,
>>> Rams
>>>
>>> On Mon, Dec 17, 2012 at 6:53 PM, Nitin Pawar <ni...@gmail.com>wrote:
>>>
>>>> hive is not mysql :)
>>>>
>>>>
>>>> On Mon, Dec 17, 2012 at 6:50 PM, Ramasubramanian Narayanan <
>>>> ramasubramanian.narayanan@gmail.com> wrote:
>>>>
>>>>> Hi,
>>>>>
>>>>> But it is working fine in MySql...
>>>>>
>>>>> mysql> select count(A1.id) as LVL, A2.id, A2.code, A2.short_name,
>>>>> A2.lft, A2.rgt from product A1 join product A2 on (A1.lft <= A2.lft and
>>>>> A1.rgt >= A2.rgt) group by A2.id, A2.code, A2.short_name, A2.lft, A2.rgt;
>>>>> +-----+------+------+----------------------+------+---------+
>>>>> | LVL | id | code | short_name | lft | rgt |
>>>>> +-----+------+------+----------------------+------+---------+
>>>>>
>>>>> | 1 | 1 | 4 | Treasury Service | 1 | 1000000 |
>>>>> | 2 | 2 | 2 | Root | 2 | 1000 |
>>>>> | 2 | 3 | Z | CKC | 1001 | 2000 |
>>>>> | 2 | 4 | A | Treasury Service | 2001 | 3000 |
>>>>> | 3 | 5 | OOAQ | CODE CASH MANAGEMENT | 3 | 100 |
>>>>> | 3 | 6 | YP00 | JPMC Treasury | 101 | 200 |
>>>>> | 3 | 7 | 432 | Treasury Service | 1002 | 1100 |
>>>>> +-----+------+------+----------------------+------+---------+
>>>>>
>>>>>
>>>>> regards,
>>>>> Rams
>>>>>
>>>>> On Mon, Dec 17, 2012 at 6:33 PM, Nitin Pawar <ni...@gmail.com>wrote:
>>>>>
>>>>>> select count(A1.id) as LVL, A2.id, A2.code, A2.short_name, A2.lft,
>>>>>> A2.rgt from vprd A1 join vprd A2 on (A1.lft <= A2.lft and A1.rgt >= A2.rgt)
>>>>>> where A.2rgt=1 and A2.lft=1 group by A2.id, A2.code, A2.short_name, A2.lft,
>>>>>> A2.rgt
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>> --
>>>> Nitin Pawar
>>>>
>>>
>>>
>>
>>
>> --
>> Nitin Pawar
>>
>
>
Re: Join not working in HIVE
Posted by Philip Tromans <ph...@gmail.com>.
Hive doesn't support theta joins. Your best bet is to do a full cross join
between the tables, and put your range conditions into the WHERE clause.
This may or may not work, depending on the respective sizes of your tables.
The fundamental problem is that parallelising a theta (or range) join via
Map-Reduce is not trivial, and Hive has no support for it.
Cheers,
Phil.
On 17 December 2012 13:55, Nitin Pawar <ni...@gmail.com> wrote:
> can you explain your needs? may be there is another alternate way
> a query is not of much help
>
>
>
>
> On Mon, Dec 17, 2012 at 7:17 PM, Ramasubramanian Narayanan <
> ramasubramanian.narayanan@gmail.com> wrote:
>
>> Hi,
>>
>> We are trying to build a tree structure in a table.. hence we have the
>> left and right limits...
>> Can't use where clause in that..
>>
>> regards,
>> Rams
>>
>> On Mon, Dec 17, 2012 at 6:53 PM, Nitin Pawar <ni...@gmail.com>wrote:
>>
>>> hive is not mysql :)
>>>
>>>
>>> On Mon, Dec 17, 2012 at 6:50 PM, Ramasubramanian Narayanan <
>>> ramasubramanian.narayanan@gmail.com> wrote:
>>>
>>>> Hi,
>>>>
>>>> But it is working fine in MySql...
>>>>
>>>> mysql> select count(A1.id) as LVL, A2.id, A2.code, A2.short_name,
>>>> A2.lft, A2.rgt from product A1 join product A2 on (A1.lft <= A2.lft and
>>>> A1.rgt >= A2.rgt) group by A2.id, A2.code, A2.short_name, A2.lft, A2.rgt;
>>>> +-----+------+------+----------------------+------+---------+
>>>> | LVL | id | code | short_name | lft | rgt |
>>>> +-----+------+------+----------------------+------+---------+
>>>>
>>>> | 1 | 1 | 4 | Treasury Service | 1 | 1000000 |
>>>> | 2 | 2 | 2 | Root | 2 | 1000 |
>>>> | 2 | 3 | Z | CKC | 1001 | 2000 |
>>>> | 2 | 4 | A | Treasury Service | 2001 | 3000 |
>>>> | 3 | 5 | OOAQ | CODE CASH MANAGEMENT | 3 | 100 |
>>>> | 3 | 6 | YP00 | JPMC Treasury | 101 | 200 |
>>>> | 3 | 7 | 432 | Treasury Service | 1002 | 1100 |
>>>> +-----+------+------+----------------------+------+---------+
>>>>
>>>>
>>>> regards,
>>>> Rams
>>>>
>>>> On Mon, Dec 17, 2012 at 6:33 PM, Nitin Pawar <ni...@gmail.com>wrote:
>>>>
>>>>> select count(A1.id) as LVL, A2.id, A2.code, A2.short_name, A2.lft,
>>>>> A2.rgt from vprd A1 join vprd A2 on (A1.lft <= A2.lft and A1.rgt >= A2.rgt)
>>>>> where A.2rgt=1 and A2.lft=1 group by A2.id, A2.code, A2.short_name, A2.lft,
>>>>> A2.rgt
>>>>
>>>>
>>>>
>>>
>>>
>>> --
>>> Nitin Pawar
>>>
>>
>>
>
>
> --
> Nitin Pawar
>
Re: Join not working in HIVE
Posted by Nitin Pawar <ni...@gmail.com>.
can you explain your needs? may be there is another alternate way
a query is not of much help
On Mon, Dec 17, 2012 at 7:17 PM, Ramasubramanian Narayanan <
ramasubramanian.narayanan@gmail.com> wrote:
> Hi,
>
> We are trying to build a tree structure in a table.. hence we have the
> left and right limits...
> Can't use where clause in that..
>
> regards,
> Rams
>
> On Mon, Dec 17, 2012 at 6:53 PM, Nitin Pawar <ni...@gmail.com>wrote:
>
>> hive is not mysql :)
>>
>>
>> On Mon, Dec 17, 2012 at 6:50 PM, Ramasubramanian Narayanan <
>> ramasubramanian.narayanan@gmail.com> wrote:
>>
>>> Hi,
>>>
>>> But it is working fine in MySql...
>>>
>>> mysql> select count(A1.id) as LVL, A2.id, A2.code, A2.short_name,
>>> A2.lft, A2.rgt from product A1 join product A2 on (A1.lft <= A2.lft and
>>> A1.rgt >= A2.rgt) group by A2.id, A2.code, A2.short_name, A2.lft, A2.rgt;
>>> +-----+------+------+----------------------+------+---------+
>>> | LVL | id | code | short_name | lft | rgt |
>>> +-----+------+------+----------------------+------+---------+
>>>
>>> | 1 | 1 | 4 | Treasury Service | 1 | 1000000 |
>>> | 2 | 2 | 2 | Root | 2 | 1000 |
>>> | 2 | 3 | Z | CKC | 1001 | 2000 |
>>> | 2 | 4 | A | Treasury Service | 2001 | 3000 |
>>> | 3 | 5 | OOAQ | CODE CASH MANAGEMENT | 3 | 100 |
>>> | 3 | 6 | YP00 | JPMC Treasury | 101 | 200 |
>>> | 3 | 7 | 432 | Treasury Service | 1002 | 1100 |
>>> +-----+------+------+----------------------+------+---------+
>>>
>>>
>>> regards,
>>> Rams
>>>
>>> On Mon, Dec 17, 2012 at 6:33 PM, Nitin Pawar <ni...@gmail.com>wrote:
>>>
>>>> select count(A1.id) as LVL, A2.id, A2.code, A2.short_name, A2.lft,
>>>> A2.rgt from vprd A1 join vprd A2 on (A1.lft <= A2.lft and A1.rgt >= A2.rgt)
>>>> where A.2rgt=1 and A2.lft=1 group by A2.id, A2.code, A2.short_name, A2.lft,
>>>> A2.rgt
>>>
>>>
>>>
>>
>>
>> --
>> Nitin Pawar
>>
>
>
--
Nitin Pawar
Re: Join not working in HIVE
Posted by Ramasubramanian Narayanan <ra...@gmail.com>.
Hi,
We are trying to build a tree structure in a table.. hence we have the left
and right limits...
Can't use where clause in that..
regards,
Rams
On Mon, Dec 17, 2012 at 6:53 PM, Nitin Pawar <ni...@gmail.com>wrote:
> hive is not mysql :)
>
>
> On Mon, Dec 17, 2012 at 6:50 PM, Ramasubramanian Narayanan <
> ramasubramanian.narayanan@gmail.com> wrote:
>
>> Hi,
>>
>> But it is working fine in MySql...
>>
>> mysql> select count(A1.id) as LVL, A2.id, A2.code, A2.short_name, A2.lft,
>> A2.rgt from product A1 join product A2 on (A1.lft <= A2.lft and A1.rgt >=
>> A2.rgt) group by A2.id, A2.code, A2.short_name, A2.lft, A2.rgt;
>> +-----+------+------+----------------------+------+---------+
>> | LVL | id | code | short_name | lft | rgt |
>> +-----+------+------+----------------------+------+---------+
>>
>> | 1 | 1 | 4 | Treasury Service | 1 | 1000000 |
>> | 2 | 2 | 2 | Root | 2 | 1000 |
>> | 2 | 3 | Z | CKC | 1001 | 2000 |
>> | 2 | 4 | A | Treasury Service | 2001 | 3000 |
>> | 3 | 5 | OOAQ | CODE CASH MANAGEMENT | 3 | 100 |
>> | 3 | 6 | YP00 | JPMC Treasury | 101 | 200 |
>> | 3 | 7 | 432 | Treasury Service | 1002 | 1100 |
>> +-----+------+------+----------------------+------+---------+
>>
>>
>> regards,
>> Rams
>>
>> On Mon, Dec 17, 2012 at 6:33 PM, Nitin Pawar <ni...@gmail.com>wrote:
>>
>>> select count(A1.id) as LVL, A2.id, A2.code, A2.short_name, A2.lft,
>>> A2.rgt from vprd A1 join vprd A2 on (A1.lft <= A2.lft and A1.rgt >= A2.rgt)
>>> where A.2rgt=1 and A2.lft=1 group by A2.id, A2.code, A2.short_name, A2.lft,
>>> A2.rgt
>>
>>
>>
>
>
> --
> Nitin Pawar
>
Re: Join not working in HIVE
Posted by Nitin Pawar <ni...@gmail.com>.
hive is not mysql :)
On Mon, Dec 17, 2012 at 6:50 PM, Ramasubramanian Narayanan <
ramasubramanian.narayanan@gmail.com> wrote:
> Hi,
>
> But it is working fine in MySql...
>
> mysql> select count(A1.id) as LVL, A2.id, A2.code, A2.short_name, A2.lft,
> A2.rgt from product A1 join product A2 on (A1.lft <= A2.lft and A1.rgt >=
> A2.rgt) group by A2.id, A2.code, A2.short_name, A2.lft, A2.rgt;
> +-----+------+------+----------------------+------+---------+
> | LVL | id | code | short_name | lft | rgt |
> +-----+------+------+----------------------+------+---------+
>
> | 1 | 1 | 4 | Treasury Service | 1 | 1000000 |
> | 2 | 2 | 2 | Root | 2 | 1000 |
> | 2 | 3 | Z | CKC | 1001 | 2000 |
> | 2 | 4 | A | Treasury Service | 2001 | 3000 |
> | 3 | 5 | OOAQ | CODE CASH MANAGEMENT | 3 | 100 |
> | 3 | 6 | YP00 | JPMC Treasury | 101 | 200 |
> | 3 | 7 | 432 | Treasury Service | 1002 | 1100 |
> +-----+------+------+----------------------+------+---------+
>
>
> regards,
> Rams
>
> On Mon, Dec 17, 2012 at 6:33 PM, Nitin Pawar <ni...@gmail.com>wrote:
>
>> select count(A1.id) as LVL, A2.id, A2.code, A2.short_name, A2.lft, A2.rgt
>> from vprd A1 join vprd A2 on (A1.lft <= A2.lft and A1.rgt >= A2.rgt) where
>> A.2rgt=1 and A2.lft=1 group by A2.id, A2.code, A2.short_name, A2.lft, A2.rgt
>
>
>
--
Nitin Pawar
Re: Join not working in HIVE
Posted by Ramasubramanian Narayanan <ra...@gmail.com>.
Hi,
But it is working fine in MySql...
mysql> select count(A1.id) as LVL, A2.id, A2.code, A2.short_name, A2.lft,
A2.rgt from product A1 join product A2 on (A1.lft <= A2.lft and A1.rgt >=
A2.rgt) group by A2.id, A2.code, A2.short_name, A2.lft, A2.rgt;
+-----+------+------+----------------------+------+---------+
| LVL | id | code | short_name | lft | rgt |
+-----+------+------+----------------------+------+---------+
| 1 | 1 | 4 | Treasury Service | 1 | 1000000 |
| 2 | 2 | 2 | Root | 2 | 1000 |
| 2 | 3 | Z | CKC | 1001 | 2000 |
| 2 | 4 | A | Treasury Service | 2001 | 3000 |
| 3 | 5 | OOAQ | CODE CASH MANAGEMENT | 3 | 100 |
| 3 | 6 | YP00 | JPMC Treasury | 101 | 200 |
| 3 | 7 | 432 | Treasury Service | 1002 | 1100 |
+-----+------+------+----------------------+------+---------+
regards,
Rams
On Mon, Dec 17, 2012 at 6:33 PM, Nitin Pawar <ni...@gmail.com>wrote:
> select count(A1.id) as LVL, A2.id, A2.code, A2.short_name, A2.lft, A2.rgt
> from vprd A1 join vprd A2 on (A1.lft <= A2.lft and A1.rgt >= A2.rgt) where
> A.2rgt=1 and A2.lft=1 group by A2.id, A2.code, A2.short_name, A2.lft, A2.rgt
Re: Join not working in HIVE
Posted by Nitin Pawar <ni...@gmail.com>.
are you trying to do a self join with less than and greater than without
having anything in where clause
I doubt that is going to work because less than and greater than will
always need a upper or lower limit to start the comparison (that includes
even in join statement)
so try something like
select count(A1.id) as LVL, A2.id, A2.code, A2.short_name, A2.lft, A2.rgt
from vprd A1 join vprd A2 on (A1.lft <= A2.lft and A1.rgt >= A2.rgt) where
A.2rgt=1 and A2.lft=1 group by A2.id, A2.code, A2.short_name, A2.lft, A2.rgt
On Mon, Dec 17, 2012 at 6:00 PM, Ramasubramanian Narayanan <
ramasubramanian.narayanan@gmail.com> wrote:
> Hi,
>
> We are using Hive 0.7.0 and we are getting error while using "<=" along
> with "join"...
>
> Same query is working fine if we use "=".
>
> *Working Query :*
>
> hive> select count(A1.id) as LVL, A2.id, A2.code, A2.short_name, A2.lft,
> A2.rgt from vprd A1 join vprd A2 on (A1.lft = A2.lft and A1.rgt = A2.rgt)
> group by A2.id, A2.code, A2.short_name, A2.lft, A2.rgt;
> Ended Job = job_201212162216_0019
> OK
> 1 1 4 Treasury Service 1 1000000
> 1 2 2 Root 2 1000
> 1 3 Z CKC 2 1001
> 1 4 A Treasury Service 2001 3000
> 1 5 OOAQ CODE CASH MANAGEMENT 3 100
> 1 6 YP00 JPMC Treasury 101 200
> 1 7 432 Treasury Service 1002 1100
> 1 ID CODE SHORT_NAME LFT RGT
> Time taken: 22.234 seconds
>
> *Getting Error in the below Query :*
> *
> *
> hive> select count(A1.id) as LVL, A2.id, A2.code, A2.short_name, A2.lft,
> A2.rgt from vprd A1 join vprd A2 on (A1.lft <= A2.lft and A1.rgt >=
> A2.rgt) group by A2.id, A2.code, A2.short_name, A2.lft, A2.rgt;;
> *FAILED: Error in semantic analysis: Line 1:110 Both left and right
> aliases encountered in JOIN lft*
>
>
>
> Please let me know what is the reason for this error.. Is there any way to
> make it work?
>
> regards,
> Rams
>
--
Nitin Pawar