DynamoDB model that supports queries on any given attribute

user_6771050 0
Viewed 267

The application we're designing has a function where users can dynamically add new elements to an entity that then need to be efficiently searched. The number of these elements is essentially unlimited. Our team has been looking at DynamoDB as a data store option, and we've been wrestling with the key/value model and how to get this dynamic data under an index for efficient querying.

I think I have a single-table solution that handles the problem elegantly and also allows for querying on any given attribute in the data store, but am disturbed that I can't find an example of it anywhere else. Hopefully it's not fundamentally flawed in some way - I would appreciate any critique!

The model is essentially the Entity-Attribute-Value approach used for adding dynamic or sparse data to RDBMs. So instead of storing different entities/objects in a DynamoDB table like so:

PK       SK   SK-1   SK-2   SK-3   SK-N...      PK       SK   SK-1   SK-N...
              Key    Key    Key    Key     -->                Name   Money
Entity   Id   Value  Value  Value  Value        Person   22   Fred   30000

... which lets me query things like "all persons where name = Fred" but where you would eventually run out of sort key indexes and you would need to know which index goes with which key before you query, the data could be stored in EAV format like so:

PK      SK & GSI-PK  GSI-SK     PK      SK & GSI-PK  GSI-SK
Id      Entity#Key   Value      22      Person#Name  Fred
Id      Entity#Key   Value  --> 22      Person#Money 30000
Id      Entity#Key   Value      22      Person#Sex   M
Id      Entity#Key   Value      22      Person#DOB   09/00

Now, with one global secondary index (GSI-1 PK over Entity.Key and GSI-1 SK over Value) I can do a range search on any value for any key and get a list of Ids that match. Users can add their attributes or even entirely new entities and have them persisted in a way that's instantly indexed without us having to revamp the DynamoDB schema.

The one major downside to this approach that I can think of is that data returned from a query on an Entity#Key-Value only contains values for that key and the entity Id, not the entire entity. That's fine for charts and graphs but a problem if you want to get a grid-type result with one query. I also worry about hot partition keys on the index, but hopefully we could solve that with intelligent write sharding.

That's pretty much it. With a few tweaks the model can be extended to support the logging of all changes on each key and allow some nice time series queries against those changes, but my question is if anyone has found it useful to take an EAV type approach to a KV store like DynamoDB, or if there's another way to handle querying a dynamic schema?

amazon-dynamodb key-value-store entity-attribute-value nosql key-value
1 Answers

You can have pk as the id of the entity. And then a sort key of {attributeName}. You may still want to have the base entity with fields like createdAt, etc.

So you might have:

PK              SORT               Attributes:  
#Entity#22    #Entity#Details    createdAt=2020     
#Entity#22    #Attribute#name                     key=name    value=Fred   
#Entity#22    #Attribute#money                    key=money   value=30000

To get all the attributes of an entity you simply do a query with no filter of pk={id}. You cannot dynamically sort by every given attribute, this is exactly what DynamoDB is not good at, I repeat! That case is exactly what NOSQL performs poorly at.

What you can do is use streaming to do aggregation. So you can for instance store the top 10 wealthiest people:

PK              SORT               Attributes:      
#Money#Highest    #1               id=#Entity#22    value=30000
#Money#Highest    #2               id=#Entity#52    value=30000

Which you would calculate in a DynamoDB Streams. But you couldn't dynamically index values, DynamoDB works by effectively copying data from one form to another so that it can be efficiently retrieved. So you would be copying your entire database for each new attribute you wanted to search by, or otherwise you would have to use Scans and that wouldn't make any sense to do because you would get no benefit to using DynamoDB if all you ever did was do Scans all the time.

Your processes need to be very well understood to make good use of DynamoDb, if you want to index data at will, and do all sorts of different queries, you probably want an SQL database or elasticsearch.

user_40332920
Related