2023-10-27

DuckDBs CSV-Sniffer: Automatische Erkennung von Typen und Dialekten

Pedro Holanda

Es gibt viele Dateiformate, in denen sich Daten ablegen lassen. Es gibt leistungsorientierte Binärformate wie Parquet: Daten liegen spaltenweise, in Row Groups partitioniert und stark komprimiert. Parquet ist aber starr und braucht spezialisierte Systeme zum Lesen und Schreiben.

Am anderen Ende stehen Dateien im CSV-Format (comma-separated values), das ich gerne das „Woodstock der Daten“ nenne. CSV-Dateien sind flexibel: Sie sind Textdateien, lassen sich mit jedem Editor bearbeiten, und fast jedes Datensystem kann sie lesen und darauf abfragen.

Diese Flexibilität hat ihren Preis. Eine CSV-Datei zu lesen ist nicht trivial; Nutzer brauchen viel Vorwissen über die Datei. DuckDBs CSV-Reader bietet zum Beispiel mehr als 25 Konfigurationsoptionen. Ich habe den Eindruck, die Leute denken, ich arbeite nicht hart genug, wenn ich nicht bei jedem Release mindestens drei neue Optionen einführe. Nur ein Scherz. Zu den Optionen gehören Trennzeichen, Quote- und Escape-Zeichen, die Zahl der Spalten, ob ein Header vorhanden ist und die Spaltentypen. Das bremst interaktive Datenexploration und macht das Analysieren neuer Datensätze mühsam und weniger angenehm.

Eine der Daseinsberechtigungen von DuckDB ist, angenehm und einfach nutzbar zu sein. Nutzer sollen nicht an CSV-Dateien herumfummeln und Optionen von Hand setzen müssen. Manuelle Eingaben sollten nur bei Dateien mit ungewöhnlichem CSV-Dialekt nötig sein (ein Dialekt ist die Kombination aus Trennzeichen, Quote, Escape und Newline) oder zum Festlegen von Spaltentypen.

CSV-Optionen automatisch zu erkennen kann einschüchternd sein. Es gibt viele Optionen, und ihre Kombinationen sprengen leicht den Suchraum. Besonders bei schlecht strukturierten CSV-Dateien. Manche argumentieren, CSV habe eine Spezifikation – in Wahrheit ändert sich die „Spezifikation“, sobald ein einziges System eine fehlerhafte Datei lesen kann. Und oh Junge, ich habe in den letzten Monaten meinen Anteil halb kaputter CSV-Dateien gesehen, die DuckDB lesen sollte.

DuckDB implementiert einen Multi-Hypothesen-CSV-Sniffer, der Dialekte, Header, Datums-/Zeitformate, Spaltentypen und schmutzige Zeilen zum Überspringen automatisch erkennt. Unser Ziel: alles zu lesen, was nach CSV aussieht – niemals aufgeben und Sie nie im Stich lassen. All das ohne großen Anfangsaufwand beim Lesen. In der Bleeding-Edge-Version läuft der Sniffer standardmäßig beim Lesen einer CSV-Datei. Der Sniffer priorisiert immer vom Nutzer gesetzte Optionen (setzt der Nutzer , als Trennzeichen, probiert der Sniffer nichts anderes und nimmt die Eingabe als korrekt an).

In diesem Beitrag erkläre ich, wie die aktuelle Implementierung funktioniert, spreche über die Leistung und gebe einen Ausblick!

DuckDBs automatische Erkennung

Der Ablauf beim Parsen von CSV-Dateien ist in der Abbildung unten dargestellt. Er besteht derzeit aus fünf Phasen, die in den nächsten Abschnitten erklärt werden.

Die CSV-Datei im Überblickbeispiel sieht so aus:

Name, Height, Vegetarian, Birthday
"Pedro", 1.73, False, 30-07-92
... imagine 2048 consistent rows ...
"Mark", 1.72, N/A, 20-09-92

