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