Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Hive: SELECT AS and GROUP BY

Tags:

hadoop

hive

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?

like image 797
Srinivas Avatar asked Sep 26 '12 10:09

Srinivas


People also ask

How do you Groupby in Hive?

Group by query: Group by clause use columns on Hive tables for grouping particular column values mentioned with the group by. For whatever the column name we are defining a “groupby” clause the query will selects and display results by grouping the particular column values.

What is difference between sort by and order by in Hive?

The difference between "order by" and "sort by" is that the former guarantees total order in the output while the latter only guarantees ordering of the rows within a reducer. If there are more than one reducer, "sort by" may give partially ordered final results.


1 Answers

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;
like image 98
rafaelvalle Avatar answered Oct 12 '22 05:10

rafaelvalle