I recently discovered the possibilities to produce beautiful graphs using Influx for persisting data and Grafana for the visual part by following the instructions in this excellent tutorial:
https://community.openhab.org/t/influxdb-grafana-persistence-and-graphing/13761
After using it a couple of weeks, I realized that it wasn’t possible to render graphs for presentation in my HABpanel widgets since I am using a Raspberry Pi for my OpenHAB installation. So, to overcome this limitation, but also to separate OpenHAB from other services, I decided to install both Influx and Grafana on a separate server. For this purpose I set up an Intel NUC with Ubuntu 20.04 and Docker with Docker compose with containers for Influx, Grafana and Grafana renderer.
The next question was how to move existing data from the existing (and live) Influx database to the new database. Backup/Restore is probably one way to go, but since I’m a total rookie on Influx, I wasn’t really sure on how to do this and a bigger (?) problem was that I didn’t want to have a longer ”gap” in my time series. In addition, I have a couple of years of weather data from a Davis weather station stored online that I would like to bring into the Influx database. After a lot of research, it seemed like Telegraf would be my best option. It is a server agent available from InfluxData allowing you to collect metrics from a number of sources and to output these metrics to a multitude of outputs. I decided to use csv-files as input to bring the data into Influx. These are the steps I did to make this happen.
I. The fist thing I did was to change the Influx database configuration file in my production OpenHAB environment (services/influxdb.cfg). Since I have the same database user, password and database name on the new server, this was just a matter of changing the url in the configuration file to point out the new server and save the file. This worked right away and OpenHAB started to populate the new database with data - exactly what I expected and hoped for!
II. The next step was to extract the existing data from the old database into csv-files. This operation is straight forward but you can start with checking out your measurements, amount of data etc. by executing commands/queries like this in the Influx command line interface:
use openhab_db
show measurements
select count(*) from dTemp
select * from dTemp limit 20
III. Generate the csv-file by executing the following command:
influx -format=csv -database=openhab_db -execute="select * from dTemp;" > dTemp.csv
The first part of the file dTemp.csv will look something like this:
name,time,value
dTemp,1606586400159000000,-0.89
dTemp,1606586514216000000,-0.94
dTemp,1606586556128000000,-0.89
dTemp,1606586641885000000,-0.94
dTemp,1606586684779000000,-0.89
dTemp,1606586727733000000,-0.94
dTemp,1606587413938000000,-1
dTemp,1606587456849000000,-0.94
dTemp,1606587628391000000,-1
…
Repeat this for all your measurements.
IV. Next step is to install and prepare Telegraf for the data import. It is driven by plug-ins and a configuration file, so after installation you have to generate a configuration file template that can be modified. Since I’m using docker I start a shell within the docker container for Telegraf:
docker exec -it telegraf bash
To generate the configuration file template with input-filter for files and output-filter for Influx, use:
telegraf -sample-config -input-filter file output-filter influxdb > my_config.conf
I did the following changes to the configuration file:
Section [agent]
- If you have large number om data points in your csv-file, you might want to increase
metric_batch_sizeandmetric_buffer_limit. I kept size at 1000 but increased limit to 55000 since my largest datafile contained ~53000 data points. - I recommend changing
# debug = false
to
debug = true
since it will provide useful information if something goes wrong during Import.
Section [[outputs.influxdb]]
-
uncomment/add a line with the ip for our Influx database server:
urls = [”http://192.168.1.123:8086”] -
uncomment/add lines with the name and credentials for your OpenHAB database:
database = ”openhab_db”
username = ”openhab”
password = ”MySupEr sEcrET paSSwoRD”
Section [[inputs.file]]
-
uncomment/add lines in this section to describe the file you want to import:
files = [”dTemp.csv"] data_format = "csv" cdv_header_row_count = 1 csv_skip_rows = 0 csv_skip_columns = 0 csv_comment = "#" csv_measurement_column = "name" csv_timestamp_column = "time" csv_timestamp_format = "unix_ns"
This was for the csv-file example earlier in this post. Documentation about the csv input format can be found here: telegraf/plugins/parsers/csv at release-1.17 · influxdata/telegraf · GitHub.
When doing my imports, I experienced some data type issues. The measurements in the database are of type float, but when exporting to csv a value of for example 25 was for some unknown reason not exported as 25.0 and when trying to import that line, I got an error since it was seen as an integer. This was easily fixed by adding the following in the Processor Plugins section that will convert the imported value to float:
[[processors.converter]]
[processors.converter.fields]
float = ["value"]
V. Run the import
telegraf -config my_config.conf
VI. Change the configuration file for the next file to import and repeat.
Some parts I did, like installation of docker, creating docker-compose files, setting up a samba share allowing for transferring of csv-files etc are intentionally left out. Please let me know If additional information is needed. Also, this is my first attempt trying to share some experience in this community. 