2025-04-16

DuckDBs CSV-Reader und der Pollock-Robustheitsbenchmark: In den CSV-Abgrund

Pedro Holanda, Gábor Szárnyas (DuckLabs); Gerardo Vitagliano (MIT)

Der traurige Zustand der CSV-Landschaft

Es ist allgemein bekannt, dass CSV-Dateien in allen Formen und Größen vorkommen. Zwar gibt es einen klar definierten Standard, doch Systeme exportieren Daten oft, ohne auch nur die grundlegenden Regeln der CSV-Konstruktion einzuhalten. Rabobank, eine der größten Banken der Niederlande, exportiert etwa die Finanzdaten ihrer Kunden mit unescaped Anführungszeichen in gequoteten Werten.

Wenn ein großes Finanzinstitut keine sauberen CSV-Dateien schreiben kann, kann man sich die Fehler vorstellen, die in CSV-Dateien aus jahrzehntealter Legacy-Software, in handgeschriebenen CSVs, in Exporten aus Tabellenkalkulationen und so weiter auftreten! Solche Fehler sind so weit verbreitet, dass Tabellenkalkulationen, Dataframe-Bibliotheken und spezialisierte CSV-Tools alles daransetzen, diese Dateien zu retten und in Tabellen zu hämmern, mit denen Nutzer arbeiten können.

DuckDBs CSV-Parser

Als wir den DuckDB-CSV-Parser entworfen haben, wollten wir sicherstellen, dass er nicht nur einfach zu bedienen und schnell ist, sondern auch extrem zuverlässig – so zuverlässig, dass er die meisten CSVs lesen kann, die man ihm vorsetzt. In diesem Blogbeitrag betrachten wir das aus zwei Blickwinkeln. Zuerst schauen wir uns die wichtigsten Optionen an, mit denen sich nicht-standardkonforme CSV-Dateien lesen lassen, und zeigen ihre Wirkung anhand einfacher Beispiele. Danach prüfen wir, wie robust DuckDBs CSV-Parser ist, indem wir ihn mit dem Pollock Benchmark testen.

Im Internet wird viel darüber diskutiert, ob ein System nicht-standardkonforme Dateien überhaupt lesen sollte. Ja, solche Dateien sind frustrierend. Man kann sich im Internet darüber beschweren oder sie ignorieren. Wir sind aber überzeugt, dass das Ignorieren der Realität einen Preis hat. Nicht-standardkonforme Dateien zu lesen – auch wenn das Mehrdeutigkeit erzeugen kann – sollte die Entscheidung des Nutzers sein.

Laufendes Beispiel

Als laufendes Beispiel nutzen wir eine CSV-Datei namens cafes.csv:

ZIP,Name,Country
1014,"Cafe ""Gedoogt""",Netherlands
1015,"Cafe "De Tuin" Amsterdam",Netherlands
1095,Joost
1872,Cafe Gezellig,Netherlands,5

Trotz ihrer geringen Größe enthält die Datei viele verschiedene Fehler. Schauen wir sie Zeile für Zeile an:

Das Beispiel naiv laden

Wenn wir unser Beispiel naiv zu lesen versuchen, erhalten wir eine Tabelle mit nur einer Zeile:

FROM read_csv('cafes.csv');

┌─────────┬───────────────┬─────────────┬─────────┐
│ column0 │    column1    │   column2   │ column3 │
│  int64  │    varchar    │   varchar   │  int64  │
├─────────┼───────────────┼─────────────┼─────────┤
│  1872   │ Cafe Gezellig │ Netherlands │    5    │
└─────────┴───────────────┴─────────────┴─────────┘

Der Reader hat alle Zeilen außer der letzten ignoriert. Fragen wir den CSV-Sniffer nach dem erkannten Dialekt:

SELECT Delimiter, Quote, Escape, SkipRows
FROM sniff_csv('cafes.csv');

┌───────────┬─────────┬─────────┬──────────┐
│ Delimiter │  Quote  │ Escape  │ SkipRows │
│  varchar  │  int16  │ varchar │  uint16  │
├───────────┼─────────┼─────────┼──────────┤
│ ,         │ \0      │ \0      │    4     │
└───────────┴─────────┴─────────┴──────────┘

