Page History

...

Code Block

language	sql
title	UDF's in action

SELECT length(string_col) FROM table_name;

In this case, the length of thebuilt-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.

Tip

title	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

Versions Compared

Old Version 5

New Version 6

Key

Mathematical Functions