You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Ravi Prasad <ra...@gmail.com> on 2014/11/14 09:08:05 UTC
Analytic functions in Lead & Lag in Hive
Cany anyone suggest, how to use the Lead and lag function in Hive.
I am using Hive* 0.10 version*. Is there any way to make it work in 0.10
version.
I have used the below code in Oracle, But it is not working in Hive.
I need to compare the difference between the previous record and currect
record.
*Oracle code for LAG :-*
SELECT empno,
ename,
job,
sal,
LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_prev,
sal - LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_diff
FROM emp;
*Oracle code for LEAD :-*
SELECT empno,
ename,
job,
sal,
LEAD(sal, 1, 0) OVER (ORDER BY sal) AS sal_next,
LEAD(sal, 1, 0) OVER (ORDER BY sal) - sal AS sal_diff
FROM emp;
--
----------------------------------------------
Regards,
Prasad
RE: Analytic functions in Lead & Lag in Hive
Posted by "Dhandapani, Karthik" <Ka...@CVSCaremark.com>.
Ravi,
I don’t see any other option other than doing a self join.
If not create another table with exact same data, and join both the tables with the right join conditions to join the previous record.
Thanks,
Karthik
From: Ravi Prasad [mailto:raviprasad29@gmail.com]
Sent: Monday, November 17, 2014 12:41 AM
To: user@hive.apache.org
Subject: Re: Analytic functions in Lead & Lag in Hive
Hi Karthik,
Thanks. But we are using Hive 0.10 version of Hive.
Is there any alternative way available to implement this Lead / Lag functionality in Hive 0.10 version
Regards
Raviprasad
On Fri, Nov 14, 2014 at 7:51 PM, Dhandapani, Karthik <Ka...@cvscaremark.com>> wrote:
Ravi,
Lead and Lag functions are introduced only in hive 0.11 version. Please refer to the below link for more details.
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+WindowingAndAnalytics
Thanks,
Karthik
From: Ravi Prasad [mailto:raviprasad29@gmail.com<ma...@gmail.com>]
Sent: Friday, November 14, 2014 3:08 AM
To: user@hive.apache.org<ma...@hive.apache.org>
Subject: Analytic functions in Lead & Lag in Hive
Cany anyone suggest, how to use the Lead and lag function in Hive.
I am using Hive 0.10 version. Is there any way to make it work in 0.10 version.
I have used the below code in Oracle, But it is not working in Hive.
I need to compare the difference between the previous record and currect record.
Oracle code for LAG :-
SELECT empno,
ename,
job,
sal,
LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_prev,
sal - LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_diff
FROM emp;
Oracle code for LEAD :-
SELECT empno,
ename,
job,
sal,
LEAD(sal, 1, 0) OVER (ORDER BY sal) AS sal_next,
LEAD(sal, 1, 0) OVER (ORDER BY sal) - sal AS sal_diff
FROM emp;
--
----------------------------------------------
Regards,
Prasad
--
----------------------------------------------
Regards,
RAVI PRASAD. T
Re: Analytic functions in Lead & Lag in Hive
Posted by Ravi Prasad <ra...@gmail.com>.
Hi Karthik,
Thanks. But we are using Hive 0.10 version of Hive.
Is there any alternative way available to implement this Lead / Lag
functionality in Hive 0.10 version
Regards
Raviprasad
On Fri, Nov 14, 2014 at 7:51 PM, Dhandapani, Karthik <
Karthik.Dhandapani@cvscaremark.com> wrote:
> Ravi,
>
>
>
> Lead and Lag functions are introduced only in hive 0.11 version. Please
> refer to the below link for more details.
>
>
>
>
> https://cwiki.apache.org/confluence/display/Hive/LanguageManual+WindowingAndAnalytics
>
>
>
> Thanks,
>
> Karthik
>
>
>
> *From:* Ravi Prasad [mailto:raviprasad29@gmail.com]
> *Sent:* Friday, November 14, 2014 3:08 AM
> *To:* user@hive.apache.org
> *Subject:* Analytic functions in Lead & Lag in Hive
>
>
>
> Cany anyone suggest, how to use the Lead and lag function in Hive.
>
> I am using Hive* 0.10 version*. Is there any way to make it work in 0.10
> version.
>
> I have used the below code in Oracle, But it is not working in Hive.
>
> I need to compare the difference between the previous record and currect
> record.
>
>
>
> *Oracle code for LAG :-*
>
> SELECT empno,
>
> ename,
>
> job,
>
> sal,
>
> LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_prev,
>
> sal - LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_diff
>
> FROM emp;
>
>
>
> *Oracle code for LEAD :-*
>
> SELECT empno,
>
> ename,
>
> job,
>
> sal,
>
> LEAD(sal, 1, 0) OVER (ORDER BY sal) AS sal_next,
>
> LEAD(sal, 1, 0) OVER (ORDER BY sal) - sal AS sal_diff
>
> FROM emp;
>
>
>
>
>
>
>
> --
>
> ----------------------------------------------
> Regards,
> Prasad
>
--
----------------------------------------------
Regards,
RAVI PRASAD. T
Hive and Hbase types
Posted by peterm_second <re...@gmail.com>.
Hi Guys,
I am trying to to map Hbase table to a Hive table ( an external table).
I am using a hive create statement to create the hbase tables. That works,
I was wondering how does HiveSQL types relate to Hbase types? I mean how
does one go about translate Hbase long to Hive SQL type.
I am pushing the data to the Hbase table from a 3rd party process and
then when a query that table from Hive i see nuls for columns that do
have values.
Has anyone had any similar issues when connecting Hive to Hbase.
Regards,
Peter
RE: Analytic functions in Lead & Lag in Hive
Posted by "Dhandapani, Karthik" <Ka...@CVSCaremark.com>.
Ravi,
Lead and Lag functions are introduced only in hive 0.11 version. Please refer to the below link for more details.
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+WindowingAndAnalytics
Thanks,
Karthik
From: Ravi Prasad [mailto:raviprasad29@gmail.com]
Sent: Friday, November 14, 2014 3:08 AM
To: user@hive.apache.org
Subject: Analytic functions in Lead & Lag in Hive
Cany anyone suggest, how to use the Lead and lag function in Hive.
I am using Hive 0.10 version. Is there any way to make it work in 0.10 version.
I have used the below code in Oracle, But it is not working in Hive.
I need to compare the difference between the previous record and currect record.
Oracle code for LAG :-
SELECT empno,
ename,
job,
sal,
LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_prev,
sal - LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_diff
FROM emp;
Oracle code for LEAD :-
SELECT empno,
ename,
job,
sal,
LEAD(sal, 1, 0) OVER (ORDER BY sal) AS sal_next,
LEAD(sal, 1, 0) OVER (ORDER BY sal) - sal AS sal_diff
FROM emp;
--
----------------------------------------------
Regards,
Prasad