2024-05-31

Eisenbahnverkehr in den Niederlanden analysieren

Gábor Szárnyas

Einleitung

Die Niederlande, das Geburtsland von DuckDB, haben eine Fläche von etwa 42.000 km² und rund 18 Millionen Einwohner. Die hohe Dichte des Landes ist ein zentraler Faktor für sein umfangreiches Eisenbahnnetz, das aus 3.223 km Gleisen und 397 Bahnhöfen besteht.

Informationen zu Stationen und Verbindungen dieses Netzes gibt es als offene Datensätze. Diese hochwertigen Datensätze werden vom Team hinter der Anwendung Rijden de Treinen (Fahren die Züge?) gepflegt.

In diesem Post zeigen wir einige von DuckDBs analytischen Fähigkeiten am niederländischen Eisenbahn-Datensatz. Anders als die meisten unserer anderen Blogposts führt dieser kein neues Feature oder Release ein: Stattdessen zeigt er mehrere bestehende Features anhand einer einzigen Domäne. Einige der hier erklärten Abfragen sind in vereinfachter Form auf DuckDBs Startseite zu sehen.

Die Daten laden

Für unsere ersten Abfragen nutzen wir den Eisenbahnverbindungs-Datensatz von 2023. Laden Sie dazu die Datei services-2023.csv.gz (330 MB) herunter und laden Sie sie in DuckDB.

Starten Sie zuerst den DuckDB-Kommandozeilenclient auf einer persistenten Datenbank:

duckdb railway.db

Laden Sie dann die Datei services-2023.csv.gz in die Tabelle services.

CREATE TABLE services AS
    FROM 'services-2023.csv.gz';

Trotz der scheinbar einfachen Abfrage passiert hier ziemlich viel. Zerlegen wir die Abfrage:

• Erstens muss kein Schema für unsere Tabelle services explizit definiert werden, und es braucht auch keine Anweisung COPY ... FROM. DuckDB erkennt automatisch, dass 'services-2023.csv.gz' auf eine gzip-komprimierte CSV-Datei verweist, und ruft die Funktion read_csv auf, die die Datei dekomprimiert und ihr Schema mit dem CSV-Sniffer aus dem Inhalt ableitet.

• Zweitens nutzt die Abfrage DuckDBs FROM-first-Syntax, die es erlaubt, die Klausel SELECT * wegzulassen. Die SQL-Anweisung FROM 'services-2023.csv.gz'; ist also eine Kurzform für SELECT * FROM 'services-2023.csv.gz';.

• Drittens erzeugt die Abfrage eine Tabelle namens services und füllt sie mit dem Ergebnis des CSV-Readers. Das geschieht über eine Anweisung CREATE TABLE ... AS.

Mit DuckDB v0.10.3 dauert das Laden des Datensatzes auf einem M2 MacBook Pro etwa 5 Sekunden. Um die geladene Datenmenge zu prüfen, können wir folgende Abfrage ausführen, die die Zeilenzahl der Tabelle services schön formatiert:

SELECT format('{:,}', count(*)) AS num_services
FROM services;

Wir sehen, dass 2023 mehr als 21 Millionen Zugverbindungen in den Niederlanden gefahren sind.

Den meistfrequentierten Bahnhof pro Monat finden

Stellen wir zuerst eine einfache Frage: Welche waren die meistfrequentierten Bahnhöfe in den Niederlanden in den ersten 6 Monaten 2023?

Zuerst berechnen wir für jeden Monat die Zahl der Verbindungen, die durch jeden Bahnhof gehen. Dazu extrahieren wir den Monat aus dem Datum der Verbindung mit der Funktion month und führen eine Group-by-Aggregation mit count(*) aus:

SELECT
    month("Service:Date") AS month,
    "Stop:Station name" AS station,
    count(*) AS num_services
FROM services
GROUP BY month, station
LIMIT 5;

