As you know external tables are tables where Hive does not manage the data of the External table. So when we drop do and alter to the external table or its partitions , then only the metadata associated with the table will get deleted or altered, the table data remains untouched by Hive. There will be a situation, if we want to refresh a particular partition by deleting all the data. In the following way we can achieve in hive .
1. Convert the External table to Managed or Internal Table ( Hive manages the data )
2. Drop the partition that you may not required.
3. Convert the Managed or Internal table back to External Table
4. Do a HIVE MSCK REPAIR on top of the table to recovers all the partitions in the directory of a table and updates the Hive metastore.
ALTER TABLE <TABLE NAME> SET TBLPROPERTIES('EXTERNAL'='FALSE');
ALTER TABLE <TABLE NAME> DROP [IF EXISTS] > PARTITION (YEAR='YEAR_VALUE');
ALTER TABLE <TABLE NAME> SET TBLPROPERTIES('EXTERNAL'='TRUE');
MSCK REPAIR TABLE <TABLE NAME>;
Example
ALTER TABLE HRM.FRESHERS_IN_LOGDATA SET TBLPROPERTIES('EXTERNAL'='FALSE');
ALTER TABLE HRM.FRESHERS_IN_LOGDATA DROP [IF EXISTS] > PARTITION (YEAR='2021');
ALTER TABLE HRM.FRESHERS_IN_LOGDATA SET TBLPROPERTIES('EXTERNAL'='TRUE');
MSCK REPAIR TABLE HRM.FRESHERS_IN_LOGDATA;
