Historic data upload to InfluxDB

[gert.a...@gmail.com]

Hi

Is it possible to upload historic data from the Sqlite3 database into the InfluxDB server. I'm using the InfluxDB for Grafana

Thanks

Gert

[vince]

On Thursday, October 29, 2020 at 1:57:27 PM UTC-7, gert.a...@gmail.com wrote:

Is it possible to upload historic data from the Sqlite3 database into the InfluxDB server. I'm using the InfluxDB for Grafana

Anything is possible if you write the software :-)

FWIW, I fed influxdb pretty reliably by having weewx publish to MQTT, and using telegraf to subscribe to the topics and feed influxdb.  That way you don't need to write any software.  That covers from 'now' moving forward.

Now if you also want your old archive data (or summary data, or both?) then you have to find/write something.  A google search of "import sqlite3 to influxdb" looked pretty promising to me at a cursory read of a few of the first hits that come back from google.

[gert.a...@gmail.com]

Hi Vince

Thanks for the answer.

I already get data inserted into InfluxDB via Matthew's script and it works fine. I was interested in getting my old data visible in Grafana. 

Unfortunately, it is not possible to use Sqlite3 as a data source yet. It could be that there was already someone who had been through the same process with old data, so therefore my question.

I will search further and look for solutions.

BTW,  are there some advantages to using Telegraf / MQTT instead of  Matthew's plugin.

Gert

[Michael]

Hi Gert,

you can use a mysql database in weewx instead of a sqlite database. The existing sqlite database can also be converted to a mysql database.
Grafana can use a mysql database as data source.

Michael

[vince]

On Thursday, October 29, 2020 at 11:32:52 PM UTC-7, gert.a...@gmail.com wrote:

I already get data inserted into InfluxDB via Matthew's script and it works fine. I was interested in getting my old data visible in Grafana. 

Sure.  Be careful that you don't have influxdb set to auto-prune aged data.   I just keep 6 months worth in mine.

Unfortunately, it is not possible to use Sqlite3 as a data source yet. It could be that there was already someone who had been through the same process with old data, so therefore my question.

Sorry, not aware of one.  It wouldn't be too hard to write some kind of a one-time catchup program if you know the database format the weewx plugin you're using to get your realtime data into weewx.  You likely want to be consistent so your grafana dashboards can pick any time range you have data for.

Basically you'd do a select on your archive table and postprocess the output into the format influxdb wants to see for batch uploads of records.  Definitely doable with a few hours work I think.

(if you don't get there by Christmas, ping me via email and I could maybe give it a try that week when I'll have time available)

BTW,  are there some advantages to using Telegraf / MQTT instead of  Matthew's plugin.

It's not weewx-centric and it's more IoT mainstream, but no, not really in your case.  If what you have works, keep using it.

The downside of telegraf/MQTT is that I've found that sometimes it loses the subscription and data doesn't come in.  For my raspi IoT sensor things here and my former WeatherFlow gear,  I changed my code to write to influxdb directly, which I'm guessing is what Matthew does.

For my Davis VP2 weewx=>influxdb stuff I stuck with the MQTT extension and the telegraf path, mainly because it was stable.  Once I got it working locally, I figured "if it ain't broke don't fix it".

[Greg from Oz]

Hi Gert,

I use mysql and weewx and grafana can us mysql as a data source (as has been mentioned above by Michael)

It works well. I have used this was for over a year.

[gert.a...@gmail.com]

Hi

Thanls All

I'll try with mySql and see how it goes.

Gert