Jonathan Wrede

influx-reshape: renaming and retyping tags and fields in InfluxDB

A command line tool that renames, drops and retypes tags, fields and measurements in a running InfluxDB 1.x or 2.x, with a count check after every step.

The problem

InfluxQL has no ALTER. A tag, a field or a measurement cannot be renamed, a single field cannot be dropped, and a field whose type differs between shards (the field type conflict error) cannot be repaired. The feature requests are among the oldest open issues of InfluxDB: #4157 "RENAME TAG" has 271 reactions and has been open since 2015, #6150 "Drop field" has 204, #4155 "RENAME MEASUREMENT" has 119.

The workaround in these threads is SELECT * INTO new FROM old GROUP BY * followed by dropping the old measurement. Users report that it turns tags into fields when GROUP BY * is missing. While testing I found a second problem: when a field has a float in one shard and a string in another, SELECT * returns only one of the types and skips the other values without an error. The only tool linked in the threads, infix, rewrites TSM files offline with the server stopped and supports 1.x only.

How it works

influx-reshape is a Go program without dependencies outside the standard library. It talks to InfluxDB 1.x natively and to 2.x through the v1 compatibility API.

plan reads the schema, the time range and the number of values per field, checks the requested changes and prints the steps. With --scan it runs every point through the changes without writing, which finds failing casts and points that would collide after a tag is dropped.

apply copies the measurement to a staging measurement with the changes applied, drops the original, copies the result back and drops the staging measurement. Points are read in time windows with GROUP BY *, so tags stay tags, and with an explicit select list "field"::type for every type of every field, which makes conflicting fields readable. After each window it counts the values per field in the target and compares them with what it wrote. Progress is saved after every window, and the same command continues after an error. --backup writes the original points as gzipped line protocol first. Besides renames, drops and casts it can turn a tag into a string field and a field into a tag, two other schema mistakes that InfluxQL cannot undo.

Some safety checks run before anything is written: renames must not land on keys that stay, a conflicting field needs a cast, casts are strict (1.5 does not become an integer), and on 1.x the measurement must not exist in another retention policy, because DROP MEASUREMENT deletes it in all of them.

Example

A measurement in which a second script wrote temperature as an integer from February on, on InfluxDB 1.11.8:

$ influx-reshape apply --db home -m sensors --rename-tag room=location \
    --drop-field battery --cast temperature=float --backup sensors.lp.gz --yes
step 1/5: back up "sensors" to sensors.lp.gz
  2544 points written to sensors.lp.gz
step 2/5: copy "sensors" -> "sensors__reshape_tmp" with the changes, verify counts
  2544 points read, 2544 written, 2544 values verified
step 3/5: drop measurement "sensors"
step 4/5: copy "sensors__reshape_tmp" -> "sensors", verify counts
  2544 points read, 2544 written, 2544 values verified
step 5/5: drop measurement "sensors__reshape_tmp"
done

Afterwards SHOW FIELD KEYS lists only temperature float, and the tag is called location.

Testing

Unit tests cover line protocol escaping, integers above 2^53, casts and the rule checks. The end-to-end tests run against real servers: locally against 1.11.8 and 2.7.12, in CI against 1.8.10, 1.11.8, 2.7.12 and 2.9.1. They cover in-place changes over several windows with escaped keys, a float/integer/string conflict across shards (including a check that SELECT * misses values), rename with backup and restore, collisions, resume after a failed window, the refusals, and 24000 points in 40 series.

The real servers found two errors the unit tests did not show. The time range came from SELECT * ... LIMIT 1, which missed the last point when it had the other type of a conflicting field. And the backup wrote a whole float of a conflicting field as an integer, because InfluxQL converts between float and integer and cannot say which one a value was. The backup now writes such fields as floats.

Limits

Writers to the measurement should be stopped or switched to the new schema during the run; points written to the old measurement between copy and drop are lost. In-place changes copy the data twice through the HTTP API, and I have not measured that on production-sized data. Only one measurement per run, no partial rewrites with a WHERE filter, and no InfluxDB 3.

The code and the full test list are at github.com/Jwrede/influx-reshape.