As Brian Spendolini referring this new functionality can change scenarios of data changes related to SQL database by applying event-based architectures. Basically this was the main reason why I have decided to get into the details of this new Trigger type for Azure Functions. The project that I am working on for the past several years, uses couple of Azure services to handle event-based changes on Azure SQL database and send those accordingly to communicate with main application and some sort of message queues. The rough architecture picture of current set-up can be represented as such (this is just one of couple of scenarios):
This is the part which sends changes that happen on data layer side to Azure Cosmos DB. Those changes are in the format of JSON documents which are being read by main application back-end code.
In this case we can apply Azure SQL Trigger and execute Azure Function only when the changes actually occur in data layer side. Currently Azure Function from the provided flow chart is a time triggered function.
But let us now review how this new SQL Trigger works in practice. I will be using my Azure set-up where I have a database with Spotify data sample of music records. Simple data manipulations are being used to form JSON documents and send those to Cosmos DB container. Same logic as in the provided picture above. This time the goal is to send data until Azure Service Bus queue.
In the first step we will need a Azure SQL database and at least one table. I have a database called [data-platform-course] and a table [staging].[SPOTIFY_SAMPLE_JSON] where JSON documents are being kept after they are generated by simple SQL stored procedure. This is our table of interest since we need to send either updated data or new data back to Cosmos DB. Knowing this, here are the SQL commands that enables tracking of changes:
ALTER DATABASE [data-platform-course]
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);
ALTER TABLE staging.SPOTIFY_SAMPLE_JSON
ENABLE CHANGE_TRACKING;
One thing to mention here already is that you need a primary key for table that you are going to track changes on.
After these SQL commands are executed we can move on to the second step of creating new Azure Function. In my case, coding environment is Visual Studio code. Very first thing to do before creating new function, we need to install new NuGet package:
Once this step is done, we can already pick from new templates:
Here is my initial code where I’m just verifying that solution is functioning and does the job:
From the code we see that table name where we want to track the changes is [staging].[SPOTIFY_SAMPLE_JSON]. And I have also added a comment in the code what might be the values for enumerator Operation: 0, 1 or 2. We are going to use this later on when finishing up the logic for sending JSON documents to the Azure Service Bus.
And here is the first output after this new function was deployed and 10 new records were added to the table:
Operation = 0 was detected, 10 Insert operations have happened. So far so good. But we haven’t reached our main goal – send data to the Azure Service Bus. So let’s add additional logic to the function and see what will happen after the deployment.
After couple iterations of testing, deploying and testing again, I finally managed to push JSON documents to the Azure Service Bus queue:
And the final code looks like this:.jpg?width=714&height=533&name=Final_Code_Version%20(1).jpg)
Azure SQL Trigger operated on three main parameters:
1. Sql_Trigger_BatchSize
2. Sql_Trigger_PollingIntervalMs
3. Sql_Trigger_MaxChangesPerWorker
In the example above first parameter got the value of 10. Trigger has detected 10 changes and created a batch out of those. The default value is 100 changes for one table. The second parameter describes the delay in milliseconds between processing the batch of changes. So if many changes would be flowing in on the table in Azure SQL Database trigger would be generating batches of 100 and processing them one by one with 1 second delay. Finally, the third parameter shows the upper limit on the number of pending changes in the user table that are allowed per application-worker. If the count of changes exceeds this limit, it might result in scale-out. The default value is 1000.
To conclude this blog post and getting back to the first idea of changing event-based architectures I can say that this statement in the case that we took as an example, is too radical. On the other hand, the code of new Azure SQL Trigger Function code looks more elegant, and we also could make an assumption that we should get better performance just because it’s not needed to create bunch of Azure Database connections in the function code. In general, this new type of trigger is definitely more convenient to use when it comes to catching database changes and this should ease data engineers' daily tasks while writing C# or Python code for Azure Functions.
Overview of Azure SQL trigger for functions
- Azure SQL trigger uses SQL change tracking functionality to monitor a SQL table for changes
- It then triggers a function when a row is created, updated, or deleted.
- In consumption plan functions, automatic scaling is not supported for SQL trigger.
- Use premium or dedicated plans for scaling benefits with SQL trigger.
https://devblogs.microsoft.com/azure-sql/azure-sql-trigger-binding-for-azure-functions-goes-ga/
https://www.youtube.com/watch?v=JrZFXWrXKFo
https://www.nuget.org/packages/Microsoft.Azure.Functions.Worker.Extensions.Sql/3.0.1 81-preview