Загрузите данные .CSV в MySQL и объедините дату и время

Предварительное условие — как импортировать метку времени из CSV-файла в MySQL?

Файл CSV (значения, разделенные запятыми) представляет собой текстовый файл с информацией, разделяемой запятыми. Они чаще всего встречаются в электронных таблицах и базах данных и в настоящее время в основном используются для наборов данных в науке о данных и машинном обучении. Они помогают предприятиям экспортировать большие объемы данных в более крупное озеро данных. Однако мы не можем выполнять запросы SQL к таким данным CSV, поэтому мы должны преобразовать их в структурированные таблицы.

Мы найдем способ преобразовать данные CSV в структурированную таблицу, объединив атрибуты даты и времени из приведенного выше примера файла .csv с помощью командной строки . Мы достигаем этой цели, используя метод под названием Трансформация .

Импорт файлов .CSV в таблицы MYSQL:

Шаг 1: Откройте окно терминала и войдите в клиент MySQL, используя пароль. Обратитесь к следующей команде:

mysql -u root -p

Шаг 2: Создайте базу данных, а затем создайте таблицу внутри этой базы данных. Данные файла CSV будут входными данными для этой таблицы:

#To create a database
CREATE DATABASE database_name;

#To use that database
use database_name;

#To create a table
CREATE TABLE table_name(
id INTEGER,
col_1 VARCHAR(100),
col_2 INTEGER,
col_3 DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id)
);

Мы создаем базу данных и таблицу с именем table_name внутри нее. Таблица содержит различные атрибуты, такие как id, col_1, col_2 и col_3. Атрибут col_3 имеет тип DATETIME и содержит как данные, так и время в формате ГГГГ-ММ-ДД чч:мм:сс. В дополнение к атрибутам integer и varchar мы рассмотрим, как импортировать атрибут типа DateTime из файла CSV в таблицу MySQL.

Шаг 3: Теперь мы должны проверить переменную MySQL «secure_file_priv». По умолчанию сервер MySQL запускается с параметром -secure-file-priv. При использовании LOAD DATA INFILE он указывает каталог, из которого файлы данных могут быть загружены в данный экземпляр базы данных. Чтобы просмотреть настроенный каталог, используйте следующую команду:

SHOW VARIABLES LIKE ‘secure_file_priv’;

Выход:

Шаг 4: Переместите входной CSV-файл в указанную структуру папок. Нам нужно будет поместить входной CSV-файл в указанную структуру папок, и только тогда мы сможем получить доступ к файлу для загрузки в базу данных. Как только мы это сделаем, теперь мы готовы запустить команду для импорта CSV-файла в базу данных. Обратитесь к следующей команде:

LOAD DATA INFILE "{folder_structure}/{csv_file_name}"
INTO TABLE table_name
FIELDS TERMINATED BY ","
ENCLOSED BY """
LINES TERMINATED BY "
"
IGNORE 1 ROWS
(id, col_1, col_2, @date, @time)
SET col_3 = timestamp(str_to_date(@date,"%Y-%m-%d"), 
str_to_date(@time, "%H:%i:%s"));

1. LOAD DATA INFILE — указывает расположение входного CSV-файла.
2. INTO TABLE — указывает таблицу, в которую должны быть помещены данные.
3. FIELDS TERMINATED BY — указывает разделитель, с помощью которого разделяются отдельные значения в файле.
4. ENCLOSED BY — указывает символ, который указывает значения в файле CSV.
5. LINES TERMINATED BY — указывает код разрыва строки.
6. IGNORE 1 ROWS — указывает количество игнорируемых строк во входном CSV-файле, который может содержать метки столбцов и т. д.

Последние две строки, однако, являются наиболее важными в этой задаче. Каждый из столбцов, соответствующих нашим входным данным, был назван (id, col 1, col 2, @date, @time). Когда имени столбца предшествует знак @, оно преобразуется в столбец, представленный локальной переменной. Предполагается, что другие имена столбцов соответствуют столбцам базы данных, даже если столбцы базы данных не всегда находятся в одном и том же порядке. Предложение SET позволяет выполнять преобразования предварительной обработки значений этих локальных переменных перед назначением результата столбцам.

Оператор SET указывает MySQL заполнить столбец col_3 типом данных timestamp из нашего файла .csv путем объединения столбцов даты и времени. Функция отметки времени в MySQL преобразует заданные даты и время в формат DateTime. Чтобы преобразовать наши строки даты и времени в различные типы данных MySQL, мы используем функцию str_to_date() . Он принимает строку даты/времени, а также ее формат в качестве аргумента.

Выход:

Узнайте больше о функции str_to_date() здесь и о том, как загрузить локально сохраненный файл .csv в таблицу MySQL здесь.

Шаг 5. Следуя описанным выше шагам, мы можем импортировать CSV-файл в таблицу MySQL без каких-либо ошибок. Следующие команды можно использовать для проверки импортированных данных, а также для определения того, правильно ли импортирован атрибут dob с типом метки времени. Таким образом, мы можем гарантировать, что атрибут в таблице соответствует фактическому типу данных DateTime MySQL:

SELECT * FROM table_name;
SELECT * FROM table_name WHERE 
col_3 = TIMESTAMP("{YYYY-MM-DD HH:MM:SS}");

Выход: