Removing points with unnecessary field values ​​from an InfluxDB dimension

Question

InfluxDB allows you to delete points based on WHERE tag='value' conditions, but not by field value.

For example, if you accidentally save a measurement with a value of -1 in a series of positive floats (for example, CPU usage), DELETE FROM metrics WHERE cpu=-1 will return this error:

fields not supported in the WHERE clause during deletion

Answer 1

This is not possible with InfluxDB - see Ticket 3210 .

You can overwrite a point with some other values ​​by inserting a point with the same timestamp and set of tags into the dimension:

A point is uniquely identified by the dimension name, tag set, and time stamp. If you send a new point with the same dimensions, a set of tags and a timestamp as an existing point, the set of fields becomes the union of the old set of fields and a new set of fields, where any links go into a new set of fields. This is the intended behavior.

Since you should not insert zeros , you probably want to repeat the values ​​from the previous paragraphs.

You might consider inserting a point with the same timestamp and setting a unique value for one of the tags, and then doing the deletion for that tag:

 DELETE FROM measurement WHERE some_existing_tag='deleteme' 

This will not work though. You will get two points with the same time stamp, and one of them will have a deleteme tag.

Answer 2

Expensive approach

 # Copy all valid data to a temporary measurement SELECT * INTO metrics_clean FROM metrics WHERE cpu!=-1 # Drop existing dirty measurement DROP measurement metrics # Copy temporary measurement to existing measurement SELECT * INTO metrics FROM metrics_clean 

Tip. If you know in what time interval you have dirty data, add it and replace the DROP request with DELETE

Answer 3

An ugly and slow but fairly reliable solution: keep timestamps, then delete records by timestamps.

NB this only works if the fields have unique timestamps! For instance. if there are several fields for one timestamp, all these fields will be deleted using the command below.

 curl -G 'http://localhost:8086/query?db=smarthome' \ --data-urlencode "q=SELECT * FROM metrics WHERE cpu=-1" |\ jq -r "(.results[0].series[0].values[][0])" > delete_timestamps.txt for i in $(cat delete_timestamps.txt); do echo $i; curl -G 'http://localhost:8086/query?db=DATABASE' \ --data-urlencode "q=DELETE FROM metrics WHERE time='$i'"; done