in Artikel, Business Intelligence

Wetterdaten NG – Fortsetzung

1.6k Views

Nachdem wir uns im letzten Monat bereits mit

  • Raspberry Pi OS installieren und einrichten
  • Installation Hadoop Cluster und
  • Installation Doris Cluster

beschäftigt haben, wollen wir unsere Wetter- und Wärmepumpendaten nun in eine DorisDB schreiben und von dort mit Superset auswerten.

Synchronisieren der MariaDB Daten in unsere DorisDB

Die Wetter- und Wärmepumpendaten werden weiterhin in unsere MariaDB geschrieben, wir werden sie jetzt zusätzlich in unsere DorisDB kopieren und regelmäßig kopieren lassen.

Wir könnten die Daten auch direkt in unsere DorisDB schreiben lassen – durch das Kopieren haben wir die Daten aber lokal, müssen nicht über das Netzwerk abfragen und quasi ein Backup.

Um auf die MariaDB zugreifen zu können, können wir praktischerweise den MySQL J Connector verwenden und in Doris einbinden.

Mysql J Connector runterladen und in Doris einbinden

MariaDB Catalog in Doris einrichten

mysql -h pi5b -P 9030 -uroot
CREATE CATALOG asusn100_mysql PROPERTIES (
„type“=“jdbc“,
„user“=“bme280“,
„password“=“bme280“,
„jdbc_url“ = „jdbc:mysql://192.168.42.10:3306/bme280“,
„driver_url“ = „mysql-connector-j-8.3.0.jar“,
„driver_class“ = „com.mysql.cj.jdbc.Driver“
);

SHOW CATALOGS;

Doris Datenbank/User anlegen und MariaDB Zugriff testen

create database bme280;
CREATE USER ‚bme280‘ IDENTIFIED BY ‚bme280‘;
GRANT ALL ON bme280.* TO ‚bme280‘;
GRANT SELECT_PRIV ON asusn100_mysql.bme280.* TO ‚bme280‘;

select max(Messzeit) from asusn100_mysql.bme280.wohnzimmer;

Doris Tabellen anlegen und befüllen

Analog zu unseren MariaDB Tabellen legen wir die Tabellen in unserer DorisDB an und befüllen sie initial mit dem aktuellen Stand.

CREATE TABLE bme280.wohnzimmer (
Messzeit DATETIME,
Temperatur DECIMAL(6 , 2),
Druck DECIMAL(6 , 2) ,
Luftfeuchtigkeit DECIMAL(6 , 2 )
)
UNIQUE KEY (Messzeit)
DISTRIBUTED BY HASH(Messzeit) BUCKETS 1
PROPERTIES(„replication_num“ = „2“);

insert into bme280.wohnzimmer select * from asusn100_mysql.bme280.wohnzimmer;

CREATE TABLE bme280.terrasse (
Messzeit DATETIME,
Temperatur DECIMAL(6 , 2),
Druck DECIMAL(6 , 2) ,
Luftfeuchtigkeit DECIMAL(6 , 2 )
)
UNIQUE KEY (Messzeit)
DISTRIBUTED BY HASH(Messzeit) BUCKETS 1
PROPERTIES(„replication_num“ = „2“);

insert into bme280.terrasse select * from asusn100_mysql.bme280.terrasse;

CREATE TABLE bme280.eltern (
Messzeit DATETIME,
Temperatur DECIMAL(6 , 2),
Druck DECIMAL(6 , 2) ,
Luftfeuchtigkeit DECIMAL(6 , 2 )
)
UNIQUE KEY (Messzeit)
DISTRIBUTED BY HASH(Messzeit) BUCKETS 1
PROPERTIES(„replication_num“ = „2“);

insert into bme280.eltern select * from asusn100_mysql.bme280.eltern;

CREATE TABLE bme280.waermepumpe (
Ablesedatum DATE,
kwh DECIMAL(10, 2)
)
UNIQUE KEY (Ablesedatum)
DISTRIBUTED BY HASH(Ablesedatum) BUCKETS 1
PROPERTIES(„replication_num“ = „2“);

insert into bme280.waermepumpe select * from asusn100_mysql.bme280.waermepumpe;

MariaDB Daten inkrementell kopieren lassen

Hierfür legen wir einen crontab Eintrag an und lassen ihn alle 5 Minuten laufen
*/5 * * * * /home/hduser/bme280_copy_from_mysql.sh > /dev/null 2>&1

 

Scriptinhalt von bme280_copy_from_mysql.sh

#!/bin/bash

source /home/hduser/.profile

# check for running DorisFE processes

if [ $(jps |grep DorisFE |wc -l) -lt 1 ]

then

exit 0;

fi

sleep 5

mysql -h pi5b -P 9030 -ubme280 -pbme280 -e ‚insert into bme280.wohnzimmer select * from asusn100_mysql.bme280.wohnzimmer where Messzeit > (select max(Messzeit) from bme280.wohnzimmer);‘

mysql -h pi5b -P 9030 -ubme280 -pbme280 -e ‚insert into bme280.terrasse select * from asusn100_mysql.bme280.terrasse where Messzeit > (select max(Messzeit) from bme280.terrasse);‘

mysql -h pi5b -P 9030 -ubme280 -pbme280 -e ‚insert into bme280.eltern select * from asusn100_mysql.bme280.eltern where Messzeit > (select max(Messzeit) from bme280.eltern);‘

mysql -h pi5b -P 9030 -ubme280 -pbme280 -e ‚insert into bme280.waermepumpe select * from asusn100_mysql.bme280.waermepumpe where Ablesedatum > (select max(Ablesedatum) from bme280.waermepumpe);‘

 

Installation Superset

Benötigte Packages installieren

  • sudo apt-get install build-essential libssl-dev libffi-dev python3-dev python3-pip libsasl2-dev libldap2-dev
  • sudo apt-get install python3-venv

 

MariaDB installieren (für Superset Metastore) und einrichten

  • sudo apt-get install mariadb-server
  • sudo mysql_secure_installation
  • sudo mysql -uroot -p

mysql> GRANT ALL PRIVILEGES on *.* to ‚root’@’localhost‘ IDENTIFIED BY ’superset‘;

mysql> FLUSH PRIVILEGES;

mysql> exit;

  • mysql_tzinfo_to_sql /usr/share/zoneinfo | sudo mysql -uroot -psupserset mysql
  • sudo vi /etc/mysql/mariadb.conf.d/50-server.cnf

[mysqld]

default_time_zone = Europe/Vienna

bind-address = 192.168.42.33

  • sudo systemctl restart mysql

 

Superset Python Environment anlegen

  • cd
  • python3 -m venv superset
  • . superset/bin/activate
  • pip install –upgrade setuptools pip

 

Superset & zusätzliche Pakete mit pip installieren

  • pip install Pillow
  • pip install gevent
  • pip install apache-superset
  • deactivate

 

Superset MariaDB User anlegen und Berechtigungen vergeben

  • mysql -uroot -psuperset

CREATE DATABASE superset DEFAULT CHARACTER SET utf8;

CREATE USER ’superset’@localhost IDENTIFIED BY ’superset‘;

GRANT all on superset.* to ’superset’@localhost identified by ’superset‘;

flush privileges;

 

Superset Configuration anlegen

  • cd
  • mkdir -p .superset
  • vi .superset/superset_config.py

# Superset specific config

#———————————————————

ROW_LIMIT = 100000

SUPERSET_WORKERS = 2

SUPERSET_WEBSERVER_PORT = 18088

#———————————————————

#———————————————————

# Flask App Builder configuration

#———————————————————

# Your App secret key

SECRET_KEY = ’supersupersetsecret‘

# The SQLAlchemy connection string to your database backend

