How to Delete a Non-Empty Database in AWS Athena

Trying to delete an unnecessary database in AWS Athena can fail.

This is a short note on the cause and the fix.

Error

The following error appears and the database cannot be deleted.

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask.

MetaException(message:Unable to drop database: InvalidOperationException(message:Database database_name is not empty.))

The database still contains tables or views, so Athena will not delete it as is.

Fix

Use the CASCADE option to delete the tables and views in the database together with the database itself.

DROP SCHEMA IF EXISTS database_name CASCADE;

Use DROP SCHEMA instead of DROP DATABASE, and add CASCADE.

That deletes every object inside the database.

Caution

If you use CASCADE, every table and view inside the database is deleted.

Make sure you are not removing an important database by mistake.

Reference

• DROP DATABASE - Amazon Athena

Summary

To delete a non-empty database in Athena, use DROP SCHEMA ... CASCADE.

The fix is written plainly in the documentation, but the error message does not always make the answer obvious, so I am leaving it here as a note.