Hi all,
I’m using influxDb 1.8 installed on my own server.
I would like to update some measurements in the same serie. But maybe there is a better way to achieve what I’m looking for.
My specific use case is the following:
I have a serie, let’s called it “P” for Primary.
I have another serie, let’s called it “S”.
I have M1, a first measurement for S.
I want another measurement M2 for S. so far, easy.
But then I would like to fuse M1 & M2 (no matter the algo of fusion yet), probably based on time and write the result as measurement into P.
By doing that I guess some measurement on P might require to be updated. So my2 questions are :
- Am I doing the logic and the database model correctly ? or maybe some tips would be welcomed
- In case I’m on a good way,how do I do such Update on measurements?
Thanks for you help.
Hello @ernond_paul,
Please take a look at the design principles and assumptions for InfluxDB. One of them is that updates are infrequent. InfluxDB design insights and tradeoffs | InfluxDB OSS v1 Documentation
You can rewrite data which will replace old data at the same timestamp.
How do you mean that you want to fuse M1 & M2?
Are you currently using telegraf? If so, have you seen the execd processor plugin? You can use it to make telegraf extensible in the language of your choice and apply any data processing you need before writing to InfluxDB. Here’s an example: TL;DR InfluxDB Tech Tips — Time Series Forecasting with Telegraf | InfluxData
- Am I doing the logic and the database model correctly ? or maybe some tips would be welcomed
How do you mean to fuse M1 & M2 into a new measurement? This could work, but you don’t want your measurements to be unbounded because you might run into runaway cardinality problems. Will the number of new measurements you create have a limit? If so what is it? If it’s not too high then this could work, but it would help me if I knew the details of what you’re doing.
- In case I’m on a good way,how do I do such Update on measurements?
You can’t update a measurement, you can simply write over it.
With some more background on what you’re doing and what you’re trying to achieve, I might be able to help you better. Thank you!
Thank you for your feedback,
I’m only using influxDb 1.8.4, And I don’t really understand yet the logic behind telegraf.
Let me explain more in details what I’m expecting to do on the following scenario :
- A sensor1 measure a value (let’s say the room temperature to make it easy)
- Sensor1 is sending the value to my server using an Api
- My server is storing this value directly to influxDb (tag : “containerSensor1”, tag : “trackA”, field : mysensor1_value)
And then,
- A sensor2 measure a value (again the room temperature)
- Sensor2 is sending the value to my server using an Api
- Server is storing this value directly to influxDb (tag : “containerSensor2”, tag : “trackA”, field : mysensor2_value)
My goal is now to :
- Detect value from sensor1 and sensor2 are on the same time window, so as a consequence I want to “fuse” both values (I considere they are measuring the same temperature because they have the same tag=“trackA”)
- and this fusion (a simple average) would be store to influxDb (tag : “containerSensors1_and_2”, tag : “trackA”, field : (mysensor1_value+mysensor1_value)/2)
Does this make more sens ?
I’m going to read a bit more how telegraf could help me. 
Hello @ernond_paul,
Yes that makes sense. Thank you for explaining.
Are your values in the same measurement? If they are then you could perform a continuous query to frequently calculate that value. It would look something like:
CREATE CONTINUOUS QUERY sensor_fusion ON mydatabase
BEGIN
SELECT (mysensor1 + mysensor2)/2 AS mysensorAvg FROM INTO measurement_fused FROM mymeasurement WHERE mytag = "trackA" GROUP BY time(1h)
END
To learn more about continuous queries please read:
Explore data using InfluxQL | InfluxDB OSS 1.8 Documentation.
Finally your query doesn’t require subqueries, but this could be useful in the future:
Now if your values are in different measurements that’s another story…
Unfortunately you can’t perform math across measurements with InfluxQL. You can do so with Flux though. You could perform a join(), calculate your average with a map(), and write that data to a new bucket (bucket equals database + retention policy in 2.x) with the to() function. Your Flux would look something like this:
measurement1 = from(bucket:"mybucket")
|> range(start: -10d)
|> filter(fn:(r) => r._measurement == "measurement1" and r._field == "mysensor1")
measurement2 = from(bucket:"mybucket")
|> range(start: -10d)
|> filter(fn:(r) => r._measurement == "measurement2" and r._field == "mysensor2")
measurement1_and_2 = join(tables:{measurement1:measurement1, measurement2:measurement2}, on:["_time"])
|> map(fn:(r) => ({
_value: (r._value_measurement1 + r._value_measurement2)/2}),
_newtag: containerSensors1_and_2 )
|> to(bucket: "mybucket", tagColumns: ["newtag")
To learn more about how to perform joins please look at this post:
Otherwise if your data is in different measurements, you can use Kapacitor to perform a join. However, I’d like to advise against trying to learn Kapacitor. It’s very much a black box and difficult to use.
If your values are in different measurements, I recommend either upgrading to 2.x or using telegraf to write your data to InfluxDB and do this processing with the Execd processor plugin as mentioned above.
1 Like
Thank you again,
Yes both came from the same measurement.
I will avoid Kapacitor just being confident on your “difficult to use”.
I’m not using Flux at all, and I don’t believe any join is necessary on my case.
Your option on continuous query seems pretty obvious for this “average” function. This can be enough for my first implementation.
But in the future it could be complex for me, maybe some weighted average or other functions (I’m thinking about fusing temperature and atmospheric pressure to make measures more accurate, that would implies some algorithm in the query).
In the meantime I was looking at telegraf, and my concern would be how to migrate from a straight forward influxDbWrite to telegrafWrite ; (maybe inputs.influxdb_listener could help me)
So in the end it seems I have 2 options :
- installing + using “telegraf” (as a influxDb proxy ?)
- or using “continuous query” at least for a start point
Great … thanks
Awsome - that article is precisely doing sensor Fusion.
All I need to do is activate Flux and read the article to fully understand the logic before implementing. A big challenge for me in the implementation. but let’s try.
Thank you for this third option (I don’t know which one is the best).
Hello @ernond_paul,
I recommend upgrading to 2.x if you’re interested in Flux because you can’t perform tasks in flux with 1.x. You’ll ultimately want to do this kind of work with Flux and then create a task around it to perform the query/data processing on a regular basis.
