hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Alexander Pivovarov <apivova...@gmail.com>
Subject Re: Join not working in HIVE
Date Mon, 17 Dec 2012 20:45:23 GMT
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
<philip.j.tromans@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 <nitinpawar432@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 <nitinpawar432@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 <nitinpawar432@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
>>
>
>

Mime
View raw message