DF-АВТ-011 · Автоматизация процессов
Выгрузка большой таблицы в CSV: как не потерять строки
- Чем потоковая выгрузка отличается от загрузки всей таблицы в память
- Какие параметры COPY и клиента зафиксировать до запуска
- Как отличить NULL от пустой строки в CSV
- Как проверить полноту строк после выгрузки
Выгрузка на десять тысяч строк проходит без замечаний, а на миллионе файл приходит обрезанным или с пустыми колонками. Причина обычно не в объёме, а в параметрах, которые никто не зафиксировал: кодировке, разделителе, кавычках и способе отличить NULL от пустой строки.
Ниже — порядок действий: выбрать потоковую выгрузку средствами базы, задать параметры формата до запуска и проверить полноту строк до передачи файла. Примеры в статье синтетические, учебные: это не клиентский кейс и не замер экономии. Смежная тема — как проверить полноту CSV с торговыми операциями, а варианты работ с данными собраны на странице автоматизации процессов.
Почему большая таблица ломает наивную выгрузку
Наивный путь — прочитать всю таблицу в память приложения и записать её в файл. На небольшой таблице это работает, на большой упирается в память и время. Потоковая выгрузка отдаёт строки порциями, и приложение не держит весь набор одновременно.
В PostgreSQL поток даёт команда COPY. При успешном завершении она возвращает тег команды вида COPY count, где count — число скопированных строк. Это число и есть первая проверка полноты: его можно сверить с ожидаемым количеством.
Есть оговорка: psql не печатает этот тег для COPY ... TO STDOUT, поэтому при потоковом выводе в клиент счётчик может быть не виден. Тогда полноту проверяют отдельно, о чём ниже.
Ещё одна ловушка — тип отношения. COPY TO не поддерживает напрямую секционированные таблицы, дочерние таблицы наследования и представления. Для них нужно использовать COPY (SELECT * FROM table) TO, иначе выгрузка просто не запустится.
Потоковая выгрузка средствами базы: COPY и клиентский \copy
У COPY два режима: файл на стороне сервера и поток в клиент. Для COPY TO с именем файла сервер требует абсолютный путь к выходному файлу — относительный путь здесь не подойдёт.
Если файл нужен на машине разработчика, а не на сервере, работает клиентская команда psql \copy: она пишет файл на стороне клиента. Это удобно, когда доступ к файловой системе сервера закрыт.
В формате CSV символ разделителя по умолчанию — запятая. Это стоит помнить: в текстовом формате по умолчанию табуляция, и путаница между режимами даёт файл, который «не открывается» в таблице.
Для больших наборов в приложении на Python есть свой поток: в pandas параметр chunksize у read_csv возвращает объект TextFileReader для итерации по частям файла. Так файл читают порциями, не загружая целиком.
Кодировка и разделители: что зафиксировать до запуска
Кодировку задаёт опция ENCODING: она указывает, в какой кодировке записан файл, а при её отсутствии используется текущая клиентская кодировка. Именно поэтому файл, снятый с одной машины, может открыться «кракозябрами» на другой — кодировка не была задана явно.
В pandas кодировку задаёт параметр encoding при чтении и записи текстовых данных. Если выгрузка и последующее чтение идут разными инструментами, кодировку нужно согласовать на обоих концах.
Разделитель лучше задавать явно, а не полагаться на автоопределение. В pandas параметр sep=None включает определение разделителя с помощью csv.Sniffer, и работает это только на движке Python. Автоопределение смотрит на первую валидную строку и на неоднородном файле может ошибиться.
Отдельно про сам формат. RFC 4180 описывает CSV как информационный меморандум и не задаёт интернет-стандарт. Практические реализации расходятся, поэтому параметры фиксируют в описании выгрузки, а не надеются на «общепринятость».
Кавычки, NULL и пустая строка в CSV
Самое частое расхождение после выгрузки — пустые ячейки. В CSV-формате PostgreSQL отличает NULL от пустой строки с помощью кавычек: NULL выводится без кавычек, а не-NULL значение, совпадающее со строкой NULL, — в кавычках. Если это правило не учитывать при чтении, NULL и пустая строка сливаются в одно.
По RFC 4180 поля, содержащие переводы строк, двойные кавычки и запятые, должны быть заключены в двойные кавычки. Двойная кавычка внутри такого поля экранируется предшествующей двойной кавычкой.
В CSV-формате PostgreSQL все символы значимы: значение в кавычках, окружённое пробелами, включает эти пробелы. Лишний пробел после разделителя не «съедается» — он попадёт в данные.
Практический вывод: если в текстовых полях бывают запятые, кавычки и переводы строк, проверьте на образце, как их видит потребитель файла. Ошибка здесь проявляется не сразу, а на строках с «неудобным» содержимым.
Заголовок и окончание файла: расхождения между RFC и реализациями
Заголовок — ещё один параметр, который нужно решить до запуска. Опция HEADER в COPY добавляет в первую строку вывода имена столбцов таблицы. Если потребитель файла ждёт заголовок, а его нет, колонки сдвинутся.
По RFC 4180 каждая запись располагается на отдельной строке, разделённой CRLF. При этом последняя запись в файле может не иметь завершающего перевода строки — это разрешено, а не ошибка.
RFC 4180 регистрирует MIME-тип text/csv с необязательными параметрами charset и header. Регистрация типа не делает сам формат строгим стандартом: реализации по-прежнему могут отличаться.
Поэтому заголовок, разделитель строк и наличие финального перевода строки фиксируют в описании файла. Тогда принимающая сторона знает, чего ждать, и не угадывает.
Проверка полноты строк после выгрузки
Полноту проверяют до передачи файла, а не после жалобы получателя. Первый ориентир — тег COPY count: число скопированных строк сверяют с ожидаемым количеством.
Второй шаг — чтение с контролем. В pandas по умолчанию строки с слишком большим числом полей вызывают ошибку, а параметр on_bad_lines позволяет пропускать такие строки. Пропуск — это потеря данных, поэтому для проверки полноты он не подходит: лучше увидеть ошибку.
Слишком малое число полей ведёт себя иначе: в pandas недостающие значения заполняются NA. Это удобно для расчётов, но маскирует неполноту строки — заполненное NA не означает, что данные были.
Как это выглядит на синтетических строках
Возьмём четыре учебные строки с полями id, amount и currency. В каждой не хватает хотя бы одного значения: у второй пустой amount, у третьей пустая currency, у четвёртой пустой id. Проверка обязательных полей помечает все четыре строки как невалидные — так неполнота видна до передачи файла, а не после.
Этот результат получен локальным обработчиком проверки обязательных полей на синтетических данных: он читает строки и помечает невалидные. Проверена только учебная логика проверки обязательных полей — не поведение вашей базы, не ваш клиент выгрузки и не права доступа реального сервиса.
Для больших файлов чтение с проверкой удобно совмещать с порциями: в pandas параметр usecols позволяет вернуть подмножество столбцов и при использовании C-движка ускоряет разбор и снижает потребление памяти. Так проверяют только нужные колонки, не поднимая весь файл.
Чек-лист перед передачей файла
Соберите параметры выгрузки в одном месте и сверьте их до запуска. Кодировка задана явно (ENCODING в COPY, encoding в pandas), а не оставлена на клиентскую по умолчанию.
Разделитель зафиксирован, а не отдан автоопределению. Для COPY TO с файлом на сервере указан абсолютный путь; для секционированных таблиц, дочерних таблиц наследования и представлений используется COPY (SELECT * FROM table) TO.
Правило различения NULL и пустой строки проверено на образце. Поля с запятыми, кавычками и переводами строк заключены в двойные кавычки, внутренняя кавычка удвоена.
Если в pandas включён on_bad_lines, строки с лишними полями будут молча пропущены — для проверки это недопустимо. А неполнота строк, замаскированная заполнением NA, — не повод считать файл полным: заполненные NA означают, что значений не было, и это стоит показать получателю явно.