Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Hive drop partition is not working

avatar
Contributor

Hello All,

 

I am trying to drop the partitions less than 14 days ago in Hive and hive query below is throwing error.

 

Hive query:

ALTER TABLE audit_logs DROP PARTITION (evt_date < 'date_format(date_sub(from_unixtime(unix_timestamp('20211013','yyyyMMdd'),'yyyy-MM-dd'),14),'yyyyMMdd')');

 

We have partitioned evt_date as string date type and evt_date is stored in this date format yyyyMMdd i.e 20211013 as string in the table .

 

Error :

Error while compiling statement: FAILED: ParseException line 4:118 cannot recognize input near ''date_format(date_sub(from_unixtime(unix_timestamp('' '20211013' '','' in constant

 

Input to the unix_timestamp('20211013','yyyyMMdd') method also comes as yyyyMMdd in string format from another oozie parameter/another program.

 

individual select query 

 

select date_format(date_sub(from_unixtime(unix_timestamp('20211013','yyyyMMdd'),'yyyy-MM-dd'),14),'yyyyMMdd'); 

Output : 20210929

 

ALTER TABLE audit_logs DROP PARTITION (evt_date < '20211029'); - Hive query works fine . 

 

Could someone assist on this.

 

Thanks

@hive 

 

2 REPLIES 2

avatar
Expert Contributor

Hello,

 

The partition clause in the drop partition expect CONSTANT VALUE on the right-hand side and the functions inside the drop partition clause are not supported

 

The correct syntax would be:-

 

ALTER TABLE audit_logs DROP PARTITION (evt_date<‘some constant value ' )

avatar
Contributor

Hi @ShankerSharma ,

 

Thank you for the confirmation . Yes. I mentioned one of the working drop partition query in the post. We were in situation to use the functions inside drop partition clause .  We will adopt the 14days calculation in script and pass the value to DROP partition statement.