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".
If you use the external_location parameter in the CTAS query, then be sure to specify an Amazon Simple Storage Service (Amazon S3) location that's empty. The Amazon S3 location that you use to store the CTAS query results must have no 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 already has data, the query doesn't overwrite the data.
To use the Amazon S3 location that has 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 is pointing to the Amazon S3 location that you want to use in your CTAS query, then do the following:
- Drop the Athena table.
- Delete the data in the key prefix location of the S3 bucket.
CTAS table properties
Considerations and limitations for CTAS queries