I have written query that reads data from multiple external tables on S3, applies requisite filter, joins and then loads results in a target table for analysis in Athena.
However as data in source table is refreshed I wanted to refresh the target table. So I was planning to drop and recreate the table but it does not seem to work. I was able to drop the target table but was unable to recreate it.
I am getting this error
HIVE_PATH_ALREADY_EXISTS: Target directory for table 'CTS.cdc_mart_refresh_op' already exists
What's the appropriate way to handle this issue?
- Do I have to create a new table every time?
- Do I have to delete the folder in S3 bucket and recreate the table
- Or is there a better way to handle the above scenario