Azure Stream Analytics Job Reference Input Join Error

Kamlesh Khollam 21

Hi Team

My Inputs for Stream Analytics Job:

Input 1: Stream Input from IoT Hub
Input 2: Reference Data from Azure Blob Storage

I am trying to use following query to join

stream input (deviceinput)
and reference input (refinputpdjson).

SELECT   
	din.deviceid as streamdeviceid,  
	din.heartrate as streamheartrate,  
	refin.deviceid as refdeviceid,  
	refin.patientid as refpatientid,  
	din.srcdatetime,  
	din.EventProcessedUtcTime  
FROM   
	[asa-in-iot-healthcare-rpm] din   
	TIMESTAMP BY srcdatetime  
JOIN  
    [asa-ref-in-patient-device-mapping] refin  
    ON refin.deviceid = din.deviceid

but its failing with following error:

The join predicate is not time bounded. JOIN operation between data streams requires specifying max time distances between matching events. Please add DATEDIFF to the JOIN condition. Example: SELECT input1.a, input2.b FROM input1 JOIN input2 ON DATEDIFF(minute, input1, input2) BETWEEN 0 AND 10

As per instruction on azure docs (https://learn.microsoft.com/en-us/stream-analytics-query/reference-data-join-azure-stream-analytics) , I did follow the mentioned steps, but above error is coming.

I am not sure about this forum to post such issue or not. But, it will be great if anyone can help/direct us on this issue.

Kamlesh Khollam 21 Reputation points

2020-11-19T13:47:30.75+00:00

Query join has one stream input and one reference input (and not the two stream inputs).

I used the "DATEDIFF" function.

But then query editor showing warning message : "Temporal Operators are not allowed with reference data".

So its not allowing me to execute the query.

After this I selected the query and "Test Query" button became "Test Selected Query" so executed the query. It shows 0 rows.

IoT input and Reference Input does have data for same deviceid.

Please suggest if I am missing any configuration for Reference Input.
HimanshuSinha-msft 19,381 Reputation points Microsoft Employee

2020-11-20T02:12:57.583+00:00

Hello @Kamlesh Khollam ,

Thanks for the ask .

I am going to reach out internally get help on this . I will update you on this .

Thanks
Himanshu
Kamlesh Khollam 21 Reputation points

2020-11-23T10:03:27.727+00:00

Hello @HimanshuSinha-msft ,

Thank you for your reply.

Hoping for positive response.

Best Regards,
@Kamlesh Khollam
HimanshuSinha-msft 19,381 Reputation points Microsoft Employee

2020-11-24T05:06:57.45+00:00

Hello @Kamlesh Khollam ,

My apoloziges as I am not getting the kind of tracktion from the internal team . If you have a support plan you may file a support ticket, else could you please send an email to AzCommunity@microsoft.com with the below details, so that we can create a one-time-free support ticket for you to work closely on this matter.

Thread URL:https://learn.microsoft.com/en-us/answers/questions/167090/azure-stream-analytics-job-reference-input-join-er.html
Subscription ID:
Subject:Attention HImanshu
Please let me know once you have done the same

Thanks
Himanshu
HimanshuSinha-msft 19,381 Reputation points Microsoft Employee

2020-12-07T20:55:12.237+00:00

Hello @Kamlesh Khollam ,

While trying the thing again I found that we cannot look into the blob reference data directly it gives the below error "" Blob storage reference inputs cannot be previewed. Upload a sample file to test the query."".

While testing the query we need to use the option for upload the sample data . I am trying to add the show the same in below . In my case the data stream has some dummy data on an eventhub and I have blob refernece data whcih I will upload and run the query .
The below animation should help .

Let me know how it goes .

Thanks
Himanshu
HimanshuSinha-msft 19,381 Reputation points Microsoft Employee

2020-12-09T01:34:18.88+00:00

Hello ,
We have not heard back from you on this and was just following up if you can eloborate more on the ask as requested before .
Incase if you have resolution , request you to share the same here , so that other community members can benefit from that .
Thanks
Himanshu

1 answer

Kamlesh Khollam 21 Reputation points

2020-12-10T06:20:46.003+00:00

Hi @HimanshuSinha-msft

Now, I am able to run the Job with Reference Input join.

It seems there was an issue with Product itself and your Microsoft team has resolved the issue on Reference Input join.

At my end, I did not do any changes. I just tried to check the Stream Analytics job run and it worked fine.

Thank you,
@Kamlesh Khollam
Please sign in to rate this answer.

0 comments No comments
Sign in to comment