CODEWITHSUNDEEP
azureazure-stream-analytics
Markus Wimmer
Markus Wimmer

Reputation: 1

Azure Stream Analytics Query

I would like to write the following json string with Stream Anaytics into a SQL Database every Minute.

{
  "time": "2018-06-05T11:00:00Z",
  "deviceId": 10,
  "val": [ 200, 250, 300, 201, 501, 250, 890 ]
}

The Database has Columns "time","deviceId", 7 Columns for the Values.

My Query:

WITH seperate AS(
SELECT
    EventEnqueuedUtcTime AS Time,
    deviceId,
    AVG(GetArrayElement(val, 0)) AS '0',
    AVG(GetArrayElement(val, 1)) AS '1'
FROM
    Input
GROUP BY 
    EventEnqueuedUtcTime,
    deviceId,
    TumblingWindow(minute, 1)
)
SELECT * INTO Output1 FROM seperate WHERE deviceId = 1
SELECT * INTO Output2 FROM seperate WHERE deviceId = 2
SELECT * INTO Output3 FROM seperate WHERE deviceId = 3
SELECT * INTO Output4 FROM seperate WHERE deviceId = 4
SELECT * INTO Output5 FROM seperate WHERE deviceId = 5

here i have the problem that update the database all minute with all Values (6-from every 10 sek) i want every minute one Database Row

second thing is there another way to write the array values (without write AVG(GetArrayElement(val, 0)) AS '0', AVG(GetArrayElement(val, 1)) AS '1', ....

Upvotes: 0

Views: 427

Answers (1)

Peter Bons
Peter Bons

Reputation: 29860

GetArrayElement is your friend. See the docs

SELECT
    time,
    deviceId,
    GetArrayElement(val, 0) AS FirstElement,
    GetArrayElement(val, 1) AS SecondElement
    … and so on
INTO
    Output
FROM
    IotHub

You indicate that the data comes from a IOT hub every 10 seconds, and the data stored should be the "raw" datas. But you also want the data to be written to the database every minute. Then you will have to aggregate data or store it in the database directly.

You cannot use Azure Stream Analytics to buffer the incoming data and then write a 6 records to the database every minute.

You can apply windowing per minute but then the data will be aggregated during the time of the window. For example, you can store the average values of the array every minute.

If you want the raw values you cannot apply windowing and you have to write them to the database when the data comes in (which is every 10 seconds)

A windows query could be something like 

SELECT
    deviceId,
    AVG(GetArrayElement(val, 0)) AS AvgFirstElement,
    AVG(GetArrayElement(val, 1)) AS AvgSecondElement
    … and so on
INTO
    Output
FROM
    IotHub
GROUP BY 
    deviceId, 
    TumblingWindow(minute, 1)

Upvotes: 0

Related Questions