When running streaming pipeline in Synapse, how to cache Cosmos DB and also keep it updating

Question

Hi Team,

Good afternoon! Currently I use Synapse Analytics Spark Notebook to connect to EventHub for processing streaming data. In this process, we require joining data from EventHub with Cosmos DB to get additional columns. Currently, we use spark.read.format('cosmos.oltp') to establish the connection. However, accessing Cosmos DB each time for data retrieval isn't very efficient. I'm considering caching the Cosmos DB data and maintaining its update whenever new data arrives in Cosmos DB. I've heard that Cosmos DB Analytical Store might facilitate this. Could you confirm if it does? If so, how can I connect to it? What changes should I make in the current approach to directly connect to Cosmos DB? If not, could you advise if achieving this is possible? Thank you!

Answer 1

Greetings @Jiacheng Zhang

You’re correct in considering the use of Azure Cosmos DB’s Analytical Store for your scenario. The Analytical Store is a fully isolated column store that enables large-scale analytics against operational data in your Azure Cosmos DB, without any impact on your transactional workloads. It’s designed to address the complexity and latency challenges that occur with traditional ETL pipelines.

The Analytical Store can automatically sync your operational data into a separate column store, which is suitable for large-scale analytical queries. This means you can run near real-time large-scale analytics on your operational data.

To connect to the Analytical Store, you can use Azure Synapse Link. This allows you to directly link to the Analytical Store from Azure Synapse Analytics.

In terms of changes to your current approach, instead of using spark.read.format('cosmos.oltp'), you would use the serverless SQL pool in Azure Synapse Link. This allows you to analyze data in your Azure Cosmos DB containers that are enabled with Azure Synapse Link in near real-time without affecting the performance of your transactional workloads. The full SELECT surface area is supported through the OPENROWSET function.

Please note that you need to enable the Analytical Store on your Azure Cosmos DB containers. Also, ensure that your Azure Cosmos DB analytical storage is in the same region as the serverless SQL pool.

I hope this helps!

Answer 2

Analytical store will be more efficient than reading from transactional store via cosmos.oltp in this use case.   But we currently do not expose Change Data Capture (CDC) via spark notebook. If you are looking to get incremental data as nee data arrives in Cosmos DB, you currently would have to use either ADF dataflow or synapse pipelines. To read from analytical store, you can use "cosmos.olap" instead of "cosmos.oltp"