Copy data from one series to another

influxdb
query

#1

1

I’ve setup a home automation system which logs data into influxdb. I’ve two measuraments taken from two variables concerning the same sensor. I would like to remove the old table and migrate old data from the old table to the new. Old table ‘entity_id=node21_child04’, new one ‘entity_id=node21samsung_21_4’. I’ve following data entries in an influxdb database:

> show series;
key
---
C,domain=sensor,entity_id=node21_child04
°C,domain=sensor,entity_id=node21samsung_21_4

Looking on the content, extracting some data, I get those results: Old table:

> SELECT * FROM "autogen"."C" WHERE ("entity_id" = 'node21_child04') AND time >= now() - 1d;
name: C
time                domain entity_id      friendly_name_str          icon_str                value
----                ------ ---------      -----------------          --------                -----
1547806798546962944 sensor node21_child04 Esterno Nord - Temperatura mdi:temperature-celsius 7.2
1547806918435410944 sensor node21_child04 Esterno Nord - Temperatura mdi:temperature-celsius 7.4
1547807278994774016 sensor node21_child04 Esterno Nord - Temperatura mdi:temperature-celsius 7.5
1547807398204632064 sensor node21_child04 Esterno Nord - Temperatura mdi:temperature-celsius 7.4
1547807518145522176 sensor node21_child04 Esterno Nord - Temperatura mdi:temperature-celsius 7.5```

New table:

> SELECT * FROM "autogen"."°C" WHERE ("entity_id" = 'node21samsung_21_4') AND time >= now() - 10m;
name: °C
time                V_TEMP battery_level child_id description description_str          device_str domain entity_id          friendly_name friendly_name_str  heartbeat icon_str        node_id value
----                ------ ------------- -------- ----------- ---------------          ---------- ------ ---------          ------------- -----------------  --------- --------        ------- -----
1547484922088189952 9.3    0             4        11          Temperatura NORD (DHT11) mqtt       sensor node21samsung_21_4 21214         node21Samsung 21 4 0         mdi:thermometer 21      9.3
1547485042146836992 9.4    0             4        11          Temperatura NORD (DHT11) mqtt       sensor node21samsung_21_4 21214         node21Samsung 21 4 0         mdi:thermometer 21      9.4
1547485162424854016 9      0             4        11          Temperatura NORD (DHT11) mqtt       sensor node21samsung_21_4 21214         node21Samsung 21 4 0         mdi:thermometer 21      9
1547485282887762176 8.7    0             4        11          Temperatura NORD (DHT11) mqtt       sensor node21samsung_21_4 21214         node21Samsung 21 4 0         mdi:thermometer 21      8.7

I would like to migrate the values from the old table to the new one. So then I can stop collecting data to the old table and can delete it. Data can be overwritten without issues. How to do that?

Thanks, Simon


#2

nobody can help me a little?
The variables (keys?) that are static i nthe second table and are not present in the first, could remain the same.
In fact only time, V_TEMP and value should moved / copied to the second table. The other colums are not needed.

And sorry, the old table was wrong, ive corrected it.


#3

If you only want to move data from one measurement to another without changing the schema, you can probably use the [INTO clause]. (https://docs.influxdata.com/influxdb/v1.7/query_language/data_exploration/#the-into-clause)


#4

Hello @katy , good morning,
Well, what I would like to do is, detailed above, copy only “colums” time, V_TEMP, domain, icon_str and value from “autogen”.“C” to “autogen”.“°C”. Would the INTO clause do this? I’m not sure if it’s possible.
In case could you give me an example. I don’t feel so safe, I’m new to influx!
Thanks a lot!
Simon


#5

sorry, no ideas?
I’m not making any progress :frowning:
The select into I fear is not exactly what I need. The destination table should remain untouched, only the data from source table needs to migrate, descrived like above.
Thanks, Simon


#6

I’m not sure I understand exactly what you need. Do these datasets live in separate databases?


#7

No, they are in the same database:

> show series
key
---
(...)
C,domain=sensor,entity_id=node21_child04
(...)
°C,domain=sensor,entity_id=node21samsung_21_4

The old dataset is not more updated and due it’s referred to a sensor which is now updated on the new dataset, I would like the old dataset is moved to the new one and I can delete the old one.

Thanks,

Simon