[sql] Can you delete data from influxdb?

How do you delete data from influxdb?

The documentation shows it should be as simple as:

delete from foo where time < 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 > 1000 and time > 14041409940s and time < 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 > 3000000"

delete from bootstrap where duration > 300000"

delete from bootstrap where time = 1404140994043"

delete from bootstrap where duration > 300000 and time > 1404141054508 "

delete from bootstrap where duration > 300000 and time > 1404141054508s "

delete from bootstrap where time > 1404141054508s and duration > 300000 "

delete from bootstrap where duration > 30000 and time > 1s"

Documentation reference

http://influxdb.com/docs/v0.8/api/query_language.html

Update

Additional queries

delete from bootstrap where time > 1404141416824 and duration > 3000;
delete sequence_number from bootstrap where time > 1s and duration > 1000;

Maybe this is a bug?

https://github.com/influxdb/influxdb/issues/975
https://github.com/influxdb/influxdb/issues/84

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-

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.

1. Query for all the records you need to delete (or use some filtering logic in your script)

2. For each of the records you want to delete:

   1. Extract the time and the tag set (ignore the fields)

   2. 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

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.

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 <retention_policy_name> ON <database_name> DURATION <duration> REPLICATION <n> [DEFAULT]

With influx, you can only delete by time

For example, the following are invalid:

#Wrong
DELETE FROM foo WHERE time < '2014-06-30' and duration > 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 > '2014-06-30' and time < '2014-06-30 15:16:01'

Update: this worked on influx 8. Supposedly it doesn't work on influx 9

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:

1. I create a DB and and add start using it.

2. Add some rows in it.Verify if they are added.

3. Delete all with tag 'Dev1' and verify the same.

Note: The tag name has to be in single quotes only. Not double.

You can only delete with your time field, which is a number.

Delete from <measurement> where time=123456

will work. Remember not to give single quotes or double quotes. Its a number.