Der Sniffer hat entschieden, dass Quotes und Escape-Zeichen in der Datei nicht vorkommen (dargestellt als \0). Deshalb überspringt er die ersten 4 Zeilen (einschließlich des Headers), um die Datei gemäß diesem Dialekt zu lesen.

Tipp DuckDB erlaubt es, alle Fehler beim Lesen einer CSV-Datei über die Tabelle reject_errors abzurufen. Das geht über den Rahmen dieses Beitrags hinaus, Details stehen auf der Dokumentationsseite „Reading Faulty CSV Files“.

Das Beispiel mit einem benutzerdefinierten CSV-Dialekt laden

Versuchen wir, die fehlerhaften CSV-Dateien mit einem benutzerdefinierten CSV-Dialekt zu lesen. Dazu setzen wir die Option auto_detect auf false und geben manuell das Vorhandensein des header, die Werte von delim(iter), quote und escape sowie das Schema mit der Option columns an.

FROM read_csv('cafes.csv',
    auto_detect = false,
    header = true,
    delim = ',',
    quote = '"',
    escape = '"',
    columns = {'ZIP': 'INT16', 'Name': 'VARCHAR', 'Country': 'VARCHAR'}
);

Diese SQL-Anweisung führt zu einem Fehler wegen unescaped Quotes in Zeile 3: Der Parser kann nicht feststellen, ob das erste Anführungszeichen in "De Tuin" das Ende des gequoteten Werts markiert oder nicht:

Invalid Input Error:
CSV Error on Line: 3
Original Line: 1015,"Cafe "De Tuin" Amsterdam",Netherlands
Value with unterminated quote found.

Possible fixes:
* Disable the parser's strict mode (strict_mode=false) to allow reading rows that do not comply with the CSV standard.
* Enable ignore errors (ignore_errors=true) to skip this row
* Set quote to empty or to a different value (e.g., quote='')

Die Fehlermeldung sagt uns, dass ein Wert mit einem unbeendeten Quote vorlag, verursacht durch das unescaped Quote-Zeichen. Sie schlägt auch mögliche Fixes vor, die unsere nächsten Versuche leiten.

Strict Mode deaktivieren

Wie die Fehlermeldung mitteilt, ist eine Möglichkeit, die Einschränkungen des DuckDB-CSV-Readers zu lockern, die Option strict_mode. Wenn man strict_mode deaktiviert, kann der CSV-Reader die folgenden häufigen Fehler übergehen:

1. Unescaped gequotete Werte, wie in Zeile 3.

2. Zeilen mit zu vielen Spalten. Bei der Zeile 1872,Cafe Gezellig,Netherlands,5 wird die letzte Spalte ignoriert, damit sie in unser Schema mit drei Spalten passt.

3. Eine Mischung aus Newline-Delimitern, z. B. Dateien mit sowohl \n als auch \r\n. Dieser Fehler tritt in unserem laufenden Beispiel nicht auf.

Standardmäßig ist strict_mode auf true gesetzt, das heißt, DuckDB versucht nicht, Zeilen zu parsen, die nicht zum Dialekt passen. Setzt man sie auf false, versucht der CSV-Reader, die Datei trotzdem zu lesen, auch wenn sie mit der gegebenen Konfiguration nicht korrekt gelesen werden kann. Zurück zu unserem Beispiel, Strict Mode aus:

FROM read_csv('cafes.csv',
    auto_detect = false,
    header = true,
    strict_mode = false,
    delim = ',',
    quote = '"',
    escape = '"',
    columns = {'ZIP': 'INT16', 'Name': 'VARCHAR', 'Country': 'VARCHAR'}
);

Der Reader bricht jetzt erst in Zeile 4 ab, das heißt, die vorherigen Zeilen wurden bis dahin korrekt gelesen:

Invalid Input Error:
CSV Error on Line: 4
Original Line: 1095,Joost
Expected Number of Columns: 3 Found: 2
Possible fixes:
* Enable null padding (null_padding=true) to replace missing values with NULL
* Enable ignore errors (ignore_errors=true) to skip this row

Zu strict_mode = false sind zwei Dinge wichtig:

• Mit dieser Option gibt es keine Garantie, dass das Ergebnis korrekt ist. Denn bei einer nicht-standardkonformen CSV-Datei lässt sich nicht definieren, was ein „korrektes Ergebnis“ überhaupt ist.

