How do I troubleshoot the "HIVE_PATH_ALREADY_EXISTS" exception when I run a CTAS query in Amazon Athena?

2 minute read
0

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:

  1. Drop the Athena table.
  2. Delete the data in the key prefix location of the S3 bucket.

Related information

CTAS table properties

Considerations and limitations for CTAS queries

AWS OFFICIAL
AWS OFFICIALUpdated 7 months ago