Operaciones de Importación y Exportación en MySQL

Introducción

En este laboratorio, aprenderá las habilidades esenciales para importar y exportar datos en una base de datos MySQL. Practicará la carga de datos desde un archivo CSV (Comma-Separated Values) a una tabla utilizando el comando LOAD DATA INFILE, un método rápido y eficiente para la inserción masiva de datos.

También aprenderá el proceso inverso: exportar datos de una tabla a un nuevo archivo CSV. Además, el laboratorio cubre la realización de comprobaciones básicas de validación de datos después de una importación para garantizar la calidad de los datos. Al final de este laboratorio, será competente en la transferencia de datos dentro y fuera de MySQL.

Preparar la Base de Datos y la Tabla

Antes de poder importar datos, necesita un destino para ellos. Esto implica crear una base de datos para albergar sus datos y una tabla con una estructura que coincida con los datos que pretende importar.

Primero, abra la terminal desde su escritorio.

Conéctese al servidor MySQL como usuario root. En este entorno de laboratorio, puede usar sudo para conectarse sin contraseña.

sudo mysql -u root

Una vez conectado, verá el prompt de MySQL (mysql>), que indica que ahora está interactuando directamente con el servidor de la base de datos.

A continuación, cree una nueva base de datos llamada company. La cláusula IF NOT EXISTS es una buena práctica que evita un error si la base de datos ya ha sido creada.

CREATE DATABASE IF NOT EXISTS company;

Ahora, cambie a su base de datos recién creada para que todos los comandos subsiguientes se apliquen a ella.

USE company;

Finalmente, cree una tabla llamada employees para almacenar los datos de los empleados. La estructura de la tabla debe coincidir con las columnas del archivo CSV que importará más adelante.

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: Define la columna id como un entero y clave primaria, lo que significa que cada valor debe ser único.
• VARCHAR(50): Define una columna que puede almacenar una cadena de longitud variable de hasta 50 caracteres.

Puede verificar que la tabla se creó correctamente ejecutando:

SHOW TABLES;

Debería ver la tabla employees listada en la salida.

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

Mantenga abierta la shell de MySQL, ya que continuará utilizándola en el siguiente paso.

Importar Datos desde un Archivo CSV

Con la base de datos y la tabla listas, ahora puede importar datos desde un archivo externo. La sentencia LOAD DATA INFILE es una forma muy eficiente de cargar datos masivamente desde un archivo de texto a una tabla.

El script de configuración para este laboratorio ya ha creado un archivo llamado employees.csv en el directorio /tmp. Antes de importar, es una buena práctica inspeccionar el contenido del archivo.

Importante: Necesitará abrir una nueva pestaña de terminal para este comando, ya que su terminal actual está ejecutando la shell de MySQL. Haga clic en el icono + en la ventana de la terminal para abrir una nueva pestaña. En la nueva terminal, ejecute:

cat /tmp/employees.csv

La salida muestra cuatro líneas de datos separados por comas:

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

Ahora, vuelva a su pestaña de terminal original con la shell de MySQL (mysql>). Utilice el comando LOAD DATA INFILE para importar el archivo.

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

Analicemos este comando:

• LOAD DATA INFILE '/tmp/employees.csv': Especifica la ruta completa y absoluta al archivo de origen.
• INTO TABLE employees: Especifica la tabla de destino para los datos.
• FIELDS TERMINATED BY ',': Indica a MySQL que las comas separan los campos de datos (columnas) en cada fila.
• LINES TERMINATED BY '\n': Indica a MySQL que cada nueva línea en el archivo representa una nueva fila.

Después de que el comando se ejecute, MySQL informará el número de filas importadas. Para verificar que la importación fue exitosa, consulte la tabla para ver su contenido.

SELECT * FROM employees;

La salida debería mostrar los cuatro registros del archivo CSV, ahora almacenados en su tabla 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)

Exportar Resultados de Consulta a un Archivo CSV

Exportar datos es tan importante como importarlos. Es posible que necesite crear informes, compartir datos con otros sistemas o realizar análisis en un programa de hojas de cálculo. La sentencia SELECT ... INTO OUTFILE le permite guardar el resultado de cualquier consulta directamente en un archivo.

Primero, agreguemos dos empleados más a la tabla en su shell de 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');

Ahora, exporte toda la tabla employees a un nuevo archivo llamado employees_export.csv. Primero, asegúrese de que todavía está en la base de datos correcta:

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 ...: Esta es una consulta estándar que especifica qué datos exportar.
• INTO OUTFILE '/tmp/employees_export.csv': Especifica la ruta completa para el archivo de salida. Por seguridad, MySQL requiere que este archivo no exista previamente.
• FIELDS TERMINATED BY ',': Separa los campos con una coma.
• ENCLOSED BY '"': Envuelve cada valor de campo entre comillas dobles, que es un formato CSV común.
• LINES TERMINATED BY '\n': Termina cada fila con un carácter de nueva línea.

Después de ejecutar el comando, cambie a su otra pestaña de terminal (o abra una nueva) y vea el contenido del archivo recién creado.

cat /tmp/employees_export.csv

Verá las seis filas de su tabla, formateadas como un archivo 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"

Validar Datos Importados

Después de importar datos, es crucial validarlos para garantizar la calidad e integridad. Los datos del mundo real a menudo son desordenados, contienen errores, valores faltantes o formatos incorrectos. Este paso le muestra cómo usar consultas SQL simples para encontrar problemas comunes.

El script de configuración creó employees_validation.csv, que contiene un correo electrónico no válido y un valor de departamento faltante. Primero, limpie la tabla employees en su shell de MySQL.

TRUNCATE TABLE employees;

Ahora, importe el archivo de validación.

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

Con los datos "sucios" cargados, ejecutemos algunas comprobaciones de validación.

1. Encontrar Formatos de Correo Electrónico No Válidos

Una verificación muy básica para un correo electrónico válido es ver si contiene un símbolo @ y un símbolo .. Podemos usar NOT LIKE para encontrar filas que no cumplan con esta verificación.

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

Esta consulta encontrará la fila donde el correo electrónico es invalid_email, ya que carece de los símbolos requeridos.

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

2. Encontrar Departamentos Faltantes

Puede encontrar filas donde falta un valor comprobando una cadena vacía ''.

SELECT * FROM employees WHERE department = '';

Esta consulta encontrará la fila donde el departamento se dejó en blanco en el archivo CSV.

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

Estas consultas simples son herramientas poderosas para una verificación de calidad de datos de primer paso. Después de identificar las filas problemáticas, puede decidir si corregirlas con sentencias UPDATE o eliminarlas con DELETE.

Ahora ha completado el laboratorio. Puede salir del shell de MySQL.

exit

Resumen

En este laboratorio, ha aprendido las operaciones fundamentales para mover datos dentro y fuera de una base de datos MySQL. Comenzó configurando un entorno de base de datos adecuado con una nueva base de datos y tabla. Luego utilizó el comando LOAD DATA INFILE para importar eficientemente datos de un archivo CSV.

A continuación, practicó la exportación de datos de una tabla a un nuevo archivo CSV utilizando la sentencia SELECT ... INTO OUTFILE, una tarea común para la generación de informes y el intercambio de datos. Finalmente, aprendió a realizar validaciones básicas de datos con consultas SQL para verificar errores de formato y valores faltantes después de una importación. Estas habilidades son esenciales para cualquier desarrollador o administrador que trabaje con MySQL.