Ascend Developer Hub

SQL Functions

Introduction

Ascend uses Spark SQL syntax. This page offers a list of functions supported by the Ascend platform.

Looking for that special function?

Have a SQL function you love, but isn't yet supported in Spark SQL? Let us know, and we'll do our best to add it for you!

Aggregate functions

AVG

Description

avg(expr) - Returns the mean calculated from values of a group.

Argument type Return type
( Integer or Float ) Float

More

AVG on Apache Spark Documentation


BIT_OR

Description

Performs a bitwise OR operation on expression and returns the result.

Argument type Return type
( Integer ) Integer

More

BIT_OR on Apache Spark Documentation


MEAN

Description

mean(expr) - Returns the mean calculated from values of a group.

Argument type Return type
( Integer or Float ) Float

More

MEAN on Apache Spark Documentation


COUNT

Description

count(*) - Returns the total number of retrieved rows, including rows
containing null. count(expr) - Returns the number of rows for which the supplied
expression is non-null. count(DISTINCT expr[, expr...]) - Returns the number of
rows for which the supplied expression(s) are unique and non-null.

Argument type Return type
( Integer or Float or Bool or String or Binary or Date or Timestamp or Array or Struct or Map or ) Integer

More

COUNT on Apache Spark Documentation


MAX

Description

max(expr) - Returns the maximum value of expr.

Argument type Return type
( Integer ) Integer
( Float ) Float
( Bool ) Bool
( String ) String
( Binary ) Binary
( Date ) Date
( Timestamp ) Timestamp

More

MAX on Apache Spark Documentation


MIN

Description

min(expr) - Returns the minimum value of expr.

Argument type Return type
( Integer ) Integer
( Float ) Float
( Bool ) Bool
( String ) String
( Binary ) Binary
( Date ) Date
( Timestamp ) Timestamp

More

MIN on Apache Spark Documentation


SUM

Description

sum(expr) - Returns the sum calculated from values of a group.

Argument type Return type
( Integer ) Integer
( Float ) Float

More

SUM on Apache Spark Documentation


COLLECT_LIST

Description

collect_list(expr) - Collects and returns a list of non-unique elements.

Argument type Return type
( Integer or Float or Bool or String or Binary or Date or Timestamp or Array or Struct or Map ) Array

More

COLLECT_LIST on Apache Spark Documentation


COLLECT_SET

Description

collect_set(expr) - Collects and returns a set of unique elements.

Argument type Return type
( Integer or Bool or String or Binary or Date or Timestamp ) Array

More

COLLECT_SET on Apache Spark Documentation


FIRST

Description

first(expr[, isIgnoreNull]) - Returns the first value of expr for a group of
rows. If isIgnoreNull is true, returns only non-null values.

Argument type Return type
( Integer ) Integer
( Integer, Bool ) Integer
( Float ) Float
( Float, Bool ) Float
( Bool ) Bool
( Bool, Bool ) Bool
( String ) String
( String, Bool ) String
( Binary ) Binary
( Binary, Bool ) Binary
( Date ) Date
( Date, Bool ) Date
( Timestamp ) Timestamp
( Timestamp, Bool ) Timestamp
( Array ) Array
( Array, Bool ) Array
( Struct ) Struct
( Struct, Bool ) Struct
( Map ) Map
( Map, Bool ) Map

More

FIRST on Apache Spark Documentation


FIRST_VALUE

Description

first_value(expr[, isIgnoreNull]) - Returns the first value of expr for a group
of rows. If isIgnoreNull is true, returns only non-null values.

Argument type Return type
( Integer ) Integer
( Integer, Bool ) Integer
( Float ) Float
( Float, Bool ) Float
( Bool ) Bool
( Bool, Bool ) Bool
( String ) String
( String, Bool ) String
( Binary ) Binary
( Binary, Bool ) Binary
( Date ) Date
( Date, Bool ) Date
( Timestamp ) Timestamp
( Timestamp, Bool ) Timestamp
( Array ) Array
( Array, Bool ) Array
( Struct ) Struct
( Struct, Bool ) Struct
( Map ) Map
( Map, Bool ) Map

More

FIRST_VALUE on Apache Spark Documentation


LAST

Description

last(expr[, isIgnoreNull]) - Returns the last value of expr for a group of rows.
If isIgnoreNull is true, returns only non-null values.

Argument type Return type
( Integer ) Integer
( Integer, Bool ) Integer
( Float ) Float
( Float, Bool ) Float
( Bool ) Bool
( Bool, Bool ) Bool
( String ) String
( String, Bool ) String
( Binary ) Binary
( Binary, Bool ) Binary
( Date ) Date
( Date, Bool ) Date
( Timestamp ) Timestamp
( Timestamp, Bool ) Timestamp
( Array ) Array
( Array, Bool ) Array
( Struct ) Struct
( Struct, Bool ) Struct
( Map ) Map
( Map, Bool ) Map

More

LAST on Apache Spark Documentation


LAST_VALUE

Description

last_value(expr[, isIgnoreNull]) - Returns the last value of expr for a group of
rows. If isIgnoreNull is true, returns only non-null values.

Argument type Return type
( Integer ) Integer
( Integer, Bool ) Integer
( Float ) Float
( Float, Bool ) Float
( Bool ) Bool
( Bool, Bool ) Bool
( String ) String
( String, Bool ) String
( Binary ) Binary
( Binary, Bool ) Binary
( Date ) Date
( Date, Bool ) Date
( Timestamp ) Timestamp
( Timestamp, Bool ) Timestamp
( Array ) Array
( Array, Bool ) Array
( Struct ) Struct
( Struct, Bool ) Struct
( Map ) Map
( Map, Bool ) Map

More

LAST_VALUE on Apache Spark Documentation

Statistical Aggregate Functions

CORR

Description

corr(expr1, expr2) - Returns Pearson coefficient of correlation between a set of
number pairs.

Argument type Return type
( Float, Float ) Float

More

CORR on Apache Spark Documentation


COVAR_POP

Description

covar_pop(expr1, expr2) - Returns the population covariance of a set of number
pairs.

Argument type Return type
( Float, Float ) Float

More

COVAR_POP on Apache Spark Documentation


COVAR_SAMP

Description

covar_samp(expr1, expr2) - Returns the sample covariance of a set of number
pairs.

Argument type Return type
( Float, Float ) Float

More

COVAR_SAMP on Apache Spark Documentation


KURTOSIS

Description

kurtosis(expr) - Returns the kurtosis value calculated from values of a group.

Argument type Return type
( Float ) Float

More

KURTOSIS on Apache Spark Documentation


SKEWNESS

Description

skewness(expr) - Returns the skewness value calculated from values of a group.

Argument type Return type
( Float ) Float

More

SKEWNESS on Apache Spark Documentation


STDDEV_POP

Description

stddev_pop(expr) - Returns the population standard deviation calculated from
values of a group.

Argument type Return type
( Float ) Float

More

STDDEV_POP on Apache Spark Documentation


STDDEV_SAMP

Description

stddev_samp(expr) - Returns the sample standard deviation calculated from values
of a group.

Argument type Return type
( Float ) Float

More

STDDEV_SAMP on Apache Spark Documentation


STDDEV

Description

stddev(expr) - Returns the sample standard deviation calculated from values of a
group.

Argument type Return type
( Float ) Float

More

STDDEV on Apache Spark Documentation


VAR_POP

Description

var_pop(expr) - Returns the population variance calculated from values of a
group.

Argument type Return type
( Float ) Float

More

VAR_POP on Apache Spark Documentation


VAR_SAMP

Description

var_samp(expr) - Returns the sample variance calculated from values of a group.

Argument type Return type
( Float ) Float

More

VAR_SAMP on Apache Spark Documentation


VARIANCE

Description

variance(expr) - Returns the sample variance calculated from values of a group.

Argument type Return type
( Float ) Float

More

VARIANCE on Apache Spark Documentation


PERCENTILE

Description

percentile(col, percentage [, frequency]) - Returns the exact percentile value
of numeric column col at the given percentage. The value of percentage must be
between 0.0 and 1.0. The value of frequency should be positive integral.

Argument type Return type
( Float, Float ) Float
( Float, Float, Integer ) Float

More

PERCENTILE on Apache Spark Documentation


PERCENTILE_APPROX

Description

percentile_approx(col, percentage [, accuracy]) - Returns the approximate
percentile value of numeric column col at the given percentage. The value of
percentage must be between 0.0 and 1.0. The accuracy parameter (default: 10000)
is a positive numeric literal which controls approximation accuracy at the cost
of memory. Higher value of accuracy yields better accuracy, 1.0/accuracy is the
relative error of the approximation.

Argument type Return type
( Float, Float ) Float
( Float, Float, Integer ) Float

More

PERCENTILE_APPROX on Apache Spark Documentation


APPROX_PERCENTILE

Description

approx_percentile(col, percentage [, accuracy]) - Returns the approximate
percentile value of numeric column col at the given percentage. The value of
percentage must be between 0.0 and 1.0. The accuracy parameter (default: 10000)
is a positive numeric literal which controls approximation accuracy at the cost
of memory. Higher value of accuracy yields better accuracy, 1.0/accuracy is the
relative error of the approximation.

Argument type Return type
( Float, Float ) Float
( Float, Float, Integer ) Float

More

APPROX_PERCENTILE on Apache Spark Documentation

Approximate Aggregate Functions

APPROX_COUNT_DISTINCT

Description

approx_count_distinct(expr[, relativeSD]) - Returns the estimated cardinality by
HyperLogLog++. relativeSD defines the maximum estimation error allowed.

Argument type Return type
( Integer or Bool or String or Binary or Date or Timestamp ) Integer
( Integer or Bool or String or Binary or Date or Timestamp, Float ) Integer

More

APPROX_COUNT_DISTINCT on Apache Spark Documentation

Misc Functions

ISNULL

Description

isnull(expr) - Returns true if expr is null, or false otherwise.

Argument type Return type
( Integer or Float or Bool or String or Binary or Date or Timestamp or Array or Struct or Map ) Bool

More

ISNULL on Apache Spark Documentation


ISNOTNULL

Description

isnotnull(expr) - Returns true if expr is not null, or false otherwise.

Argument type Return type
( Integer or Float or Bool or String or Binary or Date or Timestamp or Array or Struct or Map ) Bool

More

ISNOTNULL on Apache Spark Documentation


NULLIF

Description

nullif(expr1, expr2) - Returns null if expr1 equals to expr2, or expr1
otherwise.

Argument type Return type
( Integer, Integer ) Integer
( Float, Float ) Float
( Bool, Bool ) Bool
( String, String ) String
( Binary, Binary ) Binary
( Date, Date ) Date
( Timestamp, Timestamp ) Timestamp
( Array, Array ) Array
( Struct, Struct ) Struct
( Map, Map ) Map

More

NULLIF on Apache Spark Documentation


NOT

Description

not(expr) - Logical not.

Argument type Return type
( Bool ) Bool

More

NOT on Apache Spark Documentation


IF

Description

if(expr1, expr2, expr3) - If expr1 evaluates to true, then returns expr2;
otherwise returns expr3.

Argument type Return type
( Bool, Integer, Integer ) Integer
( Bool, Float, Float ) Float
( Bool, Bool, Bool ) Bool
( Bool, String, String ) String
( Bool, Binary, Binary ) Binary
( Bool, Date, Date ) Date
( Bool, Timestamp, Timestamp ) Timestamp
( Bool, Array, Array ) Array
( Bool, Struct, Struct ) Struct
( Bool, Map, Map ) Map

More

IF on Apache Spark Documentation


IFNULL

Description

ifnull(expr1, expr2) - Returns expr2 if expr1 is null, or expr1 otherwise.

Argument type Return type
( Integer, Integer ) Integer
( Float, Float ) Float
( Bool, Bool ) Bool
( String, String ) String
( Binary, Binary ) Binary
( Date, Date ) Date
( Timestamp, Timestamp ) Timestamp
( Array, Array ) Array
( Struct, Struct ) Struct
( Map, Map ) Map

More

IFNULL on Apache Spark Documentation


NVL

Description

nvl(expr1, expr2) - Returns expr2 if expr1 is null, or expr1 otherwise.

Argument type Return type
( Integer, Integer ) Integer
( Float, Float ) Float
( Bool, Bool ) Bool
( String, String ) String
( Binary, Binary ) Binary
( Date, Date ) Date
( Timestamp, Timestamp ) Timestamp
( Array, Array ) Array
( Struct, Struct ) Struct
( Map, Map ) Map

More

NVL on Apache Spark Documentation


NVL2

Description

