2024-08-19

DuckDB-Tricks – Teil 1

Gábor Szárnyas

In diesem Blogpost stellen wir fünf einfache DuckDB-Operationen vor, die wir für interaktive Einsatzfälle besonders nützlich gefunden haben. Die Operationen sind in der folgenden Tabelle zusammengefasst:

Den Beispieldatensatz anlegen

Wir beginnen mit einem Datensatz, den wir im Rest des Blogposts nutzen. Dazu definieren wir eine Tabelle, füllen sie mit Daten und exportieren sie in eine CSV-Datei.

CREATE TABLE example (s STRING, x DOUBLE);
INSERT INTO example VALUES ('foo', 10/9), ('bar', 50/7), ('qux', 9/4);
COPY example TO 'example.csv';

Moment, das ist viel zu ausführlich! DuckDBs Syntax hat mehrere SQL-Kurzformen, darunter die „Friendly SQL“-Klauseln. Hier kombinieren wir die VALUES-Klausel mit der FROM-first-Syntax, die die SELECT-Klausel optional macht. Damit können wir das Skript zum Anlegen der Daten auf etwa 60 % der ursprünglichen Größe komprimieren. Die neue Formulierung lässt die Schemadefinition weg und erzeugt die CSV mit einem einzigen Befehl:

COPY (FROM VALUES ('foo', 10/9), ('bar', 50/7), ('qux', 9/4) t(s, x))
TO 'example.csv';

Egal welches Skript wir ausführen, die resultierende CSV-Datei sieht so aus:

s,x
foo,1.1111111111111112
bar,7.142857142857143
qux,2.25

Weiter mit den Code-Snippets und ihren Erklärungen.

Gleitkommazahlen schön ausgeben

Beim Ausgeben einer Gleitkommazahl können die Nachkommastellen schwer zu lesen und zu vergleichen sein. Die folgende Abfrage liefert zum Beispiel drei Zahlen zwischen 1 und 8, aber ihre gedruckten Breiten sind wegen der Nachkommastellen sehr unterschiedlich.

SELECT x
FROM 'example.csv';

┌────────────────────┐
│         x          │
│       double       │
├────────────────────┤
│ 1.1111111111111112 │
│  7.142857142857143 │
│               2.25 │
└────────────────────┘

Indem wir eine Spalte auf ein DECIMAL mit fester Anzahl Nachkommastellen casten, können wir sie so schön ausgeben:

SELECT x::DECIMAL(15, 3) AS x
FROM 'example.csv';

┌───────────────┐
│       x       │
│ decimal(15,3) │
├───────────────┤
│         1.111 │
│         7.143 │
│         2.250 │
└───────────────┘

Eine typische Alternative ist die Funktion printf oder format, z. B.:

SELECT printf('%.3f', x)
FROM 'example.csv';

Diese Ansätze verlangen aber eine Formatierungszeichenkette, die man leicht vergisst. Schlimmer noch: Die obige Anweisung liefert String-Werte, was nachfolgende Operationen (z. B. Sortieren) erschwert. Solange die volle Präzision der Gleitkommazahlen kein Anliegen ist, sollte das Casten auf DECIMAL-Werte für die meisten Einsatzfälle die bevorzugte Lösung sein.

Das Schema einer Tabelle kopieren

Um das Schema einer Tabelle ohne ihre Daten zu kopieren, können wir LIMIT 0 nutzen.

CREATE TABLE example AS
    FROM 'example.csv';
CREATE TABLE tbl AS
    FROM example
    LIMIT 0;

Das ergibt eine leere Tabelle mit demselben Schema wie die Quelltabelle:

DESCRIBE tbl;

┌─────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐
│ column_name │ column_type │  null   │   key   │ default │  extra  │
│   varchar   │   varchar   │ varchar │ varchar │ varchar │ varchar │
├─────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤
│ s           │ VARCHAR     │ YES     │         │         │         │
│ x           │ DOUBLE      │ YES     │         │         │         │
└─────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┘

Alternativ können wir im CLI-Client den Dot-Befehl .schema ausführen:

.schema

