Skip to content
Tutorials Center
My learning

Topics

Lesson 12 of 36 ยท HiveQL Basics

SELECT, Filter and Sort in Hive

Master HiveQL queries: SELECT lists, WHERE filters, DISTINCT, LIKE and RLIKE, BETWEEN, IN, CASE, ORDER BY versus SORT BY, DISTRIBUTE BY, CLUSTER BY and LIMIT.

Most of HiveQL's SELECT is the SQL you already know. The differences are in the details that come from running on a cluster: sorting has four flavours, the regex operators are built in, and some habits that are harmless in a small database (such as a global ORDER BY) are costly at scale. This lesson covers the query basics and those differences.

The queries use portable SQL, so the runnable editors run them on the bookstore database in SQLite as stand-ins; HiveQL-only clauses (SORT BY, DISTRIBUTE BY, CLUSTER BY, RLIKE) are read-only.

The shape of a query

SQL
SELECT [DISTINCT] expressions
FROM table
[WHERE row_filter]
[GROUP BY columns [HAVING group_filter]]
[ORDER BY columns | SORT BY columns | CLUSTER BY columns]
[LIMIT n];

Logical order of evaluation is FROM, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, then sorting and LIMIT. That is why you cannot use a column alias from SELECT inside WHERE.

Choosing columns

SQL
SELECT * FROM books;                          -- every column
SELECT title, price FROM books;               -- two columns
SELECT title, price * 1.2 AS price_with_vat   -- computed column with alias
FROM books;
SELECT DISTINCT genre FROM books;             -- unique values

On columnar formats such as ORC and Parquet, SELECT * reads every column and wastes I/O; always list only what you need.

Hive can select columns by regex, a handy trick on wide tables:

SQL
SET hive.support.quoted.identifiers=none;
SELECT `^price.*` FROM books;        -- every column whose name starts with price

Filtering with WHERE

SQL Stand-in: WHERE with AND, OR, IN, BETWEEN (SQLite, not Hive) ยท database: bookstore

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

Operators you can rely on: =, <> (also !=), <, <=, >, >=, AND, OR, NOT, IN (...), BETWEEN a AND b (inclusive), IS NULL, IS NOT NULL. Remember that comparisons with NULL are never true. Use <=> for a NULL-safe equality (NULL <=> NULL is true).

Pattern matching

  • LIKE: % matches any characters, _ exactly one.
  • RLIKE / REGEXP: Java regular expressions, available in Hive.
SQL
SELECT url FROM page_views WHERE url LIKE '%/blog/%';
SELECT url FROM page_views WHERE url RLIKE '^https://(www\\.)?example\\.com/(blog|news)/';

Note the doubled backslash: in a Hive string literal \\. is the regex \.. Test patterns on a small table first.

The portable LIKE can be practised in the stand-in:

SQL Stand-in: LIKE patterns (SQLite, not Hive) ยท database: bookstore

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

Conditional values with CASE

SQL
SELECT title, price,
       CASE
         WHEN price < 10 THEN 'budget'
         WHEN price < 20 THEN 'standard'
         ELSE 'premium'
       END AS tier
FROM books;

The first matching WHEN wins; without ELSE, no match gives NULL. Hive also has if(cond, a, b), coalesce(a, b, ...), nvl(a, b) and nullif(a, b).

Sorting: four clauses

Sorting is where Hive differs most, because sorting globally across many machines is expensive.

Clause What it does Cost
ORDER BY Total order of the whole result; uses a single reducer Slow on large results
SORT BY Sorts within each reducer; output is several sorted files, not globally sorted Parallel, fast
DISTRIBUTE BY Decides which reducer each row goes to (by hash of the columns) Controls the shuffle
CLUSTER BY Shortcut for DISTRIBUTE BY x SORT BY x Same key sorted within reducers
SQL
-- global sort of a big table: one reducer does it all
SELECT * FROM events ORDER BY event_time;

-- parallel: all rows for one user go to the same reducer, sorted by time there
SELECT * FROM events
DISTRIBUTE BY user_id
SORT BY user_id, event_time;

SELECT * FROM events CLUSTER BY user_id;

Use ORDER BY only on small results or together with LIMIT (Hive then runs a top-N algorithm, which keeps only n rows per task). Use DISTRIBUTE BY + SORT BY when you need per-key ordering for a downstream process, for example producing sorted files per user. In strict mode (hive.mapred.mode=strict), ORDER BY without LIMIT is rejected on large tables for this reason.

Sorting details: ASC (default) and DESC; NULLS FIRST / NULLS LAST control NULL placement; Hive puts NULLs first for ascending order by default.

SQL Stand-in: ORDER BY with ties and LIMIT (SQLite, not Hive) ยท database: bookstore

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

LIMIT and sampling

LIMIT n returns n rows; LIMIT offset, n skips offset rows in recent versions (OFFSET is also supported). Without ORDER BY, which rows you get is not defined. For quick peeks at huge tables prefer LIMIT over COUNT(*): Hive can often answer a LIMIT query by reading a few files. To take a random sample of rows, see Buckets and Sampling.

Subqueries and aliases

Hive supports subqueries in FROM, and in WHERE with IN, EXISTS and scalar comparisons. Every subquery in FROM needs an alias:

SQL
SELECT t.genre, t.titles
FROM (SELECT genre, COUNT(*) AS titles FROM books GROUP BY genre) t
WHERE t.titles > 1;

Common mistakes

  • Using a SELECT alias in WHERE (not allowed; repeat the expression or use a subquery).
  • Expecting SORT BY to produce one globally sorted file.
  • Using ORDER BY without LIMIT on huge tables.
  • Forgetting that LIKE is case-sensitive in Hive: use lower(col) LIKE ....
  • Filtering on a partition column last: always filter partition columns in WHERE to prune folders.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the difference between ORDER BY and SORT BY?

    A total order needs one reducer; SORT BY keeps parallelism but gives several sorted outputs.
  2. 2. CLUSTER BY x is shorthand for what?

    CLUSTER BY sends equal keys to the same reducer and sorts them there.
  3. 3. Why does WHERE alias = 5 fail when alias is defined in SELECT?

    The logical order is FROM, WHERE, GROUP BY, HAVING, then SELECT.
  4. 4. Which operator gives regular-expression matching in HiveQL?

    RLIKE (alias REGEXP) uses Java regular expressions; LIKE only supports % and _.

Summary

  • SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT works as in SQL; list only the columns you need.
  • LIKE for simple patterns, RLIKE for regular expressions; use IS NULL and <=> for NULL logic.
  • ORDER BY is a global sort on one reducer; SORT BY, DISTRIBUTE BY and CLUSTER BY give parallel per-reducer ordering.
  • Pair ORDER BY with LIMIT on big tables and always filter partition columns.

Next: Built-in Functions