2024-11-29

DuckDB-Tricks – Teil 3

Andra Ionescu, Gábor Szárnyas

Überblick

Wir setzen unsere DuckDB-Tricks-Reihe mit einem dritten Teil fort und zeigen freundliche SQL-Features und Performance-Optimierungen.

Datensatz

Wir nutzen eine Teilmenge des Datensatzes niederländischer Bahndienste, der schon in einem Blogbeitrag früher in diesem Jahr vorkam. Diesmal nutzen wir die CSV-Dateien zwischen Januar und Oktober 2024: services-2024-01-to-10.zip. Wenn Sie den Beispielen folgen möchten, laden Sie den Datensatz herunter und entpacken Sie ihn, bevor Sie weiterlesen.

Spalten aus einer Tabelle ausschließen

Schauen wir uns zuerst die Daten in den CSV-Dateien an. Wir nehmen die CSV-Datei für August und inspizieren sie mit dem DESCRIBE-Statement.

DESCRIBE FROM 'services-2024-08.csv';

Das Ergebnis ist eine Tabelle mit den Spaltennamen und den Spaltentypen.

Jetzt nutzen wir SUMMARIZE, um ein paar Statistiken über die Spalten zu inspizieren.

SUMMARIZE FROM 'services-2024-08.csv';

Mit SUMMARIZE bekommen wir 10 Statistiken über unsere Daten (min, max, approx_unique usw.). Wollen wir ein paar davon aus dem Ergebnis entfernen, können wir den EXCLUDE-Modifikator nutzen. Um zum Beispiel min, max und die Quantile q25, q50, q75 auszuschließen, können wir den folgenden Befehl ausführen:

SELECT * EXCLUDE(min, max, q25, q50, q75)
FROM (SUMMARIZE FROM 'services-2024-08.csv');

Alternativ können wir den COLUMNS-Ausdruck mit dem Operator NOT SIMILAR TO nutzen. Das funktioniert mit einem regulären Ausdruck:

SELECT COLUMNS(lambda c: c NOT SIMILAR TO 'min|max|q.*')
FROM (SUMMARIZE FROM 'services-2024-08.csv');

In beiden Fällen enthält die resultierende Tabelle die 5 verbleibenden Statistikspalten:

Spalten mit Pattern Matching umbenennen

Beim Inspizieren der Spalten sehen wir, dass ihre Namen Leerzeichen und Semikolons (:) enthalten. Diese Sonderzeichen machen das Schreiben von Queries etwas mühsam, weil Spaltennamen in doppelte Anführungszeichen gesetzt werden müssen. Wir müssen zum Beispiel "Service:Company" in der folgenden Query schreiben:

SELECT DISTINCT "Service:Company" AS company,
FROM 'services-2024-08.csv'
ORDER BY company;

Schauen wir, wie wir die Spalten mit dem COLUMNS-Ausdruck umbenennen können. Um die Sonderzeichen (bis zu 2) zu ersetzen, können wir die folgende Query schreiben:

SELECT COLUMNS('(.*?)_*$') AS "\1"
FROM (
    SELECT COLUMNS('(\w*)\W*(\w*)\W*(\w*)') AS "\1_\2_\3"
    FROM 'services-2024-08.csv'
);

Setzen Sie DESCRIBE an den Anfang der Query, und wir sehen die umbenannten Spalten:

Zerlegen wir die Query, beginnend mit dem ersten COLUMNS-Ausdruck:

SELECT COLUMNS('(\w*)\W*(\w*)\W*(\w*)') AS "\1_\2_\3"

Hier nutzen wir einen regulären Ausdruck mit (\w*)-Gruppen, die 0…n Wortzeichen ([0-9A-Za-z_]) erfassen. Der Ausdruck \W* erfasst derweil 0…n Nicht-Wortzeichen ([^0-9A-Za-z_]). Im Alias-Teil referenzieren wir die Capture-Gruppe i mit \i, sodass "\1_\2_\3" bedeutet, dass wir nur die Wortzeichen behalten und ihre Gruppen mit Unterstrichen (_) trennen. Weil manche Spaltennamen durch ein Leerzeichen getrennte Wörter enthalten, andere nicht, bekommen wir nach diesem SELECT-Statement Spaltennamen mit einem nachgestellten Unterstrich (_), z. B. Service_Date_. Deshalb brauchen wir einen zusätzlichen Verarbeitungsschritt:

SELECT COLUMNS('(.*?)_*$') AS "\1"

Hier erfassen wir die Zeichengruppe ohne die nachgestellten Unterstriche und benennen die Spalten in \1 um, was die nachgestellten Unterstriche entfernt.

Um das Schreiben von Queries noch bequemer zu machen, können wir uns auf die Groß-/Kleinschreibungsunabhängigkeit von Identifikatoren verlassen und die Spaltennamen in Kleinschreibung abfragen:

SELECT DISTINCT service_company
FROM (
    SELECT COLUMNS('(.*?)_*$') AS "\1"
    FROM (
       SELECT COLUMNS('(\w*)\W*(\w*)\W*(\w*)') AS "\1_\2_\3"
       FROM 'services-2024-08.csv'
    )
)
ORDER BY service_company;

Der zurückgegebene Spaltenname behält seine ursprüngliche Groß-/Kleinschreibung, obwohl wir in der Query Kleinbuchstaben verwendet haben.

Laden mit Globbing

Jetzt, da wir die Spaltennamen vereinfachen können, laden wir alle 3 Monate Daten in eine Tabelle:

CREATE OR REPLACE TABLE services AS
    SELECT COLUMNS('(.*?)_*$') AS "\1"
    FROM (
        SELECT COLUMNS('(\w*)\W*(\w*)\W*(\w*)') AS "\1_\2_\3"
        FROM 'services-2024-*.csv'
    );

In der inneren FROM-Klausel nutzen wir die *-Glob-Syntax, um alle Dateien zu matchen. DuckDB erkennt automatisch, dass alle Dateien dasselbe Schema haben, und vereinigt sie. Wir haben jetzt eine Tabelle mit allen Daten von Januar bis Oktober, fast 20 Millionen Zeilen.

Parquet-Dateien umordnen

Angenommen, wir wollen die durchschnittliche Verspätung der Intercity-Direct-Züge der Nederlandse Spoorwegen (NS) analysieren, gemessen am Endziel der Zugverbindung. Wir können diese Analyse direkt auf den .csv-Dateien laufen lassen, das Fehlen von Metadaten (wie Schema und Min-Max-Indizes) begrenzt aber die Performance. Messen wir das im CLI-Client, indem wir den Timer einschalten:

.timer on

SELECT avg("Stop:Arrival delay")
FROM 'services-*.csv'
WHERE "Service:Company" = 'NS'
  AND "Service:Type" = 'Intercity direct'
  AND "Stop:Departure time" IS NULL;

Diese Query braucht etwa 1,8 Sekunden. Führen wir dieselbe Query auf der bereits in DuckDB geladenen Tabelle services aus, ist sie deutlich schneller:

SELECT avg(Stop_Arrival_delay)
FROM services
WHERE Service_Company = 'NS'
  AND Service_Type = 'Intercity direct'
  AND Stop_Departure_time IS NULL;

Die Laufzeit liegt bei etwa 35 Millisekunden.

Wollen wir ein externes Binärdateiformat nutzen, können wir die Datenbank auch in eine einzelne Parquet-Datei exportieren:

EXPORT DATABASE 'railway' (FORMAT parquet);

Wir können sie dann direkt so abfragen:

SELECT avg(Stop_Arrival_delay)
FROM 'railway/services.parquet'
WHERE Service_Company = 'NS'
  AND Service_Type = 'Intercity direct'
  AND Stop_Departure_time IS NULL;

Die Laufzeit für dieses Format liegt bei etwa 90 Millisekunden – etwas langsamer als DuckDBs eigenes Dateiformat, aber etwa 20× schneller als das Lesen der rohen CSV-Dateien.

Haben wir Vorwissen über die Felder, nach denen eine Query filtert, können wir die Parquet-Datei umordnen, um die Query-Performance zu verbessern.

