Чтение некорректных файлов CSV

Файлы CSV могут иметь самые разные форматы, и некоторые из них содержат множество ошибок, что затрудняет их чистое чтение. Чтобы помочь пользователям читать такие файлы, DuckDB поддерживает подробные сообщения об ошибках, возможность пропускать некорректные строки и возможность сохранения некорректных строк во временной таблице для упрощения этапа очистки данных.

Структурные ошибки

DuckDB поддерживает обнаружение и пропуск нескольких различных структурных ошибок. В этом разделе мы рассмотрим каждую ошибку с примером. Для примеров рассмотрим следующую таблицу:

CREATE TABLE people (name VARCHAR, birth_date DATE);

DuckDB обнаруживает следующие типы ошибок:

• CAST: Ошибки приведения типов возникают, когда столбец в файле CSV не может быть приведен к ожидаемому типу схемы. Например, строка Pedro,The 90s вызовет ошибку, так как строку The 90s нельзя привести к дате.
• MISSING COLUMNS: Эта ошибка возникает, если строка в файле CSV содержит меньше столбцов, чем ожидалось. В нашем примере ожидается два столбца; следовательно, строка с одним значением, например Pedro, вызовет эту ошибку.
• TOO MANY COLUMNS: Эта ошибка возникает, если строка в файле CSV содержит больше столбцов, чем ожидалось. В нашем примере любая строка с более чем двумя столбцами вызовет эту ошибку, например, Pedro,01-01-1992,pdet.
• UNQUOTED VALUE: Значения в кавычках в строках CSV всегда должны быть не в кавычках в конце; если значение в кавычках остается в кавычках на протяжении всей строки, это вызовет ошибку. Например, предположим, что наш сканер использует quote='"', строка "pedro"holanda, 01-01-1992 будет содержать ошибку невыведенного значения.
• LINE SIZE OVER MAXIMUM: В DuckDB есть параметр, который устанавливает максимальный размер строки в файле CSV, который по умолчанию установлен в 2,097,152 байт. Предположим, что наш сканер установлен на max_line_size = 25, строка Pedro Holanda, 01-01-1992 вызовет ошибку, так как она превышает 25 байт.
• INVALID UNICODE: DuckDB поддерживает только строки UTF-8; следовательно, строки, содержащие символы, не являющиеся UTF-8, будут вызывать ошибку. Например, строка pedro\xff\xff, 01-01-1992 будет проблематичной.

Структура сообщения об ошибке CSV

По умолчанию, при чтении CSV, если встречаются какие-либо структурные ошибки, сканер немедленно остановит процесс сканирования и выведет ошибку пользователю. Эти ошибки разработаны для предоставления максимально возможной информации, чтобы пользователи могли напрямую оценить их в своём файле CSV.

Это пример полного сообщения об ошибке:

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

Первый блок предоставляет информацию о месте возникновения ошибки, включая номер строки, исходную строку CSV и проблемный столбец:

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.

Поскольку тип этого столбца был определён автоматически, предлагается определить столбец как VARCHAR или полностью использовать набор данных для определения типа.

Наконец, последний блок представляет некоторые параметры, используемые в сканере, которые могут вызывать ошибки, указывая, были ли они определены автоматически или установлены пользователем вручную.

Использование параметра ignore_errors

Существуют случаи, когда файлы CSV могут содержать несколько структурных ошибок, и пользователи просто хотят пропустить их и прочитать корректные данные. Чтение файлов CSV с ошибками возможно с помощью параметра ignore_errors. При использовании этого параметра строки, содержащие данные, которые в противном случае вызвали бы ошибку анализатора CSV, будут пропущены. В нашем примере мы продемонстрируем ошибку приведения типов, но обратите внимание, что любая из ошибок, описанных в разделе «Структурные ошибки», приведет к пропусканию некорректной строки.

Например, рассмотрим следующий файл CSV, faulty.csv:

Pedro,31
Oogie Boogie, three

Если вы читаете файл CSV, указав, что первый столбец является VARCHAR, а второй столбец - INTEGER, загрузка файла завершится с ошибкой, так как строка three не может быть преобразована в INTEGER.

Например, следующий запрос вызовет ошибку приведения типов.

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

Однако, при ignore_errors установленной, вторая строка файла пропускается, выводит только полную первую строку. Например:

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

Вывод:

Следует отметить, что анализатор CSV подвержен оптимизации сжатия проекции. Следовательно, если мы выберем только столбец «имя», обе строки будут считаться корректными, так как ошибка приведения типов для столбца «возраст» никогда не произойдёт. Например:

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

Вывод:

Получение некорректных строк CSV

Возможность чтения файлов CSV с ошибками важна, но для многих операций очистки данных также необходимо точно знать, какие строки повреждены и какие ошибки обнаружил анализатор. В таких ситуациях можно использовать функцию DuckDB «Таблица отклонений CSV». По умолчанию эта функция создаёт две временные таблицы.

1. reject_scans: Содержит информацию о параметрах сканера CSV
2. reject_errors: Содержит информацию о каждой некорректной строке CSV и о том, в каком сканере CSV она произошла.

Обратите внимание, что любые ошибки, описанные в разделе «Структурные ошибки», будут храниться в таблицах отклонений. Также, если у строки есть несколько ошибок, будет храниться несколько записей для одной строки, по одной на каждую ошибку.

Таблицы отклонений сканирования

Таблица отклонений сканирования CSV возвращает следующую информацию:

Таблицы отклонений ошибок

Таблица отклонений ошибок CSV возвращает следующую информацию:

Параметры

Ниже перечислены параметры, используемые в функции read_csv для настройки таблицы отбракованных CSV.

Чтобы сохранить информацию о некорректных строках CSV-файла в таблице отбракованных данных, пользователю необходимо установить параметр store_rejects в значение true. Например:

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

Затем можно запросить обе таблицы reject_scans и reject_errors, чтобы получить информацию об отброшенных кортежах. Например:

FROM reject_scans;

Вывод:

FROM reject_errors;

Вывод: