Top Hive Interview Questions and Answers

Hive interviews test whether you can design tables that scale, write SQL that runs well on large data, and know what the current Hive can do. This guide gives you 45 Hive interview questions and answers, from basics to scenario questions, for freshers and experienced candidates.

Every answer starts with a short, direct response you can say aloud, followed by a table or a query. The window function and top N examples were run, so the outputs are real. The page reflects Hive 4: ACID tables, MERGE, Iceberg, Tez and Beeline, and marks Hive 3 as end of life.

The guide has six parts: 8 on Hive basics, 8 on tables, partitions and file formats, 8 on HiveQL queries, 8 on performance tuning, 7 on ACID tables and modern Hive and 6 scenario questions. For the wider platform, read the main Hadoop interview page.

Key points

• 45 questions in six parts, from basics to scenarios.
• Current for Hive 4: ACID tables, MERGE, Iceberg and Tez.
• Partitioning, bucketing and file format tables.
• Executed window function and top N examples.
• Performance tuning and scenario answers.

In this guide

Hive Basics (Freshers)

Start with what Hive is, what it is not, and how a query turns into work on the cluster. Interviewers use these questions to see whether you know where Hive fits.

Q1. What is Apache Hive, and what is it used for?

Apache Hive is a data warehouse system built on Hadoop that lets you query large datasets with a SQL like language called HiveQL. It reads data stored in HDFS or object storage, and runs the queries as distributed jobs on an engine such as Tez. It is used for batch analytics, reporting and ETL on very large data.

Q2. What are the main components of Hive?

The main components are HiveServer2, which accepts client connections and compiles queries, the metastore, which stores table definitions and partition information in a relational database, the driver and compiler, which plan queries, and the execution engine, which runs them. Clients connect with Beeline, JDBC, ODBC or BI tools.

Q3. How is Hive different from a traditional relational database?

Hive is built for large scale analysis with high latency, scans huge files and applies a schema when data is read. A relational database is built for fast transactions and small lookups and checks the schema when data is written. Hive supports ACID tables now, but it is still not a replacement for an operational database.

Q4. What is the Hive metastore, and why is it important?

The metastore is a service that keeps metadata about tables, columns, types, partitions and where the data lives, in a relational database such as MySQL or PostgreSQL. The data itself stays in storage. Many other engines, such as Spark and Trino, use the Hive metastore as their catalog, so it often outlives Hive itself.

What the interviewer is listening for: You say that metadata and data are separate, and that other engines reuse the metastore.

Q5. Which execution engines does Hive support?

Hive today runs on Apache Tez, which is the standard engine and is much faster than MapReduce. MapReduce was the original engine and is deprecated. Hive on Spark was an option in some releases, but Tez is the one to name. Tez builds a directed acyclic graph of tasks, which avoids writing temporary results to disk between stages.

Q6. Which Hive version is current?

The current line is Hive 4.2, with 4.2.1 as the latest release I verified, and the 4.2.x releases need JDK 21 at minimum. Hive 3 reached end of life in October 2024. Check the official Apache Hive download page for the latest version before you answer, because release information changes.

What the interviewer is listening for: You mention that Hive 3 has reached end of life, and you do not describe Hive 2 or 3 as current.

Q7. What is Beeline, and how does it differ from the Hive CLI?

Beeline is a JDBC client that connects to HiveServer2, supports authentication and works over a network. The old Hive CLI ran queries directly against the metastore and bypassed HiveServer2 security, so it is deprecated. Use Beeline for scripts, testing and administration.

beeline -u "jdbc:hive2://hs2-host:10000/default" -n asha -e "SHOW TABLES;"

Q8. What are the data types in Hive?

Hive has primitive types such as TINYINT, INT, BIGINT, FLOAT, DOUBLE, DECIMAL, STRING, VARCHAR, BOOLEAN, DATE and TIMESTAMP. It also has complex types: ARRAY, MAP, STRUCT and UNIONTYPE, which hold nested data such as JSON records. Use DECIMAL, not DOUBLE, for money.

Tables, Partitions and File Formats (Freshers and Intermediate)

Table design is the heart of Hive performance. Expect questions on external tables, partitions, buckets and formats.

Q9. What is the difference between managed and external tables?

For a managed table, Hive controls both the metadata and the data, so dropping the table deletes the data. For an external table, Hive controls only the metadata, and dropping it leaves the data in place. Use external tables for data shared with other tools or that must not be deleted by a mistake.