nvl2(expr1, expr2, expr3) - Returns expr2 if expr1 is not null, or expr3
otherwise.

Argument type Return type
( Integer, Integer, Integer ) Integer
( Float, Float, Float ) Float
( Bool, Bool, Bool ) Bool
( String, String, String ) String
( Binary, Binary, Binary ) Binary
( Date, Date, Date ) Date
( Timestamp, Timestamp, Timestamp ) Timestamp
( Array, Array, Array ) Array
( Struct, Struct, Struct ) Struct
( Map, Map, Map ) Map

More

NVL2 on Apache Spark Documentation


COALESCE

Description

coalesce(expr1, expr2, ...) - Returns the first non-null argument if exists.
Otherwise, null.

Argument type Return type
( Integer ) Integer
( Float ) Float
( Bool ) Bool
( String ) String
( Binary ) Binary
( Date ) Date
( Timestamp ) Timestamp
( Array ) Array
( Struct ) Struct
( Map ) Map

More

COALESCE on Apache Spark Documentation


RAISE_ERROR

Description

raise_error(msg) - Raises an error with the specified string message. The
function always returns a value of string data type.

Argument type Return type
( String ) String

More

RAISE_ERROR on Apache Spark Documentation

Cast Functions

BIGINT

Description

bigint(expr) - Casts the value expr to the target data type bigint.

Argument type Return type
( Integer or Float ) Integer

More

BIGINT on Apache Spark Documentation


DATE

Description

date(expr) - Casts the value expr to the target data type date.

Argument type Return type
( String or Date or Timestamp ) Date

More

DATE on Apache Spark Documentation


DOUBLE

Description

double(expr) - Casts the value expr to the target data type double.

Argument type Return type
( Integer or Float ) Float

More

DOUBLE on Apache Spark Documentation


TIMESTAMP

Description

timestamp(expr) - Casts the value expr to the target data type timestamp.

Argument type Return type
( Integer or Float or String or Date or Timestamp ) Timestamp

More

TIMESTAMP on Apache Spark Documentation

Analytic Functions: Numbering Functions

RANK

Description

rank() - Computes the rank of a value in a group of values. The result is one
plus the number of rows preceding or equal to the current row in the ordering of
the partition. The values will produce gaps in the sequence.

Argument type Return type
No arguments Integer

More

RANK on Apache Spark Documentation


DENSE_RANK

Description

dense_rank() - Computes the rank of a value in a group of values. The result is
one plus the previously assigned rank value. Unlike the function rank,
dense_rank will not produce gaps in the ranking sequence.

Argument type Return type
No arguments Integer

More

DENSE_RANK on Apache Spark Documentation


PERCENT_RANK

Description

percent_rank() - Computes the percentage ranking of a value in a group of
values.

Argument type Return type
No arguments Float

More

PERCENT_RANK on Apache Spark Documentation


CUME_DIST

Description

cume_dist() - Computes the position of a value relative to all values in the
partition.

Argument type Return type
No arguments Float

More

CUME_DIST on Apache Spark Documentation


NTILE

Description

ntile(n) - Divides the rows for each window partition into n buckets ranging
from 1 to at most n.

Argument type Return type
( Integer ) Integer

More

NTILE on Apache Spark Documentation


ROW_NUMBER

Description

row_number() - Assigns a unique, sequential number to each row, starting with
one, according to the ordering of rows within the window partition.

Argument type Return type
No arguments Integer

More

ROW_NUMBER on Apache Spark Documentation


BIN

Description

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

Argument type Return type
( Integer ) String

More

BIN on Apache Spark Documentation


CONV

Description

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

Argument type Return type
( String, Integer, Integer ) String

More

CONV on Apache Spark Documentation


MONOTONICALLY_INCREASING_ID

Description

monotonically_increasing_id() - Returns monotonically increasing 64-bit
integers. The generated ID is guaranteed to be monotonically increasing and
unique, but not consecutive. The current implementation puts the partition ID in
the upper 31 bits, and the lower 33 bits represent the record number within each
partition. The assumption is that the data frame has less than 1 billion
partitions, and each partition has less than 8 billion records.

Argument type Return type
No arguments Integer

More

MONOTONICALLY_INCREASING_ID on Apache Spark Documentation

Analytic Functions: Navigation Functions

LEAD

Description

lead(input[, offset[, default]]) - Returns the value of input at the offsetth
row after the current row in the window. The default value of offset is 1 and
the default value of default is null. If the value of input at the offsetth row
is null, null is returned. If there is no such an offset row (e.g., when the
offset is 1, the last row of the window does not have any subsequent row),
default is returned.

Argument type Return type
( Integer ) Integer
( Integer, Integer ) Integer
( Integer, Integer, Integer ) Integer
( Float ) Float
( Float, Integer ) Float
( Float, Integer, Float ) Float
( Bool ) Bool
( Bool, Integer ) Bool
( Bool, Integer, Bool ) Bool
( String ) String
( String, Integer ) String
( String, Integer, String ) String
( Binary ) Binary
( Binary, Integer ) Binary
( Binary, Integer, Binary ) Binary
( Date ) Date
( Date, Integer ) Date
( Date, Integer, Date ) Date
( Timestamp ) Timestamp
( Timestamp, Integer ) Timestamp
( Timestamp, Integer, Timestamp ) Timestamp
( Array ) Array
( Array, Integer ) Array
( Array, Integer, Array ) Array
( Struct ) Struct
( Struct, Integer ) Struct
( Struct, Integer, Struct ) Struct
( Map ) Map
( Map, Integer ) Map
( Map, Integer, Map ) Map

More

LEAD on Apache Spark Documentation


LAG

Description

lag(input[, offset[, default]]) - Returns the value of input at the offsetth row
before the current row in the window. The default value of offset is 1 and the
default value of default is null. If the value of input at the offsetth row is
null, null is returned. If there is no such offset row (e.g., when the offset is
1, the first row of the window does not have any previous row), default is
returned.

