Home | About | Sematext search-lucene.com search-hadoop.com
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB
 Search Hadoop and all its subprojects:

Switch to Threaded View
Hive >> mail # user >> Re: Need rank()


Copy link to this message
-
Re: Need rank()
I am not sure about this
but you defined a rank function and then aliased the column as rank itself.
Can this cause the issue?

>From the error it looks like it could not figure out which column is rank
in the where clause "WHERE rank > 1"

from Edward's blog the correct query looks similar  but his function is
named as p_rank

SELECT
 category,country,product,sales,rank
FROM (
 SELECT
   category,country,product,sales,
   p_rank(category, country) rank
 FROM (
   SELECT
     category,country,product,
     sales
   FROM p_rank_demo
   DISTRIBUTE BY
     category,country
   SORT BY
     category,country,sales desc) t1) t2
WHERE rank <= 3
On Tue, Apr 2, 2013 at 10:45 PM, Keith Wiley <[EMAIL PROTECTED]> wrote:

> I did all that, I just didn't paste it into the email.  That isn't the
> problem.  Sorry for the confusion.
>
> hive> add jar Rank.jar;
> hive> create temporary function rank as 'com.example.hive.udf.Rank';
>
> BTW, small typo, the condition at the end uses less-then, not
> greater-then...obviously...but that isn't the problem either.
>
> On Apr 2, 2013, at 10:06 , Nitin Pawar wrote:
>
> > I dont see you added rank in the classpath and defined rank function
> >
> > can you try add jar and define the function and try again
> >
> >
> >
> > On Tue, Apr 2, 2013 at 10:33 PM, Keith Wiley <[EMAIL PROTECTED]>
> wrote:
> > On Apr 1, 2013, at 16:12 , Alexander Pivovarov wrote:
> >
> > >
> http://ragrawal.wordpress.com/2011/11/18/extract-top-n-records-in-each-group-in-hadoophive/
> >
> > Is there any possibility there is a bug in Ritesh Agrawal's query
> statement from that article?  I created a test table with the exact column
> names from the example in the article and used a minimally altered version
> of the command (I removed the where clause to simplify things a bit) and
> got an error which suggests there is something slightly wrong with the
> command (or perhaps the table has to be configured a special way).  Here's
> what I get when I almost perfectly duplicate that example:
> >
> > hive> describe test;
> > OK
> > user    string
> > category        string
> > value   int
> > Time taken: 0.082 seconds
> > =================================================> > hive> select * from test;
> > OK
> > user1   cat1    1
> > user1   cat1    2
> > user1   cat1    3
> > user1   cat2    10
> > user1   cat2    20
> > user1   cat2    30
> > user2   cat1    11
> > user2   cat1    21
> > user2   cat1    31
> > user2   cat2    5
> > user2   cat2    6
> > user2   cat2    7
> > Time taken: 0.202 seconds
> > =================================================> > hive> SELECT user, category, value
> >     > FROM (
> >     > SELECT user, category, rank(user) as rank, value
> >     > FROM test
> >     > DISTRIBUTE BY user
> >     > SORT BY user, value desc
> >     > ) a
> >     > WHERE rank > 1
> >     > ORDER BY user, rank;
> > FAILED: Error in semantic analysis: Line 9:15 Invalid table alias or
> column reference 'rank': (possible column names are: _col0, _col1, _col2)
> > hive>
> >
> >
> ________________________________________________________________________________
> > Keith Wiley     [EMAIL PROTECTED]     keithwiley.com
> music.keithwiley.com
> >
> > "What I primarily learned in grad school is how much I *don't* know.
> > Consequently, I left grad school with a higher ignorance to knowledge
> ratio than
> > when I entered."
> >                                            --  Keith Wiley
> >
> ________________________________________________________________________________
> >
> >
> >
> >
> > --
> > Nitin Pawar
>
>
>
> ________________________________________________________________________________
> Keith Wiley     [EMAIL PROTECTED]     keithwiley.com
> music.keithwiley.com
>
> "It's a fine line between meticulous and obsessive-compulsive and a
> slippery
> rope between obsessive-compulsive and debilitatingly slow."
>                                            --  Keith Wiley
>
> ________________________________________________________________________________
Nitin Pawar
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB