How to delete a particular Partition (Timestamp Based) from a hive table ? Example - Partition column value is load_s=2017-02-22 12%253A21%253A39

[#1]

Let me make it clear

HDFS Level- we have only valid partitions (parition1,parition2)

Table Level - we have both valid(parition1,partition2) and invalid partitions(partition3,partition4). I would like to drop only invalid partitions(partition3,partition4).

-> Unable to drop by passing Url encoded characters & Timestamp.

-> Created invalid partition at HDFS level, unable to drop.

-> Issued MCSK REPAIR command , post success also unable to drop.

ALTER TABLE tablename DROP PARTITION IF EXISTS (load_s='2017-02-22 12:21:39'); - did not worked.

ALTER TABLE tablename DROP PARTITION (load_s='2017-02-22 12:21:39'); - did not worked.

ALTER TABLE tablename DROP PARTITION (load_s='2017-02-22 12%3A21%3A39'); - did not worked.

[#2]

Can you try the following:

ALTER TABLE tablename DROP PARTITION IF EXISTS (load_s=timestamp '2017-02-22 12:21:39');

[#3]

Deepesh - it did not worked. Getting below error.

With IF EXIST

Error: Error while compiling statement: FAILED: ParseException line 1:75 mismatched input 'IF' expecting ( near 'PARTITION' in drop partition statement (state=42000,code=40000)

Without IF EXIST -

Error: Error while compiling statement: FAILED: SemanticException Unexpected unknown partitions for (load_s = 2017-02-22 12:21:39.0) (state=42000,code=40000)

[#4]

For me the command worked.

hive> show partitions tablename;
OK
load_s=2017-02-22 12%3A21%3A39
hive> alter table tablename drop partition (load_s=timestamp '2017-02-22 12:21:39');
Dropped the partition load_s=2017-02-22 12%3A21%3A39
OK

You can potentially use this workaround on other thread.

[#5]

@Sankar T command given by deepesh should work. If not please share the command which you are executing and the errors logs. Before hand run this command and execute your query--> show partitions tablename;

[#6]

It is not working and notified to Hortonworks team. Soon I got the answer will post it here.

[#7]

I tried this, and it worked for me,

ALTER TABLE tablename DROP IF EXISTS PARTITION (load_s=timestamp '2017-02-22 12:21:39');

The sentence "IF EXISTS" goes after the "DROP".

Regards.

[#8]

Adding to that , we are able to drop the partitions at HDFS level, but those invalid partitions were not dropped even after MCSK REPAIR TABLE command too.

[#9]

@Sankar T

Can you try this?

alter table tablename DROP partition (load_s <> '');

[#10]

Let me make it clear

HDFS Level- we have only valid partitions (parition1,parition2)

Table Level - we have both valid(parition1,partition2) and invalid partitions(partition3,partition4). I would like to drop only invalid partitions(partition3,partition4).

-> Unable to drop by passing Url encoded characters & Timestamp.

-> Created invalid partition at HDFS level, unable to drop.

-> Issued MCSK REPAIR command , post success also unable to drop.

Thanks