I have created a materialized view (MV) and a corresponding NATS sink to publish average temperature data every 3 minutes. However, I'm receiving updates on the temperature.avg.3min
topic every 3 to 5 seconds instead of every 3 minutes. Here's the SQL for the MV and sink:
CREATE MATERIALIZED VIEW IF NOT EXISTS avg_temp_3min AS
SELECT temp_demo.window_end AS window_end,
temp_demo.device_id AS device_id,
AVG(temp_demo.temperature) AS avg_tmp
FROM (
SELECT device_id, window_end, AVG(temperature) AS temperature
FROM TUMBLE(
temperature_src,
ts,
INTERVAL '3' MINUTE
) GROUP BY device_id, window_end ORDER BY window_end
) AS temp_demo
GROUP BY temp_demo.device_id, temp_demo.window_end;
CREATE SINK IF NOT EXISTS temp_avg_sink_3min FROM avg_temp_3min
WITH (
connector='nats',
server_url='nats-server:4222',
subject='temperature.avg.3min',
type = 'append-only',
force_append_only = 'true',
connect_mode='plain'
);
I expected to receive updates only when the 3-minute window ends, but it seems to be sending updates more frequently. How can I adjust the setup to only send updates every 3 minutes as intended?
AHMED BOUTARAA
Asked on Jan 17, 2024
It appears that the issue is not with the NATS sink configuration but rather with how the materialized view (MV) is being updated. When a new row is inserted into the source table, it can cause the window_end
value in the MV to change, which in turn triggers an update to the NATS sink. To ensure that updates are sent every 3 minutes as intended, you may need to adjust the logic of the MV to only consider data within the specific 3-minute window and not update window_end
more frequently. Alternatively, you could explore NATS configurations or additional logic to filter out updates that are sent too frequently.