Renaming Items and Groups, and updating persisted InfluxDB 1.x measurements accordingly

Tutorials & Examples Solutions
, ,
1

Sometimes you realise that Item and Group names make little sense, or that you want those Item and Group names to follow a strict naming convention.

A typical example is when you created many items from a Thing through the UI by bulk creating new items for selected channels. Sometimes the item names make little sense or you realise that an essential part of the channel name is not conveyed in the automatically generated Item name. This can result in item naming collisions (e.g., the OneCallAPI from the OpenWeatherMap binding doesn’t distinguish the current and forecast items, hence those names may collide. Or you have a ...Windspeed item, a Wind_Speed item and a ...WindSpeed item that you want to refactor into a ...WindSpeed naming pattern


If you have lots of items and groups in the item registry for which you set up InfluxDB persistence, and if you’re running openHAB 3 on a Unix-like OS, then the following instructions may be helpful.

These instructions may even lead to the creation of ‘integrated’ openHAB item and group refactorings
 in a future version of openHAB.

:warning: Important warning: Please back up your system before attempting any of the following operations. They can ruin your openHAB setup and cripple your persisted data if not done carefully.

With the introduction of openHAB 3, virtually all configurations are stored in JSON format. On openhabian, these configuration database files reside in /var/lib/openhab/jsondb/.

Instead of tediously renaming by hand all items and groups, you can apply search-and-replace directly to the JSON storage.

The basic idea is: we want to change the name of an Item (or Group) from oldName to newName.

:warning: Important warning: If you defined custom widgets that access the item registry by assembling item names (e.g., a shared item name prefix + specific suffix parts that specify the actual item, as in the OpenWeatherMap binding), then you may have to repeat your pattern search with smaller item name fragments. Failing to do so may (temporarily) break your custom widgets until you update the item and group names in the affected widgets.

:warning: Additional remarks:

  • The same is true for rules. Rules created from the MainUI are also stored in the JSON storage. Rules written in Python / Jython are stored in $OPENHAB_CONF/automation/jsr223/python/personal/. In case you rely on node-RED on openhabian, you’ll find your flows in /home/openhabian/.node-red/flows_openhab.json.
  • If you defined your persistence strategies in .persist files, then you may also want to add those to the locations (default: $OPENHAB_CONF/persistence/*.persist)
  • If you defined file-based sitemaps, then you may also want to add those to the locations (default: $OPENHAB_CONF/sitemaps/*.sitemap)

:warning: Important warning: it is wise to stop openHAB prior to renaming the items:
$ sudo service openhab stop

Step 1. Locate the JSON storage

$ cd $OPENHAB_USERDATA/jsondb
$ ls -l

The 2nd command will list all files in the JSON storage. The item registry is stored in org.openhab.core.items.Item.json, item metadata are stored in org.openhab.core.items.Metadata.json etc. You’ll notice that all your page layouts and custom widgets are also stored in the JSON DB. This means that you can effectively rename items throughout the JSON database when done properly.

Step 2. Log in as openhab user
Switch to the openhab user. This way, we don’t run into file ownership and file permission problems. These can be fixed later on (openhab-cli reset-ownership) but this adds avoidable wear to your flash memory based storage:

$ sudo -u openhab bash

Step 3. Identify all occurrences of oldName in the JSON storage

$ grep 'oldName' *json

This command yields a list of all occurrences of oldName in the JSON storage.

:warning: Important warning: please triple-check this list of matching results to ensure that you don’t accidentally have false positive hits (e.g., other items or groups containing “oldName”, or other parameters not related to oldName).

Step 4. Rename the items via the command line

$ for f in *json ; do sed -i 's/oldName/newName/g' "$f" ; done

(In case you want to add your Jython rules and your node-RED flows in the substitution, you may want to add these paths in steps 3 and 4.)

With this command, we will perform inline substitution of oldName with newName for all occurrences of the pattern oldName in each JSON file.

:warning: Note the use of single quotes enclosing the search-and-replace pattern ('s/oldName/newName/g') and double quotes for enclosing the resulting files matching the *json glob pattern ("$f").
On the UNIX command prompt, the command shell will not expand environment variables (preceded with a ‘$’ sign) in arguments enclosed with single quotes. The opposite is true when using double quotes.
You may however decide to use double quotes on the search-and-replace pattern, e.g. if you want to iterate over a list of patterns, but in that case maybe you would rather apply multiple inline substitutions at once. How you should do this, depends on your Unix flavour. On openhabian you can use:
$ sed -i -e 'pattern1' -e 'pattern2' -e 'pattern3' "$f"

Once all items and groups you wanted renamed have been processed in the JSON storage, you may restart openHAB. If you’re still logged in as the openhab user, then you can now log out. Then issue the following command:

$ sudo service openhab start

I strongly recommend running a log monitor (openhab-cli showlogs) in another terminal window to check if all updates in the JSON have been properly processed.

Step 5: Access the InfluxDB database instance

Now it is time to interact with the InfluxDB command-line tool. Log in to your InfluxDB instance with credentials with sufficient privileges (e.g., admin) on the database where openHAB persists its state. On my setup, this database is named openhab_db.

$ influx -username admin -password ''
password: 
Connected to http://localhost:8086 version 1.8.6
InfluxDB shell version: 1.8.6

Now you can interact with the InfluxDB instance. Let’s first select the database where openHAB persists its state:

> USE openhab_db
Using database openhab_db

By default, openHAB persists Item and Group states in InfluxDB measurements that have exactly the same name as the persisted item or group. The measurements are case-sensitive.

You can retrieve a list of all measurements with the following command:

> SHOW MEASUREMENTS

When you have many persisted items and groups, you may want to return only measurements matching a specific name pattern. Fortunately, InfluxDB supports a (limited) set of regular expression patterns. For instance:

> SHOW MEASUREMENTS WITH MEASUREMENT =~ /OneCall.*Icon/

This pattern search will match patterns containing OneCall followed by 0 or more unspecified characters, followed by Icon. Note that this pattern is case-sensitive.

Step 6: Rename the persisted measurements
We can safely merge all persisted states of oldName into the new newName measurement with the following command:

> SELECT * INTO newName FROM oldName GROUP BY *

A summary of the number of entries merged in the new measurement will be provided. If all went well, you can now drop the old measurement:

> DROP MEASUREMENT oldName

Step 7: exit the InfluxDB client
When all measurements relating to renamed items and groups have been processed, you can exit the InfluxDB client:

> exit

If you’ve been meticulous and with some :four_leaf_clover: you should now have a fully functional openHAB environment with renamed items and groups as desired.

Have fun!

2

Thanks for posting. This is a more detailed set of instructions compared to what I posted in OH 3 Tips and Tricks. I’ve a few elaborations to add for those who might not have the exact same setup as you.

Actually, all configurations made through the Karaf Console, the REST API, or the UI since OH 2.0 has been stored in JSON format in the JSONDB (except really early on when MapDB was used before JSONDB was adopted prior to the release of OH 2.0. This isn’t new in OH 3.

This is the location on any Linux where OH is installed via apt or yum or whatever. On most other platforms or ways to install OH (e.g. Docker, Windows, Mac, etc.) the path will be $OH_HOME/userdata/jsondb where $OH_HOME is the root folder where openHAB resides.

If OH isn’t installed you may have to use the full path unless you’ve manually created environment variables.

To avoid confusion I’d reword this to “Switch to the openhab user”. This really isn’t logging in and in fact you cannot log in as the openhab user at all. That’s part of the security behind running services as a separate user.

I like the use of sed for this. In my instructions I had the user open the files in VSCode and use find and replace. However, one advantage of using find and replace is that you can manually verify each and every change which might be a tad safer if the original grep returned more than just lines with oldName. But it’s also a whole lot more work.

Don’t forget the .persist files. In some cases users might have listed individual Items there. The same for .sitemap files. I think that covers everywhere an Item will appear directly be name.

The last thing I’d recommend is repeat all the steps one Item at a time and test that it works before continuing. That way if your luck doesn’t hold out you don’t have to redo everything again, just the most recent Item.

I general, manually editing the JSONDB is a risky thing to do and should only be considered when no other way is possible.

3

Great writing, thanks for that. One remark if i see it right this solution is working for influxDB 1.x in influxDB 2.x the interface is different

4

You’re correct. InfluxDB up to version 1.8 (which I use) only supports InfluxQL, which is similar to SQL. From InfluxDB version 2 onwards, the preferred querying language is Flux.

I edited the title accordingly.

5

Thank you for your remarks, additions and suggestions.

I updated my instructions accordingly.

PS: blame the autocorrect for automagically translating openhab into openhanded


2 Likes
6

I just encountered an interesting unexpected side effect


Apparently the InfluxDB measurement contains the item name as one of the columns (tags in Influx jargon).

When performing a query on persisted data, InfluxDB will return data from the properly renamed measurement. However
 this data can contain the old item’s name in the item column. and this is something that openHAB checks when performing historic data queries. And this fires errors in openhab.log


Alas, there’s no easy way to rename/edit tag values in InfluxDB 1.8 similar to a SQL UPDATE query. You can use SELECT INTO but I still need to figure out how to fix this.

Here’s an example of what I found in InfluxDB:

> SELECT mean(value) FROM Wx_OWM_Current_Temperature WHERE time > now() - 1d GROUP BY item
name: Wx_OWM_Current_Temperature
tags: item=OneCallAPIweatherandforecast_Current_Temperature
time                mean
----                ----
1624790831996251699 25.8325

name: Wx_OWM_Current_Temperature
tags: item=Wx_OWM_Current_Temperature
time                mean
----                ----
1624790831996251699 18.498584070796458
7

Makes sense. As there is no actual way to rename an OH Item apart from creating a new one, this shouldn’t be a problem normally.