Beachten Sie, dass diese Abfrage eine häufige Redundanz in SQL zeigt: Wir listen die Namen nicht aggregierter Spalten sowohl in SELECT als auch in GROUP BY. Mit DuckDBs Feature GROUP BY ALL können wir das eliminieren. Gleichzeitig machen wir dieses Ergebnis mit einer Anweisung CREATE TABLE ... AS zu einer Zwischentabelle namens services_per_month:

CREATE TABLE services_per_month AS
    SELECT
        month("Service:Date") AS month,
        "Stop:Station name" AS station,
        count(*) AS num_services
    FROM services
    GROUP BY ALL;

Um die Frage zu beantworten, können wir die Aggregatfunktion arg_max(arg, val) nutzen, die die Spalte arg in der Zeile mit dem Maximalwert val zurückgibt. Wir filtern nach dem Monat und geben die Ergebnisse zurück:

SELECT
    month,
    arg_max(station, num_services) AS station,
    max(num_services) AS num_services
FROM services_per_month
WHERE month <= 6
GROUP BY ALL;

Vielleicht überraschend: In den meisten Monaten liegt der meistfrequentierte Bahnhof nicht in Amsterdam, sondern in der viertgrößten Stadt des Landes, Utrecht, dank seiner zentralen geografischen Lage.

Die Top-3 der meistfrequentierten Bahnhöfe für jeden Sommermonat

Ändern wir die Frage zu: Welche sind die Top-3 der meistfrequentierten Bahnhöfe für jeden Sommermonat? Die Funktion arg_max() hilft uns nur beim Top-1-Wert, reicht aber nicht für Top-k-Ergebnisse.

Mit einer Window-Funktion (OVER)

DuckDB hat umfangreiche Unterstützung für SQL-Features, einschließlich Window-Funktionen, und wir können die Funktion rank() nutzen, um Top-k-Werte zu finden. Zusätzlich nutzen wir make_date, um das Datum zu rekonstruieren, strftime, um es in den Monatsnamen zu verwandeln, und array_agg:

SELECT month, month_name, array_agg(station) AS top3_stations
FROM (
    SELECT
        month,
        strftime(make_date(2023, month, 1), '%B') AS month_name,
        rank() OVER
            (PARTITION BY month ORDER BY num_services DESC) AS rank,
        station,
        num_services
    FROM services_per_month
    WHERE month BETWEEN 6 AND 8
)
WHERE rank <= 3
GROUP BY ALL
ORDER BY month;

Das liefert folgendes Ergebnis:

Wir sehen, dass die Top 3 zwischen vier Bahnhöfen geteilt werden: Utrecht Centraal, Amsterdam Centraal, Schiphol Airport und Amsterdam Sloterdijk.

Mit der Funktion max_by(arg, val, n)

Ab DuckDB Version 1.1.0 können Sie eine Variante der Funktion max_by nutzen, die einen dritten Parameter n für die Zeilenzahl akzeptiert. Der resultierende Code ist kürzer und schneller als der mit einer Window-Funktion.

SELECT
    month,
    strftime(make_date(2023, month, 1), '%B') AS month_name,
    max_by(station, num_services, 3) AS stations,
FROM services_per_month
WHERE month BETWEEN 6 AND 8
GROUP BY ALL
ORDER BY month;

Parquet-Dateien direkt über HTTPS oder S3 abfragen

DuckDB unterstützt das Abfragen entfernter Dateien, einschließlich CSV und Parquet, über das HTTP(S)-Protokoll und die S3-API. Zum Beispiel können wir folgende Abfrage ausführen:

SELECT "Service:Date", "Stop:Station name"
FROM 'https://blobs.duckdb.org/nl-railway/services-2023.parquet'
LIMIT 3;

Sie liefert folgendes Ergebnis:

Mit der entfernten Parquet-Datei kann die Abfrage zur Beantwortung von Welche sind die Top-3 der meistfrequentierten Bahnhöfe für jeden Sommermonat? direkt auf einer entfernten Parquet-Datei laufen, ohne lokale Tabellen anzulegen. Dazu können wir die Tabelle services_per_month als Common Table Expression in der Klausel WITH definieren. Der Rest der Abfrage bleibt gleich:

