Операции импорта и экспорта MySQL

Введение

В этой лабораторной работе вы освоите основные навыки импорта и экспорта данных в базе данных MySQL. Вы попрактикуетесь в загрузке данных из файла CSV (Comma-Separated Values) в таблицу с использованием команды LOAD DATA INFILE — быстрого и эффективного метода массовой вставки данных.

Вы также изучите обратный процесс: экспорт данных из таблицы в новый файл CSV. Кроме того, лабораторная работа охватывает выполнение базовых проверок валидации данных после импорта для обеспечения качества данных. К концу этой лабораторной работы вы будете уверенно перемещать данные в MySQL и из него.

Подготовка базы данных и таблицы

Прежде чем импортировать данные, вам нужно место назначения для них. Это включает создание базы данных для хранения ваших данных и таблицы со структурой, соответствующей данным, которые вы намерены импортировать.

Сначала откройте терминал на вашем рабочем столе.

Подключитесь к серверу MySQL от имени пользователя root. В этой лабораторной среде вы можете использовать sudo для подключения без пароля.

sudo mysql -u root

После подключения вы увидите приглашение MySQL (mysql>), которое означает, что вы теперь взаимодействуете непосредственно с сервером базы данных.

Далее создайте новую базу данных с именем company. Фраза IF NOT EXISTS является хорошей практикой, которая предотвращает ошибку, если база данных уже была создана.

CREATE DATABASE IF NOT EXISTS company;

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

USE company;

Наконец, создайте таблицу с именем employees для хранения данных сотрудников. Структура таблицы должна соответствовать столбцам в файле CSV, который вы импортируете позже.

CREATE TABLE IF NOT EXISTS employees (
    id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    department VARCHAR(50)
);

• INT PRIMARY KEY: Определяет столбец id как целочисленный тип и первичный ключ, что означает, что каждое значение должно быть уникальным.
• VARCHAR(50): Определяет столбец, который может хранить строку переменной длины до 50 символов.

Вы можете проверить, успешно ли была создана таблица, выполнив:

SHOW TABLES;

В выводе вы должны увидеть таблицу employees.

+-------------------+
| Tables_in_company |
+-------------------+
| employees         |
+-------------------+
1 row in set (0.00 sec)

Оставьте оболочку MySQL открытой, так как вы продолжите использовать ее на следующем шаге.

Импорт данных из CSV-файла

После подготовки базы данных и таблицы вы можете импортировать данные из внешнего файла. Оператор LOAD DATA INFILE является высокоэффективным способом массовой загрузки данных из текстового файла в таблицу.

Скрипт настройки для этой лабораторной работы уже создал файл с именем employees.csv в каталоге /tmp. Перед импортом рекомендуется проверить содержимое файла.

Важно: Для выполнения этой команды вам потребуется открыть новую вкладку терминала, так как ваш текущий терминал запущен в оболочке MySQL. Нажмите значок + в окне терминала, чтобы открыть новую вкладку. В новом терминале выполните:

cat /tmp/employees.csv

Вывод показывает четыре строки данных, разделенных запятыми:

1,John,Doe,john.doe@example.com,Sales
2,Jane,Smith,jane.smith@example.com,Marketing
3,Peter,Jones,peter.jones@example.com,Engineering
4,Mary,Brown,mary.brown@example.com,HR

Теперь вернитесь на исходную вкладку терминала с оболочкой MySQL (mysql>). Используйте команду LOAD DATA INFILE для импорта файла.

LOAD DATA INFILE '/tmp/employees.csv'
INTO TABLE employees
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

Разберем эту команду:

• LOAD DATA INFILE '/tmp/employees.csv': Указывает полный абсолютный путь к исходному файлу.
• INTO TABLE employees: Указывает целевую таблицу для данных.
• FIELDS TERMINATED BY ',': Сообщает MySQL, что запятые разделяют поля данных (столбцы) в каждой строке.
• LINES TERMINATED BY '\n': Сообщает MySQL, что каждая новая строка в файле представляет собой новую запись.

После выполнения команды MySQL сообщит количество импортированных строк. Чтобы убедиться, что импорт прошел успешно, выполните запрос к таблице, чтобы увидеть ее содержимое.

SELECT * FROM employees;

Вывод должен отобразить четыре записи из файла CSV, теперь хранящиеся в вашей таблице employees.

+----+------------+-----------+---------------------------+-------------+
| id | first_name | last_name | email                     | department  |
+----+------------+-----------+---------------------------+-------------+
|  1 | John       | Doe       | john.doe@example.com      | Sales       |
|  2 | Jane       | Smith     | jane.smith@example.com    | Marketing   |
|  3 | Peter      | Jones     | peter.jones@example.com   | Engineering |
|  4 | Mary       | Brown     | mary.brown@example.com    | HR          |
+----+------------+-----------+---------------------------+-------------+
4 rows in set (0.00 sec)

Экспорт результатов запроса в CSV-файл

