Azure 流分析引用输入联接

Question

0

我尝试使用以下查询来加入流输入(deviceinput)和引用输入(refinputpdjson):

以下是我对流分析作业的输入:

输入 1:来自 IoT 中心的流输入输入 2:来自 Azure Blob 存储的引用数据

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

但由于以下原因而失败:

连接谓词没有时间限制。数据流之间的 JOIN 操作需要指定匹配事件之间的最大时间距离。请将 DATEDIFF 添加到 JOIN 条件。示例: SELECT input1.a, input2.b FROM input1 JOIN input2 ON DATEDIFF(分钟, input1, input2) BETWEEN 0 AND 10

Answer 1

如错误所示，

JOIN operation between data streams requires specifying max timedistances between matching events.

所以你可以尝试这样的事情:

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

更详细的，可以引用这个documentation .