transform.py

>>> for x in filter(lambda x: 'unknown' in x, [x['name'] for x in list(client.query("SHOW MEASUREMENTS"))[0]]):
...    print("DROP MEASUREMENT \"" + x + "\"")


>>> for x in filter(lambda x: 'unknown' in x, [x['name'] for x in list(client.query("SHOW MEASUREMENTS"))[0]]):
...    client.query("DROP MEASUREMENT \"" + x + "\"")


from influxdb import InfluxDBClient

client = InfluxDBClient('home.hoegel', 8086, database='home_assistant')

print('\n'.join([x['name'] for x in list(client.query("SHOW MEASUREMENTS"))[0]]))

# SHOW TAG VALUES FROM "W" WITH KEY = "entity_id"

ignore = (
  '__energy_12',
  '__unknown_12',
  'unknown_power_12',
  'unknown_power_12_2',
  'unknown_power_12_3',
  'unknown_power_12_4',
  'unknown_power_17',
  'unknown_power_17_4',
  'unknown_power_17_8',
  'unknown_power_19_8',
  'unknown_previous_reading_12',
  'unknown_previous_reading_12_2',
  'unknown_previous_reading_12_3',
  'unknown_previous_reading_12_4',
  'unknown_previous_reading_17',
  'unknown_previous_reading_17_9',
)

rename = {
  'greenwave_powernode_6_port_power_10': 'greenwave_powernode_6_port_power_10_8',
  'greenwave_powernode_6_port_power_10_2': 'greenwave_powernode_6_port_power_10_8_2',
  'greenwave_powernode_6_port_power_10_3': 'greenwave_powernode_6_port_power_10_8_3',
  'greenwave_powernode_6_port_power_10_4': 'greenwave_powernode_6_port_power_10_8_4',
  'greenwave_powernode_6_port_power_10_5': 'greenwave_powernode_6_port_power_10_8_5',
  'greenwave_powernode_6_port_power_10_6': 'greenwave_powernode_6_port_power_10_8_6',

  'greenwave_powernode_6_port_power_11': 'greenwave_powernode_6_port_power_11_8',
  'greenwave_powernode_6_port_power_11_2': 'greenwave_powernode_6_port_power_11_8_2',
  'greenwave_powernode_6_port_power_11_3': 'greenwave_powernode_6_port_power_11_8_3',
  'greenwave_powernode_6_port_power_11_4': 'greenwave_powernode_6_port_power_11_8_4',
  'greenwave_powernode_6_port_power_11_5': 'greenwave_powernode_6_port_power_11_8_5',
  'greenwave_powernode_6_port_power_11_6': 'greenwave_powernode_6_port_power_11_8_6',

  'fibaro_system_fgwpe_wall_plug_power_7': 'fibaro_system_fgwpe_wall_plug_power_7_4',

  'aeotec_home_energy_meter_g2_power_12': 'aeotec_home_energy_meter_g2_power_12_8',
  'aeotec_home_energy_meter_g2_power_12_2': 'aeotec_home_energy_meter_g2_power_12_8_2',
  'aeotec_home_energy_meter_g2_power_12_3': 'aeotec_home_energy_meter_g2_power_12_8_3',
  'aeotec_home_energy_meter_g2_power_12_4': 'aeotec_home_energy_meter_g2_power_12_8_4',
}


measurement_name = "W"

cnt = list(client.query("SELECT COUNT(value) FROM \"%s\"" % measurement_name))[0][0]['count']
offset = 0
res = client.query("SELECT * FROM \"%s\" LIMIT 100000" % measurement_name)
res = list(res)[0]
while len(res) != 0:
   print("Offset %s, %s %% DONE" % (offset, offset/cnt*100))
   measurements = []
   for e in res:
      if e['entity_id'] in ignore:
         continue

      if e['entity_id'] in rename:
         e['entity_id'] = rename[e['entity_id']]

      if isinstance(e['node_id'], int):
         e['node_id'] = float(e['node_id'])

      e['value'] = float(e['value'])

      measurement = dict()
      measurement["measurement"] = "%s_new" % measurement_name
      measurement["time"] = e.pop("time")
      measurement["tags"] = {}
      measurement["tags"]["domain"] = e.pop("domain")
      measurement["tags"]["entity_id"] = e.pop("entity_id")
      measurement["fields"] = e
      measurements.append(measurement)

   client.write_points(measurements)

   offset = offset + len(res) 
   res = client.query("SELECT * FROM \"%s\" LIMIT 100000 OFFSET %s" % (measurement_name, offset))
   res = list(res)[0]