Reshaping Data to Influxdb Line Protocol
Sometimes I have a gap in my Holy InfluxDB dataset which I fill with data stored elsewhere. This involves reshaping the data into the InfluxDB line protocol. Here I keep a few versions handy for future reference.
Home Assistant ¶
Export data via History viewer, giving csv like
entity_id,state,last_changed
sensor.solaredge_ac_energy_kwh,9454.003,2024-10-21T22:00:00.000Z
sensor.solaredge_ac_energy_kwh,9454.004,2024-10-22T06:27:55.840Z
sensor.solaredge_ac_energy_kwh,9454.005,2024-10-22T06:30:55.629Z
sensor.solaredge_ac_energy_kwh,9454.006,2024-10-22T06:32:55.648Z
sensor.solaredge_ac_energy_kwh,9454.007,2024-10-22T06:34:25.645Z
sensor.solaredge_ac_energy_kwh,9454.008,2024-10-22T06:35:55.645Z
which needs to be converted to e.g.
energyv3,quantity=electricity,source=solaredge,type=production,uniqueid=solaredge_ac_energy_kwh value=x <timestamp>
Check unique sensors:
infile=home-asssitant-history.csv
cat ${infile} | cut -f1 -d, | sort | uniq -c
sensor.compr_in_energy_43141
sensor.dishwasher_energy_2
sensor.dryer_energy
sensor.esp_mobile_sds011_pm10
sensor.esp_mobile_sds011_pm2_5_2
sensor.fridge_energy
sensor.laptop_energy
sensor.metercupboard_energy
sensor.nibecontrol_energy_2
sensor.solaredge_ac_energy_kwh
sensor.solaredge_ac_frequency
sensor.solaredge_ac_voltage_an
sensor.solaredge_temp_sink
sensor.triplesolar_energy_2
sensor.washingmachine_energy_2
sensor.water
sensor.wtw_energy
Map all sensors to InfluxDB measurements
cat << 'EOF' >./hasstoinfluxmap.txt
sensor.compr_in_energy_43141 energyv3,quantity=electricity,source=nibe,type=consumption,uniqueid=compr_in_energy_43141_import20241119
sensor.dishwasher_energy_2 energyv3,quantity=electricity,source=dishwasher,type=consumption,uniqueid=plug_642671_import20241119
sensor.dryer_energy energyv3,quantity=electricity,source=dryer,type=consumption,uniqueid=plug_6189e1_import20241119
sensor.fridge_energy energyv3,quantity=electricity,source=fridge,type=consumption,uniqueid=plug_fde871_import20241119
sensor.laptop_energy energyv3,quantity=electricity,source=laptop,type=consumption,uniqueid=plug_446a13_import20241119
sensor.metercupboard_energy energyv3,quantity=electricity,source=metercupboard,type=consumption,uniqueid=plug_fe5e42_import20241119
sensor.nibecontrol_energy_2 energyv3,quantity=electricity,source=nibecontrol,type=consumption,uniqueid=plug_d96335_import20241119
sensor.solaredge_ac_energy_kwh energyv3,quantity=electricity,source=solaredge,type=production,uniqueid=solaredge_ac_energy_kwh_import20241119
sensor.triplesolar_energy_2 energyv3,quantity=electricity,source=triplesolar,type=consumption,uniqueid=plug_7f2873_import20241119
sensor.washingmachine_energy_2 energyv3,quantity=electricity,source=washingmachine,type=consumption,uniqueid=plug_6426c6_import20241119
sensor.wtw_energy energyv3,quantity=electricity,source=wtw,type=consumption,uniqueid=plug_56ab84_import20241119
sensor.esp_mobile_sds011_pm10 environv3,source=sds011,board=esp_mobile,location=home,quantity=pm10,room=kitchen_import20241119
sensor.esp_mobile_sds011_pm2_5_2 environv3,source=sds011,board=esp_mobile,location=home,quantity=pm25,room=kitchen_import20241119
sensor.solaredge_ac_frequency systemv3,quantity=uacfrequency,source=solaredge_import20241119
sensor.solaredge_ac_voltage_an systemv3,quantity=uac,source=solaredge_import20241119
sensor.solaredge_temp_sink temperaturev3,quantity=actual,source=solaredge,location=device_import20241119
sensor.water waterv3,quantity=potable,source=sensus,board=esp8266-iapetus_import20241119
EOF
done in this script
mapfile=hasstoinfluxmap.txt
infile=home-asssitant-history.csv
outfile=influxquery-line2.txt
while IFS= read -r line; do
# echo "Text read from file: $line"
read -ra linearr <<<"$line"
sensorid=${linearr[0]}
influxquery=${linearr[1]}
# grep ${sensorid} ${infile} | grep -v "unavailable" | head | awk -F, '{ printf "%s%f%s",${influxquery},$2,system("gdate +%s -d \""$3"\"") }'
# grep ${sensorid} ${infile} | grep -v "unavailable" | head | awk -v influxquery="${influxquery}" -F, '{ printf "%s value=%s ", influxquery, $2, $3, system("gdate +%s -d \""$3"\"") }'
if [[ ${sensorid} = "sensor.water" ]]; then
grep ${sensorid} ${infile} | grep -v "unavailable\|unknown" | awk -v influxquery="${influxquery}" -F, '{ printf("%s value=0.5 ", influxquery); system("gdate +%s -d \""$3"\"") }' | tee -a ${outfile}
elif [[ ${influxquery} =~ "energyv3" ]]; then
grep ${sensorid} ${infile} | grep -v "unavailable\|unknown" | awk -v influxquery="${influxquery}" -F, '{ printf("%s value=%s ", influxquery, $2*1000*3600); system("gdate +%s -d \""$3"\"") }' | tee -a ${outfile}
else
grep ${sensorid} ${infile} | grep -v "unavailable\|unknown" | awk -v influxquery="${influxquery}" -F, '{ printf("%s value=%s ", influxquery, $2); system("gdate +%s -d \""$3"\"") }' | tee -a ${outfile}
fi
# gdate +%s -d "2024-10-31T01:29:59.802Z" -- only works on gnu date
# strftime("2024-10-31T01:29:59.802Z","%Y-%m-%dT%I:%M:%S.%Z"
# date +%s -i "%Y-%m-%dT%H:%M:%SZ" -d "2024-10-31T01:29:59.802Z" -- only works on gnu date
done < ${mapfile}
Now push to InfluxDB
curl -i -u ${INFLUX_USERNAME}:${INFLUX_PASSWORD} -XPOST "http://localhost:8086/write?db=smarthomev3&precision=s" --data-binary @influxquery-line2.txt
Check in Grafana if data are continuous, debug otherwise.
To debug, delete the data, e.g.
delete from energyv3 where uniqueid='compr_in_energy_43141_import20241119'