3

I have some data in influxdb that is unnecessary, like some values like "0", so how do i delete those particular once. My database name is "bootstrap" and my measurement name is "response_time"

Tried this "delete from response_time where time > 2016-01-22T06:32:44Z"

but it says "Server returned error: error parsing query: found -01, expected SELECT, DELETE, SHOW, CREATE, DROP, GRANT, REVOKE, ALTER, SET at line 1, char 44"

Tried this also: "delete from bootstrap where time > 2016-01-22T06:32:44Z"

Vishal Puliani
  • 201
  • 1
  • 3
  • 14

1 Answers1

2

The current release of InfluxDB is a bit painful with deletes. You can drop an entire measurement, or a particular series, or an entire database, or the part of a series older than 'x' (retention policy). Anything finer-grained than this is still a bit alpha. Apparently, it was more flexible in v 0.7, but that feature has gone away. Probably not the answer you were hoping for, sorry.

See here:

https://docs.influxdata.com/influxdb/v0.9/query_language/database_management/

(shameless self-promotion follows)

A similar set of questions were asked here. Beware: it seems some answers depend on which version of InfluxDB you use.

My answer, which seems to be version-independent (so far):

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.

Doing this, you can set up your Grafana queries to include "WHERE ForUse = TRUE". By filtering this way, and updating the "ForUse" field, you can replicate the functionality of "deleting" or "undeleting" points.

It's a bit kludgy, but I'm used to kludgy - every time series database I've worked with seems a bit awkward with partial deletes, so it must be something about their nature.

Community
  • 1
  • 1
Jason
  • 2,507
  • 20
  • 25
  • how do i add "ForUse" Field for existing data? – Vishal Puliani Feb 24 '16 at 06:46
  • It would be very nice to have an "alter table add column..." command, but we don't with InfluxDB. Instead, you over-write the existing record. So for each line of a SELECT * FROM ... query, you form a write command (most likely using the http api) of the same timestamp, tag keys, tag values, and append the "ForUse" value. A bit painful, but very do-able. – Jason Apr 28 '16 at 00:35
  • Oh! I see things have changed since 0.9 too. Now to add the point, you only need to duplicate the tag keys and timestamp. Field does not need to be copied. See here: https://docs.influxdata.com/influxdb/v0.10/troubleshooting/frequently_encountered_issues/#writing-duplicate-points – Jason Apr 28 '16 at 03:55
  • thanx that looks helpful, but how do i write new points in influxdb? – Vishal Puliani Apr 28 '16 at 08:13
  • I'm not sure I understand the question. Do you mean what modifications are needed to the existing app that writes to InfluxDB? Each value currently being written will also need a "ForUse" boolean written. This can be computed by the app, or defaulted to True, with other apps or manual selection over-writing ForUse as needed. – Jason Apr 28 '16 at 19:58
  • 1
    [This hasn't gotten any better in Influx 1.0](http://stackoverflow.com/questions/39685114/delete-points-with-unwanted-field-values-from-influxdb-measurement). Re. "every time series database I've worked with seems a bit awkward with partial deletes, so it must be something about their nature" - have you tried Axibase? It supports nulls, [unlike InfluxDB](https://github.com/influxdata/docs.influxdata.com/issues/717). – Dan Dascalescu Sep 26 '16 at 03:00