多输入Azure流分析查询

Question

问题

我有一个Azure物联网中心发送信息给一个Azure流分析工作。

每条消息都包含一个“NodeName”。我有一个表'plcnext_nodes‘，它对每个节点都有一个唯一的'NodeId’，其对应的'NodeName‘。

如何使用来自'plcnext_nodes‘表的输入和物联网集线器消息使用'NodeId’将事件数据存储在另一个SQL表中？

我想使用'NodeId‘而不是'NodeName’，因为有些名称可能会变得很长，并且在每条消息中反复保存它们都是浪费存储。

期望解

我想解析来自集线器的以下IoT消息

{
    "NodeName": "ns=5;s=Arp.Plc.Eclr/DI2",
    "NodeDataType": "Boolean",
    "EventValue": 0,
    "EventMeasuredUtcTime": "2019-11-11T12:15:22.4830000Z",
    "EventProcessedUtcTime": "2019-11-11T12:41:57.1706596Z",
    "EventEnqueuedUtcTime": "2019-11-11T12:15:32.1260000Z",
    "IoTHub": {
        ...
    }
}

将“NodeName”与plcnext_nodes表中的“NodeId”进行比较，以获得适当的“NodeId”：

NodeId  NodeName                 NodeDataType
---------------------------------------------
1       ns=5;s=Arp.Plc.Eclr/DI1  Boolean
2       ns=5;s=Arp.Plc.Eclr/DI2  Boolean
...

要获得以下输出并插入plcnext_events表，请执行以下操作：

NodeId  EventValue  EventMeasured
-----------------------------------------------
1       0           2019-11-11 12:15:22.4830000

查询

我尝试了以下关于的查询：

SELECT
    NodeId,
    EventValue,
    EventMeasuredUtcTime,
    EventEnqueuedUtcTime,
    EventProcessedUtcTime
INTO
    [plcnext_events]
FROM
    [plcnext_nodes],
    [iot_hub]
WHERE
    [iot_hub].NodeName = [plcnext-nodes].NodeName

但是FROM中不支持JOIN，而且由于JOIN的DATEDIFF限制，我无法使用JOIN子句( plcnext_nodes表没有时间戳)

有办法做到这一点吗？

Answer 1

您可以在中使用引用数据连接来连接表。

在流分析中使用参考数据进行查找

引用数据(也称为查找表)是一个有限的数据集，它在本质上是静态的或缓慢变化的，用于执行查找或增强您的数据流。

在您的示例中，在Stream作业中的 Input 部分下，添加一个引用输入。您可以选择Blob存储或SQL数据库作为源。

​

然后，可以定义SQL查询以返回所需的引用数据。对于您的情况，引用数据查询如下所示：

SELECT NodeId, NodeName, NodeDataType
FROM dbo.plcnext-nodes

在SA中定义引用数据之后，转到Stream中的 query 部分并更新查询。对于您的场景，您的查询如下(使用联接)：

SELECT
   pn.NodeId,
   hub.EventValue,
   hub.EventMeasuredUtcTime,
   hub.EventEnqueuedUtcTime,
   hub.EventProcessedUtcTime
   INTO [plcnext_events]
FROM [iot_hub] hub
JOIN [plcnext-nodes] pn ON pn.NodeName = hub.NodeName