Home | About | Sematext search-lucene.com search-hadoop.com
 Search Hadoop and all its subprojects:

Switch to Threaded View
Hive, mail # user - Re: date datatype in hive


Copy link to this message
-
Re: date datatype in hive
Nitin Pawar 2012-06-27, 09:52
soham,

the reason u r getting the error is there may be multiple names associated
with one country
so when you do  a group by there is no unique output to generate

so group by country, name will give you unique results

same is the case with select *

in case you want to sort the output you may try with order by country
instead of group by
if you want to omit duplicates you can use distinct

so try something like

select count(*), country from users_info group by country
select count(*) from users_info group by country
select distinct name, country from users_info order by country
select name, county from users_info group by country, name

all above should work unless i made a typo error :)

On Wed, Jun 27, 2012 at 3:11 PM, Soham Sardar <[EMAIL PROTECTED]>wrote:

> Thanks Bejoy and Nitin for replyin
> well now i got that thing ryt but then i have serious issues
>
> in hive querying like some of such queries are
>
> 1)  select * from users_info group by country;
>
> and this is giving me only the countries grouped alphabetically and
> not any other information
>
> 2) select name,country from users_info group by country;
>
> and this gives me an error
> FAILED: Error in semantic analysis: Line 1:7 Expression not in GROUP
> BY key 'name'
>
> can someone help me out with this :)
>
>
>
>
> On Wed, Jun 27, 2012 at 1:39 PM, Nitin Pawar <[EMAIL PROTECTED]>
> wrote:
> > soham,
> >
> > in your query
> > hive> select name from users_info  group by country;
> > FAILED: Error in semantic analysis: Line 1:7 Expression not in GROUP
> > BY key 'name'
> >
> > when you run group by it needs to be present in the select columns as
> well
> > thats what Bejoy suggested
> >
> > On Wed, Jun 27, 2012 at 12:05 PM, Soham Sardar <
> [EMAIL PROTECTED]>
> > wrote:
> >>
> >> Hey bejoy thats the problem i am not able to run the group by query in
> >> hive i dunno whether i m making a mistake or some thing
> >> see my previoius reply to this same thread i put up the same issue ...
> >>
> >>
> >> On Wed, Jun 27, 2012 at 12:02 PM, Bejoy KS <[EMAIL PROTECTED]> wrote:
> >> > Hi Soham
> >> >
> >> > Rewrite your query with the columns in Group By included in Select as
> >> > well. Something like
> >> >
> >> > select country,name from users_info  group by country;
> >> >
> >> > Regards
> >> > Bejoy KS
> >> >
> >> > Sent from handheld, please excuse typos.
> >> >
> >> > -----Original Message-----
> >> > From: Soham Sardar <[EMAIL PROTECTED]>
> >> > Date: Wed, 27 Jun 2012 11:57:23
> >> > To: <[EMAIL PROTECTED]>; Bejoy Ks<[EMAIL PROTECTED]>
> >> > Reply-To: [EMAIL PROTECTED]
> >> > Subject: Re: date datatype in hive
> >> >
> >> > And btw does group by works in hive because the same wuery i am
> >> > running in mysql and its working fine but its failing in hive
> >> >
> >> > select name from users_info  group by country;
> >> >
> >> > in mysql its working but whn i try to run it in hive its telling
> >> >
> >> > hive> select name from users_info  group by country;
> >> > FAILED: Error in semantic analysis: Line 1:7 Expression not in GROUP
> >> > BY key 'name'
> >> >
> >> > I wanna know why is it failing
> >> >
> >> >
> >> > On Wed, Jun 27, 2012 at 10:19 AM, Soham Sardar
> >> > <[EMAIL PROTECTED]> wrote:
> >> >> See Bejoy and Everyone ,
> >> >> I have two tables
> >> >> one users_info and one users_audit
> >> >> in hive ..
> >> >>
> >> >> hive> desc users_audit;
> >> >> OK
> >> >> id      int
> >> >> userid  int
> >> >> logtime string
> >> >>
> >> >> hive> desc users_info;
> >> >> OK
> >> >> id      int
> >> >> name    string
> >> >> age     int
> >> >> country string
> >> >> gender  string
> >> >> bday    string
> >> >>
> >> >> now i have given the description of the tables in hive
> >> >>
> >> >> my goal is to find:
> >> >> the maximum number of users loging - in at between a certain time
> >> >> frame say 1PM to 2PM- belonging to a specific country
> >> >>
> >> >> for example
> >> >> in between 1PM to 2PM there are 10000 users loging into and 5500 are

Nitin Pawar