Home | About | Sematext search-lucene.com search-hadoop.com
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB
 Search Hadoop and all its subprojects:

Switch to Threaded View
Hive >> mail # user >> Hive Join with distinct rows


Copy link to this message
-
Re: Hive Join with distinct rows
I see. I re-read your first email and you would like to query "select all
the unique ID's in T1 which are not in T2"

Query 1 seems to be doing just fine so I would say that's the way to go. I
personally use "IS" operator when comparing something with NULLs instead of
"=".

There are some optimizations you can read about like semi joins that might
come in handy for this query or queries in the future.

https://cwiki.apache.org/Hive/languagemanual-joins.html

Mark
On Fri, Nov 9, 2012 at 8:00 AM, Praveen Kumar K J V S <
[EMAIL PROTECTED]> wrote:

> Thanks Mark, I do understand that how Hive works with Distinct keyword.
>
> What I was looking for is a solution for my requirement in Hive, I am not
> an expert in SQL, hence looking for suggestions
>
>
> On Fri, Nov 9, 2012 at 9:54 AM, Mark Grover <[EMAIL PROTECTED]>wrote:
>
>> Hi Praveen,
>> Let's take an example:
>> (from
>> https://cwiki.apache.org/Hive/languagemanual-select.html#LanguageManualSelect-ALLandDISTINCTClauses
>> )
>>
>> -- Print out contents of the table
>> hive> SELECT col1, col2 FROM t1;
>> 1 3
>> 1 3
>> 1 4
>> 2 5
>>
>> -- Selects distinct col1, col2 tuple
>> hive> SELECT DISTINCT col1, col2 FROM t1;
>> 1 3
>> 1 4
>> 2 5
>>
>>
>> Similar to the second query above, your Query 2 selects each of the
>> distinct values for <id, url, timestamp> tuple possibly giving you multiple
>> records for a given id on the left side of the join. Consequently you don't
>> get the result you expect.
>>
>> Mark
>>
>> On Thu, Nov 8, 2012 at 6:15 PM, Praveen Kumar K J V S <
>> [EMAIL PROTECTED]> wrote:
>>
>>> Hi,
>>>
>>> I have 2 tables, T1 and T2 of structure columns= {ID, url, timestamp}
>>>
>>> In T1 ID's are repeatable. But T2 ID's are kind of primary key hence
>>> only unique values are present.
>>>
>>> I want to join both tables T1 & T2 such that select all the unique ID's
>>> in T1 which are not in  T2
>>>
>>> I have written 2 queries:
>>>
>>> Query1: select distinct T1.ID from T1 LEFT OUTER JOIN T2 on T1.ID=T2.IDAND
>>> T2.ID=NULL
>>>
>>> this gives me expected results, but with the below I am getting all the
>>> rows in T1
>>>
>>> Query2: select distinct(T1.ID), T1.url, T1.timestamp from T1 LEFT OUTER
>>> JOIN T2 on T1.ID=T2.ID AND T2.ID=NULL
>>>
>>> Can some one point me how to achieve: select all the unique ID's in T1
>>> which are not in T2
>>>
>>> Thanks,
>>> Praveen
>>>
>>>
>>
>
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB