Support Questions

Find answers, ask questions, and share your expertise

Hive drop partition is not working

Rising Star

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.






Cloudera Employee



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

Rising Star

Hi @COE ,


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.