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.
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 |
DOUBLE | round(DOUBLE a, INT d) | Returns |
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 | ceil(DOUBLE a), ceiling(DOUBLE a) | Returns the minimum BIGINT value that is equal to or greater than |
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 |
DOUBLE | ln(DOUBLE a), ln(DECIMAL a) | Returns the natural logarithm of the argument |
DOUBLE | log10(DOUBLE a), log10(DECIMAL a) | Returns the base-10 logarithm of the argument |
DOUBLE | log2(DOUBLE a), log2(DECIMAL a) | Returns the base-2 logarithm of the argument |
DOUBLE | log(DOUBLE base, DOUBLE a) log(DECIMAL base, DECIMAL a) | Returns the base- |
DOUBLE | pow(DOUBLE a, DOUBLE p), power(DOUBLE a, DOUBLE p) | Returns |
DOUBLE | sqrt(DOUBLE a), sqrt(DECIMAL a) | Returns the square root of |
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 |
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 |
DOUBLE | sin(DOUBLE a), sin(DECIMAL a) | Returns the sine of |
DOUBLE | asin(DOUBLE a), asin(DECIMAL a) | Returns the arc sin of |
DOUBLE | cos(DOUBLE a), cos(DECIMAL a) | Returns the cosine of |
DOUBLE | acos(DOUBLE a), acos(DECIMAL a) | Returns the arccosine of |
DOUBLE | tan(DOUBLE a), tan(DECIMAL a) | Returns the tangent of |
DOUBLE | atan(DOUBLE a), atan(DECIMAL a) | Returns the arctangent of |
DOUBLE | degrees(DOUBLE a), degrees(DECIMAL a) | Converts value of |
DOUBLE | radians(DOUBLE a), radians(DOUBLE a) | Converts value of |
INT or DOUBLE | positive(INT a), positive(DOUBLE a) | Returns |
INT or DOUBLE | negative(INT a), negative(DOUBLE a) | Returns |
DOUBLE or INT | sign(DOUBLE a), sign(DECIMAL a) | Returns the sign of |
DOUBLE | e() | Returns the value of |
DOUBLE | pi() | Returns the value of |
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 Returns int for tinyint, smallint and int |
INT BIGINT | shiftright(TINYINT|SMALLINT|INT a, INT b) shiftright(BIGINT a, INT b) | Bitwise right shift. Shifts Returns int for tinyint, smallint and int |
INT BIGINT | shiftrightunsigned(TINYINT|SMALLINT|INT a, INT b), shiftrightunsigned(BIGINT a, INT b) | Bitwise unsigned right shift. Shifts Returns int for tinyint, smallint and int |
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 |