Skip to content
Tutorials Center
My learning

Topics

Lesson 36 of 36 ยท Projects and Practice

Hive Practice Exercises and Interview Questions

Practice Hive-style queries (top-N, deduplication, anti-joins, running totals) in your browser, then review 30 concise answers to the Hive questions interviewers ask most often.

This final lesson has two parts. First, hands-on exercises on query patterns you write constantly in Hive: top-N per group, deduplication, anti-joins and running totals. Then a question bank with short answers on architecture, tables, joins, formats, ACID and tuning, the topics interviewers return to again and again.

The exercises run in SQLite on the company and shop sample databases. The SQL is written to be valid HiveQL as well, apart from the small differences noted. Try each exercise before opening the solution.

Exercises

1. Top earner per department

Show the best-paid employee of each department: department name, employee name and salary. Use a window function; this top-N-per-group pattern is the most common Hive interview query.

SQL Exercise 1 ยท database: company

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

Show solution
SQL
SELECT department, first_name, salary
FROM (
  SELECT d.name AS department, e.first_name, e.salary,
         ROW_NUMBER() OVER (PARTITION BY d.id ORDER BY e.salary DESC) AS rn
  FROM employees e
  JOIN departments d ON d.id = e.department_id
) ranked
WHERE rn = 1
ORDER BY department;

ROW_NUMBER numbers rows inside each department by salary. Use RANK or DENSE_RANK if ties should all be kept. HiveQL does not allow a window function in WHERE, hence the subquery.

2. Keep the latest record per key

A staging table holds several versions of each customer. Keep only the newest version per customer_id. This deduplication runs in nearly every Hive ingestion job.

SQL Exercise 2

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

Show solution
SQL
SELECT customer_id, city, updated_at
FROM (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) AS rn
  FROM staging
) t
WHERE rn = 1
ORDER BY customer_id;

Partition by the key, order by the version column descending, keep rank 1. In Hive, write the result with INSERT OVERWRITE so the job is repeatable.

3. Customers with no orders

List customers who never placed an order, three ways if you can: LEFT JOIN ... IS NULL, NOT EXISTS, and NOT IN. Which one is safest if the subquery can return NULL?

SQL Exercise 3 ยท database: shop

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

Show solution
SQL
SELECT c.id, c.first_name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

SELECT id, first_name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

NOT IN returns nothing at all if the subquery contains a single NULL, so NOT EXISTS or the left join is the safe choice.

4. Running total

For each order, show the month, the order's revenue and the running total of revenue for the month. Use a window SUM.

SQL Exercise 4 ยท database: shop

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

Show solution
SQL
WITH per_order AS (
  SELECT o.id AS order_id, substr(o.ordered_at, 1, 7) AS month,
         SUM(oi.quantity * oi.unit_price) AS revenue
  FROM orders o JOIN order_items oi ON oi.order_id = o.id
  GROUP BY o.id, substr(o.ordered_at, 1, 7)
)
SELECT month, order_id, ROUND(revenue, 2) AS revenue,
       ROUND(SUM(revenue) OVER (PARTITION BY month ORDER BY order_id), 2) AS running_total
FROM per_order
ORDER BY month, order_id;

In HiveQL use date_format(o.ordered_at, 'yyyy-MM') for the month. With ORDER BY inside OVER, the default frame is "from the start of the partition to the current row", which gives the running total.

Interview questions

Architecture and basics

  1. What is Hive? A data-warehouse system on Hadoop that lets you query large datasets in files with SQL (HiveQL), compiling queries into distributed jobs. It is for batch and interactive analytics (OLAP), not low-latency transactions (OLTP).
  2. What does the metastore store? Table names, columns and types, partitions, file formats, SerDe info and locations. Not the data. It sits in an RDBMS such as MySQL or PostgreSQL.
  3. Embedded, local and remote metastore? Embedded (Derby in the Hive process, one user, only for tests); local (HMS code in the Hive process, database separate); remote (a separate Thrift service on port 9083, shared by Hive, Spark, Trino). Production uses remote.
  4. Managed versus external table? Hive owns managed table data and deletes it on DROP; external tables only register a location. In Hive 3 and later, managed tables are transactional by default.
  5. Hive versus a traditional RDBMS? Hive reads files with schema-on-read, scales out, favors big scans and batch writes. An RDBMS enforces schema-on-write, is optimized for small transactions and indexes. Hive trades latency for scale.

