Імпорт даних із CSV-файлів за допомогою команди `COPY`

Привіт, друзі! Уяви, що в тебе є Excel-таблиця на 10 000 рядків, і шеф каже: "Завантаж це в базу!" Хмм... якщо ти спробуєш вставляти дані вручну, то… скоріш за все, тобі знадобляться не лише SQL-скіли, а й відпустка. 😅

На щастя, у PostgreSQL є команда, яка працює як телепорт із CSV у базу — це COPY. Вона завантажує дані миттєво, масово і без зайвого шуму. Сьогодні розберемося, як працює COPY, як підготувати дані, і що робити, якщо у файлі раптом з'явиться "плаваюча кома" чи нестандартний символ. Поїхали!

Основи команди COPY

COPY — це команда PostgreSQL, яка дозволяє переміщати дані між таблицею та файловою системою. Вона корисна для масового завантаження даних у базу або для їх експорту.

Приклад синтаксису COPY для завантаження даних у таблицю:

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

Основні параметри:

• FROM 'path/to/your/file.csv': визначає шлях до твого CSV-файлу.
• DELIMITER ',': вказує символ-роздільник (у цьому випадку це кома).
• CSV HEADER: означає, що у CSV-файлі є заголовок (перший рядок з іменами стовпців).

Команда COPY працює напряму на сервері, що робить її надзвичайно швидкою при роботі з великими об'ємами даних.

Різниця між COPY та \COPY

Іноді можна натрапити на невеличку плутанину між COPY і \COPY.

• COPY: виконується на стороні сервера. Використовується для завантаження файлів, які знаходяться на сервері.
• \COPY: клієнтська команда, доступна в psql. Завантаження даних відбувається з твого локального комп'ютера.

Якщо ти працюєш на локальній машині, скоріш за все, будеш використовувати \COPY. Про неї поговоримо трохи пізніше.

Приклад використання COPY

Давай одразу перейдемо до прикладу, щоб на практиці краще зрозуміти принцип роботи.

Крок 1: Підготовка таблиці

Уяви, що ми створюємо таблицю для зберігання інформації про студентів:

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    date_of_birth DATE,
    grade FLOAT
);

Крок 2: Підготовка CSV-файлу

Формат твого CSV-файлу має збігатися з таблицею. Приклад файлу students.csv:

first_name,last_name,date_of_birth,grade
John,Doe,2001-05-15,85.5
Jane,Smith,2000-12-22,90.0
Alice,Johnson,2002-03-10,78.0
Bob,Brown,2001-08-30,NULL

Зверни увагу: порядок стовпців у CSV має відповідати стовпцям, вказаним у таблиці, а типи даних повинні збігатися (наприклад, дата має бути у форматі YYYY-MM-DD).

Крок 3: Завантаження даних

Щоб завантажити файл students.csv у таблицю students, використовуй таку команду:

COPY students (first_name, last_name, date_of_birth, grade)
FROM '/path/to/your/students.csv'
DELIMITER ',' 
CSV HEADER;

Що тут відбувається?

• Ми вказуємо ім'я таблиці students і стовпці, у які завантажуються дані.
• Шлях до файлу вказує на його місцезнаходження.
• Роздільник , означає, що дані розділені комами.
• Параметр CSV HEADER повідомляє PostgreSQL, що перший рядок файлу містить назви стовпців.

Результат:

Після виконання команди дані з файлу завантажуються в таблицю, і ти можеш одразу перевірити їх:

SELECT * FROM students;

Обмеження та особливості

Щоб уникнути помилок, переконайся, що твій CSV-файл відповідає таким вимогам:

1. Відсутність прихованих символів або пробілів у рядку заголовка.
2. Використання правильної кодування: у PostgreSQL зазвичай рекомендується UTF-8.
3. Збіг роздільника та вказаного параметра DELIMITER.

Типові помилки при завантаженні COPY:

Невідповідність структури даних. Наприклад, якщо ти намагаєшся завантажити текстове значення в числове поле:

ERROR: invalid input syntax for type numeric: "abc"

Щоб уникнути таких проблем, переконайся, що дані відповідають очікуваному типу.

Помилка шляху до файлу.

Якщо файл знаходиться поза доступною директорією PostgreSQL на сервері, ти отримаєш помилку:

ERROR: could not open file "/path/to/your/file.csv" for reading: Permission denied

Рішенням може бути правильне налаштування прав доступу до файлу.

Як перевірити, що дані завантажені коректно?

Після завантаження даних корисно впевнитися, що все пройшло успішно. Наприклад:

Перевірка загальної кількості рядків:

SELECT COUNT(*) FROM students;

Пошук порожніх значень (наприклад, якщо поле grade містить NULL):

SELECT * FROM students WHERE grade IS NULL;

Корисні поради для роботи з COPY

Логуйте помилки. Якщо хочеш записувати помилки в окремий файл, використовуй параметр LOG ERRORS. (У PostgreSQL версії 12+).

Вимикайте індекси та тригери.

При завантаженні великих об'ємів даних можна тимчасово вимкнути індекси:

ALTER TABLE students DISABLE TRIGGER ALL;

Використовуйте транзакції.

Це дозволяє завантажити дані "атомарно" — або вони завантажаться повністю, або відкотяться у разі помилки:

BEGIN;
COPY students FROM '/path/to/your/file.csv' CSV HEADER;
COMMIT;

Практичне застосування

Вміння користуватися командою COPY стане у пригоді не лише адміністраторам баз даних — розробникам вона теж може добряче спростити життя. Уяви, що ти отримуєш великий об'єм даних зі стороннього API: спочатку зберігаєш їх у CSV-файл, а потім швидко завантажуєш у PostgreSQL — і можна починати аналіз. Або, скажімо, колеги просять перенести їхню стару базу в нову систему на PostgreSQL. У таких ситуаціях COPY стає справжньою паличкою-виручалочкою.

CSV-файли трапляються в реальних проєктах постійно. Наприклад:

• Ти завантажуєш статистику продажів в аналітичну систему.
• Імпортуєш список користувачів із зовнішньої CRM.
• Переносиш дані з Excel у PostgreSQL без зайвих танців з бубном.

На цьому ми завершуємо знайомство з командою COPY. У наступній лекції поговоримо про те, як підготувати таблиці для завантаження даних і продумати їхню структуру так, щоб усе працювало швидко й надійно. А поки не бійся експериментувати — COPY відкриває справді зручні можливості для роботи з великими об'ємами інформації.