Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Cannot drop Hive partition with control characters in partition name

avatar

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.

1 ACCEPTED SOLUTION

avatar
Explorer

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');

View solution in original post

9 REPLIES 9

avatar
Master Mentor

@bpreachuk What error message are you getting?

avatar

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'

avatar
Master Mentor

@bpreachuk

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

avatar

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.

avatar
Explorer

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');

avatar

Thanks Carter!

avatar
Contributor

@bpreachuk

did the above query delete the partition?

avatar

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.

avatar
New Contributor

@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