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;
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
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.
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;
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 Name||Column Type|
An example of data in the above tables with three rows:
A lateral view with explode() can be used to convert “Deptbranchno” into separate rows using the query:
Hive> SELECT Dname, Deptbranchno
LATERAL VIEW explode(Deptbranchno) as DeptbranchID;
The output of above HIVE Query will be: