I have a hive table where partition exists on one of the date column, but date column is stored as INT in the format YYYYMMDD. The table can also contain data for future date's partitions.
Now as part of the process I want to drop partitions which are fuing to usture days (sitting on the processing day).
So I am trying to use below
ALTER TABLE TABLE1 DROP IF EXISTS PARTITION (TBL_DATE >= CAST(REGEXP_REPLACE(CAST(DATE_SUB(TO_DATE('${load_date}'),1) AS STRING),'-','') AS INT));
means with date value
ALTER TABLE TABLE1 DROP IF EXISTS PARTITION (TBL_DATE >= CAST(REGEXP_REPLACE(CAST(DATE_SUB(TO_DATE('2016-09-11'),1) AS STRING),'-','') AS INT));
But it is failing.
The output of the query is CAST(REGEXP_REPLACE(CAST(DATE_SUB(TO_DATE('2016-09-11'),1) AS STRING),'-','') AS INT) => 20160910
When I am writing my drop partitions like ALTER TABLE TABLE1 DROP IF EXISTS PARTITION (TBL_DATE >= 20160910), then it is working fine.
Considering my input will be only date format YYYY-MM-DD and I have drop all partitions having given input date -1; how to make the above statement work.
