Grafana + MariaDB Mapping between Item number and Item name

(Raffaello Bonvino) #1

Hi all guys.
Do you know how I can easily map the Item Name (eg. Temperature_C) to the Item number (eg. Item2) into which MariaDB stores the Item values in order to use it in the SQL queries in grafana? Can you give me some examples? Sorry for the question, but i’m a noob in SQL queries. Thankyou in advance.

(Vincent Regaud) #2

MariaDB will have a main table called items where the item name and the table for that item are stored

(Raffaello Bonvino) #3

Yes I know, but I’m only able to do a SELCT * and manually translate ItemName to ItemId. Is ther an automatic way to do this? Tks.

(Udo Hartmann) #4

No. Of course you can use jdbc to use MariaDB as persistence service, and there is a parameter
tableUseRealItemNames in jdbc.cfg to get the item names as table names. You have to build a new database (but you can import old data manually to the correct table)

(Raffaello Bonvino) #6

I think I will migrate from mysql to influxdb as it seems working better with grafana. Is there a guide to follow for migrating from mysql to influxdb ? I did a little bit search and found this InfluxDB+Grafana persistence and graphing I’ll give it a try.

(Udo Hartmann) #7

Well, you have to

  • setup influxDB,
  • install the influxDB addon
  • copy mysql.persist to influxdb.persist (or at least the items which shall be persisted),
  • configure influxdb Persistence service via influxdb.cfg,
  • and of course configure grafana to use influxDB as an additional data source.
  • Finally, you have to adjust the graphs to use the new data source.

I doubt that it’s easily possible to export MySQL data to influxDB, so you will start with empty graphs :wink:

(Raffaello Bonvino) #8

Tks Udo, that’s what I did and it works very well. Just wanted to find a way to import my old data… maybe using this script https://github.com/CWempe/rest2influxdb . It seems to pull data using REST APIs and inject them into influxDB tables. I’m I right? Is there someone that has tried it? Another question: Is there a way to purge my old mysql databases?

(Udo Hartmann) #9

seems promising,.

Please ask @christoph_wempe if the script should work with your openHAB version.

(Christoph Wempe) #10

I don’t know if the script still works with newer versions of openhab.
But I think so.

When you want to inject the old data to MariaDB, you just need to modify this line.
But don’t ask me how this would look for MariaDB.