Apache Hive Functions

There are so many built-in functions available in Apache Hive that help to satisfy customer requirements or specific needs, data transformation, data analysis and data processing.

The list of functions below will be routed to the definition and example pages by clicking on it.

Collection Functions

• SIZE(Map<K.V>)
• SIZE(Array<T>)
• MAP_KEYS(Map<K.V>)
• MAP_VALUES(Map<K.V>)
• SORT_ARRAY(Array<T>)

Type Conversion Functions

• CAST(expr as <type>)

Date Functions

• FROM_UNIXTIME(bigint unixtime[, string format])
• UNIX_TIMESTAMP()
• TO_DATE(string timestamp)
• YEAR(string date)
• QUARTER(date/timestamp/string)
• MONTH(string date)
• DAY(string date)
• DAYOFMONTH(date)
• HOUR(string date)
• MINUTE(string date)
• SECOND(string date)
• WEEKOFYEAR(string date)
• EXTRACT()
• DATEDIFF(string enddate, string startdate)
• DATE_ADD(date/timestamp/string startdate, tinyint/smallint/int days)
• DATE_SUB(date/timestamp/string startdate, tinyint/smallint/int days)
• CURRENT_DATE
• CURRENT_TIMESTAMP
• ADD_MONTHS(string start_date, int num_months, output_date_format)
• LAST_DAY(string date)
• NEXT_DAY(string start_date, string day_of_week)
• TRUNC(string date, string format)
• MONTHS_BETWEEN(date1, date2)
• DATE_FORMAT(date/timestamp/string ts, string fmt)

Conditional Functions

• IF(boolean testCondition, T valueTrue, T valueFalseOrNull)
• ISNULL( a )
• NVL(T value, T default_value)
• COALESCE(T v1, T v2, …)
• CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END

String Functions

• CONCAT(string|binary A, string|binary B…)
• CONCAT_WS(string SEP, string A, string B…)
• ELT(N int,str1 string,str2 string,str3 string,…)
• FORMAT_NUMBER(number x, int d)
• GET_JSON_OBJECT(string json_string, string path)
• INSTR(string str, string substr)
• LENGTH(string A)
• LOCATE(string substr, string str[, int pos])
• LOWER(string A) lcase(string A)
• LPAD(string str, int len, string pad)
• LTRIM(string A)
• PARSE_URL(string urlString, string partToExtract [, string keyToExtract])
• PRINTF(String format, Obj… args)
• QUOTE(String text)
• REGEXP_EXTRACT(string subject, string pattern, int index)
• REPEAT(string str, int n)
• REPLACE(string A, string OLD, string NEW)
• REVERSE(string A)
• RPAD(string str, int len, string pad)
• RTRIM(string A)
• SPACE(int n)
• SPLIT(string str, string pat)
• STR_TO_MAP(text[, delimiter1, delimiter2])
• SUBSTR
• SUBSTRING_INDEX(string A, string delim, int count)
• TRANSLATE(string|char|varchar input, string|char|varchar from, string|char|varchar to)
• TRIM(string A)
• UPPER(string A) ucase(string A)
• INITCAP(string A)

Misc. Functions

• CURRENT_USER()
• LOGGED_IN_USER()
• CURRENT_DATABASE()
• VERSION()

Aggregate Functions

• COUNT(*), COUNT(expr), COUNT(DISTINCT expr[, expr…])
• SUM(col), SUM(DISTINCT col)
• AVG(col), AVG(DISTINCT col)
• MIN(col)
• MAX(col)
• VARIANCE(col)
• VAR_POP(col)
• VAR_SAMP(col)
• STDDEV_POP(col)
• STDDEV_SAMP(col)
• COVAR_POP(col1, col2)
• COVAR_SAMP(col1, col2)
• CORR(col1, col2)
• PERCENTILE
• PERCENTILE_APPROX
• COLLECT_SET(col)
• COLLECT_LIST(col)
• NTILE(INTEGER x)

Windowing Functions

• ROW_NUMBER
• RANK
• DENSE_RANK
• CUME_DIST
• PERCENT_RANK
• RANGE BETWEEN
• ROWS BETWEEN
• LAG
• LEAD

Built-in Table Generated Functions

• EXPLODE
• EXPLODE LATERAL VIEW
• INLINE
• INLINE LATERAL VIEW

a