Upsert and delete use case

This article walks through a use case on how to do upserts and deletions in Upsolver.

Note: This use case builds on the AWS S3 to Athena simple use case.

Upsolver ingests raw data. You can upsert or delete events in the data lake when compaction takes place by adding an upsert.

When compaction takes place, only the last events per upsert key are kept.

The upsert is the identifier of each row that is updated (e.g. if you wanted to keep only the latest event per host, add the host field as the Upsert Key).

Required Level of Expertise

  • Intermediate

Add upsert and delete keys

1. In your output, click More > Manage Upserts and select your upsert key.

2. Close the upserts window.

Now if you toggle your view from UI to SQL, you will find that the upsert key appears as REPLACE ON DUPLICATE <UPSERT KEY> after the SELECT statement.

3. Now to add a delete key, click on More > Manage Upserts again and then select the Deletions tab.

4. Open the Delete Indicator Field dropdown and select one of the boolean fields for deletion.

5. Run the output to create the table in Athena (the data will arrive within a couple of minutes). See: Running an output

Once the output is Running, to view the data in Athena, select the Properties tab and click on Go to AWS Athena Console in the sidebar.

Last updated