Math and bitwise functions

This page lists every Spark SQL math and bitwise function: 76 work on Databasin Sail as in Apache Spark and 5 work with a difference noted below. Each function shows its status on Sail, its usage, and examples whose results on Sail match Spark's.

Every supported function also works from PySpark through pyspark.sql.functions when PySpark has a wrapper for it. The Sail SQL reference lists every function page.

Mathematical

- · * · / · % · + · abs · acos · acosh · asin · asinh · atan · atan2 · atanh · bin · bround · cbrt · ceil · ceiling · conv · cos · cosh · cot · csc · degrees · div · e · exp · expm1 · factorial · floor · greatest · hex · hypot · least · ln · log · log10 · log1p · log2 · mod · negative · pi · pmod · positive · pow · power · radians · rand · randn · random · rint · round · sec · sign · signum · sin · sinh · sqrt · tan · tanh · try_add · try_divide · try_mod · try_multiply · try_subtract · unhex · uniform · width_bucket

-

Supported.

expr1 - expr2: Returns expr1-expr2.

Since Spark 1.0.0.

SELECT 2 - 1;
-- 1

*

Supported.

expr1 * expr2: Returns expr1*expr2.

Since Spark 1.0.0.

SELECT 2 * 3;
-- 6

/

Supported.

expr1 / expr2: Returns expr1/expr2. It always performs floating point division.

Since Spark 1.0.0.

SELECT 3 / 2;
-- 1.5

SELECT 2L / 2L;
-- 1.0

%

Supported.

expr1 % expr2, or mod(expr1, expr2): Returns the remainder after expr1/expr2.

Since Spark 1.0.0.

SELECT 2 % 1.8;
-- 0.2

SELECT MOD(2, 1.8);
-- 0.2

+

Supported.

expr1 + expr2: Returns expr1+expr2.

Since Spark 1.0.0.

SELECT 1 + 2;
-- 3

abs

Supported.

abs(expr): Returns the absolute value of the numeric or interval value.

Since Spark 1.2.0.

SELECT abs(-1);
-- 1

SELECT abs(INTERVAL -'1-1' YEAR TO MONTH);
-- 1-1

acos

Supported.

acos(expr): Returns the inverse cosine (a.k.a. arc cosine) of expr, as if computed by java.lang.Math.acos.

Since Spark 1.4.0.

SELECT acos(1);
-- 0.0

SELECT acos(2);
-- NaN

acosh

Supported.

acosh(expr): Returns inverse hyperbolic cosine of expr.

Since Spark 3.0.0.

SELECT acosh(1);
-- 0.0

SELECT acosh(0);
-- NaN

asin

Supported.

asin(expr): Returns the inverse sine (a.k.a. arc sine) the arc sin of expr, as if computed by java.lang.Math.asin.

Since Spark 1.4.0.

SELECT asin(0);
-- 0.0

SELECT asin(2);
-- NaN

asinh

Supported.

asinh(expr): Returns inverse hyperbolic sine of expr.

Since Spark 3.0.0.

SELECT asinh(0);
-- 0.0

atan

Supported.

atan(expr): Returns the inverse tangent (a.k.a. arc tangent) of expr, as if computed by java.lang.Math.atan

Since Spark 1.4.0.

SELECT atan(0);
-- 0.0

atan2

Supported.

atan2(exprY, exprX): Returns the angle in radians between the positive x-axis of a plane and the point given by the coordinates (exprX, exprY), as if computed by java.lang.Math.atan2.

• exprY: coordinate on y-axis
• exprX: coordinate on x-axis

Since Spark 1.4.0.

SELECT atan2(0, 0);
-- 0.0

atanh

Supported.

atanh(expr): Returns inverse hyperbolic tangent of expr.

Since Spark 3.0.0.

SELECT atanh(0);
-- 0.0

SELECT atanh(2);
-- NaN

bin

Supported.

bin(expr): Returns the string representation of the long value expr represented in binary.

Since Spark 1.5.0.

SELECT bin(13);
-- 1101

SELECT bin(-13);
-- 1111111111111111111111111111111111111111111111111111111111110011

SELECT bin(13.3);
-- 1101

bround

Supported.

bround(expr, d): Returns expr rounded to d decimal places using HALF_EVEN rounding mode.

Since Spark 2.0.0.

SELECT bround(2.5, 0);
-- 2

SELECT bround(25, -1);
-- 20

cbrt

Supported.

cbrt(expr): Returns the cube root of expr.

Since Spark 1.4.0.

SELECT cbrt(27.0);
-- 3.0

ceil

Supported.

