hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Steven Wong <>
Subject RE: Last value for a column
Date Sat, 28 Jan 2012 04:28:26 GMT
Other than writing a custom UDAF or TRANSFORM script, a somewhat ugly way is something like:

SELECT user_id, split(max(concat(time, '_', colour)), '_')[1]
GROUP BY user_id

From: []
Sent: Thursday, January 26, 2012 3:24 AM
Subject: Last value for a column

Dear all,
I am struggling with a Hive query where I am trying to get the last value for a column.

Let say I have a table T with three columns: user_id, time, colour and I want to know for
each user_id what is its last colour value.

At the moment I am using the following (naïve) query:

SELECT T1.user_id, T1.lastTime, T2.colour FROM (
              SELECT user_id, max(Time) AS lastTime FROM T GROUP BY user_id ) T1
             JOIN T T2 ON (T1.user_id=T2.user_id AND T2.time=T1.lastTime)

I am not happy with this query of course since I have to read the table T twice.

So I have three questions:

1)      Is there a way to re-write this query in a more efficient way ?

2)      Wouldn't that be better to  stream the output of a distributed by on user_id into
a python script and print out only one row per user_id  (assuming  sorted by user_id and time)

3)      Couldn't I create the table T with cluster by on user_id and sorted by time at the
first place and then use this structure to extract the rows I am interested in ?

Many thanks for your help,


Ce message et ses pieces jointes peuvent contenir des informations confidentielles ou privilegiees
et ne doivent donc

pas etre diffuses, exploites ou copies sans autorisation. Si vous avez recu ce message par
erreur, veuillez le signaler

a l'expediteur et le detruire ainsi que les pieces jointes. Les messages electroniques etant
susceptibles d'alteration,

France Telecom - Orange decline toute responsabilite si ce message a ete altere, deforme ou
falsifie. Merci

This message and its attachments may contain confidential or privileged information that may
be protected by law;

they should not be distributed, used or copied without authorization.

If you have received this email in error, please notify the sender and delete this message
and its attachments.

As emails may be altered, France Telecom - Orange shall not be liable if this message was
modified, changed or falsified.

Thank you.

View raw message