Укрощение «диких» CSV: продвинутые техники DuckDB для инженеров данных

Представьте: 2:30 ночи. Офис пуст. Кофе давно остыл, а вы безучастно смотрите на экран, который издевательски показывает: Error: Could not convert string 'N/A' to INTEGER on line 56,789. Всё, что вам было нужно — импортировать «простую» CSV-выгрузку из вышестоящей системы. Знакомо?

Мы все бывали в этом «чистилище CSV». В тот самый момент, когда задача на пять минут превращается в многочасовое испытание, потому что где-то в глубине этого на первый взгляд безобидного файла скрывается неконсистентный разделитель, «сбежавшая» кавычка или, мой любимый случай — столбцы, которые загадочно появляются и вновь исчезают.

Я провёл бесчисленное количество часов, разбираясь с проблемными CSV-файлами, но после того как открыл для себя несколько малоизвестных возможностей DuckDB, такие ночные сражения стали происходить гораздо реже. Несмотря на то, что автоматический CSV-ридер DuckDB уже впечатляюще «умен», знание нескольких продвинутых приёмов позволяет избежать написания собственных скриптов предобработки, когда данные оказываются грязными.

В этом руководстве я поделюсь приёмами DuckDB, которые не раз спасали меня от CSV-ада:

• Как диагностировать, каким DuckDB «видит» ваш CSV-файл

• Подробный разбор анализатора (сниффер) CSV и принципов его работы

• Способы обработки проблемных строк без прерывания всего импорта

• Стратегии работы с несогласованными схемами в разных файлах

• Тонкая настройка параметров, когда автоопределению требуется помощь

• Практическая устойчивость: как DuckDB показывает себя на бенчмарке «грязных» CSV

Давайте разберёмся и попробуем сделать ваш следующий импорт CSV менее проблемным.

Что происходит «под капотом»: sniff_csv

Перед тем как загружать данные с помощью возможностей автоопределения DuckDB, крайне полезно понять, с чем, по мнению самой DuckDB, вы имеете дело. Правильно ли определён разделитель? Обнаружен ли заголовок? Какие типы данных были выведены? Функция sniff_csv() — это ваш инструмент разведки.

Вместо того чтобы вслепую запускать read_csv и рисковать получить ошибку, сначала выполните sniff_csv. Она анализирует выборку данных из файла и возвращает информацию об определённом диалекте, типах, наличии заголовка и других параметрах.

Возьмем в качестве примера файл events.csv:

EventDate|Organizer|City|Venue2022-03-01|OpenTech|San Francisco, CA|Moscone Center, Hall A2022-03-02|OpenTech|San Francisco, CA|Moscone Center, Hall B2022-03-03|OpenTech|San Francisco, CA|Moscone Center, Hall C

Посмотрим, как DuckDB интерпретирует этот файл:

FROM sniff_csv('events.csv');

ИНФО: Используются полные HTTP-адреса.
Для краткости в примерах ниже мы будем указывать только имя файла. Вы можете скачать примерные файлы по ссылкам (например, как events.csv выше). Однако можно также указать полный HTTP-адрес, так как DuckDB поддерживает протокол HTTP для загрузки CSV-файлов.

