How to create a table with Partitions in Hive

Hive Partitions

Partitioning is the way to dividing the table based on the key columns and organize the records in a partitioned manner. It is nothing but a directory that contains the chunk of data.

In Hive, the table is stored as files in HDFS. If we specify the partitioned columns in the Hive DDL, it will create the sub directory within the main directory based on partitioned columns.

Create a partitioned Hive table

The table Customer_transactions is created with partitioned by Transaction date in Hive.Here the main directory is created with the table name and Inside that the sub directory is created with the txn_date in HDFS.

Insert values to the partitioned table in Hive

By default the hive.exec.dynamic.partition.mode is set to strict, then we need to do at least one static partition. In non-strict mode, all partitions are allowed to be dynamic.Since we got the below error while insert the record, we changed the dynamic partition mode to nonstrict.

FAILED: SemanticException [Error 10096]: Dynamic partition strict mode requires at least one static partition column. To turn this off set hive.exec.dynamic.partition.mode=nonstrict

Insert records into partitioned table in Hive

Show partitions in Hive

Lets check the partitions for the created table customer_transactions using the show partitions command in Hive

show partitions in Hive table

Partitioned directory in the HDFS for the Hive table

The sub directory has created under the table name for the partitioned columns in HDFS as below

Recommended Articles

Add partitions on existing table in Hive