Automatic (Dynamic) Partitioning With Hive

Partitioning in Hive is usually considered to be tedious task while loading the data. You have to manually segregate the data and have to tell Hive to which partition you want to to load data. This requires a lot of manual intervention and effort. 

Further partitioning is usually done on the columns that does not exist in the data. What else if we want to partition on the column that exist in our data. I usually became clueless in this scenario.

Another problem is querying the one large HDFS file that contains historical data. If we can somehow divide the large file into small pieces we can get a faster turn around time while querying.

Consider a situation when we have one file that has two fields:ORDERID and ORDERDATE. Lets say each day we receive large number of orders. We want to view that file in the Hive but creating a one large table and querying the data is a heavy operation. So another way is to view this file as Hive Table partitioned by Date and all the partitions fills automatically depending upon value in the input file.

This problem can be solved by a two step process:

1) Set couple of properties in Hive

2)Create a external staging table "staging_order" and load the input files data to this table.

3) Create a main production external table "production_order" with the date as one of the partitioned columns. 

4) Load the production table from the staging table so that data is distributed in partitions automatically.