Hive Partitions & Buckets with Example

Tables, Partitions, and Buckets are the parts of Hive data modeling. A table defines the schema, a partition splits that table into directories on disk, and a bucket splits the data inside a directory into a fixed number of files.

What is Partitions?

Hive Partitions is a way to organizes tables into partitions by dividing tables into different parts based on partition keys. Physically, every partition is a separate subdirectory under the table folder in HDFS, which is what allows Hive to skip data it does not need.

Partition is helpful when the table has one or more Partition keys. Partition keys are basic elements for determining how the data is stored in the table. A partition key is not stored inside the data files themselves – its value is encoded in the directory name, so a query filtering on that key can discard whole directories before any row is read. This is called partition pruning.

For Example: –

“Client having Some E–commerce data which belongs to India operations in which each state (38 states) operations mentioned in as a whole. If we take state column as partition key and perform partitions on that India data as a whole, we can able to get Number of partitions (38 partitions) which is equal to number of states (38) present in India. Such that each state data can be viewed separately in partitions tables.”

Sample Code Snippet for partitions

The six statements below run in order in the Hive shell. Each one is a separate step, and the screenshots that follow show the same sequence executing on a live cluster.

1. Creation of table allstates

   create table allstates(state string, District string,Enrolments string)
   
   row format delimited
   
   fields terminated by ',';
   

2. Loading data into created table allstates

   Load data local inpath '/home/hduser/Desktop/AllStates.csv' into table allstates;

3. Creation of partition table

   create table state_part(District string,Enrolments string) PARTITIONED BY(state string);

4. For partition we have to set this property

   set hive.exec.dynamic.partition.mode=nonstrict

5. Loading data into partition table

   INSERT OVERWRITE TABLE state_part PARTITION(state)
   SELECT district,enrolments,state from  allstates;

6. Actual processing and formation of partition tables based on state as partition key

There are going to be 38 partition outputs in HDFS storage with the file name as state name. We will check this in this step.

The following screen shots show the execution of the above mentioned code.

In the first screen, step 1 runs at the hive> prompt and creates the allstates table with its three columns and a comma as the field delimiter.

The next screen covers steps 2 and 3: the AllStates.csv file is loaded into allstates, and the partitioned table state_part is created with state declared as the partition key.

Steps 4 and 5 appear next. Dynamic partition mode is set to nonstrict, and the INSERT OVERWRITE statement launches a MapReduce job whose log lines show one partition being loaded for each state value.

Listing the warehouse directory in HDFS confirms the result of step 6 – the shell reports 38 items, one state_part/state= directory per state.

From the above code, we do following things

1. Creation of table allstates with 3 column names such as state, district, and enrolments
2. Loading data into table allstates
3. Creation of partition table with state as partition key
4. In this step setting partition mode as non-strict (this mode will activate dynamic partition mode)
5. Loading data into partition table state_part
6. Actual processing and formation of partition tables based on state as partition key
7. There are going to be 38 partition outputs in HDFS storage with the file name as state name. In this step, we see the 38 partition outputs in HDFS

Static vs Dynamic Partitioning in Hive

The worked example above uses dynamic partitioning, but Hive supports two loading styles and the difference decides how much typing – and how much risk – each load carries.

Static partitioning names the partition value in the statement itself, so the value must be known before the load runs and one statement fills exactly one partition. Dynamic partitioning lets Hive read the partition value out of the last column of the SELECT list and create the directories at runtime, which is why the example needs only a single INSERT to produce 38 directories.

Aspect	Static partitioning	Dynamic partitioning
Partition value	Supplied by hand in the PARTITION clause	Read at runtime from the SELECT column
Partitions per statement	One	Many
Configuration	Works in the default strict mode	Needs hive.exec.dynamic.partition.mode set to nonstrict
Load speed	Faster, because no value scan is needed	Slower, because the job groups rows by key
Best suited to	Small, known sets such as a daily load	Large or unknown key sets such as 38 states

