Data Modeling and Partitioning in Azure Cosmos DB

last but certainly not least we have another speaker lined up and this is uh lenny lobol i'm not even sure if we need to introduce lenny uh he has been he has been around for such such a long time lenny a long time nbp for how long have you been an mvp early uh it's coming up on a decade now thomas a decade now wow uh nice to see you too lenny lenny is also the cto and founder of sleek technologies based out of new york major contributor for multiple azure data services the sql and cosmos db included the topic for today lenny is a topic that i think never gets old is data partitioning and modeling right indeed yeah all right so well thank you so much and it's great to see you all here at our first ever azure cosmos db conference my name again is lenny lobel i'm a data platform mvp um as tom has mentioned i've been working with cosmos dbo since the documentdb days back in 2014. and i'm here today to talk to you about data modeling and partitioning in cosmos db we all know that partitioning is very critical so we're going to be talking quite a bit about that uh of course cosmos db is many things it is horizontally scalable it is non-relational and a bunch of other things it's uh we can define it as being globally distributed multi-model um tunable consistency there's lots of ways you can describe cosmo cb but for the purpose of this session let's just focus on these two definitions it is horizontally scalable and it is non-relational and drilling into both of those when you're dealing with cosmo cv right we're dealing with a container and we interact with one container as a single logical resource for storing documents and retrieving documents but of course behind that container there's a cluster of servers right if you will or physical partitions for lack of a better term we'll just think of them as physical machines just like a box saw that has storage and cpu processing power and there's been no real upper limit on the number of servers or physical partitions in a cluster behind a container and therefore if you partition your data model properly there's really no upper lip you have a unlimited storage and unlimited throughput with this model right because each physical partition offers more and more storage and throughput okay what was the other term we were going to drill into non-relational and if we think about how we do things in the relational world where we're dealing with tables and rows what is it that we love to do more than anything else in this world right we love to join these things right uh that's what makes us happy we like to come up with uh with the perfect normalized data model we like to craft our entities such that we have primary and foreign keys giving us a very normalized relational data model and that's the way things work in the relational world but of course in cosmos db and in the nosql world we are storing data inside of json documents not rows and while there's certainly nothing you can store in a row that you can't store in a json document and therefore nothing stopping you from designing a data model where you pretty much you know treat json documents as if they arose and have you know references from one related document to another while you can do that such a design would break down in cosmos db this would this would perform very very poorly it could be made to work but it could not be made to work well and why is that it ties back to our first definition of being horizontally scalable and the fact is that any one of these documents can land on any physical partition and in order to maintain the supreme level of performance uh the high performance slas and the single digital second reason rights and and all those guarantees on performance it's simply not practical or feasible to enforce relational constraints between related documents or indeed to even support joins on them and that's why we need to think about things differently because of course after that opening you know it certainly begs the question well then is cosmos to be at all suitable for a relational workload the answer is of course it is otherwise the session would be very short it would end here and i would go home uh but in fact uh you know many workloads are relational not all uh but many are and customizable is certainly suitable for those relational workloads it's just that you have to think about things differently you can't do things the way that you're gonna cost become accustomed to doing them in the relational world even if some of those tried and true practices uh have been around with us for decades for some of us uh we have to kind of go against the grain of what we consider to be best practices in the relational world in order to materialize relationships in an efficient manner um in this non-relational world of cosmos db and so with that kind of backdrop set let's just have a look at this this little diagram here this is our web store relational model think of this as a a database behind our customer facing ecommerce website and uh it's a relational workload like a mini adventure works it's got all the things you would expect to find in a typical relational data model you've got customers with a one too many relationship with their addresses a one-to-one relationship with their password you've got products which are which participates in a parent child relationship with product category as well there are product tags in which case we've got a many-to-many relationship here so there's that intermediate table right this intermediate product tags table here just trying to get that yeah trying to get my laser pointer to work right with foreign keys pointing to both sides of the many too many relationship and we've got sales orders right uh which are children to customers so a customer will have a set of sales orders each sales order will have a senate sales order details and it's a small data model but it is fairly representative of the most common uh data modeling patterns that you'll find in a relational database so if you're coming to cosmos to be fresh and you're just you know and it's new to you your first instinct might be to say hey i've got nine tables here maybe i should have non-containers right one container would make sense to store uh to think of a table as a container when you're first entering the world of cosmos eb but the question is is this a good design and of course the answer is no as i started explaining you know that due to the lack of uh joins and the fact that there is no referential integrity and there are no relational constraints um this could be made to work but could not be made to work well it would perform very very quickly it would be actually the worst possible design that you could come up with so for the rest of the session we're going to take it step by step and see how to implement a data model like this in cosmos db uh for high efficiency high performance and high scale and we'll start with customers we've got three tables here for the customers the one too many between customer and addresses and the one to one customer and password step one is you just literally translate your column names to property names as if it were a json document which is all i've done here other than camel casing the identifiers the property names in my json document match the uh column names in my database uh i had to rename the primary key columns simply as id because of course in cosmos db uh that that id property must always be named id but otherwise it's a simple translation forward and at that point i could stop there right because i do have these kind of and i don't want to use the word relationships here let's just say references i mean i do have ids pointing from one document to another um but let's just call them references not relationships because this is a non-relational data model and so it begs the question would it be better perhaps since we are working with json and json is semi-structured uh document format we could embed our addresses as an array as i've done here i only have space to show one address but those square brackets mean i could have any number of addresses or let's say a reasonable number of addresses as we'll get to in a moment and the password itself is embedded as an object using a curly brace and you know thereby uh you know you eliminate the problem of no joins if you pre-join right we have pre-joined effectively our addresses with the customers and the password the customer and we've also solved the problem of referential integrity because the uh a child array lives nested within his parent and can never be broken from his parent right um so the question is is this appropriate to do this so do you embed or like so or do you reference like so and you need to you know this is a very typical question that you'll ask during this process so the rules aren't hard and fast but the general rules are if you've got a one-to-one relationship like you have in the case of a password or you want to few relationship like you have in the case of addresses then it would be reasonable to embed one is what to few you know in the relational world there's no such thing it's either one-to-one or it's one-to-many and even if it's two or more it's considered one to many um in this world we are thinking of a data model where we have a json document with an upper limit size of two megabytes where it's perfectly reasonable to you know cap the number of addresses that a customer might have in their profile to all fit into megabytes and so it would make that's a one to few think of their orders we want to think of orders as one-to-many don't we we want lots and lots of orders ideally an unbounded number of orders and so it's certainly not feasible to embed all of that in a single document and so then you'll reference whether it's a one-to-many relationship particularly unbounded relationships you'll go with referencing or if you have a many-to-many relationship as you'll see the way we'll handle the product tags relationship you'll also want to reference actually a combination of embedding and referencing and another kind of general rule is that you know even if it's just one to one and even if it's uh or just one to few um if you're never really querying for these things together and you're never really updating them together it also makes sense to reference that um if they're if they're being queried or updated independently again these aren't hard and fast rules but for our scenario here we can certainly go with embedding for the addresses and for the password and turn three tables into one essentially uh by encapsulating all that information in our customer entity and then the question at that point becomes if we're gonna store customer documents like so inside of a customer container how do we partition that container what is our partition key the most critical question that you'll always have to ask when defining a container well the way that you come up with the right partition key is you need to really understand the most common querying patterns you need to understand how your application is going to most frequently query your data how your users are going to most frequently query your data and again sticking with this scenario of customer-facing e-commerce website um the most common query is that we're going to a customer is going to want to retrieve information about that you know their profile information which is their name address all their addresses their password perhaps for rendering on profile page and so the most common query is really to get an individual customer by their id which would pull in their addresses right because they're embedded and their password because it's embedded right and so that really means the id property itself would be actually the most suitable property for the partition key here now it might seem like a strange choice because as you may or may not know every document is unique in a container based on a combination of this id property here and whatever other property you decide to choose for the partition key however if you choose the id property itself as the partition key then you get exact uniqueness of one document per logical partition and you can't have more now again it might seem like an odd pattern but it is actually a very good approach when you're dealing with point operations point reads and point writes where your primary use case is to read or write an individual document at a time the id property is often a very good choice and it certainly satisfies our most common query so that's what we'll go with now let's just plow ahead with our next entity in the data model which is product categories this is very very simple it is really just a key value pair right there's a product category id and a display name so like before we translate our column names into json property names and then uh decide to put that in a container for the product category we'll put that in the product category container ask the same question how do we partition it answer it the same way what's your most common querying pattern on that on that website you're going to want to display all the product categories right maybe in a sidebar on the left or a little menu so the user can click a product category to drill in when they find a category of interest to see the products in that category and that really means a select statement with no where clause because you want them all and the way that you implement this type of pattern in cosmos db is to add a type property really i want all of them inside one logical partition think of this as a lookup list anything that's a reasonable size when i say reasonable size i mean it'll fit inside of a single logical partition which is limited to 20 gigabytes in size and you certainly don't have a list of product categories coming anywhere near that so there's no problem at all putting them all inside one single logical partition and the way we do this is we add a type property which is certainly optional you know it's schema free you're free to define any schema the id property is the only required property but it's always a good practice to throw in that type property um to help unambiguously distinguish one type of document from another and if you create a property call type and you set the value of that property to category for every product category document and then partition on the type property of course that's going to lump all of them into one logical partition and give you a very efficient result a very efficient query that results in returning all of the categories that you have in the database all in a single partition query product tags i don't rehash that because it's the same concept a simple key value pair where the user might want to see a list of tags they see a tag of interest they'll want to see all the properties associated with that tag same concept translate the column names to property names put them in a tag container ask yourself how to partition it and answer it with the same answer we want to get all the product tags it's a select statement that returns them all so we'll add that type property only for the tag documents we'll set the value to tag right and then by partitioning on the type property all of the product tag documents are stored together in the same logical partition just like categories products is where it gets more interesting with products we have that many-to-many relationship right so first we'll do step one we'll translate our column names to property names but what about this many-to-many relationship with product tags the way this is implemented and i mentioned this earlier is with a combination of embedding and referencing i am embedding an array of tag ids into the product document but that array itself is an array of references to tags and in this case i am embedding a an array of tag ids into each product document this could also be made to work by embedding an array of product ids in each tag document depending on your usage patterns one of those may be better than the other but in either case this is how you implement into many relationships now how do we partition the products we're sticking with our e-commerce website scenario the user has found a category of interest they want to get all the products in that category so the most common query products is going to be get all the products within a given category not all the products in the entire database so that means we want all of our product documents to be kind of grouped together logically in a logical partition by category which you guessed it if you were thinking category id is the proper property to use here you guessed correctly and that will achieve just that so now i've got my product documents in hand uh but if you look at a product document what do you have inside of a product document you have a bunch of ids but you don't really have everything that you need to display the product on your web page right you have the category id but not the category name you have the tag ids but not the tag names right and um that means that if you want to get all the products in a given category and display them on your web page you would have to run one query to get all the products in the given category this would be fairly efficient because it's a single partition query you could even further filter on this within a category uh if you wanted it would still be a single partition query but you don't have the display names so it would be a second query now on the category container by the category i need to get the display name for the category and then it gets worse because for each product returned by that first query you would need to run this third query on the tag document to get the display names for the tags and this is where you know you pound your fists on the table and say well where are my joins of course in the relational world this is a no-brainer you just join these things together on the ids and you get the display names but we're in cosmos db world there are no joins we need a new way a different way of handling this and and that is going to be done with denormalization we're going to simply copy or duplicate that master data in each product document so each product document in addition to having the category id will have a copy of that category name that's a copy of the master data the tags property is now not just an array of ids but an array of objects that includes both the tag id and a copy of the tag name again from the master data so at the point of insert when i create a new product i'm also copying that master data for the category name and the tag names which is great meaning when i get have a product document in hand i have everything i need to display my product on the web page but of course the obvious question becomes what happens when does it change that master data somebody comes along and changes a category a name or a tag name and so what we really want to do is listen for changes on those two containers and cascade them to the product container and that's where the change feed comes in which is a very critical component of cosmos to be a bit of an unsung hero you don't hear enough about it but it's what drives a lot of microservice architectures the exchange fee gives you a persistent log of changes so every time a document is added or modified or changed in the in the container it gets emitted through the change feed in near real time and also persistent to the change speed which so it can always be rebound and replayed and you can build microservices um you can use azure functions to do this very easily to watch the change feed respond to every change that occurs and respond and process it in some way and in this case it would be to keep our denormalized data model in sync with master data as it changes so on the product category container and on the product tag container they each have a change feed and when any master data changes we can have an azure function wake up and cascade the change to the product table there's just some pseudo code here's my azure function name the cosmo db trigger attribute means the azure function will wake up whenever there's a change to the collection name which is the container name that's the product category right here that's this guy right here on the left that we're watching for changes for this product category container and we're handed a read-only list of documents that have changed and we simply iterate them and for each one each one of these represents a change let's say to a product category in this case we will just get the category id and name and now we need to update each product to cascade and propagate that master data change and that's done first by querying for the products in that category very efficiently again because we're partitioned on the category id so this will return all the products in one efficient single partition query and then set up our um iterator to process those results and update them one at a time replacing them with point-wise updates that takes us to our last set of tables in our data model which is our sales order and sales order details by now the process should seem a little bit familiar to you step one translate column names to json property names step two ask the question embed or reference this is one to many or one to few well hopefully our sales orders are large but i still think that um at two megabytes we can accommodate even a large sales order uh so we would definitely be better off by embedding the details right so we'll embed the sales order details and essentially pre-joining them and um they're embedded there as an array of details inside each sales order document and then we're going to put them in another container called the sales order container ask the usual question how do i partition this container well answer by are we asking the usual questions what are my most common queries well we've got this very common query again that the customer is going to be running all the customers visiting your website are going to be frequently asking to see their sales orders and then perhaps less common query that might occur let's say in the back office by some executive who's looking at a dashboard perhaps who wants to see their best customers might want to get might want to query for all the customers uh descending by order account to get their best customers at the top of the list so let's go with the common scenario first getting all sales order for a customer it would look like something like so that means we you know that's how the query would look right give me all the orders where the customer id matches this customer so if we partition on the customer id it'll do just that right it'll put all the sales orders for customer a and its own logical partition and each other customer in their own separate logical partitions i could get all the sales orders or a subset of the sales orders from a customer uh with a very efficient single partition query so that means customer id is the correct uh is the appropriate partition key to use here but now let's slam on the breaks here and pause and remember i promised we'd come back to the customer container where we partitioned on the id property because that's the same value that we're partitioning on we're partitioning on the customer id in both cases in the case of the customer container and in the case of this sales order container we determine that customer id is the appropriate value now granted it's called id in the customer uh document and it's called customer id and the sales order document and we'll iron out that wrinkle in just a moment but the value is the same it's the same value that we want to partition on and when you want to uh when your partitioning needs are the same between two different types of document types you will not create two different containers we are in a schema free world here in cosmos db there's no rules on schema there's nothing preventing you from storing two different entity types in the same container and in fact it's encouraged to do so when you have two types even though they're different types but they share the same partitioning needs or they share similar throughput needs those are reasons for storing multiple items of the same type in the same container and that applies here because we have the similar partitioning needs on both these entities and that's the customer id so what we'll do is we will not create that new sales order container we will stick with the single customer container that we started with we'll just have to make a minor adjustment since the id property in each of these two types is different in the case of the customer document the id is the customer id in the case of the sales order document the id is the sales order id and it's the customer id property that's kind of a foreign key if you will so we'll fix that with another little tiny little bit of denormalization we'll throw in a customer id property on the customer document and essentially store i'm sorry two copies i guess i didn't have that call out in there we will sort two copies of the customer id the customer id will be stored both here and here and it's the same name property now in both documents and so i can partition on the customer id property it's definitely i mentioned a good idea to always have a type property but that's especially true when you are combining types in a single container you want to unambiguously distinguish between two types especially when they're in the same container but now uh that's our data model at this point we now have logical partitions where we're mixing types one document in the logical partition for customer is the customer document which remember embedded inside of that are the addresses and the password and all the other documents are sales order documents and remember that embedded within those are all the sales order details and all of which are contained in this single logical partition per customer so this is a very nice little design for uh customers and sales orders i can run a query like search to get all the sales orders for a different for a given customer by querying on the type property um for a given customer in fact if i left out the cut the the uh the type portion of my query and i just got all of the documents for a given customer it would give me the customer and all of their sales orders along with all of the embedded information with a very efficient um with a very efficient single partition query and so that therefore we are getting around the issue of there being no relational uh uh integrity no referential integrity no relational constraints no joins because you see it's kind of all joined together for us here isn't it because we partitioned properly so our documents kind of join together naturally in an efficient manner through single partition queries and within those documents we've got pre-joined embedded data as well and we've kind of like dealt with these radical differences between the relational world and the non-relational world and that takes us to our very very last query example which is that execs sitting in the back office looking at their dashboard who wants to see their top customers again in the relational world is a no-brainer you just do a select count on that order table group by customer id and you're done that doesn't work here so what we'll do is another little bit of denormalization we'll throw in a new column called a new accordion slip there a new property called the sales order account and we're going to maintain we're going to not duplicate data in the sense that we're going to duplicate the count so if there are three dots if there are three sales order documents here for this customer then the number three will be stored in the customer document as well and that means that if i want to add a new sales order for customer a i also need to update that customer document to increment the sales order account how can we do that if you're thinking change fee you're on the right you're definitely on one right track change fee is a wonderful mechanism but in this case i'm going to do one better because everything is in the same logical partition we can take advantage of transactions using a stored procedure now this is in this case it's a stored procedure with javascript it can be done in c-sharp or other languages uh or well i think at least in c-sharp using transactional bulk of the the transactional batch feature but in either case what we're doing here is just a little bit of code that is going to retrieve the customer document increment the sales order account of that customer document and then replace it although that replacement occurs in in the context of a transaction and is not actually committed until this javascript code completes successfully so it's been written to the container but not committed and then we can create the new sales order document and and return let's say the new account because we want to return something but only then um when the function completes successfully the does everything get committed the new order is written and the customer document is updated all at the same time and once you've got that uh stored procedure in place it doesn't mean that anytime you want to add an order document you must do so by calling the stored procedure and not by just dropping it in by directly inserting it and if you uh follow those guidelines you will always be able to run a very efficient query like so relatively efficient query this is a cross-partition query right because there's no where clause in here we're ordering by the cells or account descending is that evil it's not evil if it's in the minority of cases like this one exec sitting in the back office who's hitting refresh on their dashboard once an hour it is evil if it's the customer-facing website with all of your customers hammering away with cross-partition queries day in and day out that that is when cross-partitioning queries need to be avoided but here it's perfectly fine and and it's relatively inexpensive because the sales order account has been pre-materialized for us so to wrap up with two minutes to go our final design we have uh a custom we have a database with these four containers not nine containers four containers and in the customer container we've got two different types and and in case of customer sales orders and products we're also embedding to cut down on the number of containers and if you've been paying attention and i know you have you're probably thinking to yourself product tag and product category they're both partitioned on type they're both small lookup lists doesn't make sense to create a container per small lookup list we could combine those into one let's say metadata container called product meta and combine product tag and product category documents and now bring it down to three containers with a very efficient data model to set to handle a relational data scenario in a non-relational database like cosmos db the key takeaways for this for the session are you really must know your uh key patterns your key access patterns how your database is going to most be most frequently queried and written to you as well and then and you design for those patterns to state the obvious partitioning is critical you're not going to achieve that performance and scale if you pick a poor partition key you want that you want your uniform distribution as much as possible both for storage and throughput using these techniques that i covered to materialize relationships either by embedding or by referencing through denormalization pre-aggregating combining uh multiple entities uh different entity types in the same container if they share similar partitioning needs and they share similar throughput needs and using your choice of either the change feed or transactional integrity where when the updates are within a single logical partition using the stored using stored procedures as well so i hope you enjoyed the session as much as i enjoyed preparing it for you i want to thank the team for inviting me to speak to you today and uh throw it back to you guys for questions if you have any thanks lenny this was an awesome session i really love the way you graphically lay out those design considerations it makes it so much easier to understand you know how to tackle those kind of exercises i had some good inspiration there thomas yeah well you you presented very very well uh and i hope this is going to um to help any of our viewers will need to to tackle such such a design exercise in the future we are at time and we are short and time again but i just wanted to relay one question we got from the many questions we got on the chat one of our viewers where was asking should multiple micro services use the same cosmos db container should multiple micro services use the same cosmos db container so that's that's a that's an explosive question you know endorsed by this i kept things very simple here sometimes you can't just settle on one partition key and you'll need a microservice to actually replicate data from one container to another just because you have uh heavy uh querying patterns where half the time one property is suitable as the partition key the other half the time another property is suitable as the partition key then you can have one micro service whose purpose in life is to just keep those two containers in sync and then uh you will almost certainly need or want to create materialized views for queries that would otherwise be very expensive and that involves more micro services to generate to to create materialized views in yet other containers so you are talking about multiple containers and multiple micro services so although all those macro services could be all be consuming the change feed of a single container which is a really nice design aspect to the way microservices can be implemented you can have a microservices architecture where you've got uh microservices that are completely independent from one another that they're all sharing the same they're consuming the same change feed and maintaining state independently of one another regardless so it's a mix it's a mix and match it will depend on your scenario but you'll almost always involve multiple containers and multiple microservices that makes sense thanks again lenny we we couldn't have our first cosmos db conference without you we really appreciate your participation here of course and uh thanks again everyone so much for tuning in so it's been a really great live stream it's always great to see how our customers our partners and our community are building their applications with cosmos db uh i just wanted to check in tim and thomas did you have any interesting takeaways or insights or things that you wanted to share that you've learned from from what we saw today team i really enjoyed lenny's session that was awesome um i mean as somebody kind of who knows a lot about cosmos tv it was great to see a lot of tips and tricks that even more advanced users could uh could really use to to kind of become a power user of the product but thank you uh for watching today it was really great to have you as viewers in addition to these great sessions in our live stream we have 18 on demand sessions that you can watch at any time also to see this great lineup of on-demand sessions you can visit gotcosmos.com and click on on-demand sessions you can also join us for a weekly podcast where our host mark brown will be members of the cosmos tv team each week and talk about things i'm relating to cosmos db like querying change feed analytics anything you can do with cosmos tv will cover on the show for the show schedule and upcoming episodes also you can go to cosmos.com tv and you'll be able to view all that there and finally if you're new to cosmos tv and want to find new ways you can try it for free read our blog post on the four ways that you can try cosmos tv for free that'll be available at aka dot ms slash cosmos db dash free you can catch our next stream uh for the pacific time zones starting in eight hours that'll be at 8pm pacific time and 11 p.m us eastern time and thank you again for joining it was so great to have you with us for the first part of the cosmos db conference thank you you

Channel: Azure Cosmos DB

Views: 1,146

Keywords:

Id: XU1ZuwiWW_k

Length: 34min 56sec (2096 seconds)

Published: Tue Apr 20 2021