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?
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.
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.
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With