Partitioning in Hive

Updated On August 11, 2020 | By Mahesh Mogal

IN Big Data Systems, we deal with GBs, TBs, or even Petabytes of data. When querying such huge datasets, we need to organize data in such ways that we can query and analyze data efficiently. This is where Data Partitions come into the picture. The concept of partition is not new but Hive uses them to keep data in different folders and use them intelligently while querying data. Let us understand more about Partitioning.

What is Partitioning and why to use it?

Before we learn how to create partitioned tables in Hive, let us see why should we use partitioning in Hive and what benefits it brings us.

Consider that we have data for the E-Commerce website with a huge number of orders (Amazon, eBay, etc). Suppose, we have all orders placed without partitioning in this table. But when we query such a table, say for getting all orders for month 11 and year 2019, Hive has to go and read all of the data, then filter data for required condition and then form aggregation.

You can see how it go out of hand when we have huge dateset and all we want is data for few months from one of year.

In comes partitioning. Let us keep data in orders tables such that we have following folder strecture.

  • ...
  • orders/year=2019/month=01
  • orders/year=2019/month=02
  • ...
  • orders/year=2019/month=11
  • orders/year=2019/month=12
  • ....

In this case, when Hive has to query for data in the year 2019, and month 11, it can go to directory orders/year=2019/month=11 and read that data. No matter how much other data or partitions we have, Hive has to read-only that amount of data to get our results back.

This improves performance drastically. We can run complex analytics queries with good performance as filter operation(Hive only has to read data from a few partitions specified in where clause) is performed even before query execution is started.

Now, that we have seen benefits of partitions, let us see how we can implement them in Hive.

Partition Managed Tables In Hive

We will discuss managed partition tables first. Consider our table orders as above.

While creating a table we can easily specify partitioning columns. Note that we do not have mention partition columns in the column list of tables.

hive (maheshmogal)> CREATE TABLE order_partition ( order_id int, order_date string,  order_status string) PARTITIONED BY (year string, month string);
OK
Time taken: 0.972 seconds
hive (maheshmogal)> show create table order_partition;
OK
CREATE TABLE `order_partition`(
  `order_id` int, 
  `order_date` string, 
  `order_status` string)
PARTITIONED BY ( 
  `year` string, 
  `month` string)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'hdfs://nn01.itversity.com:8020/apps/hive/warehouse/maheshmogal.db/order_partition'
TBLPROPERTIES (
  'transient_lastDdlTime'='1592219107')
Time taken: 0.402 seconds, Fetched: 17 row(s)

So when we insert data into this table, each partition will have its separate folder. and when we run a query like "SELECT COUNT(1) FROM order_partition WHERE year=2019 and month=11", Hive directly goes to that directory in HDFS and read all data instated of scanning whole table and then filtering data for given condition.

I have inserted data into this table. (We will learn how to write data to a partitioned table in next paragraphs). When we check the HDFS directory we can see the number of partitions created for our orders.

hive (maheshmogal)> dfs -ls  /apps/hive/warehouse/maheshmogal.db/order_partition;
Found 2 items
drwxrwxrwx   - maheshmogal hdfs          0 2020-06-15 07:22 /apps/hive/warehouse/maheshmogal.db/order_partition/year=2013
drwxrwxrwx   - maheshmogal hdfs          0 2020-06-15 07:22 /apps/hive/warehouse/maheshmogal.db/order_partition/year=2014

hive (maheshmogal)> dfs -ls  /apps/hive/warehouse/maheshmogal.db/order_partition/year=2014;
Found 7 items
drwxrwxrwx   - maheshmogal hdfs          0 2020-06-15 07:22 /apps/hive/warehouse/maheshmogal.db/order_partition/year=2014/month=01
drwxrwxrwx   - maheshmogal hdfs          0 2020-06-15 07:22 /apps/hive/warehouse/maheshmogal.db/order_partition/year=2014/month=02
drwxrwxrwx   - maheshmogal hdfs          0 2020-06-15 07:22 /apps/hive/warehouse/maheshmogal.db/order_partition/year=2014/month=03
drwxrwxrwx   - maheshmogal hdfs          0 2020-06-15 07:22 /apps/hive/warehouse/maheshmogal.db/order_partition/year=2014/month=04
drwxrwxrwx   - maheshmogal hdfs          0 2020-06-15 07:22 /apps/hive/warehouse/maheshmogal.db/order_partition/year=2014/month=05
drwxrwxrwx   - maheshmogal hdfs          0 2020-06-15 07:22 /apps/hive/warehouse/maheshmogal.db/order_partition/year=2014/month=06
drwxrwxrwx   - maheshmogal hdfs          0 2020-06-15 07:22 /apps/hive/warehouse/maheshmogal.db/order_partition/year=2014/month=07

Let us select few rows from this table and see what kind of output we see.

Hive Partition Table Output
Hive Partition Table Output

Our table result shows our partitioned columns year and month. If you remember we did not include them in our table definition. Then how does Hive knows what value to pick for them and why did we not keep them in the table?

Even if we did not store values for partition columns in the table, Hive picks these values from directory names. That is why a query result can show us these values. We do not need to store these values in the table as a Hive can figure out those values and storing them again in the table will only introduce redundant storage.

We do not need to include partition columns in hive table definition.

Hive Facts

Partitioned External Tables in Hive

You can also use partitioning with external tables (You can read more about external vs managed tables in hive here). Rather you will find using partitioning more with external tables. External tables provide us with flexibility in selecting the HDFS path for our table and this is quite useful along with partitions. Let us see this in action

CREATE EXTERNAL TABLE order_partition_extrenal (
    order_id INT,
    order_date STRING,
    order_status STRING
)
PARTITIONED BY (
    year STRING,
    month STRING
    )
LOCATION '/apps/hive/warehouse/maheshmogal.db/order_partition';

We are pointing this external table to the location of our previously created managed table. This will not add partitions to our table though. Hive does not know anything about what data or folders we have at that location ( more on this in Inserting data into partition tables).

To load data as partitions in this table we can run following command.

ALTER TABLE order_partition_extrenal ADD PARTITION (year=2013, month=07)
LOCATION '/apps/hive/warehouse/maheshmogal.db/order_partition/year=2013/month=07';

We will revisit the inserting data in partitioned tables in the next tutorial. For now, you can check that this query will add a partition to our table and also let Hive know where that partition data is located in HDFS.

Listing Partitions in Table

Now that we have created partition tables, let us learn how we can check table is partitioned or not and if it is partitioned, list down all partitions of table.

We can use either show create a table or describe formatted table command to get metadata for that table. If the table is partitioned it will show that in detail.

SHOW CREATE TABLE order_partition_extrenal;
Partition Table Check

For listing partitions of table we can use below query.

hive (maheshmogal)> show partitions order_partition;
OK
year=2013/month=07
year=2013/month=08
year=2013/month=09
year=2013/month=10
year=2013/month=11
year=2013/month=12
year=2014/month=01
year=2014/month=02
year=2014/month=03
year=2014/month=04
year=2014/month=05
year=2014/month=06
year=2014/month=07
Time taken: 0.507 seconds, Fetched: 13 row(s)

Listing Down Subset of Partitions

You can have 100s of partitions in table. There is way you can only list partitions from one parent directory.

hive (maheshmogal)> SHOW PARTITIONS order_partition PARTITION (year=2013);
OK
year=2013/month=07
year=2013/month=11
year=2013/month=10
year=2013/month=08
year=2013/month=12
year=2013/month=09
Time taken: 0.587 seconds, Fetched: 6 row(s)

Conclusion

We have learned what is partitioning and why we use it in Hive. We have also created partitioned tables and learned about its basics.

In the next two blogs, we will learn more about partitioning in Hive. First, we will see what is static and dynamic partitioning, and finally, we will go through how we can load data to partitioned tables. See you there :).

Partitioning in Hive
.

Mahesh Mogal

I am passionate about Cloud, Data Analytics, Machine Learning, and Artificial Intelligence. I like to learn and try out new things. I have started blogging about my experience while learning these exciting technologies.

Stay Updated with Latest Blogs

Get latest blogs delivered to your mail directly.

Recent Posts

Select Expr in Spark Dataframe

In this blog, we will learn how to use select and expr in the Spark data frame. We will learn multiple use cases along with selectExpr.

Read More
Add, Rename, Drop Columns in Spark Dataframe

We will go through common column operations like add, rename, list, select, and dropping a column from spark dataframe.

Read More
MSCK Repair - Fixing Partitions in Hive Table

We will learn how to add multiple partitions to hive table using msck repair table command in hive.

msck repair hive
Read More

Leave a Reply

Your email address will not be published. Required fields are marked *

linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram
Share via
Copy link
Powered by Social Snap