ceil(expr[, scale]): Returns the smallest number after rounding up that is not smaller than expr. An optional scale parameter can be specified to control the rounding behavior.

Since Spark 3.3.0.

SELECT ceil(-0.1);
-- 0

SELECT ceil(5);
-- 5

SELECT ceil(3.1411, 3);
-- 3.142

SELECT ceil(3.1411, -3);
-- 1000

ceiling

Supported.

ceiling(expr[, scale]): Returns the smallest number after rounding up that is not smaller than expr. An optional scale parameter can be specified to control the rounding behavior.

Since Spark 3.3.0.

SELECT ceiling(-0.1);
-- 0

SELECT ceiling(5);
-- 5

SELECT ceiling(3.1411, 3);
-- 3.142

SELECT ceiling(3.1411, -3);
-- 1000

conv

Supported.

conv(num, from_base, to_base): Convert num from from_base to to_base.

Since Spark 1.5.0.

SELECT conv('100', 2, 10);
-- 4

SELECT conv(-10, 16, -10);
-- -16

cos

Supported.

cos(expr): Returns the cosine of expr, as if computed by java.lang.Math.cos.

• expr: angle in radians

Since Spark 1.4.0.

SELECT cos(0);
-- 1.0

cosh

Supported.

cosh(expr): Returns the hyperbolic cosine of expr, as if computed by java.lang.Math.cosh.

• expr: hyperbolic angle

Since Spark 1.4.0.

SELECT cosh(0);
-- 1.0

cot

Supported.

cot(expr): Returns the cotangent of expr, as if computed by 1/java.lang.Math.tan.

• expr: angle in radians

Since Spark 2.3.0.

SELECT cot(1);
-- 0.6420926159343306

csc

Supported.

csc(expr): Returns the cosecant of expr, as if computed by 1/java.lang.Math.sin.

• expr: angle in radians

Since Spark 3.3.0.

SELECT csc(1);
-- 1.1883951057781212

degrees

Supported.

degrees(expr): Converts radians to degrees.

• expr: angle in radians

Since Spark 1.4.0.

SELECT degrees(3.141592653589793);
-- 180.0

div

Supported. Results match Spark; the auto-generated result column name can differ in some examples.

expr1 div expr2: Divide expr1 by expr2. It returns NULL if an operand is NULL or expr2 is 0. The result is casted to long.

Since Spark 3.0.0.

SELECT 3 div 2;
-- 1

SELECT INTERVAL '1-1' YEAR TO MONTH div INTERVAL '-1' MONTH;
-- -13

e

Supported.

e(): Returns Euler's number, e.

Since Spark 1.5.0.

SELECT e();
-- 2.718281828459045

exp

Supported.

exp(expr): Returns e to the power of expr.

Since Spark 1.4.0.

SELECT exp(0);
-- 1.0

expm1

Supported.

expm1(expr): Returns exp(expr) - 1.

Since Spark 1.4.0.

SELECT expm1(0);
-- 0.0

factorial

Supported.

factorial(expr): Returns the factorial of expr. expr is [0..20]. Otherwise, null.

Since Spark 1.5.0.

SELECT factorial(5);
-- 120

floor

Supported.

floor(expr[, scale]): Returns the largest number after rounding down that is not greater than expr. An optional scale parameter can be specified to control the rounding behavior.

Since Spark 3.3.0.

SELECT floor(-0.1);
-- -1

SELECT floor(5);
-- 5

SELECT floor(3.1411, 3);
-- 3.141

SELECT floor(3.1411, -3);
-- 0

greatest

Supported.

greatest(expr, ...): Returns the greatest value of all parameters, skipping null values.

Since Spark 1.5.0.

SELECT greatest(10, 9, 2, 4, 3);
-- 10

hex

Supported.

hex(expr): Converts expr to hexadecimal.

Since Spark 1.5.0.

SELECT hex(17);
-- 11

SELECT hex('Spark SQL');
-- 537061726B2053514C

hypot

Supported.

hypot(expr1, expr2): Returns sqrt(expr1² + expr2²).

Since Spark 1.4.0.

SELECT hypot(3, 4);
-- 5.0

least

Supported.

least(expr, ...): Returns the least value of all parameters, skipping null values.

Since Spark 1.5.0.

SELECT least(10, 9, 2, 4, 3);
-- 2

ln

Supported.

ln(expr): Returns the natural logarithm (base e) of expr.

Since Spark 1.4.0.

SELECT ln(1);
-- 0.0

log

Supported.

log(base, expr): Returns the logarithm of expr with base.

Since Spark 1.5.0.

SELECT log(10, 100);
-- 2.0

log10

Supported.

log10(expr): Returns the logarithm of expr with base 10.

