Hive UDF implementation

Consider you have a data set like following:

AccidentId, Date, Day

 

1, 1/1/1979, 5               

 

2, 1/2/1979, 6

 

3, 1/3/1979, 7

 

4, 1/4/1979, 1

 

5, 1/5/1979, 2

 

6, 1/6/1979, 3

 

7, 1/7/1979, 4

 

8, 1/8/1979, 5

 

......

 

.......

 

16, 1/6/1980, 3

 

17, 1/7/1980, 4

 

18, 1/8/1980, 5

This is the query that we want to run on above written input data.

 Select Date , Day_of_Week,count(*) from accidents group by GetYear_FromDate(Date) , Day_of_Week;

The problem is that from Date column we just want Year part.

We have 2 ways to solve this problem :

1) By converting string date to proper format and then using Year function in Hive.

Year(cast(concat(substr(Date,7,4), '-',substr(Date,1,2), '-',substr(Date,4,2))as date)) as DT

2) Write  UDF to get following output:

1979    1    843
1979    2    1180
1979    3    1131
1979    4    1190
1979    5    1231
1979    6    1458
1979    7    1167
1989    7    2

We need to follow 2 rules for HIVE UDFs:
 1) A UDF must be subclass of  org.apache.hadoop.hive.ql.exec.UDF;
 2) A UDF must have 1 evaluate() method.

Here is the UDF code:

import org.apache.hadoop.hive.ql.exec.UDF;
import org.apache.hadoop.io.Text;

public class DateFix extends UDF {

    public Text evaluate(Text str) {
        if (str == null) {
            return null;
        }
       
        return new Text(str.toString().substring(6, 10));
    }
} 

 Now we need to register the UDF :

ADD JAR /home/edureka/Desktop/Projects/HiveUDf/DateDemo.jar;      

We also need to create alias for Java classname:

create temporary function GetYear_FromDate as 'DateFix';    

Finally, we run following query :

Select GetYear_FromDate(Date) , Day_of_Week,count(*) from accidents group by GetYear_FromDate(Date) , Day_of_Week;

That's it. You have just created a UDF in Hive and you can use it on any date column to fetch year part.

Thanks