CODEWITHSUNDEEP
sqlapache-sparkhivehiveqlbigdata
Soma Sekhar Kuruva
Soma Sekhar Kuruva

Reputation: 69

Hive partitions by date?

I have one external table like 

CREATE EXTERNAL TABLE TAB(ID INT, NAME STRING) PARTITIONED BY(YEAR INT, MONTH STRING , DATES INT) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

I have data like 

/user/input/2015/jan/1;
/user/input/2015/jan/30

like that years 2000 to 2016 ,every year with 12 months and 30 days;

ALTER TABLE TAB ADD PARTITION(year = '2015', month = 'jan',dates = '5') LOCATION '/user/input/2015/jan/1';

if i do like this i am getting only one day data ; 

select * from TAB where (year = '2015', month = 'jan',dates = '5');

if I run 

select * from TAB where (year = '2015', month = 'jan',dates = '6');

I am not getting any data. Please help me on this how to alter table for the above scenario

Upvotes: 1

Views: 9674

Answers (3)

Soma Sekhar Kuruva
Soma Sekhar Kuruva

Reputation: 69

alter table with all dates only option, I am following the same like "ALTER TABLE TAB ADD PARTITION(year = '2015', month = 'jan',dates = '5') LOCATION '/user/input/2015/jan/1'; "

Upvotes: 0

nobody
nobody

Reputation: 11090

You are getting 1 day for ALTER TABLE TAB ADD PARTITION(year = '2015', month = 'jan',dates = '5') LOCATION '/user/input/2015/jan/1'; because you are specifying 1 file in your location value

For 5 days create the partition as below

ALTER TABLE TAB 
ADD PARTITION(dates <= '5') 
LOCATION '/user/input/2015/jan/';

Upvotes: 0

Rahul
Rahul

Reputation: 36

create table tab(id int,name string,dt string) partitioned by (year string,month string);

create table samp(id int,name string,dt string) row format delimited fields terminated by '\t';

load data inpath '\dir' into table samp;
insert overwrite table tab partition (y,m) select id,name dt,YEAR(dt),MONTH(dt) from samp;

Upvotes: 2

Related Questions