Loading your Cosmos DB data into a Synapse Spark Notebook

What is, in my experience, the best way to get data from Azure Cosmos DB. In this blog, I will provide examples of how to set up a connection to Azure Cosmos DB in Azure Synapse Notebooks. And explain the difference between the analytical store and the operational store.

In my current project, I’m working with Azure Cosmos DB in Synapse Notebooks. Within Spark Notebooks we load data from SQL Server and Cosmos DB. After loading all records, the records are compared for changes and upserted to the Cosmos DB Container.

Cosmos DB Authentication

In short, there are two options here; Managed Identity or the Account Key.

I would always recommend using Managed Identity over the Account Key where possible. When the Account Key is not used, you can disable the Account Key for your entire Cosmos DB Account!

If you do use the Account Key, make sure you rotate the keys periodically.

Unfortunately, at this moment, Managed Identity authentication from Synapse Spark Notebooks is not supported at this time.

Managed Identity authentication does work for copy activities in a regular Synapse Pipeline. When using a Synapse Managed Virtual Network and Private Endpoints no specific RBAC role is needed on the Azure Cosmos DB to read data. (** I am not sure if this is a bug or by design **)

Synapse Link for Azure Cosmos DB

When extracting data from a Cosmos DB, like any other operational system, we want to prevent this from having a negative impact on the database performance. Synapse Link provides us with an option to create an Analytical store. This is a Column Store optimized for analytical queries.

Read more on how to enable Synapse Link here:

Azure Synapse Link for Azure Cosmos DB, benefits, and when to use it | Microsoft Learn

Python Spark Examples

These examples show the difference between a direct connection and a connection via a Linked Service Please note the difference in the ‘.format()’ function between reading and writing. OLAP refers to the Analytical store, this store is optimized for reading large amounts of data in bulk. OLTP is the Transactional store.

It is not possible to write to the Analytical store directly!

# Read Cosmos DB Analytical store directly via endpoint and account key
cdb_analyticalstore_directly_df = spark.read\
    .option("spark.cosmos.accountEndpoint", cosmosdb_endpoint )\
    .option("spark.cosmos.accountKey", cosmosdb_accountkey)\
    .option("spark.cosmos.database", cosmosdb_databasename)\
    .option("spark.cosmos.container", cosmosdb_container_id)\

# Read Cosmos DB Analytical store via Synapse Linked Service
cdb_analyticalstore_linkedservice_df = spark.read\
    .option("spark.synapse.linkedService", synapse_linked_service_name)\
    .option("spark.cosmos.database", cosmosdb_databasename)\
    .option("spark.cosmos.container", cosmosdb_container_id)\

# Write to Cosmos DB Transactional store via Linked Service 
    .option("spark.synapse.linkedService", synapse_linked_service_name)\
    .option("spark.cosmos.database", cosmosdb_databasename)\
    .option("spark.cosmos.container", cosmosdb_container_id)\

Synapse Serverless SQL Pool provides a great way to query an Azure Cosmos DB Analytical store as well.

Query Azure Cosmos DB data using a serverless SQL pool in Azure Synapse Link – Azure Synapse Analytics | Microsoft Learn

Microsoft Documentation

The below link describes this setup as well. Please mind that, in my opinion, this article is missing the instruction on how to set up your linked service to Cosmos DB and the code samples are missing a database reference (if not included in your linked service)

Interact with Azure Cosmos DB using Apache Spark 3 in Azure Synapse Link – Azure Synapse Analytics | Microsoft Learn

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this:
search previous next tag category expand menu location phone mail time cart zoom edit close