question Re: Cannot drop Hive partition with control characters in partition name in Support Questions

Cannot drop Hive partition with control characters in partition name

bpreachuk — Tue, 08 Dec 2015 04:58:47 GMT

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.

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

nsabharwal — Tue, 08 Dec 2015 05:06:47 GMT

@bpreachuk What error message are you getting?

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

bpreachuk — Tue, 08 Dec 2015 05:14:30 GMT

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'

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

nsabharwal — Tue, 08 Dec 2015 05:30:48 GMT

@bpreachuk

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

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

bpreachuk — Tue, 08 Dec 2015 05:38:59 GMT

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.

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

carters — Thu, 10 Dec 2015 00:30:23 GMT

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

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

bpreachuk — Thu, 10 Dec 2015 00:48:36 GMT

Thanks Carter!

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

schitirala — Tue, 11 Apr 2017 10:38:50 GMT

@bpreachuk

did the above query delete the partition?

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

bpreachuk — Tue, 11 Apr 2017 19:28:06 GMT

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.

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

girisht_bigdata — Fri, 12 Oct 2018 04:56:31 GMT

@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