Renaming fields for partial time duration

1

I have a measurement which has multiple fields and often data for only several but not all of the fields is provided, and only the received data is inserted leaving many of the fields with NULL value.

Data for fields F4 and F5 was accidentally swapped and written to Influx between 2019-08-18 and 2019-08-25.

ORIGINAL ATTEMPT

My originally attempt used a PHP script to manually go through the records, however, was definitely the wrong approach. I first performed the following query to get all timestamps which had the bad data.

SELECT F4, F5 FROM MyMes WHERE time>="2019-08-18T00:00:00Z" AND time<="2019-08-25T00:00:00Z"

Then, for each of the timestamps, I performed the following to get all fields that had a value for the given timestamp:

SELECT * FROM MyMes WHERE time>="2019-08-18T00:00:00Z" AND time<="2019-08-25T00:00:00Z"

I then “fixed” the data and wrote it back. For instance, if the previous query returned fields F1, F3, and F4, I would write back F1, F3, NOT F4, and F5 where F5 used the returned F4 value.

I think I did not understand how Influx writes data, and while I think I now know, would like to confirm. If I have an existing values for some fields fir a given timestamp, and then I write to another field for that timestamp, Influx will leave the existing fields as they are, correct?

NEW ATTEMPT

Is the following what I should have done?

SELECT F1, F2, F3, F4, F5 INTO MyMes2 FROM MyMes WHERE time<"2019-08-18T00:00:00Z" GROUP BY *
SELECT F1, F2, F3, F4 AS F5, F5 AS F4 INTO MyMes2 FROM MyMes WHERE time>="2019-08-18T00:00:00Z" AND time<="2019-08-25T00:00:00Z" GROUP BY *
SELECT F1, F2, F3, F4, F5 INTO MyMes2 FROM MyMes WHERE time>"2019-08-25T00:00:00Z" GROUP BY *

DROP MEASUREMENT MyMes
SELECT * INTO MyMes FROM MyMes2 GROUP BY *
DROP MEASUREMENT MyMes2
select min(P95), max(P95), min(P98), max(P98) FROM L2

FIXING MY ORIGINAL GOOF

F4 and F5 have very different values and one can detect using less than and greater than. Is it possible to do some sort of SELECT/INTO query which only copies the data to a new measurement if within bounds?

2

What version of InfluxDB are you using? If >1.7, this is certainly possible with flux as you can use conditionals. I’m not sure if it’s possible with InfluxQL though.

dg

3

Hi dg,

I amusing 1.7.7. Is this the flux you were referring to? Never heard about it. Looks interesting! Also, didn’t realize until now that InfluxDB 2.0 is on the horizon. Looking forward to seeing what it could do.

Is my understanding correct that if a point is overwritten, any existing field values will remain as is if they are not included in the overwrite query?

Also, if I hadn’t f’d my data, you think my “NEW ATTEMPT” will work? I have some backups for the particular time frame and can do something similar but pull from a backup database.

Thanks

4

Time series data isn’t really meant to be ‘updated’. It’s a stream of data, and it is generally understood that, say, the temperature at 10:10:02 was what the reading said it was, for example. So, over-writing old values with new values doesn’t really work the way you think it would based on experience with SQL-like databases that are designed for updating and changing data.

I’m still trying to think up a viable solution to your current problem. :slight_smile:

dg

5

Thank you David,

I didn’t quite make up my NEW ATTEMPT solution from scratch but based it on Workaround for dropping individual field keys located on the influxdb blog. I think it is a feature and not a flaw that InfluxDB allow one to write a single field to an existing point (this is a measurement with a given timestamp, right?) without having to include the existing fields in the write query. It would be nice to be able include NULL for a given field to remove it and hopefully one will be able to do so in 2.0. The workaround is to write the modified desired data to a new measurement and then delete the original measurement and rename this new measurement with the original measurement name.

At least this is how I think Influx works (pretty darn sure), but wanted to confirm that my understanding was correct.