Since Spark 1.4.0.

SELECT log10(10);
-- 1.0

log1p

Supported.

log1p(expr): Returns log(1 + expr).

Since Spark 1.4.0.

SELECT log1p(0);
-- 0.0

log2

Supported.

log2(expr): Returns the logarithm of expr with base 2.

Since Spark 1.4.0.

SELECT log2(2);
-- 1.0

mod

Supported.

expr1 % expr2, or mod(expr1, expr2): Returns the remainder after expr1/expr2.

Since Spark 2.3.0.

SELECT 2 % 1.8;
-- 0.2

SELECT MOD(2, 1.8);
-- 0.2

negative

Supported.

negative(expr): Returns the negated value of expr.

Since Spark 1.0.0.

SELECT negative(1);
-- -1

pi

Supported.

pi(): Returns pi.

Since Spark 1.5.0.

SELECT pi();
-- 3.141592653589793

pmod

Supported.

pmod(expr1, expr2): Returns the positive value of expr1 mod expr2.

Since Spark 1.5.0.

SELECT pmod(10, 3);
-- 1

SELECT pmod(-10, 3);
-- 2

positive

Supported.

positive(expr): Returns the value of expr.

Since Spark 1.5.0.

SELECT positive(1);
-- 1

pow

Supported.

pow(expr1, expr2): Raises expr1 to the power of expr2.

Since Spark 1.4.0.

SELECT pow(2, 3);
-- 8.0

power

Supported.

power(expr1, expr2): Raises expr1 to the power of expr2.

Since Spark 1.4.0.

SELECT power(2, 3);
-- 8.0

radians

Supported.

radians(expr): Converts degrees to radians.

• expr: angle in degrees

Since Spark 1.4.0.

SELECT radians(180);
-- 3.141592653589793

rand

Supported, differs from Spark. Unseeded calls are random on both engines; with a seed (rand(0)) Sail returns a different number sequence than Spark.

rand([seed]): Returns a random value with independent and identically distributed (i.i.d.) uniformly distributed values in [0, 1).

Note from Spark: The function is non-deterministic in general case.

Since Spark 1.5.0.

SELECT rand();
-- for example: 0.9629742951434543

randn

Supported, differs from Spark. Unseeded calls are random on both engines; with a seed (randn(0)) Sail returns a different number sequence than Spark.

randn([seed]): Returns a random value with independent and identically distributed (i.i.d.) values drawn from the standard normal distribution.

Note from Spark: The function is non-deterministic in general case.

Since Spark 1.5.0.

SELECT randn();
-- for example: -0.3254147983080288

random

Supported, differs from Spark. Unseeded calls are random on both engines; with a seed (random(0)) Sail returns a different number sequence than Spark.

random([seed]): Returns a random value with independent and identically distributed (i.i.d.) uniformly distributed values in [0, 1).

Note from Spark: The function is non-deterministic in general case.

Since Spark 3.0.0.

SELECT random();
-- for example: 0.9629742951434543

rint

Supported.

rint(expr): Returns the double value that is closest in value to the argument and is equal to a mathematical integer.

Since Spark 1.4.0.

SELECT rint(12.3456);
-- 12.0

round

Supported.

round(expr, d): Returns expr rounded to d decimal places using HALF_UP rounding mode.

Since Spark 1.5.0.

SELECT round(2.5, 0);
-- 3

sec

Supported.

sec(expr): Returns the secant of expr, as if computed by 1/java.lang.Math.cos.

• expr: angle in radians

Since Spark 3.3.0.

SELECT sec(0);
-- 1.0

sign

Supported. Results match Spark; the auto-generated result column name can differ in some examples.

sign(expr): Returns -1.0, 0.0 or 1.0 as expr is negative, 0 or positive.

Since Spark 1.4.0.

SELECT sign(40);
-- 1.0

SELECT sign(INTERVAL -'100' YEAR);
-- -1.0

signum

Supported. Results match Spark; the auto-generated result column name can differ in some examples.

signum(expr): Returns -1.0, 0.0 or 1.0 as expr is negative, 0 or positive.

Since Spark 1.4.0.

SELECT signum(40);
-- 1.0

SELECT signum(INTERVAL -'100' YEAR);
-- -1.0

sin

Supported.

sin(expr): Returns the sine of expr, as if computed by java.lang.Math.sin.

• expr: angle in radians

Since Spark 1.4.0.

SELECT sin(0);
-- 0.0

sinh

Supported.

sinh(expr): Returns hyperbolic sine of expr, as if computed by java.lang.Math.sinh.

• expr: hyperbolic angle

Since Spark 1.4.0.

