Articles
Apache Hive Interview Questions: Partitions, ACID Tables, and Compaction Failures
Prepare for Apache Hive interviews with a three-date partition ledger, managed ACID lifecycle, SHOW COMPACTIONS diagnosis, and cleanup cases.
A Hive interview answer should connect three operations that are easy to discuss separately: partitioning decides which files a query considers, an ACID table tracks row changes through base and delta files, and compaction rewrites those files without changing the logical result. When a query slows or old files remain, the useful question is which layer failed—not whether Hive “supports transactions” in the abstract.
This guide uses Apache Hive's DDL reference, transaction and compactor reference, and managed versus external table guidance. The partition ledger and failure investigation are original interview exercises, not a claim that a particular employer asks them or that we ran a Hive cluster. Hive documentation spans versions, so verify commands and table requirements against the installed release before applying them. For adjacent practice, start with Answer SQL and Data Warehouse Fundamentals for a Data Engineering Interview.
What is a Hive partition actually buying you?
A partition is a table-organization choice: PARTITIONED BY (ds STRING) creates separate storage directories for distinct date values, according to Hive's DDL manual. A query constrained to a date can potentially scan fewer partitions than one that reads the whole table. The candidate should say potentially until checking the plan and actual file reads. A predicate on a transformed expression, a missing partition restriction, stale metadata, or an engine/version behavior can defeat an assumed pruning benefit.
Partitioning is not a substitute for choosing a useful grain. Hourly partitions on a sparse data set may create many tiny directories and files; monthly partitions on a busy event table may still read far too much data for a one-day query. Ask about query patterns, ingestion cadence, lateness, retention, and partition count. The right answer balances read elimination against metadata and write-management cost. It should also distinguish partitioning from bucketing: partition directories are selected by partition-column values, while bucket organization is a different layout concern.
Consider an original table orders_acid partitioned by ds. It has three dates: 2026-09-21 with 1,000 orders, 2026-09-22 with 1,100, and 2026-09-23 with 1,200. The query asks for September 23. A candidate should predict that a direct predicate on ds gives the optimizer a clear chance to select one partition, then confirm with EXPLAIN and runtime evidence. Rewriting the filter only as an expression on an unrelated event timestamp may require more work, even if the dates are conceptually related. Do not assert identical plans across Hive releases without seeing them.
The worked asset is a two-column ledger: logical rows per date and physical base/delta files per date. A partition predicate should change which date's files are read; compaction should change how many physical files represent that date, while leaving the logical rows the same. This separation prevents the common mistake of claiming compaction fixed a missing partition filter or that partitioning eliminated delta-file overhead inside the selected partition.
When do you choose managed versus external tables?
Hive's managed/external guide says managed tables put data lifecycle under Hive management, while external tables describe files that may be managed outside Hive. The guide states ACID/transactional behavior applies to managed tables in its feature comparison. It also describes the usual drop distinction: dropping a managed table removes its data and metadata, whereas dropping an external table normally removes metadata while leaving files. Version-specific purge and retention properties can change a deletion outcome, so a production answer must inspect table properties before executing a destructive drop.
Use a decision table rather than a slogan. If Hive owns a mutable order table that needs row-level update/delete and compaction, managed transactional storage is the relevant path. If an upstream system owns immutable files and Hive only adds a schema and partition catalog, external may better express ownership. If multiple engines will mutate the same files, declaring an external Hive table does not magically coordinate their writes. State who owns data deletion, schema changes, and consistency.
| Requirement | Managed transactional candidate | External-file candidate |
|---|---|---|
| Hive-controlled row updates/deletes | Fits the documented ACID path, subject to release/configuration | Do not assume the same ACID behavior |
| Upstream owns raw files | May create conflicting lifecycle ownership | Typically fits metadata-over-files ownership |
| Drop behavior | Check data deletion and purge policy | Check metadata removal plus versioned purge settings |
| Compaction responsibility | Hive compactor can manage ACID base/delta layout | Do not assume Hive compactor owns arbitrary external files |
This is an editorial decision aid based on cited Hive documentation, not a promise about every vendor distribution. Describe the deployed release, transaction manager, storage format, and table properties when giving an operational answer.
What happens after an ACID update or delete?
HDFS-style files are not edited in place. Hive's transaction documentation describes base files and delta files: inserts, updates, and deletes add new data or change records through deltas; a reader merges the relevant base and delta state at read time. The logical query result can therefore be current even while the storage directory contains multiple physical generations. Counting directory entries is not counting live rows.
Imagine the September 23 partition starts with base rows for order IDs 1, 2, and 3. A transaction changes amount for order 2 and another deletes order 3. The current logical query should include order 1 and the new version of order 2, not the old version plus a second row or a still-live order 3. Meanwhile the physical layout can include an older base and multiple deltas until compaction and cleanup. This example is conceptual; the exact directory names and write IDs depend on the Hive release and workload.
The compactor runs in the background. Hive documents an Initiator that discovers work, Workers that rewrite files, and a Cleaner that removes obsolete files after they are no longer needed by readers. Minor compaction combines delta files; major compaction rewrites base plus deltas into a new base representation. Neither is a business-level deduplication job for corrupt source rows. If the logical result contains a duplicate order because two distinct transactions inserted it, compaction should preserve that logical state rather than secretly choosing a winner.
Why can old files remain after compaction?
“Compaction finished” and “old files have been deleted” are distinct states. The Hive transaction reference says compaction does not block normal concurrent reads and writes in the minor/major cases, and the system waits until readers of old files finish before removing them. The DDL SHOW COMPACTIONS reference includes ready for cleaning as a state after compaction work but before old files are cleaned. Therefore a directory snapshot that still shows old files does not alone prove the compaction failed.
Inspect the partition, compaction ID, type, state, enqueue/start time, worker, and error message where available. An initiated request suggests queue wait; working suggests active rewrite; ready for cleaning points to the cleanup phase; failed or attempted needs metastore/job logs. If a request is repeatedly failing, Hive documentation notes that automatic scheduling can stop for a partition after a configured threshold of consecutive failures. That makes “wait longer” a poor diagnosis. Check the failed-compaction threshold and the reason, then repair the cause before considering a manual retry.
The original failure card has two partitions. ds=2026-09-22 has ready for cleaning and an old long-running reader; ds=2026-09-23 has three failed attempts and no new successful base. Both have old files, but their next actions differ. For the first, examine reader age and cleaner progress. For the second, read error details, resource limits, file corruption, permissions, or queue availability as indicated by logs. A candidate who calls both “compaction stuck” has not identified the operation that is stuck.
How would you investigate a slow transactional query?
Start with the query plan and partition selection. If a one-day query touches all three example dates, fix or understand pruning before tuning compaction. Next inspect selected partitions' file counts, delta/base mix, and compaction history. A high delta count can increase read work because readers reconcile more changes, but it is not proof that compaction is the only bottleneck. Skew, joins, statistics, storage latency, and execution-engine settings may dominate.
Compare a before/after measurement with the same query text, data snapshot, execution settings, and cache conditions where possible. Record partitions scanned, physical bytes/files read, wall time, and logical row count. Compaction may change the file-read profile without changing the result count. If the result count changes after an alleged maintenance-only compaction, investigate concurrent writes, snapshot timing, or a correctness issue. Do not report “compaction fixed data” based solely on a faster query.
Version and configuration matter here. The Hive transaction page contains historical requirements from early releases alongside later notes; for example, it discusses older bucketing and format limitations and later changes. A strong interview answer avoids transplanting one version's prerequisite as a timeless rule. State the installed Hive version, distribution, transactional table type, file format, and transaction-manager configuration, then compare those with the applicable documentation.
What would you test in a small Hive lab?
In a controlled cluster with a declared version, create a tiny managed transactional table partitioned by date using the syntax supported there. Insert several rows, update one, delete one, and query the logical result before compaction. Record the partition's physical files and SHOW COMPACTIONS output. Request a compaction only if that is appropriate for the lab, then observe the state transition and the unchanged logical result. Because compaction is asynchronous, a command that enqueues it is not evidence that a worker completed it.
For the partition exercise, query one date and all dates; inspect EXPLAIN and runtime file/partition metrics. For the lifecycle exercise, keep a reader open if the lab permits it, then observe whether cleanup waits. For the failure exercise, use logs and status rather than deliberately corrupting a production table. These are proposed tests; this article has not executed a Hive cluster or verified a particular release's output. In an interview, the test design is more valuable than an invented transcript.
Close the lab by checking the metadata and data-ownership contract of any external table you create. Do not run a drop test against real files just to demonstrate a textbook distinction. The record should say whether the table is managed/external, whether purge or retention settings apply, and who owns recovery from accidental removal.
What is a concise interview answer?
“I would first state the Hive version and table type. A date partition can reduce the files a date-constrained query considers, but I would verify pruning in the plan. For a managed ACID table, logical updates/deletes are read from base plus deltas; minor or major compaction rewrites those files in the background. I would use SHOW COMPACTIONS to distinguish queued, running, ready-for-cleaning, and failed work. Old files after ready for cleaning may be waiting on readers; repeated failures need logs and a cause, not blind retries. I would compare logical row counts and physical file reads before and after maintenance so a speed gain is not confused with a data change.”
That answer connects query selection, transaction visibility, and storage maintenance. If the interviewer asks whether a raw external table should be transactional, revisit file ownership and the deployed version rather than assuming all Hive tables share the same lifecycle.