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 .

Dynamic Partition In Hive,hive tutorial,Dynamic Partition In Hive example,examples of Dynamic Partition In Hive,partition in Hive

# 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’;

Dynamic Partition In Hive,hive tutorial,Dynamic Partition In Hive example,examples of Dynamic Partition In Hive,partition in Hive

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

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

Dynamic Partition In Hive,hive tutorial,Dynamic Partition In Hive example,examples of Dynamic Partition In Hive,partition in Hive

select * from hive_table;

Dynamic Partition In Hive,hive tutorial,Dynamic Partition In Hive example,examples of Dynamic Partition In Hive,partition in Hive

Dynamic Partition In Hive,hive tutorial,Dynamic Partition In Hive example,examples of Dynamic Partition In Hive,partition in Hive

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

Dynamic Partition In Hive,hive tutorial,Dynamic Partition In Hive example,examples of Dynamic Partition In Hive,partition in Hive

Dynamic Partition In Hive,hive tutorial,Dynamic Partition In Hive example,examples of Dynamic Partition In Hive,partition in Hive

Dynamic Partition In Hive,hive tutorial,Dynamic Partition In Hive example,examples of Dynamic Partition In Hive,partition in Hive

Dynamic Partition In Hive,hive tutorial,Dynamic Partition In Hive example,examples of Dynamic Partition In Hive,partition in Hive

Dynamic Partition In Hive,hive tutorial,Dynamic Partition In Hive example,examples of Dynamic Partition In Hive,partition in Hive

Dynamic Partition In Hive,hive tutorial,Dynamic Partition In Hive example,examples of Dynamic Partition In Hive,partition in Hive

Original data

Dynamic Partition In Hive,hive tutorial,Dynamic Partition In Hive example,examples of Dynamic Partition In Hive,partition in Hive

Leave a Reply

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