DynamoDb delete with sort key

Question

I have fields below in dynamo dB table

event_on -- string type

user_id -- number type

event name -- string type

Since this table may have multiple records for user_id and event_on is the single field which can be unique so I made it primary key and user_id as sort key

Now I want to delete the all records of a user, so My code is

response = dynamodb.delete_item(
            TableName=events,
            Key={
                "user_id": {"N": str(userId)}
            })

It throwing error

Exception occured An error occurred (ValidationException) when calling the DeleteItem operation: The provided key element does not match the schema

also is there anyway to delete with range

Can someone suggest me what should I have do with dynamodb table structure to make this code work

Thanks,

Seth Geoghegan Seth Geoghegan · Accepted Answer · 2021-03-05T15:40:43

It sounds like you've modeled your data using a composite primary key, which means you have both a partition key and a sort key. Here's an example of what that looks like with some sample data.

In DynamoDB, the most efficient way to access items (aka "rows" in RDBMS language) is by specifying either the full primary key (getItem) or the partition key (query). If you want to search by any other attribute, you'll need to use the scan operation. Be very careful with scan, since it can be a costly way (both in performance and money) to access your data.

When it comes to deletion, you have a few options.

deleteItem - Deletes a single item in a table by primary key.
batchWriteItem - The BatchWriteItem operation puts or deletes multiple items in one or more tables. A single call to BatchWriteItem can write up to 16 MB of data, which can comprise as many as 25 put or delete requests
TimeToLive - You can utilize DynamoDBs Time To Live (TTL) feature to delete items you no longer need. Keep in mind that TTL only marks your items for deletion and actual deletion could take up to 48 hours.

In order to effectively use any of these options, you'll first need to identify which items you want to delete. Because you want to fetch using the value of the sort key alone, you have two options;

Use scan to find the items of interest. This is not ideal but is an option if you cannot change your data model.
Create a global secondary index (GSI) that swaps your partition key and sort key values. This pattern is called an inverted index. This would allow you to identify all items with a given user_id.

If you choose option 2, your data would look like this

This would allow you to fetch all item for a given user, which you could then delete using one of the methods I outlined above.

DynamoDb delete with sort key

3 Answers