UDFs

Hive User-defined functions (UDFs) are custom functions that are developed in Java and integrated with Apache Hive. UDFs are routines that accept parameters, perform an action, and return the result of the action as a value. The return value can be a single scalar row or a result set, it depends on the UDF's code and implemented interface.

UDFs are powerful capability that extends the classical SQL functionality with custom code, that the Hive user can utilize. Hive has a bunch of built-in UDFs that you can utilize. Like other SQL-based solutions, Apache Hive also provides functionality to extend the current - rich- UDF set with custom if needed.

Important

Every UDF's evaluate method is one row at a time! This means if your UDFs has complex code, it could introduce performance issue in execution time.

To have an understanding of the UDF's let's start with one example to have an understanding of the execution pattern.

UDF's in action

SELECT length(string_col) FROM table_name;

In this case, the length of the built-in UDF evaluates each row of the string_col values.

As we see how a built-in UDF works let's see what kind of built-in UDFs the Apache Hive has.

Tipp

All Hive keywords are case-insensitive, including the names of Hive operators and functions. I.e: length and LENGTH are also accepted by the Hive.

Mathematical Functions

The following built-in mathematical functions are supported in Hive.

Return Type	Name (Signature)	Description
DOUBLE	round(DOUBLE a)	Returns the rounded `BIGINT` value of `a`.
DOUBLE	round(DOUBLE a, INT d)	Returns `a` rounded to `d` decimal places.
DOUBLE	bround(DOUBLE a)	Returns the rounded BIGINT value of `a` using HALF_EVEN rounding mode. Also known as Gaussian rounding or bankers' rounding. Example: bround(2.5) = 2, bround(3.5) = 4.
DOUBLE	bround(DOUBLE a, INT d)	Returns `a` rounded to `d` decimal places using HALF_EVEN rounding mode. Example: bround(8.25, 1) = 8.2, bround(8.35, 1) = 8.4.
BIGINT	floor(DOUBLE a)	Returns the maximum `BIGINT` value that is equal to or less than `a`.
BIGINT	ceil(DOUBLE a), ceiling(DOUBLE a)	Returns the minimum BIGINT value that is equal to or greater than `a`.
DOUBLE	rand(), rand(INT seed)	Returns a random number (that changes from row to row) that is distributed uniformly from 0 to 1. Specifying the seed will make sure the generated random number sequence is deterministic.
DOUBLE	exp(DOUBLE a), exp(DECIMAL a)	Returns `e^a` where `e` is the base of the natural logarithm.
DOUBLE	ln(DOUBLE a), ln(DECIMAL a)	Returns the natural logarithm of the argument `a`.
DOUBLE	log10(DOUBLE a), log10(DECIMAL a)	Returns the base-10 logarithm of the argument `a`.
DOUBLE	log2(DOUBLE a), log2(DECIMAL a)	Returns the base-2 logarithm of the argument `a`.
DOUBLE	log(DOUBLE base, DOUBLE a) log(DECIMAL base, DECIMAL a)	Returns the base-`base` logarithm of the argument `a`.
DOUBLE	pow(DOUBLE a, DOUBLE p), power(DOUBLE a, DOUBLE p)	Returns `a^p`.
DOUBLE	sqrt(DOUBLE a), sqrt(DECIMAL a)	Returns the square root of `a`.
STRING	bin(BIGINT a)	Returns the number in binary format.
STRING	hex(BIGINT a) hex(STRING a) hex(BINARY a)	If the argument is an `INT` or `binary`, `hex` returns the number as a `STRING` in hexadecimal format. Otherwise if the number is a `STRING`, it converts each character into its hexadecimal representation and returns the resulting `STRING`.
BINARY	unhex(STRING a)	Inverse of hex. Interprets each pair of characters as a hexadecimal number and converts to the byte representation of the number.
STRING	conv(BIGINT num, INT from_base, INT to_base), conv(STRING num, INT from_base, INT to_base)	Converts a number from a given base to another.
DOUBLE	abs(DOUBLE a)	Returns the absolute value.
INT or DOUBLE	pmod(INT a, INT b), pmod(DOUBLE a, DOUBLE b)	Returns the positive value of `a mod b`.
DOUBLE	sin(DOUBLE a), sin(DECIMAL a)	Returns the sine of `a` (`a` is in radians).
DOUBLE	asin(DOUBLE a), asin(DECIMAL a)	Returns the arc sin of `a` if -1<=a<=1 or NULL otherwise.
DOUBLE	cos(DOUBLE a), cos(DECIMAL a)	Returns the cosine of `a` (`a` is in radians).
DOUBLE	acos(DOUBLE a), acos(DECIMAL a)	Returns the arccosine of `a` if -1<=a<=1 or NULL otherwise.
DOUBLE	tan(DOUBLE a), tan(DECIMAL a)	Returns the tangent of `a` (`a` is in radians).
DOUBLE	atan(DOUBLE a), atan(DECIMAL a)	Returns the arctangent of `a`.
DOUBLE	degrees(DOUBLE a), degrees(DECIMAL a)	Converts value of `a` from radians to degrees.
DOUBLE	radians(DOUBLE a), radians(DOUBLE a)	Converts value of `a` from degrees to radians.
INT or DOUBLE	positive(INT a), positive(DOUBLE a)	Returns `a`.
INT or DOUBLE	negative(INT a), negative(DOUBLE a)	Returns `-a`.
DOUBLE or INT	sign(DOUBLE a), sign(DECIMAL a)	Returns the sign of `a` as '1.0' (if `a` is positive) or '-1.0' (if `a` is negative), '0.0' otherwise. The decimal version returns INT instead of DOUBLE.
DOUBLE	e()	Returns the value of `e`.
DOUBLE	pi()	Returns the value of `pi`.
BIGINT	factorial(INT a)	Returns the factorial of `a` Valid `a` is [0..20].
DOUBLE	cbrt(DOUBLE a)	Returns the cube root of `a` double value.
INT BIGINT	shiftleft(TINYINT\|SMALLINT\|INT a, INT b) shiftleft(BIGINT a, INT b)	Bitwise left shift. Shifts `a` `b` positions to the left. Returns int for tinyint, smallint and int `a`. Returns bigint for bigint `a`.
INT BIGINT	shiftright(TINYINT\|SMALLINT\|INT a, INT b) shiftright(BIGINT a, INT b)	Bitwise right shift. Shifts `a` `b` positions to the right. Returns int for tinyint, smallint and int `a`. Returns bigint for bigint `a`.
INT BIGINT	shiftrightunsigned(TINYINT\|SMALLINT\|INT a, INT b), shiftrightunsigned(BIGINT a, INT b)	Bitwise unsigned right shift. Shifts `a` `b` positions to the right. Returns int for tinyint, smallint and int `a`. Returns bigint for bigint `a`.
T	greatest(T v1, T v2, ...)	Returns the greatest value of the list of values. Fixed to return NULL when one or more arguments are NULL, and strict type restriction relaxed, consistent with ">" operator.
T	least(T v1, T v2, ...)	Returns the least value of the list of values. Fixed to return NULL when one or more arguments are NULL, and strict type restriction relaxed, consistent with "<" operator.
INT	width_bucket(NUMERIC expr, NUMERIC min_value, NUMERIC max_value, INT num_buckets)	Returns an integer between 0 and num_buckets+1 by mapping expr into the ith equally sized bucket. Buckets are made by dividing [min_value, max_value] into equally sized regions. If expr < min_value, return 1, if expr > max_value return num_buckets+1. See https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions214.htm

Space shortcuts

Child pages

Mathematical Functions