Analyze Azure CosmosDB data using Synapse link and Transact-SQL language

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.

Azure Synapse workspace is advanced analytic solution that enables you to analyze various data sources in Azure cloud. Azure Synapse Link for CosmosDB connects Synapse workspace with Azure CosmosDB analytical store (preview) that contains a copy of CosmosDB data highly optimized for analytic workloads. With Synapse Link for CosmosDB, you can run complex analytic and create reports on top data stored in CosmosDB analytical store without impacting the main CosmosDB transactional containers.



Synapse Analytic s already enables you to use Apache Spark in Synapse workspace to analyze data and apply machine learning algorithms on Azure CosmosDB analytical data.

We are happy to announce that now Synapse workspace enables you to use Transact-SQL language to analyze data in CosmosDB analytical store. Synapse workspace has serverless T-SQL query engine where you can run T-SQL queries on top of your CosmosDB data.

Rich T-SQL surface in Synapse SQL endpoint enables a large ecosystem of tools and applications that can use T-SQL language to directly query CosmosDB analytical data. Synapse SQL represents a bridge between the analytical tools and globally distributed data stored in Azure CosmosDB containers.




With serverless Synapse SQL endpoint in Synapse workspace you can easily explore data in analytical store of CosmosDB container using T-SQL OPENROWSET function. You need to provide a connection string to your CosmosDB account and specify container name that should be analyzed. The following example shows how to explore sample data from your CosmosDB container:


SELECT TOP 10 * FROM OPENROWSET (     'CosmosDB',      'account=<CosmosDB account>;database=<database>;region=<region>;key=<CosmosDB key>',     Product    ) AS products



Besides reading data, Synapse SQL enables you to create complex reports that join multiple containers, group by results, apply window aggregates, and perform any analytic and data transformation that you usually apply on classic SQL database data.

Synapse SQL with CosmosDB analytical store enables you to combine the power of a low latency, globally distributed NoSQL database with a powerful relational SQL analytics engine. This solution enables you to leverage cloud-native hybrid transactional and analytical processing (HTAP) capabilities to provide near real-time analytics solutions on Azure CosmosDB data. The resources in your Azure CosmosDB transactional store are not affected by analytic queries and analytic query don’t spend any Azure CosmosDB resource units (RU). No- extract-transform-load (ETL) analytics against globally distributed operational data at scale enables business analysts and data engineers with T-SQL skills to run near real-time business intelligence and analytics without impacting the performance on the transactional workloads on Azure CosmosDB.


In this video you can see Synapse SQL link to Cosmos DB in action:



Find more information about querying Azure CosmosDB analytical store using SQL language in the article How to query CosmosDB data using serverless Synapse SQL endpoint and Synapse Link.

REMEMBER: these articles are REPUBLISHED. Your best bet to get a reply is to follow the link at the top of the post to the ORIGINAL post! BUT you're more than welcome to start discussions here:

This site uses Akismet to reduce spam. Learn how your comment data is processed.