CODEWITHSUNDEEP
jsonhivehiveqlpartitioning
TheDataGuy
TheDataGuy

Reputation: 3118

Create table partition in Hive for year,month and day

I have my data folder in the below structure with 2 years data(2015-2017).

AppData/ContryName/year/month/Day/app1.json

For eg:

AppData/India/2016/07/01/geek.json

AppData/India/2016/07/02/geek.json

AppData/US/2016/07/01/geek.json

Now I have created an external table with partition.

PARTITIONED BY (Country String, Year String, Month String, day String)

After this, I need to add the partition in alter table statement. 

ALTER TABLE mytable 
ADD PARTITION (country='India', year='2016',month='01', day='01') 
location 'AppData/India/2016/07/01/'

Create add partition script to each and every day is not possible,

Is there any simplest way to achieve this?

Upvotes: 1

Views: 10263

Answers (1)

David דודו Markovitz
David דודו Markovitz

Reputation: 44991

msck repair table mytable, but not with your current directory naming convention

Demo

bash

hdfs dfs -mkdir -p /AppData/country=India/year=2016/month=07/day=01 
hdfs dfs -mkdir -p /AppData/country=India/year=2016/month=07/day=02
hdfs dfs -mkdir -p /AppData/country=US/year=2016/month=07/day=01

hive

create table mytable (i int) 
partitioned by (country string, year string, month string, day string)
location '/AppData'
;

hive> msck repair table mytable;
OK
Partitions not in metastore:    mytable:country=India/year=2016/month=07/day=01 mytable:country=India/year=2016/month=07/day=02 mytable:country=US/year=2016/month=07/day=01
Repair: Added partition to metastore mytable:country=India/year=2016/month=07/day=01
Repair: Added partition to metastore mytable:country=India/year=2016/month=07/day=02
Repair: Added partition to metastore mytable:country=US/year=2016/month=07/day=01

hive> show partitions mytable;
OK
partition
country=India/year=2016/month=07/day=01
country=India/year=2016/month=07/day=02
country=US/year=2016/month=07/day=01

Upvotes: 3

Related Questions