azureazure-iot-hubazure-stream-analytics

Pass streams with array and combine records based on specific id

I have a stream from IoT Hub like:

{"timestamp":1669022177222,
    "values":[
        {"id":"Channel1.Device1.Tag1","v":62799,"q":true,"t":1669022176675},
        {"id":"Channel2.Device1.Tag1","v":244,"q":true,"t":1668762863650},
        {"id":"Channel2.Device1.Tag2","v":38,"q":true,"t":1669011646964},
        {"id":"Channel1.Functions.Ramp1","v":75,"q":true,"t":1669022176254}
    ]
}

I need to store above json in blob storage with below details within folder and subfolder based on id,

Channel1 (Main folder) -> Device1 (Subfolder) -> contains below json string

{"timestamp":1669022177222,
    "values":[
        {"id":"Channel1.Device1.Tag1","v":62799,"q":true,"t":1669022176675}
        
    ]
}

Channel1 (Main folder) -> Functions (Subfolder) -> contains below json string

{"timestamp":1669022177222,
    "values":[
        {"id":"Channel1.Functions.Ramp1","v":75,"q":true,"t":1669022176254}
    ]
}

Similarly, Channel2 (Main folder) -> Device1 (Subfolder) -> contains below json string

{"timestamp":1669022177222,
    "values":[
        {"id":"Channel2.Device1.Tag1","v":244,"q":true,"t":1668762863650},
        {"id":"Channel2.Device1.Tag2","v":38,"q":true,"t":1669011646964}
    ]
}

Is there any way to achieve this result? I tried to flatten json using "GetArrayElement" but with that unable to get desired result (as explained above).

Solution

  • There is no dynamic way of creating the folder directories on the fly through Azure IoT Hub Message routing. The file name format lets you define custom path, but it has some constraints. It does not allow you to add dynamic bindings besides the standard {iothub}, {partition}, {YYYY}, {MM}, {DD}, {HH} and {mm} parameters.

    However, you can create a path something similar to below
    enter image description here

    The above path would create sub folders Device1 and Channel inside the storage container of your choosing. Please refer the below image enter image description here

    You can then create a custom route with a routing query something similar to this

    $body.deviceId = "TestDevice1" AND $body.source ="Channel"

    enter image description here

    The query works in my case as I have the following telemetry data being sent to the IoT Hub in the following format.

    {"messageId":115,"deviceId":"TestDevice2","source":"Channel","temperature":27.407816047882974,"humidity":76.31359314205883}

    Since you have an array to iterate through, please look into the Query Expressions section to formulate the routing query.

    I would recommend breaking down the id in the telemetry data and pass device and source separately so that the data is easier to parse and route. Here is the set of the logs generated based on the routing (Notice the full folder directory the path creates) enter image description here

    Please ensure that the encoding is set to UTF-8 and Content type is set to application/json as you send the data from the device in order for the routing to work. Please refer the below snippet.

     var telemetryDataPoint1 = new
 {
   messageId = _messageId1++,
   deviceId = deviceId1,
   source = channel[Rand.Next(0,2)],
   temperature = currentTemperature,
   humidity = currentHumidity
  };
  string messageString = JsonConvert.SerializeObject(telemetryDataPoint1);
  Message message = new Message(Encoding.ASCII.GetBytes(messageString));
  message.ContentEncoding = "utf-8"; 
  message.ContentType = "application/json";
  message.Properties.Add("temperatureAlert", (currentTemperature > 30) ? "true" : "false");
  await deviceClient1.SendEventAsync(message);

    You would then have to create a different a different custom end point and route to match device2 and channel/function.