does your table have column called "rownum"? I think From Philip's mail, it was just an example
On Wed, Apr 11, 2012 at 11:13 AM, <karanveer.si...@barclays.com> wrote: > > When I try using rownum in my Hive QL query, I get: "Invalid column > reference rownum". Am I missing something here? > > Regards, > Karan > > > -----Original Message----- > From: David Kulp [mailto:dk...@fiksu.com] > Sent: 10 April 2012 20:15 > To: user@hive.apache.org > Subject: Re: Lag function in Hive > > New here. Hello all. > > Could you try a self-join, possibly also restricted to partitions? > > E.g. SELECT t2.value - t1.value FROM mytable t1, mytable t2 WHERE > t1.rownum = t2.rownum+1 AND t1.partition=foo AND t2.partition=bar > > If your data is clustered by rownum, then this join should, in theory, be > relatively fast -- especially if it makes sense to exploit partitions. > > -d > > On Apr 10, 2012, at 10:37 AM, <karanveer.si...@barclays.com> < > karanveer.si...@barclays.com> wrote: > > > Makes sense but is not the distribution across nodes for a chunk of > records in that order. > > > > If Hive cannot help me do this, is there another way I can do this? I > tried generating an identifier using the perl script invoked using Hive but > it does not seem to work fine. While the stand alone script works fine, > when the record is created in hive using std output from perl - I see 2 > records for some of the unique identifiers. I explored the possibility of > default data type changes but that does not solve the problem. > > > > Regards, > > Karan > > > > > > -----Original Message----- > > From: Philip Tromans [mailto:philip.j.trom...@gmail.com] > > Sent: 10 April 2012 19:48 > > To: user@hive.apache.org > > Subject: Re: Lag function in Hive > > > > Hi Karan, > > > > To the best of my knowledge, there isn't one. It's also unlikely to > > happen because it's hard to parallelise in a map-reduce way (it > > requires knowing where you are in a result set, and who your > > neighbours are and they in turn need to be present on the same node as > > you which is difficult to guarantee). > > > > Cheers, > > > > Phil. > > > > On 10 April 2012 14:44, <karanveer.si...@barclays.com> wrote: > >> Hi, > >> > >> Is there something like a 'lag' function in HIVE? The requirement is to > >> calculate difference for the same column for every 2 subsequent records. > >> > >> For example. > >> > >> Row, Column A, Column B > >> 1, 10, 100 > >> 2, 20, 200 > >> 3, 30, 300 > >> > >> > >> The result that I need should be like: > >> > >> Row, Column A, Column B, Result > >> 1, 10, 100, NULL > >> 2, 20, 200, 100 (200-100) > >> 3, 30, 300, 100 (300-200) > >> > >> Rgds, > >> Karan > >> > >> > >> > >> > >> > >> This e-mail and any attachments are confidential and intended solely > for the > >> addressee and may also be privileged or exempt from disclosure under > >> applicable law. If you are not the addressee, or have received this > e-mail > >> in error, please notify the sender immediately, delete it from your > system > >> and do not copy, disclose or otherwise act upon any part of this e-mail > or > >> its attachments. > >> > >> Internet communications are not guaranteed to be secure or virus-free. > >> The Barclays Group does not accept responsibility for any loss arising > from > >> unauthorised access to, or interference with, any Internet > communications by > >> any third party, or from the transmission of any viruses. Replies to > this > >> e-mail may be monitored by the Barclays Group for operational or > business > >> reasons. > >> > >> Any opinion or other information in this e-mail or its attachments that > does > >> not relate to the business of the Barclays Group is personal to the > sender > >> and is not given or endorsed by the Barclays Group. > >> > >> Barclays Bank PLC.Registered in England and Wales (registered no. > 1026167). > >> Registered Office: 1 Churchill Place, London, E14 5HP, United Kingdom. > >> > >> Barclays Bank PLC is authorised and regulated by the Financial Services > >> Authority. > > -- Nitin Pawar
