Issue
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 also check this refrence Data JOIN in Stream Analytics to join tables: https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-use-reference-data[url="https://www.blackshowhair.com/"]show[/url]
Desired solution
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
Query
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)
Is there a way to achieve this?