In der ersten Phase, der Dialekterkennung, wählen wir die Dialektkandidaten, die die meisten Spalten pro Zeile erzeugen und dabei konsistent bleiben (also keine großen Schwankungen der Spaltenzahl über die Datei). In unserem Beispiel erkennt der Sniffer nach dieser Phase die nötigen Optionen für Trennzeichen, Quotes, Escapes und Newline-Begrenzer.

Die zweite Phase, die Typerkennung, ermittelt die Datentypen jeder Spalte. In unserem Beispiel erkennt der Sniffer vier Spaltentypen: VARCHAR, DOUBLE, BOOL und DATE.

Der dritte Schritt, die Header-Erkennung, stellt fest, ob die Datei einen Header hat. Wenn ja, setzen wir die Spaltennamen daraus; sonst erzeugen wir sie automatisch. In unserem Beispiel gibt es einen Header, und jede Spalte bekommt den dort definierten Namen.

Jetzt haben unsere Spalten Namen, und wir kommen zur vierten, optionalen Phase: Typersetzung. DuckDBs CSV-Reader erlaubt es, Spaltentypen per Name anzugeben. Sind sie angegeben, ersetzen wir die erkannten Typen durch die Angaben des Nutzers.

Schließlich die letzte Phase, die Typverfeinerung. Hier analysieren wir weitere Teile der Datei, um die in der ersten Typerkennung bestimmten Typen zu prüfen. Wenn nötig, verfeinern wir sie. In unserem Beispiel war die Spalte Vegetarian zunächst BOOL. Bei genauerem Hinsehen enthielt sie den String N/A, daher wurde der Spaltentyp auf VARCHAR angehoben, um alle Werte aufzunehmen.

Die automatische Erkennung läuft nur auf einer sequenziellen Stichprobe der CSV-Datei. Standardmäßig umfasst die Stichprobe 20.480 Tupel (also 10 DuckDB-Execution-Chunks). Das lässt sich über sample_size konfigurieren und auf -1 setzen, wenn die gesamte Datei gesnifft werden soll. Weil dieselben Daten mit verschiedenen Optionen wiederholt gelesen werden und Nutzer die ganze Datei scannen können, werden alle beim Sniffing erzeugten CSV-Puffer gecacht und effizient verwaltet, damit die Leistung hoch bleibt.

Natürlich hat der CSV-Sniffer auf sehr großen Dateien einen starken Einfluss auf die Gesamtleistung (siehe unseren Benchmark-Abschnitt). In solchen Fällen sollte die Stichprobengröße im Rahmen bleiben.

In den nächsten Unterabschnitten beschreibe ich jede Phase im Detail.

Dialekterkennung

In der Dialekterkennung ermitteln wir Trennzeichen, Quotes, Escapes und Newline-Begrenzer einer CSV-Datei.

Unser Suchraum für Trennzeichen: ,, |, ;, \t. Hat die Datei ein Trennzeichen außerhalb dieses Raums, muss der Nutzer es angeben (z. B. delim='?'). Unser Suchraum für Quotes ist ", ' und \0, wobei \0 ein String-Terminator ist und bedeutet, dass kein Quote vorliegt; auch hier können Nutzer Zeichen außerhalb des Suchraums setzen (z. B. quote='?'). Der Suchraum der Escape-Werte hängt vom Quote ab; kurz: dieselben wie Quotes plus \, und auch sie können vom Nutzer gesetzt werden (escape='?'). Die letzte erkannte Option sind die Newline-Begrenzer: \r, \n, \r\n und eine Mischung aus allem (glauben Sie mir, ich habe eine echte CSV-Datei gesehen, die gemischt hat).

Standardmäßig läuft die Dialekterkennung auf 24 verschiedenen Dialektkombinationen. Um die vielversprechendste Konfiguration zu finden, berechnen wir, wie viele Spalten jedes CSV-Tupel unter jeder Konfiguration erzeugen würde. Diejenige mit den meisten Spalten und den konsistentesten Zeilen gewinnt.

Die Berechnung konsistenter Zeilen hängt von weiteren nutzerdefinierten Optionen ab. Die Option null_padding füllt fehlende Spalten mit NULL-Werten. Zeilen mit fehlenden Spalten bekommen also NULL.

Ist null_padding wahr, werden CSV-Dateien mit inkonsistenten Zeilen trotzdem berücksichtigt, bevorzugt werden aber Konfigurationen mit möglichst wenigen gepaddeten Zeilen. Ist null_padding falsch, überspringt der Dialektdetektor inkonsistente Zeilen am Anfang der Datei. Als Beispiel diese CSV-Datei.

I like my csv files to have notes to make dialect detection harder
I also like commas like this one : ,
A,B,C
1,2,3
4,5,6

Hier würde der Sniffer erkennen, dass mit dem Trennzeichen , die erste Zeile eine Spalte hat, die zweite zwei, die restlichen Zeilen aber 3. Ist null_padding falsch, würde er , trotzdem als Kandidat wählen und die oberen Zeilen als schmutzige Notizen annehmen. (Glauben Sie mir, CSV-Notizen gibt es wirklich!) Ergebnis:

A,B,C
1, 2, 3
4, 5, 6

Ist null_padding wahr, werden alle Zeilen übernommen:

'I like my csv files to have notes to make dialect detection harder', None, None
'I also like commas like this one : ', None, None
'A', 'B', 'C'
'1', '2', '3'
'4', '5', '6'

Ist die Option ignore_errors gesetzt, wird die Konfiguration mit den meisten Spalten und den wenigsten inkonsistenten Zeilen gewählt.

Typerkennung

Nach der Entscheidung für den Dialekt erkennen wir die Typen jeder Spalte. Unsere Typerkennung betrachtet: SQLNULL, BOOLEAN, BIGINT, DOUBLE, TIME, DATE, TIMESTAMP, VARCHAR. Diese Typen sind nach Spezifität geordnet: Zuerst prüfen wir, ob eine Spalte SQLNULL ist; wenn nicht, ob BOOLEAN, und so weiter, bis nur noch VARCHAR bleibt. DuckDB hat mehr Typen als die Standardmenge. Nutzer können über auto_type_candidates festlegen, welche Typen der Sniffer berücksichtigen soll.

In dieser Phase geht der Algorithmus über den ersten Datenchunk (2048 Tupel). Der Prozess startet bei der zweiten gültigen Zeile (also keiner Notiz). Die erste Zeile wird getrennt gespeichert und nicht zur Typerkennung genutzt. Später wird erkannt, ob sie ein Header ist. Die Typerkennung versucht pro Spalte und Wert einen Cast, um die Spaltentypen zu bestimmen. Jede Spalte startet mit einem eigenen Array aller zu prüfenden Typen. Der Wert wird auf diesen Typ gecastet; schlägt das fehl, fällt der Typ aus dem Array, es wird mit dem neuen Typ versucht, und das geht weiter, bis der ganze Chunk fertig ist.

In dieser Phase bestimmen wir auch das Format von DATE- und TIMESTAMP-Spalten. Für DATE-Spalten kommen infrage:

• %m-%d-%Y
• %m-%d-%y
• %d-%m-Y
• %d-%m-%y
• %Y-%m-%d
• %y-%m-%d

Für TIMESTAMP-Spalten:

• %Y-%m-%dT%H:%M:%S.%f
• %Y-%m-%d %H:%M:%S.%f
• %m-%d-%Y %I:%M:%S %p
• %m-%d-%y %I:%M:%S %p
• %d-%m-%Y %H:%M:%S
• %d-%m-%y %H:%M:%S
• %Y-%m-%d %H:%M:%S
• %y-%m-%d %H:%M:%S

Spalten mit Formaten außerhalb dieses Suchraums müssen über dateformat und timestampformat definiert werden.

Als Beispiel diese CSV-Datei.

Name, Age
,
Jack Black, 54
Kyle Gass, 63.2

Die erste Zeile [Name, Age] wird für die Header-Erkennung getrennt gespeichert. Die zweite Zeile [NULL, NULL] erlaubt, die erste und zweite Spalte auf SQLNULL zu casten. Ihre Typkandidaten-Arrays sind daher gleich: [SQLNULL, BOOLEAN, BIGINT, DOUBLE, TIME, DATE, TIMESTAMP, VARCHAR].

