Window Function with Ignore Nulls?

["Hulbert, Leland" <Le...@epsilon.com>]

Hi,
                I'm currently on Hive 0.11.  I'm trying to pull the last non-null record from a dataset.  Ideally, I would use something like this:

Select id, last_value(address ignore nulls) over (partition by id order by file_date) as address from address_table;

                But Hive doesn't seem to accept ignore nulls in window functions.  I've also tried this:

Select id, nvl(address, lag(lag_address, 1) over (partition by id order by file_date)) as lag_address from address_table;

                But it won't recognize the lag_address field name.  My last option was this:

Select id, case when address is not null then address else lag(address, 1) over (partition by id order by file_date) end as lag_address from address_table;

                Which throws an error that doesn't make sense to me (ParseException missing KW_END at 'over' near ')' )

                Is there a reasonable way to get this done in Hive?  I've got a couple dozen or more fields that need the same rule applied, so options that require a separate inner query per field are a performance issue.  Thanks for any direction you can give.

Thanks,

Lee


________________________________

This e-mail and files transmitted with it are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not the intended recipient, or the employee or agent responsible to deliver it to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you are not one of the named recipient(s) or otherwise have reason to believe that you received this message in error, please immediately notify sender by e-mail, and destroy the original message. Thank You.

Re: Window Function with Ignore Nulls?

[praveen akinapally <pr...@gmail.com>]

Hi Hulbert,

Select id, last_value(address,true) over (partition by id order by
file_date) as address from address_table; works in Hive 0.13.1. Not sure
about Hive 0.11. Try and let me know.


Regards,

Praveen Akinapally

On Fri, Dec 19, 2014 at 5:43 AM, Hulbert, Leland <Leland.Hulbert@epsilon.com
> wrote:

> Hi,
>                 I'm currently on Hive 0.11.  I'm trying to pull the last
> non-null record from a dataset.  Ideally, I would use something like this:
>
> Select id, last_value(address ignore nulls) over (partition by id order by
> file_date) as address from address_table;
>
>                 But Hive doesn't seem to accept ignore nulls in window
> functions.  I've also tried this:
>
> Select id, nvl(address, lag(lag_address, 1) over (partition by id order by
> file_date)) as lag_address from address_table;
>
>                 But it won't recognize the lag_address field name.  My
> last option was this:
>
> Select id, case when address is not null then address else lag(address, 1)
> over (partition by id order by file_date) end as lag_address from
> address_table;
>
>                 Which throws an error that doesn't make sense to me
> (ParseException missing KW_END at 'over' near ')' )
>
>                 Is there a reasonable way to get this done in Hive?  I've
> got a couple dozen or more fields that need the same rule applied, so
> options that require a separate inner query per field are a performance
> issue.  Thanks for any direction you can give.
>
> Thanks,
>
> Lee
>
>
> ________________________________
>
> This e-mail and files transmitted with it are confidential, and are
> intended solely for the use of the individual or entity to whom this e-mail
> is addressed. If you are not the intended recipient, or the employee or
> agent responsible to deliver it to the intended recipient, you are hereby
> notified that any dissemination, distribution or copying of this
> communication is strictly prohibited. If you are not one of the named
> recipient(s) or otherwise have reason to believe that you received this
> message in error, please immediately notify sender by e-mail, and destroy
> the original message. Thank You.
>



-- 
Thanks and Regards,

Praveen Akinapally
Phone: 321-432-5465