Why can’t I recognize the alias in the selected part?

Question

Here's the scenario: When I call hql as follows, it tells me that it cannot find an alias for u1.

hive> select user as u1, url as u2 from rank_test where u1 != ""; FAILED: SemanticException [Error 10004]: Line 1:50 Invalid table alias or column reference 'u1': (possible column names are: user, url) 

This problem is the same as when trying to use count(*) as cnt . Can someone give me some hint on how to use an alias in where clause? Many thanks!

 hive> select user, count(*) as cnt from rank_test where cnt >= 2 group by user; FAILED: ParseException line 1:58 missing EOF at 'where' near 'user' 

Answer 1

The where clause is evaluated before the select clause, so you cannot reference the selection of aliases in the where clause.

However, you can refer to aliases from the view.

 select * from ( select user as u1, url as u2 from rank_test ) t1 where u1 <> ""; select * from ( select user, count(*) as cnt from rank_test group by user ) t1 where cnt >= 2; 

Side note: a more efficient way to write the last request would be

 select user, count(*) as cnt from rank_test group by user having count(*) >= 2 

If I remember correctly, you can refer to the alias in having ie having cnt >= 2

Answer 2

I was able to use Alias ​​in my Hive select expression using the callback character ``.

 SELECT COL_01 AS `Column_A`; 

The above solution worked for Hive version 1.2.1.

link link

Answer 3

Thank you buddy, it helped me a lot!