Экспорт данных так же важен, как и импорт. Возможно, вам потребуется создавать отчеты, передавать данные другим системам или выполнять анализ в программе для работы с электронными таблицами. Оператор SELECT ... INTO OUTFILE позволяет сохранить результат любого запроса непосредственно в файл.

Сначала добавим еще двух сотрудников в таблицу в вашей оболочке MySQL.

INSERT INTO employees (id, first_name, last_name, email, department) VALUES
(5, 'Alice', 'Johnson', 'alice.johnson@example.com', 'Sales'),
(6, 'Bob', 'Williams', 'bob.williams@example.com', 'Marketing');

Теперь экспортируем всю таблицу employees в новый файл с именем employees_export.csv. Сначала убедитесь, что вы находитесь в правильной базе данных:

SELECT id, first_name, last_name, email, department
FROM company.employees
INTO OUTFILE '/tmp/employees_export.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

• SELECT ...: Это стандартный запрос, который определяет, какие данные экспортировать.
• INTO OUTFILE '/tmp/employees_export.csv': Указывает полный путь к выходному файлу. В целях безопасности MySQL требует, чтобы этот файл еще не существовал.
• FIELDS TERMINATED BY ',': Разделяет поля запятой.
• ENCLOSED BY '"': Оборачивает каждое значение поля в двойные кавычки, что является распространенным форматом CSV.
• LINES TERMINATED BY '\n': Завершает каждую строку символом новой строки.

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

cat /tmp/employees_export.csv

Вы увидите все шесть строк из вашей таблицы, отформатированных как CSV-файл.

"1","John","Doe","john.doe@example.com","Sales"
"2","Jane","Smith","jane.smith@example.com","Marketing"
"3","Peter","Jones","peter.jones@example.com","Engineering"
"4","Mary","Brown","mary.brown@example.com","HR"
"5","Alice","Johnson","alice.johnson@example.com","Sales"
"6","Bob","Williams","bob.williams@example.com","Marketing"

Проверка импортированных данных

После импорта данных крайне важно проверить их, чтобы обеспечить качество и целостность. Реальные данные часто бывают "грязными", содержат ошибки, пропущенные значения или некорректные форматы. Этот шаг покажет вам, как использовать простые SQL-запросы для поиска распространенных проблем.

Скрипт настройки создал файл employees_validation.csv, который содержит недопустимый адрес электронной почты и пропущенное значение отдела. Сначала очистите таблицу employees в вашей оболочке MySQL.

TRUNCATE TABLE employees;

Теперь импортируйте файл для проверки.

LOAD DATA INFILE '/tmp/employees_validation.csv'
INTO TABLE employees
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

После загрузки "грязных" данных выполним несколько проверок.

1. Поиск недопустимых форматов электронной почты

Очень простая проверка допустимого адреса электронной почты заключается в том, чтобы убедиться, что он содержит символ @ и символ .. Мы можем использовать NOT LIKE для поиска строк, которые не прошли эту проверку.

SELECT * FROM employees WHERE email NOT LIKE '%@%.%';

Этот запрос найдет строку, где адрес электронной почты invalid_email, поскольку в нем отсутствуют необходимые символы.

+----+------------+-----------+---------------+------------+
| id | first_name | last_name | email         | department |
+----+------------+-----------+---------------+------------+
|  3 | Invalid    | Email     | invalid_email | Sales      |
+----+------------+-----------+---------------+------------+
1 row in set (0.00 sec)

2. Поиск пропущенных отделов

Вы можете найти строки, где значение отсутствует, проверив пустую строку ''.

SELECT * FROM employees WHERE department = '';

Этот запрос найдет строку, где отдел был оставлен пустым в файле CSV.

+----+------------+------------+--------------------------------+------------+
| id | first_name | last_name  | email                          | department |
+----+------------+------------+--------------------------------+------------+
|  4 | Missing    | Department | missing.department@example.com |            |
+----+------------+------------+--------------------------------+------------+
1 row in set (0.00 sec)

Эти простые запросы являются мощными инструментами для первичной проверки качества данных. После выявления проблемных строк вы можете решить, исправить ли их с помощью операторов UPDATE или удалить с помощью DELETE.

Вы завершили лабораторную работу. Вы можете выйти из оболочки MySQL.

exit

Резюме

В этой лабораторной работе вы изучили основные операции по перемещению данных в базу данных MySQL и из нее. Вы начали с настройки правильной среды базы данных с новой базой данных и таблицей. Затем вы использовали команду LOAD DATA INFILE для эффективного импорта данных из CSV-файла.

Далее вы отработали экспорт данных из таблицы в новый CSV-файл с помощью оператора SELECT ... INTO OUTFILE, что является распространенной задачей для создания отчетов и обмена данными. Наконец, вы узнали, как выполнять базовую проверку данных с помощью SQL-запросов для поиска ошибок форматирования и пропущенных значений после импорта. Эти навыки необходимы любому разработчику или администратору, работающему с MySQL.