Support Questions
Find answers, ask questions, and share your expertise

Drop hive partition with not in clause

Drop hive partition with not in clause

Hi 

 

Can anyone tell me if i can use not in clause in partition , I want to delete all the partitions except one, 

 

alter table <tablename> drop partition(partion_name<>partition_val) doesnt work.

 

 

Regards

Suresh

5 REPLIES 5

Re: Drop hive partition with not in clause

Contributor
Hey Suresh,
What version of CDH are you on?

This should work. Here is a simple test (from one of the test files)

create table ptestfilter (a string, b int) partitioned by (c string, d string);
describe ptestfilter;

alter table ptestfilter add partition (c='US', d=1);
alter table ptestfilter add partition (c='US', d=2);
alter table ptestFilter add partition (c='Uganda', d=2);
alter table ptestfilter add partition (c='Germany', d=2);
alter table ptestfilter add partition (c='Canada', d=3);
alter table ptestfilter add partition (c='Russia', d=3);
alter table ptestfilter add partition (c='Greece', d=2);
alter table ptestfilter add partition (c='India', d=3);
alter table ptestfilter add partition (c='France', d=4);
show partitions ptestfilter;

// this should drop all partitions except where c='US'
alter table ptestfilter drop partition (c<>'US', d>'0');
// alternatively you could also use NOT EQUALS operator to accomplish the same thing
alter table ptestfilter drop partition (c != 'US', d>'0');

Let me know if this answers your question. Thanks

Re: Drop hive partition with not in clause

Contributor
Hey Suresh,
Did you get a chance to try this? Let me know how this goes. Thanks

Re: Drop hive partition with not in clause

Hi Naveen,

 

Thanks for your response, however i need know if drop partition not in clause exist, may be my example is confusing .

 

I have partitions like

load_id ='A'

load_id='B'

load_id='C'

load_id='D'

load_id='E'

load_id='F' and i want to drop all but partitions D&E

 

Regards

Suresh

Re: Drop hive partition with not in clause

Contributor
Hey Suresh,
Thanks for the clarification.

I am not sure if that is supported today. Is there an example of another hive command that works today? Thanks

Re: Drop hive partition with not in clause

Naveen,

 

Sorry for the looooooooong delay in response, currently there is no way i guess except listing all partitions that need to be removed manually .

 

 but it would be cool to have "drop partition not in "option when there are huge number of partitions to be purged