COPY
(FROM 'railway/services.parquet' ORDER BY Service_Company, Service_Type)
TO 'railway/services.parquet';

Führen wir die Query erneut aus, ist sie merklich schneller und braucht nur 35 Millisekunden. Das verdankt sich dem partiellen Lesen, das die Zonemaps (Min-Max-Indizes) nutzt, um die zu scannende Datenmenge zu begrenzen. Das Umordnen der Datei erlaubt DuckDB, mehr Daten zu überspringen, und führt zu schnelleren Query-Zeiten.

Hive-Partitionierung

Um Queries noch weiter zu beschleunigen, können wir Hive-Partitionierung nutzen, um ein Verzeichnis-Layout auf der Platte zu erzeugen, das zu den Filtern in den Queries passt.

COPY services
TO 'services-parquet-hive'
(FORMAT parquet, PARTITION_BY (Service_Company, Service_Type));

Schauen wir ins Verzeichnis aus DuckDBs CLI mit dem .sh-Dot-Command:

.sh tree services-parquet-hive

services-parquet-hive
├── Service_Company=Arriva
│   ├── Service_Type=Extra%20trein
│   │   └── data_0.parquet
│   ├── Service_Type=Nachttrein
│   │   └── data_0.parquet
│   ├── Service_Type=Snelbus%20ipv%20trein
│   │   └── data_0.parquet
│   ├── Service_Type=Sneltrein
│   │   └── data_0.parquet
│   ├── Service_Type=Stopbus%20ipv%20trein
│   │   └── data_0.parquet
│   ├── Service_Type=Stoptrein
│   │   └── data_0.parquet
│   └── Service_Type=Taxibus%20ipv%20trein
│       └── data_0.parquet
├── Service_Company=Blauwnet
│   ├── Service_Type=Intercity
│   │   └── data_0.parquet
...

Wir können die Query jetzt auf dem Hive-partitionierten Datensatz ausführen, indem wir das Flag hive_partitioning = true übergeben:

SELECT avg(Stop_Arrival_delay)
FROM read_parquet(
         'services-parquet-hive/**/*.parquet',
         hive_partitioning = true
     )
WHERE Service_Company = 'NS'
  AND Service_Type = 'Intercity direct'
  AND Stop_Departure_time IS NULL;

Diese Query braucht jetzt etwa 20 Millisekunden, weil DuckDB die Verzeichnisstruktur nutzen kann, um die Reads noch weiter zu begrenzen. Und das Tolle an Hive-Partitionierung: Sie funktioniert sogar mit CSV-Dateien!

COPY services
TO 'services-csv-hive'
(FORMAT csv, PARTITION_BY (Service_Company, Service_Type));

SELECT avg(Stop_Arrival_delay)
FROM read_csv('services-csv-hive/**/*.csv', hive_partitioning = true)
WHERE Service_Company = 'NS'
  AND Service_Type = 'Intercity direct'
  AND Stop_Departure_time IS NULL;

Obwohl die CSV-Dateien keinerlei Metadaten haben, kann DuckDB sich auf die Verzeichnisstruktur verlassen, um die Scans auf die relevanten Verzeichnisse zu begrenzen, was zu Ausführungszeiten um 150 Millisekunden führt – mehr als 10× schneller als das Lesen aller CSV-Dateien.

Wenn Ihnen all diese Formate und Ergebnisse den Kopf verdrehen, keine Sorge. Wir haben Sie mit dieser Zusammenfassungstabelle abgedeckt:

Ach ja, und das Ergebnis haben wir vergessen. Die durchschnittliche Verspätung der Intercity-Direct-Züge beträgt 3 Minuten!

Schlussgedanken

Das war’s für Teil drei der DuckDB-Tricks. Wenn Sie einen Trick teilen möchten, teilen Sie ihn mit dem DuckDB-Team auf unseren Social-Media-Kanälen oder reichen Sie ihn auf der DuckDB-Snippets-Seite ein (betreut von unseren Freunden bei MotherDuck).