Apache Hive Deep Dive for Data Engineers: Metastore, Partitions, ORC & Tez vs MapReduce

Apache Hive is the piece of the data-engineering stack that everyone claims is "legacy" and almost everyone still runs — because the thing Hive invented, a relational catalog that lets SQL run over raw files in HDFS or object storage, quietly became the foundation that Spark SQL, Trino, Presto, and Impala all sit on top of. A single Hive table is not a file; it is a contract stored in the metastore — a name, a set of columns, a physical layout of folders on disk, a serialization format, and a location — and the same folder of files can be read by five different engines because they all resolve that contract from one shared catalog. Understanding Hive is therefore not nostalgia; it is understanding the metadata layer, the physical-layout decisions, and the execution model that every modern lakehouse query still inherits.

This guide is the senior walkthrough for the four questions a Hive interview or a slow query always comes back to: where does the schema live, how is the data laid out on disk, how much of it does a query actually read, and which engine turns your HiveQL into work. It covers the Hive metastore and schema-on-read, partitions and partition pruning as the biggest scan-reduction lever, the ORC columnar format with its stripes, min/max statistics and predicate pushdown, bucketing for shuffle-free joins, and the Tez versus MapReduce execution-engine trade-off. Each section pairs a teaching block with a Solution-Tail interview answer — HiveQL, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the SQL practice library →, rehearse on the data processing practice library →, and sharpen the tuning axis with the optimization practice library →.

---

On this page

• Why Apache Hive still matters — architecture and the engine choice
• The Hive metastore — the catalog behind SQL-on-files
• Partitions and partition pruning
• ORC — columnar storage, predicate pushdown, and compression
• Bucketing, and Tez vs MapReduce execution engines
• Cheat sheet — Apache Hive recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why Apache Hive still matters — architecture and the engine choice

Four moving parts, four independent decisions — Hive is a catalog, a layout, a format, and an engine bolted together

The one-sentence invariant: Apache Hive is not one system but four loosely-coupled decisions stacked on each other — a HiveQL compiler that turns SQL into a job, a Hive metastore that stores the schema-on-read contract separately from the data, a physical layout of partitions, bucketing, and columnar ORC files on HDFS or object storage, and a pluggable execution engine (MapReduce, Tez, or Spark) that runs the resulting DAG — and every Hive performance problem, and every Hive interview, resolves to which of those four layers is the bottleneck. Treat them as one blob and you will "tune Hive" by randomly changing settings; treat them as four layers and you diagnose in minutes.

The four layers, top to bottom.

• HiveQL compiler + driver. Parses SQL, type-checks it against the metastore, builds a logical plan, applies optimizations (partition pruning, predicate pushdown, join reordering), and compiles a physical plan targeted at the chosen engine. This layer is where pruning and pushdown decisions are made — the storage layer only benefits if the compiler emits them.
• Metastore (HMS). A relational database (MySQL/Postgres/Derby) fronted by a Thrift service that stores table definitions, column types, partition lists, storage locations, and SerDe classes. This is schema-on-read: the schema lives in the catalog, not in the files, so the same bytes can be reinterpreted or shared across engines.
• Storage layout. The physical arrangement of files: which column the table is partitioned on (one directory per value), whether it is bucketed (hash-distributed into a fixed number of files), and which file format encodes the rows (text, Avro, Parquet, or ORC). This layer decides how much data a query must physically read.
• Execution engine. The runtime that executes the physical plan: classic MapReduce (a chain of map/reduce stages that write to HDFS between each), Tez (a single in-memory DAG with container reuse), or Spark. Same HiveQL, radically different wall-clock.

The four axes interviewers actually probe.

• Where does the schema live? In the metastore, never in the data files (schema-on-read). This is why DROP TABLE on an external table deletes the metadata but not the files, and why five engines can share one table.
• How is the data laid out? Partitioning (directory pruning), bucketing (join/sampling), and file format (columnar skip). The layout is chosen at table-create time and is expensive to change later.
• How much does the query scan? The product of partition pruning (skip whole directories), ORC stripe skipping (skip whole stripes via min/max stats), and column projection (read only referenced columns). A well-laid-out table can answer a query by reading <1% of the bytes.
• Which engine runs it? Tez for interactive/DAG workloads (the modern default), MapReduce only for legacy compatibility, Spark when you already run a Spark cluster. The engine is a per-session setting, not a table property.

The 2026 reality — the metastore outlived the query engine.

• The Hive Metastore (HMS) is the de-facto catalog. Spark, Trino, Presto, Impala, and Flink all read table definitions from HMS. Even Iceberg and Delta deployments frequently register tables in HMS so legacy tools can still see them. The catalog is the crown jewel; the Hive query engine is increasingly one client among many.
• Hive-on-Tez is still the batch workhorse. For large ETL batch jobs on Hadoop/YARN clusters, Hive-on-Tez with ORC remains extremely common and cost-effective. It is not the fastest interactive engine (Trino/Impala win there), but it is reliable and scales.
• ORC and Parquet won the file-format war. Plain-text and CSV tables survive only for landing zones. Any table you query repeatedly is columnar (ORC or Parquet) with statistics, compression, and predicate pushdown.
• MapReduce is deprecated as an execution engine. hive.execution.engine=mr still works but is discouraged and removed in some distributions. Knowing why Tez replaced it (the write-to-disk-between-stages tax) is a standard interview probe.

What interviewers listen for.

• Do you say "schema-on-read" and explain that the schema lives in the metastore, not the file? — required answer.
• Do you name partition pruning as the first lever for a slow Hive query, before touching engine settings? — senior signal.
• Do you explain why ORC is faster in terms of stripes, min/max stats, and predicate pushdown — not just "it's columnar"? — senior signal.
• Do you contrast Tez vs MapReduce as "one in-memory DAG" vs "a chain that materializes to HDFS between every stage"? — required answer.
• Do you distinguish partitioning (directory pruning) from bucketing (join/sampling) cleanly? — senior signal.

Worked example — mapping a slow query to the four layers

Detailed explanation. The most useful diagnostic skill in a Hive shop is decomposing "this query is slow" into which of the four layers is responsible. Every senior Hive engineer runs the same mental checklist; codifying it makes the interview answer reproducible. Walk through the checklist against a concrete complaint: a daily revenue rollup over a sales table takes 40 minutes.

• Symptom. SELECT dt, SUM(amount) FROM sales WHERE dt = '2026-08-18' GROUP BY dt takes 40 minutes.
• Table. sales — 3 TB of text files, no partitions, hive.execution.engine=mr.
• Goal. Get it under a minute without changing the query.

Question. Attribute the 40-minute cost to the four layers, and name the single highest-leverage fix in each.

Input.

Layer	Current state	Consequence
Compiler	pruning impossible (no partitions)	scans all 3 TB
Metastore	one table, zero partitions	no directory to skip
Storage	plain text, row-oriented	reads every byte of every column
Engine	MapReduce	writes to HDFS between stages

Code.

-- The slow query (unchanged)
SELECT dt, SUM(amount)
FROM   sales
WHERE  dt = '2026-08-18'
GROUP  BY dt;

-- The four-layer fix, applied at table-create time
-- 1. Storage + 2. layout: partition by dt, store as ORC
CREATE TABLE sales_orc (
    order_id     BIGINT,
    customer_id  BIGINT,
    amount       DECIMAL(12,2),
    status       STRING
)
PARTITIONED BY (dt STRING)
STORED AS ORC
TBLPROPERTIES ('orc.compress' = 'ZSTD');

-- 3. engine: use Tez instead of MapReduce (session setting)
SET hive.execution.engine = tez;
SET hive.vectorized.execution.enabled = true;

Step-by-step explanation.

1. Compiler layer. With no partitions, the compiler cannot prune anything, so the WHERE dt = ... filter is applied after reading all 3 TB. Partitioning sales by dt lets the compiler prune to a single directory at plan time — the biggest single win.
2. Metastore layer. The metastore currently knows one table and zero partitions, so there is nothing to skip. After partitioning, the metastore lists one partition per day; the compiler asks the metastore "which partition matches dt='2026-08-18'?" and gets back one directory.
3. Storage layer. Plain-text row-oriented storage means the reader decodes every column of every row even though the query needs only amount. ORC stores columns separately, so the reader projects just amount (and the partition key dt comes for free from the directory name).
4. Engine layer. MapReduce writes intermediate results to HDFS between the map and reduce stages; on a multi-stage plan that disk round-trip dominates. Tez runs the same plan as one in-memory DAG. Switching the engine is a one-line session setting and needs no table rewrite.
5. The combined effect: pruning drops the scan from 3 TB to ~8 GB (one day), ORC column projection drops it again to the amount column, and Tez removes the inter-stage disk tax. The 40-minute query becomes seconds.

Output.

Fix	Layer	Data scanned	Rough speedup
Partition by dt	compiler + metastore	3 TB → ~8 GB	~350×
Store as ORC + column projection	storage	~8 GB → ~500 MB	further ~15×
engine=tez + vectorization	engine	same bytes, less overhead	further ~3-5×
All three combined	end-to-end	40 min → < 30 s	~80×+

Rule of thumb. Diagnose a slow Hive query top-down through the four layers — compiler (is it pruning?), metastore (are there partitions to skip?), storage (is it columnar with projection?), engine (is it Tez?). The first three save I/O; the last saves overhead. Fix the I/O layers first.

Worked example — schema-on-read in one CREATE TABLE

Detailed explanation. The single idea that separates Hive from a traditional RDBMS is schema-on-read: the files exist first, and the CREATE TABLE statement layers a schema over them without moving or validating a single byte. Demonstrating this with an external table makes the concept concrete — and it is the reason DROP TABLE behaves differently for managed vs external tables.

• Files first. A folder of CSV files already sits in object storage.
• Schema second. An external table declares columns, delimiter, and location.
• No copy. The CREATE TABLE writes only metastore rows; the data is untouched.

Question. Create an external table over pre-existing CSV files and explain what actually happens on disk and in the metastore.

Input.

Item	Value
Existing data	s3://lake/raw/clicks/*.csv
Columns	user_id BIGINT, url STRING, ts STRING
Table type	EXTERNAL (data owned elsewhere)
Format	text, comma-delimited

Code.

CREATE EXTERNAL TABLE clicks (
    user_id  BIGINT,
    url      STRING,
    ts       STRING
)
ROW FORMAT DELIMITED
  FIELDS TERMINATED BY ','
STORED AS TEXTFILE
LOCATION 's3://lake/raw/clicks/';

-- Reading applies the schema on the fly; nothing was copied or validated
SELECT COUNT(*) FROM clicks;

-- DROP removes ONLY the metastore entry for an EXTERNAL table; files survive
DROP TABLE clicks;

Step-by-step explanation.

1. CREATE EXTERNAL TABLE writes rows into the metastore's TBLS, COLUMNS_V2, and SDS tables — the table name, the three columns and their types, the SerDe (LazySimpleSerDe for delimited text), and the LOCATION. No files are read; no data is scanned.
2. Because the schema is applied on read, a row whose third field is not a valid CSV column is not rejected at insert time (there was no insert) — it simply becomes NULL or a parse artifact when the query reads it. This is the schema-on-read trade-off: flexibility now, data-quality vigilance later.
3. The first query that touches clicks lists the files under the LOCATION, applies the SerDe to each line, and materializes rows. The metastore told the compiler the columns; the SerDe told it how to split each line.
4. EXTERNAL signals that Hive does not own the data lifecycle. DROP TABLE clicks deletes only the metastore rows; s3://lake/raw/clicks/ is untouched. For a managed table (omit EXTERNAL), DROP TABLE also deletes the files under the warehouse directory — a classic accidental-data-loss trap.
5. This is why the same folder can back several tables and be read by Spark, Trino, and Hive at once: the bytes are neutral; each engine resolves the schema-on-read contract from the shared metastore.

Output.

Action	Metastore effect	File effect
CREATE EXTERNAL TABLE	rows added to TBLS/COLUMNS_V2/SDS	none (no copy)
SELECT	schema applied per row at read time	files read, not modified
DROP TABLE (external)	metastore rows removed	files survive
DROP TABLE (managed)	metastore rows removed	files deleted

Rule of thumb. Use EXTERNAL tables for any data whose lifecycle you do not want Hive to own (shared lakes, landing zones, tables other engines write). Reserve managed tables for data Hive fully owns. The difference is invisible until DROP TABLE deletes a terabyte you meant to keep.

Worked example — the engine decision in three questions

Detailed explanation. Given a Hive workload, the senior engineer picks the execution engine with a three-question decision tree rather than by habit. Codifying the tree makes the interview answer reproducible: any interviewer can hand you a scenario and you walk it out loud. Walk through the tree with three canonical workloads.

• Q1. Is this an interactive/ad-hoc query needing seconds of latency? → consider Trino/Impala over Hive entirely; if it must be Hive, use Tez.
• Q2. Is this a large multi-stage batch ETL on a YARN/Hadoop cluster? → Hive-on-Tez with ORC.
• Q3. Do you already run a managed Spark platform and want one engine? → Hive-on-Spark (or migrate the query to Spark SQL).

Question. Walk the tree for three workloads and record the engine each ends up with.

Input.

Workload	Latency need	Cluster	Chosen engine
Nightly 5 TB revenue ETL	hours OK	YARN + Tez	Tez
Analyst ad-hoc dashboards	seconds	shared	Trino (not Hive)
Feature pipeline in a Spark shop	minutes	Spark on k8s	Spark

Code.

-- Tez: the modern Hive default for batch
SET hive.execution.engine = tez;
SET hive.tez.container.size = 4096;          -- MB per Tez task
SET hive.vectorized.execution.enabled = true;

-- MapReduce: legacy only; discouraged and removed in newer distros
-- SET hive.execution.engine = mr;

-- Spark: when a Spark cluster is already the platform
-- SET hive.execution.engine = spark;

Step-by-step explanation.

1. Workload 1 is a large multi-stage batch ETL — exactly what Hive-on-Tez is built for. Tez runs the multi-stage plan as one DAG, reuses containers across vertices, and passes data through in-memory edges instead of HDFS. ORC + partition pruning does the I/O reduction; Tez does the orchestration.
2. Workload 2 needs sub-second interactive latency for analyst dashboards. Hive (even on Tez) has per-query startup overhead that makes it a poor interactive engine. The senior answer is "don't use Hive here — point Trino or Impala at the same Hive metastore." The catalog is shared, so no data moves.
3. Workload 3 lives in a Spark-first platform. Rather than run two engines, either set hive.execution.engine=spark or migrate the HiveQL to Spark SQL — both read the same metastore tables. Consistency of platform beats micro-optimizing the engine.
4. MapReduce is not a serious choice in 2026 for any new workload; it survives only where a legacy distribution has not been upgraded. Naming why (disk between stages) is the interview point, not recommending it.
5. The meta-lesson: the execution engine is a per-session decision decoupled from the table. Because the metastore holds the schema and the files hold the data, you can point different engines at the same table for different workloads without any migration.

Output.

Workload	Engine	Why
5 TB nightly ETL	Hive-on-Tez	multi-stage DAG, ORC, container reuse
Ad-hoc dashboards	Trino/Impala	interactive latency; shares HMS
Spark-shop feature pipeline	Spark	one platform; reads HMS tables
Legacy compat only	MapReduce	discouraged; disk-between-stages tax

Rule of thumb. The engine is a session setting, not a table property — so pick it per workload against a shared metastore. Tez for Hive batch, an MPP engine (Trino/Impala) for interactive, Spark when Spark is already your platform, MapReduce essentially never.

Senior interview question on Hive architecture

A senior interviewer often opens with: "You join a team whose Hive queries over a 3 TB text-based events table routinely take 30–60 minutes and occasionally fail on shuffle. You cannot change the queries, only the table and the cluster settings. Walk me through the four architectural layers of Hive, which layer each symptom maps to, and the concrete change you would make in each — in the order you would ship them."

Solution Using a layered re-platform — partitioned ORC on Tez with statistics

-- Step 1 — STORAGE + LAYOUT: rebuild events as partitioned ORC
CREATE TABLE events_orc (
    event_id     BIGINT,
    user_id      BIGINT,
    event_type   STRING,
    payload      STRING,
    amount       DECIMAL(12,2)
)
PARTITIONED BY (dt STRING)
STORED AS ORC
TBLPROPERTIES (
    'orc.compress'          = 'ZSTD',
    'orc.create.index'      = 'true',      -- row-index min/max stats
    'orc.bloom.filter.columns' = 'user_id' -- fast point lookups on user_id
);

-- Step 2 — load with dynamic partitioning (one directory per dt)
SET hive.exec.dynamic.partition = true;
SET hive.exec.dynamic.partition.mode = nonstrict;

INSERT OVERWRITE TABLE events_orc PARTITION (dt)
SELECT event_id, user_id, event_type, payload, amount, dt
FROM   events;                              -- the old text table

-- Step 3 — COMPILER: gather statistics so the optimizer can prune/pushdown
ANALYZE TABLE events_orc PARTITION (dt) COMPUTE STATISTICS;
ANALYZE TABLE events_orc PARTITION (dt) COMPUTE STATISTICS FOR COLUMNS;

-- Step 4 — ENGINE: Tez + vectorization + cost-based optimizer
SET hive.execution.engine            = tez;
SET hive.vectorized.execution.enabled = true;
SET hive.cbo.enable                  = true;   -- cost-based optimizer uses stats
SET hive.tez.container.size          = 4096;
SET hive.auto.convert.join           = true;   -- map-side join for small dims

Step-by-step trace.

Symptom	Layer	Change	Effect
Scans all 3 TB	compiler + metastore	partition by dt	prune to one day (~8 GB)
Reads every column	storage	ORC + column projection	read only referenced columns
Slow decode	storage	ZSTD + vectorization	1024-row batches, less CPU
Disk between stages	engine	MapReduce → Tez	in-memory DAG edges
Bad join plan / shuffle OOM	compiler	stats + CBO + map-join	small dims broadcast, no shuffle

After the re-platform, a day-scoped query reads one partition of ORC, projects only the referenced columns, skips stripes whose min/max cannot match the predicate, runs the plan as one Tez DAG, and broadcasts small dimension tables map-side so the large shuffle disappears. The 30–60 minute query lands in the tens-of-seconds range, and the shuffle-OOM failures stop because CBO now converts the offending join to a map-side join.

Output:

Metric	Before (text + MR)	After (ORC + Tez)
Data scanned (1-day query)	3 TB	~500 MB
Query wall-clock	30–60 min	20–40 s
Inter-stage I/O	HDFS write per stage	in-memory edges
Join strategy	shuffle join (OOM risk)	map-side broadcast join
Optimizer input	none (no stats)	table + column statistics

Why this works — concept by concept:

• Partitioning — one directory per dt lets the compiler prune to the matching partition at plan time, before any I/O. This is the largest single reduction: 3 TB to one day.
• ORC columnar storage — columns are stored in separate streams within stripes, so the reader projects only referenced columns and skips whole stripes using per-stripe min/max statistics. Row-oriented text must decode everything.
• Statistics + CBO — ANALYZE TABLE ... COMPUTE STATISTICS populates the metastore with row counts and per-column min/max/NDV, which the cost-based optimizer uses to pick join order and convert small-table joins to map-side broadcasts.
• Tez engine — a single in-memory DAG with container reuse replaces MapReduce's write-to-HDFS-between-every-stage tax. The same physical plan runs with a fraction of the orchestration and disk overhead.
• Cost — a one-time rewrite of the table (I/O to transcode text → ORC) plus a statistics pass, then every future query is O(partition) instead of O(table). The eliminated cost is repeated 3 TB full scans and the MapReduce disk round-trips. Net: one-time O(N) transcode buys O(scanned) queries forever.

SQL
Topic — sql
SQL-on-Hadoop and HiveQL query problems

Practice →

Design Topic — design Design problems on warehouse and lakehouse architecture

Practice →

---

2. The Hive metastore — the catalog behind SQL-on-files

The Hive metastore is a relational database of table definitions fronted by a Thrift service — it is the crown jewel every query engine reads

The mental model in one line: the Hive metastore (HMS) is an ordinary relational database (MySQL, Postgres, or an embedded Derby for dev) whose tables describe your tables — DBS for databases, TBLS for tables, COLUMNS_V2 for columns, PARTITIONS for partition values, and SDS for storage descriptors (location + input/output format + SerDe) — and it is exposed over a Thrift API so that Hive, Spark, Trino, Presto, Impala, and Flink can all resolve the same schema-on-read contract without ever touching each other's engines. The data files never live in the metastore; the metastore only ever holds pointers and types.

The backing tables you should be able to name.

• DBS. One row per database (schema/namespace), with its warehouse DB_LOCATION_URI.
• TBLS. One row per table: name, owner, TBL_TYPE (MANAGED_TABLE vs EXTERNAL_TABLE), and a foreign key to its storage descriptor.
• COLUMNS_V2. The column list and types for each table's storage descriptor — the schema-on-read column contract.
• SDS (storage descriptor). The physical binding: LOCATION, input format, output format, and the SERDE_ID. This is where "how do I read these bytes" is stored.
• PARTITIONS + PARTITION_KEYS + PARTITION_KEY_VALS. The list of partitions and their key values. This table grows with partition count — the reason too many partitions hurts the metastore.
• SERDES + SERDE_PARAMS. The serialization/deserialization class (e.g. OrcSerde, LazySimpleSerDe) and its parameters (delimiters, compression).

Managed vs external — the lifecycle distinction.

• Managed (MANAGED_TABLE). Hive owns the data. The files live under the warehouse directory (hive.metastore.warehouse.dir), and DROP TABLE deletes both metadata and files. Use for data Hive fully controls; in modern Hive, managed ORC tables are also the ones that support ACID (transactional) semantics.
• External (EXTERNAL_TABLE). Hive owns only the metadata. DROP TABLE removes the catalog entry but leaves the files. Use for shared lakes, landing zones, and any table other engines also write.
• The trap. Converting a table between managed and external, or dropping a managed table you thought was external, is the most common accidental-data-loss incident in a Hive shop.

Schema-on-read, restated.

• The metastore stores what the columns are; the files store the bytes. Reading joins the two.
• Adding a column with ALTER TABLE ... ADD COLUMNS is a metadata-only change — instantaneous, no data rewrite. Old files simply return NULL for the new column.
• This is the superpower and the hazard: a schema change is free, but nothing validates that the files actually match the declared schema until read time.

Common interview probes on the metastore.

• "Where does Hive store the schema?" — required answer: in the metastore RDBMS, not in the files (schema-on-read).
• "What breaks if the metastore is down?" — you cannot compile queries (no schema to resolve), even though the data files are perfectly intact.
• "Why can Spark and Trino read Hive tables?" — they speak the same Thrift metastore API and resolve the same catalog rows.
• "Why does too many partitions hurt?" — the PARTITIONS table grows linearly; partition listing and planning slow down.

Worked example — reading the metastore backing tables directly

Detailed explanation. When a table "disappears" or a partition is missing, the fastest diagnosis is to query the metastore's own relational tables. Every senior Hive engineer keeps a handful of these queries. Walk through inspecting the catalog for a table named sales.

• Goal. Confirm the table type, its location, and its partition count without running a Hive query.
• Where. Directly against the metastore RDBMS (Postgres here).
• Why. The metastore answers instantly; a Hive SHOW PARTITIONS spins up a session.

Question. Write the metastore SQL that returns a table's type, storage location, SerDe, and partition count.

Input.

Backing table	Holds
TBLS	table name, TBL_TYPE
SDS	LOCATION, formats
SERDES	SerDe class
PARTITIONS	one row per partition

Code.

-- Run against the metastore RDBMS (Postgres), NOT via HiveQL
SELECT t.tbl_name,
       t.tbl_type,                       -- MANAGED_TABLE | EXTERNAL_TABLE
       s.location,                        -- physical path
       ser.slib               AS serde,   -- e.g. org.apache.hadoop.hive.ql.io.orc.OrcSerde
       s.input_format,
       COUNT(p.part_id)       AS n_partitions
FROM   tbls  t
JOIN   sds   s   ON s.sd_id   = t.sd_id
JOIN   serdes ser ON ser.serde_id = s.serde_id
LEFT   JOIN partitions p ON p.tbl_id = t.tbl_id
WHERE  t.tbl_name = 'sales'
GROUP  BY t.tbl_name, t.tbl_type, s.location, ser.slib, s.input_format;

Step-by-step explanation.

1. TBLS is the anchor: it holds the table name and TBL_TYPE, and a foreign key SD_ID to the storage descriptor. Joining on SD_ID reaches the physical binding.
2. SDS carries the LOCATION (where the files live) and the input/output format classes (OrcInputFormat for ORC, TextInputFormat for text). This is how you confirm a table is actually ORC and not text.
3. SERDES.slib is the fully-qualified SerDe class. Seeing OrcSerde confirms the read path; seeing LazySimpleSerDe means delimited text. A mismatch here is a common "why is my ORC table slow" cause.
4. The LEFT JOIN to PARTITIONS plus COUNT(part_id) returns the partition count. A count in the hundreds of thousands is the smoking gun for over-partitioning — the metastore is doing that much bookkeeping per query.
5. This whole query runs in milliseconds against the RDBMS, which is why on-call runbooks reach for it before spinning up a Hive session.

Output.

tbl_name	tbl_type	serde	n_partitions
sales	EXTERNAL_TABLE	OrcSerde	731

Rule of thumb. Keep read-only credentials to the metastore RDBMS for diagnosis. TBLS → SDS → SERDES → PARTITIONS answers "what is this table, where does it live, how is it read, and how many partitions" faster than any HiveQL, and it works even when the Hive service is unhealthy.

Worked example — recovering partitions with MSCK REPAIR

Detailed explanation. A frequent real-world break: a Spark or Flink job writes new partition folders directly to the table's LOCATION, but never tells the Hive metastore. Hive queries then silently miss the new data because the metastore's PARTITIONS table has no rows for those folders. The fix is partition discovery — MSCK REPAIR TABLE (or ALTER TABLE ... ADD PARTITION). Walk through the failure and the repair.

• Symptom. New files exist under LOCATION but SELECT returns stale counts.
• Cause. Folders on disk are not registered in PARTITIONS.
• Fix. MSCK REPAIR TABLE scans the location and adds missing partitions.

Question. A daily writer created dt=2026-08-18/ on disk but Hive does not see it. Register it, two ways.

Input.

Fact	Value
Table	sales (external, partitioned by dt)
New folder on disk	.../sales/dt=2026-08-18/
Metastore state	no PARTITIONS row for that dt

Code.

-- Option A — discover ALL missing partitions by scanning LOCATION
MSCK REPAIR TABLE sales;
-- (Hive 3+: SYNC PARTITIONS also removes stale metastore entries)
MSCK REPAIR TABLE sales SYNC PARTITIONS;

-- Option B — register a single known partition explicitly (cheaper)
ALTER TABLE sales ADD IF NOT EXISTS
  PARTITION (dt = '2026-08-18')
  LOCATION 's3://lake/sales/dt=2026-08-18/';

-- Confirm the metastore now lists it
SHOW PARTITIONS sales;

Step-by-step explanation.

1. MSCK REPAIR TABLE sales lists every folder under the table LOCATION, parses the key=value directory names into partition specs, and inserts a PARTITIONS row for each folder the metastore is missing. It is convenient but O(folders) — expensive on tables with hundreds of thousands of partitions.
2. SYNC PARTITIONS (Hive 3+) additionally removes metastore partitions whose folders no longer exist, keeping the catalog and disk in sync. Without it, a deleted folder leaves a dangling partition that errors at read time.
3. ALTER TABLE ... ADD PARTITION registers exactly one known partition and is the right tool when your writer knows precisely which dt it just wrote — it avoids the full-location scan.
4. The LOCATION clause on ADD PARTITION is optional when the folder follows the standard key=value layout under the table location; supply it explicitly only when the partition lives somewhere non-standard.
5. The durable fix is to make the writer register the partition itself (most engines can), so the metastore never drifts from disk in the first place. MSCK REPAIR is the recovery tool, not the steady-state design.

Output.

Command	Metastore effect	Cost
MSCK REPAIR TABLE	adds all missing partitions	O(folders) scan
MSCK ... SYNC PARTITIONS	adds missing + drops stale	O(folders) scan
ALTER TABLE ADD PARTITION	adds one partition	O(1)

Rule of thumb. If a Hive query silently misses data another engine wrote, suspect unregistered partitions first. Use ALTER TABLE ADD PARTITION for known single partitions and MSCK REPAIR ... SYNC PARTITIONS for bulk recovery — then fix the writer to register partitions so the metastore never drifts.

Senior interview question on the Hive metastore

A senior interviewer might ask: "A shared Hive metastore backs Hive, Spark, and Trino for 200 teams. One team reports that a table they query is 'missing yesterday's data', another accidentally dropped a managed table and lost files, and planning has become slow on your largest table. Diagnose all three against the metastore's data model, and describe the settings and conventions you would put in place to prevent recurrence."

Solution Using metastore-aware conventions — external tables, partition registration, and metastore hygiene

-- 1. Prevent data-loss: make lake tables EXTERNAL so DROP never deletes files
CREATE EXTERNAL TABLE sales (
    order_id BIGINT, customer_id BIGINT, amount DECIMAL(12,2), status STRING
)
PARTITIONED BY (dt STRING)
STORED AS ORC
LOCATION 's3://lake/sales/'
TBLPROPERTIES ('external.table.purge' = 'false');  -- extra guard: never purge

-- 2. Fix "missing yesterday's data": writers register their own partitions
ALTER TABLE sales ADD IF NOT EXISTS PARTITION (dt = '2026-08-18');
-- or enable discovery for external tables (Hive 4):
ALTER TABLE sales SET TBLPROPERTIES (
    'discover.partitions'    = 'true',   -- metastore auto-discovers new folders
    'partition.retention.period' = '30d' -- and expires old ones
);

-- 3. Fix slow planning on a huge table: cap partition explosion + gather stats
--    Diagnose partition count straight from the metastore RDBMS:
SELECT COUNT(*) FROM partitions p JOIN tbls t ON t.tbl_id = p.tbl_id
WHERE  t.tbl_name = 'sales';                     -- e.g. 900,000 -> too many

-- Re-partition on a coarser key (dt) instead of (dt, hour, country)
-- and let stats drive the optimizer:
ANALYZE TABLE sales PARTITION (dt) COMPUTE STATISTICS;

Step-by-step trace.

Symptom	Metastore cause	Fix
"Missing yesterday's data"	no PARTITIONS row for new folder	writer runs ADD PARTITION / discover.partitions=true
Dropped managed table lost files	TBL_TYPE=MANAGED_TABLE → DROP deletes files	make lake tables EXTERNAL + purge=false
Slow planning	PARTITIONS has ~900k rows	coarser partition key; retention policy
No optimizer input	no column stats in metastore	ANALYZE TABLE ... COMPUTE STATISTICS

After the changes, lake tables are external so an accidental DROP can never delete files; writers register partitions (or the metastore discovers them), so no engine ever misses freshly-written data; and the largest table's partition count is bounded by a coarser key plus a retention policy, so planning stays fast and the metastore RDBMS is not overwhelmed.

Output:

Concern	Before	After
DROP on lake table	deletes files	metadata only (external)
New-partition visibility	manual, often forgotten	auto-discovered / writer-registered
Partition count	~900k, growing	bounded by coarse key + retention
Planner statistics	none	table + column stats present

Why this works — concept by concept:

• External tables — setting TBL_TYPE=EXTERNAL_TABLE (plus external.table.purge=false) means the metastore owns only metadata; DROP TABLE removes catalog rows and leaves the files. This eliminates the single most damaging Hive accident.
• Partition registration / discovery — a query can only read partitions that exist as rows in PARTITIONS. Making writers call ADD PARTITION, or enabling discover.partitions, keeps the catalog in lockstep with the folders on disk.
• Bounded partition cardinality — every partition is a row in PARTITIONS (and folders on disk). A coarse partition key plus a retention period keeps that table small, so planning and partition listing stay fast.
• Statistics in the metastore — COMPUTE STATISTICS writes row counts and per-column min/max/NDV into metastore tables, which the cost-based optimizer reads to pick join order and pruning. No stats means the optimizer guesses.
• Cost — external tables and partition registration are free (metadata operations); the retention policy and stats passes are cheap periodic jobs. The eliminated cost is catastrophic (lost files), silent (missing data), and chronic (slow planning). One-time convention change, permanent payoff.

SQL
Topic — sql
SQL DDL, catalog, and metadata problems

Practice →

Design Topic — design Design problems on metadata and catalog systems

Practice →

---

3. Partitions and partition pruning

partitions are one directory per key value, and partition pruning skips the directories a query can never need — the single biggest scan-reduction lever in Hive

The mental model in one line: a partitioned Hive table stores each distinct value of the partition column in its own subdirectory (.../sales/dt=2026-08-18/), the partition column is not stored in the data files (its value is encoded in the folder name), and partition pruning is the compiler optimization that reads the query's WHERE clause, matches it against the partition list in the metastore, and eliminates every directory that cannot match — so a query filtered on the partition key reads only the folders it needs and never opens the rest. Partitioning is the first thing to reach for on a slow Hive query and the first thing interviewers probe.

How partitioning changes the physical layout.

• One directory per value. PARTITIONED BY (dt STRING) creates dt=2026-08-14/, dt=2026-08-15/, … each holding that day's files. Multi-column partitions nest: PARTITIONED BY (dt STRING, country STRING) → dt=.../country=.../.
• The key lives in the path, not the file. The partition column is a virtual column projected from the directory name. This saves storage and is why you never see dt inside the ORC data.
• Pruning happens at plan time. The compiler asks the metastore for the partition list, evaluates the WHERE predicate against it, and hands the engine only the surviving directories — before a single byte of data is read.

Static vs dynamic partitioning on insert.

• Static. You name the target partition explicitly: INSERT ... PARTITION (dt='2026-08-18') SELECT .... Fast and safe when you know the target.
• Dynamic. Hive derives the partition from the data: INSERT ... PARTITION (dt) SELECT ..., dt FROM .... Requires hive.exec.dynamic.partition=true and, to allow all-dynamic keys, hive.exec.dynamic.partition.mode=nonstrict. Convenient for backfills but dangerous — a high-cardinality column creates thousands of tiny partitions.
• The guardrails. hive.exec.max.dynamic.partitions and ...max.dynamic.partitions.pernode cap the blast radius so a runaway insert fails fast instead of melting the metastore.

The two failure modes senior engineers pre-empt.

• The full-scan trap. If the query does not filter on the partition key — or wraps it in a function (WHERE substr(dt,1,7) = '2026-08', WHERE CAST(dt AS DATE) = ...) — the compiler often cannot prune and scans every partition. Predicates must be on the bare partition column for pruning to fire.
• The too-many-small-partitions trap. Partitioning on a high-cardinality column (user_id, order_id) creates millions of tiny directories. Each is a PARTITIONS row and a set of small files; planning slows, the NameNode/metastore strains, and reads suffer the small-file problem. Partition on low-cardinality columns you always filter on — date is the canonical choice.

Common interview probes on partitioning.

• "What column should you partition on?" — low-cardinality, frequently-filtered (usually date); never a high-cardinality key.
• "Why is my WHERE not pruning?" — the predicate wraps the partition column in a function, or filters a non-partition column.
• "Static vs dynamic partitioning?" — static when you know the target; dynamic for backfills, with cardinality guardrails.
• "How many partitions is too many?" — when planning and metastore listing dominate; tens of thousands is a warning, millions is a failure.

Worked example — partitioned DDL and a dynamic-partition backfill

Detailed explanation. The canonical partitioning setup: a table partitioned by dt, loaded from an unpartitioned source with a dynamic-partition insert that fans each row into the right day's directory. Walk through the DDL, the required settings, and the insert.

• Table. sales partitioned by dt STRING, stored as ORC.
• Source. An unpartitioned sales_raw with a dt column in the data.
• Load. Dynamic partitioning routes each row to its dt directory.

Question. Create the partitioned table and backfill it from sales_raw using dynamic partitioning, with cardinality guardrails.

Input.

Item	Value
Target	sales PARTITIONED BY (dt)
Source	sales_raw(order_id, customer_id, amount, status, dt)
Partition mode	dynamic, nonstrict
Guardrail	max 2000 dynamic partitions

Code.

CREATE TABLE sales (
    order_id     BIGINT,
    customer_id  BIGINT,
    amount       DECIMAL(12,2),
    status       STRING
)
PARTITIONED BY (dt STRING)
STORED AS ORC;

-- Dynamic partitioning settings + guardrails
SET hive.exec.dynamic.partition       = true;
SET hive.exec.dynamic.partition.mode  = nonstrict;   -- all keys may be dynamic
SET hive.exec.max.dynamic.partitions          = 2000;
SET hive.exec.max.dynamic.partitions.pernode  = 500;

-- Backfill: the LAST select column maps to the partition key
INSERT OVERWRITE TABLE sales PARTITION (dt)
SELECT order_id, customer_id, amount, status, dt
FROM   sales_raw;

Step-by-step explanation.

1. The PARTITIONED BY (dt STRING) clause declares dt as a partition column — it will not appear among the regular columns in the data files; its value comes from the directory name.
2. Dynamic partitioning must be explicitly enabled. nonstrict mode is required because the only partition key is dynamic; strict mode would demand at least one static partition value to prevent accidental full-table fan-out.
3. In the INSERT ... PARTITION (dt) SELECT ..., the partition column must be the last item in the select list and its name must match. Hive reads that last column per row and routes the row to the corresponding dt=<value>/ directory, creating directories as needed.
4. The guardrails (max.dynamic.partitions and ...pernode) cap how many partitions one statement may create. If sales_raw accidentally contained per-second timestamps in dt, the insert fails fast at 2000 partitions instead of creating millions — a cheap insurance policy.
5. INSERT OVERWRITE replaces the contents of each touched partition; INSERT INTO appends. For an idempotent backfill you want OVERWRITE so re-running the job does not double the rows.

Output.

dt directory created	rows routed
dt=2026-08-16/	1,204,551
dt=2026-08-17/	1,190,002
dt=2026-08-18/	1,233,918
(partition key absent from data files)	value from path

Rule of thumb. Use dynamic partitioning for backfills, always in nonstrict mode with max.dynamic.partitions set defensively, and always put the partition column last in the select. If the guardrail trips, your partition key is too high-cardinality — coarsen it before retrying.

Worked example — proving pruning with EXPLAIN, and the function trap

Detailed explanation. Partition pruning is only real if the compiler actually applies it — and the most common reason it silently does not is a predicate that wraps the partition column in a function. The way to prove pruning is EXPLAIN, which shows how many partitions the plan will read. Walk through a pruned query and its broken twin.

• Good. WHERE dt = '2026-08-18' — bare partition column, prunes to one partition.
• Bad. WHERE CAST(dt AS DATE) = DATE '2026-08-18' — function on the column defeats pruning.
• Proof. EXPLAIN (or EXPLAIN EXTENDED) reveals the partition count in the plan.

Question. Show the pruned plan and the un-pruned plan, and rewrite the broken one.

Input.

Query	Predicate	Prunes?
A	dt = '2026-08-18'	yes → 1 partition
B	CAST(dt AS DATE) = DATE '2026-08-18'	no → all partitions
C (fixed)	dt = '2026-08-18'	yes → 1 partition

Code.

-- A) Prunes cleanly — bare partition column
EXPLAIN
SELECT SUM(amount) FROM sales WHERE dt = '2026-08-18';
-- plan shows:  Partition:  1/731

-- B) Does NOT prune — function wraps the partition column
EXPLAIN
SELECT SUM(amount) FROM sales WHERE CAST(dt AS DATE) = DATE '2026-08-18';
-- plan shows:  Partition:  731/731   (full scan!)

-- C) Fix: compare against the partition column's native STRING form
SELECT SUM(amount) FROM sales WHERE dt = '2026-08-18';

-- Range pruning also works when the column is bare:
SELECT SUM(amount) FROM sales
WHERE  dt BETWEEN '2026-08-01' AND '2026-08-18';   -- prunes to 18 partitions

Step-by-step explanation.

1. Query A filters on the bare partition column dt. The compiler evaluates dt = '2026-08-18' against the metastore partition list, finds exactly one match, and the EXPLAIN plan shows Partition: 1/731 — only one directory will be read.
2. Query B wraps dt in CAST(... AS DATE). The compiler cannot statically evaluate a function over every partition value at plan time, so it conservatively keeps all partitions — the plan shows 731/731, a full-table scan disguised as a filtered query.
3. The fix in C is to phrase the predicate against the partition column's stored form (STRING) directly. Because dt is stored as the string '2026-08-18', a string equality prunes; a DATE cast does not.
4. Range predicates on the bare column also prune: dt BETWEEN '2026-08-01' AND '2026-08-18' lets the compiler select the 18 matching directories. The rule is not "equality only" — it is "no function wrapping the partition column."
5. Always confirm with EXPLAIN. The Partition: n/total line (or the list of partition paths in EXPLAIN EXTENDED) is the ground truth; never assume pruning happened because the query "looks filtered."

Output.

Query	Partitions read	Bytes scanned
A (dt = ...)	1 / 731	~500 MB
B (CAST(dt) = ...)	731 / 731	~360 GB
C (fixed)	1 / 731	~500 MB
C (range, 18 days)	18 / 731	~9 GB

Rule of thumb. Never wrap a partition column in a function inside WHERE. Filter on the bare column in its stored type, and prove pruning with EXPLAIN — the Partition: n/total line must show a small n. A "filtered" query that reads every partition is the most common silent Hive performance bug.

Senior interview question on partitioning

A senior interviewer might ask: "A table is partitioned by (dt, user_id) and has grown to 40 million partitions; planning now takes minutes and the metastore is under strain, yet analysts still complain queries are slow. Diagnose the partition-design mistake, propose a corrected layout, and describe how you would migrate without downtime and verify that pruning works afterwards."

Solution Using a corrected partition key plus bucketing for the high-cardinality dimension

