Testing and querying the configuration

Open the Topics page of the Amazon SNS console.

Choose the ticketTopic topic.

Choose Publish message.

On the Publish message to topic page, enter the following for the message body. Add a newline character at the end of the message.

{"BookingDate":"2020-12-15","BookingTime":"2020-12-15 04:15:05","Destination":"Miami","FlyingFrom":"Vancouver","TicketNumber":"abcd1234"}

Keep all other options as their defaults.

Choose Publish message.

For more information on publishing messages, see Amazon SNS message publishing.

After the delivery stream interval of 60 seconds, open the Amazon Simple Storage Service (Amazon S3) console and choose the Amazon S3 bucket that you created initially.

The published message appears in the bucket.

Open the Amazon Athena console.

Run a query.

For example, assume that the notifications table in the default schema contains the following data:

{"BookingDate":"2020-12-15","BookingTime":"2020-12-15 04:15:05","Destination":"Miami","FlyingFrom":"Vancouver","TicketNumber":"abcd1234"}
{"BookingDate":"2020-12-15","BookingTime":"2020-12-15 11:30:15","Destination":"Miami","FlyingFrom":"Omaha","TicketNumber":"efgh5678"}
{"BookingDate":"2020-12-15","BookingTime":"2020-12-15 3:30:10","Destination":"Miami","FlyingFrom":"NewYork","TicketNumber":"ijkl9012"}
{"BookingDate":"2020-12-15","BookingTime":"2020-12-15 12:30:05","Destination":"Delhi","FlyingFrom":"Omaha","TicketNumber":"mnop3456"}

To find the top destination, run the following query:

SELECT destination
FROM default.notifications
GROUP BY destination
ORDER BY count(*) desc
LIMIT 1;

To query for tickets sold during a specific date and time range, run a query like the following:

SELECT * 
FROM default.notifications 
WHERE bookingtime 
  BETWEEN TIMESTAMP '2020-12-15 10:00:00' 
  AND TIMESTAMP '2020-12-15 12:00:00';

You can adapt both sample queries for your own needs. For more information on using Athena to run queries, see Getting Started in the Amazon Athena User Guide.