CODEWITHSUNDEEP
amazon-web-servicesamazon-dynamodbdynamodb-queries
mufumade
mufumade

Reputation: 430

DynamoDB query by 3 fields

Hi I am struggling to construct my schema with three search fields. So the two main queries I will use is:

Get all files from a user within a specific folder ordered by date.

Get all files from a user ordered by date.

Maybe there will be a additional query where I want:

All files from a user within a folder orderd by date and itemType == X

All files from a user orderd by date and itemType == X

So as of that the userID has to be the primaryKey.

But what should I use as my sortKey?. I tried to use a composite sortKey like: FOLDER${folderID}#FILE{itemID}#TIME{$timestamp} As I don't know the itemID I can't use the beginsWith expression right ?

What I could do is filter by beginsWith: folderID but then descending sort by date would not work.

Or should I move away from dynamoDB to a relationalDB with those query requirements in mind?

Upvotes: 1

Views: 1914

Answers (2)

Yakim
Yakim

Reputation: 153

if you are sure about using dynamoDB you should analyze access patterns to this table in advance and chose part key, sort key based on the most frequent pattern. For other patterns, you should add GSI for each pattern. See https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/GSI.html

Usually, if it is about unknown patterns RDBMS looks better, or for HighLoad systems NO_SQL for highload workloads and periodic uploading data to something like AWS RedShift.

Upvotes: 0

Seth Geoghegan
Seth Geoghegan

Reputation: 5747

DynamoDB data modeling can be tough at first, but it sounds like you're off to a good start!

When you find yourself requiring an ID and sorting by time, you should know about KSUIDs. KSUID's are unique IDs that can be lexicographically sorted by time. That means that you can sort KSUIDs and they will order by creation time. This is super useful in DynamoDB. Let's check out an example.

When modeling the one-to-many relationship between Users and Folders, you might do something like this:

enter image description here

In this example, User with ID 1 has three folders with IDs 1, 2, and 3. But how do we sort by time? Let's see what this same table looks like with KSUIDs for the Folder ID.

enter image description here

In this example, I replaced the plain ol' ID with a KSUID. Not only does this give me a unique identifier, but it also ensures my Folder items are sorted by creation date. Pretty neat!

There are several solutions to filtering by itemType, but I'd probably start with a global secondary index with a partition key of USER#user_id#itemType and FOLDER#folder_id as the sort key. Your base table would then look like this

enter image description here

and your index would look like this

enter image description here

This index allows you to fetch all items or a specific folder for a given user and itemType.

These examples might not perfectly match your access patterns, but I hope they can get your data modeling process un-stuck! I don't see any reason why your access patterns can't be implemented in DynamoDB.

Upvotes: 3

Related Questions