• DuckDB nutzt einen Best-Effort-Parser: Er scheitert nur, wenn er auf einen Fehler trifft, zu dem er keine vernünftige Vermutung über die korrekten Daten hat (stellen Sie sich eine Integer-Spalte vor, in der einige Integers als ausgeschriebene Strings stehen – das führt zu einem Casting-Fehler). Es ist möglich, dass unser Parser bei einer bestimmten Kombination von Fehlern trotzdem scheitert; wir arbeiten daran, ihn noch robuster zu machen, während wir dem Abgrund ins Auge sehen.

Fehler ignorieren

Die andere von der Fehlermeldung vorgeschlagene Option war ignore_errors = true. Das bedeutet schlicht, dass alle Zeilen, die nicht zum gewählten Dialekt oder Schema passen, aus dem Ergebnis übersprungen werden. Fügen wir das zu unserer Query hinzu:

FROM read_csv('cafes.csv',
    auto_detect = false,
    header = true,
    strict_mode = false,
    delim = ',',
    quote = '"',
    escape = '"',
    columns = {'ZIP': 'INT16', 'Name': 'VARCHAR', 'Country': 'VARCHAR'},
    ignore_errors = true
);

Wir erhalten ein fast vollständiges Ergebnis! Nur Zeile 4 1095,Joost fehlt, weil eine Spalte fehlt.

┌───────┬────────────────────────┬─────────────┐
│  ZIP  │          Name          │   Country   │
│ int16 │        varchar         │   varchar   │
├───────┼────────────────────────┼─────────────┤
│  1014 │ Cafe "Gedoogt"         │ Netherlands │
│  1015 │ Cafe De Tuin Amsterdam │ Netherlands │
│  1872 │ Cafe Gezellig          │ Netherlands │
└───────┴────────────────────────┴─────────────┘

Die Option ignore_errors beeinflusst auch das Verhalten des Sniffers: Er ignoriert Zeilen, die beim Sniffen Fehler erzeugen. Im Allgemeinen wird die Konfiguration bevorzugt, die die wenigsten Fehler erzeugt.

Null-Padding für fehlende Werte

Die letzte Option, die beim Lesen nicht-standardkonformer CSV-Dateien hilft, ist null_padding. Sie ist nützlich, wenn Zeilen in der CSV-Datei eine inkonsistente Anzahl von Werten haben und für bestimmte Spalten Werte fehlen. Lesen wir die Datei mit null_padding = true.

FROM read_csv('cafes.csv',
    auto_detect = false,
    header = true,
    strict_mode = false,
    delim = ',',
    quote = '"',
    escape = '"',
    columns = {'ZIP': 'INT16', 'Name': 'VARCHAR', 'Country': 'VARCHAR'},
    null_padding = true
);

Das erzeugt folgendes Ergebnis:

┌───────┬────────────────────────┬─────────────┐
│  ZIP  │          Name          │   Country   │
│ int16 │        varchar         │   varchar   │
├───────┼────────────────────────┼─────────────┤
│  1014 │ Cafe "Gedoogt"         │ Netherlands │
│  1015 │ Cafe De Tuin Amsterdam │ Netherlands │
│  1095 │ Joost                  │ NULL        │
│  1872 │ Cafe Gezellig          │ Netherlands │
└───────┴────────────────────────┴─────────────┘

Minimale Konfiguration zum Lesen der Beispieldatei

Puh. Wir haben die Datei geladen, aber der read_csv-Aufruf ist etwa 200 Zeichen lang! Zum Glück geht es kürzer: Setzen wir sowohl strict_mode = false als auch null_padding = true, müssen wir nur noch quote und escape angeben. Dieser Aufruf braucht nur etwa 80 Zeichen und liefert ein gültiges Ergebnis aus dieser CSV-Datei:

FROM read_csv('cafes.csv',
    strict_mode = false,
    null_padding = true,
    quote = '"',
    escape = '"'
);

┌───────┬────────────────────────┬─────────────┬─────────┐
│  ZIP  │          Name          │   Country   │ column3 │
│ int64 │        varchar         │   varchar   │  int64  │
├───────┼────────────────────────┼─────────────┼─────────┤
│  1014 │ Cafe "Gedoogt"         │ Netherlands │    NULL │
│  1015 │ Cafe De Tuin Amsterdam │ Netherlands │    NULL │
│  1095 │ Joost                  │ NULL        │    NULL │
│  1872 │ Cafe Gezellig          │ Netherlands │       5 │
└───────┴────────────────────────┴─────────────┴─────────┘

Mit dieser Konfiguration erzeugt der Sniffer tatsächlich eine neue Spalte (column3), um den Extra-Wert in der letzten Zeile unterzubringen. Dieses Beispiel zeigt: Selbst bei einer CSV-Datei mit mehreren Problemen kann DuckDBs CSV-Sniffer noch einigermaßen gute Entscheidungen zu Dialekt und Schema treffen.

Tipp Sie können die letzte Spalte zum Beispiel mit SELECT #1, #2, #3 FROM ... in der Query weglassen.

Der Pollock Benchmark

Der Pollock Benchmark ist ein Benchmark zum CSV-Laden, der misst, wie robust ein CSV-System beim Lesen nicht-standardkonformer CSV-Dateien ist. Der Benchmark wurde auf der VLDB 2023 veröffentlicht und ist vollständig Open Source; ein DuckDB-Eintrag wurde kürzlich ins Repository aufgenommen.

Die Autoren des Pollock Benchmarks haben über 245.000 öffentliche CSV-Datensätze analysiert, um die häufigsten Verletzungen des RFC-4180-Standards in CSV-Dateien in freier Wildbahn zu verstehen. Nach der Identifikation der häufigsten Fehlertypen haben sie einen CSV-Dateigenerator mit einem Pollution-Mechanismus gebaut, der diese Fehler einfügt. Insgesamt erzeugt das Benchmark-Tool über 2.200 verschmutzte Dateien. Es erzeugt außerdem die korrekte Dialektkonfiguration, mit der diese Dateien gelesen werden sollten, sowie eine saubere Version der Datei.

Zur Bewertung der Robustheit liest das Benchmark-Tool die verschmutzte Datei mit der für das jeweilige System-under-Test gesetzten Konfiguration. Anschließend schreibt das System eine neue Datei mit der Antwort, die mit der sauberen Version verglichen wird. Dieser Vergleich liefert einen Score, der angibt, wie genau das System die Datei gelesen hat. Der Benchmark erzeugt vor allem zwei Scores: den Simple Score und den Weighted Score; der Weighted Score berücksichtigt, wie häufig dieser Fehlertyp in der Praxis vorkommt.

In diesem Abschnitt beschreiben wir die häufigsten Fehler aus dem Paper und teilen die Benchmark-Ergebnisse nach dem Hinzufügen von DuckDB.

Häufige Fehler

Das Paper beschreibt viele der häufigen Fehler in CSV-Dateien. Hier skizzieren wir kurz einige der häufigsten, für den vollständigen Überblick verweisen wir auf das Paper:

1. Inkonsistente Zellenzahl in Zeilen: RFC-4180 verlangt, dass alle Zeilen einschließlich des Headers eine konsistente Spaltenzahl haben, aber viele Dateien haben zu viele oder zu wenige Delimiter pro Zeile.

2. Nicht-standardkonforme Newline-Sequenzen: RFC-4180 verlangt Carriage Return gefolgt von Line Feed (\r\n), viele Dateien nutzen aber nur Line Feeds, nur Carriage Returns oder eine Mischung.

3. Mehrere Header-Zeilen: Der Standard erlaubt eine optionale einzelne Header-Zeile, viele Dateien haben aber mehrere Header.

4. Falsch gequotete oder unescaped Zellen: Dieser Fehler tritt auf, wenn gequotete Werte unescaped Quotes enthalten. Siehe file_escape_char_0x00.csv als Beispiel. GitHubs Tabellenrenderer, der die saubere Version dieser Datei lesen kann, scheitert am Parsen.

5. Dateien mit Multibyte-Delimitern: Der Standard verlangt kommagetrennte Dateien. Single-Byte-Delimiter sind weit akzeptiert, Multibyte-Delimiter sind aber häufig.

6. Dateien mit mehreren Tabellen: Wenn eine CSV-Datei mehrere Tabellen mit unterschiedlichen Schemas in derselben Datei speichert.

Methodik

Der Benchmark testet eine große Vielfalt von Systemen, darunter CSV-Parsing-Frameworks (z. B. CleverCSV), relationale Datenbanksysteme (z. B. PostgreSQL), Tabellenkalkulationen (z. B. LibreOffice Calc), Datenvisualisierungstools (z. B. Dataviz) und Dataframe-Bibliotheken (z. B. Pandas).

Ein wichtiger Aspekt des Benchmarks ist, dass er Dialekt und Schema für jede Datei bereitstellt. Ohne das könnten Systeme ohne Sniffer (z. B. PostgreSQL) die Datei nicht lesen. Es gibt keine feste Regel, wie stark jedes System diese Information nutzt. Pandas etwa nutzt diese Einstellungen nur teilweise. Um diese Unterschiede in unserer Bewertung von DuckDB abzubilden, haben wir zwei Konfigurationen aufgenommen:

1. DuckDB (benchmark config). Unter dieser Konfiguration werden alle für DuckDB relevanten Optionen aus der Konfigurationsdatei – etwa CSV-Dialekt und Schema – an den Reader übergeben. Zusätzlich setzen wir alle in den vorherigen Abschnitten beschriebenen Optionen (also null_padding = true, strict_mode = false, ignore_errors = true). Das sagt uns im Wesentlichen, wie viel wir aus diesen Dateien lesen können, wenn Nutzer die nötigen Optionen manuell setzen.

2. DuckDB (auto-detect only). Unter dieser Konfiguration nutzen wir keine benutzerdefinierten Konfigurationsdateien. Die einzigen gesetzten Optionen sind jene, die das Lesen nicht-standardkonformer Dateien erlauben (also null_padding = true, strict_mode = false, ignore_errors = true). Diese Option bewertet damit auch die volle Kraft unseres Sniffers in unsicheren Szenarien.

Pollock-Scores

Die folgende Tabelle zeigt die Ergebnisse inklusive DuckDB. Wir haben die Originaltabelle auf die Systeme mit den besten Scores in jeder Kategorie beschränkt (z. B. Parsing-Frameworks, relationale Systeme usw.). Sowohl beim Simple als auch beim Weighted Score ist DuckDB mit allen gesetzten Optionen (die Standardkonfiguration für den Pollock Benchmark) klarer Sieger. Beim Simple Score bedeutet das, dass DuckDB 99,61% der Daten aus allen Dateien korrekt gelesen hat. DuckDB behandelt auch die häufigsten Fehler korrekt, wie der Weighted Score zeigt.

Pollock-Scores sortiert nach Weighted Score (von 10):

Laut der Website des Benchmarks ist „SpreadDesktop“ eine desktopbasierte kommerzielle Tabellenkalkulation und „SpreadWeb“ eine webbasierte Tabellenkalkulation. Ihre echten Namen wurden aus Lizenzgründen weggelassen.

Wie erwartet schneidet DuckDB im vollständigen Auto-Modus schlechter ab, weil der Sniffer Dialekte und Schemas selbst erkennen muss. Manche Dateien haben sogar einen Multibyte-Delimiter, den DuckDB unterstützt, der aber nicht im Suchraum des Sniffers liegt – das senkt den Score. In diesen Fällen hat DuckDB trotzdem etwa 90,75% der Daten korrekt gelesen und erreicht 9,075 als Gesamtscore und 8,439 als gewichteten Gesamtscore. Dieses Ergebnis kommt allein von read_csv('file_path', null_padding = true, strict_mode = false, ignore_errors = true) ohne jede Nutzereingabe zur tatsächlichen Datenkonfiguration – DuckDBs CSV-Reader kann die meisten nicht-standardkonformen CSV-Dateien also wirklich lesen, selbst mit minimaler Konfiguration!

Zum Benchmark beitragen

Es ist durchaus möglich, dass Ihr bevorzugtes CSV-Reader-System noch nicht in den Benchmark-Ergebnissen enthalten ist. DuckDB zum Beispiel war anfangs nicht dabei, aber wir haben festgestellt, dass das Hinzufügen recht einfach war! Wir hoffen, dass der DuckDB-Pull-Request als Inspiration dient für alle, die ihre bevorzugten Systeme hinzufügen möchten. Die Reproduktion der Ergebnisse ist ebenfalls recht einfach. Im Gegensatz zu den meisten Benchmarks, die auf Performance und Korrektheit fokussieren, misst der Pollock Benchmark Accuracy – das macht ihn unabhängig von der verwendeten Maschine leicht reproduzierbar.

Happy hacking!