CODEWITHSUNDEEP
azurejoinazure-stream-analytics
Kamlesh Khollam
Kamlesh Khollam

Reputation: 111

Azure Stream Analytics Reference Input Join

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

Upvotes: 2

Views: 744

Answers (2)

Kamlesh Khollam
Kamlesh Khollam

Reputation: 111

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

Thank you, Kamlesh Khollam

Upvotes: 0

Steve Johnson
Steve Johnson

Reputation: 8670

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.

Upvotes: 0

Related Questions