APACHE HIVE

The Complete One-Stop Study Guide

Architecture • Engines/LLAP • Internals • CBO/Tuning • ACID/SCD • War-Stories • Security •
Cloud • Interview Prep
Metastore • Tez/LLAP • Partitioning/Bucketing • ORC/Parquet Internals • Complex Types & Analytics • UDFs •
Joins & CBO • ACID/Compaction • SCD/MERGE • Troubleshooting • Ranger Security • Trino/Cloud Catalog •
FAANG/JPMC Interviews
Table of Contents
TOC \h \o "1-1"
0. How to Use This Guide + Structured Learning Path
A complete, interview-grade Apache Hive reference for a Data Engineer targeting JPMC / FAANG-level roles.
Study top-to-bottom; each part builds on the previous.

4-Week Roadmap
● Week 1 — Foundations: Parts 1–5. What/why Hive, architecture, the Metastore, query-execution
flow + engines (MR/Tez/LLAP), and the data model (managed vs external, views).
● Week 2 — Physical design: Parts 6–8. Partitioning, bucketing, and file-format internals (ORC/Parquet
stripes, indexes, bloom filters, compression, SerDe).
● Week 3 — Query power + performance: Parts 9–13. HiveQL (complex types, window functions,
cube/rollup), UDFs, joins & CBO optimization, the tuning playbook, and ACID/compaction.
● Week 4 — Production + interview: Parts 14–22. SCD/data modeling, troubleshooting war-stories,
debugging (EXPLAIN), security, Hive-vs-Spark/Trino + cloud, config, then drill scenarios +
FAANG/JPMC interview questions.

Mastery self-check
● You can trace a query end-to-end (parse → CBO → Tez DAG → HDFS/S3) and read an EXPLAIN plan.
● Given a slow query you can name the fix (partition prune, broadcast/SMB join, ORC + stats,
vectorization, skew handling).
● You can implement SCD-2 with MERGE and explain ACID base+delta compaction.
1. Fundamentals: What Hive Is & Why
Apache Hive is a data-warehouse system on Hadoop that lets you query data in HDFS/S3 with SQL (HiveQL)
instead of writing MapReduce/Spark code. It compiles SQL into distributed jobs.
● Purpose — bring SQL analysts to big data; abstract distributed-compute complexity. Built for batch
analytics / OLAP, not row-level OLTP.
● Schema-on-read — data sits as files; a schema is applied only at query time via a SerDe (unlike an
RDBMS's schema-on-write). Loading is cheap; a bad schema surfaces at read time.
● Not a database — no fast single-row lookups, heavyweight transactions, high per-query latency. Use
HBase for random access, Hive for large scans/aggregations.
● Origin — built at Facebook, donated to Apache. Runs on MapReduce, Tez, or Spark engines
(Tez/Spark far faster than MR).

Hive vs RDBMS (classic interview table)

● Schema: read-time (Hive) vs write-time (RDBMS). Data size: PB-scale vs GB–TB. Latency: minutes
(batch) vs milliseconds. Updates: limited ACID vs full OLTP. Indexes: min/max + bloom (ORC) vs B-
tree. Use: analytics/ELT vs transactional apps.
2. Hive Architecture
Components
● Clients — Beeline (JDBC CLI), JDBC/ODBC apps, Thrift; the old Hive CLI is deprecated.
● HiveServer2 (HS2) — service that accepts client connections (JDBC/ODBC/Thrift), handles
authentication + sessions, and runs queries. Beeline connects here.
● Driver — manages the query lifecycle (session, plan, fetch results).
● Compiler — parses HiveQL → AST → semantic analysis against the Metastore → logical plan (DAG of
stages).
● Optimizer — logical + Cost-Based Optimizer (Calcite): predicate pushdown, partition pruning, join
reorder, map-join conversion.
● Execution Engine — turns the optimized plan into MR/Tez/Spark jobs on YARN + HDFS.
● Metastore — relational DB (MySQL/Postgres) storing ALL metadata: databases, tables, columns,
types, partitions, locations, SerDe, statistics. The most important supporting piece.
3. The Metastore (Deep)
The Metastore maps logical tables → physical paths + schema. Accessed via the Metastore service (Thrift).

Deployment modes
● Embedded (Derby) — DB inside the Hive process; one session only. Testing.
● Local — Metastore in the Hive JVM, external DB (MySQL); multiple sessions.
● Remote — Metastore as a separate shared service; multiple clients (Hive, Spark, Presto/Trino) share
ONE catalog. Production standard.

Why it matters / tuning

● Shared catalog — the same Metastore backs Spark SQL, Trino, Impala → one source of truth for
tables/partitions. In cloud this becomes AWS Glue Data Catalog / Databricks Unity Catalog.
● Metastore is a common bottleneck — millions of partitions or high-concurrency DDL overload the
RDBMS. Fixes: connection pooling, `datanucleus` caching, direct SQL, partition limits, HA Metastore
behind a load balancer.
● Key tables in the backing DB: DBS, TBLS, COLUMNS_V2, PARTITIONS, SDS (storage descriptors),
SERDES, TABLE_PARAMS (stats).
4. Query Execution Flow & Engines
End-to-end flow
● 1) Beeline → HS2. 2) Driver → Compiler. 3) Parse → AST → semantic analysis (fetch table/partition
metadata from Metastore). 4) Logical plan → Optimizer (pruning, pushdown, join opt via Calcite
CBO). 5) Physical plan = MR/Tez/Spark jobs → submit to YARN. 6) Jobs read HDFS/S3 (locality),
process, write results. 7) Driver returns rows.

