Mathematical Functions

abs(x) [same as x]

Returns the absolute value of x.

acos(x) double

Returns the inverse cosine (a.k.a. arc cosine) of x.

acosh(x) double

Returns inverse hyperbolic cosine of x.

asinh(x) double

Returns inverse hyperbolic sine of x.

atanh(x) double

Returns inverse hyperbolic tangent of x.

add(x, y) [same as x]

Returns the result of adding x to y. The types of x and y must be the same. For integral types, overflow results in an error. Corresponds to sparks’s operator +.

bin(x) varchar

Returns the string representation of the long value x represented in binary.

ceil(x) [same as x]

Returns x rounded up to the nearest integer. Supported types are: BIGINT and DOUBLE.

cosh(x) double

Returns the hyperbolic cosine of x.

cot(x) double

Returns the cotangent of ``x``(measured in radians). Supported type is DOUBLE.

csc(x) double

Returns the cosecant of x.

divide(x, y) double

Returns the results of dividing x by y. Performs floating point division. Corresponds to Spark’s operator /.

SELECT 3 / 2; -- 1.5
SELECT 2L / 2L; -- 1.0
SELECT 3 / 0; -- NULL
exp(x) double

Returns Euler’s number raised to the power of x.

floor(x) [same as x]

Returns x rounded down to the nearest integer. Supported types are: BIGINT and DOUBLE.

hypot(a, b) double

Returns the square root of a squared plus b squared.

log1p(x) double

Returns the natural logarithm of the “given value x plus one”. Return NULL if x is less than or equal to -1.

log2(x) double

Returns the logarithm of x with base 2. Return null for zero and non-positive input.

log10(x) double

Returns the logarithm of x with base 10. Return null for zero and non-positive input.

multiply(x, y) [same as x]

Returns the result of multiplying x by y. The types of x and y must be the same. For integral types, overflow results in an error. Corresponds to Spark’s operator *.

not(x) boolean

Logical not.

SELECT not true; -- false
SELECT not false; -- true
SELECT not NULL; -- NULL
pmod(n, m) [same as n]

Returns the positive remainder of n divided by m. Supported types are: TINYINT, SMALLINT, INTEGER, BIGINT, FLOAT and DOUBLE.

power(x, p) double

Returns x raised to the power of p.

rand() double

Returns a random value with uniformly distributed values in [0, 1).

SELECT rand(); -- 0.9629742951434543
rand(seed, partitionIndex) double

Returns a random value with uniformly distributed values in [0, 1) using a seed formed by combining user-specified seed and framework provided partitionIndex. The framework is responsible for deterministic partitioning of the data and assigning unique partitionIndex to each thread (in a deterministic way). seed must be constant. NULL seed is identical to zero seed. partitionIndex cannot be NULL.

SELECT rand(0);    -- 0.5488135024422883
SELECT rand(NULL); -- 0.5488135024422883
random() double

An alias for rand().

random(seed, partitionIndex) double

An alias for rand(seed, partitionIndex).

remainder(n, m) [same as n]

Returns the modulus (remainder) of n divided by m. Corresponds to Spark’s operator %.

round(x, d) [same as x]

Returns x rounded to d decimal places using HALF_UP rounding mode. In HALF_UP rounding, the digit 5 is rounded up.

sec(x) double

Returns the secant of x.

sinh(x) double

Returns hyperbolic sine of x.

subtract(x, y) [same as x]

Returns the result of subtracting y from x. The types of x and y must be the same. For integral types, overflow results in an error. Corresponds to Spark’s operator -.

unaryminus(x) [same as x]

Returns the negative of x. Corresponds to Spark’s operator -.