How do you pick partition keys for Azure Cosmos Core SQL?

Alex Winfield 1 Reputation point
2022-07-15T13:03:41.667+00:00

I'm sort of confused by Core SQL, and would love some insight on how to work with it. For context, I have over 10 years of experience working with SQL Server databases, but the partitionKey concept is new to me, and I don't understand how to use it. It's required, and the default is for the partitionKey to be the same as the row primary key. I just don't see how that can work with multiple result sets, or with joining other tables at all. Unless it's only useful for very basic CRUD apps?

Let's take a simple shop for example. What would the partitionKeys be for an order, that has multiple line items, and those line items fulfilling the order might be located in separate warehouses. But you also want to be able to select all line items in a given warehouse, so the pickers know what they need to pack up and ship out.
If the partitionKey is the same as the primaryKey, you can't do any of those things.
If the partitionKey is something related, like the orderId, then you can't query for all items in a warehouse.
If the partitionKey is the warehouse, then you can't query for everything that's on an order.

I guess... do you guys have like example schemas I could look at? Seeing how a database is setup has always been more useful to me than reading concepts.
Or am I completely off base here, and Core SQL (despite its name) is only meant as a super-powered key-value store? And if THAT'S the case, then... do you guys have an inexpensive sql product that's actually sql?

Azure Cosmos DB
Azure Cosmos DB
An Azure NoSQL database service for app development.
1,363 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Mark Brown - MSFT 2,761 Reputation points Microsoft Employee
    2022-07-15T21:10:27.323+00:00

    For people who are new to distributed NoSQL databases I recommend consuming some content before getting too deep. There are some very fundamental concepts that need to be understood to help you model your data and come up with a partitioning strategy that will allow your database to scale and be as efficient as possible.

    I realize this is quite a bit of content. But after going through this, I promise you will understand how and why Cosmos DB is different than a relational database and how to design for it.

    Articles:

    Videos:

    Microsoft Learn Labs: (Essentially are HOL's for the second video above)

    GitHub Repo:

    • Cosmic Works - this is the code and data set for the adventure works talk above
    0 comments No comments