Skip to content
Tutorials Center
My learning

Topics

Lesson 13 of 36 ยท HiveQL Basics

Built-in Functions in Hive

A tour of Hive's built-in functions: string, math, date and time, conditional, collection functions, explode with LATERAL VIEW, and how to discover functions with SHOW FUNCTIONS.

Hive ships with well over two hundred built-in functions. You will use a dozen of them every day: text cleaning, date arithmetic, null handling and splitting delimited strings. This lesson groups them by purpose, shows Hive's exact names and marks where SQLite has a different spelling so the runnable stand-ins stay honest.

Function names below are Hive's. The runnable editors use SQLite and its equivalents (for example substr, length, date), labelled as stand-ins. Hive-only functions (split, explode, datediff, date_add) are shown read-only.

Discovering functions

SQL
SHOW FUNCTIONS;                 -- all functions
SHOW FUNCTIONS LIKE 'date*';
DESCRIBE FUNCTION datediff;
DESCRIBE FUNCTION EXTENDED datediff;   -- with examples

DESCRIBE FUNCTION EXTENDED is the fastest reference there is; it shows the signature and an example.

String functions

Function Result
length('hive') 4
upper('hive'), lower('HIVE') 'HIVE', 'hive'
trim(' x '), ltrim, rtrim removes spaces
concat('a', 'b'), concat_ws('-', 'a', 'b') 'ab', 'a-b'
substr('hello', 2, 3) 'ell' (1-based)
instr('hello', 'l') 3
replace('a-b', '-', '/') 'a/b'
regexp_replace('a1b22', '[0-9]+', '#') 'a#b#'
regexp_extract('id=42', 'id=([0-9]+)', 1) '42'
split('a,b,c', ',') ["a","b","c"] (an array)
lpad('7', 3, '0') '007'
initcap('hello world') 'Hello World'

split takes a regular expression, so to split on a dot or pipe escape it: split(s, '\\.'), split(s, '\\|').

Try the portable ones with the SQLite stand-in. SQLite's substr and length agree with Hive; upper, lower, trim and replace also match:

SQL Stand-in: string functions (SQLite, not Hive) ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

(Hive would write the same query; only substr's behaviour on out-of-range positions may differ between engines.)

Numeric functions

round(x, d), floor(x), ceil(x), abs(x), pow(x, y), sqrt(x), ln(x), exp(x), rand() and pmod(a, b) (a positive modulo), a % b. Integer division with / gives a decimal in Hive (7 / 2 is 3.5); use div for integer division: 7 DIV 2 is 3. That differs from SQLite, where 7 / 2 is 3. Check this before porting queries.

SQL Stand-in: rounding functions (SQLite, not Hive)

Press Run (or Ctrl + Enter) to see the result here.

Date and time functions

SQL
SELECT current_date,                                   -- 2025-03-01
       current_timestamp,
       to_date('2025-03-01 14:30:00'),                 -- 2025-03-01
       year('2025-03-01'), month('2025-03-01'), day('2025-03-01'),
       date_add('2025-03-01', 10),                     -- 2025-03-11
       date_sub('2025-03-01', 1),                      -- 2025-02-28
       datediff('2025-03-11', '2025-03-01'),           -- 10
       add_months('2025-01-31', 1),                    -- 2025-02-28
       last_day('2025-02-10'),                         -- 2025-02-28
       date_format('2025-03-01', 'yyyy/MM/dd'),
       trunc('2025-03-17', 'MM'),                      -- 2025-03-01
       unix_timestamp('2025-03-01 00:00:00'),
       from_unixtime(1740787200);

The first argument of datediff is the later date. Date format patterns are Java style (yyyy-MM-dd HH:mm:ss), not the %Y-%m-%d style of MySQL. Date strings in the format yyyy-MM-dd are converted automatically by these functions; anything else must be parsed with unix_timestamp(str, pattern) first.

SQLite has date(), julianday() and strftime() instead. The same difference in days:

SQL Stand-in: days between two dates (SQLite, not Hive)

Press Run (or Ctrl + Enter) to see the result here.

Conditional and NULL functions

SQL
if(price > 10, 'high', 'low')
coalesce(phone, email, 'unknown')       -- first non-null
nvl(discount, 0)                        -- two-argument coalesce
nullif(a, b)                            -- NULL when a = b
isnull(x), isnotnull(x)
CASE WHEN ... THEN ... END

Collection functions

For arrays and maps: size(arr), array_contains(arr, v), sort_array(arr), array(1,2,3), map('a',1), map_keys(m), map_values(m), named_struct('x',1,'y',2), collect_list(col) and collect_set(col) (aggregates that build arrays; collect_set removes duplicates).

explode and LATERAL VIEW

explode turns an array into rows. It is a table-generating function (UDTF), and you cannot select other columns next to it directly. Use LATERAL VIEW:

SQL
-- users(id, name, phones ARRAY<STRING>)
SELECT u.id, u.name, p.phone
FROM users u
LATERAL VIEW explode(u.phones) p AS phone;

-- with positions
LATERAL VIEW posexplode(u.phones) p AS pos, phone

-- keep users with no phones: OUTER
LATERAL VIEW OUTER explode(u.phones) p AS phone

A classic use is word counting: SELECT w, COUNT(*) FROM docs LATERAL VIEW explode(split(line, ' ')) t AS w GROUP BY w. Stand-in logic can be demonstrated by splitting rows with a recursive query, but the idea is simple: one input row with an array becomes one output row per element, and the other columns are repeated.

JSON and other helpers

get_json_object(json, '$.user.id') extracts one field; json_tuple(json, 'a', 'b') extracts several (use with LATERAL VIEW). Also useful: md5(s), sha2(s, 256), hash(a, b), uuid(), reflect(), java_method(), and type checks typeof(x).

Aggregate and window functions

COUNT, SUM, AVG, MIN, MAX, collect_list, percentile, percentile_approx, variance, stddev, plus window functions (ROW_NUMBER(), RANK(), LAG, LEAD, ...). Aggregates are the next lesson.

Common mistakes

  • Forgetting that split uses a regex.
  • Assuming / is integer division (it is not in Hive).
  • Passing dates in dd/MM/yyyy to date functions and getting NULL.
  • Using explode next to other columns without LATERAL VIEW.
  • Calling rand() in a filter and expecting repeatable results.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does datediff('2025-03-11', '2025-03-01') return?

    datediff(end, start) returns end minus start in days.
  2. 2. Why must you write split(s, '\.') to split on a dot?

    The delimiter is a regex pattern, so a literal dot has to be escaped.
  3. 3. What is LATERAL VIEW used for?

    It applies a UDTF to each row and joins the generated rows back to that row.
  4. 4. What does 7 DIV 2 return in Hive?

    DIV is integer division, whereas 7 / 2 is 3.5.

Summary

  • Discover functions with SHOW FUNCTIONS and DESCRIBE FUNCTION EXTENDED.
  • String, math, date, conditional and collection functions cover most daily needs; split and regexp_* use regular expressions.
  • Dates use yyyy-MM-dd and Java-style format patterns; datediff(later, earlier).
  • explode with LATERAL VIEW converts arrays to rows; collect_list and collect_set do the opposite.

Next: GROUP BY and Aggregation