[spacer] [spacer] [spacer]

Sign up with your email address to be the first to know about new publications

[mc4wp_form id="4946"] [spacer]

Create Azure Synapse Link for Azure Cosmos DB SQL API

With Azure Synapse Link for Azure Cosmos DB you can get near real-time insights on operational data without affecting your operational data store. By using a native Azure Cosmos DB analytical data store, you can have a fully isolated column store.

Today we are going to create a link to Azure Cosmos DB SQL API that will allow you to query the database, without needing to build an additional ETL/ELT process or writing code!

I have created the following sections to finalise the configuration:

  • Prepare Azure Cosmos Database SQL API
  • Create Azure Synapse Link for Azure Cosmos DB SQL API
  • Query Data

Prepare Azure Cosmos Database SQL API

Before we create the linked service in Azure Synapse Analytics, we need to enable the feature in Azure Cosmos DB.

Azure Cosmos SQL API Enable Analytical Store Feature

The next step is to create or modify a container with the analytical store:

Modify the container to use the analytical store in the Settings section:

Azure Cosmos SQL API Enable Analytical Store Feature

If you are building a new container (as was my case), see the attached additional steps below so you can add some items (you can copy them from this link or the description below):

Azure Cosmo SQL API New Item

Insert documents (one by one):

{  "id": "1",  "customerid": 101,  "firstname": "Orlando",  "lastname": "Gee",  "email": "orlando.gee@adventure-works.com",  "addresses": [  { "addressline1" : "660 Lindbergh", "city" : "Saint Louis", "stateprovince" : "Missouri", "countryregion" : "United States", "postalcode" : "63103" },  { "addressline1" : "Ontario Mills", "city" : "Ontario", "stateprovince" : "California", "countryregion" : "United States", "postalcode" : "91764" }  ] }
{  "id": "2",  "customerid" : 122,  "title" : "Ms.",  "firstname" : "Caroline",  "middlename" : "A.",  "lastname" : "Vicknair",  "companyname" : "World of Bikes",  "salesperson" : "adventure-works\\jillian0",  "addresses": [  { "addressline1" : "72540 Blanco Rd.", "addressline2" : "South Side", "city" : "San Antonio", "stateprovince" : "Texas", "countryregion" : "United States", "postalcode" : "78204" }  ] }
{  "id": "3",  "customerid": 108,  "title" : "Mr.",  "firstname" : "Robert",  "middlename" : "R.",  "lastname" : "Vessa",  "suffix" : "Esq.",  "companyname" : "Totes & Baskets Company",  "salesperson" : "adventure-works\\jillian0",  "emailaddress" : "robert13@adventure-works.com",  "phone" : "560-555-0171" }

Azure Cosmos SQL API Save Item

You can visualize the items in Azure Cosmos DB before moving on to the final configuration in Azure Synapse Analytics.

Azure Cosmos SQL API Review Items

Now, we are ready to finalize the configuration in Azure Synapse Analytics.

Create Link for Azure Cosmos DB in Azure Synapse Analytics

In Azure Synapse Analytics, we can create a link by using the following hubs:

Data Hub

In the Data Hub section, we can link Azure Cosmos DB SQL API and external data. Make sure you select Azure Cosmos DB (SQL API) for this tutorial.

Azure Synapse Link for Azure Cosmos DB Create

Manage Hub

In the Manage Hub section, you can create a linked service to your Azure Cosmos DB SQL API. Select Azure Cosmos DB (SQL API) for this tutorial.

Azure Synapse Link for Azure Cosmos DB Create Manage Hub

Both options (Data Hub and Manage Hub) will display the following screen to create the link:

Azure Synapse Link for Azure Cosmos DB Create Linked Service

Query data

Now you are ready to start consuming data from Azure Cosmos DB by using notebooks.

Azure Synapse Link for Azure Cosmos DB Load Data

Azure Synapse Analytics will pre-populate all the information for you in the notebook:

Azure Synapse Link for Azure Cosmos DB Quert

Finally, you are ready to start executing notebooks and visualize information using your ApacheSpark cluster!

Azure Synapse Link for Azure Cosmos DB Resultset

Summary

I am amazed by Azure Synapse Link for Azure cosmos DB. There is no need to build an ETL process to query the operational data. It only takes around 5 seconds for data to be replicated in the Azure Cosmos DB analytical datastore which is impressive.

The templates in Azure Synapse Analytics even allow you to query the information without writing a single line of code!

What’s next?

Come back soon for more posts on Azure Synapse Analytics’ exciting features!

No Comments Yet.

Do you want to leave a comment?

Your email address will not be published. Required fields are marked *