Hive : Drop Partitions : How to drop Date partitions containing non-date values?

[#1]

Hi All,

Below is the hive table partitions(three level partitions) I have. This table is a MANAGED table(internal table). First two partitions are incorrect partitions created due to a bug in my insert hive script. Later some days, i found this and i want to drop these two partitions somehow. I tried multiple ALTER table DROP partitions, but nothing worked for me. How to drop these partitions? Can anyone please help me?

Column - reporting_date is of DATE data type. This column got inserted with '${hiveconf:reporting_date}' value instead of '2016-12-09'. This created a problem.

Column - source_system is of STRING data type

Column - break_type is of STRING data type.

hive> show partitions crhs_fmtrade_break;
reporting_date=$%7Bhiveconf%3Areporting_date}/source_system=MXG/break_type=Destination
reporting_date=$%7Bhiveconf%3Areporting_date}/source_system=MXG/break_type=Source
reporting_date=2016-12-09/source_system=BTS/break_type=Data
reporting_date=2016-12-09/source_system=BTS/break_type=Destination
...

hive> alter table crhs_fmtrade_break drop partition(reporting_date='${hiveconf:reporting_date}');
FAILED: SemanticException Unexpected unknown partitions for (reporting_date = null)
hive> alter table crhs_fmtrade_break drop partition(reporting_date='${hiveconf:reporting_date}\r');
FAILED: SemanticException Unexpected unknown partitions for (reporting_date = null)
hive> alter table crhs_fmtrade_break drop partition(reporting_date='\r${hiveconf:reporting_date}\r');
FAILED: SemanticException Unexpected unknown partitions for (reporting_date = null)
hive> alter table crhs_fmtrade_break drop partition(reporting_date='\r${hiveconf:reporting_date}');
FAILED: SemanticException Unexpected unknown partitions for (reporting_date = null)
hive> alter table crhs_fmtrade_break drop partition(reporting_date='$%7Bhiveconf%3Areporting_date}\r');
FAILED: SemanticException Unexpected unknown partitions for (reporting_date = null)
hive> alter table crhs_fmtrade_break drop partition(reporting_date='$\r');
FAILED: SemanticException Unexpected unknown partitions for (reporting_date = null)

[#2 · Accepted solution]

You could accomplish this by temporarily changing the partitioning column type to string, see below:

-- Change the column type to string
alter table crhs_fmtrade_break partition column (reporting_date string);
-- Drop the offending partitions
alter table crhs_fmtrade_break drop partition(reporting_date='$%7Bhiveconf%3Areporting_date}');
...
-- Change the column type back to date
alter table crhs_fmtrade_break partition column (reporting_date date)

[#3]

thank you Deepesh. It works for me.

[#4]

But this is not a suitable solution for production environment

[#5]

I found other simple solution for this issue,

Simply find faulty partition from partition list by using command.

show partitions table table_name;

then rename the faulty partition to some other name in correct format of your partition.

In my case, I used

ALTER table table_name partition
(date_flag='2021-11-25_bak') rename to partition (date_flag='2021-01-01');