Read, Write, Query data from Azure Cosmos DB in PowerShell

For a lot of my projects, I needed to read or write Azure Cosmos DB data from a PowerShell script. However, most PowerShell samples only cover creating and modifying the database itself, but not much about interacting with the data itself. So, I want to show you a few sample scripts, that I used in my scripts to access the Cosmos DB.

Script parameters

Given an Azure Cosmos DB that looks like this:

We need to modify the script parameters to match our DB:

The $CosmosDBEndpoint needs the URL of the DB, the $DatabaseID gets the Database name as you see it in the Data explorer, and the $collectionId needs the Collection name. For the $MasterKey, we can use the Primary Key from the Keys Tab in the Azure Cosmos DB.

Read Data

In the first sample, I want to read all entries from a Collection to use it in PowerShell. You can find the sample script here: Get-Data.ps1

The script will create a $CosmosDBData variable, which contains all data the was available in the Collection. You can work with this data in PowerShell as you would expect.

Query Data

In the second sample, I want to show you, how you can query data from a Collection, to only get pre-filtered data. You can find the sample script here: Query-Data.ps1
Since we want to pre-filter the data, we need to modify the query in the script to match our needs. In the first sample, you saw that we got all (two) entries from the DB, now we only want the entry that id matches what we search, so I use the following query:

This query will give us the one entry that we requested:

You obviously can modify the query to whatever you need, add different filters, or select which properties you want to have. For example, we can decide to only ask for the id and Property1:

Which will give us this output:

Write Data

In the third and last sample, I want to show you, how you actually can write / replace data from PowerShell to a specific collection. You can find the sample script here: Write-Data.ps1
The script is written in a way, where it first tries to modifies an existing entry (identified by the primary key) and if this fails with exit code 404 indicating it never existed, it will create a new entry.
To create our sample data that I used in the blog post, I created a simple json object with a guid as “/id” and some sample data:

You will need to modify this to whatever you need. You can find some samples how this can look like, in my reporting series blog posts, where I e.g. created client inventory data and send it to the Cosmos DB.

Conclusion

Over the time, I really learned to love the flexibility of having an Azure Cosmos DB in the backhand, which helps me to do reporting and automation in e.g. Intune. Since it was quite hard to find working samples, I wanted to share these with you, in case you may need them for some of your projects.


Posted

in

by

Comments

Leave a Reply

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