This is the place for your DynamoDB questions.
A few notes to make this more helpful to you and to others:
A few notes to make this more helpful to you and to others:
- When asking your question, include enough context to make it helpful. If it's a data modeling question, what's the access pattern you're trying to solve? If it's an error or unexpected behavior, include examples of code or errors.
- If your question is directly related to a specific programming language, check out the language-specific spaces instead.
- If your question is directly related to The DynamoDB Book, please use the space for The DynamoDB Book.
I am still learning Single Table Design, (Yes, I bought the book and video! and they are fantastic! 🤩 ) and I have been trying to apply design patterns I learned in my Facebook Ads project, an app that uses Facebook's Marketing API and all of its items are in a single table.
Details:
https://docs.google.com/spreadsheets/d/1GU4urcOvH2oDaMwjVO3mPqgOWDp02pXKDUPXSIjf25M/edit?usp=sharing
For attributes that are only applicable to certain items, such as AccessToken for User and System User in the table above, is it not encouraged to have them consolidated into the Settings attribute if this attribute applies to all other items(or most of them) in the table to minimize the number of attributes defined for this Table? For Settings, I use an M structured data that is meaningful to the context of the item represented by the row to which it belongs. They do not necessarily have the same key/value pair for different items.
Or since each item can have its own distinct attributes, trying to make these attributes look column-wise arranged in the right place (since it is not an RDBMS) and look more normalized as a table definition is discouraged?
Details:
https://docs.google.com/spreadsheets/d/1GU4urcOvH2oDaMwjVO3mPqgOWDp02pXKDUPXSIjf25M/edit?usp=sharing
For attributes that are only applicable to certain items, such as AccessToken for User and System User in the table above, is it not encouraged to have them consolidated into the Settings attribute if this attribute applies to all other items(or most of them) in the table to minimize the number of attributes defined for this Table? For Settings, I use an M structured data that is meaningful to the context of the item represented by the row to which it belongs. They do not necessarily have the same key/value pair for different items.
Or since each item can have its own distinct attributes, trying to make these attributes look column-wise arranged in the right place (since it is not an RDBMS) and look more normalized as a table definition is discouraged?
Thank you for your insightful comments and opinions. I haven't watched the playback of Rick Houlihan's you are referring to, so I will. 😄
For an attribute like AccessToken, which is indispensably significant for functions that app won't work without, I agree that it deserves its spot as a distinct attribute by itself; therefore, it shouldn't be a part of the Settings.
It would be nice to know when to use a Map vs. when not to use a Map.
For an attribute like AccessToken, which is indispensably significant for functions that app won't work without, I agree that it deserves its spot as a distinct attribute by itself; therefore, it shouldn't be a part of the Settings.
It would be nice to know when to use a Map vs. when not to use a Map.
By the way, I just noticed the AccessToken together with its adjacent attribute ExpiresIn can be represented as a Map, because these two attributes are almost always fetched together as the validation process of the AccessToken precedes the app restoring it in memory for use.
Maybe I should rename this attribute to AccessGrant and make AccessToken and ExpiresIn as key/value pair entries of a Map.
Maybe I should rename this attribute to AccessGrant and make AccessToken and ExpiresIn as key/value pair entries of a Map.
What data modelling approaches have people taken for requirements such as:
Show me all people in NY, who tutor Guitar, who are of an EXPERIENCED level, and that cost between $60 and $80 per hour to hire; ordering either by price or community rating...or locality (eek!).
and combinations thereof.
Is crazy denormalization the answer? Or breaking rank with single table design best practice and, say, taking the results of an initial query as input to potentially multiple batch-gets? (The latter option presumably breaks down when reliable pagination is also a requirement.)
Has anyone been brave enough to implement a z-order indexing strategy. It's certainly an intriguing, if mind-bending, solution!
Or is this a point at which such searches are farmed out to an alternative service (CloudSearch/Elasticsearch)?
Is crazy denormalization the answer? Or breaking rank with single table design best practice and, say, taking the results of an initial query as input to potentially multiple batch-gets? (The latter option presumably breaks down when reliable pagination is also a requirement.)
Has anyone been brave enough to implement a z-order indexing strategy. It's certainly an intriguing, if mind-bending, solution!
Or is this a point at which such searches are farmed out to an alternative service (CloudSearch/Elasticsearch)?
I'm wrestling with a similar question in my data modeling, albeit to a smaller degree. It can be hard to determine if modeling challenges reveal limitations of DDB *or* limitations in experience!
The Z-index approach is certainly interesting, but seems like a *very* un-fun data modeling exercise! I can't imagine maintaining that. I wonder how many of these implementations exist in the wild.
I too am interested to hear how others are managing multi-faceted search like you describe. The use case you describe seems fairly basic from a users perspective. However, modeling ad-hoc queries like this does not seem like DDB's sweet spot.
A tool like Elasticsearch may provide an easier path forward, as it's specifically made to support search.
The Z-index approach is certainly interesting, but seems like a *very* un-fun data modeling exercise! I can't imagine maintaining that. I wonder how many of these implementations exist in the wild.
I too am interested to hear how others are managing multi-faceted search like you describe. The use case you describe seems fairly basic from a users perspective. However, modeling ad-hoc queries like this does not seem like DDB's sweet spot.
A tool like Elasticsearch may provide an easier path forward, as it's specifically made to support search.
What about something like this:
I imagine you have items that represent Tutors:
I imagine you have items that represent Tutors:
pk sk cost level ... TUTOR#<id> TUTOR#<id> 80 EXPERIENCED
For the specific access pattern you described, you could define a secondary index as follows:
GSI1PK GSI1SK rating TUTORS#NY BEGINNER#GUITAR#040 ... TUTORS#NY EXPERIENCED#GUITAR#050 TUTORS#NY EXPERIENCED#GUITAR#070 TUTORS#NY EXPERIENCED#GUITAR#090 TUTORS#NY EXPERIENCED#GUITAR#100
The GSIPK pattern reduces the scope to NY Tutors, and the SK groups <Experience level>#<instrument>#<hourly cost>. If you wanted to find experienced guitar tutors in NY between $60-$80/hour, you could query the index with a key condition on the SK between EXPERIENCED#GUITAR#060 and EXPERIENCED#GUITAR#080.
I assume this will narrow your result set enough to either filter the remaining criteria (community rating or locale), or just send the results to the client and let them filter it out.
· 1 reply
Ordering your attributes is important, and you're still limited to certain (efficient) queries. For example, finding all guitar tutors in NY would be very inefficient.
That's where the Z index seems interesting. But (only guessing here), it looks best suited to certain types of data. I have a situation where I want to query a combination of uuids, but interleaving long strings sounds like a worst-case scenario for the search space. But for types like these it might work great.
I wonder if anyone's put together an open source library for calculating the z index, `isRelevant`, and `nextJumpIn` functions yet. It seems like those could be generic and could be a fun weekend (or year-long) project.
That's where the Z index seems interesting. But (only guessing here), it looks best suited to certain types of data. I have a situation where I want to query a combination of uuids, but interleaving long strings sounds like a worst-case scenario for the search space. But for types like these it might work great.
I wonder if anyone's put together an open source library for calculating the z index, `isRelevant`, and `nextJumpIn` functions yet. It seems like those could be generic and could be a fun weekend (or year-long) project.
When using an adjacency list to model a many-to-many relationship, what is the expected way to handle updates or deletions of the related data?
In the adjacency list movie/actor example in chapter 12, how would you update an actor's name or remove an actor from all movies?
In the adjacency list movie/actor example in chapter 12, how would you update an actor's name or remove an actor from all movies?
Luckily that example has a GSI on SK, but, because it uses the actor's name in the primary key, I believe you would need to query all their records by SK = ACTOR#TomHanks, then delete and recreate each record using the new name.
Ideally, your primary key values won't change very often, if ever.
Ideally, your primary key values won't change very often, if ever.
As far as I know, you cannot change the value of a primary key. So you could update the ActorName field using their new name, but not PK or SK.
In that example, if an actor changed their name, you would need to find all records using their old name, delete, and recreate them using the new name.
If I'm wrong or someone knows of a better way, please correct me. I'm here to keep learning. =)
In that example, if an actor changed their name, you would need to find all records using their old name, delete, and recreate them using the new name.
If I'm wrong or someone knows of a better way, please correct me. I'm here to keep learning. =)
· 2 replies
In Chapter 13 of the Dynamo DB Book, Alex DeBrie gives an example of an access pattern that relies on the arrangement of sort keys (section 13.2.2):
I understand this specific example may have been contrived to illustrate the pattern, but it's not clear to me when this pattern would be useful.
In this example, if you were trying to get all ISSUES for a given REPO, why not query for items where the SK begins with "ISSUES#". This access strategy is interesting, but I'm not clear about what it gives us what we don't already get with a "begins with" constraint on the SK.
I understand this specific example may have been contrived to illustrate the pattern, but it's not clear to me when this pattern would be useful.
In this example, if you were trying to get all ISSUES for a given REPO, why not query for items where the SK begins with "ISSUES#". This access strategy is interesting, but I'm not clear about what it gives us what we don't already get with a "begins with" constraint on the SK.
After thinking about this more, I think I've come up with a use case for this pattern.
In this particular example, it's likely that you'd want to get both issues *and* repo information in a single query. Something a query limiting SK to ISSUE#<id> wouldn't achieve by itself. By structuring the query this way, you're able to fetch both sides of the one-to-many relationship between repos and issues in a single call. Querying with SK begins_with ISSUE# only gets you the "many" side of the relationship.
In this particular example, it's likely that you'd want to get both issues *and* repo information in a single query. Something a query limiting SK to ISSUE#<id> wouldn't achieve by itself. By structuring the query this way, you're able to fetch both sides of the one-to-many relationship between repos and issues in a single call. Querying with SK begins_with ISSUE# only gets you the "many" side of the relationship.
· 1 reply
First time DynamoDB user checking-in for feedback about my first DynamoDB data modeling attempt! The DynamoDB Book has been an amazing resource, I'm thankful it exists!
I am building an application that collects and presents running race results (e.g. 5k, 10k, marathon, etc). This is how I'm modeling the Race, User and Result entities.
One of my basic access patterns is to fetch a sorted list of results by race. I've implemented this access pattern by defining a secondary index on the RESULT entity, which includes a composite sort key. The index GSI1PK is RACE#<race_id>, the GSI1SK is TIME#<time_in_seconds>. This allows me to get sorted race results for a specific race by time:
This covers the basic needs of my application. However, have additional access patterns that require fetching results by gender (top male and top female results per race) and age group results per race (14 years old and under, 15-19, 20-24, etc.). This is where I'm a bit stuck and could use some guidance. My thoughts on a few options:
I am building an application that collects and presents running race results (e.g. 5k, 10k, marathon, etc). This is how I'm modeling the Race, User and Result entities.
One of my basic access patterns is to fetch a sorted list of results by race. I've implemented this access pattern by defining a secondary index on the RESULT entity, which includes a composite sort key. The index GSI1PK is RACE#<race_id>, the GSI1SK is TIME#<time_in_seconds>. This allows me to get sorted race results for a specific race by time:
This covers the basic needs of my application. However, have additional access patterns that require fetching results by gender (top male and top female results per race) and age group results per race (14 years old and under, 15-19, 20-24, etc.). This is where I'm a bit stuck and could use some guidance. My thoughts on a few options:
- Push this off to the client - It would be simpler if I made this a client-side concern. If my data layer returns all the required information about a race result (age/gender/time) it would be trivial to create arbitrary groupings.
- Filter existing results - each RESULT can include a user gender/age attribute, which I could then filter. This would allow me to take advantage of my existing model without creating new indexes or other complicated trickery :)
- Create additional indexes - Taking advantage of gender being an enumeration (M or F) I could create a composite sort key that includes gender. e.g. TIME#M#1200. I could also treat age groups to enumerated values (e.g. 2024 for 20-24). I could also combine the enumerations as well (e.g. 2024M and 2024M, etc). However, this is starting to feel complex.
- Create a complex attribute on the RACE item that stores age group/gender lists/maps. I don't have any access patterns on the values in age/gender groupings. I'm not sure about this pattern for this use case, since a race has an unbounded list of results.
I'm new to modeling in DynamoDB and I feel like I'm wading in an ocean of possibilities! Any advice or guidance would be greatly appreciated!
To elaborate on option 3 from above, I've come up with a compound sort key that encapsulates gender, age group and performance times: <gender_m_or_f>#<age_group>#<time_in_seconds>. In this example, age groups would be represented as 4 digits that provide a natural sort order:
14 and under -> 0014
15-19 -> 1519
20-24 -> 2024
25-29 -> 2529
...
94-99 ->9499
Therefore, my list of results would be sorted/grouped as follows:
This pattern lets me fetch grouped and sorted results in one query. Although, I lose the non-grouped, sorted results I achieved with my former TIME#<time_in_seconds> sort key example.
I suppose I could just use 2 GSI's to support these access patterns, but am not sure if I'm approaching this correctly.
This pattern lets me fetch grouped and sorted results in one query. Although, I lose the non-grouped, sorted results I achieved with my former TIME#<time_in_seconds> sort key example.
I suppose I could just use 2 GSI's to support these access patterns, but am not sure if I'm approaching this correctly.
Hi Seth, personally, I think your answer will depend a bit on the scale. How many people do you expect for each race?
If you're expecting a few hundred records, then returning all of them to the client makes sense to me. A few thousand seems doable as well. If you're looking at many thousands of records, you might think about additional server-side filtering. I'm assuming a race won't have millions of records.
For example, if you have a page that shows only the women's results, you could query by pk: RACE#<id> with a QueryFilter on gender = 'F'. AWS still charges you for the men, but the client only gets what they asked for.
It may also make sense to create some rollups for things like number of participants by age group. When you add the runner's final score, also do a lookup and update a shared record for their age group, gender, whatever. These could look something like:
pk: RACE#<id>, sk: STATS#AGE, age: { 7: 1, 18: 240, 19: 280, 20: 212 }
pk: RACE#<id>, sk: STATS#GENDER, gender: { 'male': 2455, 'female': 2674 }
These metadata-level results can answer the general questions where you don't need a specific user, just the counts across all users.
Just some ideas, I'm curious how others would handle this.
If you're expecting a few hundred records, then returning all of them to the client makes sense to me. A few thousand seems doable as well. If you're looking at many thousands of records, you might think about additional server-side filtering. I'm assuming a race won't have millions of records.
For example, if you have a page that shows only the women's results, you could query by pk: RACE#<id> with a QueryFilter on gender = 'F'. AWS still charges you for the men, but the client only gets what they asked for.
It may also make sense to create some rollups for things like number of participants by age group. When you add the runner's final score, also do a lookup and update a shared record for their age group, gender, whatever. These could look something like:
pk: RACE#<id>, sk: STATS#AGE, age: { 7: 1, 18: 240, 19: 280, 20: 212 }
pk: RACE#<id>, sk: STATS#GENDER, gender: { 'male': 2455, 'female': 2674 }
These metadata-level results can answer the general questions where you don't need a specific user, just the counts across all users.
Just some ideas, I'm curious how others would handle this.
I have been implementing some DYNDB calls straight from GraphQL via Appsync. The request resolvers are simple enough. What I want to do is return some decent errors to the client bubbling up from the update conditions not being met.
I can't seem to find anything online. Has anyone any examples that they are able to share.
So how would I respond that the PAYMETHOD has already been deleted?
I can't seem to find anything online. Has anyone any examples that they are able to share.
So how would I respond that the PAYMETHOD has already been deleted?
#if ($util.matches("^PAYMETHOD#.*", $ctx.args.id))
{
"version" : "2017-02-28",
"operation" : "UpdateItem",
"key" : {
"id" : { "S" : "UPF#${ctx.args.regionId}#${ctx.args.appId}#${ctx.identity.sub}" },
"composite" : { "S" : "${ctx.args.id}" }
},
"update" : {
"expression" : "SET entity = :deleteState",
"expressionValues" : {
":deleteState" : { "S" : "DELETED" }
}
},
"condition" : {
"expression" : "entity <> :deleteState",
"expressionValues" : {
":deleteState" : { "S" : "DELETED" }
}
}
}
#else
$util.error("You can only delete a PAYMETHOD# record with this query")
#end
with the response mapping template set as:
$util.toJson($ctx)
I get this which is progress.
{
"data": {
"ddbTest": null
},
"errors": [
{
"path": [
"ddbTest"
],
"data": null,
"errorType": "DynamoDB:ConditionalCheckFailedException",
"errorInfo": null,
"locations": [
{
"line": 2,
"column": 3,
"sourceName": null
}
],
"message": "The conditional request failed (Service: AmazonDynamoDBv2; Status Code: 400; Error Code: ConditionalCheckFailedException; Request ID: 1C3UB5EU4J4FFCF397K66FCK3FVV4KQNSO5AEMVJF66Q9ASUAAJG)"
}
]
}So I can pick up the item was not set as DELETED as the errorType tells me that and I can handle that in the response VTL template. I will put that on my inifiniteToDoList but would appreciate if anyone has a more sophisticated response template that they can share.
I recently watched these two excellent videos featuring Rick Houlihan, demonstrating the NoSQL Workbench for DynamoDB tool:
- Built With DynamodDB ep. 11, with Edin Zulich (sep 2019)
- AWS Virtual Workshop, with Sean Shriver (jun 2020)
In each of these Rick demonstrates an elaborate and fascinating Inventory/Customer/Orders management model that I really would like to play around with in the tool, to make sense of DynamoDB. In both videos it was mentioned the models would be shared, but it seems it hasn't so far.
I just tried to reach them by email, but I stumbled upon this community so I wonder: Does anyone here know where we can find those JSON models to import in the Workbench?
Ps: I've checked the models that come with the Workbench, but they don't have the level of complexity I'm dealing with in my app.
Thank you, nice to be here!
Ps: Sean just kindly replied the email I sent with the model created by Rick Houlihan in his “AWS Virtual Workshop”, so here it is for those who are interested: https://a.co/b8HCwEh
(Still, if any of you have the other model from the talk with Edin, it would be awesome.)
(Still, if any of you have the other model from the talk with Edin, it would be awesome.)
## Context
* Chapter: 3. Advanced Concepts
* Section: 3.6. Overloading keys and indexes
The example for:
> A table with both Organization and User entities might look as follows:
## Question
Will this not lead to hot partitions too, since **all users** in an organization will be in a single partition?
* Chapter: 3. Advanced Concepts
* Section: 3.6. Overloading keys and indexes
The example for:
> A table with both Organization and User entities might look as follows:
## Question
Will this not lead to hot partitions too, since **all users** in an organization will be in a single partition?
I like the fact that Alex DeBrie recommends not to use an ORM/ODM. Under exceptions to this rule, we have:
> The first exception is for tools like the Document Client provided by AWS in their Node.js SDK. The Document Client is a thin wrapper on top of the core AWS SDK.”
AWS SDK v2 (for Java) has an [enhanced client](https://aws.amazon.com/blogs/developer/introducing-enhanced-dynamodb-client-in-the-aws-sdk-for-java-v2/).
I wonder if anyone has used it and have feedback? Is this more or less a replacement for the earlier DDBMapper (in which I would not use it) or more like the Document API for Node.js?
> The first exception is for tools like the Document Client provided by AWS in their Node.js SDK. The Document Client is a thin wrapper on top of the core AWS SDK.”
AWS SDK v2 (for Java) has an [enhanced client](https://aws.amazon.com/blogs/developer/introducing-enhanced-dynamodb-client-in-the-aws-sdk-for-java-v2/).
I wonder if anyone has used it and have feedback? Is this more or less a replacement for the earlier DDBMapper (in which I would not use it) or more like the Document API for Node.js?
In section 20.3.1, recent deals are truncated by timestamp to shard data across multiple partitions. My question is, is this to avoid the 10 GB partition limit, or is it just so we don't hit one partition so much?
The reason I ask is because in section 3.5.4, Alex mentions that partition size limit is not a problem for global secondary indexes, and DynamoDB will split data across multiple partitions under the hood for you. Using a GSI seems much simpler than adding the truncating logic to your application/querying-- unless the point actually is to not hit one partition vs the fear of running out of space.
The reason I ask is because in section 3.5.4, Alex mentions that partition size limit is not a problem for global secondary indexes, and DynamoDB will split data across multiple partitions under the hood for you. Using a GSI seems much simpler than adding the truncating logic to your application/querying-- unless the point actually is to not hit one partition vs the fear of running out of space.
I've been mulling this over and believe the book example showed that pattern just to get us thinking, not out of necessity. To further complicate things, the docs say the 10 GB limit doesn't apply unless you have an LSI. I'm not 100% sure though as I've never hit the size limit (yet).
Good question, Adam Hopkins ! It's more for the latter -- to avoid hitting one partition too much. My assumption is that this would be one of the hotter read paths, so it could cause problems with the 3000 RCU limit if you had high adoption.
If you're never going to hit the RCU / WCU limits, I wouldn't worry too much about partition size. The DynamoDB team has made some indication that partitions over 10GB are split across multiple storage nodes, which could add some latency if you queried across them, but it's not something I'd lose a lot of sleep over.
If you're never going to hit the RCU / WCU limits, I wouldn't worry too much about partition size. The DynamoDB team has made some indication that partitions over 10GB are split across multiple storage nodes, which could add some latency if you queried across them, but it's not something I'd lose a lot of sleep over.