DuckDB

Datenbanken lassen sich in zwei Kategorien einteilen:

• OLTP: Online Transactional Processing zur Verwaltung von Anwendungsdaten
• OLAP: Online Analytical Processing zur Auswertung von Anwendungsdaten

Die meisten relationalen Datenbanken (PostgreSQL, Microsoft SQL Server, MySQL, SQLite) sind OLTP-Datenbanken. Sie werden als persistenter Datenspeicher von Anwendungen eingesetzt. Die Anwendung verändert den Zustand der Datenbank laufend in sogenannten Transaktionen (z.B. Mutation eines Kunden-Datensatzes).

OLAP-Datenbanken dienen zur Auswertung bestehender Datensätze. Dabei steht nicht das laufende Verändern des Datenbestandes im Vordergrund, sondern die Analyse der darin gespeicherten Informationen (z.B. statistische Auswertungen über eine Kundendatenbank).

Bei DuckDB handelt es sich um eine sehr schlanke und moderne OLAP-Datenbank, die wesentlich einsteigerfreundlicher ist als vergleichbare Lösungen (z.B. Apache Spark). DuckDB ist strenggenommen kein Dienst, sondern ein Werkzeug. Eine der Hauptstärken von DuckDB ist das Einlesen von Daten aus verschiedenen Quellen in verschiedenen Formaten ‒ eine Eigenschaft die gerade im Cloud Computing sehr nützlich ist.

Die umfassende Dokumentation enthält viele nützliche Informationen, z.B. zum SQL-Dialekt von DuckDB. Mit MotherDuck steht ein Cloud-Angebot für DuckDB zur Verfügung.

Installation

Auf der im Unterricht zur Verfügung gestellten Cloud-VM ist DuckDB bereits vorinstalliert und kann mit dem Befehl duckdb gestartet werden.

Für die lokale Ausführung kann der Befehl duckdb von der offiziellen Webseite bezogen werden: DuckDB Installation. Nach dem Entpacken legt man die Datei am besten unter einem Pfad ab, der von der Umgebungsvariable PATH referenziert wird, oder man erweitert die PATH-Umgebungsvariable entsprechend.

Übungen

In den folgenden Übungen soll DuckDB dazu verwendet werden, einen bestehenden strukturierten Datenbestand (eine CSV-Datei bzw. mehrere JSON-Dateien) in eine relationale Datenbank zu importieren (Ingestion) und dann zu analysieren.

Die erste Übung ist geführt, d.h. die Schritte müssen gemäss Anleitung ausgeführt werden. Dabei ist es wichtig, die Schritte auch nachvollziehen zu können. In der zweiten Übung müssen die jeweiligen Schritte angepasst auf einen anderen Datenbestand angewendet werden.

Übung 1 (geführt): Vorratslager

Die Datei foodstock.csv beschreibt den Bestand eines Lebensmittel-Vorratslagers. Die Datei kann auf der VM folgendermassen betrachtet werden:

cat foodstock.csv

Starten Sie DuckDB mit dem Befehl duckdb und dem Parameter foodstock-db, worauf der Prompt D erscheinen sollte:

duckdb foodstock-db
D 

Der Parameter foodstock-db sorgt dafür, dass die eingelesenen Daten als persistente Datenbank im entsprechenden Verzeichnis abgelegt werden.

Der describe-Befehl analysiert ein Datenbankobjekt (Tabelle, Ansicht) oder eine Datenquelle (Datei, URL) und gibt aus, in welcher Struktur die Daten vorliegen.

Geben Sie die Struktur der Datei foodstock.csv aus:

describe from "foodstock.csv";

Da das Schema bereits richtig erkannt worden ist, könnte man den Datenbestand direkt mit einem Befehl der Form create table … as select … einlesen. Da hier aber eine eindeutige Identifikation fehlt, soll stattdessen die Tabelle selber definiert werden:

create sequence food_id;
create table foodstock (
    id integer primary key default(nextval('food_id')),
    name varchar(100) not null,
    unit varchar(10) not null,
    price double not null,
    quantity double not null,
    category varchar(20),
    vegan boolean not null default(false)
);

Der erste Befehl (create sequence …) erzeugt eine Sequenz zum automatischen Durchnummerieren der Einträge.

Der zweite Befehl (create table …) definiert das Tabellenschema. Dabei wird zusätzlich zu den bestehenden Daten ein id-Feld definiert, dessen Werte automatisch von der zuvor definierten Sequenz food_id bezogen werden.

Die Tabelle ist nun bereit. Zur Kontrolle soll das Schema der Datei foodstock.csv mit dem Schema der Tabelle foodstock verglichen werden.

