Delete points with unwanted field values from Infl

问题:

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

For example, if you have accidentally stored a measurement with a value of -1 in a series of positive floats (e.g. CPU utilization), DELETE FROM metrics WHERE cpu=-1 will return this error:

fields not supported in WHERE clause during deletion

回答1:

This is not possible in InfluxDB - see ticket 3210.

You could overwrite the point with some other values by inserting in the measurement a point with the same timestamp and tag set:

A point is uniquely identified by the measurement name, tag set, and timestamp. If you submit a new point with the same measurement, tag set, and timestamp as an existing point, the field set becomes the union of the old field set and the new field set, where any ties go to the new field set. This is the intended behavior.

Since you're not supposed to insert nulls, you'll probably want to repeat the values from the previous point(s).

You might think about inserting a point with the same timestamp, and setting a unique value for one of the tags, then running a delete against that tag:

DELETE FROM measurement WHERE some_existing_tag='deleteme'

This won't work though. You'll end up with two points with the same timestamp, and one of them having the deleteme tag.

回答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 which time range you have dirty data, add that and replace DROP query with DELETE