Apache Hive Dynamic Partition  table

Difference between Static and Dynamic partition :

Static Partition  columns: in DML/DDL involving multiple partitioning columns, the columns whose values are known at COMPILE TIME (given by user).

Dynamic Partition columns: columns whose values are only known at EXECUTION TIME.

To enable dynamic partitioning in hive table , set these following hive properties in hive prompt

SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=non-strict;

By default , hive partition is static

Table creation :

 #Creating hive table using single dynamic column . e.g  year

 create table hive_partition(id int, name string, salary float, location string) partitioned by (year int) row format delimited fields terminated by ‘,’ lines terminated by ‘\n’;

#Creating hive table using multiple dynamic partition column.  e.g  year,location

create table hive_partition(id int, name string, salary float) partitioned by (year int , location string) row format delimited fields terminated by ‘,’ lines terminated by ‘\n’;

#Creating hive table using  static partition and Dynamic partition column. e.g year  as static partition column ,location as dynamic column

create table hive_partition(id int, name string, salary float) partitioned by (year int , location string) row format delimited fields terminated by ‘,’ lines terminated by ‘\n’;

#Note : Dont use dynamic partition column before static partition Column .

# Creating Source table , non-partitioned table

create table hive_table(id int,name string,salary float,location string,year int) row format delimited fields terminated by ‘,’ lines terminated by ‘\n’;

#Loading data from local file into the non-partitioned table

load data local inpath ‘/home/geouser/student.txt’ into table hive_table;

select * from hive_table;

INSERT OVERWRITE INTO TABLE hive_partition PARTITION (year) SELECT * from hive_table;

Original data