Dynamic loads are also capped. Hive limits how many partitions one job may create – by default 100 per mapper or reducer and 1000 for the whole statement – and the job fails once either ceiling is crossed, so a very high-cardinality key needs those limits raised or a different design.

What is Buckets?

Buckets in Hive are used in segregating of Hive table-data into multiple files or directories. They are used for efficient querying, and unlike a partition the number of buckets is fixed when the table is created, so it never grows with the data.

• The data that is present in those partitions can be divided further into buckets
• The division is performed based on hash of particular columns that we selected in the table
• Buckets use some form of hashing algorithm at back end to read each record and place it into buckets
• The bucket a row lands in is decided by hash_function(bucketing_column) mod num_buckets, so equal values always land in the same file
• On Hive 0.x and 1.x, bucketing had to be enabled with set hive.enforce.bucketing=true; before the insert

That last setting is history on a current cluster: the Apache Hive manual notes it is not needed from Hive 2.x onward, because the engine now picks the reducer count and cluster-by column from the table definition automatically.

Step 1) Creating Bucket as shown below.

The screen below shows the CREATE TABLE statement for samplebucket, with the CLUSTERED BY clause at the bottom fixing the bucket count.

From the above screen shot

• We are creating samplebucket with column names such as first_name, job_id, department, salary and country
• We are creating 4 buckets over here
• Once the data gets loaded it automatically places the data into 4 buckets
• The country column is the clustering column, so every row for one country is written to the same bucket file

Step 2) Loading data into table samplebucket

Assuming that the “employees” table is already created in the Hive system, in this step we will see the loading of data from the employees table into the table samplebucket.

Before we start moving employees data into buckets, make sure that it consists of column names such as first_name, job_id, department, salary and country.

Here we are loading data into samplebucket from the employees table – the screen below shows the INSERT OVERWRITE statement that performs the copy.

Step 3) Displaying the 4 buckets created in Step 1

Listing the table directory in HDFS shows the physical result: four numbered data files rather than one.

From the above screenshot, we can see that the data from the employees table is transferred into the 4 buckets created in step 1.

Hive Partitioning vs Bucketing: Key Differences

Both features cut a table into smaller pieces, but they do it at different levels of the file system and they answer different problems. The table below sets them side by side.

Point of comparison	Partitioning	Bucketing
Unit created	A directory per key value	A file per hash bucket
Declared with	PARTITIONED BY	CLUSTERED BY … INTO n BUCKETS
Number of pieces	Grows with the number of distinct values	Fixed at table creation
Column stored in data files	No – the value lives in the directory name	Yes – the column stays a normal column
Best column type	Low cardinality, such as state, year or country	High cardinality, such as user_id or transaction_id
Main benefit	Partition pruning skips unneeded directories	Even file sizes, cheap sampling and map-side joins

The two are not rivals. A common production layout partitions a fact table by date and then buckets each day on the join key, so a query prunes to one directory and then joins bucket-to-bucket inside it.

When to Use Partitioning, Bucketing, or Both

Choosing between them starts with the cardinality of the column and the shape of the queries that will read the table.

• Partition when queries almost always filter on the same low-cardinality column, and when the number of distinct values stays in the hundreds rather than the millions
• Bucket when the useful column has too many distinct values to be a directory, or when the table is joined or sampled repeatedly on that column
• Use both for large fact tables: partition on the date, then bucket inside each partition on the join key

The failure mode to watch for is the small-files problem. Partitioning on a column with very high cardinality – a timestamp or a customer identifier – produces thousands of tiny directories, each holding a file far below the HDFS block size. That inflates NameNode memory and makes every scan slower, which is exactly the outcome partitioning was meant to prevent. Bucketing avoids this because the file count is capped by the table definition.

Bucketing has its own caveat: the layout is only correct if every writer honours it. The bucket count declared at creation is metadata, so a job that writes to the table without clustering correctly can leave files that do not match the declared layout, and later sampling or bucket joins will read the wrong rows.

FAQs