Support Questions
Find answers, ask questions, and share your expertise

drop Partition

New Contributor

I have table 'tbl_1' with below columns. I need to delete data for revenue column for current month and last month every time I run the script which is daily and reload current month and last month. I have created partition on activity_dt and kpi_nm. Kpi_nm for revenue is ‘revenue’. How can I delete my partition? I have below query which does not work. Do I have to create shell and pass parameters? I would like to avoid it.

Activity_dt, Kpi_nm, Total_revenue, Gross_adds

alter table da_devstg_tbls.offline_transactions drop if exists partition ((activity_dt between ADD_MONTHS((CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE)+1),-1) AND (CURRENT_DATE-1)) , kpi_nm = 'revenue');

1 REPLY 1

Contributor

partition specification should be of the format "partition_column = partition_col_value". Hive would not be able to interpret "(activity_dt between ADD_MONTHS((CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE)+1),-1) AND (CURRENT_DATE-1)" as a valid partition specification. You would have to explicitly pass the correct arguments.