Chapter 5: Using Synapse Link with Azure Cosmos DB

Azure Synapse Link is a new feature added to create a link between Azure Cosmos DB and Azure Synapse. It enables you to run near real-time analytics on data residing in the analytical store of your Cosmos DB account. The analytical store and transactional store are kept in sync in a Cosmos DB account. The transactional store in Cosmos DB is optimized for transactional reads and writes whereas the analytical store is optimized for analytical queries. Synapse Link creates an integration between Cosmos DB and Synapse Analytics. In this chapter, we are going to learn how to enable an analytical store in Cosmos DB and how we can query data directly from this analytical store with Azure Synapse Spark.

We are going to cover the following topics in this chapter, which will help you learn about the concept of Synapse Link and how it can fulfill your business needs:

• Enabling the analytical store in Cosmos DB
• Data storage
• Querying...

Technical requirements

Before you start orchestrating your data, there are certain prerequisites that you should meet:

• You should have your Azure subscription or access to any other subscription with contributor-level access.
• Create your Synapse workspace on this subscription. You can follow the instructions from Chapter 1, Introduction to Azure Synapse, to create your Synapse workspace.
• Create a SQL pool and Spark pool on Azure Synapse. This has been covered in Chapter 2, Consideration for Your Compute Environments.
• Create your Azure Cosmos DB account and three containers: Products, RetailSales, and StoreDemoGraphics. You can go through the following link to create your Cosmos DB account: https://docs.microsoft.com/en-us/azure/cosmos-db/create-cosmosdb-resources-portal.
• Go to the link https://github.com/PacktPublishing/Limitless-Analytics-with-Azure-Synapse to download Chapter 05 - RetailData.zip, and extract and save all the files.
• Prior knowledge of Python...

Enabling the analytical store in Cosmos DB

You can enable Synapse Link on Cosmos DB directly from the Azure portal:

1. Log in to the Azure portal at https://porta.azure.com.
2. Go to your Cosmos DB account and click on Data Explorer.
3. Click on the Enable link while creating a new container:
   

   Figure 5.1 – Enabling Azure Synapse Link on a Cosmos DB account

4. You can click on the Features tab to verify whether Azure Synapse Link is enabled or not. You have the option to enable it from there as well if it is not enabled yet:
   

   Figure 5.2 – Verifying the status of Azure Synapse Link under the Features tab of a Cosmos DB account

   Important note

   The analytical store can only be enabled for new containers.

5. After you enable the analytical store, it creates a container with the Analytical Storage Time to Live property associated with the container. The default value is -1, which means infinite retention, however, we can change this value to any number of days and as many...

Data storage

A Cosmos DB analytical store is fully isolated from transactional workloads. The operational data in a Cosmos DB container is internally stored in row-based transactional stores in order to allow fast transactional reads and writes.

It is not recommended to run complex queries on your transactional workload – it may cause bad performance for your application running these queries. Ideally, you should add an analytical data layer on top of Cosmos DB transactional data if you want to perform complex operations on the data. The major caveat for this architecture is an ETL operation for data sync between transactional and analytical data stores. This additional step may lead to increased Total Cost of Operation (TCO) and overhead of maintaining the data in sync always.

With this new feature of Synapse Link, Cosmos DB gives you the flexibility to enable an analytical store within your Cosmos DB account without performing an ETL operation. Both the data layers are...

Querying the Cosmos DB analytical store

With Azure Synapse, you get the option to choose between Spark or SQL as your compute environment. You can query a Cosmos DB analytical store using Spark and SQL Serverless, however, this feature is not available with SQL provisioned as of now.

Let's learn how to query data in the analytical store of a Cosmos DB container.

Querying with Azure Synapse Spark

Azure Synapse Spark allows you to analyze data in your Synapse Link enabled Azure Cosmos DB containers. You can query an analytical store from Spark in two possible ways:

• Loading data to a Spark DataFrame
• Creating a Spark table

A Spark DataFrame leverages the cached metadata through the lifetime of the Spark session, so any change in the source data will not be reflected here until you start a new Spark session. The metadata of the analytical store is reloaded on every query execution against the Spark table.

You can ingest data to the analytical store of...

Summary

In this chapter, we covered Azure Synapse Link, which is a new feature added to Azure Synapse, and we learned a step-by-step process to query data directly from an Azure Cosmos DB account. This feature dispenses with the need for ETL processes to bring data from a Cosmos DB account to Synapse. Now, we know that we can write queries directly on Cosmos DB data by creating corresponding linked services. We also saw how the transactional store syncs the data in the analytical store through auto-sync, and we learned about modes of schema representation in the analytical store. We used the Python language in this chapter; however, you are free to use any supported language that you are comfortable with.

There are many possible use cases of Azure Synapse Link. You can find a couple of these use cases mentioned in Microsoft Docs: https://docs.microsoft.com/en-us/azure/cosmos-db/synapse-link-use-cases.

In the next chapter, we are going to get some good coding experience on Azure...