In der dritten Zeile [Jack Black, 54] wird es interessanter. Bei „Jack Black“ fallen aus dem Typkandidaten-Array der Spalte 0 alle spezifischeren Werte, weil „Jack Black“ nur nach VARCHAR konvertierbar ist. Die zweite Spalte lässt sich weder nach SQLNULL noch nach BOOLEAN wandeln, als BIGINT klappt es. Die Typkandidaten der zweiten Spalte sind daher [BIGINT, DOUBLE, TIME, DATE, TIMESTAMP, VARCHAR].

In der vierten Zeile haben wir [Kyle Gass, 63.2]. Für die erste Spalte kein Problem, ebenfalls ein gültiges VARCHAR. Für die zweite Spalte scheitert der Cast nach BIGINT, der nach DOUBLE gelingt. Das neue Kandidaten-Array der zweiten Spalte ist [DOUBLE, TIME, DATE, TIMESTAMP, VARCHAR].

Header-Erkennung

Die Phase Header-Erkennung nimmt die erste gültige Zeile der CSV-Datei und versucht, sie auf die Kandidatentypen unserer Spalten zu casten. Gibt es einen Cast-Mismatch, betrachten wir die Zeile als Header; sonst behandeln wir die erste Zeile als Daten und erzeugen den Header automatisch.

Im vorherigen Beispiel war die erste Zeile [Name, Age], die Kandidaten-Arrays [VARCHAR] und [DOUBLE, TIME, DATE, TIMESTAMP, VARCHAR]. Name ist ein String und lässt sich nach VARCHAR wandeln. Age ist ebenfalls ein String; der Cast nach DOUBLE scheitert. Weil der Cast fehlschlägt, betrachtet der Algorithmus die erste Zeile als Header: Die erste Spalte heißt Name, die zweite Age.

Wird kein Header erkannt, werden Spaltennamen automatisch nach dem Muster column${x} erzeugt, wobei x die (0-basierte) Position der Spalte in der CSV-Datei ist.

Typersetzung

Jetzt kennt der Auto-Detection-Algorithmus die Headernamen. Gibt der Nutzer Spaltentypen an, werden die vom Sniffer erkannten Typen in der Phase Typersetzung ersetzt. Zum Beispiel können wir den Typ von Age durch FLOAT ersetzen:

SELECT *
FROM read_csv('greatest_band_in_the_world.csv', types = {'Age': 'FLOAT'});

Diese Phase ist optional und läuft nur, wenn Typen manuell definiert sind.

Typverfeinerung

Die Phase Typverfeinerung macht dasselbe wie die Typerkennung; der Unterschied ist die Granularität der Daten, auf denen der Cast-Operator arbeitet – aus Leistungsgründen. In der Typerkennung prüfen wir Casts pro Spalte und Wert.

In dieser Phase wechseln wir zu einem effizienteren vektorisierten Cast-Algorithmus. Die Validierung bleibt dieselbe: Typen aus den Kandidaten-Arrays fallen weg, wenn ein Cast scheitert.

Wie schnell ist das Sniffing?

Um den Aufwand der automatischen Erkennung zu messen, führen wir den Sniffer auf dem NYC-Taxi-Datensatz aus. Die Datei hat 19 Spalten, 10.906.858 Tupel und ist 1,72 GB groß.

Die Kosten für das Sniffen von Dialekt, Spaltennamen und Typen liegen bei etwa 4 % der Gesamtkosten des Ladens.

Variierende Stichprobengröße

Manchmal tauchen Dialektoptionen oder feinere Typen erst später in der CSV-Datei auf. Dann wird sample_size wichtig, damit der Sniffer genug Daten sieht, um richtig zu entscheiden. Eine größere sample_size erhöht aber die Gesamtlaufzeit, weil mehr Daten für Dialekte und Typen herangezogen werden.

