Azure Stream Analytics filtering multiple input colums to output to SQL tables, getting stuck at limit of 5 receivers

Question

I am trying to sort data from IoT Hub with Azure Stream Analytics, from the IoT hub the incoming data is in multiple columns and my goal is them to be sorted into SQL tables.

So far I've got to selecting data from five columns, more than that it throws and warning (maximum event hub receivers exceeded) on my stream input and no data get processed, here's the query code:

WITH SD_input AS 
(
    SELECT *
    FROM [SDEwoninput]
),
cycleTimes AS 
(
    SELECT
        "1_Cycle_time_10" AS cycle_time,
        time AS cycle_timestamp,
        1 AS cycle_name_id -- 10 Zone Cycle
    FROM
        SD_input
    WHERE
        LAG("1_Cycle_time_10", 1) OVER (LIMIT DURATION(minute, 1)) <> "1_Cycle_time_10"
    UNION
    SELECT
        "1_Cycle_time_20" AS cycle_time,
        time AS cycle_timestamp,
        2 AS cycle_name_id -- 20 Zone Cycle
    FROM
        SD_input
    WHERE
        LAG("1_Cycle_time_20", 1) OVER (LIMIT DURATION(minute, 1)) <> "1_Cycle_time_20"
    UNION
    SELECT
        "1_Cycle_time_30" AS cycle_time,
        time AS cycle_timestamp,
        3 AS cycle_name_id -- 30 Zone Cycle
    FROM
        SD_input
    WHERE
        LAG("1_Cycle_time_30", 1) OVER (LIMIT DURATION(minute, 1)) <> "1_Cycle_time_30"
    UNION
    SELECT
        "1_Cycle_time_40" AS cycle_time,
        time AS cycle_timestamp,
        4 AS cycle_name_id -- 40 Zone Cycle
    FROM
        SD_input
    WHERE
        LAG("1_Cycle_time_40", 1) OVER (LIMIT DURATION(minute, 1)) <> "1_Cycle_time_40"
    UNION
    SELECT
        "1_Cycle_time_50" AS cycle_time,
        time AS cycle_timestamp,
        5 AS cycle_name_id -- 50 Zone Cycle
    FROM
        SD_input
    WHERE
        LAG("1_Cycle_time_50", 1) OVER (LIMIT DURATION(minute, 1)) <> "1_Cycle_time_50"
    UNION
    SELECT
        "1_Cycle_time_60" AS cycle_time,
        time AS cycle_timestamp,
        6 AS cycle_name_id -- 60 Zone cycle
    FROM
        SD_input
    WHERE
        LAG("1_Cycle_time_60", 1) OVER (LIMIT DURATION(minute, 1)) <> "1_Cycle_time_60"
        UNION
    SELECT
        "1_Cycle_time_70" AS cycle_time,
        time AS cycle_timestamp,
        7 AS cycle_name_id -- 70 Zone Cycle
    FROM
        SD_input
    WHERE
        LAG("1_Cycle_time_70", 1) OVER (LIMIT DURATION(minute, 1)) <> "1_Cycle_time_70"
)
SELECT *
INTO [sd-cycle-times]
FROM cycleTimes

Here is the input for reference:

 {
    "1_Cycle_time_10": "10.95",
    "1_Cycle_time_20": "10.67",
    "1_Cycle_time_30": "11.57",
    "1_Cycle_time_40": "12.02",
    "1_Cycle_time_50": "7.98",
    "1_Cycle_time_60": "7.83",
    "1_Cycle_time_70": "8.46",
    "1_Tot_lid_count": "600680",
    "1_vak_pump_h": "1278",
    "1_Run_time_H": "0",
    "1_Run_time_M": "2",
    "1_Run_time_S": "35",
    "1_P_Run_time_S": "12",
    "1_P_Run_time_M": "48",
    "1_P_Run_time_H": "0",
    "1_Fault40_1": "333",
    "1_Fault40_2": "167",
    "1_Fault40_3": "65",
    "1_Fault40_4": "16",
    "1_FaultSum40": "581",
    "1_Fault50_1": "140",
    "1_Fault50_2": "0",
    "1_Fault50_3": "5",
    "1_Fault50_4": "3",
    "1_FaultSum50": "148",
    "1_Fault60_1": "8",
    "1_Fault60_2": "1",
    "1_Fault60_3": "3",
    "1_Fault60_4": "0",
    "1_FaultSum60": "12",
    "1_Fault70_1": "4767",
    "1_Fault70_2": "4417",
    "1_Fault70_3": "4132",
    "1_Fault70_4": "5548",
    "1_FaultSum70": "18864",
    "1_P_kastes_time_S": "29",
    "1_P_kastes_time_M": "6",
    "1_P_kastes_time_H": "0",
    "1_AUTO": "1",
    "time": "2021-02-27 09:59:42",
    "EventProcessedUtcTime": "2021-02-27T08:57:58.0443121Z",
    "PartitionId": 1,
    "EventEnqueuedUtcTime": "2021-02-27T07:59:37.8820000Z",
    "IoTHub": {
      "MessageId": null,
      "CorrelationId": null,
      "ConnectionDeviceId": "SD_EWON",
      "ConnectionDeviceGenerationId": "637455362146855668",
      "EnqueuedTime": "2021-02-27T07:59:37.8710000Z",
      "StreamId": null
    }

What could be the solution to this, UDF functions?

Ajeta Ajeta · Accepted Answer · 2021-03-23T06:04:00

A best practice for using Event Hubs is to use multiple consumer groups for job scalability. To resolve this error, create a new Consumer Group on IoT Hub. Then edit the input in your Stream Analytics job to use the new Consumer Group.

See these documentations:

Azure Stream Analytics filtering multiple input colums to output to SQL tables, getting stuck at limit of 5 receivers

1 Answers