Impala supports several categories of built-in functions. These functions let you
perform mathematical calculations, string manipulation, date calculations, and other kinds of
data transformations directly in SQL
statements.
The categories of built-in functions supported by Impala are:
The following is a complete list of built-in functions supported in Impala:
ABS
|
ACOS
|
ADD_MONTHS
|
ADDDATE
|
APPX_MEDIAN
|
ASCII
|
ASIN
|
ATAN
|
ATAN2
|
AVG
|
AVG - Analytic Function
|
BASE64DECODE
|
BASE64ENCODE
|
BITAND
|
BIN
|
BITNOT
|
BITOR
|
BITXOR
|
BTRIM
|
CASE
|
CASE WHEN
|
CAST
|
CEIL, CEILING, DCEIL
|
CHAR_LENGTH
|
CHR
|
COALESCE
|
CONCAT
|
CONCAT_WS
|
CONV
|
COS
|
COSH
|
COT
|
COUNT
|
COUNT - Analytic Function
|
COUNTSET
|
CUME_DIST
|
CURRENT_DATABASE
|
CURRENT_TIMESTAMP
|
DATE_ADD
|
DATE_PART
|
DATE_SUB
|
DATE_TRUNC
|
DATEDIFF
|
DAY
|
DAYNAME
|
DAYOFWEEK
|
DAYOFYEAR
|
DAYS_ADD
|
DAYS_SUB
|
DECODE
|
DEGREES
|
DENSE_RANK
|
E
|
EFFECTIVE_USER
|
EXP
|
EXTRACT
|
FACTORIAL
|
FIND_IN_SET
|
FIRST_VALUE
|
FLOOR, DFLOOR
|
FMOD
|
FNV_HASH
|
GET_JSON_OBJECT
|
FROM_UNIXTIME
|
FROM_TIMESTAMP
|
FROM_UTC_TIMESTAMP
|
GETBIT
|
GREATEST
|
GROUP_CONCAT
|
GROUP_CONCAT - Analytic Function
|
HEX
|
HOUR
|
HOURS_ADD
|
HOURS_SUB
|
IF
|
IFNULL
|
INITCAP
|
INSTR
|
INT_MONTHS_BETWEEN
|
IS_INF
|
IS_NAN
|
ISFALSE
|
ISNOTFALSE
|
ISNOTTRUE
|
ISNULL
|
ISTRUE
|
JARO_DISTANCE, JARO_DIST
|
JARO_SIMILARITY, JARO_SIM
|
JARO_WINKER_DISTANCE, JW_DST
|
JARO_WINKER_SIMILARITY, JW_SIM
|
LAG
|
LAST_VALUE
|
LEAD
|
LEAST
|
LEFT
|
LENGTH
|
LN
|
LOCATE
|
LOG
|
LOG10
|
LOG2
|
LOWER, LCASE
|
LPAD
|
LTRIM
|
MAX
|
MAX - Analytic Function
|
MAX_INT, MAX_TINYINT, MAX_SMALLINT, MAX_BIGINT
|
MICROSECONDS_ADD
|
MICROSECONDS_SUB
|
MILLISECOND
|
MILLISECONDS_ADD
|
MILLISECONDS_SUB
|
MIN
|
MIN - Analytic Function
|
MIN_INT, MIN_TINYINT, MIN_SMALLINT, MIN_BIGINT
|
MINUTE
|
MINUTES_ADD
|
MINUTES_SUB
|
MOD
|
MONTH
|
MONTHNAME
|
MONTHS_ADD
|
MONTHS_BETWEEN
|
MONTHS_SUB
|
MURMUR_HASH
|
NANOSECONDS_ADD
|
NANOSECONDS_SUB
|
NDV
|
NEGATIVE
|
NEXT_DAY
|
NONNULLVALUE
|
NOW
|
NTILE
|
NULLIF
|
NULLIFZERO
|
NULLVALUE
|
NVL
|
NVL2
|
OVER
Clause
|
PARSE_URL
|
PERCENT_RANK
|
PI
|
PID
|
PMOD
|
POSITIVE
|
POW, POWER, DPOW, FPOW
|
PRECISION
|
QUARTER
|
QUOTIENT
|
RADIANS
|
RAND, RANDOM
|
RANK
|
REGEXP_ESCAPE
|
REGEXP_EXTRACT
|
REGEXP_LIKE
|
REGEXP_REPLACE
|
REPEAT
|
REPLACE
|
REVERSE
|
RIGHT
|
ROTATELEFT
|
ROTATERIGHT
|
ROUND, DROUND
|
ROW_NUMBER
|
RPAD
|
RTRIM
|
SCALE
|
SECOND
|
SECONDS_ADD
|
SECONDS_SUB
|
SETBIT
|
SHIFTLEFT
|
SHIFTRIGHT
|
SIGN
|
SIN
|
SINH
|
SLEEP
|
SPACE
|
SPLIT_PART
|
SQRT
|
STDDEV,
STDDEV_SAMP, STDDEV_POP
|
STRLEFT
|
STRRIGHT
|
SUBDATE
|
SUBSTR, SUBSTRING
|
SUM
|
SUM - Analytic Function
|
TAN
|
TANH
|
TIMEOFDAY
|
TIMESTAMP_CMP
|
TO_DATE
|
TO_TIMESTAMP
|
TO_UTC_TIMESTAMP
|
TRANSLATE
|
TRIM
|
TRUNC
|
TRUNCATE, DTRUNC, TRUNC
|
TYPEOF
|
UNHEX
|
UNIX_TIMESTAMP
|
UPPER, UCASE
|
USER
|
UTC_TIMESTAMP
|
UUID
|
VARIANCE,
VARIANCE_SAMP, VARIANCE_POP, VAR_SAMP, VAR_POP
|
VERSION
|
WEEKOFYEAR
|
WEEKS_ADD
|
WEEKS_SUB
|
WIDTH_BUCKET
|
YEAR
|
YEARS_ADD
|
YEARS_SUB
|
ZEROIFNULL
|