WITH services_per_month AS (
    SELECT
        month("Service:Date") AS month,
        "Stop:Station name" AS station,
        count(*) AS num_services
    FROM 'https://blobs.duckdb.org/nl-railway/services-2023.parquet'
    GROUP BY ALL
)
SELECT month, month_name, array_agg(station) AS top3_stations
FROM (
    SELECT
        month,
        strftime(make_date(2023, month, 1), '%B') AS month_name,
        rank() OVER
            (PARTITION BY month ORDER BY num_services DESC) AS rank,
        station,
        num_services
    FROM services_per_month
    WHERE month BETWEEN 6 AND 8
)
WHERE rank <= 3
GROUP BY ALL
ORDER BY month;

Diese Abfrage liefert dasselbe Ergebnis wie die Abfrage oben und läuft (je nach Netzwerkgeschwindigkeit) in etwa 1–2 Sekunden durch. Diese Geschwindigkeit ist möglich, weil DuckDB die ganze Parquet-Datei nicht herunterladen muss, um die Abfrage auszuwerten: Bei einer Dateigröße von 309 MB nutzt sie nur etwa 20 MB Netzwerktraffic, ungefähr 6 % der Gesamtdateigröße.

Die Reduktion des Netzwerktraffics ist möglich durch partielles Lesen sowohl entlang der Spalten als auch der Zeilen der Daten. Erstens erlaubt Parquets spaltenorientiertes Layout dem Reader, nur die benötigten Spalten zuzugreifen. Zweitens erlauben die Zonemaps in den Metadaten der Parquet-Datei die Filter-Pushdown-Optimierung (z. B. holt der Reader nur Row Groups mit Daten in den Sommermonaten). Beide Optimierungen sind über HTTP Range Requests implementiert und sparen beträchtlichen Traffic und Zeit beim Abfragen entfernter Parquet-Dateien.

Größte Distanz zwischen Bahnhöfen in den Niederlanden

Beantworten wir folgende Frage: Welche zwei Bahnhöfe in den Niederlanden haben die größte Distanz zwischen sich bei der Fahrt über die Schiene? Dazu nutzen wir zwei Datensätze. Der erste, stations-2022-01.csv, enthält Informationen zu den Bahnhöfen (Name, Land usw.). Wir können diesen Datensatz einfach laden und so abfragen:

CREATE TABLE stations AS
    FROM 'https://blobs.duckdb.org/data/stations-2022-01.csv';

SELECT
    id,
    name_short,
    name_long,
    country,
    printf('%.2f', geo_lat) AS latitude,
    printf('%.2f', geo_lng) AS longitude
FROM stations
LIMIT 5;

Der zweite Datensatz, tariff-distances-2022-01.csv, enthält die Bahnhofsdistanzen. Die Distanzen sind als kürzeste Route im Eisenbahnnetz definiert und dienen der Tarifberechnung. Schauen wir in diese Datei:

head -n 9 tariff-distances-2022-01.csv | cut -d, -f1-9

Station,AC,AH,AHP,AHPR,AHZ,AKL,AKM,ALM
AC,XXX,82,83,85,90,71,188,32
AH,82,XXX,1,3,8,77,153,98
AHP,83,1,XXX,2,9,78,152,99
AHPR,85,3,2,XXX,11,80,150,101
AHZ,90,8,9,11,XXX,69,161,106
AKL,71,77,78,80,69,XXX,211,96
AKM,188,153,152,150,161,211,XXX,158
ALM,32,98,99,101,106,96,158,XXX

Wir sehen, dass die Distanzen als Matrix kodiert sind, deren Diagonaleinträge auf XXX gesetzt sind. Wie in der Beschreibung des Datensatzes erklärt, bedeutet dieser String, dass die beiden Stationen dieselbe Station sind. Laden wir die Werte einfach als XXX, nimmt der CSV-Reader an, dass alle Spalten den Typ VARCHAR statt numerischer Werte haben. Das lässt sich später aufräumen, aber es ist deutlich einfacher, das Problem von vornherein zu vermeiden. Dazu nutzen wir die Funktion read_csv und setzen den Parameter nullstr auf XXX:

