Skip to main content
PentaReddy
PentaReddy
Gold ⭐️
January 25, 2017
Solved

Deleting External Table Partition Data: Switching TBLPROPERTIES to Internal

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.

PentaReddy
PentaReddyAnswer
Gold ⭐️
February 8, 2017

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 in thread

3 replies

jknulst
Gold ⭐️
jknulst
Gold ⭐️
January 25, 2017

@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.

Translate
    jknulst
    Gold ⭐️
    jknulst
    Gold ⭐️
    January 27, 2017

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

    Translate
    PentaReddy
    PentaReddyAuthorAnswer
    Gold ⭐️
    February 8, 2017

    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

    Translate
      Terms & ConditionsAccessibility statement