# This connection defines the path to the database that stores your

# superset metadata (slices, connections, tables, dashboards, …).

# Note that the connection information to connect to the datasources

# you want to explore are managed directly in the web UI

#SQLALCHEMY_DATABASE_URI = ’sqlite:////path/to/superset.db‘

SQLALCHEMY_DATABASE_URI = ‚mysql://superset:superset@localhost:3306/superset‘

#SQLLAB Timout auf 1h

SQLLAB_TIMEOUT = 36000

# Flask-WTF flag for CSRF

WTF_CSRF_ENABLED = False

# Add endpoints that need to be exempt from CSRF protection

WTF_CSRF_EXEMPT_LIST = []

# A CSRF token that expires in 1 year

WTF_CSRF_TIME_LIMIT = 60 * 60 * 24 * 365

# Set this API key to enable Mapbox visualizations

MAPBOX_API_KEY = “

# OAuth login broken with Content Security Policy

TALISMAN_ENABLED = False

 

Superset mit MariaDB Metastore einrichten

  • . superset/bin/activate
  • sudo apt-get install libmariadb-dev
  • pip install mysqlclient
  • deactivate

 

Systemd Service für Superset anlegen

  • sudo vi /etc/systemd/system/superset.service

[Unit]

Description=Superset

After=multi-user.target

 

[Service]

Type=simple

User=hduser

Environment=SUPERSET_CONFIG_PATH=/home/hduser/.superset/superset_config.py

Environment=PYTHONPATH=/home/hduser/superset

Environment=FLASK_APP=superset

WorkingDirectory = /home/hduser/superset

ExecStart=/home/hduser/superset/bin/gunicorn -w 4 -k gevent –worker-connections 20 –timeout 120 -b 0.0.0.0:18088 –limit-request-line 0 –limit-request-field_size 0 „superset.app:create_app()“

 

[Install]

WantedBy=default.target

 

Übernahme der vorhandenen Superset Dashboards + Anpassungen

Da wir denselben Superset SECRET_KEY in unserer superset_config.py verwenden, können wir die DB von unserem pi3b übernehmen und auf unserem pi5b einspielen

  • mariadb-dump -usuperset -psuperset -h192.168.42.43 superset > mysql.superset.sql
  • mysql -usuperset -psuperset -Dsuperset < mysql.superset.sql

 

Zusätzlich installieren wir noch den Doris Connector für Superset

  • . superset/bin/activate
  • pip install pydoris
  • deactivate

 

Danach können wir Superset auch schon starten

  • sudo systemctl start superset

 

In Superset können wir jetzt eine neue Datenquelle anlegen mit „Apache Doris“, oder bei der vorhandenen Datenquelle einfachen den Namen und „SQLALCHEMY URI“ umschreiben:

Generell müssen wir unsere Temperatur Dataset’s anpassen – bei unserer MariaDB hatten wir einfach ein „select * from“ gemacht und den Rest konnten wir dann einfach in Superset zusammenklicken.

Um jetzt keine Syntaxfehler zu erhalten, müssen wir unser SQL‘s anpassen und Metrics anlegen damit wir die Felder dann auch entsprechend benennen können:

Nach diesen kleinen Anpassungen, werden unsere Wetterdaten Charts wieder korrekt angezeigt, wenn wir unsere Metrics verwenden.

Bei den Wärmepumpendaten müssen wir unsere View #1 anpassen, weil es die MariaDB Funktion ROWNUM() in Doris nicht gibt – das nutzen wir gleich aus und lassen uns hier gleich die Heizsaison mitberechnen, zusätzliche werden wir die Monate auf Deutsch anzeigen lassen:

