What is the recommended way for Azure Stream Analytics to handle/route IoT messages of different sizes and content from the SAME device

Question

From one of our devices, at first, we want to send (and log) calibration messages. Then, after it is fully calibrated, we want to send regular IoT telemetry messages.

The work flow is: data is going from IoT Hub to Azure Stream Analytics and then to Azure Sql Database.

The contents of the IoT message can change depending on whether the device is being calibrated or not. To identify device calibration, we change that device's name field to contain "calibration". Then for sending regular telemetry data, the device's name will revert back to containing its name.

Hence, in ASA, to determine if a message is a calibration message: we check the contents of the device-name field.

So the problem needs to be solved with something like this:

if message-contents.device-name = 'calibration' then
 get these 10 fields from the message
 write them to Calibration DB
else
 get these different 5 fields from the message
 call a ML function with some of these input fields
 write result to Telemetry DB
end

I have tried writing Azure Stream Analytics code that uses the CASE statement.

WITH ALLMESSAGES AS ( 
SELECT *
FROM iothubinput2018aug21 ),

QUERY1 AS (
SELECT try_cast(deviceid as nvarchar(max)) AS DEVICE_NAME, 
       try_cast(device as nvarchar(max)) as DEVICE_MAC_ID,
       try_cast(devicenumber as nvarchar(max)) as DEVICE_UID,
       try_cast(emptymax as nvarchar(max)) as EMPTYMAX,
       try_cast(emptymin as nvarchar(max)) as EMPTYMIN,
       try_cast(emptysum as nvarchar(max)) as EMPTYSUM,
       try_cast(fullmax as nvarchar(max)) as FULLMAX,
       try_cast(fullmin as nvarchar(max)) as FULLMIN,
       try_cast(fullsum as nvarchar(max)) as FULLSUM,
       try_cast(emptyresult as nvarchar(max)) as EMPTYRESULT,
       try_cast(fullresult as nvarchar(max)) as FULLRESULT,
       try_cast(fw as nvarchar(max)) as FIRMWARE_VERSION,
       TRY_CAST(SYSTEM.TIMESTAMP AS DATETIME) AS ASA_POSTTIME,
       0 AS REC_READ
FROM ALLMESSAGES
WHERE DEVICE_NAME = 'calibration'),

QUERY2 AS (
SELECT
       try_cast(deviceid as nvarchar(max)) AS DEVICE_NAME, 
       try_cast(device as nvarchar(max)) as DEVICE_MAC_ID,
       try_cast(uid as nvarchar(max)) as DEVICE_UID,
       try_cast(weight as nvarchar(max)) as DEVICE_SENSOR_READINGS,
       TRY_CAST(SYSTEM.TIMESTAMP AS DATETIME) AS ASA_POSTTIME,
       MLFunctionBatch2019Apr10(DEVICE_MAC_ID, DEVICE_SENSOR_READINGS) AS RESULT,
       UDF.SUMOFREADINGS(DEVICE_SENSOR_READINGS) AS DEVICE_SENSOR_READINGS_SUM,
       0 AS REC_READ
FROM ALLMESSAGES
WHERE DEVICE_NAME != 'calibration')

SELECT DEVICE_NAME, 
       DEVICE_MAC_ID,
       DEVICE_UID,
       DEVICE_SENSOR_READINGS,
       ASA_POSTTIME, 
       CASE result."Scored Labels" WHEN '1' THEN 'FULL' ELSE 'EMPTY' END AS MLSVC_RESULT,
       REC_READ,
       DEVICE_SENSOR_READINGS_SUM
INTO OUT2SQLDBDEV                
FROM QUERY2
WHERE DEVICE_MAC_ID != 'calibration'

SELECT DEVICE_NAME, 
       DEVICE_MAC_ID,
       DEVICE_UID,
       EMPTYMAX,
       EMPTYMIN,
       EMPTYSUM,
       FULLMAX,
       FULLMIN,
       FULLSUM,
       EMPTYRESULT,
       FULLRESULT,
       FIRMWARE_VERSION,
       ASA_POSTTIME,
       REC_READ
INTO OUT2SQLDBCALIBRATIONDEV
FROM QUERY1
WHERE DEVICE_MAC_ID = 'calibration'

I expect the output to go to the right DB table depending on whether the device-name is calibration or something else. This is not happening. No output is coming out. And on another attempt, the ASA query fails because the database field expects NOT NULL values but the query sends a NULL value.

score 0 · Answer 1 · answered Jun 11 '19 at 14:59

I had to do some coalescing on the values to make sure that they are NOT NULL, then the queries worked. Please see below.

WITH ALLMESSAGES AS ( 
SELECT *
FROM iothubinput2018aug21 ),

QUERY1 AS (
SELECT
       try_cast(deviceid as nvarchar(max)) AS DEVICE_NAME, 
       try_cast(device as nvarchar(max)) as DEVICE_MAC_ID,
       try_cast(uid as nvarchar(max)) as DEVICE_UID,
       try_cast(weight as nvarchar(max)) as DEVICE_SENSOR_READINGS,
       TRY_CAST(SYSTEM.TIMESTAMP AS DATETIME) AS ASA_POSTTIME,
       '1' AS RESULT,
        UDF.SUMOFREADINGS(try_cast(weight as nvarchar(max))) AS DEVICE_SENSOR_READINGS_SUM,
       0 AS REC_READ
FROM ALLMESSAGES
WHERE COALESCE(deviceid,'NULL') != 'calibration'), 

QUERY2 AS (
SELECT try_cast(deviceid as nvarchar(max)) AS DEVICE_NAME, 
       try_cast(device as nvarchar(max)) as DEVICE_MAC_ID,
       try_cast(devicenumber as nvarchar(max)) as DEVICE_UID,
       try_cast(emptymax as nvarchar(max)) as EMPTYMAX,
       try_cast(emptymin as nvarchar(max)) as EMPTYMIN,
       try_cast(emptysum as nvarchar(max)) as EMPTYSUM,
       try_cast(fullmax as nvarchar(max)) as FULLMAX,
       try_cast(fullmin as nvarchar(max)) as FULLMIN,
       try_cast(fullsum as nvarchar(max)) as FULLSUM,
       try_cast(emptyresult as nvarchar(max)) as EMPTYRESULT,
       try_cast(fullresult as nvarchar(max)) as FULLRESULT,
       try_cast(fw as nvarchar(max)) as FIRMWARE_VERSION,
       TRY_CAST(SYSTEM.TIMESTAMP AS DATETIME) AS ASA_POSTTIME,
       0 AS REC_READ
FROM ALLMESSAGES
WHERE COALESCE(deviceid,'NULL') = 'calibration')

SELECT DEVICE_NAME, 
       DEVICE_MAC_ID,
       DEVICE_UID,
       DEVICE_SENSOR_READINGS,
       ASA_POSTTIME, 
       'FULL' AS MLSVC_RESULT,
       REC_READ,
       DEVICE_SENSOR_READINGS_SUM
INTO OUT2SQLDBDEV                
FROM QUERY1
WHERE COALESCE(DEVICE_NAME, 'NULL') != 'calibration'

SELECT DEVICE_NAME, 
       DEVICE_MAC_ID,
       DEVICE_UID,
       DEVICE_SENSOR_READINGS,
       ASA_POSTTIME, 
       'FULL' AS MLSVC_RESULT,
       REC_READ,
       DEVICE_SENSOR_READINGS_SUM
INTO OUT2BLOB2018OCT24              
FROM QUERY1
WHERE COALESCE(DEVICE_NAME, 'NULL') != 'calibration'

SELECT DEVICE_NAME, 
       DEVICE_MAC_ID,
       DEVICE_UID,
       EMPTYMAX,
       EMPTYMIN,
       EMPTYSUM,
       FULLMAX,
       FULLMIN,
       FULLSUM,
       EMPTYRESULT,
       FULLRESULT,
       FIRMWARE_VERSION,
       ASA_POSTTIME,
       REC_READ
INTO OUT2SQLDBCALIBRATIONDEV
FROM QUERY2
WHERE COALESCE(DEVICE_NAME, 'NULL') = 'calibration'

What is the recommended way for Azure Stream Analytics to handle/route IoT messages of different sizes and content from the SAME device

1 Answers1