Azure Stream Analytics has "User Defined Function feature, where we can write our own function by using JavaScript.

Why UDF?

Stream Analytics provides strong built-in functions, such as string function, date time function, aggregate function, etc.

Built-in Functions

However, we sometime cannot find out-of-box function we need. That's where UDF comes in to rescue us :)

Let's see some scenario.

Scenario 1: Input contains JSON payload as string

Many data source, such as IoT Hub or Event Grid send data as JSON format. But it contains string data which is JSON payload as part of input. Example JSON looks like below.

{
  "type":"temperature",
  "temperature":30.2,
  "humidity":42.2,
  "location":"{\"longitude\":139.839478,\"latitude\":35.652832}"
}

If I simply upload this as sample input, I see location becomes text data.

In this case, we cannot get longitude value by following query.

SELECT
    location.longitude
INTO
    [YourOutputAlias]
FROM
    [YourInputAlias]

To solve this issue, we can add UDF.

1. Go to Functions and add new Javascript UDF.

2. Add function to parse json string. I named this as "ParseJson".

function main(x) {
    return JSON.parse(x);
}

3. Save the function and go back to query. Now we can write query as below.

SELECT
    UDF.ParseJson(location).longitude
INTO
    [YourOutputAlias]
FROM
    [YourInputAlias]

4. See the result. UDF.ParseJson(location) returns JSON object so we can access its property now.

Scenario 2: Use "Max" value of some type

For example, Azure Table Storage Log Tail Pattern requires some calculations for datetime field. Azure Stream Analytics has many built-in datetime function, but I cannot find Datetime.MaxValue type of function.

At the end, I cannot find native Javascript datetime maxvalue property, so I use "8640000000000000" by following some of stackoverflow threads. If anyone knows better solution, please teach me :)

So let's use UDF to implement function which returns expected value for log tail pattern.

1. Add another UDF and enter following script.

// Sample UDF which returns sum of two values.
function main(x) {
    var current = new Date(x);
    return (new Date(8640000000000000).getTime() - current)
}

2. Change input sample.

{
    "type":"temperature",
    "temperature":30.2,
    "humidity":42.2,
    "location":"{\"longitude\":139.839478,\"latitude\":35.652832}",
    "timestamp":"Thu Apr 01 2021 14:22:05 GMT+0900"
  },
  {
    "type":"temperature",
    "temperature":30.2,
    "humidity":42.2,
    "location":"{\"longitude\":139.839478,\"latitude\":35.652832}",
    "timestamp":"Thu Apr 01 2021 14:23:05 GMT+0900"
  }

3. Run following query.

SELECT
    timestamp, 
    UDF.GetInvertedTicks(timestamp),
    UDF.ParseJson(location).longitude
INTO
    [YourOutputAlias]
FROM
    [YourInputAlias]

4. Confirm the result.

Summary

We can still do our best to write query to get desired result with built-in functions, but it maybe easier to use UDF depending on scenarios :)