I do not know how to copy data to another database

Grafana InfluxDB
1

Hello! I have a database of the electric consuption of my home since one year. All works great, I can show metrics in a kind frontend thanks Grafana and everything goes well antil I change the columns.

Old database:
1

New database:
2

The only difference is P_Grid & P_Export, that is the power consuption from grid or exported to grid of my home.

Old: P_Grid can be positive or negative
New: P_Grid only have values positives.
P_Export only negatives

This is necessary to calculate with precision the energy.

I tried the new schema for a month and I would like to join the two databases but:

  • The positive values of old P_Grid from first databases and write on the new P_Grid
  • The negative values of old P_Grid from first databases and write on the new P_Export

Can you help me with the INTO clause of Influx? I do not understand the documentation and can not find examples of queries to copy data from any source to another.

2

SOLVED:

Finally I got it! I got merge there databases with different schemas in one.

First create the new DB, then using right the “AS” clause to change the name of columns and “INTO” to copy data to the new database

The “secret” for a newby user is:

SELECT "old_data_name" AS "new_data_name" INTO "New_database"."Retention_Policy"."Measurement" FROM "old_DB_measurement_name" WHERE time >= '2019-06-19T21:00:00Z' AND time <= '2020-02-06T03:00:30Z' GROUP BY *;

"GROUP BY * " means copy all tags.

First os all, we create the new database:

CREATE DATABASE New_DB_v2

First database: (The oldest database. Look after WHERE, I used a filter (“P_Grid” < 0) to copy only some data)

use fotovoltaica
SELECT "P_PV" AS "P_FV" INTO "New_DB_v2"."autogen"."Potencias" FROM "Medida_Fronius" WHERE time >= '2019-06-19T21:00:00Z' AND time <= '2020-02-06T03:00:30Z' GROUP BY *;
SELECT "P_Grid" AS "P_Exp" INTO "New_DB_v2"."autogen"."Potencias" FROM "Medida_Fronius" WHERE ("P_Grid" < 0) AND time >= '2019-06-19T21:00:00Z' AND time <= '2020-02-06T03:00:30Z' GROUP BY *;
SELECT "P_Grid" AS "P_Red" INTO "New_DB_v2"."autogen"."Potencias" FROM "Medida_Fronius" WHERE ("P_Grid" >= 0) AND time >= '2019-06-19T21:00:00Z' AND time <= '2020-02-06T03:00:30Z' GROUP BY *;
SELECT "P_Load" AS "P_Cargas" INTO "New_DB_v2"."autogen"."Potencias" FROM "Medida_Fronius" WHERE time >= '2019-06-19T21:00:00Z' AND time <= '2020-02-06T03:00:30Z' GROUP BY *;

Second database: (here change the data names and have different old measurement name)

use fotovoltaica_pruebas2
SELECT "P_FV" AS "P_FV" INTO "New_DB_v2"."autogen"."Potencias" FROM "Medida_Fronius" WHERE time >= '2020-02-06T03:00:31Z' AND time <= '2020-03-01T22:11:20Z' GROUP BY *;
SELECT "P_Export" AS "P_Exp" INTO "New_DB_v2"."autogen"."Potencias" FROM "Medida_Fronius" WHERE time >= '2020-02-06T03:00:31Z' AND time <= '2020-03-01T22:11:20Z' GROUP BY *;
SELECT "P_Red" AS "P_Red" INTO "New_DB_v2"."autogen"."Potencias" FROM "Medida_Fronius" WHERE time >= '2020-02-06T03:00:31Z' AND time <= '2020-03-01T22:11:20Z' GROUP BY *;
SELECT "P_Cargas" AS "P_Cargas" INTO "New_DB_v2"."autogen"."Potencias" FROM "Medida_Fronius" WHERE time >= '2020-02-06T03:00:31Z' AND time <= '2020-03-01T22:11:20Z' GROUP BY *;

Third database: (here the name of measurements are the same “Potencias”, but it is in differents databases)

use fotovoltaica_v2
SELECT "P_FV" AS "P_FV" INTO "New_DB_v2"."autogen"."Potencias" FROM "Potencias" WHERE time >= '2020-03-01T22:11:21Z' AND time <= now() GROUP BY *;
SELECT "P_Expo" AS "P_Exp" INTO "New_DB_v2"."autogen"."Potencias" FROM "Potencias" WHERE time >= '2020-03-01T22:11:21Z' AND time <= now() GROUP BY *;
SELECT "P_Red" AS "P_Red" INTO "New_DB_v2"."autogen"."Potencias" FROM "Potencias" WHERE time >= '2020-03-01T22:11:21Z' AND time <= now() GROUP BY *;
SELECT "P_Cargas" AS "P_Cargas" INTO "New_DB_v2"."autogen"."Potencias" FROM "Potencias" WHERE time >= '2020-03-01T22:11:21Z' AND time <= now() GROUP BY *;

I hope these examples could help new users and that it helps developers to add better examples in the documentation.