hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rakesh Setty <serak...@yahoo-inc.com>
Subject RE: distinct with union all
Date Mon, 06 Jul 2009 20:19:46 GMT
Hi,

>From the counts, I could see that grouping was happening. The output is too big, so I
am attaching just partial output here

AAAGd`RH_HaA`AZNgA_WDeq 2
AAAZTMIxhpSjx[Oyb       1
AABIxQR_Un]DLGXNwla\`o  3
AAB[_pudLZeuOe  7
AAClxPCotyLn\qC 2
AAEdZeHVNj\[XECl^Amin   3
AAEkewjwSCDfCgmPbxkQEG` 2
AAFQxFnlnImcPKpvQMM     1
AAGbQojol_EXcPk 1
AAHPZBe^Q`UeMBmlhhcAVV  3
AAHs]A_gvZxfKKCI^\      2
AAIAGwnFPsrGFxaHYgOAiCHe        2
AAIjiO]r`rkrobp_xRn     1
AAIwuSGOmu_L`YGCoHrmvub`        1
AAJG[RPx\RvEYljsBgDdR   3
AAJ^BFCNpTXt]wOEir[     1
AAJvlnBfYXNnXulGOHlAeW  2
AAKwheCincrxm_jkbm      2

The number of rows written is varying between the queries

insert overwrite table Tbl1 select distinct username from (select user as username from page_views
pv union all select name as username from users u) ur;

and

insert overwrite table Tbl1Debug select  username, count(1) from (select user as username
from page_views pv union all select name as username from users u) ur group by username;

The query

select count(distinct user) from Tb1;

returns different value compared to the number of rows created by the first query. It is also
different from the query

select count(distinct user) from Tb1Debug;

@Ashish - I had attached the explain plan output in an earlier reply which Namit said looks
fine.

Please let me know what could be the problem.

Thanks,
Rakesh


________________________________
From: Namit Jain [mailto:njain@facebook.com]
Sent: Monday, July 06, 2009 10:03 AM
To: hive-user@hadoop.apache.org
Subject: RE: distinct with union all

Nothing special for these characters.

select  username, count(1) from (select user as username from page_views pv union all select
name as username from users u) ur group by username;

should return the counts.

When you see the duplicate usernames, can you send them -


From: Rakesh Setty [mailto:serakesh@yahoo-inc.com]
Sent: Monday, July 06, 2009 9:45 AM
To: hive-user@hadoop.apache.org
Subject: RE: distinct with union all

Hi Amr,

            The group by clause that you suggest shows that grouping is happening as the count
column suggests. Perhaps it is not happening across the tables?
            I see characters like \, [, _, ^, etc in username. Are these considered as special
characters? If so, is there any workaround to deal with this?

Thanks,
Rakesh

________________________________
From: Amr Awadallah [mailto:aaa@cloudera.com]
Sent: Thursday, July 02, 2009 5:53 PM
To: hive-user@hadoop.apache.org
Subject: Re: distinct with union all


make sure you don't have any leading or trailing spaces (or special characters) for the usernames
being extracted.

also to debug, try to do a select username, count(1) then group by username

-- amr

Rakesh Setty wrote:
Yes, I am getting duplicate usernames.


________________________________
From: Namit Jain [mailto:njain@facebook.com]
Sent: Thursday, July 02, 2009 1:25 PM
To: hive-user@hadoop.apache.org<mailto:hive-user@hadoop.apache.org>
Subject: RE: distinct with union all

Are you getting duplicate usernames ?


From: Rakesh Setty [mailto:serakesh@yahoo-inc.com]
Sent: Thursday, July 02, 2009 12:37 PM
To: hive-user@hadoop.apache.org<mailto:hive-user@hadoop.apache.org>
Subject: distinct with union all

Hi,

I have a query like

select distinct username from (select user as username from page_views pv union all select
name as username from users u) ur;

But I see that result is not actually distinct. Am I missing something here?

Thanks,
Rakesh

Mime
View raw message