Geek Answers Handbook

Beehive: a cleaner way to SELECT AS and GROUP BY

I am trying to write Hive Sql as if

SELECT count(1), substr(date, 1, 4) as year
FROM ***
GROUP BY year

But Hive cannot recognize the alias "year", he complains that: FAILED: SemanticException [Error 10004]: Row 1:79 Invalid table alias or column "year"

One solution ( Hive: SELECT AS and GROUP BY ) suggests using 'GROUP BY substr (date, 1, 4)'.

It works! However, in some cases, the value that I want to group can be generated from several lines of hive function code , it is very ugly to write code, for example

SELECT count(1), func1(func2(..........................)) AS something
FROM ***
GROUP BY func1(func2(..........................))

Is there any clean way in hive? Any suggestions?

+4
source share
3

By . , SET hive.groupby.orderby.position.alias = false; ( 0.12)

SELECT count(1), substr(date, 1, 4) as year  
FROM ***
GROUP BY 2;
+5

Hive 0.11.0 , hive.groupby.orderby.position.alias true ( false). set hive.groupby.orderby.position.alias=true; .hql( .hiverc ) , group by 2 . :

+4

, , GROUP BY :

SELECT count(*) , year FROM 
(
   SELECT substr(date, 1, 4) as year FORM ***
) inner
GROUP BY year

GL!

0

More articles:


All Articles