Azure Cosmos DB is a powerful multi-model NoSQL database that supports hosting various types of transactional data. However, when it comes to analyzing large volumes of transactional data, traditional relational databases may not scale or perform efficiently. This is where Azure Synapse Analytics, the data warehouse offering in the Microsoft Azure technology stack, comes into play.
One of the challenges with analyzing transactional data in relational databases using columnar warehouses is the need to replicate and/or relocate the data from operational repositories to analytical repositories. However, with the concept of Hybrid Transactional Analytical Processing (HTAP), data hosted in a relational format can be auto-organized in a columnar format, eliminating the need for extensive data replication or relocation.
In this article, we will learn how to analyze data hosted in Azure Cosmos DB using Azure Synapse Analytics. Here are the prerequisites:
- An Azure Cosmos DB account implemented using the Core (SQL) API, with all the preview features turned on.
- An Azure Synapse instance with a SQL on-demand pool in place.
To begin, we need to configure Azure Cosmos DB. First, we create a sample database and container within the Cosmos DB account. We enable the “Analytical store” option, which allows us to analyze the operational data stored in Azure Cosmos DB using Azure Synapse in near real-time without affecting the performance of Cosmos DB itself.
Next, we add some sample data to the container. Once the data is loaded, we can query it to see the results. We can also enable the Azure Synapse link, which allows connectivity from Azure Synapse to Cosmos DB.
Now, let’s move on to analyzing the data with Azure Synapse. We connect to the Cosmos DB analytical store from our Azure Synapse instance. We create a linked service to establish the connection and then generate a SQL script to query the data in Azure Cosmos DB using the openrowset function.
By executing the query, we can extract and access the data held in the container’s analytical store in Azure Synapse. This allows us to perform advanced analytics and gain valuable insights from the transactional data hosted in Azure Cosmos DB.
In conclusion, Azure Synapse Analytics provides a powerful solution for analyzing Azure Cosmos DB data. By leveraging the analytical capabilities of Azure Synapse, we can unlock the full potential of our transactional data and gain valuable insights for decision-making and business intelligence.
Table of Contents:
- Getting started with Azure Cosmos DB Serverless
- Analyzing data hosted in Azure Cosmos DB with Notebooks
- Analyze Azure Cosmos DB data using Azure Synapse Analytics
- Restore dedicated SQL pools in Azure Synapse Analytics
- Copy data into Azure Synapse Analytics using the COPY command