2024-10-11

DuckDB-Tricks – Teil 2

Gábor Szárnyas

Überblick

Dieser Beitrag ist die neueste Folge der DuckDB-Tricks-Reihe, in der wir Ihnen knifflige SQL-Tricks in DuckDB zeigen. Hier eine Zusammenfassung dessen, was wir behandeln:

Operation	SQL-Anweisungen
Zeitstempel in CSV-Dateien reparieren	`regexp_replace()`{:.language-sql .highlight} und `strptime()`{:.language-sql .highlight}
Fehlende Werte auffüllen	`CROSS JOIN`{:.language-sql .highlight}, `LEFT JOIN`{:.language-sql .highlight} und `coalesce()`{:.language-sql .highlight}
Wiederholte Transformationsschritte	`CREATE OR REPLACE TABLE t AS ... FROM t ...`{:.language-sql .highlight}
Checksums für Spalten berechnen	`bit_xor(md5_number(COLUMNS(*)::VARCHAR))`{:.language-sql .highlight}
Ein Makro für die Checksum anlegen	`CREATE MACRO checksum(tbl) AS TABLE ...`{:.language-sql .highlight}

Datensatz

Als Beispieldatensatz nutzen wir schedule.csv, eine handgeschriebene CSV-Datei, die einen Konferenzplan kodiert. Der Plan enthält die Zeitslots, die Orte und die geplanten Events.

timeslot,location,event
2024-10-10 9am,room Mallard,Keynote
2024-10-10 10.30am,room Mallard,Customer stories
2024-10-10 10.30am,room Fusca,Deep dive 1
2024-10-10 12.30pm,main hall,Lunch
2024-10-10 2pm,room Fusca,Deep dive 2

Zeitstempel in CSV-Dateien reparieren

Wie üblich in echten Einsatzfällen ist die Eingabe-CSV unordentlich, mit unregelmäßigen Zeitstempeln wie 2024-10-10 9am. Laden wir die Datei schedule.csv deshalb mit DuckDBs CSV-Reader, erkennt der CSV-Sniffer die erste Spalte als VARCHAR-Feld:

CREATE TABLE schedule_raw AS
    SELECT * FROM 'https://duckdb.org/data/schedule.csv';

SELECT * FROM schedule_raw;

┌────────────────────┬──────────────┬──────────────────┐
│      timeslot      │   location   │      event       │
│      varchar       │   varchar    │     varchar      │
├────────────────────┼──────────────┼──────────────────┤
│ 2024-10-10 9am     │ room Mallard │ Keynote          │
│ 2024-10-10 10.30am │ room Mallard │ Customer stories │
│ 2024-10-10 10.30am │ room Fusca   │ Deep dive 1      │
│ 2024-10-10 12.30pm │ main hall    │ Lunch            │
│ 2024-10-10 2pm     │ room Fusca   │ Deep dive 2      │
└────────────────────┴──────────────┴──────────────────┘

Idealerweise hätte die Spalte timeslot den Typ TIMESTAMP, damit wir sie in späteren Abfragen als Zeitstempel behandeln können. Dafür können wir die gerade geladene Tabelle nutzen und die problematischen Einträge mit einer reguläre-Ausdruck-basierten Suchen-und-Ersetzen-Operation reparieren, die das Format auf hours.minutes gefolgt von am oder pm vereinheitlicht. Dann wandeln wir den String mit strptime und dem Format-Specifier %p, der den am/pm-Teil des Strings erfasst, in Zeitstempel um.

CREATE TABLE schedule_cleaned AS
    SELECT
        timeslot
            .regexp_replace(' (\d+)(am|pm)$', ' \1.00\2')
            .strptime('%Y-%m-%d %H.%M%p') AS timeslot,
        location,
        event
    FROM schedule_raw;

Beachten Sie, dass wir den Punktoperator für Function Chaining nutzen, um die Lesbarkeit zu verbessern. Zum Beispiel wird regexp_replace(string, pattern, replacement) als string.regexp_replace(pattern, replacement) formuliert. Das Ergebnis ist die folgende Tabelle:

┌─────────────────────┬──────────────┬──────────────────┐
│      timeslot       │   location   │      event       │
│      timestamp      │   varchar    │     varchar      │
├─────────────────────┼──────────────┼──────────────────┤
│ 2024-10-10 09:00:00 │ room Mallard │ Keynote          │
│ 2024-10-10 10:30:00 │ room Mallard │ Customer stories │
│ 2024-10-10 10:30:00 │ room Fusca   │ Deep dive 1      │
│ 2024-10-10 12:30:00 │ main hall    │ Lunch            │
│ 2024-10-10 14:00:00 │ room Fusca   │ Deep dive 2      │
└─────────────────────┴──────────────┴──────────────────┘

Fehlende Werte auffüllen

Als Nächstes wollen wir einen Plan ableiten, der das vollständige Bild enthält: jeder Zeitslot für jeden Ort soll seine Zeile in der Tabelle haben. Für die Zeitslot-Ort-Kombinationen, bei denen kein Event angegeben ist, wollen wir explizit einen String hinzufügen, der <empty> sagt.

