Find a date that is in the range of any array dates in Hive

[Raj Hadoop <ha...@yahoo.com>]

Hi,


I have the following requirement from a Hive table below.

CustNumActivityDatesRates
10010-Aug-13,12-Aug-13,20-Aug-1310,15,20

The data above says that

From 10 Aug to 11 Aug the rate is 10.
From 12 Aug to 19 Aug the rate is 15.

From 20-Aug to till date the rate is 20.

Note : The order is maintained in 'ActivityDates' and 'Rates'.

From the above table , I need to find the rate on say a given date 15-Aug-13. In the above case , the rate for 15-Aug-13 is 15.

How should I get this result in Hive.

I was reading about a Generic UDF and was thinking to write one like this.
The Generic UDF takes two inputs (input date , array of input dates ) . the output should be (an int )to return the element number in the array. 

In the above case 
Generic UDF(15-Aug-13,10-Aug-13,12-Aug-13,20-Aug-13) should return the 2nd element in array - 2.



Please advise if there is an alternative solution or if the above solution works. I have never written a UDF or Generic UDF and would need some help from the forum members. Please advise.


Regards,
Raj

Re: Find a date that is in the range of any array dates in Hive

[Matouk IFTISSEN <ma...@ysance.com>]

Hé
You can use explode function that is an udf in hive: like this
SELECT explode(ActivityDates) AS ActivityDates FROM myMapTable;
and think who to map the result whith rates  ;)

Regadrs.
Matouk


2014-02-01 Raj Hadoop <ha...@yahoo.com>:

> Hi,
>
>
> I have the following requirement from a Hive table below.
>
> CustNum ActivityDates Rates
> 100 10-Aug-13,12-Aug-13,20-Aug-13 10,15,20
>
> The data above says that
>
> From 10 Aug to 11 Aug the rate is 10.
> From 12 Aug to 19 Aug the rate is 15.
> From 20-Aug to till date the rate is 20.
>
> Note : The order is maintained in 'ActivityDates' and 'Rates'.
>
> From the above table , I need to find the rate on say a given date
> 15-Aug-13. In the above case , the rate for 15-Aug-13 is 15.
>
> How should I get this result in Hive.
>
> I was reading about a Generic UDF and was thinking to write one like this.
> The Generic UDF takes two inputs (input date , array of input dates ) .
> the output should be (an int )to return the element number in the array.
>
> In the above case
> Generic UDF(15-Aug-13,10-Aug-13,12-Aug-13,20-Aug-13) should return the
> 2nd element in array - 2.
>
>
> Please advise if there is an alternative solution or if the above solution
> works. I have never written a UDF or Generic UDF and would need some help
> from the forum members. Please advise.
>
>
> Regards,
> Raj
>



-- 

*Matouk IFTISSEN | Consultant BI & Big Data [image: http://www.ysance.com] *
24 rue du sentier - 75002 Paris - www.ysance.com <http://www.ysance.com/>
Mob : +33 6 78 51 18 69 || Fax : +33 1 73 72 97 26
*Ysance sur* :*Twitter* <http://twitter.com/ysance>* | Facebook
<https://www.facebook.com/pages/Ysance/131036788697> | Google+
<https://plus.google.com/u/0/b/115710923959357341736/115710923959357341736/posts>
| LinkedIn
<http://www.linkedin.com/company/ysance> | Newsletter
<http://www.ysance.com/nous-contacter.html>*
*Nos autres sites* : *ys4you* <http://wwww.ys4you.com/>* | labdecisionnel
<http://www.labdecisionnel.com/> | decrypt <http://decrypt.ysance.com/>*