CREATE TABLE distances AS
    FROM read_csv(
        'https://blobs.duckdb.org/data/tariff-distances-2022-01.csv',
        nullstr = 'XXX'
    );

Mit der Anweisung DESCRIBE können wir dann bestätigen, dass DuckDB die Spalte korrekt als BIGINT abgeleitet hat:

FROM (DESCRIBE distances)
LIMIT 5;

Um die ersten 9 Spalten zu zeigen, können wir folgende Abfrage mit den Spaltenindizes #1, #2 usw. in der SELECT-Anweisung ausführen:

SELECT #1, #2, #3, #4, #5, #6, #7, #8, #9
FROM distances
LIMIT 8;

Die Daten wurden korrekt geladen, aber das breite Tabellenformat ist für die weitere Verarbeitung etwas unhandlich: Um nach Stationspaaren zu fragen, müssen wir sie zuerst mit der Anweisung UNPIVOT in eine lange Tabelle verwandeln. Naiv würden wir etwa Folgendes schreiben:

CREATE TABLE distances_long AS
    UNPIVOT distances
    ON AC, AH, AHP, ...

Wir haben aber fast 400 Stationen, ihre Namen auszuschreiben wäre ziemlich mühsam. Zum Glück hat DuckDB einen Trick dafür: Der Ausdruck COLUMNS(*) listet alle Spalten und seine optionale Klausel EXCLUDE kann gegebene Spaltennamen aus der Liste entfernen. Daher listet der Ausdruck COLUMNS(* EXCLUDE station) alle Spaltennamen außer station – genau das, was wir für den Befehl UNPIVOT brauchen:

CREATE TABLE distances_long AS
    UNPIVOT distances
    ON COLUMNS (* EXCLUDE station)
    INTO NAME other_station VALUE distance;

Das ergibt folgende Tabelle:

SELECT station, other_station, distance
FROM distances_long
LIMIT 3;

Jetzt können wir die Tabelle distances_long an die Tabelle stations sowohl über Start- als auch Endstation joinen und dann auf Stationen in den Niederlanden filtern. Wir führen Symmetriebrechen ein (station < other_station), damit dasselbe Stationspaar nur einmal in der Ausgabe vorkommt. Schließlich wählen wir die Top-3-Ergebnisse:

SELECT
    s1.name_long AS station1,
    s2.name_long AS station2,
    distances_long.distance
FROM distances_long
JOIN stations s1 ON distances_long.station = s1.code
JOIN stations s2 ON distances_long.other_station = s2.code
WHERE s1.country = 'NL'
  AND s2.country = 'NL'
  AND station < other_station
ORDER BY distance DESC
LIMIT 3;

Die Ergebnisse zeigen, dass es Bahnhofspaare gibt, die mindestens 425 km voneinander entfernt sind – eine ganz schöne Distanz für so ein kleines Land!

Fazit

In diesem Post haben wir einige zentrale DuckDB-Features gezeigt, darunter automatische Formerkennung anhand von Dateinamen, automatisches Ableiten des Schemas von CSV-Dateien, direktes Parquet-Abfragen, Remote-Abfragen, Window-Funktionen, Unpivot, mehrere Friendly-SQL-Features (wie FROM-first, GROUP BY ALL und COLUMNS(*)) und so weiter. Die Kombination davon erlaubt es, Abfragen mit verschiedenen Dateiformaten (CSV, Parquet), Datenquellen (lokal, HTTPS, S3) und SQL-Features zu formulieren. Das hilft Nutzern, Abfragen schnell und effizient zu beantworten.

In der nächsten Folge schauen wir uns temporale Daten mit AsOf-Joins und Geodaten mit der DuckDB-spatial-Extension an.