Argument type Return type
( Integer ) Integer
( Integer, Integer ) Integer
( Integer, Integer, Integer ) Integer
( Float ) Float
( Float, Integer ) Float
( Float, Integer, Float ) Float
( Bool ) Bool
( Bool, Integer ) Bool
( Bool, Integer, Bool ) Bool
( String ) String
( String, Integer ) String
( String, Integer, String ) String
( Binary ) Binary
( Binary, Integer ) Binary
( Binary, Integer, Binary ) Binary
( Date ) Date
( Date, Integer ) Date
( Date, Integer, Date ) Date
( Timestamp ) Timestamp
( Timestamp, Integer ) Timestamp
( Timestamp, Integer, Timestamp ) Timestamp
( Array ) Array
( Array, Integer ) Array
( Array, Integer, Array ) Array
( Struct ) Struct
( Struct, Integer ) Struct
( Struct, Integer, Struct ) Struct
( Map ) Map
( Map, Integer ) Map
( Map, Integer, Map ) Map

More

LAG on Apache Spark Documentation

Bit Functions

SHIFTLEFT

Description

shiftleft(base, expr) - Bitwise left shift.

Argument type Return type
( Integer, Integer ) Integer

More

SHIFTLEFT on Apache Spark Documentation


SHIFTRIGHT

Description

shiftright(base, expr) - Bitwise (signed) right shift.

Argument type Return type
( Integer, Integer ) Integer

More

SHIFTRIGHT on Apache Spark Documentation


SHIFTRIGHTUNSIGNED

Description

shiftrightunsigned(base, expr) - Bitwise unsigned right shift.

Argument type Return type
( Integer, Integer ) Integer

More

SHIFTRIGHTUNSIGNED on Apache Spark Documentation

Mathematical functions

E

Description

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

Argument type Return type
No arguments Float

More

E on Apache Spark Documentation


PI

Description

pi() - Returns pi.

Argument type Return type
No arguments Float

More

PI on Apache Spark Documentation


ABS

Description

abs(expr) - Returns the absolute value of the numeric value.

Argument type Return type
( Integer ) Integer
( Float ) Float

More

ABS on Apache Spark Documentation


NEGATIVE

Description

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

Argument type Return type
( Integer ) Integer
( Float ) Float

More

NEGATIVE on Apache Spark Documentation


SIGN

Description

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

Argument type Return type
( Integer or Float ) Float

More

SIGN on Apache Spark Documentation


SIGNUM

Description

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

Argument type Return type
( Integer or Float ) Float

More

SIGNUM on Apache Spark Documentation


ISNAN

Description

isnan(expr) - Returns true if expr is NaN, or false otherwise.

Argument type Return type
( Float ) Bool

More

ISNAN on Apache Spark Documentation


RAND

Description

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

Argument type Return type
No arguments Float
( Integer ) Float

More

RAND on Apache Spark Documentation


RANDN

Description

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

Argument type Return type
No arguments Float
( Integer ) Float

More

RANDN on Apache Spark Documentation


UUID

Description

uuid() - Returns an universally unique identifier (UUID) string. The value is
returned as a canonical UUID 36-character string.

Argument type Return type
No arguments String

More

UUID on Apache Spark Documentation


SQRT

Description

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

Argument type Return type
( Float ) Float

More

SQRT on Apache Spark Documentation


CBRT

Description

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

Argument type Return type
( Float ) Float

More

CBRT on Apache Spark Documentation


HYPOT

Description

hypot(expr1, expr2) - Returns sqrt(expr12 + expr22).

Argument type Return type
( Float, Float ) Float

More

HYPOT on Apache Spark Documentation


POW

Description

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

Argument type Return type
( Float, Float ) Float

More

POW on Apache Spark Documentation


EXP

Description

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

Argument type Return type
( Float ) Float

More

EXP on Apache Spark Documentation


EXPM1

Description

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

Argument type Return type
( Float ) Float

More

EXPM1 on Apache Spark Documentation


LN

Description

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

Argument type Return type
( Float ) Float

More

LN on Apache Spark Documentation


LOG

Description

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

Argument type Return type
( Float ) Float
( Float, Float ) Float

More

LOG on Apache Spark Documentation


LOG10

Description

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

Argument type Return type
( Float ) Float

More

LOG10 on Apache Spark Documentation


LOG1P

Description

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

Argument type Return type
( Float ) Float

More

LOG1P on Apache Spark Documentation


LOG2

Description

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

Argument type Return type
( Float ) Float

More

LOG2 on Apache Spark Documentation


GREATEST

Description

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

Argument type Return type
( Integer, Integer ) Integer
( Float, Float ) Float
( Bool, Bool ) Bool
( String, String ) String
( Binary, Binary ) Binary
( Date, Date ) Date
( Timestamp, Timestamp ) Timestamp

More

GREATEST on Apache Spark Documentation


LEAST

Description

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

Argument type Return type
( Integer, Integer ) Integer
( Float, Float ) Float
( Bool, Bool ) Bool
( String, String ) String
( Binary, Binary ) Binary
( Date, Date ) Date
( Timestamp, Timestamp ) Timestamp

More

LEAST on Apache Spark Documentation


MOD

Description

mod(expr1, expr2) - Returns the remainder after expr1/expr2.

Argument type Return type
( Integer, Integer ) Integer
( Float, Float ) Float

More

MOD on Apache Spark Documentation


PMOD

Description

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

Argument type Return type
( Integer, Integer ) Integer
( Float, Float ) Float

More

PMOD on Apache Spark Documentation


FACTORIAL

Description

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

Argument type Return type
( Integer ) Integer

More

FACTORIAL on Apache Spark Documentation


NANVL

Description

nanvl(expr1, expr2) - Returns expr1 if it's not NaN, or expr2 otherwise.

Argument type Return type
( Float, Float ) Float

More

NANVL on Apache Spark Documentation

Rounding Functions

ROUND

Description

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

Argument type Return type
( Float ) Float
( Float, Integer ) Float

More

ROUND on Apache Spark Documentation


BROUND

Description

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

Argument type Return type
( Float ) Float
( Float, Integer ) Float

More

BROUND on Apache Spark Documentation


CEIL

Description

ceil(expr) - Returns the smallest integer not smaller than expr.

Argument type Return type
( Float ) Float

More

CEIL on Apache Spark Documentation


FLOOR

Description

floor(expr) - Returns the largest integer not greater than expr.

Argument type Return type
( Float ) Float

More

FLOOR on Apache Spark Documentation


RINT

Description

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

Argument type Return type
( Float ) Float

More

RINT on Apache Spark Documentation

Trigonometric and hyperbolic functions

DEGREES

Description

degrees(expr) - Converts radians to degrees.

Argument type Return type
( Float ) Float

More

DEGREES on Apache Spark Documentation


RADIANS

Description

radians(expr) - Converts degrees to radians.

