Hive Partitioning

Partitioning in Hive means dividing a big table into smaller logical pieces based on the values of one or more columns. Each piece is called a partition.

For example, Think of it like this:

• Without partitioning: You have one big box with all the data jumbled together.
• With partitioning: You have multiple smaller boxes, each labeled with a value (like month = ‘January’, month = ‘February’) so you can quickly pick the one you need.

Instead of searching the entire dataset, Hive can just look inside the relevant partition, making queries much faster and more efficient.

Why Do We Use Partitioning?

There are several good reasons why partitioning is important in Hive:

• Faster Queries: Hive doesn’t have to scan the whole table—it only scans the partition(s) relevant to the query.
• Better Organization: Partitioned tables keep data cleanly separated, which is especially helpful for time-series data (like daily logs or monthly sales).
• Improved Performance: Since less data is read, queries run faster and use fewer system resources.

Adding Partitions

Once you have a partitioned table, you often need to add partitions to it when new data arrives. This is called adding partitions. Adding a partition means:

• Creating a new folder in HDFS for the new partition value (e.g., month='August').
• Optionally, loading data into this new partition.

Syntax -

Syntax (Without Data Loading):

ALTER TABLE table_name 
ADD PARTITION (partition_column='value');

Examples -

Scenario1: Adding an Empty Partition

ALTER TABLE sales_data 
ADD PARTITION (month='August');

This adds a new partition for August, but no data is loaded yet.

Scenario2: Load Data into a Partition

LOAD DATA INPATH '/user/hive/warehouse/august_sales.csv' 
INTO TABLE sales_data PARTITION (month='August');

This loads sales data for August into the correct partition.

Drop Partitions

Over time, your Hive table may collect old or unnecessary partitions (for example, data from years ago that you no longer need). To keep your system clean and fast, you can drop partitions. Dropping a partition:

• Removes the partition metadata.
• Deletes the data folder in HDFS for that partition.

Syntax -

ALTER TABLE table_name 
DROP PARTITION (partition_column='value');

Examples -

Scenario1: Drop a Single Partition

ALTER TABLE sales_data 
DROP PARTITION (month='January');

This will delete the January partition and its data.

Scenario2: Drop Multiple Partitions at Once

ALTER TABLE sales_data 
DROP PARTITION (month='February'), PARTITION (month='March');

This deletes both February and March partitions together.

Types of Partitioning

Hive supports two main types of partitioning:

1. Static Partitioning - In static partitioning, you explicitly specify the partition value when inserting data into the table.
   
   Example:

   INSERT INTO TABLE sales_partition PARTITION(month='July') 
   SELECT order_id, customer, amount FROM sales_temp WHERE month = 'July';

   Here, you are manually specifying the partition (month='July') during the data load.
2. Dynamic Partitioning - In dynamic partitioning, Hive automatically determines the partition values based on the data being inserted.
   
   Example:

   INSERT INTO TABLE sales_partition PARTITION(month)
   SELECT order_id, customer, amount, month FROM sales_temp;

   Hive will create partitions dynamically for each unique value of the month column found in the data.

---