hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mapred Learn <mapred.le...@gmail.com>
Subject Re: hive case and group-by statement
Date Mon, 05 Dec 2011 07:29:27 GMT
Thanks Bejoy !
It worked.

Sent from my iPhone

On Dec 4, 2011, at 3:10 AM, Bejoy Ks <bejoy_ks@yahoo.com> wrote:

> Hi JJ
>        The error message you are seeing now is because of your usage of groupby. You
need to specify it as 'group by' , a space between group and by. Even if you specify it as
'group by' the query wont execute because of a limitation with GROUP BY in hive. It would
give some parse error as 'Expression not in GROUP BY key'. For details refer the JIRA  2099
which is still open
> https://issues.apache.org/jira/browse/HIVE-2099
> 
> But here you can gracefully achieve your goal with the usage of a Temporary View, as
given below
> 
> CREATE VIEW temp_view_1 AS
> SELECT A,
> CASE
>  WHEN B IN(1,2) THEN 'Type A'
>  ELSE 'Type B'
> END AS B,
> C from table_a;
> 
> SELECT * FROM temp_view_1 GROUP BY A,B,C;
> 
> DROP VIEW temp_view_1;
> 
> The creation and usage of views won't create much significant overhead on your query
execution as well. 
> 
> Hope it helps !...
> 
> Regards
> Bejoy.K.S
> 
> From: Mapred Learn <mapred.learn@gmail.com>
> To: user@hive.apache.org 
> Sent: Sunday, December 4, 2011 2:51 PM
> Subject: hive case and group-by statement
> 
> Hi,
> I have a following smaple query:
>  
> select
> A,
> CASE
>  WHEN B IN(1,2) THEN 'Type A'
>  ELSE 'Type B'
> END AS B,
> C
> from table_a
> groupby
> A, B, C;
>  
> But when i run this query, it gives error:
> FAILED: Error in semantic analysis: Line 95:0 Invalid table alias or column reference
entity
>  
> This error is from B defined after 'AS' in CASE statement.
>  
> how can I make this group-by work ?
>  
> Thanks,
> -JJ
> 
>  
> 
> 

Mime
View raw message