ASA: Encountered error when communicating with EventHub: Exceeded the maximum number of allowed receivers per partition in a consumer group which is 5

Chiu I Keanu Leung 0

https://stackoverflow.com/questions/74305395/exceeded-the-maximum-number-of-allowed-receivers-azure-stream-analytics-job-con
I have read and followed the suggestion from the link above but the ASA still throws me the same error so I made some modifications and it works, at least.

I create multiple inputs at Stream Analytics. each with a different consumer group.

WITH 
pj001J0000 AS    
(
        SELECT CAST(project AS nvarchar(max)) AS Project,CAST(device AS nvarchar(max)) AS Device,CAST(time AS   datetime) AS Time FROM [iothub-pj001-grpA] WHERE Project = 'J0000'
),
pj001J0001 AS      
(   
        SELECT CAST(project AS nvarchar(max)) AS Project,CAST(device AS nvarchar(max)) AS Device,CAST(time AS   datetime) AS Time FROM [iothub-pj001-grpA] WHERE Project = 'J0001'
),
pj001J0002 AS
(        
        SELECT CAST(project AS nvarchar(max)) AS Project,CAST(device AS nvarchar(max)) AS Device,CAST(time AS   datetime) AS Time FROM [iothub-pj001-grpA] WHERE Project = 'J0002'  
),
pj001J0003 AS        
(
        SELECT CAST(project AS nvarchar(max)) AS Project,CAST(device AS nvarchar(max)) AS Device,CAST(time AS   datetime) AS Time FROM [iothub-pj001-grpA] WHERE Project = 'J0003'  
),
pj001J0004 AS        
(
        SELECT CAST(project AS nvarchar(max)) AS Project,CAST(device AS nvarchar(max)) AS Device,CAST(time AS   datetime) AS Time FROM [iothub-pj001-grpA] WHERE Project = 'J0004'
),  
pj001J0005 AS          
(
          SELECT CAST(project AS nvarchar(max)) AS Project,CAST(device AS nvarchar(max)) AS Device,CAST(time AS   datetime) AS Time FROM [iothub-pj001-grpB] WHERE Project = 'J0005'  
)


SELECT * INTO [sql-pj001-J0000] FROM pj001J0000;
SELECT * INTO [sql-pj001-J0001] FROM pj001J0001;
SELECT * INTO [sql-pj001-J0002] FROM pj001J0002;
SELECT * INTO [sql-pj001-J0003] FROM pj001J0003;
SELECT * INTO [sql-pj001-J0004] FROM pj001J0004;
SELECT * INTO [sql-pj001-J0005] FROM pj001J0005;

Could anyone help giving me some advices? Thanks.

AnnuKumari-MSFT 31,896 Reputation points Microsoft Employee

2023-09-15T15:31:02.9933333+00:00

Hi Chiu I Keanu Leung ,

Just checking in to see if the above response helped. Please let us know if you need any further assistance. Thankyou
AnnuKumari-MSFT 31,896 Reputation points Microsoft Employee

2023-09-20T10:10:24.7766667+00:00

Hi Chiu I Keanu Leung ,

Did you get a chance to visit the previous response? Please let me know if you need any further assistance. Thankyou
Chiu I Keanu Leung 0 Reputation points

2023-09-22T03:23:37.24+00:00
Hi AnnuKumari-MSFT

Thanks for your kind help on this.

As you can see earlier in my post, the data would be route into different output

SELECT * INTO [sql-pj001-J0000] FROM pj001J0000; SELECT * INTO [sql-pj001-J0001] FROM pj001J0001; SELECT * INTO [sql-pj001-J0002] FROM pj001J0002; SELECT * INTO [sql-pj001-J0003] FROM pj001J0003; SELECT * INTO [sql-pj001-J0004] FROM pj001J0004; SELECT * INTO [sql-pj001-J0005] FROM pj001J0005;

From your suggested example, only one output is addressed. Appreciated if you could advise me how to set more than 1 output.

*I got 1 IoTHub with 50 devices and 50 tables in SQL. I would like data of each device to be routed into each corresponding output table in SQL.
Chiu I Keanu Leung 0 Reputation points

