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 functions supported by Impala are:
The following is a 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
|
|
FROM_UNIXTIME
|
|
FROM_TIMESTAMP
|
|
FROM_UTC_TIMESTAMP
|
|
GET_JSON_OBJECT
|
|
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
|
|
PRETTYPRINT_BYTES
|
|
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
|