%3CLINGO-SUB%20id%3D%22lingo-sub-1791727%22%20slang%3D%22en-US%22%3ERe%3A%20Analyze%20Azure%20CosmosDB%20data%20using%20Synapse%20link%20and%20Transact-SQL%20language%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1791727%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20for%20Sharing%20with%20the%20Community%26nbsp%3B%3CIMG%20class%3D%22lia-deferred-image%20lia-image-emoji%22%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Fhtml%2Fimages%2Femoticons%2Fcool_40x40.gif%22%20alt%3D%22%3Acool%3A%22%20title%3D%22%3Acool%3A%22%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1787875%22%20slang%3D%22en-US%22%3EAnalyze%20Azure%20CosmosDB%20data%20using%20Azure%20Synapse%20Link%20and%20Transact-SQL%20language%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1787875%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fsynapse-analytics%2Foverview-what-is%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3EAzure%20Synapse%20Analytics%20workspace%3C%2FA%3E%20is%20advanced%20analytic%20solution%20that%20enables%20you%20to%20analyze%20various%20data%20sources%20in%20Azure%20cloud.%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fcosmos-db%2Fsynapse-link%3Ftoc%3D%2Fazure%2Fsynapse-analytics%2Ftoc.json%26amp%3Bbc%3D%2Fazure%2Fsynapse-analytics%2Fbreadcrumb%2Ftoc.json%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3EAzure%20Synapse%20Link%20for%20Azure%20Cosmos%20DB%3C%2FA%3E%20connects%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fsynapse-analytics%2Foverview-what-is%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3EAzure%20Synapse%20Analytics%20workspace%3C%2FA%3E%20with%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fcosmos-db%2Fanalytical-store-introduction%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3EAzure%20CosmosDB%20analytical%20store%20(preview)%3C%2FA%3E%20that%20contains%20a%20copy%20of%20Cosmos%20DB%20data%20highly%20optimized%20for%20analytic%20workloads.%20With%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fcosmos-db%2Fsynapse-link%3Ftoc%3D%2Fazure%2Fsynapse-analytics%2Ftoc.json%26amp%3Bbc%3D%2Fazure%2Fsynapse-analytics%2Fbreadcrumb%2Ftoc.json%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3EAzure%20Synapse%20Link%20for%20CosmosDB%3C%2FA%3E%2C%20you%20can%20run%20complex%20analytic%20and%20create%20reports%20on%20top%20data%20stored%20in%20Cosmos%20DB%20analytical%20store%20without%20impacting%20the%20main%20Cosmos%20DB%20transactional%20containers.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22JovanPop_0-1602849396921.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F227131iEE0AD6F8475509C6%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22JovanPop_0-1602849396921.png%22%20alt%3D%22JovanPop_0-1602849396921.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20addition%20to%20the%20languages%20available%20in%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fsynapse-analytics%2Fspark%2Fapache-spark-overview%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3EApache%20Spark%20in%20Azure%20Synapse%20Analytics%3C%2FA%3E%2C%20you%20can%20also%3CSTRONG%3E%26nbsp%3Buse%20Transact-SQL%20language%20to%20analyze%20data%20in%20Cosmos%20DB%20analytical%20store%3C%2FSTRONG%3E.%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fsynapse-analytics%2Fsql%2Fon-demand-workspace-overview%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3EServerless%20Synapse%20SQL%20pool%3C%2FA%3E%20enables%20you%20to%20run%20T-SQL%20queries%20on%20top%20of%20your%20Cosmos%20DB%20analytical%20data.%3C%2FP%3E%0A%3CP%3ERich%20T-SQL%20surface%20enables%20a%20large%20ecosystem%20of%20tools%20and%20applications%20to%20directly%20query%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fcosmos-db%2Fanalytical-store-introduction%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3ECosmos%20DB%20analytical%20data%3C%2FA%3E.%20You%20can%20easily%20explore%20data%20in%20analytical%20store%20of%20CosmosDB%20container%20using%20T-SQL%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsynapse-analytics%2Fsql%2Fdevelop-openrowset%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3EOPENROWSET%3C%2FA%3E%20function.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22JovanPop_1-1602849396940.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F227132iDAE47EA5F5C8D271%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22JovanPop_1-1602849396940.png%22%20alt%3D%22JovanPop_1-1602849396940.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsynapse-analytics%2Fsql%2Fdevelop-openrowset%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3EOPENROWSET%3C%2FA%3E%20function%20represents%20a%20bridge%20between%20the%20analytical%20tools%20and%20globally%20distributed%20data%20stored%20in%20Azure%20Cosmos%20DB%20containers.%20You%20need%20to%20provide%20a%20connection%20string%20to%20your%20Cosmos%20DB%20account%20and%20specify%20container%20name%20that%20should%20be%20analyzed.%20The%20following%20example%20shows%20how%20to%20explore%20sample%20data%20from%20your%20Cosmos%20DB%20container%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3ESELECT%20TOP%2010%20*%0AFROM%26nbsp%3BOPENROWSET%26nbsp%3B(%0A%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B'CosmosDB'%2C%26nbsp%3B%0A%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B'account%3D%3CCOSMOSDB%20account%3D%22%22%3E%3Bdatabase%3D%3CDATABASE%3E%3Bregion%3D%3CREGION%3E%3Bkey%3D%3CCOSMOSDB%20key%3D%22%22%3E'%2C%0A%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BProduct%26nbsp%3B%26nbsp%3B%26nbsp%3B%0A)%26nbsp%3BAS%26nbsp%3Bproducts%3C%2FCOSMOSDB%3E%3C%2FREGION%3E%3C%2FDATABASE%3E%3C%2FCOSMOSDB%3E%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20create%20complex%20reports%20that%20join%20multiple%20containers%2C%20group%20by%20results%2C%20apply%20window%20aggregates%2C%20and%20perform%20any%20analytic%20and%20data%20transformation%20that%20you%20usually%20apply%20on%20classic%20SQL%20database%20data.%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fcosmos-db%2Fsynapse-link%3Ftoc%3D%2Fazure%2Fsynapse-analytics%2Ftoc.json%26amp%3Bbc%3D%2Fazure%2Fsynapse-analytics%2Fbreadcrumb%2Ftoc.json%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3EAzure%20Synapse%20Link%20for%20Azure%20Cosmos%20DB%3C%2FA%3E%20enables%20you%20to%20combine%20the%20power%20of%20a%20low%20latency%2C%20globally%20distributed%20NoSQL%20database%20with%20a%20powerful%20relational%20SQL%20analytics%20engine.%20This%20solution%20enables%20you%20to%20leverage%20cloud-native%20hybrid%20transactional%20and%20analytical%20processing%20(HTAP)%20capabilities%20to%20provide%20near%20real-time%20analytics%20solutions%20on%20Azure%20Cosmos%20DB%20data.%20The%20resources%20in%20your%20Azure%20CosmosDB%20transactional%20store%20are%20not%20affected%20by%20analytic%20queries%20and%20analytic%20query%20don%E2%80%99t%20spend%20any%20Azure%20CosmosDB%20resource%20units%20(RU).%20No-%20extract-transform-load%20(ETL)%20analytics%26nbsp%3Bagainst%20globally%20distributed%20operational%20data%20at%20scale%20enables%20business%20analysts%20and%20data%20engineers%20with%20T-SQL%20skills%20to%20run%20near%20real-time%20business%20intelligence%20and%20analytics%20without%20impacting%20the%20performance%20on%20the%20transactional%20workloads%20on%20Azure%20Cosmos%20DB.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20this%20video%20you%20can%20see%20Synapse%20SQL%20Link%20for%20Cosmos%20DB%20in%20action%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3C%2FP%3E%3CDIV%20class%3D%22video-embed-center%20video-embed%22%3E%3CIFRAME%20class%3D%22embedly-embed%22%20src%3D%22https%3A%2F%2Fcdn.embedly.com%2Fwidgets%2Fmedia.html%3Fsrc%3Dhttps%253A%252F%252Fwww.youtube.com%252Fembed%252FWQlqKIpIuiU%253Ffeature%253Doembed%26amp%3Bdisplay_name%3DYouTube%26amp%3Burl%3Dhttps%253A%252F%252Fwww.youtube.com%252Fwatch%253Fv%253DWQlqKIpIuiU%26amp%3Bimage%3Dhttps%253A%252F%252Fi.ytimg.com%252Fvi%252FWQlqKIpIuiU%252Fhqdefault.jpg%26amp%3Bkey%3Db0d40caa4f094c68be7c29880b16f56e%26amp%3Btype%3Dtext%252Fhtml%26amp%3Bschema%3Dyoutube%22%20width%3D%22600%22%20height%3D%22337%22%20scrolling%3D%22no%22%20title%3D%22YouTube%20embed%22%20frameborder%3D%220%22%20allow%3D%22autoplay%3B%20fullscreen%22%20allowfullscreen%3D%22true%22%3E%3C%2FIFRAME%3E%3C%2FDIV%3E%3CP%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFind%20more%20information%20about%20querying%20Azure%20CosmosDB%20analytical%20store%20using%20T-SQL%20language%20in%20the%20article%20%3CA%20href%3D%22https%3A%2F%2Faka.ms%2Fsynapse-sql-link-cosmosdb%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3EHow%20to%20query%20Azure%20CosmosDB%20data%20using%20serverless%20Synapse%20SQL%20pool%20and%20Synapse%20Link%20for%20Cosmos%20DB%3C%2FA%3E.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-1787875%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fsynapse-analytics%2Foverview-what-is%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%20target%3D%22_blank%22%3EAzure%20Synapse%20Analytics%20workspace%3C%2FA%3E%20is%20advanced%20analytic%20solution%20that%20enables%20you%20to%20analyze%20various%20data%20in%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fcosmos-db%2Fanalytical-store-introduction%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%20target%3D%22_blank%22%3EAzure%20CosmosDB%20analytical%20store%20(preview)%3C%2FA%3E%20that%20contains%20a%20copy%20of%20CosmosDB%20data%20highly%20optimized%20for%20analytic%20workloads.%20With%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fcosmos-db%2Fsynapse-link%3Ftoc%3D%2Fazure%2Fsynapse-analytics%2Ftoc.json%26amp%3Bbc%3D%2Fazure%2Fsynapse-analytics%2Fbreadcrumb%2Ftoc.json%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%20target%3D%22_blank%22%3EAzure%20Synapse%20Link%20for%20Azure%20Cosmos%20DB%3C%2FA%3E%2C%20you%20can%20run%20complex%20analytic%20and%20create%20reports%20on%20top%20data%20stored%20in%20Cosmos%20DB%20analytical%20store%20without%20impacting%20the%20main%20Cosmos%20DB%20transactional%20containers.%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1787875%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESynapse%20SQL%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EUpdates%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Microsoft

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

JovanPop_0-1602849396921.png

 

In addition to the languages available in Apache Spark in Azure Synapse Analytics, you can also use Transact-SQL language to analyze data in Cosmos DB analytical storeServerless Synapse SQL pool enables you to run T-SQL queries on top of your Cosmos DB analytical data.

Rich T-SQL surface enables a large ecosystem of tools and applications to directly query Cosmos DB analytical data. You can easily explore data in analytical store of CosmosDB container using T-SQL OPENROWSET function. 

JovanPop_1-1602849396940.png

 

OPENROWSET function represents a bridge between the analytical tools and globally distributed data stored in Azure Cosmos DB containers. You need to provide a connection string to your Cosmos DB account and specify container name that should be analyzed. The following example shows how to explore sample data from your Cosmos DB container:

 

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

 

 

You can 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.

Azure Synapse Link for Azure Cosmos DB 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 Cosmos DB 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 Cosmos DB.

 

In this video you can see Synapse SQL Link for Cosmos DB in action:

 

 

Find more information about querying Azure CosmosDB analytical store using T-SQL language in the article How to query Azure CosmosDB data using serverless Synapse SQL pool and Synapse Link for Cosmos DB.

1 Comment

Thanks for Sharing with the Community :cool: