Insert csv naar MySQL

[3dmaster]

Ik ben met een php script bezig die een csv file naar een MySQL database importeerd. Echter bestaat de csv file uit meer als 100.000 rows. Ik heb een werkend php script maar helaas duurt het hiermee veel te lang (+- 30 minuten). Het probleem zit 'm in het feit dat het php script elke rij apart insert, wat natuurlijk niet echt opschiet . Ik ben al op zoek geweest naar een mogelijkheid om het in een keer te importeren maar dat is dus nog niet gelukt .

Tips zouden erg welkom zijn

Als er meer info nodig is, zeg dat dan even

[Sypher]

Als het goed is heeft Phpmyadmin heeft een CSV import optie
Werkt perfect.

[ Voor 21% gewijzigd door Sypher op 21-06-2007 10:29 ]

[Voutloos]

Je php script aanpassen om per x rijen te inserten kan als je de insert syntax weet nooit meer dan 2 minuten werk zijn. Waar je ook op moet letten ivm sneller importeren is de aanwezigheid van indexen. Na alle inserts weer de keys enablen of de index opnieuw opbouwen is veel sneller.

[sariel]

Eeuhm....voeg een aantal queries samen (insert bla; insert bla; insert bla;) en voer ze in een keer uit?
50 or 60 per keer in een keer uitvoeren zou al een snelheidswinst op moeten leveren, denk ik zo. tevens zorg er voor dat je niet steeds weer opnieuw de connectie opzet.

en verder...zou het misschien handig zijn om je script te zien, zodat we gerichtere antwoorden kunnen geven.

[T-MOB]

Alles in een keer gaat waarschijnlijk niet lukken omdat er een maximum zit aan de grootte van je query. Maar je kunt met een loopje wel een paar honderd rijen per query er door stampen. Zoiets zou het wel moeten doen

PHP:

$handle = fopen('file.csv',"r");
$i=0;
if ($handle !== FALSE) {
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        if ($i == 0) { $rows = array(); }

        $data = do_some_formatting($data);
        $rows[] = '(' .implode(',',$data) .')';

        if (++$i > 500) { //query each 500 entries to avoid max query size
            $i = 0;
            $sql = 'INSERT INTO `table` (`col`,`col2`,`col...`) VALUES ' .implode(',', $rows);
            $DB->query($sql);
        }
    }
}

[pjonk]

Google eens op LOAD DATA INFILE. Dat is uitermate geschikt voor bulk imports.

[drm]

Even voor de duidelijkheid (ter aanvulling op T-MOB), je kunt dus meerdere rows inserten met de volgende syntax:

SQL:

INSERT INTO 
   tablename (fieldA, fieldB) 
VALUES
   ('row1.fieldA', 'row1.fieldB'),
   ('row2.fieldA', 'row2.fieldB')
-- etcetera
;

Dat is vele malen sneller dan aparte insert queries.

[3dmaster]

code:

<?php

//connectie met de database
$server = "localhost";
$gebruikersnaam = "***";
$wachtwoord = "***";
$database = "csv";

$link = mysql_connect($server, $gebruikersnaam, $wachtwoord);
mysql_select_db($database, $link);

//array maken met de velden
$velden = array ("constateringstijd", "deviceid", "systemnotification", "startdate", "starttime", "duration", "policyid", "service", "service2", "protocolid", "sourcezone", "destinationzone", "action", "senddata", "receiveddata", "sourceip", "destinationip", "sourceport", "destinationport", "translatedip", "translatedport");

//koppeling naar het .csv bestand
$bestand  = fopen ("csv.csv", "r");

//indien het bestand geopent wordt door het script dan...
if ($bestand) 
{
    // Tabel legen
    set_time_limit(0);
    $query = "TRUNCATE testtabel";
    $result = mysql_query($query) or die ("<br><br>Fout: " . mysql_error());

    print ("<strong>De volgende query's zijn uitgevoerd:</strong><br><br>");
    
    while (!feof($bestand))
    {
        $regel = fgets($bestand, 4096);
        
        if($regel!="")
        {
            $array = explode (";", $regel);
            
            $query = "INSERT INTO testtabel (constateringstijd, deviceid, systemnotification, startdate, starttime, duration, policyid, service, service2, protocolid, sourcezone, destinationzone, action, senddata, receiveddata, sourceip, destinationip, sourceport, destinationport, translatedip, translatedport) VALUES (";
            
            $i=1;
            foreach ($array as $waarde) {
                $query .= "'" . $waarde . "'";
                
                if($i<21)
                {
                    $query .= ",";
                }
                $i++;
            }
            
            $query .= ");\n";
            
        }
        
        print($query);
        
        $result = mysql_query($query) or die ("<br><br>Fout: " . mysql_error());
    }
    fclose ($bestand);
}

