Hive Functions: Built-in & UDF (User Defined) Example

Functions are built for a specific purpose to perform operations like mathematical, arithmetic, logical and relational operations on the operands of table column names.

Built-in Functions

These are functions that are already available in Hive. First, we have to check the application requirement, and then we can use these built-in functions in our applications. We can call these functions directly in our application.

The syntax and types are mentioned in the following section.

Types of built-in functions in Hive:

• Collection Functions
• Date Functions
• Mathematical Functions
• Conditional Functions
• String Functions
• Misc. Functions

Each of the six families is covered below with its function signatures and return types.

Collection Functions

These functions are used for collections. Collections mean the grouping of elements, and they return a single element or an array of elements depending on the return type mentioned in the function name.

Date Functions

These are used to perform date manipulations and conversion of date types from one type to another type:

Mathematical Functions

These functions are used for mathematical operations. Instead of creating UDFs, we have some inbuilt mathematical functions in Hive.

Conditional Functions

These functions are used for conditional value checks.

String Functions

String manipulations and string operations are handled by the following functions.

Misc. Functions

Several built-in functions belong to none of the families above. These cover hashing, session information and calling arbitrary Java methods.

UDFs (User Defined Functions)

In Hive, users can define their own functions to meet certain client requirements. These are known as UDFs in Hive. User defined functions are written in Java for specific modules.

Some UDFs are specifically designed for the reusability of code in application frameworks. The developer writes these functions in Java and integrates those UDFs with Hive.

During query execution, the developer can directly use the code, and UDFs will return outputs according to the user-defined tasks. It provides high performance in terms of coding and execution.

For example, for string stemming we do not have any predefined function in Hive. For this, we can write a stem UDF in Java. Wherever we require stem functionality, we can directly call this stem UDF in Hive.

Here stem functionality means deriving words from their root words. A stemming algorithm reduces the words “wishing”, “wished” and “wishes” to the root word “wish”. For performing this type of functionality, we can write a UDF in Java and integrate it with Hive.

Depending on the use case, the UDF can be written to accept and produce different numbers of input and output values.

The general type of UDF will accept a single input value and produce a single output value. If the UDF is used in a query, then the UDF will be called once for each row in the result data set.

In the other direction, a function can accept a group of values as input and return a single output value as well. That difference is what separates the three custom function types described next.

UDF vs UDAF vs UDTF in Hive

Custom functions in Hive are grouped by how many rows go in and how many come out. Choosing the wrong type is the most common reason a custom function refuses to compile or returns a single row where a table was expected.

Two practical rules follow from the table:

• A UDAF is almost always paired with a GROUP BY clause, because it collapses each group down to one row.
• A UDTF cannot be selected alongside other columns in the same SELECT list, so it is normally used with a LATERAL VIEW.

There is also a second, more capable base class for row-level work, GenericUDF, which accepts complex types such as arrays, maps and structs and a variable number of arguments.

How to Write and Register a UDF in Hive

The stem example described above can be built as a real function in four steps. Nothing beyond a Java compiler and a running Hive session is required.

Step 1) Write a Java class that extends UDF and implements a public evaluate() method. Hive calls evaluate() once per row, so the method must handle a NULL input.

package com.guru99.hive.udf;

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

public final class Stem extends UDF {

    public Text evaluate(final Text input) {
        if (input == null) {
            return null;
        }
        String word = input.toString().toLowerCase();
        if (word.endsWith("ing")) {
            word = word.substring(0, word.length() - 3);
        } else if (word.endsWith("ed") || word.endsWith("es")) {
            word = word.substring(0, word.length() - 2);
        }
        return new Text(word);
    }
}

Step 2) Compile the class and package it into a JAR file, for example hive-udf-1.0.jar, together with any classes it depends on.

Step 3) Put the JAR on the Hive classpath and bind a function name to the class. A temporary function lives only for the current session.

ADD JAR /home/hduser/hive-udf-1.0.jar;

CREATE TEMPORARY FUNCTION stem AS 'com.guru99.hive.udf.Stem';

Step 4) Call the new function exactly like a built-in one. Applied to the words “wishing”, “wished” and “wishes”, this class returns “wish” for all three.

SELECT word, stem(word) FROM words;

To keep the function available to every session and every user, register it permanently against a database instead. The JAR must sit on a path that the whole cluster can read, which normally means HDFS.

CREATE FUNCTION default.stem AS 'com.guru99.hive.udf.Stem'
USING JAR '/user/hive/udfs/hive-udf-1.0.jar';

A single class may declare more than one evaluate() method. Hive matches the call against the argument signatures, which is how one function can accept both a string and an integer.

FAQs