I have an environment where I'm partitioning data in some hive tables by day.  I'd like to be able to delete data that's older than 1 week in some tables and 1 month in others.  It appears that ALTER TABLE <t> DROP PARTITION only supports a partition spec that equates a partition with a literal value.

I must be missing something obvious — how would I delete partitions that meet some criteria, rather than partitions that I'm able to reference via literal value?  The only way I can think of doing this is by first running a hive query to get all partitions that should be deleted, then constructing a series of ALTER TABLE DROP PARTITION statements, each with a literal corresponding to one of the PARTITIONS, and executing those in a second phase.

I'd like to be able to do this all within one HQL script…

Thoughts?

Thanks,
Evan

Hive 0.8.0 has metadata optimizations
([HIVE-1003]<https://issues.apache.org/jira/browse/HIVE-1003>),
but your best bet is to write a shell script that executes 'show partitions
<table_name>;', and then loop through the results and drop any partitions
that meet your criteria.  You can then create a cron job to regularly
execute the shell script.

Matt

On Sun, Feb 5, 2012 at 5:10 PM, Evan Pollan <[EMAIL PROTECTED]>wrote:

> I have an environment where I'm partitioning data in some hive tables by
> day.  I'd like to be able to delete data that's older than 1 week in some
> tables and 1 month in others.  It appears that ALTER TABLE <t> DROP
> PARTITION only supports a partition spec that equates a partition with a
> literal value.
>
> I must be missing something obvious — how would I delete partitions that
> meet some criteria, rather than partitions that I'm able to reference via
> literal value?  The only way I can think of doing this is by first running
> a hive query to get all partitions that should be deleted, then
> constructing a series of ALTER TABLE DROP PARTITION statements, each with a
> literal corresponding to one of the PARTITIONS, and executing those in a
> second phase.
>
> I'd like to be able to do this all within one HQL script…
>
> Thoughts?
>
> Thanks,
> Evan
>

Thanks — that's what I suspected.

I'm not sure HIVE-1003 would enable me to do what I want to do within a single HQL session, though…  It looks like partition enumeration will be more performant (based on metastore rather than table data files), but I don't see any provision for anything other than ALTER TABLE DROP PARTITION statements with literal partition specs…

From: Matt Tucker <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>>
Reply-To: "[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>" <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>>
Date: Sun, 5 Feb 2012 16:40:38 -0600
To: "[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>" <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>>
Subject: Re: Partition deletion w/out using a literal partition value

Hive 0.8.0 has metadata optimizations ([HIVE-1003]<https://issues.apache.org/jira/browse/HIVE-1003>), but your best bet is to write a shell script that executes 'show partitions <table_name>;', and then loop through the results and drop any partitions that meet your criteria.  You can then create a cron job to regularly execute the shell script.

Matt

On Sun, Feb 5, 2012 at 5:10 PM, Evan Pollan <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>> wrote:
I have an environment where I'm partitioning data in some hive tables by day.  I'd like to be able to delete data that's older than 1 week in some tables and 1 month in others.  It appears that ALTER TABLE <t> DROP PARTITION only supports a partition spec that equates a partition with a literal value.

I must be missing something obvious — how would I delete partitions that meet some criteria, rather than partitions that I'm able to reference via literal value?  The only way I can think of doing this is by first running a hive query to get all partitions that should be deleted, then constructing a series of ALTER TABLE DROP PARTITION statements, each with a literal corresponding to one of the PARTITIONS, and executing those in a second phase.

I'd like to be able to do this all within one HQL script…

Thoughts?

Thanks,
Evan