How to write Group by and Order by query with column position number in Hive
Contents
Hive query with Group by and order by
While we write the group by expression in Hive, we need to specify the column name instead of position number. If we specify the position number of the column in the group by expression, it will throw the error as “the position alias will be ignored“.
But we can resolve this issue by setting the following hive configuration property to true.
|
1 |
set hive.groupby.orderby.position.alias=true; |
By default, the hive.groupby.orderby.position.alias property is set as false in Hive
0.11.0 through 2.2.0
Sample table
Example for position alias property
The table product_details contains the two columns such as product_id and name.Here we are grouping the products name and trying to get the count using the group by expression.
Instead of mentioning the group by column name, we have mentioned position number 1 in group by expression and got the below error.
|
1 2 3 4 5 6 7 8 9 10 |
hive> select name,count(*) as cnt from product_details group by 1 order by cnt desc; Warning: Using constant number 1 in group by. If you try to use position alias when hive.groupby.orderby.position.alias is false, the position alias will be ignored. FAILED: SemanticException [Error 10025]: Line 1:7 Expression not in GROUP BY key 'name' |
Set the position alias property and run the query in Hive
Columns can be specified by position if hive.groupby.orderby.position.alias is set to true (the default is false). So setting set hive.groupby.orderby.position.alias=true;
Recommended Articles
