[Custom Component] QuestDB state storage (QSS) for Home Assistant

Share your Projects! Custom Integrations
1
QuestDB Logo

QuestDB state storage (QSS) custom component for Home Assistant

Repository:
CM000n/qss: QuestDB State Storage (QSS) Custom Component for Home Assistant to store entity states inside a QuestDB. (github.com)

QSS makes it possible to transfer state information of the Home Assistant entities simply and efficiently via InfluxDB Line Protocol into a QuestDB for long-term storage and later analysis.

What ist QuestDB?
QuestDB is the new kid on the block of time series optimised databases and claims to be the fastest open source time series database currently available.
QuestDB offers high throughput ingestion and real-time SQL queries for applications in a wide range of use cases. It has a tiny memory footprint and combines the best of different worlds by supporting record entry via the fast and simple InfluxDB Line protocol, while offering great compatibility for common SQL queries (PostgresSQL).
If you want to learn more about the possibilities of QuestDB, have a look at the documentation or this great Youtube video by Code to the Moon.

QSS itself is not a replacement for the recorder component integrated in Home Assistant, but merely offers an alternative for long-term data storage.

Installation

Precondition

  • Make sure that your QuestDB instance is up and running.
  • Possible installation methods for QuestDB can be found in the ‘Get started’ section of the documentation.
  • Apart from that, you do not need to take any further precautions at present. QSS automatically creates a table named qss in which it stores the data.

Installation of the QSS component:

Manual:

  • Copy the qss folder in the custom_components folder of this repository into the custom_components folder of your Home Assistant installation.

Automatic:

  • Just search for “qss” and install the integration via HACS.

configuration.yaml

  • Add an entry to your Home Assistant configuration.yaml that might look like this::
qss:
  host: "192.168.178.3"
  port: 9009
  include:
    domains:
      - "sensor"
    entities:
      - "person.john_doe"

Configuration

qss:
(map)(Required)
Enables the qss integration. Only allowed once.

  host:
  (string)(Required)
  The URL or IP Address that points to your QuestDB database.

  port:
  (int)(Required)
  The port to the InfluxDB line protocol of your QuestDB installation. This is normally 9009 by default.

  exclude:
  (map)(Optional)
  Configure which integrations should be excluded from recordings.

    domains:
    (List[str])(Optional)
    The list of domains to be excluded from recordings.

    entities:
    (List[str])(Optional)
    The list of entity ids to be excluded from recordings.

    entity_globs:
    (List[str])(Optional)
    Exclude all entities matching a listed pattern from recordings (e.g., `sensor.weather_*`).

  include:
  (map)(Optional)
  Configure which integrations should be included in recordings. If set, all other entities will not be recorded.

    domains:
    (List[str])(Optional)
    The list of domains to be included in the recordings.

    entities:
    (List[str])(Optional)
    The list of entity ids to be included in the recordings.

    entity_globs:
    (List[str])(Optional)
    Include all entities matching a listed pattern from recordings (e.g., `sensor.weather_*`).

Details

The data is stored in a QuestDB table named qss, which has the following structure:

Column name: entity_id state attributes timestamps
Type: symbol string string timestamps

Credits

Disclaimer

  • QSS is not an official extension of the QuestDB project and is not directly related to it.
  • No liability is accepted for any loss of data that may occur through the use of QSS. Use at your own risk!
2

Great news! I was also playing around with QuestDB and thought it would be nice to use it as long term state storage.
I have one discussion point regarding table schema. Wouldn’t it be nicer to have state stored as float for measurements instead of string? Storing a decimal number as string uses much more space, and i think this is relevant for long term storage and also for reporting performance.
I know that ltss is doing the same, but shouldn’t we take the chance to do better with a new integration?

3

Hi @zeek , thank’s for your feedback!

States can also have non-numeric states. For example ‘on’, ‘off’, ‘at_home’ and so on.
Since I first wanted to support all sensort states and not only numeric ones, I decided to use a string data format.
For myself, these non-numeric states are also quite relevant for long-term statistics. For example, I monitor the ‘on’, ‘off’ states of the burner in my heating system to analyze the Burner clocking during certain temperatures.
Of course, in this case, I could have converted these non-numeric states back to numeric before saving. But this again means additional manual effort and is not necessarily feasible for every non-numeric state without further ado.

Since nothing in a computer system is currently as cheap as space, and I’ve never reached the limits of it myself, I’d prefer the convenience of not having to think about the format of my entity states over the slightly smaller footprint of storing pure float value compatible states.

But maybe we could think about a feature flag in the config to disable the storage of non-numeric states at all. For this we would probably need two table layouts. One for numeric and one for non-numeric states, which is not used when deactivated via config.

4

Do you require we create a table first or just let it auto create it ?

5

It will autocreate a table called “qss”.

6

I’ve been testing different DBs for long term statistic. This looks like a good option because of you storing states as strings!

I followed the instructions, but alas, it does not seem to work for me.

configuration.yaml:

qss:
  host: "192.168.1.179"
  port: 9009
  include:
    entities:
      - alarm_control_panel.partition_inside
      - alarm_control_panel.partition_outside
      - alarm_control_panel.partition_perimeter

I have the component installed via HACS:
image

I cannot find anything in the logs about it starting or not starting. It isn’t listening on port 9000 either (web console). What am I missing?

Thanks!

7

What exactly do you mean by “listening”? This component does not listen for, but sends information to the specified port. You need a working QuestDB that you have to set up yourself, e.g. via Docker, and that can receive the information.

8

I apologize for wasting your time. Not sure how I missed that this doesn’t include QuestDB itself. Late night reading I guess. I’ll install the DB take it from there. Good weekend.