Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

How can I Alter multiple partitions at once on Impala ?

avatar
Contributor

I would like to drop multiple partitions of an impala table at once.

Can I use the following statement  in order to delete all the partitions of January ?

 

ALTER TABLE schema.Table  DROP IF EXISTS PARTITION (part_col='201801*') PURGE ;

 

Can I use Star Wildcard  just as unix?

1 ACCEPTED SOLUTION

avatar
Expert Contributor
hide-solution

This problem has been solved!

Want to get a detailed solution you have to login/registered on the community

Register/Login
7 REPLIES 7

avatar
Expert Contributor

Hi eMazarakis,

 

Multiple partitions can be dropped with the following syntax:

alter table historical_data drop partition (year = 1996 and month between 1 and 6);

Please see our ALTER TABLE Statement documentation for more details, the multiple partition drop can be found in section: To drop or alter multiple partitions.

 

avatar
Contributor

@tmater   my Cloudera version is 5.9 . I forgot to mentioned that.

avatar
Expert Contributor

Hi @eMazarakis,

Thank you for the additional information. Altering multiple partitions was implemented in IMPALA-1654, this feature is available from Impala 2.8+ which is part of CDH 5.10+.

Although, I am not aware of the workflow and the amount of data behind a partition, this specific expression part_col='201801* removes a whole month. If these requests are frequent and the workload/workflow allows it re-partitioning based on months could be a feasible workaround.

 

 

avatar
Contributor

@tmater  unfortunately this kind of expression ( part_col='201801*' ) does not work.

avatar
Expert Contributor

@eMazarakis, apologies, I meant the part_col='201801*' intends to remove a whole month. If possible might worth reconsidering the partition strategy or the drop operation could be done in Hive separately.

From CDH 5.10+ partition expressions can be specified, please see my response here for details.

avatar
Contributor

@tmater    the following command , did not remove the partitions/dirs in my cloudera version.

 

ALTER TABLE  tblName DROP IF EXISTS PARTITION ( part_col='201801*' ) PURGE.

 

I think special characters such as * cannot be used in this version.

 

avatar
Expert Contributor
hide-solution

This problem has been solved!

Want to get a detailed solution you have to login/registered on the community

Register/Login