Grokbase Groups Hive user August 2009
FAQ
I have a simple query like this (identify hosts that have more than one
request):

select host, count(1) as cnt from accesslogs where cnt>1 group by host;

and it throws the error Invalid Table Alias or Column Reference cnt.

Is using column alias like this unsupported? Is there another way of
achieving the same query as above?

Search Discussions

  • Raghu Murthy at Aug 21, 2009 at 3:23 am
    The SQL way of doing it is with a HAVING clause. But hive does not support
    it yet. You can use a sub-query to do the same.

    select a.host, a.cnt
    from (select host, count(1) as cnt
    from accesslogs group by host) a
    where a.cnt > 1

    On 8/20/09 6:55 PM, "Vijay" wrote:

    I have a simple query like this (identify hosts that have more than one
    request):

    select host, count(1) as cnt from accesslogs where cnt>1 group by host;

    and it throws the error Invalid Table Alias or Column Reference cnt.

    Is using column alias like this unsupported? Is there another way of achieving
    the same query as above?

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupuser @
categorieshive, hadoop
postedAug 21, '09 at 1:55a
activeAug 21, '09 at 3:23a
posts2
users2
websitehive.apache.org

2 users in discussion

Raghu Murthy: 1 post Vijay: 1 post

People

Translate

site design / logo © 2021 Grokbase