Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

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
Highlighted

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