DSM2SQL — castermans.org

dsm2sql: data acquisite uit een slimme meter (Raspberry PI in C en MariaDB)

Begin juli 2016 werd er in mijn huis een zogenaamde "slimme meter" geïnstalleerd. Voor mij hét moment om de data uit deze meter te gaan opslaan en deze te analyseren. Want zeg nou zelf, als je dit doet:

ben je baas over je eigen data
kun je onbeperkt data verzamelen
kun je jaren terug kijken
maanden terug kijken
weken terug kijken
dagen terug kijken
doorsnedes en combinaties maken van jouw data
vragen beantwoorden waar aan je nu nog niet hebt gedacht

Analyseren kan best gebeuren door de data te visualiseren. Een voorbeeld daarvan zijn twee grafieken die het electriciteit- en gasverbruik in een week laten zien:

Een slimme meter is eigenlijk niet slim, met "slim" wordt slechts bedoeld dat hij op afstand uitgelezen kan worden door de netwerk beheerder. Vanaf het begin heeft men bij de specificatie van deze meters voorzien dat deze een voorziening moeten krijgen om ook lokaal uitgelezen te kunnen worden. Deze voorziening is de zogenoemde P1 poort die op elke slimme meter zit. De specificatie van poort P1 is open, dat betekent dat je de precieze werking van deze poort kunt lezen in een document. Vanaf het begin is de specificatie periodiek bijgesteld. Dit heeft geleid tot verschillende versies. Alle specificaties zijn te vinden op de website van Netbeheer Nederland. Mijn meter bleek te voldoen aan versie 4.2, beschreven in een document met de titel: "Dutch Smart Meter Requirements v4.2.2 Final P1 (14 maart 2014)". Recent, juli 2016, is versie 5 verschenen.

Bij het zoeken op Internet naar "slimme meter uitlezen" vond ik sites waar ik goede ideeën heb mogen lezen. De site van Ge Janssen wordt vaak geciteerd. De domoticx site refereert netjes aan de DSMR standaarden. En Tweakblogs laat enkele goed bruikbare grafieken zien.

Ik wil voortbouwen op bovenstaande sites, zal de slimme meter uitlezen met een Raspberry PI, maar zal de software realiseren in de programmeertaal C. Waarom C? Eigenlijk alleen maar omdat het leuk is en kan, maar wellicht ook omdat langs deze weg het uitlezen gerealiseerd kan worden door één programma op een Raspberry PI te installeren.

dsm2sql, is onder de GPL beschikbaar.

Ik ga er verder van uit de je behoorlijk vertrouwd bent met werken onder Linux op een Raspberry PI of op een ander op Raspbian (= Debian) gebaseerd systeem. Ik zal bijvoorbeeld schrijven "installeer dit programma" en ga er dan vanuit dat je weet hoe dat met "aptitude" kan gebeuren. Ook ga ik er vanuit dat je met een MariaDB-server kunt omgaan.

Kabel, P1 - GPIO

Om de P1 poort van de "slimme meter" met de Raspberry PI te verbinden is een kabel nodig. De P1 poort wordt bepaald door een RJ12 connector, op de Raspberry PI gebruik ik de GPIO interface. Hierdoor is een 3-aderige kabel nodig. Als alternatief kun de P1 poort op een USB connector van de Raspberry PI aansluiten, de kabel die hiervoor nodig is is te koop. In beide gevallen dient het data signaal geïnverteerd te worden. Bij de USB-kabel kan dit door middel van een software instelling.

In aanvulling op de inhoud van de andere websites geef ik hieronder meteen het complete aansluitschema van de benodigde kabel tussen P1 en de GPIO van de Raspberry PI. De inverter, opgebouwd met een BC547 transistor en drie weerstanden (2× 10kΩ, 1× 1kΩ), is op een GPIO Header for Raspberry Pi HAT - 2x20 Short Female Header gesoldeerd. Deze header kun je op de GPIO connector van de Raspberry PI steken om de elektrische verbindingen te maken.

Doordat pin 2, CTS, van de slimme meter met pin 2, "Power 5V" van de GPIO is verbonden, zal de slimme meter elke 10 seconden een telegram versturen. Om de verbinding te controleren, kun je bijvoorbeeld het programma "cu" gebruiken. Het GPIO UART device is /dev/ttyAMA0, gebruik je een USB kabel dan moet je een ander device kiezen, b.v. /dev/ttyUSBx! Let op: om van de RS232-poort te kunnen lezen moet de gebruiker (user) lid zijn van de groep (group) "dialout"

luc@nekum: ~$ cu -l /dev/ttyAMA0 -s 115200 --parity=none

In de shell zul je nu elke 10 seconden een zogenoemd telegram voorbij zien komen, hieronder een voorbeeld van een telegram uit mijn slimme meter, waarbij ik de inhoud van de regels met daarin 0-0:96.1.1 en 0-1:96.1.0 veranderd heb vanwege privacy redenen.

/KFM5KAIFA-METER
1-3:0.2.8(42)
0-0:1.0.0(160708202351S)
0-0:96.1.1(idvanmijnslimmeelektriciteitsmeter)
1-0:1.8.1(000008.218*kWh)
1-0:1.8.2(000005.165*kWh)
1-0:2.8.1(000002.047*kWh)
1-0:2.8.2(000063.437*kWh)
0-0:96.14.0(0002)
1-0:1.7.0(00.201*kW)
1-0:2.7.0(00.000*kW)
0-0:96.7.21(00001)
0-0:96.7.9(00001)
1-0:99.97.0(1)(0-0:96.7.19)(000101000011W)(2147483647*s)
1-0:32.32.0(00000)
1-0:52.32.0(00000)
1-0:72.32.0(00000)
1-0:32.36.0(00000)
1-0:52.36.0(00000)
1-0:72.36.0(00000)
0-0:96.13.1()
0-0:96.13.0()
1-0:31.7.0(000*A)
1-0:51.7.0(001*A)
1-0:71.7.0(000*A)
1-0:21.7.0(00.022*kW)
1-0:41.7.0(00.042*kW)
1-0:61.7.0(00.137*kW)
1-0:22.7.0(00.000*kW)
1-0:42.7.0(00.000*kW)
1-0:62.7.0(00.000*kW)
0-1:24.1.0(003)
0-1:96.1.0(idvanmijnslimmeagasmeter)
0-1:24.2.1(160708200000S)(00003.407*m3)
!6833

Telegram en MariaDB

De inhoud van het telegram is beschreven in de Dutch Smart Meter Requirements v4.2.2 Final P1 (14 maart 2014). We hoeven ons nu alleen af te vragen in welke waarden we geïnteresseerd zijn en waar we die gaan bewaren?

De laatste vraag wordt net als op veel andere sites beantwoord met MariaDB. Dit is een relationele database server die grote hoeveelheden data aan kan, maar die ook op een Raspberry PI kan draaien. In deze database server zullen we een database aanmaken en voorzien van een tabel waarin we de verbruiksgegevens van de slimme meter zullen opslaan.

Stel nu dat we het hele telegram (in ASCII formaat) elke 10 seconden zouden opslaan. Hoeveel groot zou onze database dan worden? Het telegram is 835 bytes lang, in een jaar zitten 365×24×60×6 perioden van 10 seconden. Per jaar bewaren we dan 365×24×60×6×835 = 2.633.25.000 = 2,6 GByte. Waarbij ik geen rekening gehouden heb met de grootte van bijvoorbeeld drijvende komma getallen in MariaDB. Deze hoeveelheid data is veel te groot en onnodig. Om de datahoeveelheid te beperken tot bijvoorbeeld een acceptabele 4,9 MByte/jaar slaan we slechts een deel van het telegram, 49 bytes, elke 5 minuten op. De grootte, formaten, details en de betekenis van de velden staan in onderstaande tabel, waarbij we de relaties tussen de inhoud van het telegram en de velden in de database transparant laten zien:

