DEV Community

loading...

Run JavaScript code in Azure Stream Analytics query

kenakamu profile image Kenichiro Nakamura ・3 min read

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}"
}
Enter fullscreen mode Exit fullscreen mode

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

Alt Text

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

SELECT
    location.longitude
INTO
    [YourOutputAlias]
FROM
    [YourInputAlias]
Enter fullscreen mode Exit fullscreen mode

Alt Text

To solve this issue, we can add UDF.

1. Go to Functions and add new Javascript UDF.

Alt Text

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

function main(x) {
    return JSON.parse(x);
}
Enter fullscreen mode Exit fullscreen mode

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]
Enter fullscreen mode Exit fullscreen mode

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

Alt Text

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)
}
Enter fullscreen mode Exit fullscreen mode

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"
  }
Enter fullscreen mode Exit fullscreen mode

3. Run following query.

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

4. Confirm the result.

Alt Text

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 :)

Discussion (0)

pic
Editor guide