Hive drop partition is not working

[#1]

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]

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

[#3]

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.