Structuring Amazon SNS message archives in Amazon Redshift tables - Amazon Simple Notification Service
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).

Structuring Amazon SNS message archives in Amazon Redshift tables

For Amazon Redshift endpoints, Amazon SNS messages are archived as rows in a table. Here's an example of how the data is stored:

Note

In this example, raw message delivery is disabled for the published message. When raw message delivery is disabled, Amazon SNS adds JSON metadata to the message, including these properties:

  • Type

  • MessageId

  • TopicArn

  • Subject

  • Message

  • Timestamp

  • UnsubscribeURL

  • MessageAttributes

For more information about raw delivery, see Amazon SNS raw message delivery.

Although Amazon SNS adds properties to the message using the capitalization shown in this list, column names in Amazon Redshift tables appear in all lowercase characters. 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.

type

messageid

topicarn

subject

message

timestamp

unsubscribeurl

messageattributes

Notification

ea544832-a0d8-581d-9275-108243c46103

arn:aws-cn:sns:us-east-1:111111111111:my-topic

Sample subject

Sample message

2020-12-02T00:33:32.272Z

https://sns.us-east-1.amazonaws.com/?Action=Unsubscribe&SubscriptionArn=arn:aws-cn:sns:us-east-1:111111111111:my-topic:326deeeb-cbf4-45da-b92b-ca77a247813b

{\"my_attribute\":{\"Type\":\"String\",\"Value\":\"my_value\"}}

Notification

ab124832-a0d8-581d-9275-108243c46114

arn:aws-cn:sns:us-east-1:111111111111:my-topic

Sample subject 2

Sample message 2

2020-12-03T00:18:11.129Z

https://sns.us-east-1.amazonaws.com/?Action=Unsubscribe&SubscriptionArn=arn:aws-cn:sns:us-east-1:111111111111:my-topic:326deeeb-cbf4-45da-b92b-ca77a247813b

{\"my_attribute2\":{\"Type\":\"String\",\"Value\":\"my_value\"}}

Notification

ce644832-a0d8-581d-9275-108243c46125

arn:aws-cn:sns:us-east-1:111111111111:my-topic

Sample subject 3

Sample message 3

2020-12-09T00:08:44.405Z

https://sns.us-east-1.amazonaws.com/?Action=Unsubscribe&SubscriptionArn=arn:aws-cn:sns:us-east-1:111111111111:my-topic:326deeeb-cbf4-45da-b92b-ca77a247813b

{\"my_attribute3\":{\"Type\":\"String\",\"Value\":\"my_value\"}}

For more information about fanning out notifications to Amazon Redshift endpoints, see Configuring Amazon SNS message delivery and analysis in Amazon Redshift destinations.