时间戳

首页 > 解决方案 > 流分析 - 将传入的 JSON 转换为 PowerBI 流数据集

azure - 流分析 - 将传入的 JSON 转换为 PowerBI 流数据集

问题描述

我有一个传感器，它以以下格式（通过 Modbus 服务器）向 IoTHub 报告数据：

15/05/2018 14:56:56> Device: [dev], Data:[[{"DisplayName":"Temperature","HwId":"PI-1","Address":"400002","Value":"192","SourceTimestamp":"2018-05-15 13:56:52"},{"DisplayName":"Humidity","HwId":"PI-1","Address":"400001","Value":"397","SourceTimestamp":"2018-05-15 13:56:52"}]]Properties:
'content-type': 'application/edge-modbus-json'

15/05/2018 14:57:00> Device: [dev], Data:[[{"DisplayName":"Temperature","HwId":"PI-1","Address":"400002","Value":"201","SourceTimestamp":"2018-05-15 13:56:57"},{"DisplayName":"Humidity","HwId":"PI-1","Address":"400001","Value":"397","SourceTimestamp":"2018-05-15 13:56:57"}]]Properties:
'content-type': 'application/edge-modbus-json'

15/05/2018 14:57:06> Device: [dev], Data:[[{"DisplayName":"Temperature","HwId":"PI-1","Address":"400002","Value":"201","SourceTimestamp":"2018-05-15 13:57:02"},{"DisplayName":"Humidity","HwId":"PI-1","Address":"400001","Value":"397","SourceTimestamp":"2018-05-15 13:57:02"}]]Properties:
'content-type': 'application/edge-modbus-json'

15/05/2018 14:57:10> Device: [dev], Data:[[{"DisplayName":"Temperature","HwId":"PI-1","Address":"400002","Value":"195","SourceTimestamp":"2018-05-15 13:57:07"},{"DisplayName":"Humidity","HwId":"PI-1","Address":"400001","Value":"397","SourceTimestamp":"2018-05-15 13:57:07"}]]Properties:
'content-type': 'application/edge-modbus-json'

每个传感器都在一个单独的数组条目中报告，并在传感器的显示名称和值之间进行拆分。

我想要的是一个 JSON Payload，我可以将它输入 PowerBI，所以这需要按顺序排列：

时间戳：时间，湿度：湿度值，温度：温度值

如何构建合适的流分析查询来执行此操作？这种输入格式是典型的 Modbus 或 OPC-UA 类型设备，因此可能会遇到几次。

我尝试使用 GetArrayElement/(s) 但该数组在 JSON 中没有名称，因此没有可引用的内容。

标签： azureazure-stream-analyticsstream-analytics

解决方案

实际上找到了一种更简单的方法来直接处理这个没有函数。将 CASE 语句与 LAST 结合使用。

SELECT System.Timestamp as timestamp,

    CASE Address
        WHEN '400001' THEN cast(Value as float)/10 ELSE last(cast(Value as float)/10) over (partition by HwId limit duration(day,1) when Value is not null and Address like '400001')
    END
    AS Humidity,
    CASE Address
        WHEN '400002' THEN cast(Value as float)/10 ELSE last(cast(Value as float)/10) over (partition by HwId limit duration(day,1) when Value is not null and Address like '400002')
    END
    AS Temperature,
    CASE Address
        WHEN '400003' THEN cast(Value as float)/10 ELSE last(cast(Value as float)/10) over (partition by HwId limit duration(day,1) when Value is not null and Address like '400003')
    END
    AS Pressure    
INTO PowerBI
FROM IoTHub as event

推荐阅读