create view waermepumpe_view as
(
select hzs.id,hzs.Ablesedatum,hzs.kwh,
case hzs.Monat
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 Monat,
case Monat
when ‚September‘ then concat(right(Jahr,2),’/‘,right(Jahr+1,2))
when ‚October‘ then concat(right(Jahr,2),’/‘,right(Jahr+1,2))
when ‚November‘ then concat(right(Jahr,2),’/‘,right(Jahr+1,2))
when ‚December‘ then concat(right(Jahr,2),’/‘,right(Jahr+1,2))
else concat(right(Jahr-1,2),’/‘,right(Jahr,2))
end    as Heizsaison,
hzs.Jahr,hzs.kwh_monat,
hzs.kwh_monat-hzs2.kwh_monat as ‚Diff_Vorjahr‘,
concat(round((hzs.kwh_monat/hzs2.kwh_monat-1)*100,2),’%‘) as ‚Diff_Vorjahr_Prozent‘
from
(
select ROW_NUMBER() over(partition by left(all1.Ablesedatum,1) order by all1.Ablesedatum) as id,all1.Ablesedatum,all1.kwh,
date_format(DATE_SUB(all1.Ablesedatum, interval 1 day),’%M‘) as ‚Monat‘,
date_format(DATE_SUB(all1.Ablesedatum, interval 1 day),’%Y‘) as ‚Jahr‘,
all1.kwh-all2.kwh as kwh_monat
from
(
select ROW_NUMBER() over(partition by left(Ablesedatum,1) order by Ablesedatum) as id, Ablesedatum, kwh from bme280.waermepumpe where date_format(Ablesedatum,’%d‘) in (’01‘) order by Ablesedatum asc
) as all1
left outer join
(
select ROW_NUMBER() over(partition by left(Ablesedatum,1) order by Ablesedatum)+1 as id, Ablesedatum, kwh from bme280.waermepumpe where date_format(Ablesedatum,’%d‘) in (’01‘) order by Ablesedatum asc
) as all2
on all1.id=all2.id
) hzs
left outer join
(
select ROW_NUMBER() over(partition by left(all1.Ablesedatum,1) order by all1.Ablesedatum)+12 as id,all1.Ablesedatum,all1.kwh,all1.kwh-all2.kwh as kwh_monat
from
(
select ROW_NUMBER() over(partition by left(Ablesedatum,1) order by Ablesedatum) as id, Ablesedatum, kwh from bme280.waermepumpe where date_format(Ablesedatum,’%d‘) in (’01‘) order by Ablesedatum asc
) as all1
left outer join
(
select ROW_NUMBER() over(partition by left(Ablesedatum,1) order by Ablesedatum)+1 as id, Ablesedatum, kwh from bme280.waermepumpe where date_format(Ablesedatum,’%d‘) in (’01‘) order by Ablesedatum asc
) as all2
on all1.id=all2.id
order by all1.Ablesedatum asc
) hzs2
on hzs.id=hzs2.id
)
;

 

Beim Dataset für unser Chart WP_1 können wir dann die Heizsaisonberechnung weglassen und das SQL ein bisschen entschlacken:

select hzs.Heizsaison,

sep.kwh_Monat as ‚September‘,

okt.kwh_Monat as ‚Oktober‘,

nov.kwh_Monat as ‚November‘,

dez.kwh_Monat as ‚Dezember‘,

jan.kwh_Monat as ‚Jänner‘,

feb.kwh_Monat as ‚Februar‘,

mar.kwh_Monat as ‚März‘,

apr.kwh_Monat as ‚April‘,

mai.kwh_Monat as ‚Mai‘,

jun.kwh_Monat as ‚Juni‘,

jul.kwh_Monat as ‚Juli‘,

aug.kwh_Monat as ‚August‘

from

(select distinct Heizsaison from waermepumpe_view

order by 1

) as hzs

left outer join (select * from waermepumpe_view where Monat=’Jänner‘) jan on hzs.Heizsaison=jan.Heizsaison

left outer join (select * from waermepumpe_view where Monat=’Februar‘) feb on hzs.Heizsaison=feb.Heizsaison

