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

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

Solved Go to solution
Highlighted

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

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)

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

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

Master Collaborator

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)

View solution in original post

3 REPLIES 3
Highlighted

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

Master Collaborator

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)

View solution in original post

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

thank you Deepesh. It works for me.

Highlighted

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

Contributor

But this is not a suitable solution for production environment