CSV-Autoerkennung

Bei read_csv versucht das System automatisch zu erkennen, wie die CSV-Datei gelesen werden soll – über den CSV-Sniffer. Dieser Schritt ist nötig, weil CSV-Dateien sich nicht selbst beschreiben und in vielen Dialekten vorkommen. Die Autoerkennung läuft grob so ab:

• Dialekt der CSV-Datei erkennen (Trennzeichen, Quoting-Regel, Escape).
• Typen der einzelnen Spalten erkennen.
• Erkennen, ob die Datei eine Header-Zeile hat.

Standardmäßig versucht das System, alle Optionen automatisch zu erkennen. Einzelne Optionen können Sie jedoch überschreiben. Das ist nützlich, wenn das System danebenliegt. Wird etwa das Trennzeichen falsch gewählt, überschreiben Sie es, indem Sie read_csv mit einem expliziten Trennzeichen aufrufen (z. B. read_csv('file.csv', delim = '|')).

Stichprobengröße

Die Typerkennung arbeitet auf einer Stichprobe der Datei. Die Größe der Stichprobe ändern Sie über den Parameter sample_size. Die Standard-Stichprobengröße beträgt 20.480 Zeilen. Setzen Sie sample_size auf -1, wird die gesamte Datei für die Stichprobe gelesen:

SELECT * FROM read_csv('my_csv_file.csv', sample_size = -1);

Wie die Stichprobe gezogen wird, hängt vom Dateityp ab. Lesen wir von einer normalen Datei auf dem Datenträger, springen wir in die Datei und versuchen, an verschiedenen Stellen zu sampeln. Lesen wir aus einer Datei, in der wir nicht springen können – etwa einer .gz-komprimierten CSV-Datei oder stdin – stammen die Stichproben nur vom Anfang der Datei.

Die Funktion sniff_csv

Den CSV-Sniffer können Sie als eigenen Schritt mit der Funktion sniff_csv(filename) ausführen. Sie liefert die erkannten CSV-Eigenschaften als Tabelle mit einer Zeile. sniff_csv akzeptiert einen optionalen Parameter sample_size, um die Anzahl der beprobten Zeilen festzulegen.

FROM sniff_csv('my_file.csv');
FROM sniff_csv('my_file.csv', sample_size = 1000);

Spaltenname	Beschreibung	Beispiel
Delimiter	Trennzeichen	,
Quote	Anführungszeichen	"
Escape	Escape	\
NewLineDelimiter	Zeilenumbruch-Trennzeichen	\r\n
Comment	Kommentarzeichen	#
SkipRows	Anzahl übersprungener Zeilen	1
HasHeader	Ob die CSV einen Header hat	true
Columns	Spaltentypen als LIST von STRUCTs	({'name': 'VARCHAR', 'age': 'BIGINT'})
DateFormat	Datumsformat	%d/%m/%Y
TimestampFormat	Zeitstempelformat	%Y-%m-%dT%H:%M:%S.%f
UserArguments	Argumente, mit denen sniff_csv aufgerufen wurde	sample_size = 1000
Prompt	Fertige Anweisung zum Lesen der CSV	FROM read_csv('my_file.csv', auto_detect=false, delim=',', ...)

Prompt

Die Spalte Prompt enthält eine SQL-Anweisung mit den vom Sniffer erkannten Einstellungen.

-- use line mode in CLI to get the full command
.mode line
SELECT Prompt FROM sniff_csv('my_file.csv');

Prompt = FROM read_csv('my_file.csv', auto_detect=false, delim=',', quote='"', escape='"', new_line='\n', skip=0, header=true, columns={...});

Erkennungsschritte

Dialekterkennung

Die Dialekterkennung versucht, die Stichproben mit den betrachteten Werten zu parsen. Der erkannte Dialekt ist der, der (1) eine einheitliche Spaltenzahl pro Zeile hat und (2) die höchste Spaltenzahl pro Zeile.

