Mathematical Functions
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 (as of Hive 1.3.0, 2.0.0). 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 (as of Hive 1.3.0, 2.0.0). 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 (see http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_bin). |
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 (see http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_conv). |
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 (as of Hive 1.2.0). Valid a is [0..20]. |
DOUBLE | cbrt(DOUBLE a) | Returns the cube root of a double value (as of Hive 1.2.0). |
INT BIGINT | shiftleft(TINYINT|SMALLINT|INT a, INT b) shiftleft(BIGINT a, INT b) | Bitwise left shift (as of Hive 1.2.0). 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 (as of Hive 1.2.0). Shifts Returns int for tinyint, smallint and int |
INT BIGINT | shiftrightunsigned(TINYINT|SMALLINT|INTa, INT b), shiftrightunsigned(BIGINT a, INT b) | Bitwise unsigned right shift (as of Hive 1.2.0). Shifts Returns int for tinyint, smallint and int |
T | greatest(T v1, T v2, ...) | Returns the greatest value of the list of values (as of Hive 1.1.0). Fixed to return NULL when one or more arguments are NULL, and strict type restriction relaxed, consistent with ">" operator (as of Hive 2.0.0). |
T | least(T v1, T v2, ...) | Returns the least value of the list of values (as of Hive 1.1.0). Fixed to return NULL when one or more arguments are NULL, and strict type restriction relaxed, consistent with "<" operator (as of Hive 2.0.0). |
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 (as of Hive 3.0.0) |
Collection Functions
Return Type | Name(Signature) | Description |
---|---|---|
array<K> | map_keys(Map<K.V>) | Returns an unordered array containing the keys of the input map. |
array<t> | sort_array(Array<T>) | Sorts the input array in ascending order according to the natural ordering of the array elements and returns it (as of version 0.9.0). |
array<V> | map_values(Map<K.V>) | Returns an unordered array containing the values of the input map. |
boolean | array_contains(Array<T>, value) | Returns TRUE if the array contains value. |
int | size(Map<K.V>) | Returns the number of elements in the map type. |
int | size(Array<T>) | Returns the number of elements in the array type. |
Type Conversion Functions
Return Type | Name(Signature) | Description |
---|---|---|
binary | binary(string|binary) | Casts the parameter into a binary. |
Expected "=" to follow "type" | cast(expr as <type>) | Converts the results of the expression expr to <type>. For example, cast('1' as BIGINT) will convert the string '1' to its integral representation. A null is returned if the conversion does not succeed. If cast(expr as boolean) Hive returns true for a non-empty string. |
Conditional Functions
Return Type | Name(Signature) | Description |
---|---|---|
Return Type | Name(Signature) | Description |
T | if(boolean testCondition, T valueTrue, T valueFalseOrNull) | Returns valueTrue when testCondition is true, returns valueFalseOrNull otherwise. |
boolean | isnull( a ) | Returns true if a is NULL and false otherwise. |
boolean | isnotnull ( a ) | Returns true if a is not NULL and false otherwise. |
T | nvl(T value, T default_value) | Returns default value if value is null else returns value (as of HIve 0.11). |
T | COALESCE(T v1, T v2, ...) | Returns the first v that is not NULL, or NULL if all v's are NULL. |
T | CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END | When a = b, returns c; when a = d, returns e; else returns f. |
T | CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END | When a = true, returns b; when c = true, returns d; else returns e. |
T | nullif( a, b ) | Returns NULL if a=b; otherwise returns a (as of Hive 2.2.0). Shorthand for: CASE WHEN a = b then NULL else a |
void | assert_true(boolean condition) | Throw an exception if 'condition' is not true, otherwise return null (as of Hive 0.8.0). For example, select assert_true (2<1). |