RE: Hive help on the update
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:sprag...@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 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
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_chinnak...@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*** > >
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***