in Artikel, Business Intelligence

Testsystem mit DuckDB für unsere PV-Anlagen Daten

956 Views

Foto von Uriel SC auf Unsplash

Einleitung

Um nicht immer mit unserer produktiven DorisDB neue Sachen auszutesten, wollen wir uns ein kleines Testsystem erstellen. Dazu werden wir die uns schon bekannte DuckDB verwenden:

https://www.bi4you.org/artikel/duckdb-serverloses-in-memory-dbms/

Mit dieser sollten wir ein kleines unkompliziertes Testsystem aufbauen können.

 

Aktuelle DuckDB Version runterladen

Dazu verbinden wir uns per ssh auf unseren Raspberry Pi 5, legen einen neuen Ordner an und laden die aktuelle DuckDB Version herunter.

mkdir duckdb
cd duckdb
wget -c https://github.com/duckdb/duckdb/releases/download/v1.1.1/duckdb_cli-linux-aarch64.zip
unzip duckdb_cli-linux-aarch64.zip

Und schon ist unsere Installation startklar.

 

DuckDB DB-Datei anlegen

Eine Persistente Datenbank legen wir an, indem wir beim Starten einen Dateinamen angeben:

./duckdb pv_anlage.duckdb

 

Tabellen anlegen

In unserer neuen Datenbank legen wir jetzt das Schema bme280 und die benötigten Tabellen pv_anlage, pv_anlage_stromtarife und jahreszeiten analog zu unseren MariaDB Tabellen an:

Schema anlegen

create schema bme280;

Tabelle pv_anlage

CREATE TABLE bme280.pv_anlage
(
Datum_und_Uhrzeit DATE NULL,
Gesamt_Erzeugung DECIMAL(10,4) NULL,
Gesamt_Verbrauch DECIMAL(10,4) NULL,
Eigenverbrauch DECIMAL(10,4) NULL,
Energie_ins_Netz_eingespeist DECIMAL(10,4) NULL,
Energie_vom_Netz_bezogen DECIMAL(10,4) NULL,
PRIMARY KEY (Datum_und_Uhrzeit)
);

Tabelle pv_anlage_stromtarife

CREATE TABLE bme280.pv_anlage_stromtarife
(
Tarif VARCHAR(20) NULL,
Datum_Von DATE NULL,
Datum_Bis DATE NULL,
Preis DECIMAL(10,6) NULL,
EVN DECIMAL(10,6) NULL,
Netznutzungsentgelt DECIMAL(10,6) NULL,
Netzverlustentgelt DECIMAL(10,6) NULL,
Elektrizitaetsabgabe DECIMAL(10,6) NULL,
PRIMARY KEY (Tarif, Datum_Von, Datum_Bis)
);

Tabelle jahrezeiten

CREATE TABLE bme280.jahreszeiten
(
Jahreszeit VARCHAR(20) NULL,
Beginn DATE NULL,
Ende DATE NULL,
PRIMARY KEY (Jahreszeit, Beginn, Ende)
);

MariaDB Daten initial übernehmen

Mit dem Befehl ATTACH können wir unsere MariaDB in unsere neue Datenbank einhängen:

ATTACH 'host=192.168.42.10 user=bme280 password=bme280 port=3306 database=bme280' AS mysql_db (TYPE mysql_scanner, READ_ONLY);

Diesen ATTACH Befehl müssen wir jedes Mal neu eingeben, wenn wir den DuckDB Client beenden

 

Mit folgenden Befehlen können wir uns die Tabellen anzeigen lassen und somit testen, ob die Verbindung geklappt hat:

use mysql_db;
show tables;

Als Liste bekommen wir unsere ganzen Tabellen und Views angezeigt – unsere Verbindung klappt also.

Unsere Daten können wir jetzt einfach mit inserts übernehmen:

insert into bme280.pv_anlage select * from mysql_db.pv_anlage;
insert into bme280.pv_anlage_stromtarife select * from mysql_db.pv_anlage_stromtarife;
insert into bme280.jahreszeiten select * from mysql_db.jahreszeiten;

 

View anlegen

Da wir die PV-Daten übernommen haben, können wir in unserer neuen Datenbank die pv_anlage_view anlegen.

Bei der Syntax müssen wir nur das date_format durch ein strftime ersetzen, den Rest können wir 1:1 übernehmen:

create view bme280.pv_anlage_view as
(
select
Datum_und_Uhrzeit as Datum,
cast(YEAR(Datum_und_Uhrzeit) as varchar(4)) as Jahr,
cast(case MONTHNAME(Datum_und_Uhrzeit)
        when 'January' then 'Jänner'
        when 'February' then 'Februar'
        when 'March' then 'März'
        when 'April' then 'April'
        when 'May' then 'Mai'
        when 'June' then 'Juni'
        when 'July' then 'Juli'
        when 'August' then 'August'
        when 'September' then 'September'
        when 'October' then 'Oktober'
        when 'November' then 'November'
        when 'December' then 'Dezember'
        Else 'Fehler'
end as varchar(10)) as Monat,
cast(case MONTHNAME(Datum_und_Uhrzeit)
        when 'January' then '01-Jänner'
        when 'February' then '02-Februar'
        when 'March' then '03-März'
        when 'April' then '04-April'
        when 'May' then '05-Mai'
        when 'June' then '06-Juni'
        when 'July' then '07-Juli'
        when 'August' then '08-August'
        when 'September' then '09-September'
        when 'October' then '10-Oktober'
        when 'November' then '11-November'
        when 'December' then '12-Dezember'
        Else 'Fehler'
end as varchar(13)) as Monat_Sort,
-- Astronomische Jahreszeiten --
j.Jahreszeit as Jahreszeit_Astronomisch,
-- Meteorologische Jahreszeiten --
case
  when strftime(Datum_und_Uhrzeit,'%months-%days') between '03-01' and '05-31' then '01-Frühling'
  when strftime(Datum_und_Uhrzeit,'%months-%days') between '06-01' and '08-31' then '02-Sommer'
  when strftime(Datum_und_Uhrzeit,'%months-%days') between '09-01' and '11-30' then '03-Herbst'
  when strftime(Datum_und_Uhrzeit,'%months-%days') between '12-01' and '02-29' then '04-Winter'
end as Jahreszeit_Meteorologisch,
-- PV Daten
round(Gesamt_Erzeugung/1000,2) as Produktion,
round(Gesamt_Verbrauch/1000,2) as Gesamtverbrauch,
round(Eigenverbrauch/1000,2) as Eigenverbrauch,
round(Energie_ins_Netz_eingespeist/1000,2) as Netzeinspeisung,
round(Energie_vom_Netz_bezogen/1000,2) as Netzbezug,
round(Eigenverbrauch/Gesamt_Verbrauch*100,2) as Autarkie,
round(Eigenverbrauch/Gesamt_Erzeugung*100,2) as Eigenverbrauchsquote,
round(Energie_ins_Netz_eingespeist/Gesamt_Erzeugung*100,2) as Einspeisungsquote,
round(Gesamt_Erzeugung/8000,2) as kWh_pro_kWp,
round((Gesamt_Erzeugung/8000)/8.0*100,2) as Auslastung_pro_kWp,
--Stromkosten/ersparnisse
round(Eigenverbrauch/1000*b.Preis,6) as Stromkostenersparnis,
round(Energie_vom_Netz_bezogen/1000*b.Preis,6) as Strombezugskosten,
round(Energie_ins_Netz_eingespeist/1000*e.Preis,6) as Einspeisevergütung
from bme280.pv_anlage p
left outer join bme280.jahreszeiten j on p.Datum_und_Uhrzeit between j.Beginn and j.Ende
left outer join bme280.pv_anlage_stromtarife b on p.Datum_und_Uhrzeit between b.Datum_Von and b.Datum_Bis and b.Tarif='Bezugstarif'
left outer join bme280.pv_anlage_stromtarife e on p.Datum_und_Uhrzeit between e.Datum_Von and e.Datum_Bis and e.Tarif='Einspeisetarif'
);