-- 1. The mistake: user_id is high-cardinality → 40M partitions (one dir each)
--    Corrected design: partition ONLY on the low-cardinality date,
--    and BUCKET on user_id inside each partition.
CREATE TABLE sales_v2 (
    order_id     BIGINT,
    user_id      BIGINT,
    amount       DECIMAL(12,2),
    status       STRING
)
PARTITIONED BY (dt STRING)
CLUSTERED BY (user_id) INTO 64 BUCKETS   -- distribute user_id, no dir explosion
STORED AS ORC;

-- 2. No-downtime migration: backfill in date ranges into the new table
SET hive.exec.dynamic.partition = true;
SET hive.exec.dynamic.partition.mode = nonstrict;
SET hive.enforce.bucketing = true;

INSERT OVERWRITE TABLE sales_v2 PARTITION (dt)
SELECT order_id, user_id, amount, status, dt
FROM   sales
WHERE  dt BETWEEN '2026-01-01' AND '2026-08-18';   -- chunk the backfill

-- 3. Verify pruning + bucketing after cutover
EXPLAIN
SELECT SUM(amount) FROM sales_v2
WHERE  dt = '2026-08-18' AND user_id = 42;
-- expect: Partition: 1/N  (date pruned)  +  1 bucket scanned for user_id=42

Step-by-step trace.

Concern	Before	After
Partition column(s)	(dt, user_id)	(dt) only
Partition count	~40,000,000	~731 (days)
user_id access	one dir per user	one of 64 buckets
Planning time	minutes	sub-second
Pruning verified	never (too many parts)	EXPLAIN shows 1/N

The corrected table partitions only on the low-cardinality dt (bounded by the number of days) and moves the high-cardinality user_id into 64 hash buckets inside each partition. A query filtered on dt prunes to one day; a query also filtered on user_id reads a single bucket file instead of scanning the whole day. Partition count drops from 40 million to a few hundred, so planning and the metastore recover, while user_id lookups stay fast.

Output:

Metric	Before	After
Partitions	~40M	~731
Planning latency	minutes	< 1 s
dt = X AND user_id = Y scan	many small dirs	1 partition, 1 bucket
Metastore PARTITIONS rows	~40M	~731
Small-file pressure	severe	bounded (64 buckets/day)

Why this works — concept by concept:

• Partition on low cardinality — dt has a few hundred values, so the PARTITIONS table and the directory count stay small and planning stays fast. Partition count is bounded by the number of days, not by users.
• Bucketing the high-cardinality key — CLUSTERED BY (user_id) INTO 64 BUCKETS hashes user_id into a fixed 64 files per partition, so a user_id predicate reads one bucket instead of scanning everything, without creating a directory per user.
• Chunked backfill — migrating in date ranges keeps each statement's dynamic-partition count and memory bounded, and lets the old and new tables coexist so the cutover is zero-downtime (repoint readers, then drop the old table).
• EXPLAIN verification — the Partition: 1/N line plus a single-bucket read in the plan proves the new layout prunes and buckets as intended; you never trust the design without reading the plan.
• Cost — a one-time transcode of the table into the new layout, then every query is O(1 partition + 1 bucket) instead of O(40M partition listing). The eliminated cost is the per-query metastore storm from 40 million partitions. One-time O(N) rewrite, permanent O(scanned) queries.

Optimization
Topic — optimization
Partition-pruning and query-optimization problems

Practice →

Data transformation Topic — data-transformation Data transformation and partitioned-table problems

Practice →

---

4. ORC — columnar storage, predicate pushdown, and compression

ORC stores columns in stripes with per-stripe min/max statistics, so the reader skips whole stripes a predicate cannot match — this is why columnar beats text by an order of magnitude

The mental model in one line: ORC (Optimized Row Columnar) splits a file into large stripes (~64–256 MB), stores each column's values contiguously within a stripe as its own stream, and records lightweight statistics — min, max, count, sum — at the file level, the stripe level, and every 10,000-row row-index group — so that when a query has a predicate like amount > 1000, the reader consults the stats first and skips any stripe or row group whose max is ≤ 1000 without ever decompressing it, then reads only the column streams the query actually references. Columnar layout plus statistics plus predicate pushdown is why an ORC scan touches a tiny fraction of the bytes a text scan would.

The ORC file anatomy, top to bottom.

• Stripes. The unit of parallel work and skipping — a large horizontal slice of rows (default ~64 MB). Each stripe is self-contained: it has its own index, data, and footer.
• Column streams. Within a stripe, each column's values are stored contiguously (and separately from other columns). Reading amount touches only the amount streams — column projection is free.
• Row index. Every 10,000 rows within a stripe, ORC records min/max/count for each column. This is finer-grained than stripe-level stats and enables intra-stripe row-group skipping.
• Stripe footer + file footer. Hold the per-column statistics (min, max, sum, null count) at stripe and file scope. The file footer is read first so the reader knows every stripe's stats before touching data.
• Optional bloom filters. Per-column bloom filters (enabled via orc.bloom.filter.columns) give fast negative answers for equality predicates — "this stripe definitely does not contain user_id = 42" — which min/max cannot do for point lookups on unsorted data.

Predicate pushdown (SARGs) — how skipping actually fires.

• The predicate becomes a SARG. Hive converts a pushable WHERE predicate into a searchable argument handed to the ORC reader (hive.optimize.ppd = true, on by default).
• Three levels of skipping. The reader compares the SARG against file stats (skip the file), then stripe stats (skip the stripe), then row-index stats (skip the 10k-row group). Each level avoids decompressing the data below it.
• Sorting multiplies the benefit. If the data is sorted (or clustered) on the predicate column, min/max ranges per stripe are tight and non-overlapping, so skipping is dramatic. On randomly-ordered data, ranges overlap and skipping is weaker — which is where bloom filters help for equality.

Compression and vectorization.

• Codecs. orc.compress = ZLIB (default, best ratio), SNAPPY (fast, moderate ratio), ZSTD (modern, ratio close to ZLIB with speed near SNAPPY). ORC compresses per-stream, so it decompresses only the columns it reads.
• Lightweight encodings. Before generic compression, ORC applies run-length encoding, dictionary encoding for strings, and delta encoding for sorted integers — often the biggest space win.
• Vectorized reads. hive.vectorized.execution.enabled = true makes the engine process 1024-row column batches instead of row-at-a-time, which pairs naturally with columnar layout and slashes CPU per row.

Common interview probes on ORC.

• "Why is ORC faster than text?" — columnar projection + per-stripe min/max stats + predicate pushdown + per-stream compression, not just "columnar."
• "What is predicate pushdown?" — pushing the WHERE predicate into the reader so it skips stripes/row-groups via stats before decompressing.
• "When do bloom filters help?" — equality/point lookups on unsorted high-cardinality columns, where min/max ranges overlap.
• "ORC vs Parquet?" — both columnar with stats and pushdown; ORC has tighter Hive/ACID integration, Parquet is the broader ecosystem default. The concepts transfer.

Worked example — ORC DDL with stats, bloom filters, and pushdown enabled

Detailed explanation. The canonical high-performance ORC table: ZSTD compression, row indexes on, a bloom filter on the high-cardinality lookup column, and the session flags that make predicate pushdown and vectorization fire. Walk through every table property and why it is set.

• Compression. ZSTD for ratio-plus-speed.
• Row index. On, for intra-stripe skipping.
• Bloom filter. On customer_id for point lookups.
• Session flags. Pushdown + vectorization enabled.

Question. Create the ORC table and set the session so predicate pushdown and vectorized reads are active.

Input.

Property	Value
orc.compress	ZSTD
orc.create.index	true
orc.bloom.filter.columns	customer_id
orc.stripe.size	67108864 (64 MB)

Code.

CREATE TABLE orders_orc (
    order_id     BIGINT,
    customer_id  BIGINT,
    amount       DECIMAL(12,2),
    status       STRING,
    created_ts   TIMESTAMP
)
PARTITIONED BY (dt STRING)
STORED AS ORC
TBLPROPERTIES (
    'orc.compress'              = 'ZSTD',
    'orc.create.index'          = 'true',       -- row-index min/max every 10k rows
    'orc.bloom.filter.columns'  = 'customer_id',-- point-lookup skipping
    'orc.bloom.filter.fpp'      = '0.05',        -- 5% false-positive target
    'orc.stripe.size'           = '67108864'     -- 64 MB stripes
);

-- Session flags so pushdown + vectorization actually run
SET hive.optimize.ppd                  = true;   -- predicate pushdown (default on)
SET hive.optimize.index.filter         = true;   -- use ORC indexes for skipping
SET hive.vectorized.execution.enabled  = true;   -- 1024-row batches

Step-by-step explanation.

1. STORED AS ORC selects the ORC input/output format and OrcSerde. Everything else is tuning via TBLPROPERTIES, which are recorded in the metastore and read by the ORC writer at insert time.
2. orc.compress = ZSTD compresses each column stream independently. Because streams are per-column, reading amount decompresses only the amount stream — never the payload-style large columns the query ignores.
3. orc.create.index = true writes the row-index (min/max/count every 10,000 rows) inside each stripe. This is what enables intra-stripe skipping; without it, skipping is only at stripe granularity.
4. orc.bloom.filter.columns = customer_id adds a bloom filter for customer_id. For an equality predicate on an unsorted high-cardinality column, min/max ranges overlap across stripes and cannot skip; the bloom filter gives a fast "definitely not here" so those stripes are skipped anyway. fpp = 0.05 trades a little space for a 5% false-positive rate.
5. The session flags are the enabling switches: hive.optimize.ppd turns predicates into SARGs, hive.optimize.index.filter lets the reader use the row indexes, and vectorization processes column batches. All three must be on for the file-format investment to pay off at query time.

Output.

Table property	Recorded in	Runtime effect
orc.compress=ZSTD	metastore TBLPROPERTIES	per-stream decompression
orc.create.index=true	ORC stripe metadata	row-group skipping
orc.bloom.filter.columns	ORC stripe metadata	point-lookup skipping
hive.optimize.ppd=true	session	predicate → SARG

Rule of thumb. For any ORC table you query repeatedly: ZSTD (or SNAPPY for write-heavy), orc.create.index=true, a bloom filter only on the columns you do point lookups on, and confirm hive.optimize.ppd + hive.optimize.index.filter + vectorization are on. Bloom filters on every column waste space — add them surgically.

Worked example — predicate pushdown skipping stripes, proven

Detailed explanation. The payoff of ORC is watching a selective predicate read a fraction of the stripes. The way to see it is the ORC read statistics — how many stripes and row groups were selected versus skipped. Walk through a query where sorted data lets pushdown skip almost everything.

