Support Questions
Find answers, ask questions, and share your expertise
Announcements
Check out our newest addition to the community, the Cloudera Innovation Accelerator group hub.

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.

 

Thanks

@hive 

 

2 REPLIES 2

Cloudera Employee

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

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.