HIVE UDF (User Defined Functions) – HIVE Standard, Aggregate Function

HIVE UDF (User Defined Functions) – HIVE Standard, Aggregate Function

HIVE UDF (User Defined Functions) allow the user to extend HIVE Query Language. Once the UDF is added in the HIVE script, it works like a normal built-in function. To check which all UDFs are loaded in current hive session, we use SHOW command.

To check which all HIVE UDF are loaded in the current HIVE session, we use SHOW command.

hive> SHOW FUNCTIONS;

array
abs
acos
and

The documentation about a function is shown by DESCRIBE FUNCTION <“function name”> command and their detailed documentation is accessed by DESCRIBE FUNCTION EXTENDED <“function name”> command.

hive> DESCRIBE FUNCTION concat;

concat(str1,str, ... ,strN) - returns the concatenation of strings.

Different HIVE UDF used in HIVE Query Language

Standard FUNCTION

These kinds of functions take a row argument or one or more columns from a row and return a single value.

Example: round(),floor(), concat(), reverse(),ucase(),abs() etc.

It is important to note that these functions can also return a complex object, such as an array, map, or struct.

Aggregate FUNCTION

It takes one or more columns from zero to many rows and returns a single value.

Example: sum(), avg(),min(),max() etc.

Table Generating FUNCTION

Table generation functions take zero or more inputs and produce multiple columns or rows as output.

hive> SELECT array(1,2,3,4) FROM dual;

1
2
3
4

Explode() is another table generation function which takes an array of input and iterates through the list and returns each element from the list in a separate row.

The lateral view is used in conjunction with user-defined table generating functions such as explode().

An example of explode() function is given below :

Column NameColumn Type
DeptbranchnoArray<int>
DnameSTRING

An example of data in the above tables with three rows:

DnameDeptbranchno
Zoology[1,2,3]
Mathematics[5,6]

A lateral view with explode() can be used to convert  “Deptbranchno”  into separate rows using the query:

Hive> SELECT Dname, Deptbranchno

FROM dept

LATERAL VIEW explode(Deptbranchno) as DeptbranchID;

The output of above HIVE Query will be:

Dname(string)DeptbranchID(int)
Zoology1
Zoology2
Zoology3
Mathematics5
Mathematics6