Converting value of each measurement to multiple fields in another measurement

Nikodemski2002 · January 21, 2021, 1:44pm

Hi. Is there any simple way to convert the value of each measurement to multiple fields in another measurement? I’m using the InfluxDB 1.8, (I will update to 2.0 when setting up a new server).
So I have three measurements (t1, t2, m3), each measurement “inside” has a tag (meter_id), value, and timestamp.

The thing I want to achieve is to create a new measurement called readings with the tag meter_id (based on a meter_id from one of the measurements), three fields t1,t2,m3(based on the value of each measurement), and timestamp (based on a timestamp from one of the measurements, because they have a slight difference in milliseconds).

I’m a new user of InfluxDB and my knowledge is pretty limited, so every piece of information is like gold worth. I enclose below the graphical representation of what I want to achieve.

Anaisdg · January 21, 2021, 7:33pm

Hello @Nikodemski2002,
Welcome!
Interesting question. Just to make sure I understand, are you looking to perform a bulk migration and schema change simultaneously for your upgrade from 1.8 to. 2.0?

Nikodemski2002 · January 21, 2021, 7:43pm

No. For now, I only want to change the data layout from 3 measurements to multiple fields in one measurement. The update was only additional information.

Anaisdg · January 22, 2021, 8:18pm

Hello @Nikodemski2002,
If you’re looking to make this change for historical data, you’ll simply have to rewrite the data to fit the new schema design. InfluxDB isn’t designed to handle updates, the following documentation highlights why this is in detail:
https://docs.influxdata.com/influxdb/v1.8/concepts/insights_tradeoffs/

You could potentially use Flux to perform this rewrite, depending on how much data you have. How much data are you looking to change?

Nikodemski2002 · January 25, 2021, 7:03pm

Hi. It’s approximately 1,4 million records because the sensor is sending data within 16 seconds intervals. So for 2 weeks, it will be sth about this.

Nikodemski2002 · January 25, 2021, 7:05pm

Correction. 100k records

Nikodemski2002 · January 30, 2021, 9:14pm

Hi @Anaisdg, do you know what Flux script I should use, because after reading the documentation I don’t have the faintest idea?

Best regards.

Anaisdg · February 1, 2021, 4:45pm

Hello @Nikodemski2002,
For that type of data collection I think it makes the most sense to change your telegraf configuration. Rather than retroactively use flux to change the schema.

Nikodemski2002 · February 2, 2021, 10:37am

Yeah but I didn’t use telegraf for data collection. I used the HTTP push data to database. So do you mean that I can change the current data layout with telegraf?