Core: Metrics are stored in InfluxDB with the wrong data type

[chrissnell]

Home Assistant release with the issue: 0.100.2

Last working Home Assistant release (if known): None

Operating environment (Hass.io/Docker/Windows/etc.): Arch Linux, current with all patches

Integration: InfluxDB, ecobee

Description of problem:
The InfluxDB component's method of choosing data types for metric storage is flawed. It incorrectly stores values as strings instead of floats. An example of this can be found with the ecobee integration. Temperature and fan metrics are stored as strings (e.g. fan_str) and thus, they cannot be graphed in Grafana, nor can we do any downsampling or roll-ups.

This bug was reported as part of another issue a few years back: https://github.com/home-assistant/home-assistant/issues/5575

That other issue was never fixed and the bug that it reports (garbage measurements being stored) still exists.

To fix this, the InfluxDB integration could be smarter when determining data types (e.g. "Does it look like a float?"). Alternatively, a user could provide a map that ties metrics to a user-specified type.

Problem-relevant configuration.yaml entries and (fill out even if it seems unimportant):

influxdb:
  host: 127.0.0.1
  username: blah
  password: blah
  database: homeassistant

Traceback (if applicable):

Additional information:

[chrissnell]

This affects other devices, too. My Aeotec home energy power meter's metrics are also being incorrectly stored:

# influx
> SHOW SERIES
key
---
",domain=sensor,entity_id=rain_today
%,domain=sensor,entity_id=outside_humidity
%,domain=sensor,entity_id=samjin_water_010ad946_1_1
A,domain=sensor,entity_id=aeon_labs_zw095_home_energy_meter_gen5_current
A,domain=sensor,entity_id=aeon_labs_zw095_home_energy_meter_gen5_current_2
A,domain=sensor,entity_id=aeon_labs_zw095_home_energy_meter_gen5_current_3
A,domain=sensor,entity_id=aeon_labs_zw095_home_energy_meter_gen5_previous_reading_12
A,domain=sensor,entity_id=aeon_labs_zw095_home_energy_meter_gen5_previous_reading_4
A,domain=sensor,entity_id=aeon_labs_zw095_home_energy_meter_gen5_previous_reading_8
V,domain=sensor,entity_id=aeon_labs_zw095_home_energy_meter_gen5_previous_reading_11
V,domain=sensor,entity_id=aeon_labs_zw095_home_energy_meter_gen5_previous_reading_3
V,domain=sensor,entity_id=aeon_labs_zw095_home_energy_meter_gen5_previous_reading_7
V,domain=sensor,entity_id=aeon_labs_zw095_home_energy_meter_gen5_voltage
V,domain=sensor,entity_id=aeon_labs_zw095_home_energy_meter_gen5_voltage_2
V,domain=sensor,entity_id=aeon_labs_zw095_home_energy_meter_gen5_voltage_3
W,domain=sensor,entity_id=aeon_labs_zw095_home_energy_meter_gen5_power
W,domain=sensor,entity_id=aeon_labs_zw095_home_energy_meter_gen5_power_2
W,domain=sensor,entity_id=aeon_labs_zw095_home_energy_meter_gen5_power_3
W,domain=sensor,entity_id=aeon_labs_zw095_home_energy_meter_gen5_previous_reading_10
W,domain=sensor,entity_id=aeon_labs_zw095_home_energy_meter_gen5_previous_reading_2
W,domain=sensor,entity_id=aeon_labs_zw095_home_energy_meter_gen5_previous_reading_6
kWh,domain=sensor,entity_id=aeon_labs_zw095_home_energy_meter_gen5_energy
kWh,domain=sensor,entity_id=aeon_labs_zw095_home_energy_meter_gen5_energy_2
kWh,domain=sensor,entity_id=aeon_labs_zw095_home_energy_meter_gen5_previous_reading
kWh,domain=sensor,entity_id=aeon_labs_zw095_home_energy_meter_gen5_previous_reading_5
kWh,domain=sensor,entity_id=aeon_labs_zw095_home_energy_meter_gen5_previous_reading_9
sensor.aeon_labs_zw095_home_energy_meter_gen5_unknown,domain=sensor,entity_id=aeon_labs_zw095_home_energy_meter_gen5_unknown
zwave.aeon_labs_zw095_home_energy_meter_gen5,domain=zwave,entity_id=aeon_labs_zw095_home_energy_meter_gen5

As you can see, the unit of measurement (in this case, W, kWh, V, etc.) is becoming a measurement within Influx, resulting in really screwy metrics like this:

# influx
> SELECT * FROM W limit 1
name: W
time                domain entity_id                                    friendly_name friendly_name_str                            node_id power_consumption value value_id          value_index value_instance
----                ------ ---------                                    ------------- -----------------                            ------- ----------------- ----- --------          ----------- --------------
1573148689523125760 sensor aeon_labs_zw095_home_energy_meter_gen5_power 955           AEON Labs ZW095 Home Energy Meter Gen5 Power 3       0                 0     72057594093273220 8           1

[probot-home-assistant[bot]]

Hey there @fabaff, mind taking a look at this issue as its been labeled with a integration (influxdb) you are listed as a codeowner for? Thanks!

[chrissnell-okta]

So I did some digging into this issue and the one that @vvuk reported in https://github.com/home-assistant/home-assistant/issues/5575 ...

The problem is that the InfluxDB integration uses the unit of measurement as the measurement name in some circumstances.. I believe that this is not the right behavior. A unit should never be the _name_ of the measurement. I think that it's better to use the entity_id.

As a work-around, you can override the name of the measurement in the influxdb configuration section like this:

influxdb:
  component_config:
    sensor.aeon_labs_zw095_home_energy_meter_gen5_previous_reading:
      override_measurement: sensor.aeon_labs_zw095_home_energy_meter_gen5
    sensor.aeon_labs_zw095_home_energy_meter_gen5_previous_reading_2:
      override_measurement: sensor.aeon_labs_zw095_home_energy_meter_gen5
    sensor.aeon_labs_zw095_home_energy_meter_gen5_previous_reading_3:
      override_measurement: sensor.aeon_labs_zw095_home_energy_meter_gen5

You can see that I am forcing a bunch of entities to all be inserted under the same measurement. The entity_ids are applied as a tag, making it easy to SELECT only data from a particular entity.

This is super tedious, however, because you have to do this for most of your devices and do a lot of DB exploration by hand. I will see about proposing a PR to correct this bad logic.

[stale[bot]]

There hasn't been any activity on this issue recently. Due to the high number of incoming GitHub notifications, we have to clean some of the old issues, as many of them have already been resolved with the latest updates.
Please make sure to update to the latest Home Assistant version and check if that solves the issue. Let us know if that works for you by adding a comment 👍
This issue now has been marked as stale and will be closed if no further activity occurs. Thank you for your contributions.

[goggel]

An option for influx to store the measurements always with the entity as name would be great. Because a large number of measurements under % or C is confusing.

[chrissnell]

Still an issue, still a PITA. :/

[vvuk]

I haven't used HA for a while (but am about to again!) -- I believe the commit at https://github.com/vvuk/home-assistant/commits/influxdb-fixes (2nd one, first one is a bonus) still fixes the issue. It made things look sane for me, based on a configuration so as not to break existing users.

[Santobert]

The use of the entity ID would lead to a lot of measurements. That's one thing thats bothering me at the moment. In fact I like the few measurements that are units because they allow queries with multiple entity IDs and they have a fixed set of fields which improves the usability a lot. Don't get me wrong, this is still not perfect. As you said, it's confusing sometimes, but I like the idea of entity IDs as tags and the filter mechanisms coming with it.

My experience with influxdb and my understanding of their docs make me think that the measurement should somehow be related to the unit in combination with the context. This would lead to simple measurements with a fixed set of fields and it would allow filtering by entity IDs.

For the moment I would suggest a solution with domains as measurements because we're lacking some necessary information to generate a measurement for every value.

[stale[bot]]

There hasn't been any activity on this issue recently. Due to the high number of incoming GitHub notifications, we have to clean some of the old issues, as many of them have already been resolved with the latest updates.
Please make sure to update to the latest Home Assistant version and check if that solves the issue. Let us know if that works for you by adding a comment 👍
This issue now has been marked as stale and will be closed if no further activity occurs. Thank you for your contributions.

[chrissnell-okta]

Please keep open.

[stale[bot]]

There hasn't been any activity on this issue recently. Due to the high number of incoming GitHub notifications, we have to clean some of the old issues, as many of them have already been resolved with the latest updates.
Please make sure to update to the latest Home Assistant version and check if that solves the issue. Let us know if that works for you by adding a comment 👍
This issue now has been marked as stale and will be closed if no further activity occurs. Thank you for your contributions.

[vvuk]

An option for influx to store the measurements always with the entity as name would be great. Because a large number of measurements under % or C is confusing.

There is enough confusion and seems to be preferences around this, that an option should both be simple and easy. Is there any disagreement on just adding an option for this to influxdb config?

[chrissnell]

Sounds good to me

On Tue, Aug 18, 2020 at 17:29 Vladimir Vukicevic [e-mail]
wrote:

>
>
>
>

An option for influx to store the measurements always with the entity as
name would be great. Because a large number of measurements under % or C is
confusing.

There is enough confusion and seems to be preferences around this, that an
option should both be simple and easy. Is there any disagreement on just
adding an option for this to influxdb config?

—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHub
https://github.com/home-assistant/core/issues/28613#issuecomment-675729847,
or unsubscribe
https://github.com/notifications/unsubscribe-auth/AAIF34QZMI36VAUNCSHRY3LSBLXBZANCNFSM4JKIDTKA
.

>