Update false values

influxdb

#1

One of my temperature sensors broke and sent a lot of wrong values to my database:

select * from temperature where device='1'
...
1542702608655299975 1      degreeCelsius 15.7
1542702908953946806 1      degreeCelsius 15.81
1542703209255205623 1      degreeCelsius 15.83
1542790598829483133 1      degreeCelsius -140.83
1542790899130309273 1      degreeCelsius -140.83
1542791199435254116 1      degreeCelsius -140.83
...

I want to get rid of these values without dropping the whole series or data points.
Is there a way to update/overwrite the wrong values with something else, like:

update temperature where device='1' and value='-140.83' with value='15'

thanks,
Andreas


#2

you need to insert back values with the same set of tags (and timestamp obviously) easiest way would likely to select all the ones with the wrong temp into the same measurement (but with the right temperature in the select)


#3

Is there a simple way to do that with out coding something in python or nodejs?
Like a bash solution with "curl xget "… select … from … where … = " > to json or to csv.
Then find & replace the false values in that json/csv file and then simply
curl xput "… update … "< json or csv ?
Or an adequate solution using the influx cli?

thanks,
Andreas


#4

I Have not tried this, but it’s something that might be working…
SELECT time, device, 15 INTO temperature FROM temperature Where degrees = -140.83


#5

I just did it, using the influx client and the -import function. That one was really easy.
I selected all wrong measurements and dumped them into a textfile. Then I reformated the file using an editor and wrote it back:

file influx-update.txt looks like this:

# DML
# CONTEXT-DATABASE: wetter
temperature,device=1,id=degreeCelsius value=14 1542790598829483133
temperature,device=1,id=degreeCelsius value=14 1542790899130309273
temperature,device=1,id=degreeCelsius value=14 1542791199435254116
temperature,device=1,id=degreeCelsius value=14 1542791499733818277
...

and then

influx -import -path influx-update.txt

Andreas