stream analytics query to get EnqueuedTime device_id from device data

0
votes

I am trying to write Stream analytics query to push iothub data to SQL database.

MY IOTHUB data as follows.

{
    "Device_Id":"P371602011",
    "kWL1":11.683551775144204,
    "EventProcessedUtcTime":"2017-12-28T07:21:14.3829760Z",
    "PartitionId":0,
    "EventEnqueuedUtcTime":"2017-12-28T07:21:04.6660000Z",
    "IoTHub":
    {
        "MessageId":null,
        "CorrelationId":null,
        "ConnectionDeviceId":"iotclient",
        "ConnectionDeviceGenerationId":"636500361000571958",
        "EnqueuedTime":"2017-12-28T07:21:04.0540000Z",
        "StreamId":null
    }
}

Now i am trying to write Device_Id, kWL1 and EnqueuedTime to SQL database. My Stream analytics query looks like below.

SELECT
Device_Id AS PowerScout,
IoTHub.EnqueuedTime AS [ReadingTime],
kWL1 AS [kW L1]
INTO
[DataBase]
FROM
[IoTHub]

When i run Stream analytics job, i can see Device_Id and kWL1 Values but EnqueuedTime is showing as NULL. How to get EnqueuedTime from IOTHub Data.

{
"DeviceData": [
    {
        "Device_Id": "5",
        "AMPSL1": 1.2515641182178531E-38
   }
],
"EventEnqueuedUtcTime": "2018-01-08T05:03:08.0840000Z",
"IoTHub": {
    "MessageId": null,
    "CorrelationId": null,
    "ConnectionDeviceId": "VHW1",
    "ConnectionDeviceGenerationId": "636509839893748612",
    "EnqueuedTime": "2018-01-08T05:03:06.7460000Z",
    "StreamId": null
}
}

How can i get Device_Id and AMPSL1 in above format.

Thanks in Advance

3
sqlazureazure-iot-hubazure-stream-analytics

3 Answers

0
votes

From your provided infomation, I think the problem is that the name IoTHub is dumplicate, one is inputs in your Stream Analytics,but meanwhile the IoTHub is an object in your data.You can modify the name as IoTHubMessageContent(for example, you can change it to other name) in your data such as following.

{
   "Device_Id":"P371602011",
   "kWL1":11.683551775144204,
   "EventProcessedUtcTime":"2017-12-28T07:21:14.3829760Z",
   "PartitionId":0,
   "EventEnqueuedUtcTime":"2017-12-28T07:21:04.6660000Z",
   "IoTHubMessageContent":
   {
       "MessageId":null,
       "CorrelationId":null,
       "ConnectionDeviceId":"iotclient",
       "ConnectionDeviceGenerationId":"636500361000571958",
       "EnqueuedTime":"2017-12-28T07:21:04.0540000Z",
       "StreamId":null
   }
}

And then modify the query as following.

SELECT
Device_Id AS PowerScout,
IoTHubMessageContent.EnqueuedTime AS [ReadingTime],
kWL1 AS [kW L1]
INTO
[DataBase]
FROM
[IoTHub]

After that,when executing the query command, the result will come.

enter image description here

0
votes

Another way would be to modify your query as follow to be able to read the nested JSON object called IoTHub in the IoTHub input:

SELECT
Device_Id AS PowerScout,
[IoTHub].[IoTHub].EnqueuedTime AS [ReadingTime],
kWL1 AS [kW L1]
INTO
[DataBase]
FROM
[IoTHub]
0
votes

Finally i am able to make it with the help of @Michael Xu - MSFT. The final query looks below.

WITH DeviceDataCollection AS 
( 
     SELECT GetArrayElement(DeviceData,0)as DeviceData, 
     IoTHub.EnqueuedTime as time FROM IoTHubIn     
) 
SELECT 
     DeviceData.Device_Id AS PowerScout,
     time AS [TimeStamp]
INTO
[DataBase]
FROM 
[DeviceDataCollection]