Dafür legen wir zuerst eine Tabelle timeslot_location_combinations an, die alle möglichen Kombinationen mit einem CROSS JOIN enthält. Dann können wir die ursprüngliche Tabelle über die Kombinationen mit einem LEFT JOIN verbinden. Schließlich ersetzen wir NULL-Werte durch den String <empty> mit der Funktion coalesce.

Die Klausel CROSS JOIN ist gleichbedeutend damit, die Tabellen einfach in der FROM-Klausel aufzulisten, ohne Join-Bedingungen anzugeben. Indem wir CROSS JOIN explizit ausschreiben, kommunizieren wir, dass wir ein kartesisches Produkt berechnen wollen – eine teure Operation auf großen Tabellen, die in den meisten Einsatzfällen vermieden werden sollte.

CREATE TABLE timeslot_location_combinations AS
    SELECT timeslot, location
    FROM (SELECT DISTINCT timeslot FROM schedule_cleaned)
    CROSS JOIN (SELECT DISTINCT location FROM schedule_cleaned);

CREATE TABLE schedule_filled AS
    SELECT timeslot, location, coalesce(event, '<empty>') AS event
    FROM timeslot_location_combinations
    LEFT JOIN schedule_cleaned
        USING (timeslot, location)
    ORDER BY ALL;

SELECT * FROM schedule_filled;

┌─────────────────────┬──────────────┬──────────────────┐
│      timeslot       │   location   │      event       │
│      timestamp      │   varchar    │     varchar      │
├─────────────────────┼──────────────┼──────────────────┤
│ 2024-10-10 09:00:00 │ main hall    │ <empty>          │
│ 2024-10-10 09:00:00 │ room Fusca   │ <empty>          │
│ 2024-10-10 09:00:00 │ room Mallard │ Keynote          │
│ 2024-10-10 10:30:00 │ main hall    │ <empty>          │
│ 2024-10-10 10:30:00 │ room Fusca   │ Deep dive 1      │
│ 2024-10-10 10:30:00 │ room Mallard │ Customer stories │
│ 2024-10-10 12:30:00 │ main hall    │ Lunch            │
│ 2024-10-10 12:30:00 │ room Fusca   │ <empty>          │
│ 2024-10-10 12:30:00 │ room Mallard │ <empty>          │
│ 2024-10-10 14:00:00 │ main hall    │ <empty>          │
│ 2024-10-10 14:00:00 │ room Fusca   │ Deep dive 2      │
│ 2024-10-10 14:00:00 │ room Mallard │ <empty>          │
├─────────────────────┴──────────────┴──────────────────┤
│ 12 rows                                     3 columns │
└───────────────────────────────────────────────────────┘

Wir können alles auch in einer einzelnen Abfrage mit einer WITH-Klausel zusammenfassen:

WITH timeslot_location_combinations AS (
    SELECT timeslot, location
    FROM (SELECT DISTINCT timeslot FROM schedule_cleaned)
    CROSS JOIN (SELECT DISTINCT location FROM schedule_cleaned)
)
SELECT timeslot, location, coalesce(event, '<empty>') AS event
FROM timeslot_location_combinations
LEFT JOIN schedule_cleaned
    USING (timeslot, location)
ORDER BY ALL;

Wiederholte Datentransformationsschritte

Datenbereinigung und -transformation passieren üblicherweise als Folge von Transformationen, die die Daten in eine Form bringen, die für die spätere Analyse am besten passt. Diese Transformationen werden oft erledigt, indem neuere und neuere Tabellen mit CREATE TABLE ... AS SELECT-Anweisungen definiert werden.

In den Abschnitten oben haben wir zum Beispiel schedule_raw, schedule_cleaned und schedule_filled angelegt. Wollen wir aus irgendeinem Grund die Bereinigungsschritte für die Zeitstempel überspringen, müssen wir die Abfrage, die schedule_filled berechnet, so umformulieren, dass sie schedule_raw statt schedule_cleaned nutzt. Das kann mühsam und fehleranfällig sein und führt zu einer Menge ungenutzter temporärer Daten – Daten, die versehentlich von Abfragen aufgesammelt werden können, die wir vergessen haben zu aktualisieren!

Bei interaktiver Analyse ist es oft besser, denselben Tabellennamen zu nutzen, indem man CREATE OR REPLACE-Anweisungen ausführt:

CREATE OR REPLACE TABLE ⟨table_name⟩ AS
    ...
    FROM ⟨table_name⟩
    ...;

Mit diesem Trick können wir unsere Analyse wie folgt ausführen:

CREATE OR REPLACE TABLE schedule AS
    SELECT * FROM 'https://duckdb.org/data/schedule.csv';

CREATE OR REPLACE TABLE schedule AS
    SELECT
        timeslot
            .regexp_replace(' (\d+)(am|pm)$', ' \1.00\2')
            .strptime('%Y-%m-%d %H.%M%p') AS timeslot,
        location,
        event
    FROM schedule;

CREATE OR REPLACE TABLE schedule AS
    WITH timeslot_location_combinations AS (
        SELECT timeslot, location
        FROM (SELECT DISTINCT timeslot FROM schedule)
        CROSS JOIN (SELECT DISTINCT location FROM schedule)
    )
    SELECT timeslot, location, coalesce(event, '<empty>') AS event
    FROM timeslot_location_combinations
    LEFT JOIN schedule
        USING (timeslot, location)
    ORDER BY ALL;

SELECT * FROM schedule;

Mit diesem Ansatz können wir jeden Schritt überspringen und die Analyse fortsetzen, ohne den nächsten anzupassen.

Darüber hinaus kann unser Skript jetzt vom Anfang an neu ausgeführt werden, ohne explizit Tabellen zu löschen: Die CREATE OR REPLACE-Anweisungen ersetzen automatisch alle bestehenden Tabellen.

Checksums für Spalten berechnen

Es ist oft nützlich, eine Checksum für jede Spalte in einer Tabelle zu berechnen, z. B. um zu sehen, ob sich der Inhalt einer Spalte zwischen zwei Operationen geändert hat. Wir können eine Checksum für die Tabelle schedule wie folgt berechnen:

SELECT bit_xor(md5_number(COLUMNS(*)::VARCHAR))
FROM schedule;

Was passiert hier? Wir listen zuerst Spalten auf (COLUMNS(*)) und casten alle auf VARCHAR-Werte. Dann berechnen wir die numerischen MD5-Hashes mit der Funktion md5_number und aggregieren sie mit der Aggregatfunktion bit_xor. Das erzeugt einen einzelnen HUGEINT-(INT128-)Wert pro Spalte, der genutzt werden kann, um den Inhalt von Tabellen zu vergleichen.

Führen wir diese Abfrage im Skript oben aus, bekommen wir die folgenden Ergebnisse:

┌──────────────────────────────────────────┬────────────────────────────────────────┬─────────────────────────────────────────┐
│                 timeslot                 │                location                │                  event                  │
│                  int128                  │                 int128                 │                 int128                  │
├──────────────────────────────────────────┼────────────────────────────────────────┼─────────────────────────────────────────┤
│ -134063647976146309049043791223896883700 │ 85181227364560750048971459330392988815 │ -65014404565339851967879683214612768044 │
└──────────────────────────────────────────┴────────────────────────────────────────┴─────────────────────────────────────────┘

┌────────────────────────────────────────┬────────────────────────────────────────┬─────────────────────────────────────────┐
│                timeslot                │                location                │                  event                  │
│                 int128                 │                 int128                 │                 int128                  │
├────────────────────────────────────────┼────────────────────────────────────────┼─────────────────────────────────────────┤
│ 62901011016747318977469778517845645961 │ 85181227364560750048971459330392988815 │ -65014404565339851967879683214612768044 │
└────────────────────────────────────────┴────────────────────────────────────────┴─────────────────────────────────────────┘

┌──────────────────────────────────────────┬──────────┬──────────────────────────────────────────┐
│                 timeslot                 │ location │                  event                   │
│                  int128                  │  int128  │                  int128                  │
├──────────────────────────────────────────┼──────────┼──────────────────────────────────────────┤
│ -162418013182718436871288818115274808663 │        0 │ -135609337521255080720676586176293337793 │
└──────────────────────────────────────────┴──────────┴──────────────────────────────────────────┘

Ein Makro für die Checksum-Abfrage anlegen

Wir können die Checksum-Abfrage mit der neuen Funktion query_table in ein Tabellenmakro verwandeln:

CREATE MACRO checksum(table_name) AS TABLE
    SELECT bit_xor(md5_number(COLUMNS(*)::VARCHAR))
    FROM query_table(table_name);

So können wir es einfach auf der Tabelle schedule wie folgt aufrufen (und dabei auch DuckDBs FROM-first-Syntax nutzen):

FROM checksum('schedule');

┌──────────────────────────────────────────┬────────────────────────────────────────┬─────────────────────────────────────────┐
│                 timeslot                 │                location                │                  event                  │
│                  int128                  │                 int128                 │                 int128                  │
├──────────────────────────────────────────┼────────────────────────────────────────┼─────────────────────────────────────────┤
│ -134063647976146309049043791223896883700 │ 85181227364560750048971459330392988815 │ -65014404565339851967879683214612768044 │
└──────────────────────────────────────────┴────────────────────────────────────────┴─────────────────────────────────────────┘

Abschließende Gedanken

Das war’s für heute! Wir sind bald wieder da mit mehr DuckDB-Tricks und Fallstudien. Wenn Sie in der Zwischenzeit einen Trick haben, den Sie teilen möchten, teilen Sie ihn bitte mit dem DuckDB-Team auf unseren Social-Media-Seiten oder reichen Sie ihn auf der DuckDB-Snippets-Seite ein (betreut von unseren Freunden bei MotherDuck).