How do I resolve 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 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:

  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 3 years ago