Dutch Smart Meter Requirements v4.2.2 Final P1 (14 maart 2014)		MariaDB tabel "emeter"
OBIS	Betekenis	Formaat	#	Veld
0-0:1.0.0	datumtijd + zomer (S)/wintertijd(W), Format: YYMMDDhhmmssX	datetime	8	e_dattijd
1-0:1.8.1	totale verbruik tarief 1 (kWh), Format: YYYYYY.YYY	float(9,3)	4	e_ver_t1
1-0:1.8.2	totale verbruik tarief 2 (kWh), Format: YYYYYY.YYY	float(9,3)	4	e_ver_t2
1-0:2.8.1	totale teruglevering tarief 1 (kWh); Format: YYYYYY.YYY	float(9,3)	4	e_ter_t1
1-0:2.8.2	totale teruglevering tarief 2 (kWh); Format: YYYYYY.YYY	float(9,3)	4	e_ter_t2
0:96.14.0	actueel tarief; 1= tarief 1, 2 = tarief 2	unsigned tinyint(1)	1	t
1-0:1.7.0	actueel verbruik (+P) (kW), Format: YY.YYY	float(5,3)	4	e_ver_mom
1-0:2.7.0	actuele teruglevering (-P) (kW), Format: YY.YYY	float(5,3)	4	e_ter_mom
0-1:24.2.1	datumtijd + zomer (S)/wintertijd(W) + gas verbruik (m3) Format: YYMMDDhhmmssX + YYYYY.YYY	datetime float(8,3)	8 4	g_dattijd g_ver
	ID (auto_increment)	unsigned int (11)	4	id
		TOTAAL Bytes	49

Om het telegram op te slaan is in MariaDB een database "energie" aangemaakt met daarin een tabel "emeter". Van deze laatste tabel staat hieronder de definitie. Naast de benodigde velden is een "id" toegevoegd, deze wordt gebruikt als de unieke aanduiding voor een rij in de tabel, de "primary key". Voor "id" is een "unsigned int", 32-bits veld, gekozen, waardoor deze tot 4.294.967.296 of te wel bijna 4,3 miljard kan lopen, genoeg voor 4.294.967.296 rijen / (12×24×365) rijen/jaar = 41000 jaar opslag.

De naaam van de database kan willekeurig gekozen worden, de naam van de tabel en de namen van de velden zijn vast gekozen. Wil je die veranderen dan zul je de C-code moeten veranderen.

Beschrijving dsm2sql

Het programma dsm2sql interfaced tussen de P1-poort en een MariaDB server.

Het gedraagt zich precies zoals het MariaDB-client programma, zoals b.v. MariaDB-client op Raspberry PI. Het programma leest een telegram dat op poort P1 verschijnen, zet deze om in een MariaDB query en stuurt de query naar een MariaDB-server. Deze laatste wordt geidentificeerd door het vijf-tal: host, port, username, password en database naam, zoals bij de MariaDB-client. Dit impliceert dat de MariaDB server NIET PERSÉ op de Raspberry PI hoeft te draaien. Hij mag ook op je NAS of zelfs extern draaien. Je moet natuurlijk wel schrijfrechten hebben.

Ik heb ervoor gekozen de MariaDB-server ook op de Raspberry PI te draaien, heb je toegang tot een MariaDB-server elders, b.v. op een NAS of "in de cloud" en wil je die gebruiken geef, dan de toegangsgegevens hiervan in de .my.cnf in. Dat is alles. Let op dat we er voor willen zorgen dat dsm2sql elke 5 minuten een query afgeeft, je MariaDB-server moet daarom continue toegankelijk zijn.

In vergelijking met de MariaDB-client heeft dsm2sql 2 extra opties: de rs232 poort, -c, en de debug, -d, vlag. Voor de GPIO poort op de Raspberry PI is de rs232 poort ttyAMA0. Als de debug-vlag is gezet zal dsm2sql precies één telegram ontvangen en hierna de resulterende query als uitvoer geven. Deze query wordt in dit geval niet aan de MariaDB server door gegeven, maar slechts naar stdout gestuurd.

luc@nekum:~ $ ./dsm2sql/dsm2sql -u account -c ttyAMA0 -pyrpassword energie
[2016-08-14 10:31:50] INSERT INTO emeter (e_dattijd, e_ver_t1, e_ver_t2,e_ter_t1,e_ter_t2,t, e_ver_mom,e_ter_mom,g_dattijd,g_ver) VALUES ("2016-08-14 10:31:47",000098.091,000029.806,000152.162,000396.454,0001,00.995,00.000,"2016-08-14 10:00:00",00024.439);
luc@nekum:~ $

Om dsm2sql elke 5 minuten te draaien gebruik ik "cron". Door het aanmaken van onderstaande regel, na ingeven van het commando "crontab -e" wordt "dsm2sql" elke 5 minuten aangeoepen, tevens worden meldingen bewaard in een log-bestand.

# m h dom mon dow command
*/5 * * * * /home/luc/dsm2sql/dsm2sql -u account -c ttyAMA0 -pyrpassword energie >> /home/luc/dsm2sql.log 2>&1