• Data. orders_orc for one day, sorted by amount, ~40 stripes.
• Predicate. amount > 100000 — matches only the top few stripes.
• Proof. Read stats show stripes selected vs skipped.

Question. Show how amount > 100000 skips stripes on amount-sorted ORC, and why the same predicate skips less on unsorted data.

Input.

Setup	Stripes	Predicate	Expected read
Sorted by amount	40	amount > 100000	~2 stripes
Unsorted	40	amount > 100000	most stripes (ranges overlap)

Code.

-- Write the day sorted by amount so per-stripe min/max are tight
SET hive.enforce.sorting = true;
INSERT OVERWRITE TABLE orders_orc PARTITION (dt = '2026-08-18')
SELECT order_id, customer_id, amount, status, created_ts
FROM   orders_raw
WHERE  dt = '2026-08-18'
ORDER  BY amount;                    -- sorted load → non-overlapping stripe ranges

-- The selective query; pushdown skips stripes whose max <= 100000
SELECT COUNT(*), SUM(amount)
FROM   orders_orc
WHERE  dt = '2026-08-18' AND amount > 100000;

-- Inspect ORC read stats (e.g. via the ORC tooling / query logs):
--   RECORDS_IN            = 40,000,000
--   RECORDS_READ          =    980,000   <- only matching row groups decoded
--   SELECTED_STRIPES      = 2 / 40

Step-by-step explanation.

1. The sorted load (ORDER BY amount) makes each stripe cover a contiguous, non-overlapping range of amount. Stripe 1 might hold amount in [0, 500], stripe 2 [500, 1200], and only the last couple of stripes hold values above 100,000.
2. At query time, hive.optimize.ppd turns amount > 100000 into a SARG. The ORC reader reads the file footer, checks each stripe's max(amount), and skips every stripe whose max is ≤ 100,000 — decompressing nothing in those stripes.
3. Within the surviving stripes, the row-index (every 10k rows) narrows further: row groups whose max ≤ 100,000 are skipped too, so RECORDS_READ is far below RECORDS_IN.
4. Column projection compounds it: only the amount (for the filter and sum) and the implicit count are read; status, created_ts, payload-style columns are never touched.
5. On unsorted data the same predicate skips far less: because rows are in random order, most stripes have a max(amount) above 100,000 even if only a handful of rows qualify, so their ranges overlap the threshold and cannot be skipped. Sorting on the predicate column is what makes pushdown dramatic.

Output.

Layout	Stripes read	Rows decoded	Relative work
Sorted by amount	2 / 40	~980k	~5%
Unsorted	~36 / 40	~38M	~95%
Text (no ORC)	n/a	40M (all cols)	100%+

Rule of thumb. Predicate pushdown skips on statistics, and statistics only help when ranges are tight — so sort (or cluster) ORC data on the column you filter on most. Pushdown on unsorted data still helps via column projection and vectorization, but stripe-skipping needs sorted or bucketed layout to shine.

Senior interview question on ORC

A senior interviewer might ask: "An analytics table is stored as gzip-compressed CSV and a common query filters on a date range and a status, aggregating one numeric column, but it reads the entire dataset every time. Redesign the storage as ORC to minimize bytes scanned, explain each ORC feature you rely on, and describe how you would verify the improvement rather than assume it."

Solution Using partitioned, sorted ORC with row indexes, pushdown, and vectorization

-- 1. Partitioned + sorted ORC replacing gzip CSV
CREATE TABLE events_orc (
    event_id     BIGINT,
    user_id      BIGINT,
    status       STRING,
    amount       DECIMAL(12,2),
    created_ts   TIMESTAMP
)
PARTITIONED BY (dt STRING)
STORED AS ORC
TBLPROPERTIES (
    'orc.compress'             = 'ZSTD',
    'orc.create.index'         = 'true',
    'orc.bloom.filter.columns' = 'status'   -- low-ish cardinality equality filter
);

-- 2. Load each partition sorted by the range/filter columns
SET hive.exec.dynamic.partition = true;
SET hive.exec.dynamic.partition.mode = nonstrict;

INSERT OVERWRITE TABLE events_orc PARTITION (dt)
SELECT event_id, user_id, status, amount, created_ts, dt
FROM   events_csv
DISTRIBUTE BY dt
SORT   BY status, amount;        -- tight per-stripe ranges for pushdown

-- 3. Enable pushdown + vectorization, then VERIFY with stats + EXPLAIN
SET hive.optimize.ppd          = true;
SET hive.optimize.index.filter = true;
SET hive.vectorized.execution.enabled = true;

ANALYZE TABLE events_orc PARTITION (dt) COMPUTE STATISTICS FOR COLUMNS;

EXPLAIN
SELECT status, SUM(amount)
FROM   events_orc
WHERE  dt BETWEEN '2026-08-01' AND '2026-08-18'
  AND  status = 'settled'
GROUP  BY status;   -- expect: partition pruning + SARG on status + vectorized

Step-by-step trace.

Feature	What it removes	Verified by
Partition by dt	out-of-range days	EXPLAIN Partition: 18/N
ORC column projection	unread columns (event_id, user_id, created_ts)	ORC read stats
Row index + stripe stats	non-matching row groups	SELECTED_STRIPES stat
Bloom filter on status	stripes lacking status='settled'	RECORDS_READ ≪ RECORDS_IN
Vectorization	per-row CPU overhead	query CPU time

The redesigned query prunes to the 18 in-range partitions, reads only the status and amount column streams, uses the bloom filter and stripe stats to skip stripes that cannot contain status='settled', and processes the survivors in 1024-row vectorized batches. Instead of decompressing the entire gzip CSV dataset and parsing every column of every row, it touches a small, provable fraction of the bytes — and the EXPLAIN plan plus the ORC read counters prove it rather than leaving it to faith.

Output:

Metric	gzip CSV	Partitioned sorted ORC
Partitions read	all	18 in range
Columns read	all 6	2 (status, amount)
Stripes decoded	n/a (whole file)	small selected subset
CPU model	row-at-a-time parse	vectorized batches
Verification	none	EXPLAIN + ORC read stats

Why this works — concept by concept:

• Columnar projection — ORC stores each column as its own stream, so the query reads only status and amount and never decompresses the columns it does not reference. CSV must parse every field of every line.
• Per-stripe + row-index statistics — min/max/count at stripe and 10k-row-group scope let the reader skip data that cannot match, before decompressing it. This is the mechanical basis of predicate pushdown.
• Sorted load — DISTRIBUTE BY dt SORT BY status, amount makes each stripe cover tight, non-overlapping ranges of the filter columns, which is what turns "pushdown is possible" into "pushdown skips most stripes."
• Bloom filter on status — for the equality predicate status = 'settled', the bloom filter gives fast negative answers on stripes that cannot contain that value, complementing min/max which is weak for equality.
• Cost — a one-time transcode from CSV to sorted ORC plus a stats pass; thereafter every query reads O(selected stripes × referenced columns) instead of O(whole dataset). The eliminated cost is repeated full-dataset decompression and full-row parsing. One-time O(N) write buys O(matched) reads forever.

Data processing
Topic — data-processing
Data processing and columnar file-format problems

Practice →

Optimization Topic — optimization Predicate-pushdown and scan-reduction optimization problems

Practice →

---

5. Bucketing, and Tez vs MapReduce execution engines

bucketing hash-distributes rows into a fixed number of files so joins skip the shuffle, and Tez runs the plan as one in-memory DAG instead of MapReduce's write-to-disk-between-stages chain

The mental model in one line: bucketing (CLUSTERED BY (col) INTO N BUCKETS) hashes each row's bucket column into one of N files per partition, so two tables bucketed the same way on their join key can be joined bucket-against-bucket without a shuffle (a sort-merge-bucket join), while the execution engine — MapReduce versus Tez — decides how the resulting plan runs: MapReduce materializes every intermediate result to HDFS between each map and reduce stage, whereas Tez executes the whole multi-stage plan as a single DAG with reused containers and in-memory edges, which is why the same HiveQL runs several times faster on Tez. Bucketing optimizes the data for joins; the engine optimizes the execution of the plan.

Bucketing — what it buys and how it differs from partitioning.

• Fixed file count, not directory count. INTO 64 BUCKETS makes exactly 64 files per partition, assigned by hash(bucket_col) % 64. Unlike partitioning, it does not create a directory per value, so it is safe for high-cardinality keys.
• Sort-merge-bucket (SMB) join. If both tables are bucketed on the join key with the same bucket count (and sorted within buckets), Hive joins bucket i of one table against bucket i of the other — no shuffle, low memory, streamed merge. This is the headline win.
• Bucket-map join. A weaker variant: if only one side is small enough, its matching buckets are loaded map-side. Still avoids a full shuffle.
• Sampling. TABLESAMPLE (BUCKET 1 OUT OF 64) reads one bucket — a cheap, reproducible sample for exploratory queries.
• The requirement. Bucketing pays off only when both join sides share the bucket column and count; mismatched bucketing gives no join benefit.

MapReduce vs Tez — the execution-engine trade-off.

• MapReduce. Each stage is an independent map or reduce job whose output is written to HDFS and re-read by the next stage. A query with 4 stages does 3 full HDFS round-trips of intermediate data. Robust, but the disk tax dominates multi-stage plans, and every job pays JVM startup.
• Tez. Compiles the whole query into one Directed Acyclic Graph of vertices connected by edges. Intermediate data flows through in-memory (or local-disk) edges between vertices instead of HDFS; containers are reused across vertices, killing per-stage JVM startup. Same logical plan, far less overhead.
• Container reuse + dynamic scheduling. Tez keeps YARN containers warm and reuses them for successive vertices, and can adjust parallelism at runtime — both impossible in stage-per-stage MapReduce.
• When Spark. Hive-on-Spark exists for shops standardized on Spark; it shares the metastore and gives similar DAG benefits. The decision is platform consistency, not raw speed.

The tuning knobs that matter on Tez.

• hive.execution.engine = tez. The switch itself.
• hive.tez.container.size. Memory (MB) per Tez task; too small causes spills/OOM, too large wastes the cluster.
• hive.auto.convert.join + hive.auto.convert.join.noconditionaltask.size. Auto-convert small-table joins into map-side broadcast joins so the shuffle disappears entirely.
• hive.vectorized.execution.enabled. Column-batch processing; pairs with ORC.
• hive.cbo.enable. Cost-based optimizer; uses metastore statistics to order joins and choose strategies.

Common interview probes on bucketing and engines.

