Querying the archive with Athena

Firehose writes every SES event from our configuration sets, in full, to the archive bucket in your account, under ses/yyyy/MM/dd/. The template creates a Glue database and a table, ses_events, over it, with partition projection by day, so you can query it with Amazon Athena straight away. Objects expire after the stack’s ArchiveRetentionDays (400 by default).

The archive is the full event, so unlike our copy it includes recipient addresses, subjects and headers. It’s your data, in your account: we never read it.

Find the database name

The database is named rhino_relay_ plus part of your stack’s ID. Read it from the stack’s GlueDatabaseName output:

aws cloudformation describe-stacks --stack-name rhino-relay \
  --query "Stacks[0].Outputs[?OutputKey=='GlueDatabaseName'].OutputValue" --output text

Query it

In the Athena console, choose the database, then run queries on ses_events. Always filter on dt, the day partition, so Athena reads only the days you ask about.

Events by type, for a month:

SELECT detail.eventType AS event, count(*) AS events
FROM ses_events
WHERE dt BETWEEN '2026/10/01' AND '2026/10/31'
GROUP BY 1
ORDER BY 2 DESC;

Hard bounces for one campaign, with the reason each mail server gave. Our sends carry a campaign message tag with the campaign ID:

SELECT r.emailAddress, detail.bounce.bounceSubType, r.diagnosticCode
FROM ses_events
CROSS JOIN UNNEST(detail.bounce.bouncedRecipients) AS t(r)
WHERE dt BETWEEN '2026/10/01' AND '2026/10/07'
  AND detail.eventType = 'Bounce'
  AND detail.bounce.bounceType = 'Permanent'
  AND element_at(detail.mail.tags, 'campaign')[1] = 'cm_01J9Z8X7W6V5T4S3R2Q1P0N9M8';

Clicks by link, humans only:

SELECT detail.click.link, count(*) AS clicks
FROM ses_events
WHERE dt >= '2026/10/01'
  AND detail.eventType = 'Click'
  AND detail.click.isBotEvent <> 'Likely'
GROUP BY 1
ORDER BY 2 DESC;

Or run a query with the AWS CLI:

aws athena start-query-execution --work-group primary \
  --query-execution-context Database=rhino_relay_1a2b3c4d \
  --query-string "SELECT detail.eventType, count(*) FROM ses_events WHERE dt >= '2026/10/01' GROUP BY 1"

Replace the database name with yours. The primary work group needs a query result location: set one in the Athena console first.

Check it worked: the query returns rows for the days your campaigns sent. Test sends are in the archive too, with the message tag test set to true.

Columns

ses_events has the EventBridge envelope (version, id, detail_type, source, account, time, region, resources) and detail, the SES event: eventType, mail (with messageId, destination, timestamp and tags), and one of bounce, complaint, delivery, open, click, reject, failure or deliveryDelay. See Amazon SES event data for every field. Athena charges for the data each query scans; see Athena pricing.