UDF's Part 2: Custom GenericUDF in Hive (NVL2)

1.0. What's in this blog?

In my previous blog on creating custom UDFs in Hive, I covered a sample basic UDF.  This blog covers generic UDF creation, to mimic the same NVL2 functionality covered in the previous blog.  It includes sample data, java code for creating the UDF, expected results, commands to execute and the output.  
[hive 0.10]

About UDFs:

UDF stands for User Defined Function.  In Hive, there are (a) reusable functions available, as part of core Hive (out of the box) that can be used in Hive queries; They are called UDFs, even though they are not user-defined.  And then there are (b) functions that one can create in Java, also called UDFs, and use in Hive queries.  The focus of this blog is custom UDFs (b), specifically generic UDFs.

About generic UDF:

UDFs in Hive have are extensions of either UDF or GenericUDF classes.  GenericUDFs are more optimal from a performance perspective as they use short circuit evaluation and lazy evaluation, when compared to UDFs that use reflection.  GenericUDFs support non-primitive Hive types like arrays, structs and maps in addition to primitive types, unlike UDFs that support only primitive types.  

About NVL2:

NVL2 takes three parameters, we will refer to as expr1, expr2 and expr3.

NVL2 lets you determine the value returned by a query based on whether a specified expression is null or not null. If expr1 is not null, then NVL2 returns expr2. If expr1 is null, then NVL2 returns expr3.

2.0. NVL2 generic UDF in Hive

1: Create the test data file for a Hive external table

	**************************
	Input data
	**************************
	1. Execute locally on the node you are running Hive client from
	-----------------------------------------------------------------
	Create input file/data to use for the demo.
	Since this gist is merely for instructional purpose, the dataset is small.
	cd ~
	mkdir hiveProject
	cd hiveProject
	vi Departments_UDFTest
	Paste this..ensuring the fields are delimited by tabs and record with new line.
	d001 Marketing
	d002 Finance
	d003 Human Resources
	d004 Production
	d005 Development
	d006 Quality Management
	d007 Sales
	d008
	d009 Customer Service
	2. Hadoop commands
	--------------------
	hadoop fs -mkdir hiveProject
	hadoop fs -put hiveProject/Departments_UDFTest hiveProject

view raw 01-InputData hosted with ❤ by GitHub

2: Create the Hive table

	*********************************************
	Setting up the Hive table
	*********************************************
	In hive shell....
	a) Create table:
	CREATE EXTERNAL TABLE IF NOT EXISTS departments_UDFTest
	(
	deptNo String,
	deptName String
	)
	Row format delimited
	fields terminated by '\t'
	LOCATION '/user/akhanolk/hiveProject';
	b) Quick test:
	Select * from departments_UDFTest;
	d001 Marketing
	d002 Finance
	d003 Human Resources
	d004 Production
	d005 Development
	d006 Quality Management
	d007 Sales
	d008 NULL
	d009 Customer Service

view raw 02-HiveTableSetup hosted with ❤ by GitHub

3: Create the UDF in Java

	package khanolkar.HiveUDFs;
	import org.apache.hadoop.hive.ql.exec.Description;
	import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
	import org.apache.hadoop.hive.ql.exec.UDFArgumentLengthException;
	import org.apache.hadoop.hive.ql.exec.UDFArgumentTypeException;
	import org.apache.hadoop.hive.ql.metadata.HiveException;
	import org.apache.hadoop.hive.ql.udf.UDFType;
	import org.apache.hadoop.hive.ql.udf.generic.GenericUDF;
	import org.apache.hadoop.hive.ql.udf.generic.GenericUDFUtils;
	import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
	@UDFType(deterministic = true)
	@Description(name = "NVL2", value = "NVL2(expr1,expr2,expr3) returns expr3, if expr1 is null, otherwise returns expr2;", extended = "NVL2(expr1,expr2,expr3) returns expr3, if expr1 is null, otherwise retruns expr2")
	public class NVL2GenericUDF extends GenericUDF {
	private GenericUDFUtils.ReturnObjectInspectorResolver returnOIResolver;
	private ObjectInspector[] argumentOIs;
	@Override
	public ObjectInspector initialize(ObjectInspector[] arguments)
	throws UDFArgumentException {
	argumentOIs = arguments;
	// First check - do we have the right number of arguments?
	if (arguments.length != 3) {
	throw new UDFArgumentLengthException(
	"The operator 'NVL2' accepts 3 arguments.");
	}
	// Second check - throw exception if any complex types have been passed
	// as parameters
	if (arguments[0].getCategory() != ObjectInspector.Category.PRIMITIVE
	|| arguments[1].getCategory() != ObjectInspector.Category.PRIMITIVE
	|| arguments[2].getCategory() != ObjectInspector.Category.PRIMITIVE)
	throw new UDFArgumentTypeException(0,
	"Only primitive type arguments are accepted");
	// Third check - throw exception if the data types across parameters are
	// different
	if (!(returnOIResolver.update(arguments[0]) && returnOIResolver
	.update(arguments[1]))
	|| !(returnOIResolver.update(arguments[1]) && returnOIResolver
	.update(arguments[2]))) {
	throw new UDFArgumentTypeException(2,
	"The arguments of function NLV2 should have the same type, "
	+ "but they are different: \""
	+ arguments[0].getTypeName() + "\" and \""
	+ arguments[1].getTypeName() + "\" and \""
	+ arguments[2].getTypeName() + "\"");
	}
	returnOIResolver = new GenericUDFUtils.ReturnObjectInspectorResolver(
	true);
	return returnOIResolver.get();
	}
	@Override
	public Object evaluate(DeferredObject[] arguments) throws HiveException {
	// The NVL2 functionality
	Object retVal = returnOIResolver.convertIfNecessary(arguments[0].get(),
	argumentOIs[0]);
	if (retVal == null) {
	retVal = returnOIResolver.convertIfNecessary(arguments[2].get(),
	argumentOIs[2]);
	} else {
	retVal = returnOIResolver.convertIfNecessary(arguments[1].get(),
	argumentOIs[1]);
	}
	return retVal;
	}
	@Override
	public String getDisplayString(String[] children) {
	StringBuilder sb = new StringBuilder();
	sb.append("if ");
	sb.append(children[0]);
	sb.append(" is null ");
	sb.append("returns");
	sb.append(children[2]);
	sb.append("else ");
	sb.append("returns");
	sb.append(children[1]);
	return sb.toString();
	}
	}

