Influxdb (1.8) setup - ONE continuous query for WHOLE database

Third party integrations
1

Hey folks

since i haven’t found anything in the home assistant community about this / hardly found anything anywhere else and the help in this forum mainly consists of guesses anyway, here are the settings for a proper influxdb database setup to bring up the quality and lower database size.

All credits go to Janina Trischler from

she found the proper settings for influxdb to create a database, create a retention policy (so your raw data wont be stored forever and gets compressed properly at the right time) and, the most tricky for me, the right CONTINUOUS QUERY to not create one for every measurement (C°, %, V, …). I wrote it down for a new influxdb setup. She also has a part for already existing databases (to not loose existing data when messing with the auto-generated retention policy for home_assistant) in her post.

Just my settings, definitely not a guideline and should be changed for own needs:
Raw Data from home assistant stored 6 months(4380h0m0s). After that it gets compressed / shrunk / downsized to 5 minutes, stored for 2 years (17520h0m0s). After that, 15 minutes for 5 years (43800h0m0s).

Create Database:

CREATE DATABASE "home_assistant" WITH DURATION 4380h0m0s REPLICATION 1 SHARD DURATION 168h NAME "6M_raw"

Create Retention Policy:

CREATE RETENTION POLICY "2y_5m" ON "home_assistant" DURATION 17520h0m0s REPLICATION 1
CREATE RETENTION POLICY "5y_15m" ON "home_assistant" DURATION 43800h0m0s REPLICATION 1

Check:

SHOW RETENTION POLICIES

Create Continuous Querys for the last two retention policys:

CREATE CONTINUOUS QUERY "6M_raw-2y_5m" ON "home_assistant" BEGIN SELECT mean(value) AS value INTO "home_assistant"."2y_5m".:MEASUREMENT FROM "home_assistant"."6m_raw"./.*/ WHERE time < now() -4380h0m0s GROUP BY time(5m),* END
CREATE CONTINUOUS QUERY "2y_5m-5y_15m" ON "home_assistant" BEGIN SELECT mean(value) AS value INTO "home_assistant"."5y_15m".:MEASUREMENT FROM "home_assistant"."2y_5m"./.*/ WHERE time < now() -17520h0m0s GROUP BY time(15m),* END

Check:

SHOW CONTINUOUS QUERIES

for completeness, my influxdb entry in the configuration.yaml

influxdb:
  tags_attributes:
        friendly_name
  exclude:
    domains:
      - group
      - history_graph
      - media_player
      - scan_store
      - zone
      - zwave
      - automation
      - camera
      - frl_store
      - light_store
      - script
      - timer
      - updater
    entities:
      - sensor.date
      - sensor.time
4 Likes
2

Hi, I had already data in, how can I repack the already existing data?

Thank you very much

3

Seems that this does the trick, I forgot the * at the end in the beginning…

Good read here: Data Exploration | InfluxData Documentation Archive

SELECT mean(value) AS value INTO "homeassistant"."2y_5m".:MEASUREMENT FROM "homeassistant"."autogen"./.*/ WHERE time < now()GROUP BY time(5m),*

SELECT mean(value) AS value INTO "homeassistant"."5y_15m".:MEASUREMENT FROM "homeassistant"."2y_5m"./.*/ WHERE time < now() -17520h0m0s GROUP BY time(15m),*
4

Sorry to revive this old thread. I followed the instructions from this and the linked thread and have a question understanding it:

I thought the continuous queries would do the following:
take all data from “autogen” (i have already a database with data in it) which is older than 6 months (for example) and put this in the retention policy 2y_5m with aggregation. So in my understanding, when in look in my influxdb (or with grafana) i would see my entities with stats from before 6 months to 2 years. Since the cq is running continuous i would always have data up to 6 months ago.
But for me, the cq is filling the retention policy with actual data and an actual timestamp (from now), not with the data from past 6 months.
Here are my cq’s:

2y_5m-inf_15min CREATE CONTINUOUS QUERY "2y_5m-inf_15min" ON homeassistant BEGIN SELECT mean(value) AS value, mean(crit) AS crit, mean(warn) AS warn INTO homeassistant.inf_15m.:MEASUREMENT FROM homeassistant."2y_5m"./.*/ WHERE time < now() - 730d GROUP BY time(15m), * END
autogen-2y_5m   CREATE CONTINUOUS QUERY "autogen-2y_5m" ON homeassistant BEGIN SELECT mean(value) AS value, mean(crit) AS crit, mean(warn) AS warn INTO homeassistant."2y_5m".:MEASUREMENT FROM homeassistant.autogen./.*/ WHERE time < now() - 26w GROUP BY time(5m), * END

And for me it’s filling not data around 6 Months ago but data from now:

Screenshot 2022-04-15 201759
Screenshot 2022-04-15 2017591473×537 26.7 KB

(screenshot from the 2y_5m policy).
Can anyone point me a hint what i’m doing wrong?

5

I just investigated on my own, it seems the continuous queries don’t accept the where clause and ignores it. https://docs.influxdata.com/influxdb/v1.8/query_language/continuous_queries/#the-cq_query

Regarding this the above constellation couldn’t be possible to hold the raw data in the first database until 6 months, copy all older to the 2y_5m database and so on doing by a contiuous query. Can anyone confirm this?

6

Thanks for this.

As a personal reminder you can also have the CQ apply to all values at once

CREATE CONTINUOUS QUERY "cq_aggregate" ON "home_assistant" BEGIN SELECT mean(*) INTO "home_assistant"."aggregated".:MEASUREMENT FROM "home_assistant"."autogen"./.*/ GROUP BY time(1h), * END

automatically creates mean_value1, mean_value2, … in the aggreagted retention database.

8

Hi guys,
it seems that in the new InfluxDB there is a problem with the syntax where “.:MEASUREMENT” is used.
As I insert the string where this is used into the query, it falls into an error “A Chronograf error has occurred”.
Don’t know how to modify this for use in the new InfluxDB 4.7.0 for HA?

2 Likes
9

Unfortunately I am running into the same problem. Have you found a solution for this?

10

I have the same problem, so what now???

11

Did you get this solved some how?

I’m having the same probem. Actually the error is generated as soon as you use the character “:”. So even to an empty query textbox as soon as : is put, Influx crashes.

1 Like
12

same issue here, still in 2023.12 version.
any solution?