CSV в PostgreSQL: быстро и надёжно

Стопка морских контейнеров на терминале грузовой перегрузки

Краткое описание формата CSV

CSV — текстовый формат, где строки соответствуют записям, а запятые (или другие разделители) — столбцам. Удобен для обмена данными и хорошо отображается в реляционных таблицах.

Создание CSV-файла

Ниже — пример CSV, который можно сохранить как /tmp/sample.csv и затем импортировать в PostgreSQL:

id,firstname,lastname,email
1,Riannon,Pulsifer,Riannon.Pulsifer@example.com
2,Moyna,Palocz,Moyna.Palocz@example.com
3,Roslyn,Bearnard,Roslyn.Bearnard@example.com
4,Gloria,Aldric,Gloria.Aldric@example.com
5,Felice,Greenwald,Felice.Greenwald@example.com

Важно: кодировка файла должна быть совместима с сервером (обычно UTF-8). Если есть специальные символы или запятые внутри полей, используйте кавычки и указание CSV с соответствующими опциями.

Создание таблицы в PostgreSQL

Перед импортом создайте таблицу, соответствующую структуре CSV:

CREATE TABLE employees(
  id int NOT NULL,
  firstname char(20),
  lastname char(20),
  email char(50)
);

Совет: лучше использовать varchar или text для имён и email, чтобы избежать проблем с обрезанием пробелов, которые бывают у char.

Импорт CSV в PostgreSQL

Вы можете импортировать файлы на стороне сервера (сервер читает файл напрямую) или на стороне клиента (psql читает файл и отправляет данные серверу).

Импорт на стороне сервера

Команда COPY читает файл, доступный серверу PostgreSQL. Пример:

COPY employees(id,firstname,lastname,email)
FROM '/tmp/sample.csv'
DELIMITER ','
CSV HEADER;

Примечание: путь должен быть доступен серверному процессу PostgreSQL и обычно требуется абсолютный путь. Права доступа к файлу и SELinux/AppArmor могут блокировать чтение.

Импорт на стороне клиента (psql)

Если у вас нет доступа к файловой системе сервера, используйте команду \copy из psql. Файл читается клиентом и данные передаются серверу:

\copy employees FROM '/tmp/sample.csv' DELIMITER ',' CSV HEADER;

\copy работает из интерактивного psql и полезен для локальной загрузки, когда файл хранится на вашей машине.

Экспорт CSV из PostgreSQL

Аналогично импорту, экспорт можно делать на сервере и клиенте.

Экспорт на стороне сервера

COPY с ключевым словом TO записывает данные в файл на сервере:

COPY employees TO '/tmp/employees.csv' CSV HEADER;

Для выборочных строк используйте SELECT внутри COPY:

COPY (SELECT * FROM employees WHERE firstname='Moyna') TO '/tmp/employees-moyna.csv' CSV HEADER;

Экспорт на стороне клиента (psql)

На клиенте используйте \copy TO:

\copy employees TO '/home/user/employees.csv' CSV HEADER;

Эта команда создаст файл на локальной машине, где запущен psql.

Использование адаптера базы данных (пример на Python)

Для автоматизации и сценариев ETL удобнее работать через драйвер. На Python распространённый выбор — psycopg2.

Установка:

pip install psycopg2-binary

Подключение и выполнение COPY серверной команды (сервер должен иметь доступ к файлу) — аккуратно с правами:

import psycopg2

connection = psycopg2.connect(
    dbname='employee_db',
    host='localhost',
    user='postgres',
    password='password'
)

cursor = connection.cursor()
cursor.execute(
    """
    COPY employees(id,firstname,lastname,email)
    FROM '/tmp/sample.csv'
    DELIMITER ','
    CSV HEADER;
    """
)
connection.commit()
cursor.close()
connection.close()

Альтернатива: читать CSV в Python и выполнять batch INSERT или использовать cursor.copy_expert/copy_from для передачи данных из потока.

Практические советы и распространённые ошибки

Права на файл: при server-side COPY файл читает процесс postgres. Убедитесь, что владелец и права корректны.
Кодировка: несоответствие кодировок (например, Windows-1251 против UTF-8) приведёт к ошибкам. Конвертируйте в UTF-8.
Разделитель: не все CSV используют запятую. Укажите DELIMITER ‘;’ или другой символ при необходимости.
Заголовки: указывайте CSV HEADER если первая строка — имена столбцов.
Типы данных: несоответствие типов (например, текст в числовом столбце) вызовет ошибку загрузки.
Большие файлы: для больших импортов COPY обычно быстрее, чем INSERT батчи.

Важно: при массовой загрузке отключение индексов или их отложенная сборка может ускорить импорт, но требует осторожности.

Когда этот подход не работает

Нет доступа к файловой системе сервера и psql недоступен — используйте сетевые каналы (SFTP/HTTP) или ETL-инструмент.
CSV содержит сложные вложенные структуры (JSON внутри ячейки) — возможно, лучше сначала подготовить данные или использовать формат JSON/Parquet.
Требуются транзакционные преобразования при импорте — используйте промежуточные таблицы и проверки целостности.

Мини-методология: шаги для надёжного импорта

Проверка CSV: кодировка, разделитель, заголовки.
Создание/проверка схемы таблицы.
Тестовый импорт маленькой части данных (проверка типов и значений).
Полный импорт через COPY/\copy или адаптер.
Валидация: контрольные суммы строк/количество строк, выборочные проверки.
Индексация и VACUUM/ANALYZE по необходимости.

Сниппет: шпаргалка команд

Server-side import:

COPY table FROM '/path/to/file.csv' CSV HEADER DELIMITER ',';

Client-side import (psql):

\copy table FROM '/local/path/file.csv' CSV HEADER;

Server-side export:

COPY (SELECT * FROM table WHERE condition) TO '/path/out.csv' CSV HEADER;

Client-side export:

\copy (SELECT * FROM table) TO '/local/out.csv' CSV HEADER;

Ролевые контрольные списки

DBA:
- Проверить права доступа и SELinux/AppArmor.
- Настроить пространство для временных файлов.
- Планировать индексацию после загрузки.
Разработчик/инженер данных:
- Автоматизировать импорт через адаптер.
- Логировать ошибки и трансформации.
Аналитик данных:
- Проверить соответствие колонок и кодировки.
- Выполнить пробную загрузку и выборочные проверки.
Пользователь GUI (pgAdmin):
- Использовать мастер импорта/экспорта и проверить настройки разделителя и кодировки.

Критерии приёмки

Все строки из CSV корректно импортированы (количество строк совпадает).
Столбцы имеют ожидаемые типы и значения без усечений.
Нет ошибок при импорте (лог подтверждает успешное завершение).
Производительность удовлетворяет SLA (при больших объёмах измерьте время и нагрузку).

1‑строчный глоссарий

CSV: простой текстовый формат для табличных данных.
COPY: серверная команда PostgreSQL для массовой загрузки/выгрузки.
\copy: клиентская команда psql, обёртка над COPY.
psycopg2: популярный Python-драйвер для PostgreSQL.
pgAdmin: графическая утилита для управления PostgreSQL.

Тестовые случаи

Малый файл (10 строк): успешный импорт без ошибок.
Файл с неверной кодировкой: ожидаемая ошибка кодирования.
Файл с лишними столбцами: проверка на игнорирование/ошибку в зависимости от схемы.
Большой файл (миллионы строк): измерение времени и проверки целостности.

Заключение

Импорт и экспорт CSV в PostgreSQL просты и гибки: COPY и \copy покрывают серверные и клиентские сценарии, а драйверы позволяют автоматизировать процессы. Выбирайте метод, исходя из доступа к файловой системе, объёма данных и требований к автоматизации.

Важно: всегда выполняйте тестовую загрузку и проверку схемы перед массовой операцией.

CSV в PostgreSQL — импорт и экспорт