Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Cannot drop Hive partition with control characters in partition name

Solved Go to solution
Highlighted

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

Accepted Solutions
Highlighted

Re: Cannot drop Hive partition with control characters in partition name

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
Highlighted

Re: Cannot drop Hive partition with control characters in partition name

@bpreachuk What error message are you getting?

Highlighted

Re: Cannot drop Hive partition with control characters in partition name

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'

Highlighted

Re: Cannot drop Hive partition with control characters in partition name

@bpreachuk

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

Highlighted

Re: Cannot drop Hive partition with control characters in partition name

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.

Highlighted

Re: Cannot drop Hive partition with control characters in partition name

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

Highlighted

Re: Cannot drop Hive partition with control characters in partition name

Thanks Carter!

Highlighted

Re: Cannot drop Hive partition with control characters in partition name

Explorer

@bpreachuk

did the above query delete the partition?

Re: Cannot drop Hive partition with control characters in partition name

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.

Highlighted

Re: Cannot drop Hive partition with control characters in partition name

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

Don't have an account?
Coming from Hortonworks? Activate your account here