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

回答1:

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

回答2:

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

回答3:

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]

回答4:

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.

回答5:

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-

回答6:

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.