You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Kumar Chinnakali <Ku...@infosys.com> on 2013/06/28 14:52:23 UTC
Hive help on the update
Hi Hive Team,
I have Teradata Query as below and my intention is to convert in to Hive Query(HQL). Tried but it's vain.
SELECT Col_1, Col_2, Col_3, MIN(Col_3) OVER(Partition by Col_1 ORDER BY Col_3, Col_2 rows between 1 preceding and 1 preceding) prev_Col_3 FROM work_table ;
Example:
Actual Work table:
Col_1
Col_3
Prev_Col_3
P1
9:00
Null
P1
10:00
Null
P2
11:00
Null
P2
12:00
Null
Expected result:
Col_1
Col_3
Prev_Col_3
P1
9:00
Null
P1
10:00
9:00
P2
11:00
Null
P2
12:00
11:00
Any help will be much help full.
Regards, Kumar Chinnakali :)
**************** CAUTION - Disclaimer *****************
This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely
for the use of the addressee(s). If you are not the intended recipient, please
notify the sender by e-mail and delete the original message. Further, you are not
to copy, disclose, or distribute this e-mail or its contents to any other person and
any such actions are unlawful. This e-mail may contain viruses. Infosys has taken
every reasonable precaution to minimize this risk, but is not liable for any damage
you may sustain as a result of any virus in this e-mail. You should carry out your
own virus checks before opening the e-mail or attachment. Infosys reserves the
right to monitor and review the content of all messages sent to or from this e-mail
address. Messages sent to or from this e-mail address may be stored on the
Infosys e-mail system.
***INFOSYS******** End of Disclaimer ********INFOSYS***
RE: Hive help on the update
Posted by Christopher Wirt <ch...@struq.com>.
To emulate the RANK function you will need a custom UDF
Edward Capriolo has a nice one here
https://github.com/edwardcapriolo/hive-rank
Philip Tromans has another here https://github.com/philiptromans/hive-udf
If you're on 0.11 already there is a native version.
https://cwiki.apache.org/Hive/languagemanual-windowingandanalytics.html
From: Stephen Sprague [mailto:spragues@gmail.com]
Sent: 28 June 2013 17:30
To: user@hive.apache.org
Subject: Re: Hive help on the update
what version of hive are you running? and show us your attempts - don't be
shy!
On Fri, Jun 28, 2013 at 5:52 AM, Kumar Chinnakali
<Ku...@infosys.com> wrote:
Hi Hive Team,
I have Teradata Query as below and my intention is to convert in to Hive
Query(HQL). Tried but it's vain.
SELECT Col_1, Col_2, Col_3, MIN(Col_3) OVER(Partition by Col_1 ORDER BY
Col_3, Col_2 rows between 1 preceding and 1 preceding) prev_Col_3 FROM
work_table ;
Example:
Actual Work table:
Col_1
Col_3
Prev_Col_3
P1
9:00
Null
P1
10:00
Null
P2
11:00
Null
P2
12:00
Null
Expected result:
Col_1
Col_3
Prev_Col_3
P1
9:00
Null
P1
10:00
9:00
P2
11:00
Null
P2
12:00
11:00
Any help will be much help full.
Regards, Kumar Chinnakali J
**************** CAUTION - Disclaimer *****************
This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely
for the use of the addressee(s). If you are not the intended recipient,
please
notify the sender by e-mail and delete the original message. Further, you
are not
to copy, disclose, or distribute this e-mail or its contents to any other
person and
any such actions are unlawful. This e-mail may contain viruses. Infosys has
taken
every reasonable precaution to minimize this risk, but is not liable for any
damage
you may sustain as a result of any virus in this e-mail. You should carry
out your
own virus checks before opening the e-mail or attachment. Infosys reserves
the
right to monitor and review the content of all messages sent to or from this
e-mail
address. Messages sent to or from this e-mail address may be stored on the
Infosys e-mail system.
***INFOSYS******** End of Disclaimer ********INFOSYS***
Re: Hive help on the update
Posted by Stephen Sprague <sp...@gmail.com>.
what version of hive are you running? and show us your attempts - don't be
shy!
On Fri, Jun 28, 2013 at 5:52 AM, Kumar Chinnakali <
Kumar_Chinnakali@infosys.com> wrote:
> Hi Hive Team,****
>
> ** **
>
> I have Teradata Query as below and my intention is to convert in to Hive
> Query(HQL). Tried but it’s vain. ****
>
> ** **
>
> SELECT Col_1, Col_2, Col_3, MIN(Col_3) OVER(Partition by Col_1 ORDER BY
> Col_3, Col_2 rows between 1 preceding and 1 preceding) prev_Col_3 FROM
> work_table ;****
>
> ** **
>
> *Example:*****
>
> ** **
>
> *Actual Work table: *****
>
> Col_1 ****
>
> Col_3****
>
> Prev_Col_3****
>
> P1****
>
> 9:00****
>
> Null****
>
> P1****
>
> 10:00****
>
> Null****
>
> P2****
>
> 11:00****
>
> Null****
>
> P2****
>
> 12:00****
>
> Null****
>
> ** **
>
> *Expected result**:*****
>
> Col_1 ****
>
> Col_3****
>
> Prev_Col_3****
>
> P1****
>
> 9:00****
>
> Null****
>
> P1****
>
> 10:00****
>
> 9:00****
>
> P2****
>
> 11:00****
>
> Null****
>
> P2****
>
> 12:00****
>
> 11:00****
>
> ** **
>
> Any help will be much help full.****
>
> ** **
>
> Regards, Kumar Chinnakali J****
>
> **************** CAUTION - Disclaimer *****************
> This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely
> for the use of the addressee(s). If you are not the intended recipient, please
> notify the sender by e-mail and delete the original message. Further, you are not
> to copy, disclose, or distribute this e-mail or its contents to any other person and
> any such actions are unlawful. This e-mail may contain viruses. Infosys has taken
> every reasonable precaution to minimize this risk, but is not liable for any damage
> you may sustain as a result of any virus in this e-mail. You should carry out your
> own virus checks before opening the e-mail or attachment. Infosys reserves the
> right to monitor and review the content of all messages sent to or from this e-mail
> address. Messages sent to or from this e-mail address may be stored on the
> Infosys e-mail system.
> ***INFOSYS******** End of Disclaimer ********INFOSYS***
>
>