I would like to sample relevant data from Cosmos DB documents every time Cosmos DB receives a new document, and send it automatically to Azure SQL Database. The whole purpose is to create a live Power BI report that gets updated with the newest data that comes in to Cosmos DB, but since I don't need to show everything, I made a SQL Database in Azure and I am only missing how to make an Azure function that is triggered when Cosmos DB receives a new document. Also that the function needs to get the relevant data and sends it to Azure SQL. Is there a standard way to do this? Do I have to make an Azure Function App? I am very new to both coding and Azure, so I appreciate if someone can help using a beginner language. However, any help is appreciated.
1 Answer
You can easily do that with an Azure Function that uses the CosmosDB Trigger.
The trigger will be, well, triggered whenever there is one of more changes or and additions in the CosmosDB collection that you are targeting. Then simply add your own code for SQL insertion.
namespace CosmosDBSamplesV2
{
public static class CosmosTrigger
{
[FunctionName("CosmosTrigger")]
public static void Run([CosmosDBTrigger(
databaseName: "CosmosDBName",
collectionName: "CosmosColName",
ConnectionStringSetting = "CosmosDBConnection",
LeaseCollectionName = "leases",
CreateLeaseCollectionIfNotExists = true)]IReadOnlyList<Document> documents,
ILogger log)
{
if (documents != null && documents.Count > 0)
{
//do SQL insert with your code here
}
}
}
}
You can read how you can connect to a SQL db from an azure function here: https://learn.microsoft.com/en-us/azure/azure-functions/functions-scenario-database-table-cleanup
You can read more about CosmosDB related azure functions here: https://learn.microsoft.com/en-us/azure/azure-functions/functions-bindings-cosmosdb-v2
3 Comments
Hi Nick, Thanks a lot, very useful and quick. Could you also write a SQL code example just to see how it looks in an azure function? Just anything, I am curious to see its structure, since different platforms use different way of expressing a code. Else, Thank you for your help!
Rob Reagan
I believe it's also possible to set up Azure Logic Apps to do this. Your trigger would be a document insert on CosmosDB. The action you'd take would be in the SQL Server connector. This might be a no-code solution. I'm only commenting here because I have only read the documentation on Logic Apps but haven't performed an implementation myself.
Nick Chapsas
@SSRH The first link literally has an example step by step on how to write the SQL. I won't copy paste the code here for that as it seems redundant.