Execution engines (know the differences)

● MapReduce — legacy; writes to disk between every stage (slow). `[Link]=mr`.
● Tez — DAG engine: in-memory data passing between stages, container reuse, dynamic optimization
→ far faster than MR. Enterprise default. `set [Link]=tez`.
● Spark — Hive-on-Spark uses Spark as the engine (RDD/DAG in memory).
● LLAP (Live Long And Process) — persistent daemons that cache hot data + keep JVMs warm → sub-
second / interactive queries; great for BI dashboards.

Vectorization
● `[Link]=true` processes batches of ~1024 rows at once (columnar)
instead of row-by-row → big CPU win on ORC/Parquet.
5. Data Model: Tables, Views, Materialized Views
● Database (schema) — namespace/directory of tables. Table → an HDFS/S3 directory; rows = records
in files. Partition → subdirectory keyed by a column. Bucket → hashed files within a partition.

Managed vs External (critical!)

● Managed (internal) — Hive owns the data. `DROP TABLE` deletes metadata AND the files. Use when
Hive is the sole owner. (Also required for ACID.)
● External — Hive owns only metadata; data lives at a LOCATION. `DROP TABLE` removes only
metadata; files remain. Use for shared/raw data other tools read (the norm for S3 data lakes).

Views & Materialized Views

● View — a stored query (logical, no data); re-runs each time. Materialized View — precomputed +
stored results with automatic query rewrite (Hive rewrites eligible queries to use the MV) and
incremental refresh — big speedups for repeated aggregations.
CREATE TABLE sales (id INT, amount DOUBLE) STORED AS ORC; -- managed
CREATE EXTERNAL TABLE raw_sales (id INT, amount DOUBLE) -- external
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION 's3a://lake/raw/sales/';
CREATE TABLE orders (id INT, amt DOUBLE) -- part + bucket
PARTITIONED BY (country STRING)
CLUSTERED BY (id) INTO 8 BUCKETS STORED AS ORC;
CREATE MATERIALIZED VIEW mv_daily AS
SELECT country, SUM(amt) t FROM orders GROUP BY country;
6. Partitioning (Deep)
● Partition = directory per value (e.g., `/orders/country=IN/dt=2026-07-24/`) → partition pruning
scans only relevant folders. Use low-cardinality columns (date, country, region).

Static vs Dynamic
● Static — you name the partition on insert (`PARTITION(dt='2026-07-24')`). Fast, controlled.
● Dynamic — Hive derives values from data (`PARTITION(dt)`); needs
`[Link]=true` + mode `nonstrict`. Convenient but risks too many
partitions/small files.