CREATE EXTERNAL TABLE sales_raw (
  order_id BIGINT,
  amount   DECIMAL(10,2),
  country  STRING
)
STORED AS PARQUET
LOCATION '/data/raw/sales';

In Hive 3 and later, managed tables are ACID tables by default, which makes the difference even more important.

Q10. What is partitioning in Hive, and why use it?

Partitioning splits a table into separate directories by the values of one or more columns, such as date or country. A query that filters on the partition column reads only the matching directories, which is called partition pruning. It can cut the data scanned by orders of magnitude. Choose columns with a modest number of distinct values.

CREATE TABLE sales (
  order_id BIGINT,
  amount   DECIMAL(10,2)
)
PARTITIONED BY (sale_date DATE, country STRING)
STORED AS ORC;

SELECT SUM(amount) FROM sales
WHERE sale_date = DATE '2026-03-01' AND country = 'IN';

Q11. What is the problem with too many partitions?

Each partition is a directory and a row in the metastore, so a huge number of partitions slows planning, strains the metastore and creates many small files. A table partitioned by user ID, which has millions of values, is a typical mistake. Partition by date or a low cardinality column, and use buckets for the rest.

days, countries = 365 * 3, 40

print("date x country partitions in 3 years:", days * countries)
print("date only:", days)

date x country partitions in 3 years: 43800
date only: 1095

Q12. What are static and dynamic partitions?

In static partitioning, you state the partition value in the insert statement. In dynamic partitioning, Hive reads the partition values from the last columns of the query result and creates partitions automatically. Dynamic partitioning needs hive.exec.dynamic.partition turned on, and a mode setting for strict or nonstrict behaviour.

SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;

INSERT OVERWRITE TABLE sales PARTITION (sale_date, country)
SELECT order_id, amount, sale_date, country FROM sales_raw;

Q13. What is bucketing, and how does it differ from partitioning?

Bucketing splits the data of a table or partition into a fixed number of files by the hash of a column. It helps joins and sampling, since matching keys are in matching buckets. Partitioning makes directories by value and the number grows with the data, while bucketing has a fixed count that you choose.

CREATE TABLE orders_b (order_id BIGINT, user_id BIGINT, amount DECIMAL(10,2))
CLUSTERED BY (user_id) INTO 32 BUCKETS
STORED AS ORC;

Q14. Which file formats does Hive support, and which should you choose?

Hive reads text, SequenceFile, Avro, Parquet and ORC, among others. ORC and Parquet are columnar and compressed, so queries read only the needed columns and the files are much smaller. ORC is the native Hive format and supports ACID, and Parquet is the best choice when other engines also read the data.

Q15. What is a SerDe?

A SerDe, short for serialiser and deserialiser, tells Hive how to read a row from a file into columns and how to write it back. Examples are the default text SerDe, a CSV SerDe, a JSON SerDe, and the ORC and Parquet SerDes. You choose one with the ROW FORMAT or STORED AS clauses of CREATE TABLE.

Q16. What does schema on read mean?

Schema on read means Hive checks the structure of the data when you query it, not when it is loaded. You can place files in HDFS first and define a table over them afterwards. If a file does not match the schema, you get NULL values or errors at query time. This makes loading fast and flexible, but needs data quality checks.

HiveQL Queries (Intermediate)

The SQL examples in this section were run in SQLite, which supports the same window function syntax as Hive, so the results shown are real and the queries work in HiveQL as written.

Q17. What is the difference between ORDER BY, SORT BY, DISTRIBUTE BY and CLUSTER BY?

ORDER BY gives a total order using a single reducer, so it is slow on big data. SORT BY sorts the rows inside each reducer, so the output is sorted only within each part. DISTRIBUTE BY decides which reducer gets each row. CLUSTER BY is a shortcut for DISTRIBUTE BY and SORT BY on the same column.

Q18. What are the types of joins in Hive, and how do you optimise them?

Hive supports inner, left, right, full outer, left semi and cross joins. A common join shuffles both tables by the join key. A map join loads the small table into memory and joins in the map stage with no shuffle, which is much faster. A bucket map join and a skew join handle sorted or skewed data.

SET hive.auto.convert.join=true;

SELECT /*+ MAPJOIN(c) */ o.order_id, c.name
FROM orders o JOIN customers c ON o.customer_id = c.id;

Q19. How do window functions work in Hive?

A window function calculates a value over a set of rows related to the current row, without collapsing them like GROUP BY. You use OVER with PARTITION BY and ORDER BY. ROW_NUMBER gives a unique rank, RANK leaves gaps after ties, DENSE_RANK has no gaps, and SUM with OVER gives a running total.

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE sales (region TEXT, rep TEXT, amount INT)")
con.executemany("INSERT INTO sales VALUES (?,?,?)", [
    ("East", "Asha", 500), ("East", "Ravi", 500), ("East", "Meena", 300),
    ("West", "Kiran", 800), ("West", "Dev", 200)])

rows = con.execute("""
SELECT region, rep, amount,
       ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC, rep) AS rn,
       RANK()       OVER (PARTITION BY region ORDER BY amount DESC) AS rnk,
       DENSE_RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS drnk
FROM sales ORDER BY region, rn""").fetchall()

for r in rows:
    print(r)

('East', 'Asha', 500, 1, 1, 1)
('East', 'Ravi', 500, 2, 1, 1)
('East', 'Meena', 300, 3, 3, 2)
('West', 'Kiran', 800, 1, 1, 1)
('West', 'Dev', 200, 2, 2, 2)

Q20. How do you get the top N rows for each group?

Rank the rows inside each group with ROW_NUMBER over a partition, then filter on the rank in an outer query or a common table expression. Use RANK or DENSE_RANK if you want ties to be kept. This pattern replaces correlated subqueries and is the most common Hive interview query.

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE sales (region TEXT, rep TEXT, amount INT)")
con.executemany("INSERT INTO sales VALUES (?,?,?)", [
    ("East", "Asha", 500), ("East", "Ravi", 450), ("East", "Meena", 300),
    ("West", "Kiran", 800), ("West", "Dev", 200), ("West", "Zoya", 650)])

rows = con.execute("""
WITH ranked AS (
    SELECT region, rep, amount,
           ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn
    FROM sales)
SELECT region, rep, amount FROM ranked WHERE rn <= 2 ORDER BY region, rn""").fetchall()

for r in rows:
    print(r)

('East', 'Asha', 500)
('East', 'Ravi', 450)
('West', 'Kiran', 800)
('West', 'Zoya', 650)

Q21. How do you calculate a running total?

Use SUM as a window function with OVER, ordering by the date and, if needed, a frame such as rows between unbounded preceding and the current row. The running total grows with each row, restarting for each partition if you add PARTITION BY.

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE daily (day TEXT, amount INT)")
con.executemany("INSERT INTO daily VALUES (?,?)", [("d1", 100), ("d2", 250), ("d3", 50)])

for r in con.execute("""SELECT day, amount,
       SUM(amount) OVER (ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running
       FROM daily ORDER BY day"""):
    print(r)

('d1', 100, 100)
('d2', 250, 350)
('d3', 50, 400)

Q22. How do you handle arrays and maps with explode and lateral view?

The explode function turns an array or map into one row per element. Because you cannot select other columns beside explode directly, you combine it with LATERAL VIEW, which joins each source row to the rows it generates. Use LATERAL VIEW OUTER to keep rows whose array is empty.

SELECT user_id, tag
FROM users
LATERAL VIEW explode(tags) t AS tag;

Q23. What are UDF, UDAF and UDTF?

A user defined function, or UDF, maps one row to one value. A user defined aggregate function, or UDAF, combines many rows into one value, like SUM. A user defined table generating function, or UDTF, turns one row into many rows, like explode. You write them in Java and register the JAR, though Hive has a very large set of built in functions.

Q24. What is the difference between INSERT OVERWRITE and INSERT INTO?

INSERT INTO appends rows to a table or partition. INSERT OVERWRITE replaces the existing data in the table or the named partitions with the query result. Overwrite is used to reload a partition safely, so that a rerun gives the same result and does not duplicate data.

Performance Tuning (Intermediate and Experienced)

Be ready to explain the first three things you would check when a query is slow.

Q25. How do you improve the performance of a Hive query?

Filter on partition columns so pruning works, store data as ORC or Parquet, use Tez, enable vectorisation and the cost based optimiser with up to date statistics, use map joins for small tables, avoid many small files, and select only the columns you need. Look at the query plan before changing settings.

• Partition and prune first.
• Use a columnar format.
• Keep statistics fresh with ANALYZE TABLE.
• Prefer map joins and avoid skew.

Q26. What does the EXPLAIN command show?

EXPLAIN prints the plan Hive will use without running the query. It shows the stages, the operators, the joins chosen and any partition pruning. Reading it tells you whether a filter was pushed down, whether a map join was used, and where a shuffle happens. EXPLAIN EXTENDED adds more detail.

EXPLAIN
SELECT country, SUM(amount) FROM sales
WHERE sale_date = DATE '2026-03-01'
GROUP BY country;

Q27. What is vectorisation?

Vectorisation processes rows in batches of, by default, 1024 rows at a time, in place of one row at a time, which reduces CPU overhead and speeds scans, filters and aggregations. It works with ORC and, in newer versions, Parquet. It is enabled by a setting and is on by default in recent releases.

Q28. What is the cost based optimiser, and why do statistics matter?

The cost based optimiser, built on Apache Calcite, uses table and column statistics to choose the join order and join type with the lowest estimated cost. Without current statistics it makes poor choices. Collect them with ANALYZE TABLE, and enable automatic statistics gathering where possible.

ANALYZE TABLE sales PARTITION (sale_date) COMPUTE STATISTICS;
ANALYZE TABLE sales COMPUTE STATISTICS FOR COLUMNS;

Q29. What is data skew, and how do you handle it in Hive?

Skew is when one key holds a large share of the rows, so one reducer does most of the work. Handle it with the skew join setting, by adding a salt to the hot key and joining in two steps, by filtering and handling the hot key separately, or by using a map join if one side is small.

Q30. How do you deal with small files in Hive?

Small files strain the NameNode and create many tiny tasks. Reduce them by merging output files with the merge settings, compacting partitions with a scheduled job, writing fewer and larger files with fewer reducers, and using ACID compaction on transactional tables. Avoid over partitioning, which is the common cause.

Q31. What is LLAP?

Live Long and Process, or LLAP, is a long running daemon layer that caches data and runs query fragments in memory on the worker nodes, giving interactive speed to Hive queries without the start up cost of new containers. It is used where many short queries come from BI tools. It is optional.

Q32. How do Hive, Spark SQL, Impala and Trino compare?

All can query data in a Hadoop style lake, and many share the Hive metastore. Hive is strong for large, reliable batch jobs and ACID tables. Spark SQL suits ETL and machine learning in one engine. Impala and Trino are built for fast interactive queries. Choose by the workload and the skills you have.

ACID Tables and Modern Hive (Experienced)

These questions confirm that you know the Hive of the last few years, and not only the original read only warehouse.

Q33. Does Hive support updates and deletes?

Yes, on ACID tables. Since Hive 3, managed tables are transactional by default, so INSERT, UPDATE, DELETE and MERGE work at the row level, with snapshot isolation for readers. Older non transactional tables and external tables do not support row level changes, so you rewrite the data or partition instead.

CREATE TABLE customers (id INT, name STRING, city STRING)
STORED AS ORC TBLPROPERTIES ('transactional'='true');

UPDATE customers SET city = 'Pune' WHERE id = 7;
DELETE FROM customers WHERE id = 9;

What the interviewer is listening for: You say that row level changes work on ACID tables, and that the old answer of Hive cannot update is out of date.

Q34. How do ACID tables work internally?

Hive writes changes as delta files next to the base files, in an ORC layout. An insert writes a delta, and an update or delete writes a delete delta that marks the old row. A reader merges the base and the deltas at query time. Compaction later merges deltas into new base files to keep reads fast.

Q35. What is compaction in Hive?

Compaction merges delta files. A minor compaction combines several delta files into one, and a major compaction rewrites the base file with all deltas applied, which removes deleted rows. The metastore runs a compactor that triggers automatically on thresholds, and you can request one manually.

ALTER TABLE customers COMPACT 'major';
SHOW COMPACTIONS;

Q36. Does Hive enforce constraints such as primary and foreign keys?

Primary and foreign key constraints in Hive are informational. They are declared with DISABLE NOVALIDATE and are not enforced, though the optimiser can use them with RELY. NOT NULL, DEFAULT and CHECK constraints are enforced on write in Hive 3 and later. Do not assume Hive stops duplicate keys.

What the interviewer is listening for: You say that keys are informational and not enforced.

Q37. What is the MERGE statement used for?

MERGE updates, inserts or deletes rows in a target table based on a match with a source table, in one statement. It is the standard way to apply change data to an ACID table, such as a daily extract of changed customers, and it avoids rewriting a whole partition.

MERGE INTO customers AS t
USING customer_updates AS s ON t.id = s.id
WHEN MATCHED THEN UPDATE SET city = s.city
WHEN NOT MATCHED THEN INSERT VALUES (s.id, s.name, s.city);

Q38. How does Hive work with Apache Iceberg?

Hive 4 supports Iceberg tables, an open table format that adds snapshots, schema and partition evolution and time travel on top of Parquet, ORC or Avro files. It lets Hive, Spark and Trino share one table safely. Create an Iceberg table in Hive with the STORED BY ICEBERG clause.

CREATE TABLE events_ice (id BIGINT, ts TIMESTAMP, payload STRING)
STORED BY ICEBERG;

Q39. How do you secure Hive?

Use Kerberos or LDAP for authentication on HiveServer2 and TLS for connections. Use Apache Ranger or storage based authorisation for table and column level permissions and row filters, and mask sensitive columns. Disable the old Hive CLI, and audit queries.

Scenario Questions (Experienced)

Describe the cause first, then the fix, and finish with the prevention.

Q40. A daily report query that used to take 5 minutes now takes an hour. What do you check?

Compare the plans from before and after with EXPLAIN. Check whether partition pruning still applies, whether statistics are stale, whether the data grew or became skewed, whether many small files appeared, and whether the cluster queue is busy. Refresh statistics, fix the file layout and rerun. Add monitoring on query time.

Q41. You must load 3 years of history into a partitioned table. How do you do it?

Load by date range in batches with dynamic partitioning, using INSERT OVERWRITE so a rerun is safe. Raise the limits for partitions per node, convert to ORC or Parquet, and collect statistics at the end. Check the file count per partition, and merge small files, then validate row counts against the source.

Q42. How would you design a Hive warehouse for sales analysis?

Land raw data in external tables, then build cleaned tables in ORC, partitioned by sale date. Model facts and dimensions, bucket large fact tables on the common join key, and keep small dimensions for map joins. Use ACID or Iceberg tables for data that changes, collect statistics and schedule loads with Airflow.

What the interviewer is listening for: You separate the raw and curated layers, partition by date, and consider joins and updates.

Q43. A reducer is running for hours while all others finished. What is happening?

That is data skew on a key, such as a null or a default value that has many rows. Check the key distribution, filter or handle the hot key separately, enable skew join, or salt the key. Check the number of reducers too, and consider a map join if one side is small.

Q44. Your team still runs Hive 2 on a Hadoop 2 cluster. How do you plan an upgrade?

Move in steps. First take inventory of tables, UDFs and jobs, and test them on a new cluster running a supported Hive 4 release with the current Java requirements. Convert tables to ORC and ACID where needed, update deprecated settings, and run old and new in parallel to compare results. Cut over by workload.

Q45. Business users want to run interactive dashboards on Hive. What do you suggest?

Use Tez with LLAP, or an interactive engine such as Trino or Impala over the same tables. Store data in ORC or Parquet with statistics, partition well, and pre aggregate heavy dashboards into summary tables or materialised views. Set queue limits, so dashboards do not compete with large batch jobs.

Common questions about Hive interviews

What topics should I prepare for a Hive interview?

Prepare the architecture, managed and external tables, partitions and buckets, file formats and joins first. Then window functions, top N queries and performance tuning. Add ACID tables, MERGE, Iceberg and the changes in Hive 4. Practise writing queries by hand.

Is Hive still relevant in 2026?

Yes, for large batch warehousing on Hadoop and object storage, and because the Hive metastore is the catalog for many other engines. Many teams also use Spark SQL or Trino, so interviewers value people who understand both Hive tables and those engines.

What is the difference between Hive and Spark SQL?

Hive is a warehouse system with its own server, metastore and engine, strongest for reliable large batch jobs and ACID tables. Spark SQL is the SQL part of Spark and suits ETL and machine learning in a single program. Spark can read Hive tables through the shared metastore.

Do I need to know SQL before learning Hive?

Yes. HiveQL is close to standard SQL, so knowing joins, group by, subqueries and window functions makes Hive quick to learn. The Hive specific parts are partitions, buckets, file formats and tuning.

How can I practise Hive at home?

Run Hive in a container, create external tables over sample files, then build partitioned ORC tables and compare query plans with EXPLAIN. To practise queries only, you can use any SQL engine that supports window functions, since the syntax matches.

A four week plan to prepare

Use this plan if you have about a month before your interview.

Practise with a trainer

If you want to practise these questions with a trainer and work on hands on projects, you can book a free demo class for the Prwatech Big Data course and Hadoop course. It covers Hive, Spark, HDFS, data pipelines and hands on projects.

Contact the learning team for current batch dates and fees.

Written by Mithun Ray, data engineering trainer at Prwatech. Reviewed by Avishekh Verma. Last updated October,2026.