User Defined Function in Stream Analytics

Azure Stream Analytics provides a great solution for temporal queries of streams of data. The query language is pretty simple, especially if you have a background in SQL queries.

The list of built-in functions is a long list, ranging from aggregation, analytics, geospatial, records, scalars to the recently introduced anomaly detection.

But what if you want to write your own functions?

Stream Analytics supports three types of custom functions:

1. user-defined functions (UDF) written in Javascript
2. user-defined aggregates (UDA) written in Javascript
3. Machine learning endpoint disguised as functions

In this blog, I will show how easy it is to write and use your own custom logic in a Stream Analytics job. We will look at the user-defined functions.

Using an External Table output in Azure Functions

As seen in my previous blog, I am experimenting with passing data to a SqlAzure table using Azure Functions.

I first used the new and apparently experimental External Table output available in Azure Functions. Experimental just means in preview.

Due to throttling issues, I had to abandon it where I wanted to insert multiple (+200) records.

But this Azure Functions output is a very promising way to insert records in all kinds of data sources. I can recommend using it when you want to insert, alter or lookup data.

Let’s take a closer look.

Passing JSON arrays to SqlAzure from Azure Functions using OPENJSON

During my last project, we had to pass arrays of data to SqlAzure.

Normally, passing data is not that interesting. But we were passing data in bulk to SqlAzure so there is always a chance of throttling.

This is when you pass correct records towards SqlAzure but you reach certain limitations eg. if you hit the DTU limits.

We were getting a few hundred records which we first inserted one by one (using the ExternalTable (experimental) output). But we only we able to insert approx. forty records.

So we switched back to a stored procedure.

The second challenge was how to pass an array of records. Because we got the data as JSON, we wanted to pass an array of records like JSON.

In the example below, I show how to call a stored procedure within an Azure Function. The code could be simplified if not I wanted to do some transformations.