Hi there,
i am using Influxdb2 to store my smarthome values with openhab.
Now with new OH version my energy measurements no longer stored as kWh but in Wh. So i have the old values and new values (=old value *1000) mixed up in one measurement.
How can i update the old values and recalculate them with value*1000 ?
thanks in advance
br
I feel your pain. In my opinion there needs to be more articles on how to handle this kind of thing. This is lifecycle / roadmap planning stuff and needs better info for users/admins to plan how they will handle this before they have two data formats collide.
For now, (and maybe this is good enough forever in your environment) , you could probably to a conditional transform to normalize the values
Eg (untested!)
from(bucket: "example-bucket")
|> range(start: -5m)
|> filter(fn: (r) => r._measurement == "mem" and r._field == "used_percent" )
|> map(fn: (r) => ({
r with
r._value:
if r._value >= 1000.1 then r._value / 1000
else r._value
})
)
Hi!
Thanks for your answer!
But this is no solution for me.
Meanwhile i tried to overwrite the wrong values (only 225) with a csv file. influx write imported it but the data did not change…
Really frustrating!
What’s the retention on your bucket? If it’s not forever, then the two different magnitudes of data will be temporary. In which case, the normalization applied during any queries on that table should be sufficient, and will be redundant after the original magnitude data ages enough to be deleted.
What error did the query throw when you ran it?
Is there more to the issue ?
It is forever…
That is the point, the query returned no error. It ran successfully…
So i have no idea why original data was not overwritten with new value.
Understand now.
The query I put above only returns and transforms the data on the fly. Eg as part of a dashboard query. It doesn’t update the source data
Perhaps @scott might know of a way to handle this.
@dominikbenner What was the query you used to overwrite the data with CSV?
All data points in InfluxDB are uniquely identified by their timestamp, tag set, and field key. To overwrite an existing data point, provide an updated point with an identical timestamp, tag set, and field key and write that point back to InfluxDB.
(I’ve added influxdata/docs-v2#2256 to start and track work for creating a guide that walks through overwriting data.)
If you know the time and date that the data format changed, you should just be able to query data from before that time, update it, and write it back to InfluxDB:
dataChangeDate = 2021-01-01T00:00:00Z
from(bucket: "example-bucket")
|> range(start: -2y, stop: dataChangeDate)
|> filter(fn: (r) => r._measurement == "example-measurement")
|> map(fn: (r) => ({ r with _value: r._value * 1000 }))
|> to(bucket: "example-bucket")
I’ve tested this method locally 
and it works.
@scott
Thanks for your answer!
I tried with influx write
with these test lines:
#datatype measurement,double,dateTime:RFC3339
Haushalt_Total,29601.526,2021-02-27T06:27:44.454Z
Haushalt_Total,29601.5276,2021-02-27T06:27:50.261Z
Haushalt_Total,29601.529,2021-02-27T06:28:01.724Z
Haushalt_Total,29601.5304,2021-02-27T06:28:10.810Z
Haushalt_Total,29601.5318,2021-02-27T06:28:21.061Z
Haushalt_Total,29601.5332,2021-02-27T06:28:31.366Z
Haushalt_Total,29601.5346,2021-02-27T06:28:41.669Z
Haushalt_Total,29601.536,2021-02-27T06:28:52.070Z
Haushalt_Total,29601.5372,2021-02-27T06:29:05.569Z
Haushalt_Total,29601.5386,2021-02-27T06:29:12.179Z
Haushalt_Total,29601.54,2021-02-27T06:29:22.230Z
Haushalt_Total,29601.5414,2021-02-27T06:29:32.481Z
Haushalt_Total,29601.5426,2021-02-27T06:29:42.527Z
Haushalt_Total,29601.544,2021-02-27T06:29:52.576Z
Haushalt_Total,29601.5454,2021-02-27T06:30:02.679Z
Haushalt_Total,29601.5466,2021-02-27T06:30:12.731Z
Haushalt_Total,29601.548,2021-02-27T06:30:22.783Z
Haushalt_Total,29601.5494,2021-02-27T06:30:33.138Z
Haushalt_Total,29601.5506,2021-02-27T06:30:43.180Z
Haushalt_Total,29601.552,2021-02-27T06:30:53.228Z
Haushalt_Total,29601.5532,2021-02-27T06:31:03.273Z
Haushalt_Total,29601.5546,2021-02-27T06:31:13.323Z
Haushalt_Total,29601.556,2021-02-27T06:31:23.373Z
Haushalt_Total,29601.5574,2021-02-27T06:31:33.423Z
Haushalt_Total,29601.5588,2021-02-27T06:31:43.581Z
Haushalt_Total,29601.5602,2021-02-27T06:31:53.625Z
Haushalt_Total,29601.562,2021-02-27T06:32:03.669Z
Haushalt_Total,29601.5637,2021-02-27T06:32:13.868Z
Haushalt_Total,29601.5649,2021-02-27T06:32:23.908Z
Haushalt_Total,29601.5663,2021-02-27T06:32:33.956Z
Haushalt_Total,29601.5677,2021-02-27T06:32:44.017Z
Haushalt_Total,29601.5691,2021-02-27T06:32:54.216Z
Haushalt_Total,29601.5703,2021-02-27T06:33:04.267Z
Haushalt_Total,29601.5722,2021-02-27T06:33:14.315Z
Haushalt_Total,29601.5737,2021-02-27T06:33:24.440Z
Haushalt_Total,29601.5751,2021-02-27T06:33:34.486Z
There was no error but the data has not changed. I got the timestamps and values from my chronograf query.:
SELECT value FROM "openhab"."autogen"."Haushalt_Total" WHERE time > :dashboardTime: AND time < :upperDashboardTime: AND value > 1000000
I will try your flux query.
br.
Modified your query. It ran without error but the data did not change either…
from(bucket: "openhab")
|> range(start: 2021-02-27T06:00:00Z, stop: 2021-02-27T09:00:00Z)
|> filter(fn: (r) => r._measurement == "Haushalt_Total" and r._value > 100000)
|> map(fn: (r) => ({ r with _value: r._value / 1000 }))
|> to(bucket: "openhab")
And you’re certain the following query returns data?
from(bucket: "openhab")
|> range(start: 2021-02-27T06:00:00Z, stop: 2021-02-27T09:00:00Z)
|> filter(fn: (r) => r._measurement == "Haushalt_Total" and r._value > 100000)
@scott
mea culpa!
Was my fault.
It was the wrong bucket. Instead of “openhab” i had to use “openhab/autogen”.
Now your script worked and my values are fine again.
Thanks for your help!
br
@dominikbenner Awesome! Happy to help!
Hi Scott,
It worked for me:
from(bucket: “example-bucket”)
|> range(start: -2y, stop: dataChangeDate)
|> filter(fn: (r) => r._measurement == “example-measurement”)
|> map(fn: (r) => ({ r with _value: r._value * 1000 }))
|> to(bucket: “example-bucket”)
i need to update the values for the host. For example → I need to update the values for a particular hostname. How to add where condition in this
@seema, you’d use filter() to filter the data to only rows with the hostname value you want to update. Then you use set() to update the set the value of the hostname column.
from(bucket: “example-bucket”)
|> range(start: -2y, stop: dataChangeDate)
|> filter(fn: (r) => r._measurement == “example-measurement”)
|> filter(fn: (r) => r.hostname == “old-hostname”)
|> set(fn: (r) => ({key: "hostname", value: "new-hostname}))
|> to(bucket: “example-bucket”)
One important thing to note here however is that because hostname is a tag (I’m assuming), when you write the updated data back to InfluxDB, it will not update the existing points. It will write them as new points. InfluxDB uniquely identifies points based on time, measurement, and tag set. Because the tag set would be different here, InfluxDB would see these as “new” data points. All the points with the old hostname would still exist. You’ll have to manually delete those points.
Quick question on this: I’m using Chronograf 1.10.0 on influxDB 1.8 and I’m getting the error error calling function "to": function "to" is not implemented. Here I read that Flux in InfluxDB 1.x is read-only, so I am not able to write data back to InfluxDB. Is this true?
If yes, what’s the alternative? Using influxQL instead?
