separated - jira csv import example

Mejores prácticas para importar archivos CSV grandes

Question

Mi compañía obtiene un conjunto de archivos CSV llenos de información de la cuenta bancaria cada mes que debo importar a una base de datos. Algunos de estos archivos pueden ser bastante grandes. Por ejemplo, uno es de aproximadamente 33 MB y cerca de 65 000 líneas.

En este momento tengo una aplicación Symfony / Doctrine (PHP) que lee estos archivos CSV y los importa a una base de datos. Mi base de datos tiene aproximadamente 35 tablas diferentes y en el proceso de importación, tomo estas filas, las divido en sus objetos constituyentes y las inserto en la base de datos. Todo funciona muy bien, excepto que es lento (cada fila dura aproximadamente un cuarto de segundo) y usa mucha memoria.

El uso de memoria es tan malo que tengo que dividir mis archivos CSV. Un archivo de 20,000 líneas casi no lo logra. Para cuando está cerca del final, tengo un 95% de uso de memoria. Importar ese archivo de 65,000 líneas simplemente no es posible.

Encontré que Symfony es un marco excepcional para crear aplicaciones y normalmente no consideraría usar nada más, pero en este caso estoy dispuesto a arrojar todas mis ideas preconcebidas por la ventana en nombre del rendimiento. No estoy comprometido con ningún lenguaje específico, DBMS ni nada.

A Stack Overflow no le gustan las preguntas subjetivas, así que trataré de hacer que esto sea lo más subjetivo posible: para aquellos de ustedes que no solo tienen una opinión sino que experimentan la importación de grandes archivos CSV , qué herramientas / prácticas han usado en el pasado que han tenido éxito?

Por ejemplo, ¿solo usa el ORM / OOP de Django y no ha tenido ningún problema? ¿O lee todo el archivo CSV en la memoria y prepara unas cuantas declaraciones INSERT enormes?

Una vez más, no solo quiero una opinión, sino algo que realmente funcionó para ti en el pasado.

Editar: no solo estoy importando una hoja de cálculo CSV de 85 columnas en una tabla de base de datos de 85 columnas. Estoy normalizando los datos y poniéndolos en docenas de tablas diferentes. Por esta razón, no puedo usar LOAD DATA INFILE (estoy usando MySQL) o cualquier otra función de DBMS que solo lea en archivos CSV.

Además, no puedo usar ninguna solución específica de Microsoft.

Answer 1

Estoy leyendo un archivo CSV que tiene cerca de 1 millón de registros y 65 columnas. Cada 1000 registros procesados ​​en PHP, hay una gran declaración de MySQL que va a la base de datos. La escritura no lleva mucho tiempo. Es el análisis lo que hace. La memoria utilizada para procesar este archivo descomprimido de 600 MB es de aproximadamente 12 MB.

Answer 2

FWIW los siguientes pasos causaron una gran aceleración de mi LOAD DATA INFILE :

SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;
SET SESSION tx_isolation=''READ-UNCOMMITTED'';
SET sql_log_bin = 0;
#LOAD DATA LOCAL INFILE....
SET UNIQUE_CHECKS = 1;
SET FOREIGN_KEY_CHECKS = 1;
SET SESSION tx_isolation=''READ-REPEATABLE'';

Ver artículo here

Answer 3

Necesito hacer esto también de vez en cuando (importo grandes CSV no estandarizados donde cada fila crea una docena de objetos DB relacionados) así que escribí un script python donde puedo especificar qué va y dónde está relacionado. El script simplemente genera instrucciones INSERT.

Aquí está: csv2db

Descargo de responsabilidad: básicamente soy un novato cuando se trata de bases de datos, por lo que podría haber mejores formas de lograr esto.

Answer 4

No me gustan algunas de las otras respuestas :)

Solía ​​hacer esto en un trabajo.

Usted escribe un programa para crear un gran script SQL lleno de instrucciones INSERT, una por línea. Entonces, ejecutas el script. Puede guardar el script para futuras referencias (registro barato). Usa gzip y reducirá el tamaño al 90%.

No necesita herramientas sofisticadas y realmente no importa qué base de datos está utilizando.

Puede hacer unos cientos de Inserts por transacción o todos en una transacción, depende de usted.

Python es un buen lenguaje para esto, pero estoy seguro de que php también está bien.

Si tiene problemas de rendimiento, algunas bases de datos como Oracle tienen un programa especial de carga masiva que es más rápido que las instrucciones INSERT.

Debería quedarse sin memoria porque solo debería analizar una línea a la vez. No es necesario que guardes todo en la memoria, ¡no hagas eso!

Answer 5

Perdónenme si no estoy entendiendo exactamente su problema correctamente, pero parece que están tratando de obtener una gran cantidad de datos CSV en una base de datos SQL. ¿Hay alguna razón por la que desee usar una aplicación web u otro código para procesar los datos CSV en las instrucciones INSERT? Tuve éxito importando grandes cantidades de datos CSV en SQL Server Express (versión gratuita) usando SQL Server Management Studio y usando BULK INSERT. Una inserción masiva simple se vería así:

BULK INSERT [Company].[Transactions]
    FROM "C:/Bank Files/TransactionLog.csv"
    WITH
    (
        FIELDTERMINATOR = ''|'',
        ROWTERMINATOR = ''/n'',
        MAXERRORS = 0,
        DATAFILETYPE = ''widechar'',
        KEEPIDENTITY
    )
GO

Answer 6

Primero: 33 MB no es grande. MySQL puede manejar fácilmente datos de este tamaño.

Como habrás notado, la inserción fila por fila es lenta. Usar un ORM además es aún más lento: hay gastos generales para construir objetos, serialización, etc. Usar un ORM para hacer esto en 35 tablas es aún más lento . No hagas esto

De hecho, puede usar LOAD DATA INFILE ; solo escriba un script que transforme sus datos en el formato deseado, separándolo en archivos por tabla en el proceso. Luego puede LOAD cada archivo en la tabla adecuada. Este script puede escribirse en cualquier idioma.

Aparte de eso, los INSERT (column, ...) VALUES ... granel INSERT (column, ...) VALUES ... también funcionan. No adivine cuál debe ser el tamaño de su lote de fila; cronometrarlo empíricamente , ya que el tamaño óptimo del lote dependerá de la configuración particular de su base de datos (configuración del servidor, tipos de columnas, índices, etc.)

Bulk INSERT no va a ser tan rápido como LOAD DATA INFILE , y aún tendrá que escribir un script para transformar los datos sin procesar en consultas INSERT utilizables. Por esta razón, probablemente haga LOAD DATA INFILE si es posible.

Answer 7

Puede usar el generador para el archivo de memoria eficiente listo. El pequeño fragmento a continuación podría ser útil.

#Method
public function getFileRecords($params)
{
    $fp = fopen(''../'' . $params[''file''] . ''.csv'', ''r'');
    //$header = fgetcsv($fp, 1000, '',''); // skip header

    while (($line = fgetcsv($fp, 1000, '','')) != FALSE) {
        $line = array_map(function($str) {
            return str_replace(''/N'', '''', $str);
        }, $line);

        yield $line;
    }

    fclose($fp);

    return;
}

#Implementation
foreach ($yourModel->getFileRecords($params) as $row) {
    // you get row as an assoc array;
    $yourModel->save($row);
}

Answer 8

Puede utilizar Mysql LOAD DATA INFILE , le permite leer datos de un archivo de texto e importar los datos del archivo en una tabla de base de datos muy rápido.

LOAD DATA INFILE ''/opt/lampp/htdocs/sample.csv'' INTO TABLE discounts FIELDS TERMINATED BY '','' ENCLOSED BY ''"'' LINES TERMINATED BY ''/n'' IGNORE 1 ROWS (title,@expired_date,discount) SET expired_date = STR_TO_DATE(@expired_date, ''%m/%d/%Y'');

para más información: http://dev.mysql.com/doc/refman/5.5/en/load-data.html y http://www.mysqltutorial.org/import-csv-file-mysql-table/

Answer 9

Si está utilizando el servidor Sql y tiene acceso a .NET, puede escribir una aplicación rápida para usar la clase SQLBulkCopy . Lo he usado en proyectos anteriores para obtener una gran cantidad de datos en SQL muy rápidamente. La clase SQLBulkCopy hace uso del BCP de SQL Server, por lo que si está utilizando algo que no sea .NET, puede valer la pena analizar si esa opción también está abierta para usted. No estoy seguro si está usando una base de datos que no sea SQL Server.

Answer 10

Tuve exactamente este mismo problema hace 2 semanas. Escribí algunos .NET para hacer inserciones ROW BY ROW y según mis cálculos con la cantidad de datos que tenía, tomaría alrededor de una semana hacerlo de esta manera.

Así que, en cambio, utilicé un generador de cadenas para crear una consulta ENORME y la envié a mi sistema relacional, todo a la vez. Pasó de tomar una semana a tomar 5 minutos. Ahora no sé qué sistema relacional estás usando, pero con enormes consultas probablemente tendrás que modificar tu parámetro max_allowed_packet o similar.