Door met de MariaDB-client de MariaDB-server te benaderen kun je de "emeter" tabel bekijken en controleren of dsm2sql zijn werk doet.

Het programma dsm2sql bestaat uit 3 delen: rs232.c, db.c en dm.c.

rs232.c beheert de seriële poorten, het opent en sluit deze en leest er van. Ik heb hiervoor gebruik gemaakt van het werk van Teunis van Beelen, dat onder GPL ter beschikking is gesteld. Let op: om van de RS232-poort te lezen moet de gebruiker van dsm2sql lid zijn van de groep "dialout"
dm.c leest via rs232.c de telegrammen in en zet deze om in een query. De in het telegram aanwezige CRC wordt gecontroleerd. Het vinden van het juiste algoritme heeft enig zoek werk gekost, het DSMR document is hier vaag over. Navraag bij netbeheernederland.nl leerde dat het het CRC-16 algoritme op de site van Lammert Bies is. Telegrammen met een ongeldige CRC worden als zodanig gemeld. De data in het telegram wordt in de bewerkingen niet gewijzigd. Het programma dm.c is zo ontworpen dat de ASCII waarden die in het telegram zitten, één op één terecht komen in de query. Pas de MariaDB-server zal de betreffende ASCII waarde omzetten in een "float". Dit principe houdt de data schoon, en gooit niks weg, op het laatste moment kun je zo beslissen of je de data wil veranderen of niet.
db.c bevat main(). Het leest de MariaDB opties in en zal uiteindelijk de query naar de MariaDB-server uitvoeren. Het is mogelijk dat dsm2sql zijn werk begint terwijl er een telegram uitgezonden wordt. Er zal dan een "half telegram" ontvangen worden. De CRC controle zal het bericht ongeldig doen verklaren, maar het is goed om zo snel mogelijk een nieuw telegram te ontvangen. Daarom vind je in de main() functie twee aanroepen van "DM_rcv_telegram()" achter elkaar.

Installatie op een Rasperry PI

T.o.v. Raspbian Stretch Lite image based on Debian Stretch
Version: June 2018
Release date: 2018-06-27
login met pi en raspberrypi

root@nekum:~# aptitude update
root@nekum:~# aptitude dist-upgrade
root@nekum:~# /etc/init.d/ssh start

Log files in RAM

Bron en uitleg.

root@nekum:~# vi /etc/fstab
root@nekum:~# reboot

Controle (mount)

root@nekum:~# mount | fgrep tmpfs
devtmpfs on /dev type devtmpfs (rw,relatime,size=470120k,nr_inodes=117530,mode=755)
tmpfs on /dev/shm type tmpfs (rw,nosuid,nodev)
tmpfs on /run type tmpfs (rw,nosuid,nodev,mode=755)
tmpfs on /run/lock type tmpfs (rw,nosuid,nodev,noexec,relatime,size=5120k)
tmpfs on /sys/fs/cgroup type tmpfs (ro,nosuid,nodev,noexec,mode=755)
tmpfs on /var/cache/atp/archives type tmpfs (rw,nosuid,nodev,noexec,relatime,size=102400k,mode=755)
tmpfs on /var/spool/cups type tmpfs (rw,noatime,size=102400k,mode=755)
tmpfs on /var/log type tmpfs (rw,noatime,size=20480k,mode=755)
tmpfs on /var/spool/cups/tmp type tmpfs (rw,noatime,mode=755)
tmpfs on /run/user/1001 type tmpfs (rw,nosuid,nodev,relatime,size=94944k,mode=700,uid=1001,gid=100)
root@nekum:~#

Installatie MariaDB

root@nekum:~# aptitude install mariadb-client mariadb-server libmariadbclient-dev

geef het root-wachtwoord op zodra dit gevraagd wordt.

root@nekum:~# systemctl enable mariadb.service
root@nekum:~# systemctl start mariadb.service

Control (MariaDB)