Pruning + tuning
● Partition pruning — filter ON the partition column so only needed dirs are read (the single biggest
Hive speedup).
● Dynamic Partition Pruning (DPP) — at runtime, a join with a filtered dimension prunes fact
partitions (Tez feature).
● Anti-patterns: over-partitioning (thousands of tiny partitions → Metastore + small-files pain),
partitioning by high-cardinality columns (bucket those instead).
SET [Link]=true;
SET [Link]=nonstrict;
SET [Link]=10000;
INSERT OVERWRITE TABLE orders PARTITION(country)
SELECT id, amt, country FROM staging;
7. Bucketing (Deep)
● Bucketing splits data within a partition into a fixed number of files by `hash(col) % numBuckets` —
even distribution for high-cardinality keys.
● Benefits: efficient sampling (`TABLESAMPLE`), and fast bucket joins.

Bucket joins
● Bucket Map Join — both tables bucketed on the join key with matching bucket counts → each
bucket joined independently (no full shuffle).
● Sort-Merge Bucket (SMB) join — bucketed AND sorted on the key → merge join; extremely efficient
for very large × large joins.
● Combine with partitioning: partition by date (pruning), bucket by user_id (even distribution + joins).
8. File Formats & SerDe (Internals)
● TextFile — human-readable, no compression by default, slow. SequenceFile — binary key-value,
splittable.
● Avro — row-based + schema; best for schema evolution + ingestion.
● ORC — Hive's best columnar format. Parquet — cross-engine columnar standard (prefer when
sharing with Spark/Trino).

ORC internals (know this)

● File → stripes (~64–256 MB) → each stripe has an index, row data, and a footer. Stripe/file footers
hold min/max stats per column → predicate pushdown skips stripes.
● Row-index (every 10k rows) + optional bloom filters on chosen columns → skip data for
point/equality lookups. Lightweight compression (ZLIB/Snappy/Zstd) per column.

Parquet internals
● File → row groups → column chunks → pages; column stats + dictionary encoding + optional bloom
filters; predicate/projection pushdown at row-group/page level.

SerDe + compression
● SerDe (Serializer/Deserializer) applies schema-on-read (JSON SerDe, Regex, OpenCSV, Avro).
Compression: Snappy/Zstd (fast, splittable inside ORC/Parquet), ZLIB/Gzip (higher ratio). Columnar +
Snappy is the analytics default.
9. HiveQL: Complex Types, Analytics, Advanced SQL
Complex data types
● ARRAY, MAP, STRUCT (nested) — Hive handles semi-structured data. Explode arrays with LATERAL
VIEW explode(); parse JSON with `get_json_object` / `json_tuple` or a JSON SerDe.
-- explode an array column into rows
SELECT [Link], item
FROM orders o
LATERAL VIEW explode([Link]) t AS item;

-- parse JSON field

SELECT get_json_object(payload, '$.[Link]') AS cust_id FROM events;

Window / analytic functions

SELECT id, country, amount,
ROW_NUMBER() OVER (PARTITION BY country ORDER BY amount DESC) rn,
RANK() OVER (PARTITION BY country ORDER BY amount DESC) rnk,
SUM(amount) OVER (PARTITION BY country
ORDER BY amount ROWS UNBOUNDED PRECEDING) running,
LAG(amount) OVER (PARTITION BY country ORDER BY amount) prev
FROM orders;

Grouping sets / CUBE / ROLLUP (multi-level aggregation)

SELECT country, category, SUM(amount)
FROM orders
GROUP BY country, category WITH CUBE; -- all combinations
-- WITH ROLLUP = hierarchical subtotals; GROUPING SETS = pick specific groupings

CTEs & set operations

● WITH CTEs for readability; UNION/UNION ALL, INTERSECT, EXCEPT for set ops. Subqueries in
FROM/WHERE (correlated + uncorrelated).
10. Functions & UDFs
● Built-ins — string (`concat`, `regexp_replace`), date (`date_add`, `datediff`), math, conditional
(`CASE`, `COALESCE`, `nvl`), collection, and JSON functions.
● UDF (User-Defined Function) — 1 row in → 1 value out (e.g., custom formatting).
● UDAF (Aggregate) — many rows in → 1 value out (custom aggregation).
● UDTF (Table-Generating) — 1 row in → many rows out (e.g., `explode`; use with LATERAL VIEW).
● Register a JAR: `ADD JAR ...; CREATE TEMPORARY FUNCTION myfn AS '[Link]';`. Prefer built-
ins/vectorized paths — custom UDFs can block vectorization and slow queries.
11. Joins & Query Optimization
Join strategies
● Common (shuffle/reduce-side) join — default; both tables shuffled by key. Works for any size,
expensive.
● Map-side (broadcast/map join) — small table loaded into memory, joined on the map side (no
shuffle). Auto when a side < `[Link]`. Fastest big×small.
● Bucket Map Join / SMB join — bucketed (and sorted) tables joined per bucket; best big×large.
● Skew join (`[Link]=true`) — handles hot keys by processing skewed keys separately.

