Настройка разделителей и форматов данных при загрузке

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

Мало того, в реальной жизни вы можете столкнуться с ситуациями, когда требуется загрузить данные в текстовом формате без стандартных 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 

Требуется:

1. Загрузить файл с правильной интерпретацией пустых значений как NULL.
2. Убедиться, что данные загружены корректно.

Решение:

1. Создайте таблицу grades:

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

2. Загрузите данные из файла:

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

3. Проверьте загруженные данные:

SELECT * FROM grades;

Ожидаемый результат:

Рекомендации и типичные ошибки

Ошибка: несоответствие разделителя. Если вы не укажете правильный разделитель, PostgreSQL выдаст ошибку или загрузит данные некорректно. Например, если в файле используется точка с запятой, но вы забыли указать DELIMITER ';', команда COPY воспримет всю строку как один столбец.

Ошибка: неправильная интерпретация NULL. Если не указать параметр NULL AS '', пустые значения в файле могут быть интерпретированы как пустые строки, что часто вызывает ошибки в расчетах или фильтрации.

Ошибка: неправильный формат данных. Некорректная установка разделителя или ошибок в файле (например, использование табуляции вместо пробелов) может привести к ошибке вида: ERROR: null value in column violates not-null constraint.

2

Задача

SQL SELF, 24 уровень, 1 лекция

Недоступна

Установка нестандартного разделителя при загрузке

Установка нестандартного разделителя при загрузке

Открыть