CODEWITHSUNDEEP
hadoophive
Srinivas
Srinivas

Reputation: 2539

Hive: SELECT AS and GROUP BY

I have a Hive Query like 

SELECT Year, Month, Day, Hours, Minutes,
           cast((cast(Seconds as int)/15) as int)*15
AS secondMod, Count(*) AS PerCount FROM LoggerTable 
 GROUP BY Year, Month, Day, Hours, Minutes, secondMod 
ORDER BY PerCount;

the above query fails with an error

FAILED: Error in semantic analysis: line 1:175 Invalid Table Alias or Column Reference secondMod

'LoggerTable' is a Hive Table with all columns of string type.

Any workaround for this issue?

Upvotes: 9

Views: 35061

Answers (2)

rafaelvalle
rafaelvalle

Reputation: 7073

In Hive 0.11.0 and later, columns can be specified by position if hive.groupby.orderby.position.alias is set to true. Please confirm if the following query works for you.

SET hive.groupby.orderby.position.alias=true;
SELECT Year
       ,Month
       ,Day
       ,Hours
       ,Minutes
       ,cast((cast(Seconds as int)/15) as int)*15 AS secondMod
       ,count(*) AS PerCount 
FROM LoggerTable 
GROUP BY 1, 2, 3, 4, 5, 6
ORDER BY 7;

Upvotes: 4

Alexey Bychkov
Alexey Bychkov

Reputation: 581

Try this:

SELECT Year, Month, Day, Hours, Minutes, 
cast((cast(Seconds as int)/15) as int)*15 
AS secondMod, Count(*) AS PerCount FROM LoggerTable 
 GROUP BY Year, Month, Day, Hours, Minutes, 
   cast((cast(Seconds as int)/15) as int)*15
ORDER BY PerCount;

Upvotes: 12

Related Questions