Created 03-16-2017 07:45 AM
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)
Created 03-16-2017 05:04 PM
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)
Created 03-16-2017 05:04 PM
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)
Created 03-21-2017 10:53 AM
thank you Deepesh. It works for me.
Created 08-21-2017 09:53 AM
But this is not a suitable solution for production environment
Created on 11-29-2021 02:10 AM - edited 11-29-2021 02:11 AM
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');