Argument type Return type
( Float ) Float

More

RADIANS on Apache Spark Documentation


COS

Description

cos(expr) - Returns the cosine of expr.

Argument type Return type
( Float ) Float

More

COS on Apache Spark Documentation


COSH

Description

cosh(expr) - Returns the hyperbolic cosine of expr.

Argument type Return type
( Float ) Float

More

COSH on Apache Spark Documentation


ACOS

Description

acos(expr) - Returns the inverse cosine (a.k.a. arccosine) of expr if
-1\<=expr\<=1 or NaN otherwise.

Argument type Return type
( Float ) Float

More

ACOS on Apache Spark Documentation


SIN

Description

sin(expr) - Returns the sine of expr.

Argument type Return type
( Float ) Float

More

SIN on Apache Spark Documentation


SINH

Description

sinh(expr) - Returns the hyperbolic sine of expr.

Argument type Return type
( Float ) Float

More

SINH on Apache Spark Documentation


ASIN

Description

asin(expr) - Returns the inverse sine (a.k.a. arcsine) the arc sin of expr if
-1\<=expr\<=1 or NaN otherwise.

Argument type Return type
( Float ) Float

More

ASIN on Apache Spark Documentation


TAN

Description

tan(expr) - Returns the tangent of expr.

Argument type Return type
( Float ) Float

More

TAN on Apache Spark Documentation


TANH

Description

tanh(expr) - Returns the hyperbolic tangent of expr.

Argument type Return type
( Float ) Float

More

TANH on Apache Spark Documentation


COT

Description

cot(expr) - Returns the cotangent of expr.

Argument type Return type
( Float ) Float

More

COT on Apache Spark Documentation


ATAN

Description

atan(expr) - Returns the inverse tangent (a.k.a. arctangent).

Argument type Return type
( Float ) Float

More

ATAN on Apache Spark Documentation


ATAN2

Description

atan2(expr1, expr2) - Returns the angle in radians between the positive x-axis
of a plane and the point given by the coordinates (expr1, expr2).

Argument type Return type
( Float, Float ) Float

More

ATAN2 on Apache Spark Documentation

Hash functions

HASH

Description

hash(expr1, expr2, ...) - Returns a hash value of the arguments.

Argument type Return type
( Integer or Float or Bool or String or Binary or Date or Timestamp or Array or Struct or Map ) Integer

More

HASH on Apache Spark Documentation


CRC32

Description

crc32(expr) - Returns a cyclic redundancy check value of the expr as a bigint.

Argument type Return type
( Binary ) Integer

More

CRC32 on Apache Spark Documentation


MD5

Description

md5(expr) - Returns an MD5 128-bit checksum as a hex string of expr.

Argument type Return type
( Binary ) String

More

MD5 on Apache Spark Documentation


SHA

Description

sha(expr) - Returns a sha1 hash value as a hex string of the expr.

Argument type Return type
( Binary ) String

More

SHA on Apache Spark Documentation


SHA1

Description

sha1(expr) - Returns a sha1 hash value as a hex string of the expr.

Argument type Return type
( Binary ) String

More

SHA1 on Apache Spark Documentation


SHA2

Description

sha2(expr, bitLength) - Returns a checksum of SHA-2 family as a hex string of
expr. SHA-224, SHA-256, SHA-384, and SHA-512 are supported. Bit length of 0 is
equivalent to 256.

Argument type Return type
( Binary, Integer ) String

More

SHA2 on Apache Spark Documentation

String functions

LENGTH

Description

length(expr) - Returns the character length of expr or number of bytes in binary
data.

Argument type Return type
( String or Binary ) Integer

More

LENGTH on Apache Spark Documentation


ASCII

Description

ascii(str) - Returns the numeric value of the first character of str.

Argument type Return type
( String ) Integer

More

ASCII on Apache Spark Documentation


CHAR

Description

char(expr) - Returns the ASCII character having the binary equivalent to expr.
If n is larger than 256 the result is equivalent to chr(n % 256).

Argument type Return type
( Integer ) String

More

CHAR on Apache Spark Documentation


CHR

Description

chr(expr) - Returns the ASCII character having the binary equivalent to expr. If
n is larger than 256 the result is equivalent to chr(n % 256).

Argument type Return type
( Integer ) String

More

CHR on Apache Spark Documentation


ELT

Description

elt(n, str1, str2, ...) - Returns the n-th string, e.g., returns str2 when n is
2.

Argument type Return type
( Integer, String ) String
( Integer, Binary ) Binary

More

ELT on Apache Spark Documentation


CONCAT

Description

concat(str1, str2, ..., strN) - Returns the concatenation of str1, str2, ...,
strN.

Argument type Return type
No arguments String
( Binary ) Binary

More

CONCAT on Apache Spark Documentation


CONCAT_WS

Description

concat_ws(sep, [str | array(str)]+) - Returns the concatenation of the strings
separated by sep.

Argument type Return type
( String ) String

More

CONCAT_WS on Apache Spark Documentation


FORMAT_NUMBER

Description

format_number(expr1, expr2) - Formats the number expr1 like
'#,###,###.##', rounded to expr2 decimal places. If expr2 is 0, the
result has no decimal point or fractional part. This is supposed to function
like MySQL's FORMAT.

Argument type Return type
( Float, Integer ) String

More

FORMAT_NUMBER on Apache Spark Documentation


FORMAT_STRING

Description

format_string(strfmt, obj, ...) - Returns a formatted string from printf-style
format strings.

Argument type Return type
( String ) String

More

FORMAT_STRING on Apache Spark Documentation


PRINTF

Description

printf(strfmt, obj, ...) - Returns a formatted string from printf-style format
strings.

Argument type Return type
( String ) String

More

PRINTF on Apache Spark Documentation


LPAD

Description

lpad(str, len, pad) - Returns str, left-padded with pad to a length of len. If
str is longer than len, the return value is shortened to len characters.

Argument type Return type
( String, Integer, String ) String

More

LPAD on Apache Spark Documentation


LCASE

Description

lcase(str) - Returns str with all characters changed to lowercase.

Argument type Return type
( String ) String

More

LCASE on Apache Spark Documentation


LOWER

Description

lower(str) - Returns str with all characters changed to lowercase.

Argument type Return type
( String ) String

More

LOWER on Apache Spark Documentation


LTRIM

Description

