Azure Cosmos DB SQL Studio is a Hidden Gem!

Jay Gordon

This post was authored by Microsoft MVP and Azure Cosmos DB community champion Hasan Savran.  You can reach Hasan (@SavranWeb) on Twitter. 

Azure Cosmos DB Core (SQL) API has many features that can help you with your cloud projects in Microsoft Azure. Most of these features; especially the new ones are at the SDK level, and you need to know .NET or Java language to access them.

I developed the extension for all levels of users within the Azure Cosmos DB community. It is a user-friendly, free tool that is not browser-based and doesn’t require you to know C# or Java to interact with Azure Cosmos DB. Azure Cosmos DB SQL Studio is a free VSCode extension you can download directly from the VSCode Extensions tab or the VSCode Marketplace website.

In this article, we will walk though setting up and using the Azure Cosmos DB SQL Studio tool to:

• Start using the extension
• Connect to your Azure Cosmos DB account
• Run a query
• Point Read Operations
• Enabling Indexing Metrics
• Analyzing Data
• Performance Tuning
• Configuring Spatial Data Options

Start using the extension

If you are new to VSCode, you need to know how to start/use an extension. Press Ctrl + Shift + P and find Azure Cosmos DB SQL Studio from the list to start the extension.

Connect to your Azure Cosmos DB account

After you select the Azure Cosmos DB SQL Studio, you will see the following screen. This screen asks you how you would like to connect to your Azure Cosmos DB account. The first option “Connect by VsCode Azure Account” uses the VSCode Azure extension and a token to get access to your account. This option works well if you are the owner of the Azure subscription, if you do not own the subscription, you might not have the rights to run some of the functions it requires. I suggest you use the second option, copy and paste any of your connection strings from the Azure Portal to the textbox and click on the Connect button to continue. The application does not have write functionality so you can use the read-only keys if you like.

You should see all your databases and containers on the top. You can easily find how many physical partitions a container has by clicking the Partition List button. You will see a list in a popup window. In the following example, the selected container has 5 physical partitions. If you do not include the partition key of your container in your queries, Azure Cosmos DB will run your query in 5 physical partitions.

Run a query

To run a query, type your query and click on the Execute button. You will see the Request Charge and the Number of items in the middle section.

You can see execution Metrics by clicking on the tab to the right side of the results. There are two numbers here you should watch closely. Retrieved Documents value and Index Hit Documents value should be equal or very close to each other. The gap between these two numbers tells you that there might be some opportunities to make your query faster/cheaper. Usually, you fix this problem by changing your indexing policies.

Your query might need to retrieve data from multiple physical partitions if you do not include the partition key of the selected container in the query. You can see each physical partition’s execution plan by clicking on the link for the Number of Requests property.

You can easily look at the current indexing policy for the selected container by clicking on the Indexing Policy tab.

Point Read Operations

Azure Cosmos DB SQL Studio lets you make Point Read calls. You can reach this functionality by clicking on the Point Read tab. To make a Point Read call, you must pass the partition key value and the document id of the document. By giving these two values you are telling Azure Cosmos DB exactly where to find the data you are looking for. Azure Cosmos DB does not need to create a query execution plan for this request. Since you use fewer resources for Point Read operation, you will get charged fewer Request Units (RUs). In the following example, this point read operation costs only 1 R/U.

Enabling Indexing Metrics

The Indexing Metrics is another great feature of Azure Cosmos DB. You can see which indexes are getting used by queries and find out if Azure Cosmos DB suggests you create new indexes for your queries. This is not an easy feature to demonstrate, especially if you don’t know how to code. You can easily run this feature by checking the Display Indexing Metrics in Options. This will make the Indexing Metrics tab available beside the Results tab.

