RDBMS data in Cosmos DB RRS feed

  • Question

  • I'm exploring the benefits of moving a database from sql server into cosmos and the first challenge I see is:

    Some tables have upwards of 100 columns and maybe 15 of those would be an int or uniqueidentifier for something like CategoryId which links to the Category table.  When I import this table into Cosmos, how do I establish all the links so I can see the category names in the list of entities?  

    for example, in tsql I could write this:

    SELECT        dbo.tbTask.Tk_Id, dbo.tbTask.Tk_Number, dbo.tbTask.Tk_Task, dbo.tbTaskCategory.TkCt_Name
    FROM            dbo.tbTask INNER JOIN
                             dbo.tbTaskCategory ON dbo.tbTask.Tk_TkCt_Id = dbo.tbTaskCategory.TkCt_Id

    and the results would look something like this.

    Tk_Id                                                           Tk_Number   Tk_Task                                                  TkCt_Name
    9EBCAB95-27EC-4683-9E79-0400BCBCD204   83                Kosteneinsparung abschätzen                   Product
    D5B8AAB6-07D1-4E35-AB6E-C3A1859659EB   21                Marktsondierung zu möglichen Lösungen   Product

    Next, for a table with 100 columns and 15+ linked tables the tsql would be much more complex, but fortunately we some something called a query designer which helps for these large statements.  and actually, this scenario is simple compared to large select statements with large nested derived tables, etc.

    This seems like its going to be a real HairBall in Cosmos.  how will we do this?

    Thank you.

    Wednesday, June 20, 2018 6:49 PM

All replies

  • Hi Moondaddy,

    With Cosmos DB, you achieve this through collections and partitions. I have included some documentation that provides some theory and guidance on designing a data structure in Cosmos DB. If you deploy the SQL API, queries can be executed very similar to a SQL data source. 

    Partition and scale in Azure Cosmos DB

    Azure Cosmos DB: Designing your data structure

    Please let me know if you require additional information. Regards, Mike

    Wednesday, June 20, 2018 7:25 PM
  • Thanks, but those links don't answer my question.  in the example I provided, I have a task table and a category table.  If I import those into cosmos from sql server, I will have 2 collections.  how to I link the categories to the category Id in each task?  What happens if I change the spelling of a category name.  I know how this works in sql server, but haven't seen any info only on how to do this in cosmos.


    Wednesday, June 20, 2018 7:48 PM
  • Cosmos DB will automatically index collections and those indexes are implemented via paths, where the root of a given path is a collection. If SQL index concept is translated to the Cosmos DB index path terminology:

    /<sql table>/<table column>/<wildcard operator>

    So, you can let Cosmos DB create indexes or you can create these manually, and to help shed light on that process, I have included the following documentation on How does Azure Cosmos DB index data?

    There are two chapters that are specific to your question:

    Index Paths

    Index Data Types, Kinds, and Precision

    In terms of category name being renamed, the index policy for that collection would need to be redone. 

    Additional Info and FAQ: Azure Cosmos DB FAQ

    Stack Overflow thread with some additional information: How to create multi-key index in Cosmos DB with MongoAPI?

    I hope this information helps you understand more about Cosmos DB. Regards, Mike

    Wednesday, June 20, 2018 8:43 PM