Deleting every point older than the most recent one of each unique ID

user_17353676 0
Viewed 26

What I'm trying to achieve is to only keep the latest of any given point identified by an ID, delete everything else.

The ID is a tag.

So let's say we have:

time                 ID   ...
2022-06-28 18:29:00  id1  ...
2022-06-28 18:28:00  id1  ...
2022-06-28 18:27:00  id1  ...
2022-06-28 18:29:00  id2  ...
2022-06-28 18:28:00  id2  ...
2022-06-28 18:29:00  id3  ...

Would result to:

time                 ID   ...
2022-06-28 18:29:00  id1  ...
2022-06-28 18:29:00  id2  ...
2022-06-28 18:29:00  id3  ...

Is that possible without having to do something like:

DELETE FROM "measurement" WHERE "ID" = '...' AND time < ...

Which take way too much time to execute on all possible "duplicates". You can't also have any OR in a delete where statement. Multiple statements like specified here also take too long to execute.

influxdb-go influxql influxdb time-series
1 Answers

Having ID as a tags has saved you a lot of time already.

Maybe we could think over a little bit about your scenario. Are you trying to keep the LATEST data for every INTERVAL? For example, your original sampling rate is 1 second (that is, your data is fed into the InfluxDB every single second) and you would like downsample it for every 5 seconds. That is, you are only taking the last record over that 5-second interval and dump all rest of data.

If the above assumption is right, you could try:

  1. run continuous queries first to get your downsampled data into another database per this doc:
CREATE CONTINUOUS QUERY "cq_basic" ON "someDatabase"
BEGIN
  SELECT time, id, LAST("fieldA"), LAST("fieldA") INTO "latest_5sec_measurement" FROM "measurement" GROUP BY time(5s)
END

This CQ is like a cron job and will keep transforming the original data into the less granular one.

  1. delete the obsolete data with time range (InfluxDB loves you when you are doing this as time range deletion is the most efficient way) in the original database per this doc:
DELETE WHERE time > '2022-06-27 18:29:00' AND time < '2022-06-28 18:29:00'

And you can set up another cron job to run this query as long as you double check the above CQ has been executed successfully.

user_184889550
Related