FROM sniff_csv(‘http://duckdb-example-files.s3-website-us-east-1.amazonaws.com/2025-blog-post-taming-wild-csvs/events.csv’);

Также можно управлять тем, какую часть файла анализировать:

FROM sniff_csv('events.csv', sample_size=10000); -- выборка 10 тыс. строк

Или проанализировать весь файл (осторожно с очень большими файлами!):

FROM sniff_csv('events.csv', sample_size=-1);

Результат возвращает большое количество информации в виде таблицы из одной строки:

• Delimiter, Quote, Escape, NewLineDelimiter — определённые структурные символы.

• SkipRows — количество строк, которые, по мнению системы, нужно пропустить в начале.

• HasHeader — булевый флаг, указывающий, обнаружен ли заголовок.

• Columns — список структур с определёнными именами и типами столбцов (например, {'name': 'VARCHAR', 'age': 'BIGINT'}).

• DateFormat, TimestampFormat — обнаруженные форматы даты и времени (если есть).

• Prompt — крайне полезное поле. Оно содержит готовую команду read_csv со всеми явно заданными параметрами. Её можно скопировать, вставить и при необходимости изменить.

Предварительный запуск sniff_csv позволяет существенно сократить количество догадок при сбоях импорта. Если, например, неверно определён разделитель или система считает, что заголовок есть, хотя его нет, вы сразу понимаете, какие параметры нужно переопределить в вызове read_csv.

Как работает анализатор

На самом деле CSV-анализатор DuckDB проходит несколько этапов, чтобы определить наилучший способ чтения файла:

1. Определение диалекта: на момент написания статьи анализатор проверяет 24 различных сочетания параметров диалекта (разделители, кавычки, экранирование, переводы строк), чтобы найти вариант, при котором число столбцов в строках оказывается наиболее согласованным.

2. Определение типов: после выбора диалекта анализатор изучает первый фрагмент данных (по умолчанию 2048 строк), чтобы определить типы столбцов, пытаясь приводить значения от наиболее специфичных типов к наименее специфичным (SQLNULL → BOOLEAN → BIGINT → DOUBLE → TIME → DATE → TIMESTAMP → VARCHAR).

3. Определение заголовка: анализатор проверяет, можно ли привести первую корректную строку к определённым типам столбцов. Если нельзя, эта строка считается заголовком.

4. Замена типов: если вы явно задали типы столбцов, они переопределяют типы, найденные анализатором.

5. Уточнение типов: затем анализатор проверяет найденные типы на большем объёме данных, используя векторизованное приведение (векторизацию).

Вот пример кода, который показывает, как посмотреть, что именно анализатор «видит» в более сложном случае:

-- Посмотреть, что анализатор определяет при большем размере выборкиSELECT   Delimiter, Quote, Escape, SkipRows, HasHeader, DateFormat, TimestampFormatFROM sniff_csv('events.csv',  sample_size=50000);-- Посмотреть определённые типы столбцовSELECT unnest(Columns)FROM sniff_csv('events.csv');

Когда я работал с набором данных, где было более 20 столбцов со смешанными типами, приём с unnest(Columns) оказался особенно полезным: он позволял точно увидеть, какие столбцы к каким типам были отнесены, и избавлял от множества повторных проверок и лишней отладки.

Разбираемся с ошибками: ignore_errors, store_rejects и не только

Итак, sniff_csv показывает, что всё в порядке, но с самим файлом проблемы всё равно остаются — например, среди миллионов корректных строк встречаются несколько проблемных. По умолчанию DuckDB остановит импорт на первой же ошибке. Но часто вам нужно просто загрузить корректные данные, а с плохими строками разобраться отдельно.

Вариант 1: просто пропустить их (ignore_errors)

Самый простой способ — указать DuckDB пропускать строки, которые вызывают ошибки разбора или приведения типов, с помощью параметра ignore_errors = true.

Представим файл faulty_folks.csv:

Name,AgeAlice,30Bob,forty-twoCharlie,35

Если попытаться прочитать этот файл обычным способом с явно заданными типами, импорт завершится ошибкой на возрасте Bob:

-- Это вызовет ошибку!SELECT * FROM read_csv('faulty_folks.csv', header=true, columns={'Name':'VARCHAR', 'Age':'INTEGER'});

Но если нам нужны только Alice и Charlie:

SELECT * FROM read_csv('faulty_folks.csv',     header = true,     -- Указываем ожидаемые типы    columns = {'Name': 'VARCHAR', 'Age': 'INTEGER'},     ignore_errors = true  -- Ключевой момент!    );

Пояснение:

• Мы задаём ожидаемые столбцы, включая тип INTEGER для Age.

• ignore_errors = true сообщает ридеру: если встретится строка, где значение в столбце ‘Age’ нельзя привести к INTEGER (например, «forty-two»), просто отбрось эту строку и продолжай чтение.

Вывод:

Bob остаётся за бортом, но импорт успешно завершается для корректных строк. Такой подход позволяет пропускать строки с самыми разными проблемами: ошибками приведения типов, неверным количеством столбцов, неэкранированными кавычками и так далее.

Вариант 2: пропустить и сохранить информацию (store_rejects)

Игнорировать ошибки удобно, но обычно всё же нужно понимать, что именно пошло не так и какие строки были отброшены. Возможно, вам нужно исправить исходные данные или подготовить отчёт по проблемам. Здесь параметр store_rejects = true становится особенно полезным.

Если использовать store_rejects, DuckDB по-прежнему пропускает плохие строки, как и при ignore_errors, но дополнительно записывает подробную информацию о каждой отброшенной строке и возникшей ошибке в две временные таблицы: reject_scans и reject_errors.

-- Читаем файл и сохраняем информацию об отброшенных строкахSELECT * FROM read_csv(   'faulty_folks.csv',   header = true,   columns = {'Name': 'VARCHAR', 'Age': 'INTEGER'},   store_rejects = true -- Сохранять сведения об ошибках   -- Необязательно: можно задать свои имена таблиц и лимит   -- rejects_scan = 'my_scan_info',   -- rejects_table = 'my_rejected_rows',   -- rejects_limit = 100 -- Сохранять максимум 100 ошибок на файл);-- Теперь посмотрим, что было отброшеноFROM reject_errors;-- И сведения о самом сканированииFROM reject_scans;

Пояснение:

1. Вызов read_csv выполняется, пропускает строку Bob и возвращает Alice и Charlie, как и раньше.

2. Ключевое отличие в том, что store_rejects = true заполняет временные таблицы.

3. FROM reject_errors; показывает сведения о строках, которые не удалось обработать:

• scan_id, file_id — ссылка на конкретное сканирование и файл.

• line — исходный номер строки в CSV.

• column_idx, column_name — в каком столбце возникла проблема, если это применимо.

• error_type — категория ошибки, например CAST или TOO_MANY_COLUMNS.

• csv_line — фактическое содержимое отброшенной строки.

• error_message — конкретное сообщение об ошибке, сгенерированное DuckDB.

4. FROM reject_scans; возвращает метаданные о самой операции read_csv: разделитель, правило обработки кавычек, использованную схему, путь к файлу и другие параметры.

Я считаю это невероятно полезным при отладке «грязных» данных. Вы получаете загруженные корректные данные и подробный отчёт по отброшенным строкам — и всё это прямо в DuckDB. Больше не нужно использовать поиск по тексту (grep) по огромным файлам в попытке найти ту самую проблемную строку.

Вариант 3: ослабление правил (strict_mode=false и null_padding=true)

Иногда задача сводится к тому, чтобы просто загрузить данные — даже если они немного «кривые». В таких случаях помогают более «снисходительные» параметры разбора CSV в DuckDB.

Параметр strict_mode = false говорит DuckDB ослабить требования к формату. Он будет пытаться читать строки, даже если в них есть типичные проблемы:

• Неэкранированные кавычки в полях (например, "15" Laptop").

• Строки с большим количеством столбцов, чем ожидается (DuckDB просто отбросит лишние).

• Смешанные форматы перевода строки (например, одновременно \n и \r\n).

ПРЕДУПРЕЖДЕНИЕ: быстро, но не всегда точно

Устанавливая strict_mode=false, вы полагаетесь на то, что DuckDB сделает «наилучшее предположение». Это отлично работает, когда важна скорость, но если точность данных критична, результат стоит перепроверить.

Ещё один часто используемый параметр — null_padding = true. Он обрабатывает строки с недостаточным количеством столбцов. Вместо ошибки DuckDB просто заполняет недостающие значения NULL.

Рассмотрим пример. Пусть у нас есть «грязный» CSV-файл inventory.csv:

ItemID,Description,Price101,"15" Laptop",999.99102,"Wireless Mouse"103,"Mechanical Keyboard",129.99,ExtraField

Этот файл содержит:

• Неэкранированную кавычку в описании первой строки

• Отсутствующую цену во второй строке

• Лишний столбец в третьей строке

Попробуем прочитать его обычным способом:

FROM read_csv('inventory.csv');

DuckDB пропустит все строки, кроме последней.

А теперь с ослабленными настройками:

-- Разбор «грязного» CSV с корректной обработкой недостающих и лишних полейFROM read_csv('inventory.csv',    strict_mode = false,   -- Игнорируем огрехи форматирования    null_padding = true   -- Заполняем недостающие столбцы значениями NULL);

Результирующая таблица:

Работа с меняющимися схемами: union_by_name

Ещё одна распространённая проблема: нужно загрузить несколько CSV-файлов (например, ежемесячные отчёты), но структура столбцов в них немного отличается. Допустим, в марте добавился новый столбец, а в июне изменился порядок. Попытка прочитать их вместе через простой вызов read_csv('monthly_report_*.csv') может завершиться ошибкой или привести к некорректному выравниванию данных.

Параметр union_by_name = true в DuckDB решает эту задачу элегантно. При чтении нескольких файлов (через шаблоны или списки) он выравнивает столбцы по именам заголовков, а не по их позиции. Если в каком-то файле отсутствует столбец, который есть в других, он будет заполнен значением NULL.

Представим файл report_jan.csv:

UserID,MetricA,MetricB1,10,1002,15,110

И файл report_feb.csv:

UserID,MetricB,MetricC,MetricA3,120,xyz,204,125,abc,25

Обратите внимание на разный порядок столбцов и появление нового столбца MetricC в феврале.

SELECT *FROM read_csv(    ['report_jan.csv', 'report_feb.csv'], -- Список файлов (или шаблон)    union_by_name = true -- Вот в чём «магия»);

Пояснение:

• DuckDB считывает заголовки из всех файлов.

• Формируется объединённая схема со всеми уникальными именами столбцов (UserID, MetricA, MetricB, MetricC).

• Для каждого файла данные сопоставляются с этой схемой по именам столбцов.

• Если в файле отсутствует какой-либо столбец (например, MetricC в report_jan.csv), он заполняется значениями NULL.

Результат:

Тонкая настройка автоопределения и переопределение параметров

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

Увеличение выборки: если типы определяются некорректно (например, столбец в основном содержит целые числа, но из-за нескольких вещественных значений позже определяется как BIGINT), попробуйте увеличить размер выборки:

SELECT * FROM read_csv('file.csv', sample_size = 50000); -- Или проанализировать весь файл (может быть медленно для больших файлов)SELECT * FROM read_csv('file.csv', sample_size = -1);

Явное задание типов: переопределите типы столбцов, если автоопределение ошибается или вам нужен другой тип:

-- Переопределение по имени столбцаSELECT * FROM read_csv('file.csv',   types = {'UserID': 'VARCHAR', 'TransactionAmount': 'DOUBLE'});-- Или по позиции, если нет заголовковSELECT * FROM read_csv('file.csv', header = false,  types = ['VARCHAR', 'DOUBLE', 'DATE']);

Принудительное указание заголовка/его отсутствие: если определение заголовка работает некорректно (часто бывает, когда все столбцы выглядят как строки):

-- Принудительно указать наличие заголовкаSELECT * FROM read_csv('file.csv', header = true);-- Или отсутствие заголовка с заданием своих имён столбцовSELECT * FROM read_csv('file.csv',  header = false,  names = ['colA', 'colB', 'colC']);

Форматы даты и времени: если даты не в формате ISO 8601 (YYYY-MM-DD) или время задано нестандартно:

 SELECT * FROM read_csv('file.csv',  dateformat = '%m/%d/%Y',  timestampformat = '%Y-%m-%dT%H:%M:%S.%f');

Все значения как строки (String): если вы хотите сначала загрузить всё как VARCHAR, а с типами разобраться позже:

SELECT * FROM read_csv('file.csv', all_varchar = true);

Какие столбцы могут быть NULL? По умолчанию пустое поле интерпретируется как NULL. Если пустые строки должны считаться допустимыми значениями:

SELECT * FROM read_csv('file.csv',   force_not_null = ['column_name1', 'column_name2']);

Очистка имён столбцов: если в заголовках есть пробелы или нестандартные символы:

SELECT * FROM read_csv('file.csv', normalize_names = true);

Это автоматически приведёт имена к корректному виду (например, заменит все неалфавитно-цифровые символы на _ и т.д.) при импорте.

Парсер CSV в DuckDB: лидер бенчмарка Pollock

Для тех, кто действительно интересуется устойчивостью работы с CSV, существует любопытный бенчмарк под названием Pollock, который оценивает, насколько хорошо разные системы справляются с нестандартными CSV-файлами. Его создатели проанализировали более 245 000 публичных CSV-наборов данных, выявили типичные нарушения стандарта RFC-4180 и на их основе сформировали тестовые файлы.

В недавних тестах DuckDB занял первое место: при настройке на обработку проблемных файлов он корректно прочитал 99,61% данных во всех тестовых наборах. Даже в режиме автоопределения с минимальной настройкой DuckDB успешно обрабатывает около 90,75% данных.

Это практическое подтверждение того, что описанные в статье подходы позволяют справляться с подавляющим большинством реальных проблем, которые возникают при работе с CSV.

Двигаемся дальше базовых возможностей

Мы успели охватить довольно многое в этом разборе возможностей DuckDB для работы с CSV: от диагностики проблем с помощью sniff_csv до обработки ошибок через ignore_errors и store_rejects, объединения несовместимых схем через union_by_name и тонкой настройки всего процесса с помощью различных переопределений параметров.

Что мне особенно нравится в DuckDB — его CSV-ридер не сводится к простому инструменту загрузки. Это полноценный, продуманный инструмент, созданный для работы с реальной «грязью» в данных прямо внутри SQL. Большинство инструментов для работы с данными умеют читать идеальные CSV-файлы, но в повседневной работе действительно важно то, как они справляются с неидеальными.

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

В следующий раз, когда вам попадётся проблемный CSV, вспомните об этих приёмах. Они могут сэкономить вам немало времени и нервов и позволят быстрее вернуться к действительно интересной части работы — анализу данных. Удачных запросов!

Если CSV регулярно требует внешней предобработки, значит часть работы с данными вынесена за пределы базы. На курсе «SQL для разработчиков и аналитиков» разбирают, как обрабатывать и приводить данные прямо в SQL — от базового синтаксиса до оптимизации и работы с большими объёмами. Это упрощает пайплайны и снижает риск сбоев.

Готовы к обучению на SQL? Пройдите вступительный тест. До 30 апреля за прохождение теста действует скидка 15% на курс.

А чтобы узнать больше о формате обучения и задать вопросы экспертам, приходите на бесплатные уроки:

• 20 апреля 20:00. «Качество данных (data quality) на практике: от технических метрик до внедрения в команде». Записаться

• 21 апреля 20:00. «Функции в SQL: вычисления и переиспользование кода». Записаться

• 23 апреля 20:00. «Что нового в Spark 4.0». Записаться