ltrim(str) - Removes the leading space characters from str. ltrim(trimStr, str)

  • Removes the leading string contains the characters from the trim string
Argument type Return type
( String ) String
( String, String ) String

More

LTRIM on Apache Spark Documentation


REGEXP_EXTRACT

Description

regexp_extract(str, regexp[, idx]) - Extracts a group that matches regexp.

Argument type Return type
( String, String, Integer ) String

More

REGEXP_EXTRACT on Apache Spark Documentation


REGEXP_REPLACE

Description

regexp_replace(str, regexp, rep) - Replaces all substrings of str that match
regexp with rep.

Argument type Return type
( String, String, String ) String

More

REGEXP_REPLACE on Apache Spark Documentation


REPEAT

Description

repeat(str, n) - Returns the string which repeats the given string value n
times.

Argument type Return type
( String, Integer ) String

More

REPEAT on Apache Spark Documentation


REPLACE

Description

replace(str, search[, replace]) - Replaces all occurrences of search with
replace.

Argument type Return type
( String, String ) String
( String, String, String ) String

More

REPLACE on Apache Spark Documentation


REVERSE

Description

reverse(str) - Returns the reversed given string.

Argument type Return type
( String ) String

More

REVERSE on Apache Spark Documentation


RPAD

Description

rpad(str, len, pad) - Returns str, right-padded with pad to a length of len. If
str is longer than len, the return value is shortened to len characters.

Argument type Return type
( String, Integer, String ) String

More

RPAD on Apache Spark Documentation


RTRIM

Description

rtrim(str) - Removes the trailing space characters from str. rtrim(trimStr, str)

  • Removes the trailing string which contains the characters from the trim string
Argument type Return type
( String ) String
( String, String ) String

More

RTRIM on Apache Spark Documentation


SPLIT

Description

split(str, regex) - Splits str around occurrences that match regex.

Argument type Return type
( String, String ) Array

More

SPLIT on Apache Spark Documentation


INSTR

Description

instr(str, substr) - Returns the (1-based) index of the first occurrence of
substr in str.

Argument type Return type
( String, String ) Integer

More

INSTR on Apache Spark Documentation


LOCATE

Description

locate(substr, str[, pos]) - Returns the position of the first occurrence of
substr in str after position pos. The given pos and return value are 1-based.

Argument type Return type
( String, String ) Integer
( String, String, Integer ) Integer

More

LOCATE on Apache Spark Documentation


POSITION

Description

position(substr, str[, pos]) - Returns the position of the first occurrence of
substr in str after position pos. The given pos and return value are 1-based.

Argument type Return type
( String, String ) Integer
( String, String, Integer ) Integer

More

POSITION on Apache Spark Documentation


SUBSTRING_INDEX

Description

substring_index(str, delim, count) - Returns the substring from str before count
occurrences of the delimiter delim. If count is positive, everything to the left
of the final delimiter (counting from the left) is returned. If count is
negative, everything to the right of the final delimiter (counting from the
right) is returned. The function substring_index performs a case-sensitive match
when searching for delim.

Argument type Return type
( String, String, Integer ) String

More

SUBSTRING_INDEX on Apache Spark Documentation


SUBSTRING

Description

substring(str, pos[, len]) - Returns the substring of str that starts at pos and
is of length len, or the slice of byte array that starts at pos and is of length
len.

Argument type Return type
( String, Integer ) String
( String, Integer, Integer ) String
( Binary, Integer ) Binary
( Binary, Integer, Integer ) Binary

More

SUBSTRING on Apache Spark Documentation


SUBSTR

Description

substr(str, pos[, len]) - Returns the substring of str that starts at pos and is
of length len, or the slice of byte array that starts at pos and is of length
len.

Argument type Return type
( String, Integer ) String
( String, Integer, Integer ) String
( Binary, Integer ) Binary
( Binary, Integer, Integer ) Binary

More

SUBSTR on Apache Spark Documentation


LEFT

Description

left(str, len) - Returns the leftmost len(len can be string type) characters
from the string str,if len is less or equal than 0 the result is an empty
string.

Argument type Return type
( String, Integer ) String

More

LEFT on Apache Spark Documentation


RIGHT

Description

right(str, len) - Returns the rightmost len(len can be string type) characters
from the string str,if len is less or equal than 0 the result is an empty
string.

Argument type Return type
( String, Integer ) String

More

RIGHT on Apache Spark Documentation


BASE64

Description

base64(bin) - Converts the argument from a binary bin to a base 64 string.

Argument type Return type
( Binary ) String

More

BASE64 on Apache Spark Documentation


UNBASE64

Description

unbase64(str) - Converts the argument from a base 64 string str to a binary.

Argument type Return type
( String ) Binary

More

UNBASE64 on Apache Spark Documentation


DECODE

Description

decode(bin, charset) - Decodes the first argument using the second argument
character set.

Argument type Return type
( Binary, String ) String

More

DECODE on Apache Spark Documentation


ENCODE

Description

encode(str, charset) - Encodes the first argument using the second argument
character set.

Argument type Return type
( String, String ) Binary

More

ENCODE on Apache Spark Documentation


TRIM

Description

trim(str) - Removes the leading and trailing space characters from str.
trim(trimStr, str) - Removes the leading and trailing string which contains the
characters from the trim string

Argument type Return type
( String ) String
( String, String ) String

More

TRIM on Apache Spark Documentation


UCASE

Description

ucase(str) - Returns str with all characters changed to uppercase.

Argument type Return type
( String ) String

More

UCASE on Apache Spark Documentation


UPPER

Description

upper(str) - Returns str with all characters changed to uppercase.

Argument type Return type
( String ) String

More

UPPER on Apache Spark Documentation


INITCAP

Description

initcap(str) - Returns str with the first letter of each word in uppercase. All
other letters are in lowercase. Words are delimited by white space.

Argument type Return type
( String ) String

More

INITCAP on Apache Spark Documentation


HEX

Description

hex(expr) - Converts expr to hexadecimal.

Argument type Return type
( Integer or String or Binary ) String

More

HEX on Apache Spark Documentation


UNHEX

Description

unhex(expr) - Converts hexadecimal expr to binary.

Argument type Return type
( String ) Binary

More

UNHEX on Apache Spark Documentation


LEVENSHTEIN

Description

levenshtein(str1, str2) - Returns the Levenshtein distance between the two given
strings.

Argument type Return type
( String, String ) Integer

More

