When I run a CREATE TABLE AS SELECT (CTAS) query in Amazon Athena, I get the exception: "HIVE_PATH_ALREADY_EXISTS: Target directory for table".
Resolution
If you use the external_location parameter in the CTAS query, then you must specify an empty Amazon Simple Storage Service (Amazon S3) location. The Amazon S3 location that you use to store the CTAS query output can't contain any data. When you run your CTAS query, the query checks that the path location or prefix in the Amazon S3 bucket has no data. If the Amazon S3 location has data, then the CTAS query doesn't overwrite that data.
To use an Amazon S3 location that already contains data in your CTAS query, delete the data in the key prefix location in the bucket. Otherwise, your CTAS query fails with the exception "HIVE_PATH_ALREADY_EXISTS".
If an existing Athena table points to the Amazon S3 location that you want to use in your CTAS query, then complete the following steps:
- Drop the Athena table.
- Delete the data in the key prefix location of the S3 bucket.
Related information
CTAS table properties
Considerations and limitations for CTAS queries