Impala Mathematical Functions

Mathematical functions, or arithmetic functions, perform numeric calculations that are typically more complex than basic addition, subtraction, multiplication, and division. For example, these functions include trigonometric, logarithmic, and base conversion operations.

Related information:

The mathematical functions operate mainly on these data types: INT Data Type, BIGINT Data Type, SMALLINT Data Type, TINYINT Data Type, DOUBLE Data Type, FLOAT Data Type, and DECIMAL Data Type (CDH 5.1 or higher only). For the operators that perform the standard operations such as addition, subtraction, multiplication, and division, see Arithmetic Operators.

Function reference:

Impala supports the following mathematical functions:

abs(numeric_type a)

Purpose: Returns the absolute value of the argument.

Return type: Same as the input value

Usage notes: Use this function to ensure all return values are positive. This is different than the positive() function, which returns its argument unchanged (even if the argument was negative).

acos(double a)

Purpose: Returns the arccosine of the argument.

Return type: double

asin(double a)

Purpose: Returns the arcsine of the argument.

Return type: double

atan(double a)

Purpose: Returns the arctangent of the argument.

Return type: double

bin(bigint a)

Purpose: Returns the binary representation of an integer value, that is, a string of 0 and 1 digits.

Return type: string

ceil(double a), ceiling(double a), ceil(decimal(p,s) a), ceiling(decimal(p,s) a)

Purpose: Returns the smallest integer that is greater than or equal to the argument.

Return type: bigint or decimal(p,s) depending on the type of the input argument

conv(bigint num, int from_base, int to_base), conv(string num, int from_base, int to_base)

Purpose: Returns a string representation of an integer value in a particular base. The input value can be a string, for example to convert a hexadecimal number such as fce2 to decimal. To use the return value as a number (for example, when converting to base 10), use CAST() to convert to the appropriate type.

Return type: string

cos(double a)

Purpose: Returns the cosine of the argument.

Return type: double

degrees(double a)

Purpose: Converts argument value from radians to degrees.

Return type: double

e()

Purpose: Returns the mathematical constant e.

Return type: double

exp(double a)

Purpose: Returns the mathematical constant e raised to the power of the argument.

Return type: double

floor(double a)

Purpose: Returns the largest integer that is less than or equal to the argument.

Return type: bigint or decimal(p,s) depending on the type of the input argument

fmod(double a, double b), fmod(float a, float b)

Purpose: Returns the modulus of a floating-point number.

Return type: float or double, depending on type of arguments

Added in: Impala 1.1.1

Usage notes:

Because this function operates on DOUBLE or FLOAT values, it is subject to potential rounding errors for values that cannot be represented precisely. Prefer to use whole numbers, or values that you know can be represented precisely by the DOUBLE or FLOAT types.

Examples:

The following examples show equivalent operations with the fmod() function and the % arithmetic operator, for values not subject to any rounding error.

select fmod(10,3);
+-------------+
| fmod(10, 3) |
+-------------+
| 1           |
+-------------+

select fmod(5.5,2);
+--------------+
| fmod(5.5, 2) |
+--------------+
| 1.5          |
+--------------+

select 10 % 3;
+--------+
| 10 % 3 |
+--------+
| 1      |
+--------+

select 5.5 % 2;
+---------+
| 5.5 % 2 |
+---------+
| 1.5     |
+---------+

The following examples show operations with the fmod() function for values that cannot be represented precisely by the DOUBLE or FLOAT types, and thus are subject to rounding error. fmod(9.9,3.0) returns a value slightly different than the expected 0.9 because of rounding. fmod(9.9,3.3) returns a value quite different from the expected value of 0 because of rounding error during intermediate calculations.

select fmod(9.9,3.0);
+--------------------+
| fmod(9.9, 3.0)     |
+--------------------+
| 0.8999996185302734 |
+--------------------+

select fmod(9.9,3.3);
+-------------------+
| fmod(9.9, 3.3)    |
+-------------------+
| 3.299999713897705 |
+-------------------+

fnv_hash(type v),

Purpose: Returns a consistent 64-bit value derived from the input argument, for convenience of implementing hashing logic in an application.

Return type: BIGINT

Usage notes:

You might use the return value in an application where you perform load balancing, bucketing, or some other technique to divide processing or storage.

Because the result can be any 64-bit value, to restrict the value to a particular range, you can use an expression that includes the ABS() function and the % (modulo) operator. For example, to produce a hash value in the range 0-9, you could use the expression ABS(FNV_HASH(x)) % 10.

This function implements the same algorithm that Impala uses internally for hashing, on systems where the CRC32 instructions are not available.

This function implements the Fowler–Noll–Vo hash function, in particular the FNV-1a variation. This is not a perfect hash function: some combinations of values could produce the same result value. It is not suitable for cryptographic use.

Similar input values of different types could produce different hash values, for example the same numeric value represented as SMALLINT or BIGINT, FLOAT or DOUBLE, or DECIMAL(5,2) or DECIMAL(20,5).

Examples:

[localhost:21000] > create table h (x int, s string);
[localhost:21000] > insert into h values (0, 'hello'), (1,'world'), (1234567890,'antidisestablishmentarianism');
[localhost:21000] > select x, fnv_hash(x) from h;
+------------+----------------------+
| x          | fnv_hash(x)          |
+------------+----------------------+
| 0          | -2611523532599129963 |
| 1          | 4307505193096137732  |
| 1234567890 | 3614724209955230832  |
+------------+----------------------+
[localhost:21000] > select s, fnv_hash(s) from h;
+------------------------------+---------------------+
| s                            | fnv_hash(s)         |
+------------------------------+---------------------+
| hello                        | 6414202926103426347 |
| world                        | 6535280128821139475 |
| antidisestablishmentarianism | -209330013948433970 |
+------------------------------+---------------------+
[localhost:21000] > select s, abs(fnv_hash(s)) % 10 from h;
+------------------------------+-------------------------+
| s                            | abs(fnv_hash(s)) % 10.0 |
+------------------------------+-------------------------+
| hello                        | 8                       |
| world                        | 6                       |
| antidisestablishmentarianism | 4                       |
+------------------------------+-------------------------+

For short argument values, the high-order bits of the result have relatively low entropy:

[localhost:21000] > create table b (x boolean);
[localhost:21000] > insert into b values (true), (true), (false), (false);
[localhost:21000] > select x, fnv_hash(x) from b;
+-------+---------------------+
| x     | fnv_hash(x)         |
+-------+---------------------+
| true  | 2062020650953872396 |
| true  | 2062020650953872396 |
| false | 2062021750465500607 |
| false | 2062021750465500607 |
+-------+---------------------+

Added in: Impala 1.2.2

greatest(bigint a[, bigint b ...]), greatest(double a[, double b ...]), greatest(decimal(p,s) a[, decimal(p,s) b ...]), greatest(string a[, string b ...]), greatest(timestamp a[, timestamp b ...])

Purpose: Returns the largest value from a list of expressions.

Return type: same as the initial argument value, except that integer values are promoted to BIGINT and floating-point values are promoted to DOUBLE; use CAST() when inserting into a smaller numeric column

hex(bigint a), hex(string a)

Purpose: Returns the hexadecimal representation of an integer value, or of the characters in a string.

Return type: string

is_inf(double a),

Purpose: Tests whether a value is equal to the special value "inf", signifying infinity.

Return type: boolean

Usage notes:

Infinity and NaN can be specified in text data files as inf and nan respectively, and Impala interprets them as these special values. They can also be produced by certain arithmetic expressions; for example, 1/0 returns Infinity and pow(-1, 0.5) returns NaN. Or you can cast the literal values, such as CAST('nan' AS DOUBLE) or CAST('inf' AS DOUBLE).

is_nan(double a),

Purpose: Tests whether a value is equal to the special value "NaN", signifying "not a number".

Return type: boolean

Usage notes:

least(bigint a[, bigint b ...]), least(double a[, double b ...]), least(decimal(p,s) a[, decimal(p,s) b ...]), least(string a[, string b ...]), least(timestamp a[, timestamp b ...])

Purpose: Returns the smallest value from a list of expressions.

ln(double a)

Purpose: Returns the natural logarithm of the argument.

Return type: double

log(double base, double a)

Purpose: Returns the logarithm of the second argument to the specified base.

Return type: double

log10(double a)

Purpose: Returns the logarithm of the argument to the base 10.

Return type: double

log2(double a)

Purpose: Returns the logarithm of the argument to the base 2.

Return type: double

max_int(), max_tinyint(), max_smallint(), max_bigint()

Purpose: Returns the largest value of the associated integral type.

Return type: The same as the integral type being checked.

Usage notes: Use the corresponding min_ and max_ functions to check if all values in a column are within the allowed range, before copying data or altering column definitions. If not, switch to the next higher integral type or to a DECIMAL with sufficient precision.

min_int(), min_tinyint(), min_smallint(), min_bigint()

Purpose: Returns the smallest value of the associated integral type (a negative number).

Return type: The same as the integral type being checked.

negative(numeric_type a)

Purpose: Returns the argument with the sign reversed; returns a positive value if the argument was already negative.

Return type: Same as the input value

Usage notes: Use -abs(a) instead if you need to ensure all return values are negative.

pi()

Purpose: Returns the constant pi.

Return type: double

pmod(bigint a, bigint b), pmod(double a, double b)

Purpose: Returns the positive modulus of a number. Primarily for HiveQL compatibility.

Return type: int or double, depending on type of arguments

Examples:

The following examples show how the fmod() function sometimes returns a negative value depending on the sign of its arguments, and the pmod() function returns the same value as fmod(), but sometimes with the sign flipped.

select fmod(-5,2);
+-------------+
| fmod(-5, 2) |
+-------------+
| -1          |
+-------------+

select pmod(-5,2);
+-------------+
| pmod(-5, 2) |
+-------------+
| 1           |
+-------------+

select fmod(-5,-2);
+--------------+
| fmod(-5, -2) |
+--------------+
| -1           |
+--------------+

select pmod(-5,-2);
+--------------+
| pmod(-5, -2) |
+--------------+
| -1           |
+--------------+

