Apache Impala Built-in Functions

It’s a comprehensive collection of Apache Impala’s built-in functions. Most of the functions in this list are described in full in this blog, so you can see how they benefit you in your day-to-day tasks. When you click on each function, it will take you to the appropriate post. If there isn’t a link, it either will be published soon or the function isn’t very important.

Note: The majority of these functions act similarly to those in Apache Hive. As a result, don’t bother about the titles of the various posts.

Impala Mathematical Functions

• ABS
• ACOS
• ASIN
• ATAN
• ATAN2
• BIN
• CEIL, CEILING, DCEIL
• CONV
• COS
• COSH
• COT
• DEGREES
• E
• EXP
• FACTORIAL
• FLOOR, DFLOOR
• FMOD
• FNV_HASH
• GREATEST
• HEX
• IS_INF
• IS_NAN
• LEAST
• LN
• LOG
• LOG10
• LOG2
• MAX_INT, MAX_TINYINT, MAX_SMALLINT, MAX_BIGINT
• MIN_INT, MIN_TINYINT, MIN_SMALLINT, MIN_BIGINT
• MOD
• MURMUR_HASH
• NEGATIVE
• PI
• PMOD
• POSITIVE
• POW, POWER, DPOW, FPOW
• PRECISION
• QUOTIENT
• RADIANS
• RAND, RANDOM
• ROUND, DROUND
• SCALE
• SIGN
• SIN
• SINH
• SQRT
• TAN
• TANH
• TRUNCATE, DTRUNC, TRUNC
• UNHEX
• WIDTH_BUCKET

Impala Type Conversion Functions

• CAST
• TYPEOF

Impala Date and Time Functions 

• ADD_MONTHS
• ADDDATE
• CURRENT_TIMESTAMP
• DATE_ADD
• DATE_PART
• DATE_SUB
• DATE_TRUNC
• DATEDIFF
• DAY
• DAYNAME
• DAYOFWEEK
• DAYOFYEAR
• DAYS_ADD
• DAYS_SUB
• EXTRACT
• FROM_TIMESTAMP
• FROM_UNIXTIME
• FROM_UTC_TIMESTAMP
• HOUR
• HOURS_ADD
• HOURS_SUB
• INT_MONTHS_BETWEEN
• MICROSECONDS_ADD
• MICROSECONDS_SUB
• MILLISECOND
• MILLISECONDS_ADD
• MILLISECONDS_SUB
• MINUTE
• MINUTES_ADD
• MINUTES_SUB
• MONTH
• MONTHNAME
• MONTHS_ADD
• MONTHS_BETWEEN
• MONTHS_SUB
• NANOSECONDS_ADD
• NANOSECONDS_SUB
• NEXT_DAY
• NOW
• QUARTER
• SECOND
• SECONDS_ADD
• SECONDS_SUB
• SUBDATE
• TIMEOFDAY
• TIMESTAMP_CMP
• TO_DATE
• TO_TIMESTAMP
• TO_UTC_TIMESTAMP
• TRUNC
• UNIX_TIMESTAMP
• UTC_TIMESTAMP
• WEEKOFYEAR
• WEEKS_ADD
• WEEKS_SUB
• YEAR
• YEARS_ADD
• YEARS_SUB

Impala Conditional Functions

• CASE
• CASE2
• COALESCE
• DECODE
• IF
• IFNULL
• ISFALSE
• ISNOTFALSE
• ISNOTTRUE
• ISNULL
• ISTRUE
• NONNULLVALUE
• NULLIF
• NULLIFZERO
• NULLVALUE
• NVL
• NVL2
• ZEROIFNULL

Impala String Functions

• ASCII
• BASE64DECODE
• BASE64ENCODE
• BTRIM
• CHAR_LENGTH
• CHR
• CONCAT
• CONCAT_WS
• FIND_IN_SET
• GROUP_CONCAT
• INITCAP
• INSTR
• LEFT
• LENGTH
• LEVENSHTEIN, LE_DST
• LOCATE
• LOWER, LCASE
• LPAD
• LTRIM
• PARSE_URL
• REGEXP_ESCAPE
• REGEXP_EXTRACT
• REGEXP_LIKE
• REGEXP_REPLACE
• REPEAT
• REPLACE
• REVERSE
• RIGHT
• RPAD
• RTRIM
• SPACE
• SPLIT_PART
• STRLEFT
• STRRIGHT
• SUBSTR, SUBSTRING
• TRANSLATE
• TRIM
• UPPER, UCASE

Impala Aggregate Functions

• APPX_MEDIAN
• AVG
• COUNT
• GROUP_CONCAT
• GROUP_CONCAT with GROUP Clause
• MAX
• MIN
• NDV
• STDDEV, STDDEV_SAMP, STDDEV_POP
• SUM
• VARIANCE, VARIANCE_SAMP, VARIANCE_POP, VAR_SAMP, VAR_POP

Impala Analytic Functions

• OVER Clause
• Window Clause
• AVG Function – Analytic Context
• COUNT Function – Analytic Context
• CUME_DIST Function (CDH 5.5 or higher only)
• DENSE_RANK Function
• FIRST_VALUE Function
• LAG Function
• LAST_VALUE Function
• LEAD Function
• MAX Function – Analytic Context
• MIN Function – Analytic Context
• NTILE Function (CDH 5.5 or higher only)
• PERCENT_RANK Function (CDH 5.5 or higher only)
• RANK Function
• ROW_NUMBER Function
• SUM Function – Analytic Context

Impala Bit Functions

• BITAND
• BITNOT
• BITOR
• BITXOR
• COUNTSET
• GETBIT
• ROTATELEFT
• ROTATERIGHT
• SETBIT
• SHIFTLEFT
• SHIFTRIGHT

Impala Miscellaneous Functions

• CURRENT_DATABASE
• EFFECTIVE_USER
• GET_JSON_OBJECT
• LOGGED_IN_USER
• PID
• SLEEP
• USER
• UUID
• VERSION