CloudTrail Athena: Create Partitions

create-partition.sh

#!/bin/bash

# creates Athena partitions for cloudtrail logs (per account, region, year, month)
# usage:
# ./create-partition.sh my-cloudtrail-bucket 1234566 | pbcopy

BUCKET=$1
ACCOUNT=$2
YEAR=$(date +'%Y')

echo "ALTER TABLE cloudtrail_logs ADD IF NOT EXISTS"

for REGION in $(aws ec2 describe-regions --query "Regions[].{Name:RegionName}" --output text); do
  for MONTH in $(seq -w 1 12); do
    echo "PARTITION (account='$ACCOUNT', region='$REGION', year='$YEAR', month='$MONTH') LOCATION 's3://$BUCKET/AWSLogs/$ACCOUNT/CloudTrail/$REGION/$YEAR/$MONTH/'"
  done
done

echo ";"