Optimization techniques
● Partition pruning — filter on the partition column (biggest win).
● Predicate & projection pushdown — push filters/column selection into ORC/Parquet readers → read
fewer stripes/columns.
● Cost-Based Optimizer (CBO / Calcite) — needs statistics (`ANALYZE TABLE ... COMPUTE STATISTICS
[FOR COLUMNS]`) to choose join order + algorithm + parallelism. Enable `[Link]=true`.
● Vectorization — batch row processing on ORC/Parquet.
● Tez/LLAP + ORC/Parquet + stats = the single biggest speedup over MR + TextFile.
● Avoid SELECT-all queries on columnar data — select only the needed columns to leverage column
pruning.
12. Performance Tuning Playbook
-- Engine + vectorization + CBO
SET [Link]=tez;
SET [Link]=true;
SET [Link]=true;
SET [Link]=true;

-- Map-join / broadcast threshold (bytes)

SET [Link]=true;
SET [Link]=209715200; -- 200MB

-- Parallelism / Tez containers

SET [Link]=true;
SET [Link]-size=134217728; -- control mapper input size
SET [Link]=268435456; -- ~256MB per reducer

-- Small-file mitigation on output

SET [Link]=true;
SET [Link]=134217728;
SET [Link]=268435456;

-- Dynamic partitions + skew

SET [Link]=nonstrict;
SET [Link]=true;

● Rules of thumb: partition-prune first; store ORC/Parquet + Snappy; keep stats fresh; broadcast small
dims; SMB for big joins; merge small output files; size reducers by bytes-per-reducer; enable
vectorization + CBO + LLAP for interactive.
13. ACID / Transactions & Compaction
● Hive supports INSERT/UPDATE/DELETE/MERGE only on transactional, bucketed(optional in newer),
ORC, managed tables with `TBLPROPERTIES('transactional'='true')`.

How it works
● Writes go to base + delta files (deltas hold inserts/updates/deletes). Reads merge base + deltas at
query time.
● Compaction consolidates files: minor (merge deltas into fewer deltas) and major (merge deltas into
a new base). Runs automatically or via `ALTER TABLE ... COMPACT 'major'`.
● Isolation — snapshot isolation via transaction/write IDs. Heavyweight + higher latency → designed
for slowly-changing data / CDC upserts, NOT high-frequency OLTP.

MERGE (upsert)
MERGE INTO dim_customer t
USING staging_updates s ON t.cust_id = s.cust_id
WHEN MATCHED THEN UPDATE SET name=[Link], email=[Link], updated=[Link]
WHEN NOT MATCHED THEN INSERT VALUES (s.cust_id, [Link], [Link], [Link]);
14. Data Modeling & Slowly Changing Dimensions
● Dimensional modeling — star schema (central fact + surrounding dims), snowflake (normalized
dims). Partition facts by date; keep dims small (broadcast-friendly).
● SCD Type 1 — overwrite (no history) → simple `MERGE ... UPDATE`.
● SCD Type 2 — keep history with `effective_date`, `end_date`, `is_current` flags: MERGE closes the
old row (`end_date`, `is_current=false`) and inserts a new current row.
● Incremental loads — process only new/changed data (by watermark/CDC) into partitioned tables;
use MERGE for upserts.
-- SCD2 pattern (close old, insert new)
MERGE INTO dim_customer_scd t
USING updates s ON t.cust_id=s.cust_id AND t.is_current=true
WHEN MATCHED AND [Link] <> [Link] THEN
UPDATE SET end_date=current_date(), is_current=false;
INSERT INTO dim_customer_scd
SELECT s.cust_id, [Link], current_date(), NULL, true, [Link] FROM updates s ...;
15. Troubleshooting & Real War-Stories
Symptom → diagnosis → fix — the 2-YOE differentiators.
● Query scans the whole 2 TB table despite a WHERE on country — table not partitioned by country,
or the filter isn't on the partition column → no pruning. Fix: partition by country / filter the partition
column; verify with EXPLAIN (partition list).
● New date folders copied to HDFS but queries return nothing — Metastore doesn't know the
partitions. Fix: `MSCK REPAIR TABLE t` (or `ALTER TABLE ADD PARTITION`).
● OOM on a 'small-table' map join — the broadcast side is actually large. Fix: lower/disable auto-
convert threshold, or switch to SMB/bucket join for big×big.
● Reduce-side skew (one reducer runs forever) — hot join/group key. Fix:
`[Link]=true`, salt the key, or map-side join if a side is small.
● Dynamic partition insert fails: 'too many dynamic partitions' — exceeded
`[Link]`. Fix: reduce partition-column cardinality or raise the limit
(carefully — Metastore load + small files).
● Small-files explosion after many inserts — each insert wrote tiny files → slow reads + Metastore/NN
pressure. Fix: enable `[Link].*`, `INSERT OVERWRITE` to compact, use fewer reducers, or run
compaction (ACID).
● Metastore slow / DDL timeouts — millions of partitions or Derby single-session. Fix: remote
MySQL/Postgres Metastore, connection pooling, partition limits, direct-SQL, HA Metastore.
● Wrong results / stale data after DROP on an external table — DROP kept the files; recreating points
at stale data. Fix: understand managed vs external semantics; clean the LOCATION if intended.
● MapRedTask return code 2 / vertex failed — underlying job failed (bad data or OOM). Fix: `yarn logs
-applicationId`, check the Tez/Spark vertex, raise container memory, inspect malformed rows.
● **Slow because of TextFile + `SELECT *` + no stats — no columnar pruning or CBO. Fix:** convert to
ORC/Parquet, select needed columns, run ANALYZE, enable vectorization + CBO.
16. Debugging Methodology (Read the Plan)
● `EXPLAIN <query>` — the logical/physical plan: check for partition pruning (which partitions are
read), join type (Map Join vs Merge Join), and pushed predicates.
● `EXPLAIN ANALYZE` / `EXPLAIN VECTORIZATION` — actual vs estimated rows, and whether
operators are vectorized.
● Tez UI / Spark UI — per-vertex/stage timings, data read/shuffled, skew (one task much slower).
● Stats — `DESCRIBE FORMATTED t` shows row count/size; if 'null'/stale, `ANALYZE TABLE t COMPUTE
STATISTICS [FOR COLUMNS]` so the CBO can plan.
● Method: EXPLAIN → is it pruning? right join type? → check stats → look at the slow vertex/task in
the UI → fix (prune/broadcast/SMB/skew/format) → re-measure.
17. Security & Governance
● Authentication — Kerberos on secure clusters; HS2 supports LDAP/PAM/custom. Delegation tokens
for jobs.
● Authorization models: (1) Storage-based (HDFS perms), (2) SQL Standard (GRANT/REVOKE on
tables/columns), (3) Apache Ranger — centralized policies with column masking, row-level filtering,
tag-based access, and full audit. Ranger is the enterprise/JPMC standard.
● Encryption — HDFS TDE (encryption zones + KMS) at rest; TLS in transit.
● Lineage/governance — Apache Atlas (metadata + lineage); pairs with Ranger for regulated
environments.
18. Hive vs Spark SQL vs Trino + Cloud Context
When to use which
● Hive (Tez/LLAP) — heavy batch ELT + warehouse SQL; ACID/MERGE; mature governance.
● Spark SQL — unified batch + streaming + ML; programmatic pipelines; shares the same
Metastore/catalog.
● Trino/Presto — fast interactive federated queries across many sources (S3, RDBMS, Kafka); no ETL
storage of its own.
● They coexist via a shared Metastore/catalog — same tables, different engines.

Cloud context (must-know)

