CosmosDB is Microsoft’s latest NoSQL database offering, a metamorphosed form of DocumentDB, so to speak. Read more about CosmosDB here.
Here is a quick note on how to export data from CosmosDB to SQL and do data comparison. This is going to be handy when migration to CosmosDB is still in progress, and you want to check data integrity.
There are a few ways to go about this. The quickest option is to use Document DB Migration Tool. This is a tool provided by Microsoft that helps to migrate data from various sources like MongoDB, csv, SQL Server to CosmosDB. Tool also helps to export data out of CosmosDB, which is what we will be doing in this case.
1. Use Document DB Migration Tool to export data to json files
2. Import json files to SQL Server using a json parse function such as OPENJSON
3. Run SQL scripts in SSMS to check data integrity
Please be mindful of RU costs when doing Step 1 and scale your collection up as required to avoid throttling.
SQL 2016 introduced the OPENJSON table-valued function that lets us read from json files. If your SQL Server instance is 2016, but all user databases are still in compatibility level 2014 or lower, make sure to context-switch to a system database to use this function.