Cannot drop Hive partition with control characters in partition name

[#1]

Cleaning up some bad partitions, created in error.

We currently have an external table with a Hive Partition that I am unable to drop via Alter statement. The Partition has control characters (%0D - what was a Carriage Return) in the partition name field.

The table is External and is called <tableName>. It's partitioned by fiscal_year and erp.

show partitions <tableName>;    
fiscal_year=2014%0D/erp=ae_na%0D%0D    
fiscal_year=2014/erp=ae_na    
fiscal_year=2014/erp=be_na 
...

The underlying files in HDFS were deleted long ago and no longer exist.

I have tried the following commands without success:

ALTER TABLE <tableName> DROP IF EXISTS PARTITION(fiscal_year='2014%0D', erp='ae_na%0D%0D');
ALTER TABLE <tableName> DROP PARTITION(fiscal_year='2014%0D', erp='ae_na%0D%0D');
ALTER TABLE <tableName> DROP PARTITION(fiscal_year>'2014');

Is there a way to drop this partition, or do I have to copy the data out, rebuild the table and move the data back in?

Thanks in advance.

[#2]

@bpreachuk What error message are you getting?

[#3]

No error message - It just silently does not drop the partition. Checked Hive logs - no underlying messages. Plus I've confirmed there are no security issues either.

When it does drop a partition you get a positive message - something to the effect of 'PARTITION XXXXX dropped successfully'

[#4]

@bpreachuk

Could you share the ddl? I would like to reproduce in my env.

[#5]

Will do. I will send DDL and show partitions data in an email as-is. The difficulty lies in that I can't show you you how the partition was created with the bad data - the person who did it is no longer available.

[#6 · Accepted solution]

Use \r. Special characters are URL escaped, which is why your command above doesn't work.

ALTER TABLE <tableName> DROP IF EXISTS PARTITION(fiscal_year='2014\r', erp='ae_na\r\r');

[#7]

Thanks Carter!

[#8]

@bpreachuk

did the above query delete the partition?

[#9]

Hi @suresh kumar c. It was a long time ago, but yes I believe it did fix the query.

Here is some quick checking to confirm: %0D is a carriage return in this chart: http://www.degraeve.com/reference/urlencoding.php and then I looked it up in this chart https://en.wikipedia.org/wiki/Escape_sequences_in_C and saw that \r = carriage return = %0D.

Whatever your control character is, look it up and make sure it is URL-escaped when you try to drop the partition.

I hope this helps.

[#10]

@bpreachuk @Carter Shanklin I have similar problem inserted Hive partition column as (CL=18) which stored as /../CL=CL%3D18 (invalid partition). I tried using escape sequence \x3D , \u0030 did not work, am I doing some thing wrong for Equal(=) sign?

alter table tb drop partition (CL='CL\x3D18); <-- did not work