● External tables on S3/ADLS/GCS are the norm in cloud lakehouses; Hive/Spark/Trino read the same
files.
● Catalog: the Hive Metastore role is played by AWS Glue Data Catalog, Databricks Unity Catalog, or a
managed HMS — one catalog many engines share.
● Object-store gotchas: non-atomic renames (commit protocols / `_SUCCESS`), no true data locality →
rely on columnar formats + pushdown + caching (LLAP/Photon).
19. Configuration Reference
# Engine + optimization
[Link]=tez
[Link]=true
[Link]=true
[Link]=true

# Joins
[Link]=true
[Link]=209715200 # 200MB
[Link]=true
[Link]=true

# Partitions
[Link]=true
[Link]=nonstrict
[Link]=10000

# Small files / reducers

[Link]=true
[Link]=134217728
[Link]=268435456

# ACID
[Link]=true
[Link]=[Link]
20. HiveQL Cheat Sheet
USE mydb; SHOW TABLES; DESCRIBE FORMATTED orders;
MSCK REPAIR TABLE orders; -- discover HDFS partitions
ANALYZE TABLE orders COMPUTE STATISTICS FOR COLUMNS; -- feed CBO
SHOW PARTITIONS orders;

-- Dynamic-partition insert
INSERT OVERWRITE TABLE orders PARTITION(country)
SELECT id, amt, country FROM staging;

-- Sampling a bucketed table

SELECT * FROM orders TABLESAMPLE(BUCKET 1 OUT OF 8 ON id);

-- Explode + JSON
SELECT id, item FROM t LATERAL VIEW explode(items) x AS item;
SELECT get_json_object(payload,'$.id') FROM events;

-- Compaction (ACID)
ALTER TABLE txn_tbl COMPACT 'major';
21. Scenario-Based Questions
Q1. You DROP a table and the HDFS files vanish. What kind of table?
→ Managed (internal) — Hive owns the data, so DROP deletes metadata AND files. External would keep
the files.
Q2. You copied new date-partition folders but Hive returns nothing for them. Fix?
→ The Metastore doesn't know them — run MSCK REPAIR TABLE (or ALTER TABLE ADD PARTITION).
Q3. A query on a 2 TB table filtering country='IN' still scans everything. Why?
→ Not partitioned by country, or the filter isn't on the partition column → no pruning. Partition/filter on
country; confirm via EXPLAIN.
Q4. Huge fact × tiny lookup is slow with full shuffle. Improve it.
→ Enable a map-side (broadcast) join so the small table is held in memory; ensure it's under the auto-
convert threshold.
Q5. Two very large tables must be joined efficiently. Best approach?
→ Bucket both on the join key with matching bucket counts and sort them → SMB join (no full shuffle).
Q6. Only one Hive session works at a time. Cause?
→ Embedded Derby Metastore (single session). Move to a remote MySQL/Postgres Metastore.
Q7. Why ORC over TextFile for a large analytics table?
→ Columnar + compression + stripe min/max stats + row indexes/bloom filters → predicate/column
pushdown → far less I/O than row-based TextFile.
Q8. A wrong column type — when do you find out (schema-on-read)?
→ At query/read time via the SerDe; loading succeeded regardless. Mismatch surfaces on deserialization.
Q9. Reducer runs forever on a skewed join key. Fixes?
→ [Link]=true, salt the hot key, or broadcast a small side; verify skew in the Tez UI.
Q10. You need SCD-2 history for a customer dimension. Approach?
→ Transactional ORC table + MERGE: close the old current row (end_date, is_current=false) and insert a
new current row when the hash changes.
Q11. Repeated dashboard queries aggregate the same data slowly. Speed up.
→ Create a Materialized View with automatic query rewrite (and LLAP for interactivity).
22. FAANG / JPMC-Level Interview Questions
1. Trace a Hive query end-to-end.
A: Beeline→HS2→Driver→Compiler (parse+semantic analysis via Metastore)→logical plan→CBO/Calcite
optimizer (pruning/pushdown/join reorder)→physical plan of Tez/Spark jobs→YARN→read
HDFS/S3→write results→Driver returns rows.
2. Managed vs external tables — when each, and the S3 implication?
A: Managed: Hive owns lifecycle; DROP deletes data (sole-owner case). External: metadata only, files
persist (shared/raw data, the norm for S3 lakes where many engines read the same files).
3. Why is a remote Metastore critical, and how does it become the cloud catalog?
A: It's a shared, concurrent catalog (no Derby single-session) that Hive/Spark/Trino all use. In cloud its role
is played by AWS Glue Data Catalog / Databricks Unity Catalog / managed HMS — one catalog, many
engines.
4. Partitioning vs bucketing — how, when, and combine?
A: Partition: directories on a low-cardinality column for pruning. Bucket: hash a high-cardinality column
into fixed files for even distribution, sampling, and bucket/SMB joins. Combine: partition by date, bucket
by user_id.
5. Explain ORC internals and how they enable predicate pushdown.
A: File→stripes→(index, row data, footer). Stripe/file footers hold per-column min/max stats; row indexes
(every 10k rows) + optional bloom filters let the reader skip stripes/rows that can't match a predicate →
far less I/O.
6. How does the CBO improve plans, and what does it need?
A: Calcite CBO uses table + column statistics (ANALYZE) to choose join order, join algorithm (broadcast vs
shuffle vs SMB), and parallelism. Without fresh stats it falls back to rule-based plans that are often poor.
7. Compare map-join, bucket-map-join, SMB, and skew join.
A: Map-join: small side broadcast in memory (big×small, no shuffle). Bucket-map-join: matching-bucketed
tables joined per bucket. SMB: bucketed+sorted → merge join (big×large). Skew join: hot keys processed
separately to avoid a single overloaded reducer.
8. How does Hive ACID work, and when is it appropriate?
A: Transactional ORC managed tables use base+delta files with snapshot isolation via write IDs;
minor/major compaction consolidates deltas. Appropriate for slowly-changing data / CDC upserts
(MERGE), not high-frequency OLTP (heavyweight, higher latency).
9. Implement SCD-2 in Hive and explain the mechanics.
A: On a transactional table, MERGE closes the current row (set end_date, is_current=false) when the
record's hash changed, then insert the new version as current (start_date, is_current=true). Preserves full
history with effective dating.
10. Diagnose a Hive query that suddenly got 5× slower. Playbook.
A: EXPLAIN → confirm partition pruning + correct join type; DESCRIBE FORMATTED → check stats freshness
(ANALYZE); Tez/Spark UI → find the slow vertex (skew/spill/shuffle); check for new small files / data
growth; apply the targeted fix (prune/broadcast/SMB/skew/ORC+stats/vectorize) and re-measure.
11. Why is Hive-on-Tez/LLAP faster than Hive-on-MapReduce?
A: Tez uses a DAG with in-memory data passing, container reuse, and runtime optimization (no disk write
between every stage); LLAP adds persistent daemons + data caching + warm JVMs for sub-second
interactive queries — eliminating MR's disk I/O and job-startup overhead.
12. How would you secure a multi-tenant Hive warehouse for a bank?
 A: Kerberos auth; Apache Ranger for fine-grained authorization with column masking + row-level filtering +
