Beehive: SELECT AS and GROUP BY

Question

I have a hive request 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 request fails

FAILED: semantic analysis error: row 1: 175 Invalid table alias or ReferenceModMod column

"LoggerTable" is a table with a table with all columns of row type.

Any workaround for this problem?

Answer 1

Try the following:

 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; 

Answer 2

In Hive 0.11.0 and later, columns can be specified by position if hive.groupby.orderby.position.alias set to true . Make sure 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;