How to create External Table in Hive with Partitions And Load Data ?

Start with Create Table 

Create EXTERNAL TABLE Countries(
Id TINYINT,
Country String,
udate String,
UPDATE_DT String,
ACTIVE_FLAG String)
PARTITIONED BY (INSERT_DT String)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','

Location '/training/test/';

Now table is create d in Hive but data is still not in hive tables.

Data can be loaded into partitions table in two ways :

1)Static partitions Insert
2) Dynamic Partition Insert

1.Static Partitions Insert

LOAD Data into partiton table by giving path of file
ALTER TABLE Countries ADD PARTITION(INSERT_DT='25-12-2015')
LOCATION '/training/test/25-12-2015';


In case you want to delete or drop partitions from existing table :

ALTER TABLE Countries PARTITION(INSERT_DT='25-12-2015')
SET LOCATION '/training/test/25th';

Note :This won't delete the existing data .it will simply change the location of partiton data.

2. Dynamic Partitions

create a new Hive Table

Create EXTERNAL TABLE Countries_dynamic(
Id TINYINT,
Country String,
udate String,
UPDATE_DT String,
ACTIVE_FLAG String)
PARTITIONED BY (INSERT_DT String)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','

Location '/training/test_dynamic/';

Set following features :

set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=1000;

Now to add data in partitions run following query :

INSERT OVERWRITE TABLE Countries_dynamic
PARTITION (dt) SELECT Id  ,
Country ,udate,UPDATE_DT,ACTIVE_FLAG ,INSERT_DT as dt

FROM Countries ;