audit; HDFS TDE (KMS) at rest + TLS in transit; per-team databases/queues; Atlas for lineage — end-to-end
governance.
13. Hive vs Spark SQL vs Trino — how do you choose in a modern stack?
A: Hive/Tez-LLAP for heavy batch ELT + warehouse SQL + ACID; Spark SQL for unified batch/streaming/ML
pipelines; Trino for fast interactive federated queries across sources. They coexist on a shared
Metastore/catalog — same tables, different engines.
Appendix: Quick Reference & Mastery Checklist
Golden rules
● ORC/Parquet + Tez/LLAP + partition pruning + fresh stats + vectorization = fast Hive.
● Managed DROP deletes data; External DROP keeps files. New HDFS partitions need MSCK REPAIR.
● Broadcast small dims; SMB for big joins; skew-join for hot keys; merge small output files.
● ACID = base+delta+compaction on transactional ORC; use MERGE for SCD/upserts.
● Remote Metastore = shared catalog (→ Glue/Unity in cloud).

Mastery checklist
● Read an EXPLAIN plan and confirm pruning + join type.
● Choose the right join (map/bucket/SMB/skew) for a given size pair.
● Explain ORC stripes/indexes/bloom filters and predicate pushdown.
● Write SCD-2 with MERGE and explain compaction.
● Secure + govern a multi-tenant warehouse (Ranger/Kerberos/TDE).
● Map Hive concepts to the cloud lakehouse (external tables + Glue/Unity catalog).