Apache Hive Tutorial: Learn Data Warehouse from Scratch (2026)
Apache Hive was my first encounter with SQL-on-Hadoop, and for years it was the only game in town for running analytical queries on petabyte-scale HDFS data. Hive translates SQL into MapReduce (or Tez or Spark) jobs, so you get parallel execution without writing Java. The Hive Metastore acts as a central schema repository that other tools like Spark SQL, Presto, and Impala also use.
This tutorial covers Hive from the perspective of someone building a production data warehouse: table design with partitioning and bucketing, file format choices, UDFs for custom logic, and transactional features.
DDL, DML, and the Metastore
Hive DDL is similar to SQL but with Hadoop-specific clauses. The Metastore stores schema metadata, partition locations, and table statistics in a relational database. Every query starts with a Metastore lookup to resolve table schemas. If the Metastore goes down, no new queries can start.
The Metastore Thrift API allows external services like Spark and Presto to read Hive tables. This is why Hive remains relevant: the schema ecosystem outlives the runtime.
CREATE TABLE sales (
order_id INT,
customer_id STRING,
amount DOUBLE,
order_date DATE
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE
LOCATION '/user/hive/warehouse/sales';Partitioning for Query Performance
Partitioning divides table data into subdirectories based on partition keys — commonly date, region, or department. A query with a partition filter reads only the relevant partitions, dramatically reducing I/O. Static partitioning lets you control the directory structure; dynamic partitioning creates partitions automatically.
Over-partitioning is a common mistake. I keep partition granularity at day level for most tables.
CREATE TABLE sales_partitioned (
order_id INT,
amount DOUBLE
)
PARTITIONED BY (year INT, month INT)
STORED AS PARQUET;
INSERT OVERWRITE TABLE sales_partitioned PARTITION(year, month)
SELECT order_id, amount, year(order_date), month(order_date) FROM raw_sales;Bucketing and Sampling
Bucketing divides partitions into fixed-size buckets based on a hash of one or more columns. CLUSTERED BY ... INTO 10 BUCKETS ensures all rows with the same bucket key colocate in the same file, enabling efficient map-side joins when both tables are bucketed on the join key. Bucketing also enables sampling.
I use bucketing for large dimension tables to improve join performance.
CREATE TABLE customer_bucketed (
customer_id INT,
name STRING,
segment STRING
)
CLUSTERED BY (customer_id) INTO 50 BUCKETS
STORED AS ORC;
SELECT * FROM customer_bucketed TABLESAMPLE(BUCKET 3 OUT OF 50 ON customer_id);ORC and Parquet File Formats
ORC (Optimized Row Columnar) is the best file format for Hive. It provides columnar storage, predicate pushdown with built-in indexes (min/max, bloom filters), and lightweight compression. A query reads only the relevant columns stripes from disk.
Parquet is the cross-platform alternative used by Spark and Impala. I use ORC for Hive-only pipelines and Parquet when multiple engines need the same data.
CREATE TABLE sales_orc (
order_id INT,
amount DOUBLE
)
STORED AS ORC
TBLPROPERTIES ('orc.compress'='SNAPPY', 'orc.bloom.filter.columns'='customer_id');
INSERT OVERWRITE TABLE sales_orc SELECT * FROM sales_text;UDFs and Transform Logic
Hive supports custom logic via UDFs (row-level functions), UDAFs (aggregate functions), and UDTFs (table-generating functions). Built-in functions cover most needs: regex extract, JSON parsing with get_json_object, and string manipulation. For complex logic, write a UDF in Java and register it with CREATE FUNCTION.
I have written UDFs for geolocation distance calculations and customer segmentation scoring.
ADD JAR hdfs:///udfs/geo-udf.jar;
CREATE TEMPORARY FUNCTION geo_distance AS 'com.example.GeoDistanceUDF';
SELECT geo_distance(lat1, lon1, lat2, lon2) AS distance_km FROM shipment;ACID Transactions and MERGE
Hive ACID enables row-level INSERT, UPDATE, DELETE, and MERGE on ORC tables. This makes Hive viable for ETL workloads that previously required databases. MERGE inserts new rows and updates existing ones in a single pass, useful for slowly changing dimension updates.
ACID tables require the hive_txn_manager to track open transactions and compaction threads to merge delta files.
CREATE TABLE customer_updates (
customer_id INT,
segment STRING
)
STORED AS ORC
TBLPROPERTIES ('transactional'='true');
MERGE INTO customer c
USING customer_updates u ON c.customer_id = u.customer_id
WHEN MATCHED THEN UPDATE SET segment = u.segment
WHEN NOT MATCHED THEN INSERT VALUES (u.customer_id, u.segment);Frequently Asked Questions
What is the difference between Hive and Spark SQL?
Hive translates SQL into Tez or MapReduce jobs. Spark SQL uses the Catalyst optimizer and runs on Spark DAG engine. Spark SQL is generally faster.
How does Hive handle schema evolution?
Hive supports schema-on-read evolution. You can add columns with ALTER TABLE. ORC and Parquet store column metadata in the file footer for forward compatibility.
What is the Metastore and why is it important?
The Hive Metastore is a central catalog of table schemas, partition locations, and statistics used by Hive, Spark SQL, Presto, and Impala.
Can Hive handle real-time queries?
Hive is designed for batch processing with latencies in seconds to minutes. For sub-second queries, use Impala or Presto.
Originally published on Ayodhyyya. Last updated June 1, 2026.