Cómo importar archivos de datos JSON, texto, XML y CSV a MySQL

En las aplicaciones de bases de datos, importar datos externos a la base de datos es un requisito muy común. De hecho, esta es la parte L (Cargar) de ETL (Extraer, Transformar, Cargar) en la gestión y manipulación de datos, es decir, importar datos en una estructura o formato específico a un destino específico (por ejemplo, una base de datos como MySQL). ).

Proceso ETL

Este artículo analizará cómo importar fácilmente datos en múltiples formatos (JSON, texto, XML y CSV) a MySQL.

Resumen:

Importar archivos de texto (incluidos archivos CSV) a MySQL

Importar archivos XML a MySQL

Importar archivos JSON a MySQL

Utilice el asistente de importación y exportación de datos de MySQL Workbench para importar y exportar archivos JSON o CSV

1 Importe archivos de texto (incluidos archivos CSV) a MySQL

. Nuestra discusión aquí supone que los archivos de texto y los archivos CSV están en el formato correcto. Por ejemplo, cada campo de una fila está separado por **** el mismo delimitador (como tabulación: \t).

Entonces, primero, debe diseñar la tabla de la base de datos (esquema) de acuerdo con el formato de los datos (qué campos hay).

Por ejemplo, al procesar archivos de texto o archivos CSV, (t) se utiliza como delimitador. Cada línea tiene tres campos de datos: identificación, nombre y saldo. Luego, primero debemos crearlo en el archivo. base de datos:

CREAR TABLA sometable(id INT, nombre VARCHAR (255), saldo DECIMAL(8,4));

CREAR TABLA sometable(id INT, nombre VARCHAR). (255), saldo DECIMAL(8,4));

CREATE se puede importar después de una creación exitosa. La operación es muy simple:

CARGAR DATOS INFILE LOCAL 'La ruta a su archivo (por ejemplo, ~/file.csv)' EN LA TABLA CAMPOS sometable TERMINADOS POR '\t' [ENCERRADO POR '"' (opcional ) ] LÍNEAS TERMINADAS EN '\n' (id, nombre, saldo)

Cabe señalar aquí que necesitamos abrir el archivo local (un parámetro de configuración de MySQL) para importar correctamente. Se utiliza --local. -infile inicia mysqld, el parámetro local-infile se habilitará. Si se inicia con --local-infile=0, LOCAL no tendrá ningún efecto. Consulte la Sección 6.1.6, "Problemas de seguridad con LOAD DATA LOCAL". "

Por razones de seguridad, esta es la configuración predeterminada de MySQL. Por lo tanto, al iniciar MySQL desde la línea de comando, debemos asegurarnos de que el archivo de configuración my.cnf (o /etc/my.cnf , para distribuciones Debian como Ubuntu)) agregue

local-infile=1

o --local-infile:

mysql --local-infile - uroot -pyourpwd yourdbname

Además, también podemos usar mysqlimport, un programa de importación oficial de MySQL, que esencialmente proporciona una línea de comando CARGAR ARCHIVO DE DATOS. Este programa esencialmente proporciona una interfaz de línea de comando para CARGAR ARCHIVO DE DATOS. lo cual es fácil de entender, por lo que no entraremos en detalles aquí.

2. Importar archivos XML a MySQL

La forma de importar tiene mucho que ver con la forma del XML.

Por ejemplo, si su archivo de datos XML tiene un formato muy, muy estandarizado, como por ejemplo:

< fila>

1

Gratis

< .3333

2

Niki

1289.2333

o

Entonces, podemos usar fácilmente LOAD XML para importar. Consulte la manual oficial de MySQL aquí - CARGAR sintaxis XML.

Sin embargo, es posible que tengamos otras necesidades, por ejemplo, es posible que queramos asignar campos en el archivo XML a columnas con nombres diferentes (COLUMNA DE TABLA). Tenga en cuenta que después de MySQL v5.0.7, los procedimientos almacenados de MySQL ya no pueden ejecutar LOAD XML INFILE o LOAD DATA INFILE, por lo que el procedimiento de conversión se escribe de manera diferente que antes. Aquí necesitamos usar las funciones Load_File() y ExtractValue().

El siguiente es un ejemplo de un archivo XML y un programa:

Archivo:

< alguna_lista>

Programa:

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDIMIENTO `import_some_xml`(ruta varchar(255), nodo varchar(255))

COMENZAR

declarar texto xml_content;

declarar v_row_index int unsigned predeterminado 0

declarar v_row_count int unsigned

Declarar v_xpath_row varchar(255);

Establecer xml_content = load_file(path);

- Cuente el número de elementos de fila.

set v_row_count = extractValue(xml_content, concat('count( ', node, ')');

-- Recorre todos los elementos de la fila

while v_row_index < v_row_count hacer

set v_row_index = v_row_index + 1;

set v_xpath_row = concat(nodo, '[', v_row_index, ']/@*');

insertar en los valores de los solicitantes (

extractValue(xml_content, concat(v_ xpath_row, '[1]')),

extractValue(xml_content, concat(v_xpath_row, ' [2 ]')),

extractValue(xml_content, concat(v_xpath_row, '[ 3]'))

);

finalizar mientras;

END

En MySQL, use esto para importar:

llame a import_some_xml('la ruta de su archivo XML', '/some_list/alguien');