A Cosmos DB post? Yea, a Cosmos DB post. A while back in the start of my Powershell and APIs series I mentioned I may try to post data to Azure Cosmos DB REST API with Powershell. So, here we are.
What is Cosmos DB
From Microsoft:
Azure Cosmos DB is Microsoft’s globally distributed, multi-model database. With the click of a button, Azure Cosmos DB enables you to elastically and independently scale throughput and storage across any number of Azure’s geographic regions. It offers throughput, latency, availability, and consistency guarantees with comprehensive service level agreements (SLAs), something no other database service can offer. You can Try Azure Cosmos DB for Free without an Azure subscription, free of charge and commitments.
Sounds really cool. I’m already posting JSON data like BBQ and weather to Azure Log Analytics, so what am I doing with Cosmos DB? Well, Log Analytics is great, however the longest data retention I have available to me is 30 days. I want something longer term than that, so a proper database is needed. Of course I could have just gone with a regular SQL server, or some alternative, but where’s the fun in that? Plus, I love cool new stuff. So if you want to learn more about Cosmos DB check out the documentation here https://docs.microsoft.com/en-us/azure/cosmos-db/.
However, in all that documentation there are examples for C# and other languages to post & query against the REST API, but not Powershell. I did find how to query the REST API with Powershell here. There are 3 or 4 examples, but none on how to post data into your database. I ended up modifying the query collection to figure out how to post to the API. So lets get started.
Prerequisites
Much like posting Custom Logs to Azure Log Analytics, you’ll need your Cosmos DB URI and Primary key to build an authorization hash.
Building the Headers
This is the modified sample code from the Technet Powershell script I linked to previously.
Function Post-CosmosDb
{
[CmdletBinding()]
Param
(
[Parameter(Mandatory=$true)][String]$EndPoint,
[Parameter(Mandatory=$true)][String]$DataBaseId,
[Parameter(Mandatory=$true)][String]$CollectionId,
[Parameter(Mandatory=$true)][String]$MasterKey,
[Parameter(Mandatory=$true)][String]$JSON
)
$Verb = "POST"
$ResourceType = "docs";
$ResourceLink = "dbs/$DatabaseId/colls/$CollectionId"
$dateTime = [DateTime]::UtcNow.ToString("r")
$authHeader = Generate-MasterKeyAuthorizationSignature -verb $Verb -resourceLink $ResourceLink -resourceType $ResourceType -key $MasterKey -keyType "master" -tokenVersion "1.0" -dateTime $dateTime
$header = @{authorization=$authHeader;"x-ms-version"="2017-02-22";"x-ms-date"=$dateTime}
$contentType= "application/json"
$queryUri = "$EndPoint$ResourceLink/docs"
$result = Invoke-RestMethod -Method $Verb -ContentType $contentType -Uri $queryUri -Headers $header -Body $JSON
return $result.statuscode
}
The key change were changing the content type variable to “application/json”. Previously, it was “application/query+json”. The other key change is that I removed “x-ms-documentdb-isquery”=”True” out of the $header variable.
Response Codes and JSON
The API has a pretty good list of response codes, in particular response code 400 more often than not means there is something wrong with your JSON input. In my case, I was struggling at first. I wasn’t including an ID field, then I found where the ID field is required. However, if the ID name is capitalized the response will be 400. I figured that part out as seen in the below GIF. You can see in the Azure Portal, when manually creating a document, if ID is capitalized, it creates a lower case one, if your input doesn’t have an ID field it creates it automatically. Neither of these are true for the REST API. The ID field value needs to be unique as well.
Since the ID field needs to be unique I decided to use the date. I used get-date -format o
$date = get-date -format o $JSON | Add-Member -name "id" -MemberType NoteProperty -Value $date $JSON = convertto-json $JSON
The Code
# add necessary assembly
#
Add-Type -AssemblyName System.Web
# generate authorization key
Function Generate-MasterKeyAuthorizationSignature
{
[CmdletBinding()]
Param
(
[Parameter(Mandatory=$true)][String]$verb,
[Parameter(Mandatory=$true)][String]$resourceLink,
[Parameter(Mandatory=$true)][String]$resourceType,
[Parameter(Mandatory=$true)][String]$dateTime,
[Parameter(Mandatory=$true)][String]$key,
[Parameter(Mandatory=$true)][String]$keyType,
[Parameter(Mandatory=$true)][String]$tokenVersion
)
$hmacSha256 = New-Object System.Security.Cryptography.HMACSHA256
$hmacSha256.Key = [System.Convert]::FromBase64String($key)
$payLoad = "$($verb.ToLowerInvariant())`n$($resourceType.ToLowerInvariant())`n$resourceLink`n$($dateTime.ToLowerInvariant())`n`n"
$hashPayLoad = $hmacSha256.ComputeHash([System.Text.Encoding]::UTF8.GetBytes($payLoad))
$signature = [System.Convert]::ToBase64String($hashPayLoad);
[System.Web.HttpUtility]::UrlEncode("type=$keyType&ver=$tokenVersion&sig=$signature")
}
# query
Function Post-CosmosDb
{
[CmdletBinding()]
Param
(
[Parameter(Mandatory=$true)][String]$EndPoint,
[Parameter(Mandatory=$true)][String]$DataBaseId,
[Parameter(Mandatory=$true)][String]$CollectionId,
[Parameter(Mandatory=$true)][String]$MasterKey,
[Parameter(Mandatory=$true)][String]$JSON
)
$Verb = "POST"
$ResourceType = "docs";
$ResourceLink = "dbs/$DatabaseId/colls/$CollectionId"
$dateTime = [DateTime]::UtcNow.ToString("r")
$authHeader = Generate-MasterKeyAuthorizationSignature -verb $Verb -resourceLink $ResourceLink -resourceType $ResourceType -key $MasterKey -keyType "master" -tokenVersion "1.0" -dateTime $dateTime
$header = @{authorization=$authHeader;"x-ms-version"="2017-02-22";"x-ms-date"=$dateTime}
$contentType= "application/json"
$queryUri = "$EndPoint$ResourceLink/docs"
$result = Invoke-RestMethod -Method $Verb -ContentType $contentType -Uri $queryUri -Headers $header -Body $JSON
return $result.statuscode
}
# fill the target cosmos database endpoint uri, database id, collection id and masterkey
$CosmosDBEndPoint = "https://yourcosmosdbname.documents.azure.com:443/"
$DatabaseId = "Database"
$CollectionId = "Collection"
$MasterKey = "your key"
# execute
Post-CosmosDb -EndPoint $CosmosDBEndPoint -DataBaseId $DataBaseId -CollectionId $CollectionId -MasterKey $MasterKey -JSON $weather
Again most of this code came from the Microsoft examples on querying Cosmos DB with Powershell.
Conclusion
You can post data to Cosmos DB REST API with Powershell. You need properly formated JSON payload and you need to include an ID field that is unique, other than that its relatively easy, just like posting data to any other REST API.
I have posted this code on github at the following link https://github.com/scautomation/CosmosDB-REST-API
I’m still new to github so be gentle.
3 thoughts on “POST to Cosmos DB REST API with Powershell”
Comments are closed.