首页 文章

Azure流分析查询：将json数组数据发送到PowerBI

提问于
浏览 次
1

我正在尝试通过Stream Analytics将我的IoT Hub数据发送到Power BI . 只要IoT设备的json数据中不包含任何数组，这样就可以正常工作 .

My questions are: 如何修改流分析查询，以便PowerBI能够将数据解释为数组/表？我如何TIMESTAMP每个数组元素（使用"timecreated"）？

我发送给IoT Hub的json字符串如下所示：

{"sensordata":[{"name":"Temp_0","value":3,"timecreated":"2016-11-09T11:08:00Z"},
{"name":"Temp_0","value":7,"timecreated":"2016-11-09T11:08:02Z"},
{"name":"Temp_1","value":2,"timecreated":"2016-11-09T11:08:04Z"}]}

Stream Analytics从IoT Hub收到的内容：

[{"sensordata":[{"name":"Temp_0","value":3,"timecreated":"2016-11-09T11:08:00.0000000Z"},
{"name":"Temp_0","value":7,"timecreated":"2016-11-09T11:08:02.0000000Z"},
{"name":"Temp_1","value":2,"timecreated":"2016-11-09T11:08:04.0000000Z"}],    
"EventProcessedUtcTime":"2016-11-09T10:08:57.9325156Z","PartitionId":0,
 "EventEnqueuedUtcTime":"2016-11-09T10:08:47.8050000Z","IoTHub":
{"MessageId":null,"CorrelationId":null,"ConnectionDeviceId":"toCloudDevice",
"ConnectionDeviceGenerationId":"607350268321425367",
"EnqueuedTime":"0001-01-01T00:00:00.0000000","StreamId":null}}]

我的Stream Analytics查询如下所示：

SELECT sensordata.ArrayValue AS data,
  COUNT(*)
INTO
  [transmit-data]
FROM
  [receive-data] AS e
CROSS APPLY GetArrayElements(e.sensordata) AS sensordata
GROUP BY
  sensordata,
  tumblingWindow(Second, 10)

IoT Hub向Power BI发送的内容：

[{"data":{"name":"Temp_1","value":2,"timecreated":"2016-11-09T11:08:04.0000000Z"},
"count":1},{"data":{"name":"Temp_0","value":7,"timecreated":"2016-11-09T11:08:02.0000000Z"},
"count":1},{"data":{"name":"Temp_0","value":3,"timecreated":"2016-11-09T11:08:00.0000000Z"},
"count":1}]

PowerBI无法以合理的方式解释此数据 .

I would like to be able to generate graphs, for example having the time on the x-axis and having the values for Temp_0 on the y-axis.

关于这个问题的任何想法？非常感谢您的帮助 .

（顺便说一句：IoT设备的json字符串有不同数量的数组元素！）

powerbi azure-stream-analytics azure-iot-hub

1 回答

  • 1

    Power BI不允许复杂的对象，如数组或记录 . 您的问题是“数据”字段是一个记录 .

    请尝试以下查询：

    SELECT 
    sensordata.ArrayValue.data.Name,
    sensordata.ArrayValue.data.Value,
    sensordata.ArrayValue.data.Timecreated,
    COUNT(*)
INTO
  [transmit-data]
FROM
  [receive-data] AS e
CROSS APPLY GetArrayElements(e.sensordata) AS sensordata
GROUP BY
  sensordata,
  tumblingWindow(Second, 10)

    不幸的是，今天你不能在各个数组元素上应用TIMESTAMP BY . 单个事件只能有一个时间戳有一个限制 . 但是，您可以将作业拆分为两个，其中第一个作业执行CROSS APPLY并将事件插入中间事件中心，第二个作业执行TIMESTAMP BY和聚合

    回复于

相关问题