Azure Stream Analytics query with multiple inputs

betakiaagia3121

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-datashow

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?

Martin [PLCnext Technology Team]

This question is a copy/paste from this stack overflow question:

https://stackoverflow.com/questions/58803490/azure-stream-analytics-query-with-multiple-inputs

I will assume a charitable explanation in this case, but please be warned that anyone misusing this forum will have their account blocked.