LEVENSHTEIN on Apache Spark Documentation


SOUNDEX

Description

soundex(str) - Returns Soundex code of the string.

Argument type Return type
( String ) String

More

SOUNDEX on Apache Spark Documentation


TRANSLATE

Description

translate(input, from, to) - Translates the input string by replacing the
characters present in the from string with the corresponding characters in the
to string.

Argument type Return type
( String, String, String ) String

More

TRANSLATE on Apache Spark Documentation

JSON functions

TO_JSON

Description

to_json(expr[, options]) - Returns a json string with a given struct value

Argument type Return type
( Array or Struct or Map ) String
( Array or Struct or Map, Map ) String

More

TO_JSON on Apache Spark Documentation


GET_JSON_OBJECT

Description

get_json_object(json_txt, path) - Extracts a json object from path.

Argument type Return type
( String, String ) String

More

GET_JSON_OBJECT on Apache Spark Documentation

Array functions

ARRAY

Description

array(expr, ...) - Returns an array with the given elements.

Argument type Return type
No arguments Array
No arguments Array
No arguments Array
No arguments Array
No arguments Array
No arguments Array
No arguments Array
No arguments Array
No arguments Array
No arguments Array

More

ARRAY on Apache Spark Documentation


ARRAY_CONTAINS

Description

array_contains(array, value) - Returns true if the array contains the value.

Argument type Return type
( Array, Integer or Float or Bool or String or Binary or Date or Timestamp or Array or Struct or Map ) Bool

More

ARRAY_CONTAINS on Apache Spark Documentation


SORT_ARRAY

Description

sort_array(array[, ascendingOrder]) - Sorts the input array in ascending or
descending order according to the natural ordering of the array elements.

Argument type Return type
( Array ) Array
( Array, Bool ) Array

More

SORT_ARRAY on Apache Spark Documentation


SIZE

Description

size(expr) - Returns the size of an array or a map. Returns -1 if null.

Argument type Return type
( Array or Map ) Integer

More

SIZE on Apache Spark Documentation


ARRAY_JOIN

Description

array_join(array, delimiter[, nullReplacement]) - Concatenates the elements of
the given array using the delimiter and an optional string to replace nulls. If
no value is set for nullReplacement, any null value is filtered.

Argument type Return type
( Array, String ) String
( Array, String, String ) String

More

ARRAY_JOIN on Apache Spark Documentation

Struct functions

STRUCT

Description

struct(col1, col2, col3, ...) - Creates a struct with the given field values.

Argument type Return type
No arguments Struct

More

STRUCT on Apache Spark Documentation


NAMED_STRUCT

Description

named_struct(name1, val1, name2, val2, ...) - Creates a struct with the given
field names and values.

Argument type Return type
( String ) Struct

More

NAMED_STRUCT on Apache Spark Documentation


FROM_JSON

Description

from_json(jsonStr, schema[, options]) - Returns a struct value with the given
jsonStr and schema.

Argument type Return type
( String, String ) Struct
( String, String, Map ) Struct

More

FROM_JSON on Apache Spark Documentation

Map functions

MAP

Description

map(key0, value0, key1, value1, ...) - Creates a map with the given key/value
pairs.

Argument type Return type
No arguments Map

More

MAP on Apache Spark Documentation


MAP_FROM_ARRAYS

Description

map_from_arrays(keys, values) - Creates a map with a pair of the given key/value
arrays. All elements in keys should not be null

Argument type Return type
( Array, Array ) Map

More

MAP_FROM_ARRAYS on Apache Spark Documentation


MAP_FROM_ENTRIES

Description

map_from_entries(arrayOfEntries) - Returns a map created from the given array of
entries.

Argument type Return type
( Array ) Map

More

MAP_FROM_ENTRIES on Apache Spark Documentation


MAP_KEYS

Description

map_keys(map) - Returns an unordered array containing the keys of the map.

Argument type Return type
( Map ) Array

More

MAP_KEYS on Apache Spark Documentation


MAP_VALUES

Description

map_values(map) - Returns an unordered array containing the values of the map.

Argument type Return type
( Map ) Array

More

MAP_VALUES on Apache Spark Documentation


STR_TO_MAP

Description

str_to_map(text[, pairDelim[, keyValueDelim]]) - Creates a map after splitting
the text into key/value pairs using delimiters. Default delimiters are ',' for
pairDelim and ':' for keyValueDelim.

Argument type Return type
( String ) Map
( String, String ) Map
( String, String, String ) Map

More

STR_TO_MAP on Apache Spark Documentation

DATE functions

ADD_MONTHS

Description

add_months(start_date, num_months) - Returns the date that is num_months after
start_date.

Argument type Return type
( Date, Integer ) Date

More

ADD_MONTHS on Apache Spark Documentation


CURRENT_DATE

Description

current_date() - Returns the current date at the start of query evaluation.

Argument type Return type
No arguments Date

More

CURRENT_DATE on Apache Spark Documentation


DATE_ADD

Description

date_add(start_date, num_days) - Returns the date that is num_days after
start_date.

Argument type Return type
( Date, Integer ) Date

More

DATE_ADD on Apache Spark Documentation


DATE_SUB

Description

date_sub(start_date, num_days) - Returns the date that is num_days before
start_date.

Argument type Return type
( Date, Integer ) Date

More

DATE_SUB on Apache Spark Documentation


DATEDIFF

Description

datediff(endDate, startDate) - Returns the number of days from startDate to
endDate.

Argument type Return type
( Date, Date ) Integer

More

DATEDIFF on Apache Spark Documentation


LAST_DAY

Description

last_day(date) - Returns the last day of the month which the date belongs to.

Argument type Return type
( Date ) Date

More

LAST_DAY on Apache Spark Documentation


NEXT_DAY

Description

next_day(start_date, day_of_week) - Returns the first date which is later than
start_date and named as indicated.

Argument type Return type
( Date, String ) Date

More

NEXT_DAY on Apache Spark Documentation


TO_DATE

Description

to_date(date_str[, fmt]) - Parses the date_str expression with the fmt
expression to a date. Returns null with invalid input. By default, it follows
casting rules to a date if the fmt is omitted.

Argument type Return type
( String, String ) Date
( String or Date or Timestamp ) Date

More

TO_DATE on Apache Spark Documentation


TRUNC

Description

trunc(date, fmt) - Returns date with the time portion of the day truncated to
the unit specified by the format model fmt.

