Drop typed fields baddly named


I have made a mess in my data. And I’d like some advices/support to cleanup that.

From the CLI (influxDb 1.8.6)

name: data
fieldKey fieldType

text string
time string
valueBoolean boolean
valueBoolean string
valueBoolean::boolean boolean
valueFloat float
valueInteger integer
valueInteger string
valueString float
valueString string

The first mess I’d like to clean :

select valueInteger::string from data order by time DESC limit 3
name: data
time valueInteger

2020-03-07T09:52:25.879Z NaNi
2020-03-07T09:51:27.53Z NaNi
2020-03-07T09:51:01.003Z NaNi

So, I have tried :

DROP SERIES FROM data WHERE valueInteger::string!=’’
ERR: shard 1715: fields not supported in WHERE clause during deletion

Then the other issue :

DROP SERIES FROM data WHERE “valueBoolean::boolean”::boolean!=’’
ERR: shard 1751: fields not supported in WHERE clause during deletion

So, what should I do to remove the field “valueBoolean::boolean”::boolean and “valueInteger”::string

Thanks a lot

you simply can’t delete/drop using field keys or values. (docs here)

  • DELETE supports regular expressions in the FROM clause when specifying measurement names and in the WHERE clause when specifying tag values.
  • DELETE does not support fields in the WHERE clause.

The same is valid also for DROP SERIES.

I see no easy way out for your case but here are some ideas:

  1. Identify the time range and drop data inside that time range, if some data needs to be saved make a specific query to sideload them in a different temporary measurement.
  2. A field key can only have one datatype inside a shard, therefore you could try to identify which shards are “broken” and drop them. I don’t see a lot of docs about it this but try to check what SHOW SHARDS and DROP SHARD can do. (note: shards are created by RP, therefore a single shard contains data from all the measurements for a time range if you want to save some data before dropping you will have to put it into a different retention policy.)
  3. Export your data (out of influx), delete, then import the data back. This should allow you to also clean your data and scavange them i.e. you could turn a number saved as sting back to numeric and then reimport it. This process is time consuming and not that easy (to me), but you can find different ways of doing it by browsing around.

If you can reduce the amount of dropped points by filtering by tags or Measurements, that might help, but sadly I think that’s not your case.
To save/scavenge the good part of the data the query might look like this one

SELECT GoodField1,GoodField2, {...} 
INTO _db_._TmpRP_._TmpMeasurement_ 
FROM _db_._rp_._Measurement_ 
WHERE time >= ___ AND time <= ___ 
1 Like

Thanks @Giovanni_Luisotto for your detailed answer.
@ernond_paul here’s another resource for you as well: