Stream Analytics: Referring an input from blob storage in JOIN resulted "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"

Question

Hello,

I'm trying to refer a blob storage input config in our query window to determine the threshold value of the event value. But while joining the 2 tables resulting in below 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.

Input data format is as below:

[
  {
    "ruleid": 3,
    "devicename": "usb-Raspberry_Pi_Pico_E6614864D3563327-if00",
    "parameter": "Temp",
    "unit": "°F",
    "alertName": "high temperature reported over 150°F",
    "thresholdvalue": "150"
  }
]

Kindly, suggest what am I doing wrong here.

Thank you,

Answer 1

Hi @azureutthunga6
Welcome to Microsoft Q&A platform and thanks for posting your query here.

Based on the error you're encountering in your Azure Stream Analytics query is because you're trying to perform a JOIN operation between two data streams without specifying a time window. Stream Analytics requires a time-based condition for joining data streams to ensure that the events being joined are within a certain time window.

To fix this, you need to modify your query to include a DATEDIFF function in the JOIN condition to specify the maximum time difference between events.

Here’s how you can adjust your query:

1. Use the DATEDIFF function to specify the time difference between events in your join.
2. Define the unit of time (e.g., minutes, seconds) that suits your data.

SELECT 
    DeviceData.DeviceId, 
    DeviceConfig.devicename, 
    DeviceData.alertName 
FROM 
    SENSOR_DEV_DATA as DeviceData
JOIN 
    SENSOR_DEVICE_CONFIG as DeviceConfig
ON 
    DeviceData.DeviceId = DeviceConfig.devicename
    AND DATEDIFF(minute, DeviceData.Timestamp, DeviceConfig.Timestamp) BETWEEN 0 AND 10

In this example:

• DATEDIFF(minute, DeviceData.Timestamp, DeviceConfig.Timestamp) calculates the difference in minutes between the two timestamps.
• The BETWEEN 0 AND 10 part specifies that the events should be within a 10-minute window.

Make sure that both DeviceData and DeviceConfig have a Timestamp field that indicates when the event occurred. If the Timestamp field does not exist, you may need to adjust based on how the events are timestamped in your dataset.

Hope this helps. Do let us know if you any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.