Insert Data Into Azure Cosmos DB Using Power Automate

Below are the components used in this document

1. Azure Cosmos DB
2. Power Automate

Introduction

 

Let me first give some brief description about Azure Cosmos DB for those who don’t know much about it. Azure Cosmos DB is a fully managed service that take away the problems associated with scaling a distributed database. Hence we don’t have to worry about managing Virtual Machines, hardware provisioning, setup and configuration, capacity, replication, software patching or cluster scaling etc. For more details you can read the MS Doc here.

 

Some time back I got a requirement to insert some data into Cosmos DB whenever a button was clicked on a Power App Form. This was achieved using Power Automate connecting to Azure Cosmos DB to insert the data. In my example, I already had Cosmos DB created. In case you need help with creating the database and performing any other operations on that database like putting the index, unique keys etc, you can refer the same MS docs mentioned above. Below is the step by step way to insert data to Azure Cosmos DB.

 

Open Power Automate and create a new Flow

 

NOTE

In case you already have a Power Automate created, you can ignore this step.

 

Step 1

 

Browse to https://india.flow.microsoft.com and provide your Office 365 account details to login

 

 

Step 2

 

Select either a “Templates” to start your Flow or you can search for the template. For my example I am selecting “Templates” from left navigation highlighted with yellow color in below image and then selecting Power Apps button, as trigger point for my Flow is a button click from Power Apps. You can select the template based on your requirements.

 

 

Step 3

 

Empty Flow would be opened.

 

 

Power Automate to insert data to Cosmos DB

 

Step 1

 

Name your Flow with the appropriate meaning. Add a new Step “Create or Update document”. In case you are not able to find it, then you should search for “Cosmos” and under that you would be able to select “Create or Update document” Step as shown in the below screen shot.

 

 

Step 2

 

Now once you add the step, you will need to fill the mandatory field values. The most important field is “Document”. When it comes to Cosmos DB, every data which gets stored in it, is considered as a JSON object. So we would need to create a JSON object of the data which needs to be inserted in Azure Cosmos DB. In my example: I have few text fields and few DateTime fields. My JSON object looks like below. Create your JSON object according to your Cosmos DB Column structure.

{
    "$pk": "intranet.contoso.com",
    "$id": "uniqueKey121",
    "id": "uniqueKey121",
    "SourceSiteTitle": {
        "$t": 2,
        "$v": "Champion"
    },
    "MigrationStartDate": {
        "$t": 9,
        "$v": "00637264155620000000"
    },
    "GoLiveDate": {
        "$t": 9,
        "$v": "00637270203800000000"
    },
    "PrimaryOwnerEmail": {
        "$t": 2,
        "$v": "[email protected]"
    },
    "MigrationStatus": {
        "$t": 2,
        "$v": "submitted"
    },
    "SourceSiteURL": {
        "$t": 2,
        "$v": "http://abcd.onmicrosoft.com"
    }
}

In the above example: "$pk" stands for the Partition Key for the Azure Cosmos DB. "$id" and “id” stands for row identifier which should be unique for every row we insert into the Cosmos DB. Remaining are the columns in my Cosmos DB. I am making all the values to lower case before I insert them to Cosmos DB as Cosmos DB Queries are case sensitive.

 

 

Step 3

 

The way Cosmos DB handles the DateTime column is different than how SQL server handles it. Here you can see that we have passed the date time in a string format. So these are the ticks from year 1970 till the date you want to enter. Example: "00637134504100000000" is Jan 01, 2020. "00637264155620000000" is 30 May, 2020. "00637233914640000000" is 25 Apr, 2020

 

Step 4

 

Now there are many other options in the “Create or Update Document” step which we can leverage during inserting the data to Cosmos DB.

 

Partition Key Value: this should be same as the value you gave in the JSON object.

 

IsUpsert: value = yes means it would update the data if already exist

 

Pre-Trigger: we can initiate an Azure Trigger before the data is inserted to the Cosmos DB using this option.

Post-Trigger: we can initiate an Azure Trigger after the data is inserted to the Cosmos DB using this option.

 

 

Step 5

 

Save your Flow and test the flow, we should see data inserted in Cosmos DB.

 

 

That is it. I hope you have learned something new from this article and will utilize this in your work.