Unsere View können wir mit einem select testen:

select * from bme280.pv_anlage_view;

Die Daten sehen gut aus, unsere View ist somit startklar.

Incrementelle Datenübernahme

Für die Übernahme der neueren Daten erstellen wir uns ein Skript, dass uns die Daten von der MariaDB in unsere neue Datenbank bei Bedarf kopiert:

sync_from_mariadb.sh
#!/bin/bash

./duckdb pv_anlage.duckdb -c "ATTACH 'host=192.168.42.10 user=bme280 password=bme280 port=3306 database=bme280' AS mysql_db (TYPE mysql_scanner, READ_ONLY); insert into bme280.pv_anlage (select m.* from mysql_db.pv_anlage m left outer join bme280.pv_anlage d on m.Datum_und_Uhrzeit=d.Datum_und_Uhrzeit where d.Datum_und_Uhrzeit is NULL);"

./duckdb pv_anlage.duckdb -c "ATTACH 'host=192.168.42.10 user=bme280 password=bme280 port=3306 database=bme280' AS mysql_db (TYPE mysql_scanner, READ_ONLY); insert into bme280.pv_anlage_stromtarife (select m.* from mysql_db.pv_anlage_stromtarife m left outer join bme280.pv_anlage_stromtarife d on m.Datum_Von=d.Datum_Von and m.Tarif=d.Tarif where m.Tarif='Bezugstarif' and d.Datum_Von is NULL);"

./duckdb pv_anlage.duckdb -c "ATTACH 'host=192.168.42.10 user=bme280 password=bme280 port=3306 database=bme280' AS mysql_db (TYPE mysql_scanner, READ_ONLY); insert into bme280.pv_anlage_stromtarife (select m.* from mysql_db.pv_anlage_stromtarife m left outer join bme280.pv_anlage_stromtarife d on m.Datum_Von=d.Datum_Von and m.Tarif=d.Tarif where m.Tarif='Einspeisetarif' and d.Datum_Von is NULL);"

DuckDB engine für Superset installieren

Um unsere DuckDB in Superset verwenden zu können, müssen wir zuerst die duckdb-engine installieren.

Dazu verbinden wir uns per ssh auf unseren Raspberry Pi 5 und aktivieren das Superset Environment mit

. superset/bin/activate

Danach können wir mit pip die Engine installieren

pip install duckdb-engine

Das Environment verlassen wir wieder mit

deactivate

Zum Schluss starten wir noch Superset neu

sudo systemctl restart superset

Datenbank in Superset verbinden

In Superset erstellen wir eine neue Datenbankverbindung, als Datenbank können wir jetzt DuckDB auswählen:

Als Name vergeben wir „bme280 (DuckDB)“, dann den Pfad zur unserer .duckdb Datei

Mit „Test Connection“ können wir noch überprüfen, ob wir uns verbinden können:

Im SQL Lab können wir jetzt unsere neue Datenbank auswählen und Abfragen darauf machen:

Fazit

Wir haben uns recht einfach und schnell ein kleines Testdatensystem aufgebaut und können unsere DuckDB nun in Superset für unsere Tests verwenden.

Sollten wir dann zufrieden mit einem Chart sein, können wir die Datasource auf unsere DorisDB ändern und in unsere produktiven Dashboards übernehmen.

Unsere Datenbank-Datei können wir zur Sicherung einfach wegkopieren – sollte etwas schief gehen, können wir sie einfach zurück kopieren und so unseren alten Stand wiederherstellen.

Jira für Nicht-Techniker – Teil 6: Workflow einrichten