如何批量删除AWS Athena中的所有数据库？含长随机名测试库删除需求

Absolutely, there's a way to skip the tedious manual deletion of those random-named test databases—let's use AWS CLI with simple scripts to get this done efficiently. Here's a step-by-step breakdown:

Prerequisites #

• Make sure you have the AWS CLI installed and configured with credentials that include these permissions:
  • athena:ListDatabases (to retrieve your database list)
  • glue:DeleteDatabase and glue:DeleteTable (Athena databases/tables are stored in the Glue Data Catalog, so these permissions are required for deletions)

Step 1: Identify Your Target Test Databases #

First, list all your Athena databases and filter to target only your random-string test ones. Run this command (adjust the regex ^[a-z0-9]{16,}$ to match your test DB naming pattern—this example targets 16+ character alphanumeric strings):

aws athena list-databases --catalog-name AwsDataCatalog --query 'DatabaseList[].Name' --output text | grep -E '^[a-z0-9]{16,}$'

Critical Check: Before deleting, verify the output only includes test databases you want to remove. Save the list to a file for review if needed:

aws athena list-databases --catalog-name AwsDataCatalog --query 'DatabaseList[].Name' --output text | grep -E '^[a-z0-9]{16,}$' > test-dbs.txt

Step 2: Batch Delete Script (Bash) #

If your test databases have tables, you'll need to delete tables first (Glue won't let you delete a non-empty database). Use this script to handle both tables and databases:

# Fetch target test databases
DB_NAMES=$(aws athena list-databases --catalog-name AwsDataCatalog --query 'DatabaseList[].Name' --output text | grep -E '^[a-z0-9]{16,}$')

# Loop through each database to delete tables, then the database itself
for DB in $DB_NAMES; do
  echo "Starting cleanup for database: $DB"
  
  # List and delete all tables in the database
  TABLE_NAMES=$(aws glue get-tables --database-name "$DB" --query 'TableList[].Name' --output text)
  for TABLE in $TABLE_NAMES; do
    echo "Deleting table: $TABLE"
    aws glue delete-table --database-name "$DB" --name "$TABLE"
  done
  
  # Delete the empty database
  echo "Deleting database: $DB"
  aws glue delete-database --name "$DB"
done

If your test databases are already empty, you can simplify the script to just delete the databases directly.

Step 3: PowerShell Alternative (Windows Users) #

If you're on Windows, use this PowerShell script instead:

# Get test databases (adjust regex to match your naming pattern)
$dbNames = aws athena list-databases --catalog-name AwsDataCatalog --query 'DatabaseList[].Name' --output json | ConvertFrom-Json | Where-Object { $_ -match '^[a-z0-9]{16,}$' }

foreach ($db in $dbNames) {
    Write-Host "Cleaning up database: $db"
    
    # Delete tables in the database
    $tableNames = aws glue get-tables --database-name $db --query 'TableList[].Name' --output json | ConvertFrom-Json
    foreach ($table in $tableNames) {
        Write-Host "Deleting table: $table"
        aws glue delete-table --database-name $db --name $table
    }
    
    # Delete the database
    Write-Host "Deleting database: $db"
    aws glue delete-database --name $db
}

Key Notes #

• Double-Check Permissions: If you get access denied errors, update your IAM policy to include the required Glue/Athena permissions mentioned earlier.
• Avoid Accidental Deletion: Always verify the list of databases before running the delete script—you don't want to wipe production data by mistake!

内容的提问来源于stack exchange，提问作者Andy