Argument type Return type
( Date, String ) Date

More

TRUNC on Apache Spark Documentation

TIMESTAMP functions

NOW

Description

now() - Returns the current timestamp at the start of query evaluation.

Argument type Return type
No arguments Timestamp

More

NOW on Apache Spark Documentation


CURRENT_TIMESTAMP

Description

current_timestamp() - Returns the current timestamp at the start of query
evaluation.

Argument type Return type
No arguments Timestamp

More

CURRENT_TIMESTAMP on Apache Spark Documentation


FROM_UNIXTIME

Description

from_unixtime(unix_time, format) - Returns unix_time in the specified format.

Argument type Return type
( Integer, String ) String
( Integer ) String

More

FROM_UNIXTIME on Apache Spark Documentation


FROM_UTC_TIMESTAMP

Description

from_utc_timestamp(timestamp, timezone) - Given a timestamp like '2017-07-14
02:40:00.0', interprets it as a time in UTC, and renders that time as a
timestamp in the given time zone. For example, 'GMT+1' would yield '2017-07-14
03:40:00.0'.

Argument type Return type
( Timestamp, String ) Timestamp

More

FROM_UTC_TIMESTAMP on Apache Spark Documentation


TO_TIMESTAMP

Description

to_timestamp(timestamp[, fmt]) - Parses the timestamp expression with the fmt
expression to a timestamp. Returns null with invalid input. By default, it
follows casting rules to a timestamp if the fmt is omitted.

Argument type Return type
( Integer or Float or String or Date or Timestamp ) Timestamp
( String, String ) Timestamp

More

TO_TIMESTAMP on Apache Spark Documentation


TO_UTC_TIMESTAMP

Description

to_utc_timestamp(timestamp, timezone) - Given a timestamp like '2017-07-14
02:40:00.0', interprets it as a time in the given time zone, and renders that
time as a timestamp in UTC. For example, 'GMT+1' would yield '2017-07-14
01:40:00.0'.

Argument type Return type
( Timestamp, String ) Timestamp

More

TO_UTC_TIMESTAMP on Apache Spark Documentation


TO_UNIX_TIMESTAMP

Description

to_unix_timestamp(expr[, pattern]) - Returns the UNIX timestamp of the given
time.

Argument type Return type
( String, String ) Integer
( String or Timestamp ) Integer

More

TO_UNIX_TIMESTAMP on Apache Spark Documentation


UNIX_TIMESTAMP

Description

unix_timestamp([expr[, pattern]]) - Returns the UNIX timestamp of current or
specified time.

Argument type Return type
No arguments Integer
( String, String ) Integer
( String or Timestamp ) Integer

More

UNIX_TIMESTAMP on Apache Spark Documentation


DATE_TRUNC

Description

date_trunc(fmt, ts) - Returns timestamp ts truncated to the unit specified by
the format model fmt. fmt should be one of ["YEAR", "YYYY", "YY", "MON",
"MONTH", "MM", "DAY", "DD", "HOUR", "MINUTE", "SECOND", "WEEK", "QUARTER"]

Argument type Return type
( String, Timestamp ) Timestamp

More

DATE_TRUNC on Apache Spark Documentation


WINDOW

Description

window(time, windowDuration[, slideDuration[, startTime]]) - Bucketize rows into
one or more time windows given a timestamp specifying column.

Argument type Return type
( Timestamp, String ) Struct
( Timestamp, String, String ) Struct
( Timestamp, String, String, String ) Struct

More

WINDOW on Apache Spark Documentation


DATE_FORMAT

Description

date_format(timestamp, fmt) - Converts timestamp to a value of string in the
format specified by the date format fmt.

Argument type Return type
( Date or Timestamp, String ) String

More

DATE_FORMAT on Apache Spark Documentation


MONTHS_BETWEEN

Description

months_between(timestamp1, timestamp2) - Returns number of months between
timestamp1 and timestamp2.

Argument type Return type
( Date or Timestamp, Date or Timestamp ) Float

More

MONTHS_BETWEEN on Apache Spark Documentation


DAY

Description

day(date) - Returns the day of month of the date/timestamp.

Argument type Return type
( Date or Timestamp ) Integer

More

DAY on Apache Spark Documentation


DAYOFMONTH

Description

dayofmonth(date) - Returns the day of month of the date/timestamp.

Argument type Return type
( Date or Timestamp ) Integer

More

DAYOFMONTH on Apache Spark Documentation


DAYOFYEAR

Description

dayofyear(date) - Returns the day of year of the date/timestamp.

Argument type Return type
( Date or Timestamp ) Integer

More

DAYOFYEAR on Apache Spark Documentation


DAYOFWEEK

Description

dayofweek(date) - Returns the day of the week for date/timestamp (1 = Sunday, 2
= Monday, ..., 7 = Saturday).

Argument type Return type
( Date or Timestamp ) Integer

More

DAYOFWEEK on Apache Spark Documentation


WEEKOFYEAR

Description

weekofyear(date) - Returns the week of the year of the given date. A week is
considered to start on a Monday and week 1 is the first week with >3 days.

Argument type Return type
( Date or Timestamp ) Integer

More

WEEKOFYEAR on Apache Spark Documentation


YEAR

Description

year(date) - Returns the year component of the date/timestamp.

Argument type Return type
( Date or Timestamp ) Integer

More

YEAR on Apache Spark Documentation


MONTH

Description

month(date) - Returns the month component of the date/timestamp.

Argument type Return type
( Date or Timestamp ) Integer

More

MONTH on Apache Spark Documentation


QUARTER

Description

quarter(date) - Returns the quarter of the year for date, in the range 1 to 4.

Argument type Return type
( Date or Timestamp ) Integer

More

QUARTER on Apache Spark Documentation


HOUR

Description

hour(timestamp) - Returns the hour component of the timestamp.

Argument type Return type
( Timestamp ) Integer

More

HOUR on Apache Spark Documentation


MINUTE

Description

minute(timestamp) - Returns the minute component of the timestamp.

Argument type Return type
( Timestamp ) Integer

More

MINUTE on Apache Spark Documentation


SECOND

Description

second(timestamp) - Returns the second component of the timestamp.

Argument type Return type
( Timestamp ) Integer

More

SECOND on Apache Spark Documentation


SQL Functions


Suggested Edits are limited on API Reference Pages

You can only suggest edits to Markdown body content, but not to the API spec.