Can you delete data from influxdb

Question

How do you delete data from influxdb    The documentation shows it should be as simple as   delete from foo where time  lt  now   -1h   For some reason  influxdb rejects my delete statements saying  Delete queries can t have where clause that doesn t reference time   select   from bootstrap where duration  gt  1000 and time  gt  14041409940s and time  lt  now     I want to delete these 5 entries whos duration   1000 seconds    This should be a valid sql statement  yet it fails    None of these delete statements work either  delete from bootstrap where duration  gt  3000000   delete from bootstrap where duration  gt  300000   delete from bootstrap where time   1404140994043   delete from bootstrap where duration  gt  300000 and time  gt  1404141054508    delete from bootstrap where duration  gt  300000 and time  gt  1404141054508s    delete from bootstrap where time  gt  1404141054508s and duration  gt  300000    delete from bootstrap where duration  gt  30000 and time  gt  1s    Documentation reference  http   influxdb com docs v0 8 api query language html  Update  Additional queries  delete from bootstrap where time  gt  1404141416824 and duration  gt  3000  delete sequence number from bootstrap where time  gt  1s and duration  gt  1000    Maybe this is a bug    https   github com influxdb influxdb issues 975 https   github com influxdb influxdb issues 84

User · Answer

With influx  you can only delete by time  For example  the following are invalid    Wrong DELETE FROM foo WHERE time  lt   2014-06-30  and duration  gt  1000  Can t delete if where clause has non time entity   This is how I was able to delete the data  DELETE FROM foo WHERE time  gt   2014-06-30  and time  lt   2014-06-30 15 16 01    Update  this worked on influx 8  Supposedly it doesn t work on influx 9

User · Answer

The accepted answer  DROP SERIES  will work for many cases  but will not work if the records you need to delete are distributed among many time ranges and tag sets   A more general purpose approach  albeit a slower one  is to issue the delete queries one-by-one  with the use of another programming language    Query for all the records you need to delete  or use some filtering logic in your script  For each of the records you want to delete    Extract the time and the tag set  ignore the fields  Format this into a query  e g   DELETE FROM  things  WHERE time 123123123 AND tag1  val  AND tag2  val    Send each of the queries one at a time

User · Answer

Because InfluxDB is a bit painful about deletes  we use a schema that has a boolean field called  ForUse   which looks like this when posting via the line protocol  v0 9    your measurement your tag foo ForUse TRUE value 123 5 1262304000000000000   You can overwrite the same measurement  tag key  and time with whatever field keys you send  so we do  deletes  by setting  ForUse  to false  and letting retention policy keep the database size under control   Since the overwrite happens seamlessly  you can retroactively add the schema too   Noice

User · Answer

It appears that you can do this in influxdb 0 9  For instance  here s a query that just succeeded for me    DROP SERIES FROM temperature WHERE machine  zagbar     Per generous comment by  MuratCorlu  I m reposting my earlier comment as an answer

User · Answer

You can only delete with your time field  which is a  number   Delete from  lt measurement gt  where time 123456   will work  Remember not to give single quotes or double quotes  Its a number

User · Answer

I m surprised that nobody has mentioned InfluxDB retention policies for automatic data removal   You can set a default retention policy and also set them on a per-database level   From the docs   CREATE RETENTION POLICY  lt retention policy name gt  ON  lt database name gt  DURATION  lt duration gt  REPLICATION  lt n gt   DEFAULT

User · Answer

I am adding this commands as reference for altering retention inside of InfluxDB container in kubernetes k8s  wget is used so as container doesn t have curl and influx CLI   wget  localhost 8086 query pretty true  --post-data  db k8s q ALTER RETENTION POLICY   default   on   k8s   duration 5h shard duration 4h default  -O-   Verification  wget  localhost 8086 query pretty true  --post-data  db k8s q SHOW RETENTION POLICIES  -O-

User · Answer

This is for InfluxDB shell version  1 8 2 Delete works without time field too  As you can see from the series of screen shots   I create a DB and and add start using it     Add some rows in it Verify if they are added     Delete all with tag  Dev1  and verify the same    Note  The tag name has to be in single quotes only  Not double

[sql] Can you delete data from influxdb?

Examples related to sql

Examples related to influxdb