代码之家 › 专栏 › 技术社区 › krishna bh

流分析查询以从设备数据获取EnqueuedTime device\u id

azure-stream-analytics azure-iot-hub azure sql

krishna bh · 技术社区 · 6 年前

我正在尝试编写流分析查询,以将iothub数据推送到SQL数据库。

我的IOTHUB数据如下。

{
    "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
    }
}

现在,我正在尝试将Device\u Id、kWL1和EnqueuedTime写入SQL数据库。我的流分析查询如下所示。

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

当我运行流分析作业时,我可以看到Device\u Id和kWL1值,但排队时间显示为NULL。如何从IOTHub数据中获取排队时间。

{
"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
}
}

如何以上述格式获取Device\u Id和AMPL1。

提前感谢

3 回复 | 直到 6 年前

Michael Xu 6 年前

根据您提供的信息,我认为问题在于 IoTHub 是dumplicate,一个是输入在流分析中,但同时 IoTHub 是数据中的对象。您可以将名称修改为 IoTubMessageContent (例如,您可以将其更改为其他名称),如以下数据。

{
   "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
   }
}

然后修改查询,如下所示。

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

之后,当执行query命令时,将得到结果。

Jean-Sébastien 6 年前

另一种方法是修改查询,如下所示,以便能够读取IoTHub输入中名为IoTHub的嵌套JSON对象:

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

krishna bh 6 年前

最后,在@MichaelXu-MSFT的帮助下,我终于成功了。最后的查询如下所示。

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]