2023-09-25T01:19:39.1166667+00:00
Hi AnnuKumari-MSFT

Thanks for your kind help on this.

As you can see earlier in my post, the data would be route into different output

SELECT * INTO [sql-pj001-J0000] FROM pj001J0000; SELECT * INTO [sql-pj001-J0001] FROM pj001J0001; SELECT * INTO [sql-pj001-J0002] FROM pj001J0002; SELECT * INTO [sql-pj001-J0003] FROM pj001J0003; SELECT * INTO [sql-pj001-J0004] FROM pj001J0004; SELECT * INTO [sql-pj001-J0005] FROM pj001J0005;

From your suggested example, only one output is addressed. Appreciated if you could advise me how to set more than 1 output.

*I got 1 IoTHub with 50 devices and 50 tables in SQL. I would like data of each device to be routed into each corresponding output table in SQL.
AnnuKumari-MSFT 31,896 Reputation points Microsoft Employee

2023-10-26T10:14:08.12+00:00

Hi Chiu I Keanu Leung ,

Just following up to see if the below answer helped. Please do consider clicking Accept Answer as accepted answers help community as well. Also, please click on Yes for the survey 'Was the answer helpful'

1 answer

AnnuKumari-MSFT 31,896 Reputation points Microsoft Employee

2023-09-12T17:36:01.67+00:00
Hi Chiu I Keanu Leung ,

Welcome to Microsoft Q&A platform and thanks for posting your query here.

It seems like you've created multiple inputs in Azure Stream Analytics (ASA) for different consumer groups, and you're using a SQL query to filter and route data from an IoT hub into different outputs and looking for suggestion on improvement.

You can optimize the query for readability . Instead of creating multiple CTEs (Common Table Expressions) for each 'Project,' you can simplify your query by using a single query with conditional filtering. Here's an example:

SELECT CAST(project AS nvarchar(max)) AS Project, CAST(device AS nvarchar(max)) AS Device, CAST(time AS datetime) AS Time INTO [sql-pj001-J0000] FROM [iothub-pj001-grpA] WHERE Project IN ('J0000', 'J0001', 'J0002', 'J0003', 'J0004'); SELECT CAST(project AS nvarchar(max)) AS Project, CAST(device AS nvarchar(max)) AS Device, CAST(time AS datetime) AS Time INTO [sql-pj001-J0005] FROM [iothub-pj001-grpB] WHERE Project = 'J0005';

Hope it helps. Thankyou
Please sign in to rate this answer.
Chiu I Keanu Leung 0 Reputation points

2023-10-13T04:27:29.1133333+00:00

Hi AnnuKumari-MSFT

Thanks for your kind help on this.

As you can see earlier in my post, the data would be route into different output

SELECT * INTO [sql-pj001-J0000] FROM pj001J0000; SELECT * INTO [sql-pj001-J0001] FROM pj001J0001; SELECT * INTO [sql-pj001-J0002] FROM pj001J0002; SELECT * INTO [sql-pj001-J0003] FROM pj001J0003; SELECT * INTO [sql-pj001-J0004] FROM pj001J0004; SELECT * INTO [sql-pj001-J0005] FROM pj001J0005;

From your suggested example, only one output is addressed. Appreciated if you could advise me how to set more than 1 output.

*I got 1 IoTHub with 50 devices and 50 tables in SQL. I would like data of each device to be routed into each corresponding output table in SQL.

AnnuKumari-MSFT 31,896 Reputation points Microsoft Employee

2023-10-17T10:51:37.5066667+00:00

Hi Chiu I Keanu Leung ,

Apologies for delay in response.

I see that you want to route data from IoT Hub to multiple SQL tables based on the device or project information. To achieve this in Azure Stream Analytics, you can use the PARTITION BY clause in your query. Here's an example of how you can route data from a single input to multiple outputs based on the "Project" field:

SELECT * INTO [sql-pj001-J0000] FROM [iothub-pj001-grpA] PARTITION BY Project HAVING Project = 'J0000'; SELECT * INTO [sql-pj001-J0001] FROM [iothub-pj001-grpA] PARTITION BY Project HAVING Project = 'J0001'; -- Repeat the above SELECT INTO block for other projects (J0002, J0003, J0004,

Kindly check the following documentation for more details: Leverage query parallelization in Azure Stream Analytics

Use repartitioning to optimize processing with Azure Stream Analytics

Hope it helps. Kindly accept the answer by clicking on accept answer button. Thankyou

Chiu I Keanu Leung 0 Reputation points

2023-10-18T02:43:46.58+00:00

Hi AnnuKumari-MSFT

Thanks for your kind help on this.

Just one more question on the consumer group of IoTHub, so I still need to create more than one consumer group to handle this situation? As you know the topic of this post is to tackle the error

"Encountered error when communicating with EventHub: Exceeded the maximum number of allowed receivers per partition in a consumer group which is 5"

From the code below, I gotta prepare [iothub-pj001-grpB] for the [sql-pj001-J0005] onward

SELECT CAST(project AS nvarchar(max)) AS Project,CAST(device AS nvarchar(max)) AS Device,CAST(time AS datetime) AS Time INTO [sql-pj001-J0000] FROM [iothub-pj001-grpA] PARTITION BY Project HAVING Project = 'J0000'; SELECT CAST(project AS nvarchar(max)) AS Project,CAST(device AS nvarchar(max)) AS Device,CAST(time AS datetime) AS Time INTO [sql-pj001-J0001] FROM [iothub-pj001-grpA] PARTITION BY Project HAVING Project = 'J0001'; SELECT CAST(project AS nvarchar(max)) AS Project,CAST(device AS nvarchar(max)) AS Device,CAST(time AS datetime) AS Time INTO [sql-pj001-J0002] FROM [iothub-pj001-grpA] PARTITION BY Project HAVING Project = 'J0002'; SELECT CAST(project AS nvarchar(max)) AS Project,CAST(device AS nvarchar(max)) AS Device,CAST(time AS datetime) AS Time INTO [sql-pj001-J0003] FROM [iothub-pj001-grpA] PARTITION BY Project HAVING Project = 'J0003'; SELECT CAST(project AS nvarchar(max)) AS Project,CAST(device AS nvarchar(max)) AS Device,CAST(time AS datetime) AS Time INTO [sql-pj001-J0004] FROM [iothub-pj001-grpA] PARTITION BY Project HAVING Project = 'J0004'; SELECT CAST(project AS nvarchar(max)) AS Project,CAST(device AS nvarchar(max)) AS Device,CAST(time AS datetime) AS Time INTO [sql-pj001-J0005] FROM [iothub-pj001-grpB] PARTITION BY Project HAVING Project = 'J0005';

Is there any better way to make it more concise by using Function of ASA together with Query?

AnnuKumari-MSFT 31,896 Reputation points Microsoft Employee

2023-10-25T05:49:47.1233333+00:00

Hi Chiu I Keanu Leung ,

You can optimize the Azure Stream Analytics job to be more concise and efficient by using user-defined functions (UDFs) and making your queries more dynamic.

Kindly get more details on UDFs in AAS here: User-defined functions in Azure Stream Analytics .

Hope it helps. If you find it helpful, kindly accept the answer by clicking on Accept answer button. If you have any additional query, I would suggest you to create a new post. Thankyou

AnnuKumari-MSFT 31,896 Reputation points Microsoft Employee

2023-10-31T05:15:32.98+00:00

Hi Chiu I Keanu Leung ,

We haven't heard anything from your side on the updates regarding issue resolution. Kindly accept the above answer in case it was helpful. Thankyou.

Chiu I Keanu Leung 0 Reputation points

2023-10-31T07:59:07.9633333+00:00

I am sorry I would kindly accept the answer if more help with example related my case could be offered but not just simply throwing the link and tell me to digest it. What you mentioned on UDF I heard of that before.

May I have the optimized way/suggestion on tackling the captioned error with more examples?
Could I not create lots of consumer group of IoTHub to handle different data going to different output under the condition or It's correct to do prepare the consumer groups and what I can do more is to make the query to be more concise?
Sign in to comment

Share via

ASA: Encountered error when communicating with EventHub: Exceeded the maximum number of allowed receivers per partition in a consumer group which is 5

1 answer