Analyzing Amazon SNS messages stored in Amazon S3 using Athena - Amazon Simple Notification Service
Example query

Analyzing Amazon SNS messages stored in Amazon S3 using Athena

This page describes how to analyze Amazon SNS messages sent through Amazon Data Firehose delivery streams to Amazon Simple Storage Service (Amazon S3) destinations.

To analyze SNS messages sent through Firehose delivery streams to Amazon S3 destinations

  1. Configure your Amazon S3 resources. For instructions, see Creating a bucket in the Amazon Simple Storage Service User Guide and Working with Amazon S3 Buckets in the Amazon Simple Storage Service User Guide.

  2. Configure your delivery stream. For instructions, see Choose Amazon S3 for Your Destination in the Amazon Data Firehose Developer Guide.

  3. Use Amazon Athena to query the Amazon S3 objects using standard SQL. For more information, see Getting Started in the Amazon Athena User Guide.

Example query

For this example query, assume the following:

  • Messages are stored in the notifications table in the default schema.

  • The notifications table includes a timestamp column with a type of string.

The following query returns all SNS messages received in the specified date range:

SELECT * 
FROM default.notifications
WHERE from_iso8601_timestamp(timestamp) BETWEEN TIMESTAMP '2020-12-01 00:00:00' AND TIMESTAMP '2020-12-02 00:00:00';