Einführung in die Benutzung von TimescaleDB

Motivation

Ich wollte für die Arbeit lernen, wie man mit einer TimescaleDB arbeitet. Es gibt da einiges Material, aber leider nicht viel auf deutsch. Deshalb protokolliere ich meine Erfahrungen hier.

Was ist TimescaleDB?

TimescaleDB ist eine Datenbank für Zeitserien-Daten. Sie ist eine Erweiterung von PostgreSQL und unterstützt alle SQL-Operationen, die auch von PostgreSQL unterstützt werden. Zusätzlich werden notwendige und nützliche Funktionen für einfache Nutzung und analytische Flexibilität ergänzt.

Hypertables

Hypertables sind eine Abstraktionsschicht, welche dazu dient, die Komplexität automatischer Partitionierung der Daten vom Nutzer zu verbergen. Dadurch muss man sich nicht um die Datenaufteilung kümmern und kann sich auf die Nutzung der Daten konzentrieren. Chunks und Clustering werden transparent verwaltet.

Vorbedingungen

Diese Anleitung basiert auf der offiziellen Dokumentatoin. Die folgende Anleitung ist für Linux-Anwender geschrieben. Sie erfordert außerdem eine laufende Docker-Umgebung.

Datenbank-Setup mit Docker

Ich habe mir zum Starten und Stoppen ein einfaches Makefile unter $HOME/docker/images/Makefile gespeichert.

volumes = $$HOME/docker/volumes

# headings created with https://www.askapache.com/online-tools/figlet-ascii/

#   d888888P oo                                                dP          888888ba   888888ba  
#      88                                                      88          88    `8b  88    `8b 
#      88    dP 88d8b.d8b. .d8888b. .d8888b. .d8888b. .d8888b. 88 .d8888b. 88     88 a88aaaa8P' 
#      88    88 88'`88'`88 88ooood8 Y8ooooo. 88'  `"" 88'  `88 88 88ooood8 88     88  88   `8b. 
#      88    88 88  88  88 88.  ...       88 88.  ... 88.  .88 88 88.  ... 88    .8P  88    .88 
#      dP    dP dP  dP  dP `88888P' `88888P' `88888P' `88888P8 dP `88888P' 8888888P   88888888P 
#ooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo

timescale_build:
        mkdir -p $(volumes)/timescaldb/storage
        -docker network create timescaledb

timescale_start: timescale_build timescale_stop
        docker run -d --name timescaledb \
                --hostname=timescaledb \
                -p 5432:5432 \
                -v $(volumes)/timescaldb/storage:/var/lib/postgresql/data \
                -e POSTGRES_PASSWORD=CoronaXP \
                --net=timescaledb \
                timescale/timescaledb:latest-pg12

timescale_stop: grafana_stop
        -docker rm -f timescaledb 2>/dev/null

Im Makefile ist ein Passwort CoronaXP vergeben, dieses sollte nach Lust und Laune angepasst werden.

Wenn man ein Terminal aufmacht und mittels cd $HOME/docker/images in den images-Ordner wechselt, kann man die Datenbankanwendung einfach

starten: make timescale_start
stoppen: make timescale_stop

Datenbank einrichten

Nach der Docker-Installation und dem Start der Datenbankanwendung kann man nun einfach mit dem mitgelieferten Client eine Verbindung zur Datenbank herstellen:

docker exec -it timescaledb psql -U postgres

Jetzt sollte man die TimescaleDB-Shell sehen:

psql (12.2)
Type "help" for help.

postgres=#

Zeit eine Datenbank anzulegen: CREATE DATABASE tutorial; eingeben und Enter drücken.
Mittels \c tutorial aktiviert man diese Datenbank nun.

Erweitern der (einfachen PostgreSQL-)Datenbank um TimscaleDB-Funktionalität:

CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

Die Datenbank ist nun bereit, wir können uns mit \q ausloggen.

Hypertables einrichten

Eine Hypertable ist eine Erweiterung „normaler“ PostgreSQL-Tabellen um Funktionalität für hohen Datendurchsatz.

