UPSERT streaming data to Amazon Athena
In addition to the shopping table, In this section we will create an S3 data source for shipping data. Follow the below steps to to build an output into Amazon Athena.
The output should be an UPSERTing table that contains the most recent shipping status for every order. Sample data:
{
"time": "2020/07/07 01:10:00",
"orderId": "abc",
"trackingNumber": "Z1234ABC",
"shipmentStatus": "SHIPPED"
}
1. Create an Amazon Athena data output
Click on DATA OUTPUT > NEW and SELECT Amazon Athena output. Give the output a NAME > shipping and choose DATA SOURCES > Orders Click on NEXT to continue.

2. Map fields from source to Amazon Athena
Map orderId, trackingNumber, and shipmentStatus to Amazon Athena table by clicking on the + sign next to each field.

3. Define the key field used to UPSERT
Click on MORE > Manage Upserts find the orderid field and check under USE AS KEY. Click on CLOSE. Switch over to the SQL UI and click on PREVIEW to make sure everything looks as expected. Click on RUN.

// Copy and paste the UPSERT SQL to your UI if it looks different
// from your environment.
SELECT data.orderId AS orderid:STRING,
time AS processing_time:TIMESTAMP,
data.shipmentStatus AS shipmentstatus:STRING,
data.trackingNumber AS trackingnumber:STRING
FROM "Orders"
REPLACE ON DUPLICATE data.orderId
4. Start streaming data to Amazon Athena table
Choose the DATABASE NAME > upsolver and TABLE NAME > shipping Click on NEXT.

Leave everything on the Run page as default and click on DEPLOY.

Wait for the data stream start writing to Amazon Athena tables.

4. Verify data on Amazon Athena
Query the shipping data set created by Upsolver (it may take a few minutes before the data is available).
Sample queries:
// What is the current shipping status of order 57459731866?
SELECT shipmentstatus
FROM upsolver.shipping
WHERE orderid = '57459731866'

// What are the tracking numbers of all items that have been shipped?
SELECT trackingnumber
FROM shipping
WHERE shipmentstatus = 'SHIPPED'

Last updated
Was this helpful?