Reading Azure Cosmos DB Data with Azure Data Explorer

By:   |   Updated: 2023-06-07   |   Comments   |   Related: > Azure Cosmos DB


We have data stored as documents in an Azure Cosmos DB container. We want to read the data from this container in near real-time. We have been considering using Azure Data Explorer to query the data but are unsure how. Is this even possible?


Azure Cosmos DB is a globally distributed, multi-model database service in Azure, typically used as a NoSQL document database. You can find an introduction to Azure Cosmos DB in the following tips:

On the other hand, Azure Data Explorer (ADX) is a big data analytics platform intended to analyze high volumes of data in near real-time. In contrast with Azure Synapse Analytics (dedicated pools), ADX is used for interactive analytics, typically on log or event data. You might say ADX has more in common with Azure Synapse Analytics Serverless SQL Pools as a query engine than a regular database. In fact, ADX is also available as a query pool in Synapse Analytics but is, at the moment, still in preview and not available in every region.

adx pools in synapse analytics

The query language of ADX is the Kusto Query Language (KQL), which is also used in Azure Monitoring, for example. You can find more info on ADX in the official documentation.

The feature described in this tip (reading Cosmos DB data directly in ADX) is still in preview at the time of writing. It's possible that functionality or appearances might change later. The same feature is sometimes called "Cosmos DB to Azure Data Explorer Synapse Link," although it has nothing to do with Synapse Analytics at first glance. This is because ADX was released as a product before Azure Synapse Analytics. But the Kusto technology is part of Synapse Analytics (alongside SQL and Spark). Synapse Link is the name for connections between operational data stores and analytical systems.

How to Read Cosmos DB Data with Azure Data Explorer


First, you need an Azure Cosmos DB account (using the NoSQL API) with a database and a container with documents in it. You can follow the steps in the tips, Analyze Azure Cosmos DB data with Synapse Serverless SQL Pools and How to Install a Sample Database in Azure Cosmos DB, to get your environment started.

Second, we need an ADX cluster and database (again, at the time of writing, we cannot use Data Explorer Pools in Synapse Analytics). In the Azure Portal, create a new resource.

create new resource in portal

This will take you to the marketplace, where you can search for "data explorer."

ADX in the marketplace

Click on Create and again on Create on the next page.

click on create to provision a new ADX cluster

In the next screen, you can configure your subscription, resource group, and cluster details.

configure your ADX cluster

For Workload, choose the "Dev/test" option, which will be the cheapest. Pricing is based on the time the cluster is running and the workload size. An ADX cluster will be paused automatically if it is inactive for five days (which cannot be changed). You can find more info about this behavior in the documentation.

In the next section, you can optionally configure the autoscale option. We will set this to Manual scale with an instance count of 1, the smallest option.

configure autoscale options

In the Configurations tab, you can adjust some extra settings. Make sure the auto-stop cluster option is set to On.

ADX configurations options

In the Security tab, verify that the cluster has a system-assigned managed identity. This will be important later when we want to connect with the Cosmos DB database.

ADX security options

Finally, ensure the cluster can be accessed from public endpoints in the Network sections.

ADX network settings

Review your configuration and click Create once validation has finished.

review ADX cluster config and create

Once the cluster has been created, you can create a new database on the overview page.

create new database for ADX cluster

Give the database a name and choose a retention and cache period.

configure database options

Once the database is created, you can find it in the Databases page under the data section. You can only see the list of available databases if the ADX cluster is running. It can take some time to resume a paused cluster.

newly created database

Create the Connection to Azure Cosmos DB

Before we can create a connection to Cosmos DB, we need to create a table in ADX that will hold the data. We will also need to specify a mapping that will translate the hierarchical structure of the JSON documents to the relational structure of the table.

In the Databases overview, click on your database name to go to its overview page.

database overview page

In the left menu, click Query to go to the query editor. There we can specify our create table statement (it looks a lot like SQL, but it's a Kusto query. Don't forget the dot before create).

.create table script in the ADX query editor
.create table BeerCheckins(

We're using the same sample data as in the tip Analyze Azure Cosmos DB data with Synapse Serverless SQL Pools. Once the table is created, you can view its definition on the left:

the table is successfully created

Now we can specify the mapping that will detail how each JSON field will map to a corresponding column of the table. It's possible that some JSON arrays are stored as JSON inside the ADX table.

create table mapping

The mapping is very similar to the WITH clause you specify when using the OPENROWSET command in Azure Synapse Serverless SQL Pools:

openrowset statement with with clause in serverless sql pools
.create table BeerCheckins ingestion json mapping "DocumentMapping"

Once you have defined the table and the mapping, you can create a connection to the Cosmos DB container. In the overview page, click on Create data connection in the Data ingestion square, and choose Cosmos DB as the source.

create connection to cosmos DB

In the configuration window, choose the database you just created, a name for the connection, and your Cosmos DB account, database, and container.

configure cosmos db connection in ADX

You will also need to specify the table and its mapping. Data from the container is fetched from the Azure Cosmos DB change feed. The setup wizard will take care of everything. It will connect using the managed identity you specify and give appropriate permissions so the identity can read the data from the container. If you want to retrieve all of the data from the container, you need to specify a date before the container was created to make sure ADX imports everything.

Once the connection is created, you might have to wait for ADX to import all the data. You can view the size of the database in the portal:

database size

Query the Data

Once the data is imported, you can query it using the Kusto language. Entering the table name only will fetch all of the data:

query adx table

With the following query, we can do a row count of the table:

| count
counting rows in ADX

It's even possible to render charts directly in the editor:

render a time chart in ADX
| summarize count() by bin(todatetime(created_at),30d)
| render timechart
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on Koen has been awarded the Microsoft MVP data platform award for many years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2023-06-07

Comments For This Article

get free sql tips
agree to terms