How to copy data between measurements

I’m currently running OSS 2.0, which has migrated data from 1.x
Sometimes I need to move data between measurements, which was relatively easy in InfluxQL, but unfortunately the support is read-only, so SELECT INTO is not an option anymore.
Is there a way to merge measurement data using Flux?

You could use union or join function to merge two input streams into a single output stream.

1 Like

Thanks for the links, the union part is clear, so I can query and merge the measurements I need.
To be more specific, by merge, I also mean to store the result not only union/join them on the fly :slight_smile:
So the harder part for me is the InfluxQL INTO clause equivalent of Flux, which I couldn’t find in the docs.

Question in short: Can I use Flux to insert/store/write query results back into InfluxDB?

@kajarit Yes, you can. Query then data, update the measurement name, then write it back to InfluxDB with to(). Queries in 2.x must be scoped by time, so you have to provide a time range. Depending on how much data is in the measurement, you may need to do this in time-based batches:

from(bucket: "example-bucket")
    |> range(start: 2021-11-21T00:00:00Z, stop: 2021-11-22T00:00:00Z)
    |> filter(fn: (r) => r._measurement == "old")
    |> set(key: "_measurement", value: "new")
    |> to(bucket: "example-bucket")
1 Like

@scott
Thanks for your answer. That works very good for small measurements.

I have two influx Buckets (one from 1.8 and one new 2.2) and want to migrate one into the other. The measurements are from the same things (old smarthome server and new one).

If i use your script in chronograf with only one year for one measurement, chronograf does not copy data and shows me an error of too large segment.

Is there a way to copy the complete measurement into the other even if its large (1.000.000 datapoints)? I have 80 measurements to copy and it would be very frustrating to do it with mothly commands.

from(bucket: "openhab_old")
  |> range(start: 2018-01-01T00:00:00Z, stop: 2019-01-01T00:00:00Z)
  |> filter(fn: (r) => r._measurement == "Vitocal_Betriebsstunden_Belastung1")
  |> set(key: "_measurement", value: "KGHeizungskellerVitocal300A_Belastung1Betriebsstunden")
  |> set(key: "item", value: "KGHeizungskellerVitocal300A_Belastung1Betriebsstunden")
  |> set(key: "category", value: "time")
  |> set(key: "label", value: "Belastung1 Betriebsstunden")
  |> set(key: "type", value: "Number:Time")
  |> to(bucket: "openhab", org: "benner")

@dominikbenner Are you migrating from 1.8 to 2.2 or the other way around?

@scott wasnt clear about this.

Im already an 2.2 with both databases. So i have updated the 1.8 server to 2.2 and moved to a new Server.

I think i have used the wrong wording…

I have 2 buckets (openhab = new; openhab_old = old ). And now i want all old item measurements in my new bucket. For this i used the script above but this die not work for my amount of data.

I think with any method, you’re going to have to batch write the data into the new bucket. Another possibility is to export your data as line protocol and then write the line protocol in. You can use one of the following commands (depending on the InfluxDB version) to export data on disk as line protocol:

With these, you don’t have to use the query engine to query the data out first. You can then write the exported line protocol to your new bucket.

This seems to be the best way!

I exported one measurement.

Do i have to bulk edit the file and change the old measurement name with the new one?

The old data is missing some tags, which are added in the new measurement.
Is there a way to add those tags (all static - so simple to define) to ALL values in one measurement ?