root@nekum:~# fgrep -i mariadb /var/log/syslog
Aug 14 15:56:11 nekum systemd[1]: Starting MariaDB database server...
Aug 14 15:56:18 nekum mysqld[579]: 2018-08-14 15:56:18 1989103616 [Note] /usr/sbin/mysqld (mysqld 10.1.23-MariaDB-9+deb9u1) starting as process 579 ...
Aug 14 15:56:21 nekum mysqld[579]: Version: '10.1.23-MariaDB-9+deb9u1' socket: '/var/run/mysqld/mysqld.sock' port: 3306 Raspbian 9.0
Aug 14 15:56:22 nekum systemd[1]: Started MariaDB database server.
root@nekum:~#

Maak de database aan

root@nekum:~# mariadb energie < createdb.sql

met als inhoud:

root@nekum:~ $ cat createdb.sql
CREATE DATABASE IF NOT EXISTS `energie`;
DROP TABLE IF EXISTS `emeter`;
CREATE TABLE `emeter` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`e_dattijd` datetime DEFAULT NULL,
`e_ver_t1` float(9,3) DEFAULT NULL,
`e_ver_t2` float(9,3) DEFAULT NULL,
`e_ter_t1` float(9,3) DEFAULT NULL,
`e_ter_t2` float(9,3) DEFAULT NULL,
`t` tinyint(1) unsigned DEFAULT NULL,
`e_ver_mom` float(5,3) DEFAULT NULL,
`e_ter_mom` float(5,3) DEFAULT NULL,
`g_dattijd` datetime DEFAULT NULL,
`g_ver` float(8,3) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4330 DEFAULT CHARSET=latin1;
root@nekum:~ $

Toegang tot de seriële poort:

Raspbian gaat ervan uit dat mensen via de seriële poort toegang willen hebben tot het systeem, de z.g. console. Voor onze toepassing is dit niet wenselijk en moeten we deze toegangsmogelijkheid uit zetten. (1) service stoppen en (2) voorkomen dat die nog een keer start

root@nekum:~ $ systemctl stop serial-getty@ttyAMA0.service
root@nekum:~ $ systemctl disable serial-getty@ttyAMA0.service

Voorkomen dat de kernel een seriel poort als console gebruikt, door "console=ttyAMA0,115200" weg te halen uit cmdline.txt

root@nekum:~/ $ cat /boot/cmdline.txt
dwc_otg.lpm_enable=0 console=tty1 root=/dev/mmcblk0p2 rootfstype=ext4 elevator=deadline fsc k.repair=yes rootwait

Testen!

We testen het programma, met de debug vlag zien we het telegram en de query:

luc@nekum:~/dsm2sql $ ./dsm2sql -u account -c ttyAMA0 -pyrpassword energie -d
/KFM5KAIFA-METER

1-3:0.2.8(42)
0-0:1.0.0(161106115629W)
0-0:96.1.1(idvanmijnslimmeelektriciteitsmeter)
1-0:1.8.1(000350.759*kWh)
1-0:1.8.2(000159.184*kWh)
1-0:2.8.1(000358.177*kWh)
1-0:2.8.2(001041.184*kWh)
0-0:96.14.0(0001)
1-0:1.7.0(00.000*kW)
1-0:2.7.0(00.998*kW)
0-0:96.7.21(00001)
0-0:96.7.9(00001)
1-0:99.97.0(1)(0-0:96.7.19)(000101000011W)(2147483647*s)
1-0:32.32.0(00000)
1-0:52.32.0(00000)
1-0:72.32.0(00000)
1-0:32.36.0(00000)
1-0:52.36.0(00000)
1-0:72.36.0(00000)
0-0:96.13.1()
0-0:96.13.0()
1-0:31.7.0(000*A)
1-0:51.7.0(005*A)
1-0:71.7.0(002*A)
1-0:21.7.0(00.018*kW)
1-0:41.7.0(00.000*kW)
1-0:61.7.0(00.220*kW)
1-0:22.7.0(00.000*kW)
1-0:42.7.0(01.258*kW)
1-0:62.7.0(00.000*kW)
0-1:24.1.0(003)
0-1:96.1.0(idvanmijnslimmeelektriciteitsmeter)
0-1:24.2.1(161106110000W)(00185.352*m3)
!7AC8
[2016-11-06 10:56:32] INSERT INTO emeter (e_dattijd, e_ver_t1,e_ver_t2,e_ter_t1,e_ter_t2,t, e_ver_mom,e_ter_mom,g_dattijd,g_ver) VALUES ("2016-11-06 11:56:29",000350.759,000159.184,000358.177,001041.184,0001,00.000,00.998,"2016-11-06 11:00:00",00185.352);