Mailing-List: contact user-help@hive.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@hive.apache.org
Received-SPF: pass (athena.apache.org: domain of kwiley@keithwiley.com
 designates 69.56.148.7 as permitted sender)
Content-Type: text/plain; charset=us-ascii
Mime-Version: 1.0 (Apple Message framework v1085)
Subject: Re: Need rank()
From: Keith Wiley <kwiley@keithwiley.com>
In-Reply-To: 
 <CACYx2-+5e3soi5Fpv0jkKdXY8JY3GYv7uDjUZO5em1F5AS8VjQ@mail.gmail.com>
Date: Tue, 2 Apr 2013 12:51:20 -0700
Content-Transfer-Encoding: quoted-printable
Message-Id: <396B47A9-57A2-4E78-89BF-8493C0D8A2C9@keithwiley.com>
References: <10A7E036-778F-410E-84B1-4759B5E21D7D@keithwiley.com>
 <CAKKt98TTjKxiGcCFmWFLjS+nT9AuPbxS2Ou_oYTQY8d_osPy2Q@mail.gmail.com>
 <844940D9-C255-41C7-BC72-86BCF8A48F9E@keithwiley.com>
 <CACYx2-+5e3soi5Fpv0jkKdXY8JY3GYv7uDjUZO5em1F5AS8VjQ@mail.gmail.com>
To: user@hive.apache.org

Yep, the original article is definitely erroneous in this regard.  I =
figured out that eventually.  I'm not sure how much I can trust that =
resource now.  I may have to look elsewhere.  I agree that Edward's =
description is pretty good, but as I said earlier, I can't actually use =
his code, so I'm trying to cobble a workable solution together from the =
various resources available.  Ritesh's article, despite the error in the =
Hive syntax, is still useful in that it enables one to quickly compile a =
simple rank jar without relying on git, maven, or other project =
dependencies -- problems which have plagued me with Edward's approach.  =
So, if I can use Ritesh's method to write a simple rank function, and =
Edward's accurate description of how to construct the query, then I can =
put all the pieces together into a workable solution.

I'll let you know if I get it.

On Apr 2, 2013, at 10:56 , Igor Tatarinov wrote:

> You are getting the error because you are ORDERing BY rank but rank is =
not in the top SELECT
>=20
> Also, DISTRIBUTE BY/SORT BY are done after SELECT so you have to use a =
subquery:
> SELECT ..., rank(user)
> FROM (SELECT ... DISTRIBUTE BY ... SORT BY)
>=20
> igor
> decide.com
>=20
>=20
> On Tue, Apr 2, 2013 at 10:03 AM, Keith Wiley <kwiley@keithwiley.com> =
wrote:
> On Apr 1, 2013, at 16:12 , Alexander Pivovarov wrote:
>=20
> > =
http://ragrawal.wordpress.com/2011/11/18/extract-top-n-records-in-each-gro=
up-in-hadoophive/
>=20
> 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:
>=20
> hive> describe test;
> OK
> user    string
> category        string
> value   int
> Time taken: 0.082 seconds
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D
> 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
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D
> 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>
>=20
> =
__________________________________________________________________________=
______
> Keith Wiley     kwiley@keithwiley.com     keithwiley.com    =
music.keithwiley.com
>=20
> "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
> =
__________________________________________________________________________=
______
>=20
>=20


=
__________________________________________________________________________=
______
Keith Wiley     kwiley@keithwiley.com     keithwiley.com    =
music.keithwiley.com

"Luminous beings are we, not this crude matter."
                                           --  Yoda
=
__________________________________________________________________________=
______