Error: HIVE_PATH_ALREADY_EXISTS: Target directory for table | error while CTAS in AWS Athena

the good New? CTAS in Athena exist.

https://docs.aws.amazon.com/athena/latest/ug/ctas.html

The bad news? the error messages still require some work to make them human readable….

“HIVE_PATH_ALREADY_EXISTS: Target directory for table ‘sampledb.ctas_json_partitioned’ already exists: s3://myBucket/.”

In order to run the CTAS command in Athena, it is always preferable to use an explicit location. If you don’t specify the explicit location, Athena uses this location by default :

s3://aws-athena-query-results-<account>-<region>/<query-name-or-unsaved>/<year>/<month/<date>/<query-id>/.

For example, if we are running following CTAS query with location ‘s3://mybucket/my_ctas_table/’:

CREATE my_ctas_table WITH ( external_location = ‘s3://mybucket/my_ctas_table/’ ) AS SELECT …

 

In this example, we’d first want to make sure the location (exists !) and is empty

To check the location, use the CLI [1] command:

aws s3 ls s3://aws-athena-query-results-783591744239-eu-west-1/Unsaved/2019/02/03/tables/ –recursive

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s