JavaRush /Курси /SQL SELF /Налаштування роздільників і форматів даних при завантажен...

Налаштування роздільників і форматів даних при завантаженні

SQL SELF

Рівень 24 , Лекція 1

Відкрита

Коли дані приходять з різних систем, їхній формат може відрізнятися. Один файл може використовувати коми як роздільники, інший — крапки з комою, а в третьому табуляція може бути основним способом відділення стовпців. Некоректне налаштування роздільників при завантаженні даних може призвести до помилок або неправильного трактування даних.

Більше того, в реальному житті ти можеш зіткнутися з ситуаціями, коли треба завантажити дані у текстовому форматі без стандартних CSV-заголовків, а також обробити порожні значення і передбачити випадки, коли порожні рядки мають інтерпретуватися як NULL. Тому налаштування роздільників і форматів — це невід’ємна частина роботи з масовим завантаженням даних.

Основні роздільники: як з ними працювати?

У PostgreSQL команда COPY дає гнучкість у налаштуванні роздільників. Давай розберемо, як це працює.

Встановлення роздільників

За замовчуванням команда COPY очікує, що стовпці у CSV-файлі будуть розділені комами. Але це не завжди зручно чи можливо: хтось може використовувати крапки з комою (;), вертикальні риски (|) або навіть табуляцію (\t).

Ось як можна вказати роздільник за допомогою параметра DELIMITER:

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

Якщо замість ком використовується крапка з комою:

COPY students FROM '/path/to/students.csv'
DELIMITER ';'
CSV HEADER;

Можна навіть завантажити файл з табуляцією:

COPY students FROM '/path/to/students.tsv'
DELIMITER E'\t' 
CSV HEADER;

Тут E'\t' означає, що роздільником буде символ табуляції.

Завантаження файлів з нестандартним роздільником

Практичний приклад: у тебе є файл з даними про курси, де використовуються вертикальні риски (|) як роздільники. Дані у файлі виглядають так:

course_id|course_name|credits
1|SQL Basics|3
2|Advanced SQL|4
3|PostgreSQL Masterclass|5

Ось як можна завантажити ці дані в таблицю courses:

COPY courses(course_id, course_name, credits)
FROM '/path/to/courses.csv' 
DELIMITER '|' 
CSV HEADER;

Тут ми явно вказуємо PostgreSQL, що роздільником є вертикальна риска.

Налаштування форматів даних при завантаженні

Роздільники — це лише частина задачі. Формат даних у файлі теж грає важливу роль. Давай розглянемо основні способи налаштування форматів даних.

Ігнорування порожніх рядків і задання `NULL`

Часто у великих масивах даних трапляються порожні рядки або стовпці, які не містять даних. PostgreSQL сприймає їх як порожні рядки, якщо не вказано інакше. Щоб трактувати такі значення як NULL, можна використати параметр NULL AS:

Приклад. Припустимо, у твоєму файлі є записи з порожніми значеннями:

id,first_name,last_name,email
1,John,Doe,
2,Jane,Smith,jane.smith@example.com
3,,Brown,james.brown@example.com

Завантаження даних з інтерпретацією порожніх значень у стовпці email як NULL:

COPY students(id, first_name, last_name, email)
FROM '/path/to/students.csv'
DELIMITER ',' 
CSV HEADER 
NULL AS '';

У результаті порожні значення у файлі будуть збережені як NULL у таблиці.

Ігнорування порожніх рядків

Іноді файл може містити порожні рядки, які не треба завантажувати. PostgreSQL дозволяє ігнорувати такі рядки автоматично.

Приклад. Файл з порожнім рядком:

id,first_name,last_name,email
1,John,Doe,john.doe@example.com

2,Jane,Smith,jane.smith@example.com

Використовуємо параметр IGNORE_BLANK_LINES:

COPY students(id, first_name, last_name, email)
FROM '/path/to/students.csv'
DELIMITER ',' 
CSV HEADER 
NULL AS '' 
IGNORE_BLANK_LINES;

Тепер порожні рядки будуть ігноруватися при завантаженні.

Робота з нестандартним форматом даних

Іноді тобі може знадобитися завантажити дані, представлені у текстовому форматі, замість CSV. Наприклад, рядки у файлі розділені вертикальними рисками |, а дані не мають рядка заголовка.

Приклад файлу:

1|John|Doe|john.doe@example.com
2|Jane|Smith|jane.smith@example.com

У цьому випадку можна використати такий запит:

COPY students(id, first_name, last_name, email)
FROM '/path/to/students.txt' 
DELIMITER '|' 
NULL AS '' 
CSV;

Якщо у файлі немає заголовків, просто прибери параметр HEADER.

Практичний приклад налаштування форматів даних

Сценарій: у тебе є файл grades.tsv, що містить оцінки студентів. Дані виглядають так:

student_id  course_id   grade
1   101     85
1   102     90
2   101     78
2   102     88
3   101     95
3   102

Потрібно:

Завантажити файл з правильною інтерпретацією порожніх значень як NULL.
Переконатися, що дані завантажені коректно.

Рішення:

Створи таблицю grades:

CREATE TABLE grades (
    student_id INTEGER NOT NULL,
    course_id INTEGER NOT NULL,
    grade INTEGER
);

Завантаж дані з файлу:

COPY grades(student_id, course_id, grade)
FROM '/path/to/grades.tsv' 
DELIMITER E'\t' 
NULL AS '' 
CSV HEADER;

Перевір завантажені дані:

SELECT * FROM grades;

Очікуваний результат:

student_id	course_id	grade
1	101	85
1	102	90
2	101	78
2	102	88
3	101	95
3	102	NULL