SELECT sinh(0);
-- 0.0

sqrt

Supported.

sqrt(expr): Returns the square root of expr.

Since Spark 1.1.1.

SELECT sqrt(4);
-- 2.0

tan

Supported.

tan(expr): Returns the tangent of expr, as if computed by java.lang.Math.tan.

• expr: angle in radians

Since Spark 1.4.0.

SELECT tan(0);
-- 0.0

tanh

Supported.

tanh(expr): Returns the hyperbolic tangent of expr, as if computed by java.lang.Math.tanh.

• expr: hyperbolic angle

Since Spark 1.4.0.

SELECT tanh(0);
-- 0.0

try_add

Supported, differs from Spark. Year-month interval results are typed INTERVAL YEAR TO MONTH (Spark: INTERVAL YEAR), e.g. '3-0' vs '3'; values equal.

try_add(expr1, expr2): Returns the sum of expr1and expr2 and the result is null on overflow. The acceptable input types are the same with the + operator.

Since Spark 3.2.0.

SELECT try_add(1, 2);
-- 3

SELECT try_add(2147483647, 1);
-- NULL

SELECT try_add(date'2021-01-01', 1);
-- 2021-01-02

SELECT try_add(date'2021-01-01', interval 1 year);
-- 2022-01-01

SELECT try_add(timestamp'2021-01-01 00:00:00', interval 1 day);
-- 2021-01-02 00:00:00

try_divide

Supported.

try_divide(dividend, divisor): Returns dividend/divisor. It always performs floating point division. Its result is always null if expr2 is 0. dividend must be a numeric or an interval. divisor must be a numeric.

Since Spark 3.2.0.

SELECT try_divide(3, 2);
-- 1.5

SELECT try_divide(2L, 2L);
-- 1.0

SELECT try_divide(1, 0);
-- NULL

SELECT try_divide(interval 2 month, 2);
-- 0-1

SELECT try_divide(interval 2 month, 0);
-- NULL

try_mod

Supported.

try_mod(dividend, divisor): Returns the remainder after expr1/expr2. dividend must be a numeric. divisor must be a numeric.

Since Spark 4.0.0.

SELECT try_mod(3, 2);
-- 1

SELECT try_mod(2L, 2L);
-- 0

SELECT try_mod(3.0, 2.0);
-- 1.0

SELECT try_mod(1, 0);
-- NULL

try_multiply

Supported.

try_multiply(expr1, expr2): Returns expr1*expr2 and the result is null on overflow. The acceptable input types are the same with the * operator.

Since Spark 3.3.0.

SELECT try_multiply(2, 3);
-- 6

SELECT try_multiply(-2147483648, 10);
-- NULL

SELECT try_multiply(interval 2 year, 3);
-- 6-0

try_subtract

Supported, differs from Spark. Year-month interval results are typed INTERVAL YEAR TO MONTH (Spark: INTERVAL YEAR), e.g. '1-0' vs '1'; values equal.

try_subtract(expr1, expr2): Returns expr1-expr2 and the result is null on overflow. The acceptable input types are the same with the - operator.

Since Spark 3.3.0.

SELECT try_subtract(2, 1);
-- 1

SELECT try_subtract(-2147483648, 1);
-- NULL

SELECT try_subtract(date'2021-01-02', 1);
-- 2021-01-01

SELECT try_subtract(date'2021-01-01', interval 1 year);
-- 2020-01-01

SELECT try_subtract(timestamp'2021-01-02 00:00:00', interval 1 day);
-- 2021-01-01 00:00:00

unhex

Supported.

unhex(expr): Converts hexadecimal expr to binary.

Since Spark 1.5.0.

SELECT decode(unhex('537061726B2053514C'), 'UTF-8');
-- Spark SQL

uniform

Supported.

uniform(min, max[, seed]): Returns a random value with independent and identically distributed (i.i.d.) values with the specified range of numbers. The random seed is optional. The provided numbers specifying the minimum and maximum values of the range must be constant. If both of these numbers are integers, then the result will also be an integer. Otherwise if one or both of these are floating-point numbers, then the result will also be a floating-point number.

Since Spark 4.0.0.

SELECT uniform(10, 20, 0) > 0 AS result;
-- true

width_bucket

Supported. Results match Spark; the auto-generated result column name can differ in some examples.

width_bucket(value, min_value, max_value, num_bucket): Returns the bucket number to which value would be assigned in an equiwidth histogram with num_bucket buckets, in the range min_value to max_value."

Since Spark 3.1.0.

SELECT width_bucket(5.3, 0.2, 10.6, 5);
-- 3