Für die automatische Dialekterkennung werden die folgenden Dialekte betrachtet.

Parameter	Betrachtete Werte
delim	, `
quote	" ' (leer)
escape	" ' \ (leer)

Betrachten Sie die Beispieldatei flights.csv:

FlightDate|UniqueCarrier|OriginCityName|DestCityName
1988-01-01|AA|New York, NY|Los Angeles, CA
1988-01-02|AA|New York, NY|Los Angeles, CA
1988-01-03|AA|New York, NY|Los Angeles, CA

In dieser Datei läuft die Dialekterkennung so ab:

• Teilen wir an |, wird jede Zeile in 4 Spalten zerlegt.
• Teilen wir an ,, werden die Zeilen 2–4 in 3 Spalten zerlegt, die erste Zeile in 1 Spalte.
• Teilen wir an ;, wird jede Zeile in 1 Spalte zerlegt.
• Teilen wir an \t, wird jede Zeile in 1 Spalte zerlegt.

In diesem Beispiel wählt das System | als Trennzeichen. Alle Zeilen werden in dieselbe Anzahl Spalten zerlegt, und es gibt mehr als eine Spalte pro Zeile – das Trennzeichen wurde also tatsächlich in der CSV-Datei gefunden.

Typerkennung

Nach der Dialekterkennung versucht das System, die Typen der einzelnen Spalten zu bestimmen. Dieser Schritt läuft nur bei read_csv. Bei der Anweisung COPY werden stattdessen die Typen der Tabelle verwendet, in die kopiert wird.

Die Typerkennung versucht, die Werte jeder Spalte in die Kandidatentypen umzuwandeln. Scheitert die Umwandlung, wird der Kandidatentyp für diese Spalte aus der Menge entfernt. Nachdem alle Stichproben verarbeitet wurden, wird der verbleibende Kandidatentyp mit der höchsten Priorität gewählt. Die Standardmenge der Kandidatentypen lautet, in Prioritätsreihenfolge:

Typen
NULL
BOOLEAN
TIME
DATE
TIMESTAMP
TIMESTAMPTZ
BIGINT
DOUBLE
VARCHAR

Alles lässt sich nach VARCHAR casten; dieser Typ hat daher die niedrigste Priorität. Alle Spalten werden als Fallback nach VARCHAR gewandelt, wenn sie sich nicht in etwas anderes casten lassen. In flights.csv wird die Spalte FlightDate nach DATE gecastet, die übrigen Spalten nach VARCHAR.

Die Menge der Kandidatentypen, die der CSV-Reader berücksichtigen soll, können Sie explizit mit der Option auto_type_candidates angeben. VARCHAR als Fallback-Typ wird immer als Kandidat berücksichtigt, unabhängig davon, ob Sie ihn angeben.

Hier sind alle zusätzlichen Kandidatentypen, die Sie über auto_type_candidates angeben können, in Prioritätsreihenfolge:

Typen
TINYINT
SMALLINT
INTEGER
DECIMAL
FLOAT

Auch wenn die automatisch erkennbaren Datentypen begrenzt wirken, lässt sich der CSV-Reader so konfigurieren, dass er beliebig komplexe Typen liest – über die Option types, die im nächsten Abschnitt beschrieben wird.

Die Typerkennung lässt sich vollständig mit der Option all_varchar deaktivieren. Ist sie gesetzt, bleiben alle Spalten VARCHAR (so wie sie ursprünglich in der CSV-Datei stehen).

Ob Anführungszeichen verwendet werden oder nicht (z. B. "42" und 42), macht für die Typerkennung keinen Unterschied. Gequotete Felder werden nicht automatisch zu VARCHAR; der Sniffer sucht weiterhin den Kandidatentyp mit der höchsten Priorität.

Typerkennung überschreiben

Die erkannten Typen können Sie einzeln mit der Option types überschreiben. Diese Option nimmt eine von zwei Formen an:

• Eine Liste von Typdefinitionen (z. B. types = ['INTEGER', 'VARCHAR', 'DATE']). Das überschreibt die Typen der Spalten in der Reihenfolge ihres Auftretens in der CSV-Datei.
• Alternativ nimmt types eine Abbildung name → type entgegen, die einzelne Spalten überschreibt (z. B. types = {'quarter': 'INTEGER'}).

Die Menge der Spaltentypen, die Sie über types angeben können, ist nicht so begrenzt wie bei auto_type_candidates: Jede gültige Typdefinition ist für types zulässig. (Eine gültige Typdefinition erhalten Sie mit der Funktion typeof() oder über die Spalte column_type im Ergebnis von DESCRIBE.)

Das Feld Column der Funktion sniff_csv() liefert ein Struct mit Spaltennamen und -typen, das als Grundlage zum Überschreiben von Typen dienen kann.

Header-Erkennung

Die Header-Erkennung prüft, ob die Kandidaten-Headerzeile sich von den übrigen Zeilen der Datei hinsichtlich der Typen unterscheidet. In flights.csv besteht die Headerzeile nur aus VARCHAR-Spalten – die Werte enthalten dagegen einen DATE-Wert in der Spalte FlightDate. Deshalb legt das System die erste Zeile als Headerzeile fest und extrahiert die Spaltennamen daraus.

In Dateien ohne Headerzeile werden die Spaltennamen als column0, column1 usw. erzeugt.

Header können nicht korrekt erkannt werden, wenn alle Spalten den Typ VARCHAR haben – das System kann die Headerzeile dann nicht von den übrigen Zeilen unterscheiden. In diesem Fall nimmt das System an, dass die Datei einen Header hat. Das überschreiben Sie, indem Sie die Option header auf false setzen.

Datums- und Zeitstempelwerte

DuckDB unterstützt standardmäßig das ISO-8601-Format für Zeitstempel, Daten und Zeiten. Leider sind nicht alle Daten und Zeiten in diesem Standard formatiert. Deshalb unterstützt der CSV-Reader auch die Optionen dateformat und timestampformat. Darüber können Sie einen Formatstring angeben, der festlegt, wie Datum oder Zeitstempel gelesen werden sollen.

Im Rahmen der Autoerkennung versucht das System festzustellen, ob Daten und Zeiten in einer anderen Darstellung gespeichert sind. Das gelingt nicht immer – die Darstellung kann mehrdeutig sein. Das Datum 01-02-2000 lässt sich etwa als 2. Januar oder als 1. Februar lesen. Oft lassen sich solche Mehrdeutigkeiten auflösen. Treffen wir später auf das Datum 21-02-2000, muss das Format DD-MM-YYYY gewesen sein. MM-DD-YYYY entfällt, weil es keinen 21. Monat gibt.

Lassen sich die Mehrdeutigkeiten anhand der Daten nicht auflösen, hat das System eine Prioritätsliste, welches Datumsformat verwendet wird. Wählt das System falsch, können Sie dateformat und timestampformat manuell angeben.

Das System betrachtet die folgenden Formate für Daten (dateformat). Höhere Einträge werden bei Mehrdeutigkeiten gegenüber niedrigeren bevorzugt (d. h. ISO 8601 hat Vorrang vor MM-DD-YYYY).

dateformat
ISO 8601
%y-%m-%d
%Y-%m-%d
%d-%m-%y
%d-%m-%Y
%m-%d-%y
%m-%d-%Y

Das System betrachtet die folgenden Formate für Zeitstempel (timestampformat). Höhere Einträge werden bei Mehrdeutigkeiten gegenüber niedrigeren bevorzugt.

timestampformat
ISO 8601
%y-%m-%d %H:%M:%S
%Y-%m-%d %H:%M:%S
%d-%m-%y %H:%M:%S
%d-%m-%Y %H:%M:%S
%m-%d-%y %I:%M:%S %p
%m-%d-%Y %I:%M:%S %p
%Y-%m-%d %H:%M:%S.%f