left outer join (select * from waermepumpe_view where Monat=’März‘) mar on hzs.Heizsaison=mar.Heizsaison

left outer join (select * from waermepumpe_view where Monat=’April‘) apr on hzs.Heizsaison=apr.Heizsaison

left outer join (select * from waermepumpe_view where Monat=’Mai‘) mai on hzs.Heizsaison=mai.Heizsaison

left outer join (select * from waermepumpe_view where Monat=’Juni‘) jun on hzs.Heizsaison=jun.Heizsaison

left outer join (select * from waermepumpe_view where Monat=’Juli‘) jul on hzs.Heizsaison=jul.Heizsaison

left outer join (select * from waermepumpe_view where Monat=’August‘) aug on hzs.Heizsaison=aug.Heizsaison

left outer join (select * from waermepumpe_view where Monat=’September‘) sep on hzs.Heizsaison=sep.Heizsaison

left outer join (select * from waermepumpe_view where Monat=’Oktober‘) okt on hzs.Heizsaison=okt.Heizsaison

left outer join (select * from waermepumpe_view where Monat=’November‘) nov on hzs.Heizsaison=nov.Heizsaison

left outer join (select * from waermepumpe_view where Monat=’Dezember‘) dez on hzs.Heizsaison=dez.Heizsaison

where sep.kwh_Monat is not null
order by hzs.Heizsaison;

Zusätzlich legen wir uns die Metrics für die einzelnen Monate an und verwenden sie in unserem Chart.

Unser Dataset für unser Chart WP_2 können wir ebenfalls ein bisschen vereinfachen:

select monate.Monat

,hzs2122.kwh_Monat as ‚Heizsaison_21_22‘

,hzs2223.kwh_Monat as ‚Heizsaison_22_23‘

,hzs2324.kwh_Monat as ‚Heizsaison_23_24‘

from

(select distinct Monat as Monat from waermepumpe_view) as monate

left outer join

(select * from waermepumpe_view

) as hzs2122 on monate.Monat=hzs2122.Monat and hzs2122.Heizsaison=’21/22′

left outer join

(select * from waermepumpe_view

) as hzs2223 on monate.Monat=hzs2223.Monat and hzs2223.Heizsaison=’22/23′

left outer join

(select * from waermepumpe_view

) as hzs2324 on monate.Monat=hzs2324.Monat and hzs2324.Heizsaison=’23/24′

order by

case Monat

               when ‚September‘ then 1

               when ‚Oktober‘ then 2

               when ‚November‘ then 3

               when ‚Dezember‘ then 4

               when ‚Jänner‘ then 5

               when ‚Februar‘ then 6

               when ‚März‘ then 7

               when ‚April‘ then 8

               when ‚Mai‘ then 9

               when ‚Juni‘ then 10

               when ‚Juli‘ then 11

               when ‚August‘ then 12

else 99

end;

 

Auch hier legen wir wieder Metrics für die Heizsaisons an:

Fazit

Wir haben unser Vorhaben erfolgreich umgesetzt, unser Cluster läuft und kopiert regelmäßig die Daten von der MariaDB in unsere DorisDB.

Zusätzlich haben wir auch ein Hadoop-Grundgerüst bei Bedarf am Laufen und können hier auch zukünftig neue Tools ausprobieren.

Die Umstellung von MariaDB auf DorisDB war in Hinsicht auf unsere Superset Charts und Dashboards nicht ganz so einfach wie gedacht, allerdings hielt sich hier der Aufwand auch in Grenzen (sobald wir den Dreh raushatten, was wir wie anpassen müssen)

Für unsere Zwecke ist so ein Cluster natürlich übertrieben, aber das Gute ist, dass wir einfach die MicroSD Karten tauschen können und unseren Raspberry Pi 5 als SingleNode nur mit DorisDB & Superset betreiben können – die nötige Vorarbeit haben wir ja schon geleistet.

 

Wetterdaten NG

DuckDB – serverloses In-Memory-DBMS