?>

Als het goed is heeft Phpmyadmin heeft een CSV import optie
Werkt perfect.

het moet snachts automatisch de laatste csv file importeren zodat ik sochtends meteen alles kan uitlezen en niet eerst veel te lang moet wachten voordat ik de data kan bekijken

[Megamind]

Maak van je tabel eens een MyISAM ipv een INNODB Kwam ik achter dat die 2500 records p/s insertte, ipv 5 bij innodb

[wackmaniac]

En niet elke query afdrukken kan ook nog wel eens een snelheidswinst opleveren. Misschien kan je dan beter het aantal queries afdrukken ipv elke query in zijn geheel.

[Voutloos]

Megamind schreef op donderdag 21 juni 2007 @ 11:09:
Maak van je tabel eens een MyISAM ipv een INNODB Kwam ik achter dat die 2500 records p/s insertte, ipv 5 bij innodb

Bah, dit soort opmerkingen moeten toch echt met meer nuance en argumentatie.

Zoals bijvoorbeeld de nuance dat je de verschillen tussen deze storage engines moet weten en dat je moet weten wat de primary key is om te kunnen zeggen of het een verschil is. Dit met de aanname dat in jouw specifieke geval het verschil ontstond omdat InnoDB met een clustered index werkt. Een andere onbekende is dat jij nu niet weet of er verder nog InnoDB specifieke featuers gebruikt worden, of dat select queries juist sneller zijn dankzij InnoDB aanpak.

Het is een zeer goed idee om na te denken welke storage engine het beste bij een tabel past, het is een slecht idee om iets dergelijks blindelings als gebod ('maak de tabel eens myisam') te uitten.

[Megamind]

Voutloos schreef op donderdag 21 juni 2007 @ 12:18:
[...]
Bah, dit soort opmerkingen moeten toch echt met meer nuance en argumentatie.

Zoals bijvoorbeeld de nuance dat je de verschillen tussen deze storage engines moet weten en dat je moet weten wat de primary key is om te kunnen zeggen of het een verschil is. Dit met de aanname dat in jouw specifieke geval het verschil ontstond omdat InnoDB met een clustered index werkt. Een andere onbekende is dat jij nu niet weet of er verder nog InnoDB specifieke featuers gebruikt worden, of dat select queries juist sneller zijn dankzij InnoDB aanpak.

Het is een zeer goed idee om na te denken welke storage engine het beste bij een tabel past, het is een slecht idee om iets dergelijks blindelings als gebod ('maak de tabel eens myisam') te uitten.

Als hij al zo'n crappy php script laat zien, en zelf niet onderbouwd, ga ik niet dit soort onzin voordragen. Hij vraagt om een oplossing en ik geef die. Dat jij het er niet mee eens bent fijn, bedenk jij dan een goede oplossing PK kan je ook op allebei de storage engines zetten en qua performance scheelt een myisam wel iets ja. Leuk als je stored proc enzo wilt gebruiken, maar voor een simpele tabel maakt het niet veel uit.

[3dmaster]

code:

//array maken met de velden
$velden = array ("constateringstijd", "deviceid", "systemnotification", "startdate", "starttime", "duration", "policyid", "service", "service2", 

"protocolid", "sourcezone", "destinationzone", "action", "senddata", "receiveddata", "sourceip", "destinationip", "sourceport", "destinationport", 

"translatedip", "translatedport");

//koppeling naar het .csv bestand
$bestand  = fopen ("csv.csv", "r");

if ($bestand) 
{
    // Tabel legen
    set_time_limit(0);
    $query = "TRUNCATE testtabel";
    $result = mysql_query($query) or die ("<br><br>Fout: " . mysql_error());

    print ("<strong>De volgende query's zijn uitgevoerd:</strong><br><br>");
    
    $prequery = "INSERT INTO testtabel (constateringstijd, deviceid, systemnotification, startdate, starttime, duration, policyid, service, service2, 

protocolid, sourcezone, destinationzone, action, senddata, receiveddata, sourceip, destinationip, sourceport, destinationport, translatedip, translatedport) 

