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

Steve Zhao Steve Zhao · Accepted Answer · 2020-11-18T09:32:16

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.

Azure Stream Analytics Reference Input Join

1 Answers