Das liefert das Schema der Tabelle.

CREATE TABLE example (s VARCHAR, x DOUBLE);

Nach dem Ändern des Tabellennamens (z. B. example zu tbl) kann diese Abfrage eine neue Tabelle mit demselben Schema anlegen.

Daten mischen

Manchmal müssen wir durch Mischen etwas Entropie in die Reihenfolge der Daten bringen. Um nicht-deterministisch zu mischen, können wir einfach nach einem Zufallswert der Funktion random() sortieren:

FROM 'example.csv' ORDER BY random();

Deterministisches Mischen ist etwas kniffliger. Dazu können wir nach dem Hash der Pseudospalte rowid sortieren. Beachten Sie, dass diese Spalte nur in physischen Tabellen verfügbar ist; wir müssen die CSV also zuerst in eine Tabelle laden und dann mischen:

CREATE OR REPLACE TABLE example AS FROM 'example.csv';
FROM example ORDER BY hash(rowid + 42);

Das Ergebnis dieser Mischoperation ist deterministisch – führen wir das Skript wiederholt aus, liefert es immer die folgende Tabelle:

┌─────────┬────────────────────┐
│    s    │         x          │
│ varchar │       double       │
├─────────┼────────────────────┤
│ bar     │  7.142857142857143 │
│ qux     │               2.25 │
│ foo     │ 1.1111111111111112 │
└─────────┴────────────────────┘

Beachten Sie, dass das + 42 nur nötig ist, um die erste Zeile von ihrer Position zu stoßen – weil hash(0) den Wert 0 liefert, den kleinstmöglichen Wert, bleibt die erste Zeile beim Sortieren danach an ihrem Platz.

Typen im CSV-Loader angeben

DuckDBs CSV-Loader erkennt Typen automatisch aus einer kurzen Liste von BOOLEAN, BIGINT, DOUBLE, TIME, DATE, TIMESTAMP und VARCHAR. In manchen Fällen ist es sinnvoll, den erkannten Typ einer Spalte durch einen Typ außerhalb dieser Liste zu überschreiben. Zum Beispiel wollen wir die Spalte x von Anfang an als DECIMAL-Wert behandeln. Das geht spaltenweise mit dem Argument types der Funktion read_csv:

CREATE OR REPLACE TABLE example AS
    FROM read_csv('example.csv', types = {'x': 'DECIMAL(15, 3)'});

Dann können wir die Tabelle einfach abfragen, um das Ergebnis zu sehen:

FROM example;

┌─────────┬───────────────┐
│    s    │       x       │
│ varchar │ decimal(15,3) │
├─────────┼───────────────┤
│ foo     │         1.111 │
│ bar     │         7.143 │
│ qux     │         2.250 │
└─────────┴───────────────┘

CSV-Dateien an Ort und Stelle aktualisieren

In DuckDB ist es möglich, CSV-Dateien an Ort und Stelle zu lesen, zu verarbeiten und zu schreiben. Um zum Beispiel die Spalte s in dieselbe Datei zu projizieren, reicht:

COPY (SELECT s FROM 'example.csv') TO 'example.csv';

Die resultierende Datei example.csv hat folgenden Inhalt:

s
foo
bar
qux

Beachten Sie, dass dieser Trick in Unix-Shells ohne Workaround nicht möglich ist. Man könnte versucht sein, den folgenden Befehl auf example.csv auszuführen und dasselbe Ergebnis zu erwarten:

cut -d, -f1 example.csv > example.csv

Wegen der Eigenheiten von Unix-Pipelines bleibt uns nach diesem Befehl aber eine leere Datei example.csv. Die Lösung ist, andere Dateinamen zu verwenden und dann umzubenennen:

cut -d, -f1 example.csv > tmp.csv && mv tmp.csv example.csv

Schlussgedanken

Das war’s für heute. Die in diesem Post gezeigten Tricks stehen auf duckdbsnippets.com. Wenn Sie einen Trick teilen möchten, reichen Sie ihn dort ein oder schicken Sie ihn uns über Social Media oder Discord. Viel Spaß beim Hacken!