SELECT width_bucket(-2.1, 1.3, 3.4, 3);
-- 0

SELECT width_bucket(8.1, 0.0, 5.7, 4);
-- 5

SELECT width_bucket(-0.9, 5.2, 0.5, 2);
-- 3

SELECT width_bucket(INTERVAL '0' YEAR, INTERVAL '0' YEAR, INTERVAL '10' YEAR, 10);
-- 1

SELECT width_bucket(INTERVAL '1' YEAR, INTERVAL '0' YEAR, INTERVAL '10' YEAR, 10);
-- 2

SELECT width_bucket(INTERVAL '0' DAY, INTERVAL '0' DAY, INTERVAL '10' DAY, 10);
-- 1

SELECT width_bucket(INTERVAL '1' DAY, INTERVAL '0' DAY, INTERVAL '10' DAY, 10);
-- 2

Bitwise

& · ^ · << · >> · >>> · | · ~ · bit_count · bit_get · getbit · shiftleft · shiftright · shiftrightunsigned

&

Supported.

expr1 & expr2: Returns the result of bitwise AND of expr1 and expr2.

Since Spark 1.4.0.

SELECT 3 & 5;
-- 1

^

Supported.

expr1 ^ expr2: Returns the result of bitwise exclusive OR of expr1 and expr2.

Since Spark 1.4.0.

SELECT 3 ^ 5;
-- 6

<<

Supported.

base << exp: Bitwise left shift.

Note from Spark: << operator is added in Spark 4.0.0 as an alias for shiftleft.

Since Spark 4.0.0.

SELECT shiftleft(2, 1);
-- 4

SELECT 2 << 1;
-- 4

>>

Supported.

base >> expr: Bitwise (signed) right shift.

Note from Spark: >> operator is added in Spark 4.0.0 as an alias for shiftright.

Since Spark 4.0.0.

SELECT shiftright(4, 1);
-- 2

SELECT 4 >> 1;
-- 2

>>>

Supported.

base >>> expr: Bitwise unsigned right shift.

Note from Spark: >>> operator is added in Spark 4.0.0 as an alias for shiftrightunsigned.

Since Spark 4.0.0.

SELECT shiftrightunsigned(4, 1);
-- 2

SELECT 4 >>> 1;
-- 2

|

Supported.

expr1 | expr2: Returns the result of bitwise OR of expr1 and expr2.

Since Spark 1.4.0.

SELECT 3 | 5;
-- 7

~

Supported.

~ expr: Returns the result of bitwise NOT of expr.

Since Spark 1.4.0.

SELECT ~ 0;
-- -1

bit_count

Supported.

bit_count(expr): Returns the number of bits that are set in the argument expr as an unsigned 64-bit integer, or NULL if the argument is NULL.

Since Spark 3.0.0.

SELECT bit_count(0);
-- 0

bit_get

Supported.

bit_get(expr, pos): Returns the value of the bit (0 or 1) at the specified position. The positions are numbered from right to left, starting at zero. The position argument cannot be negative.

Since Spark 3.2.0.

SELECT bit_get(11, 0);
-- 1

SELECT bit_get(11, 2);
-- 0

getbit

Supported.

getbit(expr, pos): Returns the value of the bit (0 or 1) at the specified position. The positions are numbered from right to left, starting at zero. The position argument cannot be negative.

Since Spark 3.2.0.

SELECT getbit(11, 0);
-- 1

SELECT getbit(11, 2);
-- 0

shiftleft

Supported.

base shiftleft exp: Bitwise left shift.

Note from Spark: << operator is added in Spark 4.0.0 as an alias for shiftleft.

Since Spark 1.5.0.

SELECT shiftleft(2, 1);
-- 4

SELECT 2 << 1;
-- 4

shiftright

Supported.

base shiftright expr: Bitwise (signed) right shift.

Note from Spark: >> operator is added in Spark 4.0.0 as an alias for shiftright.

Since Spark 1.5.0.

SELECT shiftright(4, 1);
-- 2

SELECT 4 >> 1;
-- 2

shiftrightunsigned

Supported.

base shiftrightunsigned expr: Bitwise unsigned right shift.

Note from Spark: >>> operator is added in Spark 4.0.0 as an alias for shiftrightunsigned.

Since Spark 1.5.0.

SELECT shiftrightunsigned(4, 1);
-- 2

SELECT 4 >>> 1;
-- 2

Function names, usage text, argument descriptions and examples are from the Apache Spark SQL built-in functions documentation, Copyright The Apache Software Foundation, licensed under the Apache License, Version 2.0. Apache Spark is a trademark of the Apache Software Foundation. Sail status and notes are from Databasin.

Where to go next