I have a Azure IoT-Hub sending messages to a Azure Stream Analytics Job.
Each message contains a 'NodeName'. I have a table 'plcnext_nodes' which has a unique 'NodeId' for each node with their corresponding 'NodeName'.
How can I use both the input from the 'plcnext_nodes' table and the IoT-Hub messages to store the event data in another SQL table using the 'NodeId'?
I want to use the 'NodeId' instead of the 'NodeName' because some names can get very long and saving them over and over with each message is a waste of storage.
I would like to parse the following message from the IoT Hub:
{
"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": {
...
}
}
Compare the 'NodeName' with those in the plcnext_nodes table to get the appropriate 'NodeId':
NodeId NodeName NodeDataType
---------------------------------------------
1 ns=5;s=Arp.Plc.Eclr/DI1 Boolean
2 ns=5;s=Arp.Plc.Eclr/DI2 Boolean
...
To get the following output and insert into the plcnext_events table:
NodeId EventValue EventMeasured
-----------------------------------------------
1 0 2019-11-11 12:15:22.4830000
I have tried the following query on Azure Stream Analytics:
SELECT
NodeId,
EventValue,
EventMeasuredUtcTime,
EventEnqueuedUtcTime,
EventProcessedUtcTime
INTO
[plcnext_events]
FROM
[plcnext_nodes],
[iot_hub]
WHERE
[iot_hub].NodeName = [plcnext-nodes].NodeName
But JOIN is not supported in FROM and I haven't been able to use a JOIN clause because of it's DATEDIFF restriction (The plcnext_nodes table has no timestamps)
You can use a Reference Data JOIN in Stream Analytics to join tables.
Using Reference Data for Lookups in Stream Analytics
Reference data (also known as a lookup table) is a finite data set that is static or slowly changing in nature, used to perform a lookup or to augment your data streams.
In your case, go under the Inputs section in your Stream Analytics job and add a Reference Input. You can select either Blob Storage or SQL Database as your source.
You can then define your SQL query to return the reference data you need. For your case, your reference data query would look like this:
SELECT NodeId, NodeName, NodeDataType
FROM dbo.plcnext-nodes
Once you define your reference data join in SA, go to the Query section in Stream Analytics and update your query. For your scenario your query would look like this (with the JOIN):
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