FULL JOIN of a STREAM and a LOOK UP TABLE

[johker]

Hi all,

I want to detect when certain events in Edgex are missing, for example when a device gets disconnected unexpectedly. Each device sends an event every 10 secs.

My stream is

{"sql":"CREATE STREAM EdgexStream () WITH (format = \"JSON\", type = \"edgex\", shared=\"true\")"}

I have a rule that writes the timestamp of each device to a redis based Lookup Table

{"sql": "SELECT meta(deviceName) AS deviceName, meta(origin) AS timestamp FROM EdgexStream"}

That works fine. My redis has entries like:

"{\"deviceName\":\"Random-Integer-Device\",\"timestamp\":1707162351302794250}"

Now I want another rule that is triggered every 10 secs and returns all entries of the Lookup table with a timestamp older than 10 secs.

I have tried a full join of the stream and my Table:

{"sql": "SELECT AlarmLUT.deviceName, AlarmLUT.timestamp, EdgexStream.deviceName FROM EdgexStream FULL JOIN AlarmLUT ON AlarmLUT.deviceName = EdgexStream.deviceName"}

It only returns deviceNames that are in the stream, but I am interested in the deviceNames that are NOT in the stream.

Is there a way to do this with ekuiper?

Thanks in advance

[Yisaer]

Hi @johker,

Could you provide the example input frin the stream and lookup table, and the expceted output result for the sql you want?

[johker]

The lookup table has entries like this:

"{\"deviceName\":\"Random-Integer-Device\",\"timestamp\":1707162351302794250}"
"{\"deviceName\":\"Random-Boolean-Device\",\"timestamp\":1707162351302794250}"
"{\"deviceName\":\"Random-Float-Device\",\"timestamp\":1707162351302794250}"

I want a rule that returns all table entries in an array every 10 sec:

[{deviceName: Random-Integer-Device, timestamp: 1707162351302794250}, { ... ]

[Yisaer]

It only returns deviceNames that are in the stream, but I am interested in the deviceNames that are NOT in the stream.

I think you need to use RIGHT JOIN instead of FULL JOIN

[johker]

Unfortunately that does not work for me. It is just empty.

Is there such a thing as "Redis Pull" source that reads out a table ?

[Yisaer]

For now this is not supported.