csv to sql script traag bij grote files, te optimaliseren?

[switchboy]

Omdat ik een aantal .csv files heb die ik voor een hobby project wil inladen in een mySQL database heb ik een script gemaakt die de .csv files die op een vaste manier geformat zijn inleest en in bruikbare SQL uitspuugt. Dit script werkt met kleine bestanden prima.

Nu heb ik echter één .csv bestand van een paar honderd megabyte groot en als ik die door mijn script heen haal doet hij ongeveer een procent per uur, waardoor het omzetten dus in totaal ~100 uur gaat duren. Kortom mijn script is waarschijnlijk ‘een beetje’ inefficiënt.

Wat het in het kort doet is dat het het file per regel leest en de regel van comma gescheiden velden inleest in een array. Vanuit het array van regel 0 en de gelezen regel construeert het vervolgens een mySQL query per regel. Deze plakt hij allemaal aan elkaar vast in de string $output. Welke wanneer de loop helemaal doorlopen is wordt weggeschreven naar een .sql bestand en vervolgens geechoed.

De code als volgt:

PHP:

<?php
set_time_limit(0);
ini_set('memory_limit','1200M');
$linecount = 0;
$filename = 'test1.csv';
$lineNumber = 0;
$fields = NULL;
$output = NULL;
$tableName = $_GET['tableName'];
if($tableName == NULL){
    $tableName = 'test';
}
$prevPercentage = 0;

//tel het aantal regels
$handle = fopen($filename, "r");
while(!feof($handle)){
    $line = fgets($handle);
    $linecount++;
}
fclose($handle);


//Loop het file regel voor regel af en maak er sql van, de eerste regel bevat de tabelstructuur
$handle = fopen($filename, "r");
echo "Main loop starting...<br>";
if ($handle) {
    while (($line = fgets($handle)) !== false) {    
        $lineArray = str_getcsv($line, ",", "\"");
        
        //Spuug een soort van percentage uit op basis van hoeveel regels er al gedaan zijn
        $countArray = count($lineArray);
        $a = 0;
        $percentage = ($lineNumber/$linecount)*100;
        if($percentage > $prevPercentage+1){
            $prevPercentage = intval($percentage);
            echo intval($percentage)."% done... Program is still alive and kicking <br>";
        }
        
        // de eerste regel bevat de tabelstructuur
        if($lineNumber == 0){
            $fieldsStructured = NULL;
            while($a < $countArray){
                if($a != 0){
                    $fieldsStructured = $fieldsStructured.", `".$lineArray[$a]."` text NULL";
                    $fields = $fields.", `".$lineArray[$a]."`";
                } else {
                    $fieldsStructured = "`".$lineArray[$a]."` text NULL";
                    $fields = "`".$lineArray[$a]."`";
                }
                $a++;
            }
            $output = "DROP TABLE IF EXISTS `".$tableName."`;<br>CREATE TABLE IF NOT EXISTS `".$tableName."` (".$fieldsStructured.") ENGINE=InnoDB DEFAULT CHARSET=latin1;";
        } else {
            // Niet de eerste regel dit is dus data
            $lineStructured = NULL;
            while($a < $countArray){
                                //verwijder bestaande " van de data
                $lineArray[$a] =  str_replace("\"","", $lineArray[$a]);
                                //escape alle andere dingen
                $lineArray[$a] = mysql_real_escape_string($lineArray[$a]);
                if($a != 0){
                    $lineStructured = $lineStructured.", '".$lineArray[$a]."'";
                } else {
                    $lineStructured = "'".$lineArray[$a]."'";
                }
                $a++;
            }
            $output = $output."INSERT INTO `".$tableName."` (".$fields.") VALUES (".$lineStructured.");<br>"; 
        }
        $lineNumber++;
                //zorg ervoor dat als er wat geëchoed wordt dit meteen zichtbaar is
        flush();
        ob_flush();
    }
    fclose($handle);
}
file_put_contents('output.sql', preg_replace('#<br\s*?/?>#i', "\n", $output));
echo $output;

?>

Mijn vragen zijn:
Is er een betere methode dan de data eerst in een array inladen? Met een gewone find replace per regel gaat hij bijvoorbeeld niet goed om met comma’s die binnen ‘’ of “” staan.
Is het misschien slimmer om de variabele $output niet honderden mb’s groot te laten worden en per iteratie van de while loop de data al weg te schrijven? (ik denk het niet want dan moet het script steeds wachten op disk I/O). Of valt er eigenlijk niet veel te winnen als ik deze operatie per sé door php wil laten uitvoeren en moet ik mijn heil in een andere programmeertaal zoeken?

[BarôZZa]

Lock je tables en insert meerdere queries tegelijk.

[TheBorg]

BarôZZa schreef op zaterdag 09 april 2016 @ 11:19:
Lock je tables en insert meerdere queries tegelijk.

Het script doet geen queries.

Ik zou er wat timers instoppen om te zien hoe lang wat duurt. Misschien is die preg_replace op het eind wel duur, verder zie ik namelijk zo snel geen rare dingen.

[HuHu]

Verplaats de flush() en ob_flush() eens naar direct na de echo.

Verder kun je de while's op 1 laten beginnen en het geval $a=0 daarvoor apart afhandelen. Idem voor $lineNumber=0.

[Edwin88]

Misschien kan je het anders aanpakken:

csv file inladen in excel of spreadsheet programma
data exporteren naar bestand wat door een database manager zoals Sequel Pro kan worden geïmporteerd

Ik weet niet zo goed wat die preg_replace('#<br\s*?/?>#i') doet, maar kan je die niet al eerder doen bij het stukje waar je de regel aanmaakt? Nu moet die hele functie door de $output die dan 100'en MB's groot is, tegenover 1 regeltje (wellicht sneller)

PHP:

<?php
set_time_limit(0);
ini_set('memory_limit','1200M');
$linecount = 0;
$filename = 'test1.csv';
$lineNumber = 0;
$fields = NULL;
$output = NULL;
$tableName = $_GET['tableName'];
if($tableName == NULL){
    $tableName = 'test';
}
$prevPercentage = 0;

//tel het aantal regels
$handle = fopen($filename, "r");
while(!feof($handle)){
    $line = fgets($handle);
    $linecount++;
}
fclose($handle);


//Loop het file regel voor regel af en maak er sql van, de eerste regel bevat de tabelstructuur
$handle = fopen($filename, "r");
echo "Main loop starting...<br>";
if ($handle) {
    while (($line = fgets($handle)) !== false) {    
        $lineArray = str_getcsv($line, ",", "\"");
        
        //Spuug een soort van percentage uit op basis van hoeveel regels er al gedaan zijn
        $countArray = count($lineArray);
        $a = 0;
        $percentage = ($lineNumber/$linecount)*100;
        if($percentage > $prevPercentage+1){
            $prevPercentage = intval($percentage);
            echo intval($percentage)."% done... Program is still alive and kicking <br>";
        }
        
        // de eerste regel bevat de tabelstructuur
        if($lineNumber == 0){
            $fieldsStructured = NULL;
            while($a < $countArray){
                if($a != 0){
                    $fieldsStructured = $fieldsStructured.", `".$lineArray[$a]."` text NULL";
                    $fields = $fields.", `".$lineArray[$a]."`";
                } else {
                    $fieldsStructured = "`".$lineArray[$a]."` text NULL";
                    $fields = "`".$lineArray[$a]."`";
                }
                $a++;
            }
            $output = "DROP TABLE IF EXISTS `".$tableName."`;<br>CREATE TABLE IF NOT EXISTS `".$tableName."` (".$fieldsStructured.") ENGINE=InnoDB DEFAULT CHARSET=latin1;";
        } else {
            // Niet de eerste regel dit is dus data
            $lineStructured = NULL;
            while($a < $countArray){
                                //verwijder bestaande " van de data
                $lineArray[$a] =  str_replace("\"","", $lineArray[$a]);
                                //escape alle andere dingen
                $lineArray[$a] = mysql_real_escape_string($lineArray[$a]);
                if($a != 0){
                    $lineStructured = $lineStructured.", '".$lineArray[$a]."'";
                } else {
                    $lineStructured = "'".$lineArray[$a]."'";
                }
                $a++;
            }
            $newline = preg_replace('#<br\s*?/?>#i', "\n", "INSERT INTO `".$tableName."` (".$fields.") VALUES (".$lineStructured.");<br>");
            $output = $output.$newline; 
        }
        $lineNumber++;
                //zorg ervoor dat als er wat geëchoed wordt dit meteen zichtbaar is
        flush();
        ob_flush();
    }
    fclose($handle);
}
file_put_contents('output.sql', $output);
echo $output;