Tables, partitions and buckets

  1. Partitioning versus bucketing? Partitioning splits data into directories by column values (pruning by directory). Bucketing hashes a column into a fixed number of files (helps joins, sampling and skew control).
  2. Static versus dynamic partitions? Static: you give the partition value in the statement. Dynamic: Hive derives partitions from the last SELECT columns; needs hive.exec.dynamic.partition.mode=nonstrict when all partition columns are dynamic.
  3. What is MSCK REPAIR TABLE? It registers partition directories that exist in storage but not in the metastore.
  4. How do you avoid too many partitions? Pick a low-cardinality key (date, region), use coarser buckets of time, and bucket for the high-cardinality column.
  5. ORDER BY, SORT BY, DISTRIBUTE BY, CLUSTER BY? Total order with one reducer; order within each reducer; which reducer gets each row; DISTRIBUTE BY x SORT BY x.

Joins and optimization

  1. Name the join strategies. Shuffle (reduce-side), map (broadcast), bucket map, sort-merge bucket, and skew joins.
  2. When does a map join happen? When one side fits under hive.auto.convert.join.noconditionaltask.size and the join type allows it, or with a MAPJOIN hint.
  3. What is LEFT SEMI JOIN? An EXISTS-style filter: left rows with a match, left columns only, no duplicates.
  4. What is the cost-based optimizer? Calcite-based planning that uses table and column statistics to reorder joins and choose strategies. Needs ANALYZE TABLE.
  5. How do you handle skew? Filter junk keys, hive.optimize.skewjoin, SKEWED BY, split the hot key, or salt it.

Formats and compression

  1. Why ORC? Columnar, compressed, with stripe statistics, row indexes and bloom filters; required for full ACID tables; vectorization and LLAP cache friendly.
  2. ORC versus Parquet? ORC: strongest in Hive and ACID. Parquet: broadest ecosystem support.
  3. Which formats are splittable? Uncompressed text, SequenceFile, ORC, Parquet, Avro. Gzip-compressed text is not; bzip2 text is.
  4. What is a SerDe? The class that converts bytes to columns and back, for example LazySimpleSerDe, OpenCSVSerde, JsonSerDe.
  5. How do you fix small files? Fewer partitions, hive.merge.* settings, CONCATENATE for ORC, compaction for ACID tables, batch inserts.

Functions and transactions

  1. UDF, UDAF, UDTF? One value per row; many rows to one value; one row to many rows.
  2. What is LATERAL VIEW? A join of each row with the rows a UDTF such as explode generates for it.
  3. How do ACID tables work on HDFS? Inserts create delta directories, deletes create delete_delta directories, readers merge base plus deltas, compaction folds them into a new base.
  4. Which settings enable transactions? hive.support.concurrency=true, hive.txn.manager=DbTxnManager, and the compactor initiator and worker threads.
  5. Limitations of ACID tables? One statement per transaction, ORC required for updates, partition and bucket columns cannot be updated, compaction overhead.

Engines and tuning

  1. Tez versus MapReduce? Tez runs a DAG with container reuse and no HDFS writes between stages. MapReduce launches a job per stage and is deprecated.
  2. What is LLAP? Long-lived daemons with a shared in-memory columnar cache for low-latency queries.
  3. What is vectorization? Processing batches of about 1,024 rows per operator call instead of one row at a time.
  4. How do you read an EXPLAIN plan? Check scans and their estimates, join types (broadcast versus shuffle edges), where filters sit, and whether statistics are COMPLETE.
  5. First three things when a query is slow? EXPLAIN it; check partitions, format and file counts; refresh statistics and check join strategy.

Final project ideas

Build your own portfolio: a clickstream warehouse (events, sessions, funnels), an IoT sensor table with hourly partitions and compaction, or a CDC pipeline that merges a customer change feed into a type 2 dimension. Write the DDL, load logic and five reports for each, explain your partition and bucket choices, and show an EXPLAIN before and after tuning. If you reached this page from the start, revisit the Apache Hive track page for the earlier chapters.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why is NOT IN risky when the subquery can return NULL?

    x NOT IN (..., NULL) is never true, so NOT EXISTS or a left join anti-pattern is safer.
  2. 2. Which pattern keeps the newest row per key?

    Number the rows per key from newest to oldest and keep number one.
  3. 3. Which SORT instruction gives a total ordering of the whole result?

    ORDER BY sorts globally through a single reducer; SORT BY only sorts within each reducer.
  4. 4. How is partitioning different from bucketing?

    Partitions prune by value; buckets spread rows evenly and help joins and sampling.

Summary

  • Window functions solve top-N per group, deduplication and running totals; know them well.
  • Prefer NOT EXISTS or a left-join anti join over NOT IN when NULL is possible.
  • Be ready to explain the metastore, partitions versus buckets, join strategies, ORC, ACID and Tez/LLAP.
  • Back every tuning claim with an EXPLAIN plan and measurements.

Next: Back to the Apache Hive track