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

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

Highlighted

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

Contributor

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.

9 REPLIES 9
Highlighted

Re: 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

Master Collaborator

Can you try the following:

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

Re: 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

Contributor

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)

Highlighted

Re: 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

Master Collaborator

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.

Highlighted

Re: 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

@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;

Re: 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

Contributor

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

Highlighted

Re: 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

New Contributor

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.

Highlighted

Re: 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

Contributor

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.

Highlighted

Re: 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

@Sankar T

Can you try this?

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

Highlighted

Re: 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

Contributor

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