Azure Stream Analytics query to input single data row into Azure SQL

Question

I can insert IoT sensor data into an Azure SQL database via an Azure Stream Analytics query,

SELECT  
* 
INTO
    myazuredb
FROM 
    mystreamin

except each time a sensor sample is taken, stream analytics creates roughly about 60 messages that are all the same and inserts them into the database. I would like just 1 row for each sample to be inserted based on the Date TIMESTAMP which are all identical. My first thought was to try GROUP BY but after some reading about Stream Analytics Query Language I tried.

SELECT CollectTop(1) OVER (ORDER BY Date ASC) as Date  
INTO
    myazuredb
FROM 
    mystreamin TIMESTAMP BY Time
GROUP BY Date, TumblingWindow(second, 60)

This query doesn't insert anything, not sure I am even on the right track. Any ideas on how to approach the problem would be great. Table: Date, DeviceId, Temperature, Humidity, Moisture, EventProcessedUtcTime, PartitionId, EventEnqueuedUtcTime, IoTHub, EventID

Your first query would pass through all the events the SA job receives and then add it to 'myazuredb'. How often is a sensor sample taken and fed to the SA job? — Sid Ramadoss
Output of CollectTop(1) is an array, you will have to convert it to a primitive type to insert to SQL. The query you would produce one output for every input record, if you would like a summarized output, you will have to use groupby. Are the input records identical? How would you like to summarize them? — Vignesh Chandramohan
A sensor sample would be taken once per hour. In each sample the records are identical, I only need one per sample, I was thinking GROUP BY summarized by Date(timestamp) should do it but I couldn't get it to work. Thanks. — kaedwards
If each sample has identical records and you want only one per sample, try TopOne() — Sid Ramadoss

Sid Ramadoss Sid Ramadoss · Accepted Answer · 2018-05-31T23:18:27

SELECT TopOne() OVER (ORDER BY Date ASC) as Date
INTO
myazuredb
FROM mystreamin TIMESTAMP BY Time
GROUP BY Date, TumblingWindow(second, 60)

TopOne() returns top record based on the ordering.

Azure Stream Analytics query to input single data row into Azure SQL

1 Answers