Influxdb v2 delete specific data

Example how to delete specific or wrong data from time series.

Posted by Milovan Misho Petković on Sun, Jan 23, 2022
In Development
Tags influxdb, grafana

Very often in measurement from sensors you can get the wrong measurement and get this data in the database. In my case sometimes hardware connection between raspberry pi and temperature sensor can be lost and I get temperature 85°Ç. It’s wrong and this point needs to be removed from the database.

Query data and get a list of points with wrong measurement.

curl --request POST "https://influxurl/api/v2/query?org=OrangeUnit" --header 'Authorization: Token API_TOKEN' --header 'Accept: application/csv' --header 'Content-type: application/vnd.flux' --data 'from(bucket:"heating")
            |> range(start: -7d, stop: -1d )                           
            |> filter(fn: (r) => r._measurement == "sensors")
            |> filter(fn: (r) => r["feature"] == "temp2" AND r._value > 84.0 AND exists r._value)' --output temperature.csv

This curl command query data from bucket heating, before 7 days to yesterday, measurement sensor, feature temp2 and if exists value > 84°C. This data will be stored to temperature.csv, but this csv file contan to many columns. We need only the 6th column with time of measurement 85 and we need to delete this.

Better option is format csv after query and store only time.

curl --request POST "https://influxurl/api/v2/query?org=OrangeUnit" --header 'Authorization: Token API_TOKEN' --header 'Accept: application/csv' --header 'Content-type: application/vnd.flux' --data 'from(bucket:"heating")
            |> range(start: -7d, stop: -1d )                           
            |> filter(fn: (r) => r._measurement == "sensors")
            |> filter(fn: (r) => r["feature"] == "temp2" AND r._value > 84.0 AND exists r._value)' | cut -f6 -d ',' > delete_timesmap.txt  

Delete data

Simple bash script.

for i in $(cat delete_timesmap.txt); do
 echo $i;
 curl --request POST "https://influxurl/api/v2/delete?org=OrangeUnit&bucket=heating" --header 'Authorization: Token API_TOKEN' --header 'Content-Type: application/json' --data '{                                            
    "start": "'$i'",                                         
    "stop": "'$i'"
  }'                                                        
done

Influx v1.8

I found this method on Stackoverflow.

Query

curl -G 'http://localhost:8086/query?db=heating' --data-urlencode "q=SELECT * FROM sensors WHERE  ("feature" = 'temp3') AND temp_input>55" |\jq -r "(.results[0].series[0].values[][0])" > delete_timestamps.txt

delete

for i in $(cat delete_timestamps.txt); do
 echo $i;
 curl -G 'http://localhost:8086/query?db=heating' --data-urlencode "q=DELETE FROM sensors WHERE time='$i'";
done
comments powered by Disqus