Drop hive partition with not in clause

[#1]

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

[#2]

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

[#3]

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

[#4]

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

[#5]

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

[#6]

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