Skip to content
Tutorials Center
My learning

Topics

Lesson 26 of 36 ยท UDFs and Transactions

Built-in Functions and UDF Basics

Tour Hive's built-in functions for strings, dates, conditionals, JSON and collections, including explode and LATERAL VIEW, and learn the three kinds of user-defined functions.

Hive ships with several hundred functions. Before writing your own, it pays to know what is already there, because a built-in function runs inside the engine, is vectorized where possible, and never needs a JAR on every node. When the built-ins are not enough, you add a user-defined function (UDF), which the next lesson builds.

Hive cannot run in the browser. Hive statements are shown read-only; the editors use SQLite functions that behave like their Hive counterparts, and say so when the names differ.

Discovering functions

SQL
SHOW FUNCTIONS;                    -- every function name
SHOW FUNCTIONS LIKE 'date*';       -- filter with a pattern
DESCRIBE FUNCTION concat_ws;       -- one-line description
DESCRIBE FUNCTION EXTENDED concat_ws;  -- with examples

Functions fall into three families based on how many rows go in and out:

Kind Rows in / out Examples
UDF (plain function) one row in, one value out upper, round, datediff
UDAF (aggregate) many rows in, one value out sum, avg, collect_list
UDTF (table-generating) one row in, many rows out explode, posexplode, json_tuple

Everyday scalar functions

Strings

SQL
SELECT upper(name), lower(name), length(name), trim(name),
       substr(name, 1, 3), concat(first, ' ', last),
       concat_ws('-', y, m, d),               -- separator first, skips NULLs
       split('a,b,c', ',')[1],                -- 'b' (arrays are 0-based)
       regexp_replace(phone, '[^0-9]', ''),
       regexp_extract(url, 'id=(\\d+)', 1),
       lpad(code, 6, '0'), instr(name, 'a'), repeat('ab', 3)
FROM customers;

Numbers and conditionals

SQL
SELECT round(price, 1), floor(price), ceil(price), abs(delta),
       pmod(-7, 3),                                 -- positive modulo: 2
       if(stock > 0, 'in stock', 'sold out'),
       nvl(discount, 0),                            -- same idea as coalesce
       coalesce(a, b, c),
       CASE WHEN price > 100 THEN 'high' ELSE 'low' END
FROM products;

Dates and timestamps

SQL
SELECT current_date, current_timestamp,
       date_add('2026-01-31', 7),                -- 2026-02-07
       date_sub('2026-01-31', 30),
       datediff('2026-03-01', '2026-02-01'),      -- 28 (first minus second)
       add_months('2026-01-31', 1),               -- 2026-02-28
       year(d), month(d), day(d), weekofyear(d),
       date_format(ts, 'yyyy-MM'),
       from_unixtime(1767225600),                 -- epoch seconds to string
       unix_timestamp('2026-01-01 00:00:00'),
       trunc(d, 'MM')                             -- first day of month
FROM events;

Many of these have close SQLite equivalents, so you can practice the logic here:

SQL String, number and date functions (SQLite stand-in) ยท database: shop

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

Hive differences to remember: datediff(end, start) returns days directly; substr is 1-based; if(cond, a, b) exists in Hive but not in SQLite (use CASE); date strings must be in yyyy-MM-dd form.

Collections and complex types

Hive has ARRAY, MAP, STRUCT and UNIONTYPE columns, with functions to build and read them:

SQL
SELECT array(1, 2, 3), map('a', 1, 'b', 2), named_struct('x', 1, 'y', 2),
       size(tags), array_contains(tags, 'sale'),
       sort_array(tags), tags[0], attrs['color'], address.city,
       map_keys(attrs), map_values(attrs)
FROM products;

Aggregating into collections is a classic Hive idiom:

SQL
SELECT customer_id,
       collect_list(product_id)  AS all_products,    -- keeps duplicates
       collect_set(product_id)   AS distinct_products
FROM order_items GROUP BY customer_id;

UDTFs: explode and LATERAL VIEW

A UDTF produces several rows from one. explode turns an array into one row per element, or a map into key/value rows:

SQL
SELECT explode(array('red', 'green', 'blue'));
-- red / green / blue (three rows)

You cannot select other columns next to a UDTF directly. Use LATERAL VIEW, which joins each source row with the rows the function generates for it:

SQL
SELECT p.name, t.tag
FROM products p
LATERAL VIEW explode(p.tags) t AS tag;

-- keep rows whose array is empty or NULL
SELECT p.name, t.tag
FROM products p
LATERAL VIEW OUTER explode(p.tags) t AS tag;

-- element position as well
SELECT p.name, t.pos, t.tag
FROM products p
LATERAL VIEW posexplode(p.tags) t AS pos, tag;

JSON strings are handled by get_json_object(col, '$.user.id') for one value, or the UDTF json_tuple(col, 'a', 'b') for several keys in one pass.

SQLite has no arrays, but it has the table-valued json_each, which does the same job as explode:

SQL Explode in SQLite with json_each

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

The second query keeps Rope with a NULL tag, exactly what LATERAL VIEW OUTER does in Hive.

Here is the same operation as a plain JavaScript model, which helps to see that LATERAL VIEW is a per-row join:

JS LATERAL VIEW as a per-row expansion

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

No output.

Window and analytic functions

Hive also supports ROW_NUMBER(), RANK(), LAG(), LEAD(), SUM() OVER (PARTITION BY ... ORDER BY ...) and other window functions, with the same meaning as in standard SQL. They are covered in the SQL track's window-function lessons.

Three ways to extend Hive

When no built-in does what you need, you have these options, from simplest to heaviest:

  1. Rewrite with built-ins. Often CASE, regexp_extract and split are enough.
  2. Use a script. TRANSFORM pipes rows through any program (Python, shell) over standard input and output. Convenient, but slow and not vectorized.
  3. Write a Java UDF, UDAF or UDTF, package it as a JAR and register it. Fastest and the most reusable, and the topic of the next lesson.
SQL
ADD FILE clean.py;
SELECT TRANSFORM (id, comment)
       USING 'python3 clean.py'
       AS (id STRING, cleaned STRING)
FROM reviews;

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which function type produces several output rows from one input row?

    A UDTF (table-generating function) such as explode returns multiple rows per input row.
  2. 2. Why is LATERAL VIEW needed with explode?

    LATERAL VIEW pairs each source row with the rows its UDTF generates, so other columns remain available.
  3. 3. What does LATERAL VIEW OUTER do differently?

    Without OUTER, rows that produce no generated rows disappear.
  4. 4. What is the difference between collect_list and collect_set?

    Both aggregate values into an array; collect_set de-duplicates.

Summary

  • Use SHOW FUNCTIONS and DESCRIBE FUNCTION EXTENDED to find built-ins before writing your own.
  • UDFs map row to value, UDAFs rows to one value, UDTFs one row to many rows.
  • LATERAL VIEW [OUTER] explode(...) is the standard way to unpack arrays and maps.
  • collect_list/collect_set build arrays from groups; get_json_object and json_tuple read JSON.
  • Extend Hive with built-in rewrites, TRANSFORM scripts or Java UDFs.

Next: Writing Java UDFs, UDAFs and UDTFs