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>;
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?
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.
@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