?>

zoiets

[ Voor 77% gewijzigd door Edwin88 op 09-04-2016 11:38 ]

[CyBeRSPiN]

Waarom niet mysqlimport gebruiken? http://dev.mysql.com/doc/en/mysqlimport.html

[GlowMouse]

Is het misschien slimmer om de variabele $output niet honderden mb’s groot te laten worden en per iteratie van de while loop de data al weg te schrijven? (ik denk het niet want dan moet het script steeds wachten op disk I/O).

Dit is het inderdaad. Je kopieert nu in elke iteratie de hele string naar een nieuwe string. Wegschrijven betekent niet automatisch wachten op disk i/o, dat is alleen bij een flush. Gebruik dan wel fopen/fwrite, en niet file_put_contents met FILE_APPEND.

Je zou ook regel 70 kunnen vervangen door:

PHP:

$output[] = $newline;

(regel 8/53 ook aanpassen), en dan uiteindelijk nog iets hebben dat er één string van maakt:

PHP:

$output = implode("\n", $output);

Het geheugengebruik wordt dan wel hoger dan wanneer je direct wegschrijft, maar bij een paar honderd MB is dat effect niet zo belangrijk.

[ Voor 33% gewijzigd door GlowMouse op 09-04-2016 11:46 ]

[switchboy]

Het zat hem inderdaad in die hele grote $output

Ik zal zo wat expirimenteren met de andere suggesties of ik hem nog sneller kan krijgen. Dank voor alle input! Dit is erg lerzaam

[ Voor 87% gewijzigd door switchboy op 09-04-2016 12:11 ]

[SPee]

Je kan ook meerdere records tegelijk INSERTen:

SQL:

  INSERT INTO mytable(colum1,colum2) VALUES (row1value1,row1value2), (row2value1,row2value2), (row3value2,row3value2);

En ik weet niet of je de flush ook daadwerkelijk nodig hebt in PHP, maar anders kun je ook in bulk flushen, door dit niet per record te doen, maar per 10 ofzo (vergeet dit dan ook niet bij de laatste te doen).

[MSteverink]

Ik heb niet je hele script doorgelezen. Maar:

Zet eventueel de indexen uit voor je gaat inserten;
Zet autocommit uit, en doe een commit na iedere 1000 records (http://php.net/manual/en/mysqli.commit.php)

[ShitHappens]

CyBeRSPiN schreef op zaterdag 09 april 2016 @ 11:37:
Waarom niet mysqlimport gebruiken? http://dev.mysql.com/doc/en/mysqlimport.html

Dit is eigenlijk toch wel de beste optie, en die het snelste verloopt.

Alternatief is het bestand in Excel inlezen en dan de MySQL plugin gebruiken om de data over te pompen.

[DJMaze]

switchboy schreef op zaterdag 09 april 2016 @ 11:03:
Kortom mijn script is waarschijnlijk ‘een beetje’ inefficiënt.

Zo, dit is een stuk fatsoenlijker en geen geheugen vreter.
Ik hoop dat je er van leert, je bent nog best rookie

PHP:

<?php
set_time_limit(0);
ob_implicit_flush();
if (ob_get_level()) { ob_end_clean(); }
$filename = 'test1.csv';
$tableName = $_GET['tableName'];
if ($tableName == NULL) {
    $tableName = 'test';
}

// tel het aantal regels
// Gejat van stackoverflow en aangepast door DJMaze
$linecount = 0;
$handle = fopen($filename, 'r');
while (false !== fgets($handle)) {
    ++$linecount;
}
fclose($handle);

// Loop het file regel voor regel af en maak er sql van, de eerste regel bevat de tabelstructuur
$handle = fopen($filename, "r");
$output = fopen('output.sql', 'w+');
echo "Main loop starting...<br>";
if ($handle && $output) {
    // de eerste regel bevat de tabelstructuur
    $fields = fgetcsv($handle, ',', '"');
    $fieldsStructured = '`' . implode('` text NULL, `', $fields) . '` text NULL';
    $fields = '`' . implode('`, `', $fields) . '`';
    fwrite($output, "DROP TABLE IF EXISTS `{$tableName}`;\nCREATE TABLE IF NOT EXISTS `{$tableName}` ({$fieldsStructured}) ENGINE=InnoDB DEFAULT CHARSET=latin1;\n");
    
    // Niet de eerste regel dit is dus data
    $lineNumber = 0;
    $prevPercentage = 0;
    while (false !== ($lineArray = fgetcsv($handle, ',', '"'))) {
        // Spuug een soort van percentage uit op basis van hoeveel regels er al gedaan zijn
        $percentage = intval(++$lineNumber * 100 / $linecount);
        if ($percentage > $prevPercentage) {
            $prevPercentage = $percentage;
            echo "{$percentage}% done... Program is still alive and kicking<br>";
        }
        
        $lineStructured = "'" . implode("', '", array_map('mysqli_real_escape_string', $lineArray)) . "'";

        fwrite($output, "INSERT INTO `{$tableName}` ({$fields}) VALUES ({$lineStructured});\n");
    }

    fclose($handle);
    fclose($output);
}

// echo sql
if ($output = fopen('output.sql', 'r')) {
    fpassthru($output);
    fclose($output);
}

[ Voor 18% gewijzigd door DJMaze op 09-04-2016 17:32 . Reden: Echo SQLtoegevoegd op einde ]

[Gomez12]

@DJMaze : Wat ik me altijd afvraag is : hoe belangrijk is een voortgangspercentage?

Want in wezen lees je nu 2x het bestand in, waarbij 1x enkel is om een redelijk accuraat voortgangspercentage te geven.

Persoonlijk zou ik eerder de 1e keer inlezen overslaan en gewoon tijdens het 2e keer inlezen (waar je dus actief iets doet) gewoon iets zeggen in de trant van : if ($linecount%1000==0) then print "Busy with line " : $linecount
Dan heb je gewoon een tellertje wat oploopt zodat je weet dat hij bezig is, je weet niet hoe lang hij nog bezig gaat zijn, maar wel dat hij bezig is en dat is voor mij over het algemeen het belangrijkste...

[GlowMouse]

Met filesize() en ftell() kun je nauwkeurig een percentage weergeven (tov het aantal bytes) zonder de file 2x hoeft in te lezen.

[Hydra]

DJMaze schreef op zaterdag 09 april 2016 @ 14:44:
[...]

Zo, dit is een stuk fatsoenlijker en geen geheugen vreter.
Ik hoop dat je er van leert, je bent nog best rookie

Nogal een statement van iemand die een hele file leest alleen maar om de regels te tellen.

[DJMaze]

Klopt, de eerste keer het hele bestand inlezen is best overbodig. Maar daar ging mijn wijziging niet over.
Dat was namelijk al een "feature" wat hij zelf had ingebakken als je zijn code leest.
Die kan hij best zelf anders oplossen, zoals GlowMouse al aangeeft.

Mij ging het vooral om het tweede gedeelte waar veel winst te halen is. Vanwege:

switchboy schreef op zaterdag 09 april 2016 @ 11:03:
Nu heb ik echter één .csv bestand van een paar honderd megabyte groot en als ik die door mijn script heen haal doet hij ongeveer een procent per uur, waardoor het omzetten dus in totaal ~100 uur gaat duren.

Het laatste deel die de hele sql echo'd is ook zinloos, maar dat had hij er ook in zitten.

Met de tip van GlowMouse er bij is het natuurlijk beter

PHP:

<?php
set_time_limit(0);
ob_implicit_flush();
if (ob_get_level()) { ob_end_clean(); }

$filename = 'test1.csv';
$filesize = filesize($filename);
$tableName = empty($_GET['tableName']) ? 'test' : $_GET['tableName'];

// Loop het file regel voor regel af en maak er sql van, de eerste regel bevat de tabelstructuur
$handle = fopen($filename, "r");
$output = fopen('output.sql', 'w+');
echo "Main loop starting...<br>";
if ($handle && $output) {
    // de eerste regel bevat de tabelstructuur
    $fields = fgetcsv($handle, ',', '"');
    $fieldsStructured = '`' . implode('` text NULL, `', $fields) . '` text NULL';
    $fields = '`' . implode('`, `', $fields) . '`';
    fwrite($output, "DROP TABLE IF EXISTS `{$tableName}`;\nCREATE TABLE IF NOT EXISTS `{$tableName}` ({$fieldsStructured}) ENGINE=InnoDB DEFAULT CHARSET=latin1;\n");
    
    // Niet de eerste regel dit is dus data
    $prevPercentage = 0;
    $starttime = microtime(1);
    while (false !== ($lineArray = fgetcsv($handle, ',', '"'))) {
        // Schrijf de data weg
        fwrite($output, "INSERT INTO `{$tableName}` ({$fields}) VALUES ('" . implode("', '", array_map('mysqli_real_escape_string', $lineArray)) . "');\n");
        // Spuug een soort van percentage uit
        $percentage = floor(100.0 * ftell($handle) / $filesize);
        if ($percentage > $prevPercentage) {
            $prevPercentage = $percentage;
            $estimate = (microtime(1) - $starttime) / $percentage * (100 - $percentage);
            echo "{$percentage}% done... " . gmdate('H:i:s', $estimate) . " time remaining<br>";
        }
    }

    fclose($handle);
    fclose($output);
}

Sneller dan dit wordt het hem niet denk ik.

[ Voor 97% gewijzigd door DJMaze op 13-04-2016 10:39 . Reden: Antwoord verbeterd met duidelijke uitleg en estimated time ]

[Ankona]

De pijn die zit naar mijn idee in de veelvuldige string concatinations. Zoals anderen ook al stelden, in de meeste programmeertalen zijn die heel duur.
Achtergrond is dat strings immutable objects zijn. In plaats van een string aan een bestaande te plakken maak je een derde aan in een nieuw stukje geheugen en moet vervolgens het oude weer vrij gegeven worden.

Een simpele techniek die ik vaak toepas als ik niet weet waar ik de pijn moet zoeken is een voor een regels uit commentariëren. Een groffe domme techniek, maar als je geen idee hebt waar je het moet zoeken lokaliseer je zo wel de pijn. Daarna kun je de oorzaak van de pijn zoeken.

Maar wat betreft de oplossing. Ik ben geen php programmeur dus ik weet niet wat php in de gereedschapskoffer heeft zitten om je te helpen. Een oude techniek die vaak werkt is met arrays werken die je uiteindelijk joined. Of, moderner, met streams. (filestream bijvoorbeeeld.) Het .net framework (als voorbeeld, dat is mijn wereld maar voor deze casus slechts een mogelijk irrelevant voorbeeld) heeft hiervoor een Stringbuilder class die goud is. Werkt makkelijk en performt goed.

[Gomez12]

DJMaze schreef op zondag 10 april 2016 @ 13:53:
Sneller dan dit wordt het hem niet denk ik.

Kan nog wel een paar ordes van grootte sneller hoor(even afhankelijk hoeveel regels, hoeveel csv-kolommen etc), alleen wil je het nog leesbaar houden tja...

Maar even heel ergens anders over beginnend : Wat is jouw idee achter een $handle en een $output?
Want imho is het of :
- $input en $output (of eigenlijk : $fileInput en $fileOutput)
- $handle1 en $handle2

Maar jij kiest ervoor om een $handle te hebben en een $output, terwijl je output niet naar je scherm gaat, want dat is weer andere output die je echoed.

Voor een 1e voorbeeldje was het gewoon voorbeeldcode geweest, maar nu heb je die verbeterd en dan krijg ik opeens allerlei vragen waarom je dat op die manier verbeterd

Ankona schreef op woensdag 13 april 2016 @ 20:50:
De pijn die zit naar mijn idee in de veelvuldige string concatinations.

Kan je die eens aanwijzen? Want ik zie ze eerlijk gezegd niet zo 1-2-3.

Achtergrond is dat strings immutable objects zijn.

Sinds wanneer is dat zo in php?

[GlowMouse]

Gomez12 schreef op woensdag 13 april 2016 @ 22:59:
[...]

Kan je die eens aanwijzen? Want ik zie ze eerlijk gezegd niet zo 1-2-3.

Regel 69 in de OP.

[...]

Sinds wanneer is dat zo in php?

Dat is nooit zo geweest, je kunt een karakter in een string probleemloos vervangen.

[Gomez12]

GlowMouse schreef op woensdag 13 april 2016 @ 23:04:
[...]

Regel 69 in de OP.

Mwah, als je daar valt over 7 string concatenations dan mis je volgens mij dat de $output ietwat groot wordt en dat je een groot risico hebt op een OOM.

Het voordeel van geen string concatenations zit hem voornamelijk in dat je cheap iets toevoegt tegenover een duurdere functie om het alsnog naar string te krijgen.
Oftewel als je 100.000x iets toevoegt aan een string dan kan je in menig andere taal beter kiezen voor een andere manier en aan het einde die eenmalige duurdere functie uitvoeren.
Echter hier wil je het in principe al geen 100.000x uitgevoerd hebben, je wilt de output gewoon gelijk weer dumpen als je die gegenereerd hebt, waardoor je het nog maar hebt over 7 string concatenations die je daadwerkelijk gedaan wilt hebben.
En met die kleine aantallen is het heel erg implementatie afhankelijk of iets als een stringbuilder nog wel efficiënter is of dat die stringbuilder nu net een nieuwe bottleneck gaat vormen.

[GlowMouse]

Gomez12 schreef op woensdag 13 april 2016 @ 23:15:
[...]

Mwah, als je daar valt over 7 string concatenations dan mis je volgens mij dat de $output ietwat groot wordt en dat je een groot risico hebt op een OOM.

Het voordeel van geen string concatenations zit hem voornamelijk in dat je cheap iets toevoegt tegenover een duurdere functie om het alsnog naar string te krijgen.

Toevoegen is hier niet cheap. Per iteratie wordt uiteindelijk een paar honderd MB gekopieerd om misschien 100 bytes toe te voegen, zoals Ankona zegt.

[DJMaze]

Gomez12 schreef op woensdag 13 april 2016 @ 22:59:
Maar even heel ergens anders over beginnend : Wat is jouw idee achter een $handle en een $output?
Want imho is het of :
- $input en $output (of eigenlijk : $fileInput en $fileOutput)
- $handle1 en $handle2

Maar jij kiest ervoor om een $handle te hebben en een $output, terwijl je output niet naar je scherm gaat, want dat is weer andere output die je echoed.

Voor een 1e voorbeeldje was het gewoon voorbeeldcode geweest, maar nu heb je die verbeterd en dan krijg ik opeens allerlei vragen waarom je dat op die manier verbeterd

hahaha, natuurlijk komt er commentaar.
Je zou ze ook $csv_file_pointer en $sql_file_pointer kunnen noemen of $csv_handle en $sql_handle;
Aangezien PHP ze aanmerkt als "resource" zou je ze ook $csv_resource en $sql_resource kunnen noemen.