references:
https://hostpresto.com/community/tutorials/how-to-install-influxdb-on-ubuntu-14-04/
official guide https://docs.influxdata.com/influxdb/v1.1/introduction/installation/
The setup:
- Raspberry Pi zero
- Raspbian Jessy image
- 64GB SD card
- wifi dongle
- P1 cable
InfluxDB is a time series database specifically built to store large amounts of timestamped data. We're going to use its retention policies and continuous queries to automatically aggregate and expire the ingested metering data in order to built an efficient Smart Metering dashboard.
Let's follow the official guide to install the latest version for Debian. Add the InfluxData repository, but be aware that you might need to run sudo apt-get install apt-transport-https first, if it's not already installed on your system:
curl -sL https://repos.influxdata.com/influxdb.key | sudo apt-key add -
source /etc/os-release
test $VERSION_ID = "7" && echo "deb https://repos.influxdata.com/debian wheezy stable" | sudo tee /etc/apt/sources.list.d/influxdb.list
test $VERSION_ID = "8" && echo "deb https://repos.influxdata.com/debian jessie stable" | sudo tee /etc/apt/sources.list.d/influxdb.list
Then, install and run the InfluxDB service
sudo apt-get update && sudo apt-get install influxdb
sudo systemctl start influxdb
Test if the service is listening to the network ports
$ sudo netstat -naptu | grep LISTEN | grep influxd
tcp6 0 0 :::8088 :::* LISTEN 1011/influxd
tcp6 0 0 :::8086 :::* LISTEN 1011/influxd
And query the repository for listed databases:
$ curl -G 'http://localhost:8086/query?pretty=true' --data-urlencode "q=SHOW DATABASES"
{
"results": [
{
"series": [
{
"name": "databases",
"columns": [
"name"
],
"values": [
[
"_internal"
],
[
"p1smartmeter"
]
]
}
]
}
]
}
in conf file: enable admin panel (v1.1 disables it by default) ---> TODO: enable?? --> reporting off
Don't forget to restart the influx service for the changes to take effect.
sudo systemctl restart influxdb
Start the CLI by: influx -precision rfc3339
should open the cli
Create the smartmeter db:
CREATE DATABASE "p1smartmeter"
Note that if you choose another name for the database, you need to provide it later on as argument when starting the python script.
Typing SHOW DATABASES should now return the following:
> SHOW DATABASES
name: databases
name
----
_internal
p1smartmeter
Now enter the database:
USE p1smartmeter
Retention policies (RP) and continuous queries (CQ; see https://docs.influxdata.com/influxdb/v1.1/guides/downsampling_and_retention/) are used to downsample (CQ) and persist (RP) the data for a specified amount of time.
Three retention policies are created for data storage: 30 days, 6 months and infinity:
CREATE RETENTION POLICY "30_days" ON "p1smartmeter" DURATION 30d REPLICATION 1 DEFAULT;
CREATE RETENTION POLICY "6_months" ON "p1smartmeter" DURATION 26w REPLICATION 1;
CREATE RETENTION POLICY "infinite" ON "p1smartmeter" DURATION INF REPLICATION 1;
See all retention policies:
> SHOW RETENTION POLICIES
name duration shardGroupDuration replicaN default
---- -------- ------------------ -------- -------
autogen 0s 168h0m0s 1 false
30_days 720h0m0s 24h0m0s 1 true
6_months 4368h0m0s 168h0m0s 1 false
infinite 0s 168h0m0s 1 false
Raw data from the smart meter is captured every 10 seconds. The raw data is kept for 30 days under the default "30_days" RP. Every 15 minutes, the following CQ "cq_smartmeter_hourly" downsamples the raw data into hourly data, which is stored for 6 months as enforced by the "6_months" RP.
CREATE CONTINUOUS QUERY "cq_smartmeter_hourly" ON "p1smartmeter" RESAMPLE EVERY 15m BEGIN SELECT min(*), max(*), spread(*) INTO "6_months"."smartmeter_hourly" FROM "30_days"."smartmeter" GROUP BY time(1h),* END
Every hour, another QC "cq_smartmeter_daily" downsamples the raw data into daily chunks and stores it for an infinite amount of time in the "infinite" RP.
CREATE CONTINUOUS QUERY "cq_smartmeter_daily" ON "p1smartmeter" RESAMPLE EVERY 1h BEGIN SELECT min(*), max(*), spread(*) INTO "infinite"."smartmeter_daily" FROM "30_days"."smartmeter" GROUP BY time(1d),* END
Finally, I created python script that calls a remote weather service to get outside temperatures. This is useful for relating gas consumption to the weather. The weather data is stored indefinitely in hourly blocks by the "cq_weather_daily" CQ:
CREATE CONTINUOUS QUERY "cq_weather_daily" ON "p1smartmeter" RESAMPLE EVERY 1h BEGIN SELECT min(*), max(*), mean(*) INTO "infinite"."weather_daily" FROM "30_days"."weather" GROUP BY time(1d),* END
Show all continuous queries:
> SHOW CONTINUOUS QUERIES
name: p1smartmeter
name query
---- -----
cq_smartmeter_hourly CREATE CONTINUOUS QUERY cq_smartmeter_hourly ON p1smartmeter RESAMPLE EVERY 15m BEGIN SELECT min(*), max(*), spread(*) INTO p1smartmeter."6_months".smartmeter_hourly FROM p1smartmeter."30_days".smartmeter GROUP BY time(1h), * END
cq_smartmeter_daily CREATE CONTINUOUS QUERY cq_smartmeter_daily ON p1smartmeter RESAMPLE EVERY 1h BEGIN SELECT min(*), max(*), spread(*) INTO p1smartmeter.infinite.smartmeter_daily FROM p1smartmeter."30_days".smartmeter GROUP BY time(1d), * END
cq_weather_daily CREATE CONTINUOUS QUERY cq_weather_daily ON p1smartmeter RESAMPLE EVERY 1h BEGIN SELECT min(*), max(*), mean(*) INTO p1smartmeter.infinite.weather_daily FROM p1smartmeter."30_days".weather GROUP BY time(1d), * END
type exit to quit.
http://influxdb-python.readthedocs.io/en/latest/include-readme.html
sudo apt-get install python-influxdb
sudo easy_install pip
sudo pip2 install --upgrade influxdb
Note that the installation through apt-get as suggested did not work for me. It throws an 404 error at the client.create_database('example'). See also
http://stackoverflow.com/questions/36846975/influxdb-python-404-page-not-found
check the config / run the script
Make sure you add any variables as argument that you chosse not to be default.
systemd file maken /etc/systemd/system/smartmeter.service
[Unit] Description=smartmeter
[Service] Type=simple User=hugo ExecStart=/usr/bin/python2 /usr/share/dsmr/smartmeter-influxdb.py -b 115200 -q -d /dev/ttyUSB0 Restart=always
[Install] WantedBy=multi-user.target
systemctl enable and start sudo systemctl enable smartmeter && sudo systemctl start smartmeter
check status sudo systemctl status smartmeter
set access rights on dialout group??
Grafana is ...
As there are no official Debian packages available, it makes the installation of Grafana on a Raspberry Pi not as straightforward as the InfluxDB installation. However, this repository provides unofficial deb packages specifically build for Raspberry Pi.
[CHECK] Be sure to check your Pi's architecture by running uname -a first. For a Rasperry Pi zero (which returns armv6) you can use the following commands. For other Pi versions, check the wiki for the correct repository.
sudo apt-get install adduser libfontconfig
curl -L https://dl.bintray.com/fg2it/deb-rpi-1b/main/g/grafana_4.0.2-1481228314_armhf.deb -o /tmp/grafana_4.0.2-1481228314_armhf.deb
sudo dpkg -i /tmp/grafana_4.0.2-1481228314_armhf.deb
Now enable and start the grafana server
sudo systemctl daemon-reload
sudo systemctl enable grafana-server && sudo systemctl start grafana-server
The dashboard is accessible through port 3000. The default credentials are admin/admin.
cd /etc/grafana sudo nano grafana.ini
(http://docs.grafana.org/installation/configuration/) edit [auth.anonymous] enabled Set to true to enable anonymous access. Defaults to false
In the gui set the default dashboards for the organization
sudo systemctl restart grafana-server.service
download dashboard here
menu: data sources Add data source
name: optionally check default type: InfluxDB url: http://localhost:8086
database: p1smartmeter user pass
And please switch to the light theme.
http://bigl.es/using-python-to-get-weather-data/
Remove again??
sudo apt-get remove python-pip
sudo easy_install pip
sudo pip2 install --upgrade pyowm
wget https://gist.githubusercontent.com/nl-hugo/acf9ceabb9a813d067484d9723ca3f77/raw/owm-influxdb.py
sudo vi var/spool/cron/crontabs/<user_name>
*/15 * * * * /usr/bin/python2 /usr/share/dsmr/owm-influxdb.py --api-key="[your key here]" --location=2751320 -v
SELECT * FROM "infinite".weather_daily ORDER BY time desc limit 10
http://nl-hugo.roughdraft.io/acf9ceabb9a813d067484d9723ca3f77
