Partitioning in Hive
The partitioning in Hive means dividing the table into some parts based on the values of a particular column like date, course, city or country. The advantage of partitioning is that since the data is stored in slices, the query response time becomes faster.
As we know that Hadoop is used to handle the huge amount of data, it is always required to use the best approach to deal with it. The partitioning in Hive is the best example of it.
Let's assume we have a data of 10 million students studying in an institute. Now, we have to fetch the students of a particular course. If we use a traditional approach, we have to go through the entire data. This leads to performance degradation. In such a case, we can adopt the better approach i.e., partitioning in Hive and divide the data among the different datasets based on particular columns.
The partitioning in Hive can be executed in two ways -
Static partitioning
Dynamic partitioning
Static Partitioning
In static or manual partitioning, it is required to pass the values of partitioned columns manually while loading the data into the table. Hence, the data file doesn't contain the partitioned columns.
Example of Static Partitioning
First, select the database in which we want to create a table.
hive> use test;
Create the table and provide the partitioned columns by using the following command: -
hive> create table student (id int, name string, age int, institute string)
partitioned by (course string)
row format delimited
fields terminated by ',';
Partitioning in Hive
Let's retrieve the information associated with the table.
hive> describe student;
Partitioning in Hive
Load the data into the table and pass the values of partition columns with it by using the following command: -
hive> load data local inpath '/home/codegyani/hive/student_details1' into table student
partition(course= "java");
Partitioning in Hive
Here, we are partitioning the students of an institute based on courses.
Load the data of another file into the same table and pass the values of partition columns with it by using the following command: -
hive> load data local inpath '/home/codegyani/hive/student_details2' into table student
partition(course= "hadoop");
Partitioning in Hive
In the following screenshot, we can see that the table student is divided into two categories.
Partitioning in Hive
Let's retrieve the entire data of the able by using the following command: -
hive> select * from student;
Partitioning in Hive
Now, try to retrieve the data based on partitioned columns by using the following command: -
hive> select * from student where course="java";
Partitioning in Hive
In this case, we are not examining the entire data. Hence, this approach improves query response time.
Let's also retrieve the data of another partitioned dataset by using the following command: -
hive> select * from student where course= "hadoop";
