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

How to delete/drop a partition of an external table along with data

Rising Star

I have an external table which is created with partitions and i would like to delete/drop few partition along with data as i no longer require it.

Appreciate any suggestions.

1 ACCEPTED SOLUTION

Accepted Solutions

Rising Star

Here is work around, which i have implemented.

For an external table, If you are trying to drop a partition and as-well would like to delete the data. This can be achieved as below.

1. Alter external table as internal table -- by changing the TBL properties as external =false

2. Drop the partitions -- when you drop the partitions, data pertained to the partitions will also be dropped as now this table is managed table

3. Alter back the table as external=True.

By doing this, there more controlled on what we are deleting and drop the partitions rather than using hadoop rm command

View solution in original post

3 REPLIES 3

Super Collaborator

@Reddy

Because it is an external table there is no one-liner to do it. That is probably the whole point of having external tables

So you need to do

ALTER TABLE some.table DROP PARTITION (part="some") PURGE;

and

hdfs dfs -rm -R /path/to/table/basedir

I put the 'PURGE' in there intentionally. It would work for non-external tables, but just not for external tables.

Super Collaborator

@Reddy Please don't forget to mark the question as answered if it is answered.

Rising Star

Here is work around, which i have implemented.

For an external table, If you are trying to drop a partition and as-well would like to delete the data. This can be achieved as below.

1. Alter external table as internal table -- by changing the TBL properties as external =false

2. Drop the partitions -- when you drop the partitions, data pertained to the partitions will also be dropped as now this table is managed table

3. Alter back the table as external=True.

By doing this, there more controlled on what we are deleting and drop the partitions rather than using hadoop rm command

View solution in original post