Aufgabe: Halte die Ausgabe der folgenden beiden Befehle fest und beschreibe die Unterschiede zwischen den beiden Ausgaben. Was haben diese Unterschiede zu bedeuten?

describe from "foodstock.csv";
describe foodstock;

Im nächsten Schritt sollen die Werte aus der Datei foodstock.csv in die Tabelle foodstock eingelesen werden:

insert into foodstock (name, unit, price, quantity, category, vegan) select * from "foodstock.csv";

Zur Kontrolle können die Daten aus der Tabelle ausgegeben werden:

select * from foodstock;

Diese sollte ungefähr folgendermassen aussehen (Ausgabe gekürzt):

Anhand dieses Datenbestandes sollen nun Auswertungen vorgenommen werden. Damit die Abfragen einfach gehalten werden können, empfiehlt sich der Gebrauch sogenannter Sichten oder Ansichten (engl. Views).

Eine View lässt sich wie eine Tabelle abfragen, ist aber nichts weiter als eine gespeicherte Abfrage. Im Gegensatz zu einer kopierten Tabelle sind Views darum immer aktuell: Wird die zugrundeliegende Tabelle angepasst, liefert die View auch die aktualisierten Daten zurück.

Um den Wert des Lebensmittel-Bestandes berechnen zu können, müssen die Preisangaben mit den Mengenangaben multipliziert werden. Hierzu soll eine View namens foods_worth erstellt werden:

create view foods_worth as (
    select id, name, unit, price, quantity, (price * quantity) as worth, category, vegan
    from foodstock
);

Das Feld worth ist ein berechnetes Feld: das Produkt aus Preis (price) und Menge (quantity). Bei jeder Abfrage der View wird dieser Wert neu berechnet.

Diese View kann nun wie eine Tabelle abgefragt werden:

select * from foods_worth;

Das Ergebnis sollte ungefähr folgendermassen aussehen (Ausgabe gekürzt):

Die Spalte worth beinhaltet nun den Wert des Warenbestands für jedes einzelne Lebensmittel.

Es lassen sich nun weitere Auswertungen anstellen, z.B. der Wert pro Kategorie aller nicht-veganen Produkte. Hierzu wird nach der Spalte category gruppiert und dabei die Spalte worth aufsummiert:

select sum(worth) as category_worth, category
from foods_worth
where vegan = false
group by category
having category_worth > 150
order by category_worth desc;

• Mit sum(worth) wird die Spalte worth aufsummiert. gruppiert.
• Mit where wird eine Filter-Bedingung auf die Originaldaten angewendet. Konkret werden mit where vegan = false werden nur Produkte berücksichtigt, die nicht vegan sind.
• Mit group by category werden die Einträge nach der Spalte category
• Mit having wird eine Filter-Bedingung auf die aggregierten (d.h. gruppierten bzw. summierten) Daten angewendet. Konkret werden mit having category_worth > 150 nur Kategorien zurückgeliefert, deren Gesamtwert 150.- übersteigt.
• Mit order by category_worth desc werden die Einträge absteigend (“descending”) nach ihrem Gesamtwert sortiert.

Ausgabe:

Schliesslich kann der Gesamtwert des Vorratslagers berechnet werden:

select sum(worth) as total from foods_worth;

Ausgabe:

Aufgabe: Überlege dir mindestens eine weitere Auswertung zu diesem Datenbestand. Beschreibe die Auswertung in eigenen Worten (was sie erreichen soll), formuliere den SQL-Befehl und halte die Ausgabe fest.

DuckDB kann mit dem Befehl .exit oder durch Betätigung der Tastenkombination [Ctrl]-[D] verlassen werden.

Übung 2 (selbständig): Fussball-Ligatabellen

Das Archiv leagues.zip beinhaltet fiktive Spielergebnisse verschiedener europäischer Fussball-Liegen. Es kann folgendermassen entpackt werden:

unzip leagues.zip

Die Struktur des daraus resultierenden Verzeichnises leagues kann mit dem Befehl tree betrachtet werden:

tree leagues

Das Verzeichnis enthält ein Unterverzeichnis pro Liga (bundesliga, la-liga usw.). Jedes Unterverzeichnis enthält eine Reihe von JSON-Dateien; eine pro Spieltag (day01.json, day02.json usw.).

Die Dateien sehen folgendermassen aus (z.B. bundesliga/day01.json, Auszug):

[
  {
    "homeTeam": "Bayern München",
    "awayTeam": "FSV Mainz 05",
    "homeGoals": 2,
    "awayGoals": 3
  },
  {
    "homeTeam": "RB Leipzig",
    "awayTeam": "Werder Bremen",
    "homeGoals": 1,
    "awayGoals": 2
  },
  …
]

Jede Datei enthält eine Reihe von Spielen mit einer Heim- und einer Auswärtsmannschaft sowie deren erzielten Toren. Der obenstehende Auszug bezeichnet folgende Spielergebnisse:

• Bayern München 2:3 FSV Mainz 05
• RB Leipzig 1:2 Werder Bremen

Aus diesen Speilergebnissen soll eine Ligatabelle berechnet werden. Bei der Bundesliga sieht diese folgendermassen aus:

Die Spalten haben folgende Bedeutung:

• #: Rang (absteigend sortiert nach Punkten und Tordifferenz)
• team: Mannschaft
• m: Anzahl Spiele (“matches”)
• p: Anzahl Punkte (3 pro Sieg, 1 pro Unentschieden, 0 pro Niederlage)
• w: Anzahl Siege (“wins”)
• t: Anzahl Unentschieden (“ties”)
• d: Anzahl Niederlagen (“defeats”)
• g+: Anzahl erzielter Tore
• g-: Anzahl kassierter Tore
• g=: Tordifferenz (erzielte minus kassierte Tore)

Zuerst soll DuckDB mit einer neuen Datenbank gestartet werden:

duckdb leagues-db

Das Schema der JSON-Dateien kann folgendermassen beschrieben werden:

describe from "leagues/bundesliga/day*.json";

Aufgabe: Erstellen Sie eine Sequenz namens bundesliga_match_id und eine Tabelle namens bundesliga_matches. Neben den vier Informationen aus den JSON-Dateien soll eine automatisch nummerierte ID aus der Sequenz bundesliga_match_id vergeben werden. Halten Sie die Befehle fest! Lesen Sie anschliessend die Daten der Bundesliga ein (insert into … from "leagues/bundesliga/day*.json).

Tipp: Bei Unklarheiten schauen Sie oben bei Übung 1 nach.

Die Tabelle sollte ungefähr folgendermassen aussehen (Auszug von select * from bundesliga_matches;):

Um die Spielergebnisse pro Mannschaft auswerten zu können, müssen diese Einträge auseinandergenommen werden, sodass für jedes Spiel zwei Einträge entstehen. Hierzu soll eine View namens bundesliga_per_team erstellt werden:

create view bundesliga_per_team as (
    select id as gameId, homeTeam as team, homeGoals as goals_scored,
    awayGoals as goals_conceded from bundesliga_matches
    union
    select id as gameId, awayTeam as team, awayGoals as goals_scored,
    homeGoals as goals_conceded from bundesliga_matches
);

Die View sollte ungefähr folgendermassen aussehen (Auszug von select * from bundesliga_per_team;):

Aufgabe: Erstellen Sie anhand dieser Informationen eine neue View namens bundesliga_result_day, welche für jeden Eintrag die folgenden Informationen als berechnete Felder enthält:

• Tordifferenz: goals_scored - goals_conceded
• Anzahl Punkte: 3 für einen Sieg, 1 für ein Unentschieden, 0 für eine Niederlage
  • siehe CASE-Statement
• Anzahl Siege: 1 falls es sich um einen Sieg handelt, 0 andernfalls
• Anzahl Unentschieden: 1 falls es sich um ein Unentschieden handelt, 0 andernfalls
• Anzahl Niederlagen: 1 falls es sich um eine Niederlage handelt, 0 andernfalls

Halten Sie den dazu verwendeten Befehl fest.

Die View sollte ungefähr folgendermassen aussehen (Auszug von select * from bundesliga_result_day;):

Die Einträge dieser View entsprechen nun einer Mini-Tabelle pro Spieltag und Mannschaft. Nun sollen die Einträge pro Mannschaft aggregiert werden, indem man die Summe aller Felder berechnet und sie nach der Spalte team gruppiert.

Aufgabe: Erstellen Sie eine View bundesliga_table, in welcher die Daten aus der View bundesliga_result_day wie beschrieben aggregiert werden. Die View sollte nach Punkten und Tordifferenz absteigend sortiert sein. Halten Sie den dazu verwendeten Befehl fest.

Die Tabelle sollte ungefähr folgendermassen aussehen (Auszug aus select * from bundesliga_table;):

Der Rang muss noch separat berechnet werden, was mit folgendem Befehl bewerkstelligt werden kann:

select row_number() over() as '#', * from bundesliga_table;

Das Vorgehen kann mit einer weiteren Liga und anhand der festgehaltenen Befehle wiederholt werden.