How to fix Error: Error creating Athena Database: AlreadyExistsException in Terraform

Log in to the AWS Management Console and navigate to Amazon Athena. Check if the database you're trying to create already exists in the Data Catalog. Note the exact name and case used by AWS. This will help you understand if case sensitivity is the issue.

If the database already exists but is not in your Terraform state, import it using:

terraform import aws_athena_database.example my_database_name

Replace my_database_name with your actual database name. This adds the existing database to your Terraform state without trying to recreate it.

Update your Terraform configuration to use lowercase database names, since Athena automatically converts all database names to lowercase. For example:

resource "aws_athena_database" "example" {
  name   = "my_database_name"  # Use lowercase
  bucket = aws_s3_bucket.example.id
}

This prevents case sensitivity mismatches between Terraform and AWS.

If you want Terraform to create a fresh database, manually delete the existing one from the AWS Athena console (or via AWS CLI). First, you must delete all tables in the database:

aws athena list-table-metadata --catalog-name hive --database-name my_database_name

Then delete the database:

aws glue delete-database --name my_database_name

After deletion, run terraform apply again.

Update your aws_athena_database resource to include force_destroy to ensure Terraform can destroy the database even if it contains tables:

resource "aws_athena_database" "example" {
  name          = "my_database_name"
  bucket        = aws_s3_bucket.example.id
  force_destroy = true
}

Note: force_destroy = true will delete all tables in the database when you run terraform destroy. Tables are not recoverable.

Ensure your AWS credentials have the necessary permissions. Terraform needs:
- glue:CreateDatabase
- glue:GetDatabase
- glue:DeleteDatabase
- s3:GetBucketLocation
- s3:ListBucket
- s3:GetObject

If permissions are restricted, the error may not clearly indicate the real issue. Add these permissions to your IAM policy if missing.