view raw 03a-UDFCode hosted with ❤ by GitHub

	Concepts
	-------------------------
	There are three methods-
	1. initialize() - called once, at first. The goal of this method is to determine the return type
	from the arguments. The user can also throw an Exception to signal that bad types are being sent
	to the method. The returnOIResolver is a built-in class that determines the return type by finding
	the type of non-null variables and using that type. The ObjectInspector is used to the transform
	raw records into objects that Hive can access. The initialize() method is passed an
	ObjectInspector for each argument
	2. evaluate() - where the logic for the function should be written.
	The evaluate method has access to the values passed to the method stored in an array of DeferredObject
	values. The returnOIResolver created in the initialize method is used to get values from the
	DeferredObjects.
	3. getDisplayString() - The final method to override is getDisplayString(), is used inside the Hadoop
	tasks to display debugging information when the function is being used.
	Annotations:
	@UDFType(deterministic = true) annotation: Indicates that the UDF returns the same value any time its
	called
	@Description(...) annotation: Includes information that is displayed when you do a describe on the UDF

view raw 03b-SomeNotesOnUDF hosted with ❤ by GitHub

4: Expected results

	****************************
	Expected results
	****************************
	Query:
	select deptNo,NVL2(deptName,deptName,'Procrastrination') from departments_UDFTest;
	The null in the department name for department d008, should be returned as "Procrastrination".
	For the rest of the records, the query should return the data in Hive, as is.

view raw 04-ExpectedResults hosted with ❤ by GitHub

5: Try out the UDF

	*******************************
	Testing the UDF
	*******************************
	1) Add jar
	hive> add jar hiveProject/jars/NVL2GenericUDF.jar;
	2) Create alias for the function
	hive> CREATE TEMPORARY FUNCTION NVL2
	AS 'khanolkar.HiveUDFs.NVL2GenericUDF';
	3) Test the description provided
	hive> DESCRIBE FUNCTION NVL2;
	OK
	NVL2(expr1,expr2,expr3) returns expr3, if expr1 is null, otherwise returns expr2;
	4) Test if there are checks in place for number of parameters
	hive> select deptNo,NVL2(deptName,deptName) from departments_UDFTest;
	FAILED: SemanticException [Error 10015]: Line 1:14 Arguments length mismatch 'deptName': The operator 'NVL2' accepts 3 arguments.
	hive> select deptNo,NVL2(deptName,deptName,123,1) from departments_UDFTest;
	FAILED: SemanticException [Error 10015]: Line 1:14 Arguments length mismatch '1': The operator 'NVL2' accepts 3 arguments.
	5) Results
	hive> select deptNo,NVL2(deptName,deptName,'Procrastrination') from departments_UDFTest;
	OK
	d001 Marketing
	d002 Finance
	d003 Human Resources
	d004 Production
	d005 Development
	d006 Quality Management
	d007 Sales
	d008 Procrastrination
	d009 Customer Service

view raw 05-HiveTest hosted with ❤ by GitHub

3.0. Making the UDF permanently available when you launch the hive shell

There are several ways to make a custom UDF available when you launch the Hive shell, bypassing the need to execute the "add jar..." statement before using a custom UDF.  I have listed a couple of them.

Option 1:
From "Programming Hive"

Your function may also be added permanently to Hive, however this requires a small modification to a Hive Java file and then rebuilding Hive.
Inside the Hive source code, a one-line change is required to the FunctionRegistry class found atql/src/java/org/apache/hadoop/hive/ql/exec/FunctionRegistry.java. Then you rebuild Hive following the instructions that come with the source distribution.
While it is recommended that you redeploy the entire new build, only the hive-exec-*.jar, where \* is the version number, needs to be replaced.

Option 2:

Add it to the .hiverc file on each node from where hive queries will be run.

Check out my blog - http://hadooped.blogspot.com/2013/08/hive-hiverc-file.html

4.0. References

Apache documentation:
http://hive.apache.org/docs/r0.10.0/api/org/apache/hadoop/hive/ql/udf/generic/GenericUDF.html
https://cwiki.apache.org/confluence/display/Hive/OperatorsAndFunctions

A good article on creating a UDF that involves non-primitive types - link

Programming Hive - from O'Reilly

That's it for this blog.  Do share any additional insights with me.

Cheers!