Reputation: 18387

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

enter image description here

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

https://docs.influxdata.com/influxdb/v2.0/query-data/influxql/

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

Upvotes: 67

Answers (11)

Benyamin Jafari

Reputation: 33946

Run influxdb and select the database:

influx -database '<database-name>'

Then run the query:

DELETE WHERE time < '2021-04-11 7:00:00'

Additionally, if you want to delete data from a specific measurement, do as follows:

DELETE FROM <measurement> WHERE time > '2014-06-30' and time < '2021-04-10 15:16:01'

Upvotes: 4

myuce

Reputation: 1381

Adding to already given nice answers; If you are using a version >2.* and can use its UI (usually on port 8086):

Go to "Data"
Select "Buckets"
And also select the bucket you want to manage
and give a retention time as described below;

Upvotes: 1

Dan Esparza

Reputation: 28367

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]

Upvotes: 17

jrc

Reputation: 21881

In InfluxDB 2, InfluxQL commands like DROP don't exist (only InfluxQL read-only queries are supported). Instead, you need to use the CLI or REST API.

Example:

influx delete --bucket "MY BUCKET" --predicate '_measurement="MY_MEASUREMENT"' -o "MY ORG" --start '1970-01-01T00:00:00Z' --stop '2025-12-31T23:59:00Z'

Upvotes: 5

Arindam Roychowdhury

Reputation: 6493

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.

Upvotes: 3

max pleaner

Reputation: 26758

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:
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

Upvotes: 2

Mahaveer Jangir

Reputation: 605

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.

Upvotes: 1

Zaur

Reputation: 431

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-

Upvotes: -2

spuder

Reputation: 18387

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

Upvotes: 30

Jason

Reputation: 2597

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.

Upvotes: 10

John Clements

Reputation: 17203

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

Upvotes: 58

Can you delete data from influxdb?

I want to delete these 5 entries whos duration > 1000 seconds

Answers (11)

Related Questions