Azure Stream Analytics Reference Input Join

Question

0

I am trying to use following query to join stream input (deviceinput) and reference input (refinputpdjson):

Following are my Inputs for Stream Analytics Job:

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

SELECT 
din.EventProcessedUtcTime,
din.deviceid as streamdeviceid,
din.heartrate as streamheartrate,
refin.deviceid as refdeviceid,
refin.patientid as refpatientid 
FROM 
deviceinput din 
TIMESTAMP BY EventProcessedUtcTime 
LEFT OUTER JOIN 
refinputpdjson refin 
ON din.deviceid = refin.deviceid

but its failing with following reasons:

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

score 0 · Answer 1 · answered Nov 18 '20 at 09:32

0

As error shows,

JOIN operation between data streams requires specifying max time distances between matching events.

So you can try something like this:

SELECT 
    din.EventProcessedUtcTime, din.deviceid as streamdeviceid, din.heartrate as streamheartrate, refin.deviceid as refdeviceid, refin.patientid as refpatientid 
FROM deviceinput din TIMESTAMP BY EventProcessedUtcTime 
LEFT OUTER JOIN refinputpdjson refin TIMESTAMP BY EventEndUtcTime
ON din.deviceid = refin.deviceid
AND DATEDIFF(minute,din,refin) BETWEEN 0 AND 15

More detail, you can refer to this documentation.

answered Nov 18 '20 at 09:32

Steve Johnson

8,057
1
6
17

HI @Steve Zhao, 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 in case I am missing any config for Reference Input – Kamlesh Khollam Nov 19 '20 at 12:59
@KamleshKhollam having the same issue. did you solve the problem? – Markus S. Feb 17 '21 at 07:11

score 0 · Accepted Answer · answered Sep 28 '21 at 08:48

0

It was a Azure service bug. Its resolved by MS Team. Its working fine now.

Thank you, Kamlesh Khollam

answered Sep 28 '21 at 08:48

Kamlesh Khollam

111
2
15

Azure Stream Analytics Reference Input Join

2 Answers2