Azure Cosmos DB Indexing Engine communicates via the Indexing Metrics feature. It analyzes your query and suggests indexes. It does not analyze your data when it makes these suggestions. Test these suggestions before you try to put them into any production indexing policy. The following example shows Indexing Metrics in action, as you can see the WHERE clause has many filters and Azure Cosmos DB suggests me a Composite Index.

Analyzing Data

Sometimes, you may need to analyze the data returned by a query.  This can be a challenge since returned data is in JSON format. The data Analyzer tab will help you with simple analysis requirements. It supports three types of charts. To visualize the data,  define the x-axis and y-axis with two dropdowns.  At the bottom of the charts, Data Analyzer gives you simple stats of selected properties.

Performance Tuning

The process of improving query performance usually requires tracking query execution plans. By tracking this information, you can see if the changes you make in a query help the performance of a query. The Query Analyzer tracks the queries, execution plans, and indexing metrics of queries. You can easily compare queries to figure out what has changed. In the following example, you can see Query Analyzer in action. You need to click the Start button for Query Analyzer to start tracking query execution. From that point, you will see each executed query’s important execution metrics properties side by side.

If you like to see the query itself or the indexing suggestions of any tracked queries, click on Compare buttons to see more details about the queries. The following screenshot shows you that there are composite index suggestions for each query. To see the suggested indexes, click the Play button to run the query again and see its Indexing Metrics. Re-executed queries will not be tracked and listed again in the Query Analyzer.

Configuring Spatial Data Options

Azure Cosmos DB supports Geospatial data saved in the GeoJSON format. There are special types of indexes and functions to retrieve spatial data from Azure Cosmos DB. Use Azure Cosmos DB SQL Studio to render spatial data on a map by using the Map tab.

To see the data on the map, you need to set spatial options first by using the Options button. There are two important options you need to set here. The First one is the Spatial Property to draw, you need to give the property name that has the spatial data in your data model. The second one is the Spatial Property to use in filters, the application lets you draw a rectangle or a custom polygon to make a search in Azure Cosmos DB. The application dynamically creates an Azure Cosmos DB spatial query in the backend, and it needs to know which property has the spatial data. Usually, both options have the same property name unless you have multiple spatial properties in your data model, and you use one for searching and use the other one for drawing.

In the following example, Data Model has spatial data in the property named Location.

We are ready to use the Map tab after this setup. I have all the hurricane information including the location in this container. I want to see the data for Katrina, so I typed a very simple query without any spatial functions in the following example. The application will use the given spatial property name to render points on the map.

You can use the Draw Polygon or Draw Rectangle functions to search. In the following example, I draw a rectangle. The Application runs a query with a ST_WITHIN spatial function and displays the data under the rectangle. If you like to see the query, you can go back to Options and check the Display Spatial Queries option.

I hope you like the Azure Cosmos DB SQL Studio. I will continue to improve this application and as always I am open to feedback and suggestions.   Azure Cosmos DB SQL studio is available for download in the Visual Studio Code extensions Marketplace.

If you’d like to see more, check out the July 2022 Azure Global User Group video on YouTube for a full demonstration along with questions and answers right from our community users.  Join the user group on Meetup.com to see more great demonstrations from Microsoft and community members from across the globe.

About Azure Cosmos DB

Azure Cosmos DB is a fast and scalable cloud database for modern app development. See how to get started, dev/test, and run small production workloads free.

Get product updates, ask questions, and learn more about Azure Cosmos DB by following us on LinkedIn , YouTube, and Twitter.

About Hasan

Hasan Savran is a Business Intelligence Manager at Progressive Insurance Company. He spends his days architecting cutting edge business solutions by using the latest Web and Database technologies.  He has 15+ years  of experience in Web development and Business Intelligence. He is a Microsoft Data Platform MVP, Microsoft Certified Solutions Developer and the Vice President of Ohio North SQL Server User Group. He likes to write about SQL, Azure Cosmos DB, C#, and Front End development on his blog.

Jay Gordon Senior Program Manager, Azure Cosmos DB

Follow