Analyzing Amazon SNS messages stored in Amazon Redshift destinations - Amazon Simple Notification Service
Example query
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Analyzing Amazon SNS messages stored in Amazon Redshift destinations

This page describes how to analyze Amazon SNS messages sent through Amazon Data Firehose delivery streams to Amazon Redshift destinations.

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

  1. Configure your Amazon Redshift resources. For instructions, see Getting started with Amazon Redshift in the Amazon Redshift Getting Started Guide.

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

  3. Run a query. For more information, see Querying a database using the query editor in the Amazon Redshift Management Guide.

Example query

For this example query, assume the following:

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

  • The Timestamp property from the SNS message is stored in the table's timestamp column with a column data type of timestamptz.

    Note

    To transform the JSON metadata for the Amazon Redshift endpoint, you can use the SQL COPY command. For more information, see Copy from JSON examples and Load from JSON data using the 'auto ignorecase' option in the Amazon Redshift Database Developer Guide.

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

SELECT *
FROM public.notifications
WHERE timestamp > '2020-12-01T09:00:00.000Z' AND timestamp < '2020-12-02T09:00:00.000Z';