You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Bosio Andrea <a....@reply.it> on 2009/10/21 21:59:02 UTC

lead and lag analytic functions

Hello,

I'm trying to convert some Oracle queries to HIVE.
I have some queries using LEAD and LAG analytic functions (see: http://ss64.com/ora/syntax-analytic-lead.html)
These functions give access to values in a row prior or after current position.

Is there a way to do similar things with HIVE?

Thanks.

--
Andrea Bosio


________________________________
--
The information transmitted is intended for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer.

RE: lead and lag analytic functions

Posted by Bosio Andrea <a....@reply.it>.
Thank you!
I'll try this way.

--
Andrea

From: Amr Awadallah [mailto:aaa@cloudera.com]
Sent: mercoledì 21 ottobre 2009 23.09
To: hive-user@hadoop.apache.org
Subject: Re: lead and lag analytic functions

I am +1 to adding such functions to Hive, opened a JIRA at:

https://issues.apache.org/jira/browse/HIVE-896

There is no straightforward way right now AFAIK.  You can simulate the LAG function using a map transform, just buffer the last n rows that you care about in memory as a sliding window, it will only be a local LAG, won't work across mappers (should be ok if your table is clustered properly). If you want to do global windowing then you would need to do that on the reduce side, but it might lead to really bad performance if you don't set the number of reducers properly.

More on transforms at:

http://wiki.apache.org/hadoop/Hive/LanguageManual/Transform

-- amr

Bosio Andrea wrote:
Hello,

I'm trying to convert some Oracle queries to HIVE.
I have some queries using LEAD and LAG analytic functions (see: http://ss64.com/ora/syntax-analytic-lead.html)
These functions give access to values in a row prior or after current position.

Is there a way to do similar things with HIVE?

Thanks.

--
Andrea Bosio


________________________________
--
The information transmitted is intended for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer.

________________________________
--
The information transmitted is intended for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer.

Re: lead and lag analytic functions

Posted by Amr Awadallah <aa...@cloudera.com>.
I am +1 to adding such functions to Hive, opened a JIRA at:

https://issues.apache.org/jira/browse/HIVE-896

There is no straightforward way right now AFAIK.  You can simulate the 
LAG function using a map transform, just buffer the last n rows that you 
care about in memory as a sliding window, it will only be a local LAG, 
won't work across mappers (should be ok if your table is clustered 
properly). If you want to do global windowing then you would need to do 
that on the reduce side, but it might lead to really bad performance if 
you don't set the number of reducers properly.

More on transforms at:

http://wiki.apache.org/hadoop/Hive/LanguageManual/Transform

-- amr

Bosio Andrea wrote:
>
> Hello,
>
>  
>
> I'm trying to convert some Oracle queries to HIVE.
>
> I have some queries using LEAD and LAG analytic functions (see: 
> http://ss64.com/ora/syntax-analytic-lead.html)
>
> These functions give access to values in a row prior or after current 
> position.
>
>  
>
> Is there a way to do similar things with HIVE?
>
>  
>
> Thanks.
>
>  
>
> /--///
>
> /Andrea Bosio// /
>
>  
>
>
> ------------------------------------------------------------------------
> --
> The information transmitted is intended for the person or entity to 
> which it is addressed and may contain confidential and/or privileged 
> material. Any review, retransmission, dissemination or other use of, 
> or taking of any action in reliance upon, this information by persons 
> or entities other than the intended recipient is prohibited. If you 
> received this in error, please contact the sender and delete the 
> material from any computer.