Support Questions

Find answers, ask questions, and share your expertise

Cannot drop Hive partition with control characters in partition name

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

New Contributor

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

@bpreachuk What error message are you getting?

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'

@bpreachuk

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

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.

New Contributor

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

Thanks Carter!

Explorer

@bpreachuk

did the above query delete the partition?

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.

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

Take a Tour of the Community
Don't have an account?
Your experience may be limited. Sign in to explore more.