HIVE_PATH_ALREADY_EXISTS exception resolution by Bobcares

At Bobcares, we offer solutions for every query, big and small, as a part of our Server Management Service.

Let’s take a look at how our Support Team recently helped a customer by resolving the HIVE_PATH_ALREADY_EXISTS exception while running a CTAS query in Amazon Athena.

What is HIVE_PATH_ALREADY_EXISTS exception?

The HIVE_PATH_ALREADY_EXISTS exception is a common error faced while attempting to run a CTAS query in Amazon Athena.

According to our Support Team, we have to ensure that we specify an empty Amazon S3 empty location while using the external_location parameter in the CTAS query. In other words, the Amazon S3 location should not have any data.

In fact, each time we run a CTAS query, the query verifies the prefix or oath location in the Amazon S3 bucket is devoid of any data. Moreover, if data exist at this location, the query will not overwrite the data. Instead, it returns a “HIVE_PATH_ALREADY_EXISTS” exception.

Fortunately, there is a solution to this specific issue. If the existing Athena table points to an Amazon S3 location you would prefer to use in the CTAS query, then:

1. First, drop the Athena table.
2. Then, delete the data S3 bucket’s key prefix location.

HIVE_PATH_ALREADY_EXISTS exception: Dropping Athena table

We can drop the Athena table with the following syntax where table_name is the name of the table to be dropped:

DROP TABLE [IF EXISTS] table_name

For instance:

DROP TABLE fulfilled_orders
DROP TABLE IF EXISTS fulfilled_orders

Furthermore, when we drop an external table, the data remains intact. This is because the tables in Athena are EXTERNAL.

The IF EXISTS parameter suppresses the error if the table does not exist.

In case we are using Athena console query editor and the table name consists of special characters besides “_”, we can use backticks instead as seen below:

DROP TABLE `my-database-01.my-table`

If we are using JDBC connector, we can forego the backticks as seen below:

DROP TABLE my-database-01.my-table

HIVE_PATH_ALREADY_EXISTS exception: Deleting data in S3 bucket’s key prefix location

We can either delete one or more objects directly from Amazon S3 via the Amazon S3 console, AWS CLI, AWS SDKs, or REST API. Furthermore, it is a good idea to delete objects no longer requires to avoid S3 bucket storage costs.

For instance, our Support Techs recommend deleting log files that we do not need anymore. We can also set up a lifecycle rule in order to automatically delete objects like log files.
Amazon S3 offers the following options while deleting data:

• Deleting a single object:
  We can use the DELETE API Option to delete a single object in a single HTTP request.
• Deleting multiple objects:
  We can use the Multi-Object Delete API to delete up to 1000 objects in a single HTTP request.

How to delete objects from a version-enabled bucket

If the bucket is version-enabled, the same objects can exist in multiple versions in the bucket. The delete API offers the following options if we are working with version-enabled buckets:

• Specifying a non-versioned delete request:
  If we mention only the object’s key and not the version ID, Amazon S3 creates a delete market and then returns its version ID> This allows our object to disappear from the bucket.
• Specifying a non-versioned delete request:
  If we mention both the key and the version, one of the two outcomes will occur:
  • In case the version ID maps to a particular object version, Amazon S3 will delete the specific version of the object.
  • If the version ID maps to the object’s delete marker, Amazon S3 deletes the delete marker. This results in the object reappearing in the bucket.

How to delete objects from an MFA-enabled bucket

According to our Support Techs, we have to remember the following points while deleting objects from a multi-factor authentication-enabled bucket:

• The token will always fail if we provide an invalid MFA token.
• The request will fail if we have an MFA_enabled bucket and we make a versioned delete request without offering a valid MFA token.

  Furthermore, while using the Multi-Object Delete API, the request will fail if we do not offer an MFA token if any of the deletes are a versioned delete request.

However, the request succeeds in the following scenarios:

• If we possess an MFA-enabled bucket and we make a non-versioned delete request without providing an MFA token, the delete succeeds.
• If we have a Multi-Object Delete request that specific only non-versioned objects for deleting from an MFA-enabled bucket and we don’t offer an MFA token, the deletions succeed.

[Need assistance with another query? We are just a click away.]

Conclusion

To conclude, the skilled Support Engineers at Bobcares demonstrated how to resolve the “HIVE_PATH_ALREADY_EXISTS” exception when running a CTAS query in Amazon Athena.