VALUES";
    $query = "";
    
    $j=0;
    
    while (!feof($bestand))
    {
        $regel = fgets($bestand, 4096);
        
        if($regel!="")
        {
            $array = explode (";", $regel);
            
            $query .= "(";
            
            $i=1;
            foreach ($array as $waarde) {
                $query .= "'" . $waarde . "'";
                
                if($i<21)
                {
                    $query .= ",";
                }
                $i++;
            }
            
            if($j==10)
            {
                $query .= ")";
            }
            else
            {
                $query .= "),";

            }           
        }
        
        $j++;
        
        if($j==10)
        {
            $prequery .= $query;
            print($prequery . "<br>");
            $result = mysql_query($prequery) or die ("<br><br>Fout: " . mysql_error());
            $query="";
            $j=0;
        }
    }
    
    $prequery .= $query;
    print($prequery . "<br>");
    $result = mysql_query($prequery) or die ("<br><br>Fout: " . mysql_error());
    
    fclose ($bestand);
}

ik ben er nu bijna. Alleen het rare is dat hij na de 10 rijen geen ) doet maar een ), en daardoor een fout genereerd en dus niets in de database zet. Dit terwijl ik geen fout kan ontdekken. Het is nu nog 10 maar straks wel hoger dit om het te kunnen testen.

edit/// opgelost

[Voutloos]

3dmaster: implode() is ook een heel handige functie. Met implode() wordt je code veel overzichtelijker dan een aanpak met allemaal condities om te checken of je al dan niet bij het laatste element aanbeland bent.

Megamind schreef op donderdag 21 juni 2007 @ 13:23:
[...]

Als hij al zo'n crappy php script laat zien, en zelf niet onderbouwd, ga ik niet dit soort onzin voordragen. Hij vraagt om een oplossing en ik geef die. Dat jij het er niet mee eens bent fijn, bedenk jij dan een goede oplossing PK kan je ook op allebei de storage engines zetten

Er zijn al ideeen geopperd welke altijd goed zijn en nofi, maar je kent zelf de verschillen tussen deze 2 engines niet goed genoeg.

qua performance scheelt een myisam wel iets ja.

MyISAM is dus niet per definitie sneller.

[Gomez12]

@3d master. Kijk eens na wat je script doet, schrijf dit eens uit voor deze 10 stappen. En concludeer dat je op regel 33 op j=9 zit, dus ")," dan op regel 50 ga je naar j=10 en op regel 63 begin je je query.

[3dmaster]

Gomez12 schreef op donderdag 21 juni 2007 @ 20:41:
@3d master. Kijk eens na wat je script doet, schrijf dit eens uit voor deze 10 stappen. En concludeer dat je op regel 33 op j=9 zit, dus ")," dan op regel 50 ga je naar j=10 en op regel 63 begin je je query.

Die n00bfout kwam ik net ook al achter
Maar met het volgende script is het uiteindelijk gelukt. Ben gewoon opnieuw begonnen en heb nu een veel sneller en eenvouder script.

code:

<?php 
set_time_limit(0); 
// timelimit op 0 zetten waardoor het script oneindig door kan gaan en niet na 30 seconden wordt gestopt door de browser
$query = "TRUNCATE tabel"; 
// leeg de database
mysql_query($query) or die(mysql_error()); 
// voer $query uit of geen een foutmelding
$sql = "LOAD DATA INFILE 'Z:/map/bestand' INTO TABLE tabel FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n'";
// laad het csv file in de tabel, velden zijn gescheiden door een ;, regels zijn gescheiden door een enter
mysql_query($sql) or die(mysql_error());
// voer $query uit of geen een foutmelding
?>

Erg leuk dat het zo veel eevoudig kon . Importeren naar de database gaat nu met 2.2 miljoen recores in 35 sec

[ Voor 3% gewijzigd door 3dmaster op 22-06-2007 11:46 ]

[Gwaihir]

Ja, dat gaat wel effe sneller . O.a. omdat het maar één query is en omdat de index(en) tussentijds niet steeds bijgewerkt wordt/worden. Mocht je dat nou in de toekomst met toevoegingen gaan doen (dus zonder de tabel eerst te legen), dan kun je hier lezen hoe je het zo snel houdt .

Overigens zou ik de tijdslimiet nooit op oneindig zetten. Als zo'n script ooit flink hangt, dan blijft het een forse serverbelasting totdat er een beheerder langs komt om het te killen. Al helemaal als het ondertussen nog een paar keer extra is opgestart "omdat het niet leek te werken". Wat dacht je van bijvoorbeeld 300 (5 minuten)? Je weet nu dat je slechts zo'n 35 seconden nodig hebt, dus da's ook al een zee van tijd.

Verder zetten we commentaar meestal vóór de betreffende code, niet op de regel erna. Vond ik aanvankelijk ook niet logisch, maar is typisch zo'n gewoonte waar je je als eenling aanpast omdat je er niemand blij mee maakt (ook jezelf niet, naarmate je meer van anderen leest of aan anderen wilt tonen).

@Megamind: stored procedure != transaction