select fmod(5,-2);
+-------------+
| fmod(5, -2) |
+-------------+
| 1           |
+-------------+

select pmod(5,-2);
+-------------+
| pmod(5, -2) |
+-------------+
| -1          |
+-------------+

positive(numeric_type a)

Purpose: Returns the original argument unchanged (even if the argument is negative).

Return type: Same as the input value

Usage notes: Use abs() instead if you need to ensure all return values are positive.

pow(double a, double p), power(double a, double p)

Purpose: Returns the first argument raised to the power of the second argument.

Return type: double

precision(numeric_expression)

Purpose: Computes the precision (number of decimal digits) needed to represent the type of the argument expression as a DECIMAL value.

Usage notes:

Typically used in combination with the scale() function, to determine the appropriate DECIMAL(precision,scale) type to declare in a CREATE TABLE statement or CAST() function.

Return type: int

Examples:

The following examples demonstrate how to check the precision and scale of numeric literals or other numeric expressions. Impala represents numeric literals in the smallest appropriate type. 5 is a TINYINT value, which ranges from -128 to 127, therefore 3 decimal digits are needed to represent the entire range, and because it is an integer value there are no fractional digits. 1.333 is interpreted as a DECIMAL value, with 4 digits total and 3 digits after the decimal point.

[localhost:21000] > select precision(5), scale(5);
+--------------+----------+
| precision(5) | scale(5) |
+--------------+----------+
| 3            | 0        |
+--------------+----------+
[localhost:21000] > select precision(1.333), scale(1.333);
+------------------+--------------+
| precision(1.333) | scale(1.333) |
+------------------+--------------+
| 4                | 3            |
+------------------+--------------+
[localhost:21000] > with t1 as
  ( select cast(12.34 as decimal(20,2)) x union select cast(1 as decimal(8,6)) x )
  select precision(x), scale(x) from t1 limit 1;
+--------------+----------+
| precision(x) | scale(x) |
+--------------+----------+
| 24           | 6        |
+--------------+----------+

quotient(bigint numerator, bigint denominator), quotient(double numerator, double denominator)

Purpose: Returns the first argument divided by the second argument, discarding any fractional part. Avoids promoting integer arguments to DOUBLE as happens with the / SQL operator. Also includes an overload that accepts DOUBLE arguments, discards the fractional part of each argument value before dividing, and again returns BIGINT. This function is the same as the DIV operator.

Return type: bigint

radians(double a)

Purpose: Converts argument value from degrees to radians.

Return type: double

rand(), rand(bigint seed)

Purpose: Returns a random value between 0 and 1. After rand() is called with a seed argument, it produces a consistent random sequence based on the seed value.

Return type: double

Usage notes: Currently, the random sequence is reset after each query, and multiple calls to rand() within the same query return the same value each time. For different number sequences that are different for each query, pass a unique seed value to each call to rand(). For example, select rand(unix_timestamp()) from ...

round(double a), round(double a, int d), round(decimal a, int_type d)

Purpose: Rounds a floating-point value. By default (with a single argument), rounds to the nearest integer. Values ending in .5 are rounded up for positive numbers, down for negative numbers (that is, away from zero). The optional second argument specifies how many digits to leave after the decimal point; values greater than zero produce a floating-point return value rounded to the requested number of digits to the right of the decimal point.

Return type: bigint for single float argument. double for double argument when second argument greater than zero. For DECIMAL values, the smallest DECIMAL(p,s) type with appropriate precision and scale.

scale(numeric_expression)

Purpose: Computes the scale (number of decimal digits to the right of the decimal point) needed to represent the type of the argument expression as a DECIMAL value.

Usage notes:

Typically used in combination with the precision() function, to determine the appropriate DECIMAL(precision,scale) type to declare in a CREATE TABLE statement or CAST() function.

Return type: int

Examples:

[localhost:21000] > select precision(5), scale(5);
+--------------+----------+
| precision(5) | scale(5) |
+--------------+----------+
| 3            | 0        |
+--------------+----------+
[localhost:21000] > select precision(1.333), scale(1.333);
+------------------+--------------+
| precision(1.333) | scale(1.333) |
+------------------+--------------+
| 4                | 3            |
+------------------+--------------+
[localhost:21000] > with t1 as
  ( select cast(12.34 as decimal(20,2)) x union select cast(1 as decimal(8,6)) x )
  select precision(x), scale(x) from t1 limit 1;
+--------------+----------+
| precision(x) | scale(x) |
+--------------+----------+
| 24           | 6        |
+--------------+----------+

sign(double a)

Purpose: Returns -1, 0, or 1 to indicate the signedness of the argument value.

Return type: int

sin(double a)

Purpose: Returns the sine of the argument.

Return type: double

sqrt(double a)

Purpose: Returns the square root of the argument.

Return type: double

tan(double a)

Purpose: Returns the tangent of the argument.

Return type: double

unhex(string a)

Purpose: Returns a string of characters with ASCII values corresponding to pairs of hexadecimal digits in the argument.

Return type: string

Built-In Functions

Type Conversion Functions