[SOLVED] Is there any way to check if my Stream Analytics input is already in a table?

Is there any way to check if my Stream Analytics input is already in a table?

I have an input from a iothub to a stream analytics(SA). This input contains data detected by a Bluetooth device and I store them on a data base (an output of the SA), in a table called InputMessages. The problem is that I have another table (Devices) with the devices that it must detect. Its primary key is the MAC of the detected devices which is also related with the inputMessages by FK. Sometimes I have an error in the stream analytics input because the detected device is not in the devices table. I'm trying to make a filter on the SA query to avoid this happening discarding the messages from the devices that are not in the Devices table with this code, but its not working.

The query looks like this:

    IF EXISTS(
            SELECT deviceId
            FROM inputSqlDevices D, inputIotHub M
            WHERE D.deviceId= M.deviceId') BEGIN
            
    SELECT
        Message
    INTO
        outputsql
    FROM
        inputiothub
    WHERE
    END

Any suggestion?

Solution

No built-in ASA operator or query elements could implement above pseudo code as i know.

Since your obstacle is the foreign key constraint when writing to the database.So as a workaround,i'd suggest you configuring an Azure Function as output of ASA job.You could select the properties, and pass them into azure function!Then inside azure function,you could do the judge of foreign key constraint issue.Anyway,it's easy to make sure that data will only be written to the database if it meets the conditions with coding.

More details,please refer to this doc:https://learn.microsoft.com/en-us/azure/stream-analytics/stream-analytics-with-azure-functions. That's for Redis,your destination is sql db.No differences i think.