Zunächst verbinden wir uns mit der oben erzeugten Datenbank:

docker exec -it timescaledb psql -U postgres -d tutorial

Danach erzeugen wir eine erste Tabelle:

CREATE TABLE conditions (
  time        TIMESTAMPTZ       NOT NULL,
  location    TEXT              NOT NULL,
  temperature DOUBLE PRECISION  NULL,
  humidity    DOUBLE PRECISION  NULL
);

Diese wird mit dem Befehl SELECT CREATE_HYPERTABLE('conditions', 'time'); in eine Hypertable umgewandelt.

Hier können nun Werte eingetragen werden:

INSERT INTO conditions(time, location, temperature, humidity) VALUES (NOW(), 'office', 70.0, 50.0);

Mittels SELECT * FROM conditions ORDER BY time DESC LIMIT 100; kann man sich die erfassten Werte ansehen.

UPDATE- und DELETE-Kommandos funktionieren ebenfalls gemäß SQL-Standard.

Nutzung

Visualisierung

Zeitserien-Daten in TimescaleDB können einfach in Graphen dargestellt werden. TimescaleDB ist kompatibel mit Tools, die auch mit PostgreSQL arbeiten.

Grafana

Grafana ist ein quelloffenes Visualisierungs-Werkzeug. Es stammt aus der DevOps-Überwachungs-Welt, kann aber gut für Zeitserien-Daten verwendet werden.

Es kann ebenfalls einfach mittels Docker installiert werden. Hierzu ergänzt man obiges Makefile:

#    .88888.                    .8888b                            
#    d8'   `88                   88   "                            
#    88        88d888b. .d8888b. 88aaa  .d8888b. 88d888b. .d8888b. 
#    88   YP88 88'  `88 88'  `88 88     88'  `88 88'  `88 88'  `88 
#    Y8.   .88 88       88.  .88 88     88.  .88 88    88 88.  .88 
#     `88888'  dP       `88888P8 dP     `88888P8 dP    dP `88888P8 
#ooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo

grafana_build:
        mkdir -p $(volumes)/grafana
        mkdir -pm a+rwx $(volumes)/grafana/storage

grafana_start: grafana_build grafana_stop timescale_start
        docker run -d --name=grafana \
                -p 3000:3000 \
                -v $(volumes)/grafana/storage:/var/lib/grafana \
                -e GF_SECURITY_ADMIN_USER=admin \
                -e GF_SECURITY_ADMIN_PASSWORD=CoronaXP \
                --net=timescaledb \
                grafana/grafana

grafana_stop:
        -docker rm -f grafana 2>/dev/null

Wieder ist ein Passwort per Umgebungsvariable gesetzt. Hat man das Makefile derart ergänzt, kann man nun im Ordner $HOME/docker/images den Befehl make grafana_start absetzen.

Anschließend kann man Grafana im Browser unter http://localhost:3000 bewundern und sich mit admin und CoronaXP anmelden:

Grafana Dashboard

Grafana mit DB verbinden

Als nächstes sollte man Grafana mit der Datenbank verbinden. Dafür wurden beide Anwendungen ins gemeinsame timescaledb-Netzwerk eingebunden. Man kann also einfach auf Add data source klicken und im folgenden Dialog folgende Daten eingeben:

Feld	Wert
Name	beliebig, z.B. `TimescaleDB`
Host	`timescaledb:5432` wie im Makefile gesetzt
Database	`tutorial` wie im Makefile gesetzt
User	`postgres`
SSL Mode	`disable`
Connection limits	Vorgaben lassen
Version	`9.3` lassen
TimescaleDB	aktivieren

und dann Save & Test anklicken – es sollte ein grünes Feld Database Connection OK erscheinen.

Dashboard anlegen

Nun geht man wieder auf das Grafana-Symbol Grafana-Icon oben links und klickt auf New dashboard. Falls keine anderen Datenquellen an Grafana angebunden worden, wird automatisch eine Vorlage wie folgt erzeugt: