Misuse Azure Stream Analytics

We had one case where we synced user info from Azure Ad to SMS messaging service. And colleague of mine suggested to try Azure Stream Analytics. Well, we ended up to a different solution, but for fun of it I had to try the Stream Analytics approach. Actually Stream Analytics is marketed for analysing IoT or Logging data, but thinking outside of the box is always much more enjoyable.

Sync contactlists in SMS messaging service

For simplicity of this excercise, I chose only sync two contactlists in SMS messaging service. In the SMS messaging service you can create contact lists. So you can send SMS message easily to a group of people. You can create contacts (cell number, first name, last name, email) and groups (group name) to this service and attach contacts to group/s. The SMS messaging service also has a REST-service that we can you for handling contacts and groups.

Actual use case could be that you have groups like All, Team1, Team2. And when you add contact to either of Team1 or Team2, the added contact should also go to group All.

Building blocks

Because Stream Analytics can use blobs as inputs, I chose to fetch contactgroup info from SMS service to blobs. So I needed a blob storage (userblobs). There I created 3 containers: group1, group2 and outputblobs.

Next I created a Logic App (GetContactGroups) that fetches contacts from two groups to be synced and put results in blob containers group1 and group2. Then I added another Logic App (AddToGroup2) that adds contacts from blob in outputblobs-container to SMS service group2. I used group1 as master.

And then the fun part Stream Analytics.

Configure Stream Analytics to compare blob data

I created new Stream Analytics Job (CompareUsers), chose the capacity as 1 streaming unit and configured inputs (group1input, group2input) from blob containers group1 and group2. Then added output (addtogroup2) to blob container outputblobs.

Next I had to figure out how to compare the two inputs and end up to a list of ids that are not in group2 when looking up against group1 in the output.

Stream Analytics Query

Stream analytics uses SQL-like query language. At first I tried to write a straightforward SQL-clause to find which contacts in group1 are not in group2. But that approach didn’t end well, query editor constantly complaining about somtehing. So I had to go through Stream Analytics query language reference to find out how to achieve my goal.

Stream Analytics queries are relying in timetamps of the data to decide what data to include in query from inputs. And when joining two inputs, left outer join returns unmatched rows from right input with null column values. When I understood these two things, it was quite easy to figure out the needed query.

So I wanted contact ids that exist in group1input but not in group2input to be sent to outputblob. I made a left outer join from group1 to group2 and wanted contacts (e.g. events in Stream Analytics) that are coming to inputs in three minutes timewindow. Also with where clause g2.Id is null we accomplish that only contact ids exist only only in group1input end up to outputblob.

The three minutes timewindow is probably exaggeration, but I wanted to be sure that group1 and group2 blobs are available for analyze.

To ease the query development I ran the Logic App GetContactGroups and loaded the result blobs as samples to query editor. Then it was easy to test the query and see the output.

Let’s get this thing started

I pushed start for the CompareUsers Stream Analytics Job and waited it to start. Then I triggered GetContactGroups Logic App and watched the Stream Analytics Job Monitoring as inputs picked up the contact group data blobs and outputblob was generated.

I watched stream analytics monitor that input events flowed in and output events also generated. Then I checked that outputblob actually was generated and included the contact ids like I wanted them.

Next I triggered AddToGroup2 Logic App that read the output blob and updated the group2 in the SMS service. I checked the groups in SMS service and lo and behold, contacts that were missing in group2 were there and group1 and group2 were identical. To make things tidy I added deleting of the handled blobs to AddToGroup2 Logic App.

I tested what would happen if I run this thing again, only this time contact groups already synced. CompareUsers Stream Analytic Job processed inputs resulting no output. That was excpected since groups were identical.

I removed all contacts from group2 and triggered the process. Again resulting identical groups in SMS service. So I think that this thing works as planned. Nice!


Well, as I mentioned before my little expriment worked and I think that you can use things in Azure many ways, and also not in the way the designers intended. And that is so much fun.

If I have time, I will experiment a little and run this thing several times and approximate how much it is going to cost. Also it could be fun to try this with the original idea: syncing groups and users from Azure AD to SMS messaging service.

And if you want to know more details or contact me about something else, feel free doing so. Linkedin and twitter links are found up there and down there :)

Ps. If you wonder what was the SMS service used. It is Elisa Dialogi. Finnish only, sorry.

comments powered by Disqus