You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by fab wol <da...@gmail.com> on 2014/07/21 16:02:19 UTC
Drop Partition by ID
Hi everyone,
I have the following problem: I have a partitoned managed table (Partition
table is a string which represents a date, eg. log-date="2014-07-15").
Unfortunately there is one partition in there like this:
log_date=2014-07-15-23%3A45%3A38 (copied from show partitions stmt). This
partitions most likeley got created to a wrong script 8which is fixed).
Now i want to delete this partition, but it doesn't work:
- alter table ... drop partitition (log_date='2014-07-15-23%3A45%3A38')
gives no error, but the partition is still existing afterwards
- I tried escaping the %-signs with backslashes but no luck with that
- I delete the directory in the HDFS and run msck repair table
afterwards. It recognizes that the folder is missing but is not deleting
the metadata
So what can I do to get rid of the metadata? My next guess would be to go
directly to the metastore DB and delete the metadata there. But what
exactly has to be deleted? I guess there are several dependencies.
Other idea: is there a possibility in Hive to delete a partition by a
unique ID or something like that?
Or what is needed to delete the table with the normal "alter table drop
partition" command?
Cheers
Wolli
Re: Drop Partition by ID
Posted by Devopam Mittra <de...@gmail.com>.
Please try using escape character around the '%' if not already done so.
regards
Dev
On Mon, Jul 21, 2014 at 7:32 PM, fab wol <da...@gmail.com> wrote:
> Hi everyone,
>
> I have the following problem: I have a partitoned managed table (Partition
> table is a string which represents a date, eg. log-date="2014-07-15").
> Unfortunately there is one partition in there like this:
> log_date=2014-07-15-23%3A45%3A38 (copied from show partitions stmt). This
> partitions most likeley got created to a wrong script 8which is fixed).
>
> Now i want to delete this partition, but it doesn't work:
>
> - alter table ... drop partitition
> (log_date='2014-07-15-23%3A45%3A38') gives no error, but the partition is
> still existing afterwards
> - I tried escaping the %-signs with backslashes but no luck with that
> - I delete the directory in the HDFS and run msck repair table
> afterwards. It recognizes that the folder is missing but is not deleting
> the metadata
>
> So what can I do to get rid of the metadata? My next guess would be to go
> directly to the metastore DB and delete the metadata there. But what
> exactly has to be deleted? I guess there are several dependencies.
>
> Other idea: is there a possibility in Hive to delete a partition by a
> unique ID or something like that?
>
> Or what is needed to delete the table with the normal "alter table drop
> partition" command?
>
> Cheers
> Wolli
>
--
Devopam Mittra
Life and Relations are not binary
Re: Drop Partition by ID
Posted by Prem Yadav <ip...@gmail.com>.
Only today I had the exact same issue.
I used a script to load partitions but due to a mistake there were a lot of
unwanted partitions with special characters.
"Alter table drop partitions" showed successful but partitions where never
removed.
Finally, this is what I did
1) hive> show create <table name>;
copy the output of this to my editor, to use the same command to recreate
tables.
2) Move the tables data from /user/hive/warehouse/<table> to another backup
folder.
3) Drop table.
4) Create table- using the output of 1.
5) Use a shell script to reload all the partitions.
On Mon, Jul 21, 2014 at 3:10 PM, Nitin Pawar <ni...@gmail.com>
wrote:
> you can try with like statement
> On 21 Jul 2014 19:32, "fab wol" <da...@gmail.com> wrote:
>
>> Hi everyone,
>>
>> I have the following problem: I have a partitoned managed table
>> (Partition table is a string which represents a date, eg.
>> log-date="2014-07-15"). Unfortunately there is one partition in there like
>> this: log_date=2014-07-15-23%3A45%3A38 (copied from show partitions stmt).
>> This partitions most likeley got created to a wrong script 8which is fixed).
>>
>> Now i want to delete this partition, but it doesn't work:
>>
>> - alter table ... drop partitition
>> (log_date='2014-07-15-23%3A45%3A38') gives no error, but the partition is
>> still existing afterwards
>> - I tried escaping the %-signs with backslashes but no luck with that
>> - I delete the directory in the HDFS and run msck repair table
>> afterwards. It recognizes that the folder is missing but is not deleting
>> the metadata
>>
>> So what can I do to get rid of the metadata? My next guess would be to go
>> directly to the metastore DB and delete the metadata there. But what
>> exactly has to be deleted? I guess there are several dependencies.
>>
>> Other idea: is there a possibility in Hive to delete a partition by a
>> unique ID or something like that?
>>
>> Or what is needed to delete the table with the normal "alter table drop
>> partition" command?
>>
>> Cheers
>> Wolli
>>
>
Re: Drop Partition by ID
Posted by Nitin Pawar <ni...@gmail.com>.
you can try with like statement
On 21 Jul 2014 19:32, "fab wol" <da...@gmail.com> wrote:
> Hi everyone,
>
> I have the following problem: I have a partitoned managed table (Partition
> table is a string which represents a date, eg. log-date="2014-07-15").
> Unfortunately there is one partition in there like this:
> log_date=2014-07-15-23%3A45%3A38 (copied from show partitions stmt). This
> partitions most likeley got created to a wrong script 8which is fixed).
>
> Now i want to delete this partition, but it doesn't work:
>
> - alter table ... drop partitition
> (log_date='2014-07-15-23%3A45%3A38') gives no error, but the partition is
> still existing afterwards
> - I tried escaping the %-signs with backslashes but no luck with that
> - I delete the directory in the HDFS and run msck repair table
> afterwards. It recognizes that the folder is missing but is not deleting
> the metadata
>
> So what can I do to get rid of the metadata? My next guess would be to go
> directly to the metastore DB and delete the metadata there. But what
> exactly has to be deleted? I guess there are several dependencies.
>
> Other idea: is there a possibility in Hive to delete a partition by a
> unique ID or something like that?
>
> Or what is needed to delete the table with the normal "alter table drop
> partition" command?
>
> Cheers
> Wolli
>