Deleting data in InfluxDB?

I’ve been playing around with InfluxDB and Grafana today and it’s easier that it first come across actually.
But I’m struggling with deleting data, I have data from some sensors which I don’t want, how can I delete it?

I was hoping this should work, but nope…

DELETE FROM "home_assistant"."autogen"."%" WHERE "entity_id"='winecooler_relative_humidity'

Error:
received status code 400 from server: err: error parsing query: retention policy not supported at line 1, char 1

I believe any data that makes its way into the database needs to be deleted in Influx. I believe you want the “drop measurement MEASUREMENT NAME” command.

Take a look here https://docs.influxdata.com/influxdb/v1.6/query_language/database_management/.

It seams impossible from the Chronograf interface at least…

I had to bring out my Windows laptop and download this tool, from there I could run normal sql-queries and remove the data I wanted.

2 Likes

Running it like this is a pita, here’s a script I wrote for removing unwanted measurements from InfluxDB: https://gist.github.com/artkrz/c66f0b735e844a7dc4de907f203fd535

2 Likes

Really useful script, thanks :+1:

Where do I run this script?

Anywhere you have influx client and access to the influx server, or just do it on the host your db is hosted.

I think I fall into same boat as you.

Some things I learnt in last 2 hours after installing InfluxDB addon from addon store in Home Assistant and then renaming entities which I then needed to delete the old entities

  1. The addon that you open and see and interact with in Home Assistant is not InfluxDB it is Chronograf the hover icon screentip is a little misleading.
  2. InfluxDB is installed in Home Assistant and Chronograf is a viewer of the data and from what I have gathered in last 2 hours… Is it is limited in it’s function with doing SQL commands and from what I have seen people are using other programs to connect to InfluxDB like Chronograf is connecting to Influx.

At first I thought I am doing it on the influx client…where is the area to run the script?! Until I realized the influx client was not the influxdb addon I installed in home assistant, but this program

So before you can run the script you need a program like the one mentioned by @ZyberSE GitHub - CymaticLabs/InfluxDBStudio: InfluxDB Studio is a UI management tool for the InfluxDB time series database. that can connect to the InfluxDB in HomeAssistant which you will do through the IP address Port and username and password (same as you would if you wanted to do things with the mqtt side of homeassistant) and then you can talk to the database and run code and scripts at it. But you can’t do it through Chromograf interface you see in HomeAssistant(i don’t think)… happy for anyone to correct me if I am wrong.

3 Likes

What I meant by influx client is literally the client that comes with influxdb server installation from APT package. This is easy for me as I work with influx professionally and I am familiar with how it’s set up professionally. At home I use stand-alone server for it which I use for various things. Maybe you guys are confused because you’re using the addons or whatever it’s called. Sorry, I’m running my flat on ha core. I never used any GUI for it as I never had to, most things can be done in chronograf and rest from cli client.

1 Like

I still see old sensors and such in Grafana after running script in Influx.
Do I need to delete stuff from _internal or autogen in Influx?
I had HA running a long time ago and finally bought house and trying to get back into but i have a lot of old entries and stuffs that been renamed so it gets confusing sifting through orphaned sensors…etc.
Using homeassistant / grafana/influx from docker (not hass.io)

Maybe You should set some retention rather than dig around and remove old entities.

If I try a new integration or rename something, I dont necessarily want to wait for whatever any retention period is so that I can still clean up grafana w/o an enomorous autocomplete dropdown list of values prepolutating.

I have the same issue. When I first started using influxdb I included everything from home assistant.

So I setup includes for only the sensors I wanted then set the retention to 7d. After 7 days I still see all the unwanted measurements but they contain no data other than entity_id and domain.

Is there a way to drop all measurements with no values?

I found a “relatively” simple way of doing it by looking at the script @artkrz provided. I accidentally created about 3000 persistent notifications (D’oh!). The steps assume you’re using the Influxdb HA add-on.

First you need to open a terminal in the Influxdb docker container. The easiest way to do this is using Portainer. I have it installed separately, but I imagine you could use the HA add-on. While you’re there you might want to see where it stores the database, and take a backup - if you do it from within the container it’s /data, which you could copy to /share, for example.

From within the terminal, set up your authentication to Influxdb using the commands below. This is the user/password you set up for Influxdb, which might be different to that for HA.

 export INFLUX_USERNAME=your-user
 export INFLUX_PASSWORD=your-password

Then you can show the series based on the domain (or entity_id) with the following. Note that you need to enter whatever database name you originally configured (mydb in my case).

 influx --host localhost --execute "show series where domain='persistent_notification'" --database=mydb

In my case they’re all only in the state series, so you can delete the data using the following, once again putting in the database name:

influx --host localhost --execute "drop series from state where domain='persistent_notification'"--database=mydb

And you’re done. Rerun the show series to see that it has all gone.

Can anyone advise on how to connect MySQL running on a PC to Influx running on a Raspberry Pi within HA.
I’ve tried the obvious settings but they don’t work. Just no response.