Unten sehen Sie, wie eine Vervielfachung der Standard-Stichprobengröße (X-Achse) die Sniffer-Laufzeit auf dem NYC-Datensatz beeinflusst. Erwartungsgemäß steigt die Sniffing-Zeit linear mit der Stichprobengröße.

Variierende Spaltenzahl

Die andere Haupteigenschaft einer CSV-Datei, die die Auto-Detection beeinflusst, ist die Zahl der Spalten. Hier testen wir den Sniffer gegen eine variierende Zahl von INTEGER-Spalten in Dateien mit 10.906.858 Tupeln. Die Ergebnisse zeigt die Abbildung unten. Von einer auf zwei Spalten steigt die Laufzeit steiler. Bei einzelnen Spalten ist die Dialekterkennung vereinfacht, weil Trennzeichen fehlen. Bei den übrigen Spalten steigt die Laufzeit erwartungsgemäß eher linear mit der Spaltenzahl.

Fazit und Ausblick

Wenn Sie ungewöhnliche CSV-Dateien abfragen, aufräumen oder normalisieren wollen, ist DuckDB schon eine der besten verfügbaren Lösungen. Der Einstieg ist einfach. Zum Lesen einer CSV-Datei mit dem Sniffer genügt:

SELECT *
FROM 'path/to/csv_file.csv';

DuckDBs CSV-Auto-Detection ist ein wichtiges Werkzeug, um CSV-Dateien zu erkunden. Mit den Standardoptionen hat sie geringen Einfluss auf die Gesamtkosten des Ladens und Lesens. Das Ziel ist, Dateien immer lesen zu können – auch bei schlecht definierten Dateien so gut wie möglich.

Wir haben eine Liste von Punkten rund um den Sniffer, die wir künftig verbessern wollen.

1. Erweiterte Header-Erkennung. Ob eine CSV einen Header hat, bestimmen wir derzeit über einen Typ-Mismatch zwischen der ersten gültigen Zeile und dem Rest der Datei. Das kann False Negatives erzeugen, wenn etwa alle Spalten vom Typ VARCHAR sind. Wir wollen die Header-Erkennung um Abgleiche mit häufigen Headernamen erweitern.
2. Genauigkeits- und Geschwindigkeitsbenchmarks. Wir haben viele Genauigkeits- und Regressionstests; wegen der Flexibilität von CSV ist das manuelle Anlegen von Testfällen aber mühsam. Als Nächstes wollen wir eine ganze Suite mit dem Pollock Benchmark umsetzen.
3. Besseres Sampling. Der Auto-Detection-Algorithmus läuft derzeit auf einer sequenziellen Stichprobe. Neue Einstellungen tauchen oft erst später in der Datei auf (Quotes zum Beispiel nur in den letzten 10 %). Den Sniffer an verschiedenen Stellen der Datei auszuführen kann die Genauigkeit verbessern.
4. CSV-Dateien mit mehreren Tabellen. In derselben CSV-Datei können mehrere Tabellen stehen, ein häufiges Szenario beim Export von Tabellenkalkulationen. Das wollen wir erkennen und unterstützen.
5. Erkennung von Null-Strings. Derzeit haben wir keinen Algorithmus, der die Darstellung von Null-Strings erkennt.
6. Erkennung der Dezimalpräzision. Auch die Dezimalpräzision erkennen wir noch nicht automatisch. Das wollen wir angehen.
7. Parallelisierung. DuckDBs CSV-Reader ist vollständig parallelisiert, der Sniffer läuft aber noch auf einem Thread. Eine Parallelisierung analog zum CSV-Reader (Beschreibung in einem späteren Beitrag) würde das Sniffing deutlich beschleunigen und Full-File-Sniffing ermöglichen.
8. Sniffer als eigenständige Funktion. Nutzer können die Abfrage DESCRIBE nutzen, um Informationen vom Sniffer zu bekommen; sie liefert aber nur Spaltennamen und Typen. Wir wollen den Sniffing-Algorithmus als eigenständige Funktion bereitstellen, die die vollständigen Ergebnisse liefert. Dann lassen sich Dateien mit genau denselben Optionen konfigurieren, ohne den Sniffer erneut zu starten.