Adding data manually to InfluxDB by Line Protocol

1

I want to add data manually to my InfluxDB database that is connected to OpenHAB. I found that data can be added manually by using the InfluxDB Webservice and the InfluxDB Line Protocol (see: Write data | Get started with InfluxDB | InfluxDB OSS v2 Documentation).

I have the data available in an Excel Sheet and wants to transfer them into the right lines according to the Line Protocol. Hence, I am struggling how the respective Line Protocol command should look for OpenHAB.

Several questions I see needed to be answered:

  1. When I check the database entries for OpenHAB, the columns all are starting with an underscore, like “_measurement”, “_field”, “_value”, “_start”, “_stop”, “_time”, but “item” is without underscore. Are all the columns named with an underscore?
  2. The line protocol is requiring a timestamp. It is equivalent to “_time”?
  3. Which columns are of type “tag” and “field”? Will InfluxDB differentiate between them in case of an import besides the allowed data types?

Thanks in advanced of any support.

2

I don’t know InfluxDB to the level that will help answer your questions.

However, I do know that you can import data into OH through the REST API which works the same no matter what persistence database you are using. Have you considered using OH to add this data instead of working with the database itself?

Under Developer Tools → API Explorter → persistence → PUT /persistence/items/{itemname} you will find everything you need to know to create the curl command necessary to inject this data using OH.

If you do that then your three questions go away, OH handles all that stuff for you. But inserting the data is going to require a different approach from the InfluxDB Line Protocol (e.g. a script that issues HTTP calls). I can’t say which would be easiest.

GitHub - CWempe/rest2influxdb might help you get started.

3

I had to do the same once, and found two ways of doing so:

Via Terminal to insert single values:
Connect to the database:

docker exec -it influxdb bash
influx
show databases  
use <name of the database>
show measurements

Then:

insert <measurement name> value=2.0 1638301257000000000

Important: Use Unix timestamp (https://www.epochconverter.com/) and fill up to 19 digits (with 0) to fulfil nanosecond-requirement.

Via Chronograf to insert multiple values via .csv:
Go to „Explore“ → „Write Data“ → „Manual Entry“ or „.csv“
If „Manual data“ use the format

<measurement name> value=2.0 1671741051

Hope this helps.

4

Thanks for all the feedback. As I am using InfluxDB2 Chronograf is not available, but the frontend is also allowing a manual input by: Load Data > Buckets > autogen → “+Add Data” > Line Protocol.

I added the following line:

<measurement name> value=2.0 1638301257000000000

Hence in the frontend it shows different then the entries added by OpenHAB directly. The column “table” shows 0 instead of 1. So, it seems that a second table was created.
Further, the data added by OpenHAB are showing and additional column called “item” that is not available for the manual added line. I tried the add “item” as additional entry both as tag and key, but this does not made a change.

Any idea on that?

5

Thanks for the hint with the Development Tools. This worked out to add a correct line to the database. Hence, in Grafana it is not recognized and the way is very inconvenient for a bigger set of data. But, I will further investigate the Grafana Issue.

6

I am using v2 of influx DB, and if I remember correctly, Chronograf worked perfectly well.

Unfortunately not, as I used the two methods described above.