• "Partitioning vs bucketing?" — partitioning = directory per value (pruning), bucketing = fixed hash files (join/sampling); use partitioning for low-cardinality filters, bucketing for high-cardinality join keys.
• "How does an SMB join avoid a shuffle?" — matching bucket counts on the join key mean bucket i joins bucket i directly.
• "Why is Tez faster than MapReduce?" — one in-memory DAG with container reuse vs a chain that writes to HDFS between every stage.
• "When would you still use MapReduce?" — essentially never for new work; legacy compatibility only.

Worked example — bucketed tables and a sort-merge-bucket join

Detailed explanation. The canonical bucketing win: two large tables joined on user_id, both bucketed the same way, so Hive performs a shuffle-free sort-merge-bucket join. Walk through the DDL, the load, and the settings that let SMB fire.

• Tables. orders and users, both CLUSTERED BY (user_id) INTO 64 BUCKETS and sorted by user_id.
• Join. orders JOIN users ON user_id.
• Result. Bucket i of orders merges with bucket i of users — no shuffle.

Question. Create two SMB-joinable tables and run the shuffle-free join.

Input.

Table	Bucketing	Sorted by
orders	64 buckets on user_id	user_id
users	64 buckets on user_id	user_id
Join key	user_id	(matching bucket spec)

Code.

CREATE TABLE orders (
    order_id BIGINT, user_id BIGINT, amount DECIMAL(12,2), status STRING
)
CLUSTERED BY (user_id) SORTED BY (user_id) INTO 64 BUCKETS
STORED AS ORC;

CREATE TABLE users (
    user_id BIGINT, name STRING, country STRING
)
CLUSTERED BY (user_id) SORTED BY (user_id) INTO 64 BUCKETS
STORED AS ORC;

-- Enforce bucketing on write so files land in the right bucket
SET hive.enforce.bucketing = true;
INSERT OVERWRITE TABLE orders SELECT order_id, user_id, amount, status FROM orders_raw;
INSERT OVERWRITE TABLE users  SELECT user_id, name, country FROM users_raw;

-- Enable sort-merge-bucket map join
SET hive.optimize.bucketmapjoin           = true;
SET hive.optimize.bucketmapjoin.sortedmerge = true;
SET hive.auto.convert.sortmerge.join      = true;
SET hive.execution.engine                 = tez;

SELECT o.status, COUNT(*), SUM(o.amount)
FROM   orders o
JOIN   users  u ON o.user_id = u.user_id
WHERE  u.country = 'US'
GROUP  BY o.status;

Step-by-step explanation.

1. Both tables are CLUSTERED BY (user_id) ... INTO 64 BUCKETS, so hash(user_id) % 64 assigns each row to a bucket file. Because the bucket column and count match, bucket i of orders contains exactly the same set of user_id hash values as bucket i of users.
2. hive.enforce.bucketing = true makes the writer create the correct number of reducers so each bucket file is written correctly. Without it, the physical files may not match the declared bucket count and the SMB join silently degrades to a shuffle join.
3. SORTED BY (user_id) inside each bucket means the join can be a streamed merge — walk both sorted bucket files in lockstep, matching on user_id, with O(1) memory per bucket instead of building a hash table.
4. The three bucketmapjoin/sortmerge flags tell the optimizer it may pair bucket i with bucket i and merge them, rather than shuffling all rows across the network by user_id. The shuffle — usually the most expensive and OOM-prone stage — disappears.
5. Running on Tez, the whole thing is one DAG: 64 parallel bucket merges feeding the group-by, with intermediate data flowing through in-memory edges. The combination (SMB join + Tez) is dramatically faster and more memory-stable than a shuffle join on MapReduce.

Output.

Strategy	Shuffle?	Memory profile	Relative speed
SMB join (matched buckets)	none	streamed merge, low	fastest
Bucket-map join	none (one side map-side)	small hash side	fast
Plain shuffle join	full network shuffle	large, OOM risk	slowest

Rule of thumb. Bucket both join sides on the join key with the same bucket count and SORTED BY that key, set hive.enforce.bucketing=true on write, and enable the bucketmapjoin/sortmerge flags. Mismatched bucket counts give zero join benefit — the counts must be identical (or integer multiples) for SMB to fire.

Worked example — the same query on MapReduce vs Tez

Detailed explanation. To make the engine difference concrete, take one multi-stage query (filter → join → group-by → order-by) and compare how MapReduce and Tez execute it. The plan is logically identical; the physical execution and the disk traffic are not. Walk through both.

• Query. A 4-stage plan: scan+filter, join, aggregate, sort.
• MapReduce. Each stage is a job writing intermediate output to HDFS.
• Tez. One DAG; intermediate data flows via in-memory edges.

Question. Run the same query on both engines and explain where the time goes.

Input.

Stage	Work	MapReduce artifact	Tez artifact
1	scan + filter	HDFS temp	DAG vertex
2	join	HDFS temp	in-memory edge
3	group-by	HDFS temp	in-memory edge
4	order-by	final output	final vertex

Code.

-- Same HiveQL for both engines
SELECT o.status, SUM(o.amount) AS revenue
FROM   orders o
JOIN   users  u ON o.user_id = u.user_id
WHERE  u.country = 'US' AND o.dt = '2026-08-18'
GROUP  BY o.status
ORDER  BY revenue DESC;

-- Run A: legacy MapReduce
SET hive.execution.engine = mr;
-- (4 stages → 3 intermediate HDFS write/read round-trips + per-job JVM startup)

-- Run B: Tez
SET hive.execution.engine            = tez;
SET hive.vectorized.execution.enabled = true;
SET hive.auto.convert.join           = true;
-- (1 DAG → in-memory edges between vertices, container reuse)

Step-by-step explanation.

1. On MapReduce, stage 1 (scan+filter) runs as a job whose output is written to a temporary HDFS location. Stage 2 (join) reads that back, runs, and writes its output to HDFS again. Every stage boundary is a full write-then-read of the intermediate dataset — 3 round-trips for a 4-stage plan.
2. Each MapReduce stage is a separate job with its own JVM/container startup and teardown. On a short query, this fixed overhead can exceed the actual compute time; on a long one it still adds meaningful latency.
3. On Tez, the compiler builds one DAG: a vertex per logical stage, edges connecting them. Intermediate data flows through the edges in memory (or spills to local disk under pressure) — no HDFS round-trip between stages.
4. Tez reuses containers across vertices, so the JVM-startup tax is paid roughly once, not per stage. It can also decide parallelism at runtime based on the data volume seen at the previous vertex — MapReduce fixes reducer counts up front.
5. The result is the same rows via the same logical plan, but Tez avoids the inter-stage HDFS traffic and repeated startup. For this 4-stage query the difference is typically 3–10× wall-clock, more on plans with many stages.

Output.

Aspect	MapReduce	Tez
Intermediate data	3× HDFS write+read	in-memory edges
Container lifecycle	per-stage startup	reused across vertices
Parallelism	fixed up front	dynamic at runtime
Typical wall-clock	baseline	3–10× faster

Rule of thumb. Default to hive.execution.engine=tez for every Hive query; the more stages a plan has, the larger the Tez win because MapReduce pays a full HDFS round-trip and a JVM startup at every stage boundary. Reserve mr for legacy compatibility you cannot avoid.

Senior interview question on bucketing and execution engines

A senior interviewer might ask: "Two 2-billion-row tables are joined on user_id in a nightly Hive job that runs on MapReduce, frequently OOMs on the join shuffle, and takes three hours. You may change the table layout and the engine. Design a layout and execution strategy that eliminates the shuffle and the OOMs and cuts the runtime, and explain how you would confirm the join strategy the optimizer actually chose."

Solution Using matched bucketing for an SMB join on Tez with CBO

-- 1. Bucket BOTH tables identically on the join key, sorted, as ORC
CREATE TABLE fact_orders (
    order_id BIGINT, user_id BIGINT, amount DECIMAL(12,2), status STRING
)
PARTITIONED BY (dt STRING)
CLUSTERED BY (user_id) SORTED BY (user_id) INTO 256 BUCKETS
STORED AS ORC TBLPROPERTIES ('orc.compress' = 'ZSTD');

CREATE TABLE dim_users (
    user_id BIGINT, name STRING, country STRING
)
CLUSTERED BY (user_id) SORTED BY (user_id) INTO 256 BUCKETS
STORED AS ORC;

SET hive.enforce.bucketing = true;   -- correct bucket files on write
-- (load both from raw with INSERT OVERWRITE ... , omitted for brevity)

-- 2. Enable SMB join + Tez + CBO + vectorization
SET hive.execution.engine                   = tez;
SET hive.optimize.bucketmapjoin             = true;
SET hive.optimize.bucketmapjoin.sortedmerge = true;
SET hive.auto.convert.sortmerge.join        = true;
SET hive.cbo.enable                         = true;
SET hive.vectorized.execution.enabled       = true;

ANALYZE TABLE fact_orders PARTITION (dt) COMPUTE STATISTICS FOR COLUMNS;
ANALYZE TABLE dim_users COMPUTE STATISTICS FOR COLUMNS;

-- 3. Confirm the chosen strategy in the plan
EXPLAIN
SELECT u.country, SUM(o.amount)
FROM   fact_orders o JOIN dim_users u ON o.user_id = u.user_id
WHERE  o.dt = '2026-08-18'
GROUP  BY u.country;   -- expect "Sorted Merge Bucket Map Join Operator", no Reduce shuffle

Step-by-step trace.

Problem	Cause	Fix
Join OOM	full shuffle of 2B rows by user_id	matched 256-bucket SMB join
3-hour runtime	MapReduce disk between stages	Tez in-memory DAG
Bad join order	no statistics	CBO + COMPUTE STATISTICS
Wrong strategy chosen silently	unverified plan	EXPLAIN shows SMB operator

Because both tables are bucketed identically (256 buckets, sorted on user_id), the join becomes 256 independent bucket merges — bucket i against bucket i — with no network shuffle, so the memory-hungry shuffle stage that caused the OOMs is gone. Running the DAG on Tez removes the inter-stage HDFS traffic, and CBO (fed by fresh statistics) confirms the sort-merge-bucket strategy and orders the join correctly. EXPLAIN shows a Sorted Merge Bucket Map Join Operator and no reduce-side shuffle, proving the intended plan.

Output:

Metric	Before (MR shuffle join)	After (Tez SMB join)
Join strategy	shuffle join	sort-merge-bucket join
Network shuffle	full 2B-row shuffle	none
OOM failures	frequent	eliminated
Runtime	~3 h	tens of minutes
Plan verified	no	EXPLAIN shows SMB operator

Why this works — concept by concept:

• Matched bucketing — identical CLUSTERED BY (user_id) INTO 256 BUCKETS on both tables means bucket i of each holds the same hash range of user_id, so the join runs bucket-against-bucket with no cross-network redistribution.
• Sort-merge join — SORTED BY (user_id) lets each bucket pair be merged by walking both sorted files in lockstep, using O(1) memory instead of building a hash table — this is what kills the OOMs.
• Tez DAG — the multi-stage plan runs as one DAG with in-memory edges and reused containers, removing MapReduce's write-to-HDFS-between-stages tax that dominated the 3-hour runtime.
• CBO + statistics — the cost-based optimizer, fed by COMPUTE STATISTICS, orders the join and confirms the SMB strategy instead of guessing; EXPLAIN makes the chosen operator visible so you verify rather than assume.
• Cost — a one-time rewrite of both tables into matched bucketed ORC plus a statistics pass; thereafter the join is O(bucket) parallel merges with no shuffle. The eliminated cost is the 2-billion-row network shuffle and its OOM retries. One-time O(N) rewrite, permanent shuffle-free joins.

Bucketing
Topic — bucketing
Bucketing and join-optimization problems

Practice →

Optimization
Topic — optimization
Execution-engine and shuffle-reduction problems

Practice →

---

Cheat sheet — Apache Hive recipes

• The four layers. Every Hive question resolves to one of four layers: the HiveQL compiler (does it prune/pushdown?), the Hive metastore (are there partitions/stats to use?), the storage layout (partitioned + bucketed + columnar?), and the execution engine (Tez, not MapReduce?). Diagnose top-down; the first three save I/O, the last saves overhead.
• Metastore backing-table lookups. TBLS (name, TBL_TYPE) → SDS (LOCATION, formats) → SERDES (slib) → PARTITIONS (one row per partition). SELECT tbl_type, location FROM tbls JOIN sds USING(sd_id) WHERE tbl_name='X'; answers "what/where/how/how-many" in milliseconds, even when Hive is unhealthy. A PARTITIONS count in the millions is the over-partitioning smoking gun.
• Managed vs external. Managed → DROP TABLE deletes files (Hive owns lifecycle, supports ACID); external → DROP TABLE keeps files (metadata only). Use EXTERNAL for lakes and shared data; add TBLPROPERTIES('external.table.purge'='false') as a belt-and-braces guard against accidental data loss.
• Partition DDL + dynamic insert. PARTITIONED BY (dt STRING); enable hive.exec.dynamic.partition=true, ...mode=nonstrict, cap with hive.exec.max.dynamic.partitions; put the partition column last in INSERT OVERWRITE ... PARTITION (dt) SELECT ..., dt. Partition on low-cardinality columns you always filter on (date); never on user_id/order_id.
• Prove pruning. EXPLAIN SELECT ... WHERE dt='...'; must show Partition: 1/N. Never wrap the partition column in a function (CAST(dt AS DATE), substr(dt,...)) — it defeats pruning and silently full-scans. Filter the bare column in its stored type; ranges (dt BETWEEN ...) prune too.
• ORC table properties. STORED AS ORC TBLPROPERTIES('orc.compress'='ZSTD','orc.create.index'='true','orc.bloom.filter.columns'='<point-lookup col>'). Enable hive.optimize.ppd=true, hive.optimize.index.filter=true, hive.vectorized.execution.enabled=true. Load sorted on the filter column (SORT BY) so per-stripe min/max ranges are tight and pushdown skips stripes.
• ORC skipping model. Predicate → SARG → skip at file stats → stripe stats → row-index (10k-row) group → decode only survivors → project only referenced columns. Bloom filters add equality/point-lookup skipping where min/max ranges overlap. Verify with ORC read stats (SELECTED_STRIPES, RECORDS_READ ≪ RECORDS_IN).
• Bucketing + SMB join. CLUSTERED BY (join_key) SORTED BY (join_key) INTO N BUCKETS on both tables with the same N; SET hive.enforce.bucketing=true on write; SET hive.optimize.bucketmapjoin=true, ...sortedmerge=true, hive.auto.convert.sortmerge.join=true. Bucket i joins bucket i — no shuffle, low memory. Mismatched bucket counts give zero benefit.
• Partitioning vs bucketing. Partitioning = one directory per value → pruning; use for low-cardinality filters (date). Bucketing = fixed hash files → shuffle-free joins + sampling; use for high-cardinality join keys (user_id). They compose: partition by dt, bucket by user_id inside each partition.
• Tez tuning knobs. hive.execution.engine=tez; hive.tez.container.size (MB per task — too small spills, too large wastes); hive.auto.convert.join=true (+ noconditionaltask.size) for map-side broadcast of small dims; hive.cbo.enable=true (needs ANALYZE ... COMPUTE STATISTICS); hive.vectorized.execution.enabled=true.
• Engine decision matrix. Batch ETL on YARN → Hive-on-Tez + ORC. Interactive/ad-hoc → Trino or Impala on the same metastore (not Hive). Spark-standardized shop → Hive-on-Spark or Spark SQL. MapReduce → legacy compatibility only; discouraged and removed in newer distros.
• Statistics discipline. ANALYZE TABLE t PARTITION (p) COMPUTE STATISTICS; and ... FOR COLUMNS; populate row counts and per-column min/max/NDV in the metastore. CBO, join-order, and map-join conversion all depend on these; no stats means the optimizer guesses. Re-run after large loads.

Frequently asked questions

What is Apache Hive in one sentence?

Apache Hive is a SQL layer over files in HDFS or object storage: it stores each table's schema, physical location, and serialization format in a relational Hive metastore (schema-on-read), compiles HiveQL into a job that prunes partitions and pushes predicates into columnar ORC readers, and runs that job on a pluggable execution engine — modern Tez (an in-memory DAG) or legacy MapReduce (a disk-materializing chain). Its lasting importance is less the query engine and more the metastore, which became the shared catalog that Spark, Trino, Presto, Impala, and Flink all read, so understanding Hive is understanding the metadata, layout, and execution model the whole lakehouse still inherits. Every senior data-engineering interview probes it because partitioning, ORC, and the engine choice are the levers behind most "why is this query slow" problems.

Partitioning vs bucketing — when do I use each?

Use partitioning for low-cardinality columns you frequently filter on — almost always a date. PARTITIONED BY (dt) creates one directory per value, and partition pruning lets the compiler skip entire directories at plan time before any I/O. Use bucketing for high-cardinality columns you join on — typically an id like user_id. CLUSTERED BY (user_id) INTO N BUCKETS hashes rows into a fixed number of files (no directory explosion), which enables shuffle-free sort-merge-bucket joins and reproducible sampling. The two compose: a well-designed fact table is partitioned by dt and bucketed by user_id inside each partition, so date filters prune directories and id joins skip the shuffle. The classic mistake is partitioning on a high-cardinality column, which creates millions of tiny directories and cripples the metastore — bucket that column instead.

Why is ORC faster than plain text or CSV?

ORC wins for four compounding reasons, not just "it's columnar." First, columns are stored in separate streams, so a query reads only the columns it references and never decodes the rest. Second, ORC records min/max/count statistics at the file, stripe, and every-10,000-row level, so predicate pushdown lets the reader skip whole stripes and row groups a WHERE clause cannot match — without decompressing them. Third, each column stream is compressed independently (ZLIB/SNAPPY/ZSTD) after lightweight run-length/dictionary/delta encoding, so both storage and read I/O shrink. Fourth, columnar layout pairs with vectorized execution (1024-row batches) to slash CPU per row. Text and CSV have none of this: every query decodes every field of every row. Sorting the ORC data on the filter column tightens per-stripe ranges and makes stripe-skipping dramatic.

Tez vs MapReduce vs Spark — which execution engine should I use?

Default to Tez for Hive batch workloads: it compiles the query into a single in-memory DAG with reused containers, so intermediate data flows through in-memory edges instead of being written to and re-read from HDFS between every stage the way MapReduce does. That inter-stage disk tax plus per-job JVM startup is exactly why the same HiveQL runs 3–10× faster on Tez, and MapReduce is deprecated (and removed in some distributions) for new work. Use Spark (Hive-on-Spark or migrate to Spark SQL) when your platform is already standardized on Spark — the win is one engine and one operational model, not raw speed, and it reads the same metastore. For genuinely interactive, sub-second dashboards, the senior answer is often "not Hive at all" — point Trino or Impala at the same Hive metastore, since those MPP engines are built for low-latency serving.

What exactly does the Hive metastore store?

The Hive metastore stores metadata about your tables, never the data itself. Concretely, in its backing relational database it keeps DBS (databases and their locations), TBLS (table names, owners, and whether each is MANAGED_TABLE or EXTERNAL_TABLE), COLUMNS_V2 (each table's columns and types — the schema-on-read contract), SDS (the storage descriptor: physical LOCATION, input/output formats), SERDES (the serialization class and its parameters), and PARTITIONS/PARTITION_KEYS (the list of partitions and their key values). It also holds table and column statistics that the cost-based optimizer relies on. Because all of this is exposed over a Thrift API, Spark, Trino, Presto, Impala, and Flink can resolve the same schema and locations — the metastore is the shared catalog, which is why it outlived Hive's own query engine in importance. If the metastore is down you cannot compile queries even though the data files are perfectly intact.

Managed vs external tables — which should I use?

Choose based on who owns the data lifecycle. A managed table (the default) means Hive owns the data: files live under the warehouse directory, and DROP TABLE deletes both the metastore entry and the files — and in modern Hive, managed ORC tables are the ones that support ACID/transactional operations. An external table (CREATE EXTERNAL TABLE) means Hive owns only the metadata: DROP TABLE removes the catalog entry but leaves the files untouched. Use external tables for shared data lakes, landing zones, and any dataset that other engines also read or write, so an accidental DROP can never destroy a terabyte of files; add TBLPROPERTIES('external.table.purge'='false') as an extra guard. Reserve managed tables for data Hive fully controls end to end, or when you specifically need Hive ACID semantics. The managed-vs-external confusion is the single most common cause of accidental data loss in a Hive shop.

Practice on PipeCode

• Drill the SQL practice library → for the HiveQL, DDL, partition, and join problems senior interviewers love.
• Rehearse on the data processing practice library → for columnar file formats, ORC-style predicate pushdown, and layout trade-offs.
• Sharpen the tuning axis with the optimization practice library → for partition pruning, scan reduction, and shuffle-free join strategies.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the four-layer diagnosis — compiler, metastore, storage, engine — against real graded inputs.

Lock in Apache Hive muscle memory

Docs explain features. PipeCode drills explain the decision — when partition pruning is silently defeated by a function, when ORC predicate pushdown actually skips a stripe, when bucketing turns a shuffle join into a merge, and when Tez earns its place over MapReduce. Pipecode.ai is Leetcode for Data Engineering — layout-first practice tuned for the production trade-offs senior data engineers actually face.

Practice SQL problems →
Practice data processing problems →