CODEWITHSUNDEEP
sqlfunctionazuretriggersazure-cosmosdb
user10662179
user10662179

Reputation:

How to automatically transfer data from Cosmos DB to Azure SQL database?

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.

Upvotes: 0

Views: 1669

Answers (1)

Nick Chapsas
Nick Chapsas

Reputation: 7190

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

Upvotes: 1

Related Questions