Fehlerhafte CSV-Dateien lesen

CSV-Dateien treten in allen Formen auf; manche enthalten viele Fehler, die ein sauberes Einlesen grundsätzlich erschweren. Damit Sie solche Dateien trotzdem lesen können, liefert DuckDB ausführliche Fehlermeldungen, kann fehlerhafte Zeilen überspringen und fehlerhafte Zeilen in einer temporären Tabelle speichern, um einen Bereinigungsschritt zu unterstützen.

Strukturelle Fehler

DuckDB erkennt und überspringt mehrere Arten struktureller Fehler. In diesem Abschnitt gehen wir jeden Fehler anhand eines Beispiels durch. Für die Beispiele gilt die folgende Tabelle:

CREATE TABLE people (name VARCHAR, birth_date DATE);

DuckDB erkennt die folgenden Fehlertypen:

• CAST: Cast-Fehler treten auf, wenn eine Spalte in der CSV-Datei nicht in den erwarteten Schemawert gecastet werden kann. Die Zeile Pedro,The 90s würde etwa einen Fehler auslösen, weil sich die Zeichenkette The 90s nicht in ein Datum casten lässt.
• MISSING COLUMNS: Dieser Fehler tritt auf, wenn eine Zeile in der CSV-Datei weniger Spalten hat als erwartet. In unserem Beispiel erwarten wir zwei Spalten; eine Zeile mit nur einem Wert, z. B. Pedro, löst diesen Fehler aus.
• TOO MANY COLUMNS: Dieser Fehler tritt auf, wenn eine Zeile in der CSV mehr Spalten hat als erwartet. In unserem Beispiel löst jede Zeile mit mehr als zwei Spalten diesen Fehler aus, z. B. Pedro,01-01-1992,pdet.
• UNQUOTED VALUE: Gequotete Werte in CSV-Zeilen müssen am Ende immer ungequotet werden; bleibt ein gequoteter Wert durchgehend gequotet, entsteht ein Fehler. Nimmt unser Scanner quote='"' an, würde die Zeile "pedro"holanda, 01-01-1992 einen Unquoted-Value-Fehler erzeugen.
• LINE SIZE OVER MAXIMUM: DuckDB hat einen Parameter für die maximale Zeilengröße einer CSV-Datei, der standardmäßig 2.097.152 Bytes beträgt. Ist unser Scanner auf max_line_size = 25 gesetzt, erzeugt die Zeile Pedro Holanda, 01-01-1992 einen Fehler, weil sie 25 Bytes überschreitet.
• INVALID ENCODING: DuckDB unterstützt UTF-8-Zeichenketten sowie die Kodierungen UTF-16 und Latin-1. Zeilen mit anderen Zeichen erzeugen einen Fehler. Die Zeile pedro\xff\xff, 01-01-1992 wäre etwa problematisch.

Anatomie eines CSV-Fehlers

Standardmäßig bricht der Scanner beim CSV-Lesen sofort ab und wirft den Fehler an den Nutzer, sobald ein struktureller Fehler auftritt. Diese Fehler sollen möglichst viele Informationen liefern, damit Sie sie direkt in der CSV-Datei beurteilen können.

Ein Beispiel für eine vollständige Fehlermeldung:

Conversion Error:
CSV Error on Line: 5648
Original Line: Pedro,The 90s
Error when converting column "birth_date". date field value out of range: "The 90s", expected format is (DD-MM-YYYY)

Column date is being converted as type DATE
This type was auto-detected from the CSV file.
Possible solutions:
* Override the type for this column manually by setting the type explicitly, e.g., types={'birth_date': 'VARCHAR'}
* Set the sample size to a larger value to enable the auto-detection to scan more values, e.g., sample_size=-1
* Use a COPY statement to automatically derive types from an existing table.

  file= people.csv
  delimiter = , (Auto-Detected)
  quote = " (Auto-Detected)
  escape = " (Auto-Detected)
  new_line = \r\n (Auto-Detected)
  header = true (Auto-Detected)
  skip_rows = 0 (Auto-Detected)
  date_format = (DD-MM-YYYY) (Auto-Detected)
  timestamp_format =  (Auto-Detected)
  null_padding=0
  sample_size=20480
  ignore_errors=false
  all_varchar=0

Der erste Block nennt den Ort des Fehlers: Zeilennummer, die ursprüngliche CSV-Zeile und das problematische Feld:

Conversion Error:
CSV Error on Line: 5648
Original Line: Pedro,The 90s
Error when converting column "birth_date". date field value out of range: "The 90s", expected format is (DD-MM-YYYY)

Der zweite Block nennt mögliche Lösungen:

Column date is being converted as type DATE
This type was auto-detected from the CSV file.
Possible solutions:
* Override the type for this column manually by setting the type explicitly, e.g., types={'birth_date': 'VARCHAR'}
* Set the sample size to a larger value to enable the auto-detection to scan more values, e.g., sample_size=-1
* Use a COPY statement to automatically derive types from an existing table.

Da der Typ dieses Felds automatisch erkannt wurde, wird vorgeschlagen, das Feld als VARCHAR zu definieren oder den gesamten Datensatz für die Typerkennung zu nutzen.

Der letzte Block zeigt einige Scanner-Optionen, die Fehler verursachen können, und gibt an, ob sie automatisch erkannt oder manuell gesetzt wurden.

Die Option ignore_errors verwenden

Manchmal enthalten CSV-Dateien mehrere strukturelle Fehler, und Sie möchten diese einfach überspringen und die korrekten Daten lesen. Fehlerhafte CSV-Dateien lassen sich mit der Option ignore_errors lesen. Ist sie gesetzt, werden Zeilen ignoriert, die sonst einen Parserfehler auslösen würden. In unserem Beispiel zeigen wir einen CAST-Fehler; jeder der im Abschnitt zu strukturellen Fehlern beschriebenen Fehler würde die fehlerhafte Zeile überspringen.

Betrachten Sie die folgende CSV-Datei, faulty.csv:

Pedro,31
Oogie Boogie, three

Lesen Sie die CSV-Datei und geben Sie an, dass die erste Spalte ein VARCHAR und die zweite ein INTEGER ist, schlägt das Laden fehl, weil sich die Zeichenkette three nicht in ein INTEGER umwandeln lässt.

Die folgende Abfrage wirft beispielsweise einen Cast-Fehler.

FROM read_csv('faulty.csv', columns = {'name': 'VARCHAR', 'age': 'INTEGER'});

Mit gesetztem ignore_errors wird die zweite Zeile der Datei übersprungen, und nur die vollständige erste Zeile wird ausgegeben. Zum Beispiel:

FROM read_csv(
    'faulty.csv',
    columns = {'name': 'VARCHAR', 'age': 'INTEGER'},
    ignore_errors = true
);

Ausgabe:

name	age
Pedro	31

Beachten Sie, dass der CSV-Parser von der Projection-Pushdown-Optimierung beeinflusst wird. Würden wir nur die Spalte name selektieren, wären beide Zeilen gültig, weil der Cast-Fehler bei age nie auftritt. Zum Beispiel:

SELECT name
FROM read_csv('faulty.csv', columns = {'name': 'VARCHAR', 'age': 'INTEGER'});

Ausgabe:

name
Pedro
Oogie Boogie

Fehlerhafte CSV-Zeilen abrufen

Fehlerhafte CSV-Dateien lesen zu können ist wichtig; für viele Bereinigungsschritte müssen Sie aber auch genau wissen, welche Zeilen beschädigt sind und welche Fehler der Parser darin gefunden hat. Dafür gibt es DuckDBs CSV-Rejects-Table-Funktion. Standardmäßig legt diese Funktion zwei temporäre Tabellen an.

1. reject_scans: Speichert Informationen zu den Parametern des CSV-Scanners.
2. reject_errors: Speichert Informationen zu jeder fehlerhaften CSV-Zeile und in welchem CSV-Scanner sie auftrat.

Jeder der im Abschnitt zu strukturellen Fehlern beschriebenen Fehler wird in den Rejects-Tabellen gespeichert. Hat eine Zeile mehrere Fehler, entstehen mehrere Einträge für dieselbe Zeile, einer pro Fehler.

Reject-Scans

Die CSV-Reject-Scans-Tabelle liefert die folgenden Informationen:

Spaltenname	Beschreibung	Typ
scan_id	Die interne ID, mit der DuckDB diesen Scanner darstellt	UBIGINT
file_id	Ein Scanner kann über mehrere Dateien laufen; file_id steht für eine eindeutige Datei in einem Scanner	UBIGINT
file_path	Der Dateipfad	VARCHAR
delimiter	Das verwendete Trennzeichen, z. B. ;	VARCHAR
quote	Das verwendete Anführungszeichen, z. B. “	VARCHAR
escape	Das verwendete Escape, z. B. “	VARCHAR
newline_delimiter	Das verwendete Zeilenumbruch-Trennzeichen, z. B. \r\n	VARCHAR
skip_rows	Ob Zeilen am Dateianfang übersprungen wurden	UINTEGER
has_header	Ob die Datei einen Header hat	BOOLEAN
columns	Das Schema der Datei (also alle Spaltennamen und -typen)	VARCHAR
date_format	Das Format für Datumstypen	VARCHAR
timestamp_format	Das Format für Zeitstempeltypen	VARCHAR
user_arguments	Zusätzliche Scanner-Parameter, die manuell gesetzt wurden	VARCHAR

Reject-Fehler

Die CSV-Reject-Errors-Tabelle liefert die folgenden Informationen:

Spaltenname	Beschreibung	Typ
scan_id	Die interne ID, mit der DuckDB diesen Scanner darstellt; zum Joinen mit den Reject-Scans-Tabellen	UBIGINT
file_id	file_id steht für eine eindeutige Datei in einem Scanner; zum Joinen mit den Reject-Scans-Tabellen	UBIGINT
line	Zeilennummer in der CSV-Datei, in der der Fehler auftrat.	UBIGINT
line_byte_position	Byte-Position des Zeilenanfangs, an der der Fehler auftrat.	UBIGINT
byte_position	Byte-Position, an der der Fehler auftrat.	UBIGINT
column_idx	Tritt der Fehler in einer bestimmten Spalte auf, der Index der Spalte.	UBIGINT
column_name	Tritt der Fehler in einer bestimmten Spalte auf, der Name der Spalte.	VARCHAR
error_type	Der Typ des aufgetretenen Fehlers.	ENUM
csv_line	Die ursprüngliche CSV-Zeile.	VARCHAR
error_message	Die von DuckDB erzeugte Fehlermeldung.	VARCHAR

Parameter

Die folgenden Parameter der Funktion read_csv konfigurieren die CSV-Rejects-Tabelle.

Name	Beschreibung	Typ	Standard
store_rejects	Ist der Wert true, werden Fehler in der Datei übersprungen und in den standardmäßigen temporären Rejects-Tabellen gespeichert.	BOOLEAN	False
rejects_scan	Name einer temporären Tabelle, in der die Scan-Informationen fehlerhafter CSV-Dateien gespeichert werden.	VARCHAR	reject_scans
rejects_table	Name einer temporären Tabelle, in der die Informationen zu fehlerhaften Zeilen einer CSV-Datei gespeichert werden.	VARCHAR	reject_errors
rejects_limit	Obere Grenze der fehlerhaften Datensätze einer CSV-Datei, die in der Rejects-Tabelle erfasst werden. 0 bedeutet, dass kein Limit gilt.	BIGINT	0

Um die Informationen fehlerhafter CSV-Zeilen in einer Rejects-Tabelle zu speichern, setzen Sie die Option store_rejects auf true. Zum Beispiel:

FROM read_csv(
    'faulty.csv',
    columns = {'name': 'VARCHAR', 'age': 'INTEGER'},
    store_rejects = true
);

Anschließend können Sie die Tabellen reject_scans und reject_errors abfragen, um Informationen zu den zurückgewiesenen Tupeln zu erhalten. Zum Beispiel:

FROM reject_scans;

Ausgabe:

scan_id	file_id	file_path	delimiter	quote	escape	newline_delimiter	skip_rows	has_header	columns	date_format	timestamp_format	user_arguments
5	0	faulty.csv	,	“	“	\n	0	false	{‘name’: ‘VARCHAR’,‘age’: ‘INTEGER’}			store_rejects=true

FROM reject_errors;

Ausgabe:

scan_id	file_id	line	line_byte_position	byte_position	column_idx	column_name	error_type	csv_line	error_message
5	0	2	10	23	2	age	CAST	Oogie Boogie, three	Error when converting column “age”. Could not convert string “ three“ to ‘INTEGER’