Velký import CSV -> MySQL

7. 11. 2014 13:37:43

Zdravím,

jaký je nejlepší způsob vložení CVS s produkty do DB. CSV má asi 540 řádků a 82 sloupců.

Měl by tam probíhat jak import nových, tak update stávajících produktů, ale to klidně lze udělat na dva scripty. Zkoušel jsem to pomocí PHP a větvení, ale příkaz je na server moc dlouhý a po importu asi 40 produktů skáče vypršení časového limitu. Všechny údaje se zapisují asi do 6 tabulek v DB

Lze nějak určit po kolika řádcích se bude CSV načítat např. že by se po zpracování 10 řádků script aktualizoval, aby nepřekročil časový limit.

Předem díky, pokud někoho něco napadne

7. 11. 2014 13:37:43

https://webtrh.cz/diskuse/velky-import-csv-mysql#reply1067045

(22 hodnocení)

7. 11. 2014 13:43:02

Neni ten kod spis spatne napsan nebo se nezacykli? Pokud to nema stovky MB, tak to nemuze byt snad tak narocny :)

Ale v kazdym pripade muzes skriptu predavat treba parametr $_GET a v nem napoprvy poslat treba 0. Skript zpracuje od 0. radku souboru + 10. Ten skript pak treba presmeruje znovu na ten skript s $_GET+10, tzn. zpracuje pak od 10. radku + 10 radku. Cunacina, ale v omezenejch podminkach mozny..

7. 11. 2014 13:43:02

https://webtrh.cz/diskuse/velky-import-csv-mysql#reply1067044

acidclick

7. 11. 2014 13:46:24

2) Data nahrnout hromadne do docasne tabulky, jednim updatem updatnout puvodnu tabulku, jednim dotazem smazat z docasne co chybi v puvodni a to hromadne nahrnout do puvodni

Kazdopadne, jak pise sh, pro tak malo zaznamu to nemuze trvat dlouho. A mit tabulku s 82 sloupcema asi taky nebude to prave orechove.

7. 11. 2014 13:46:24

https://webtrh.cz/diskuse/velky-import-csv-mysql#reply1067043

Petr Holomoj

(22 hodnocení)

7. 11. 2014 14:02:44

On je asi taky problém v tom, že ten script nemá jen funkci insert a update, ale jelikož několik důležitý údajů nemá identifikační znaky, který by pro tabulky společný jako např. ID, ale před importem je tam select a pro dvě tabulky i delete

To acidclick: tabulka nemám 82 sloupců :-) ten import je rozdělený do 6 tabulek s tím, že do každé se píše pouze část a většinou je jen jeden údaj společný a to ID, set_time jsem zkoušel a stejně mi to na hostingu nebylo moc platný

To sh: zkusím, díky

Teď mě tak napadá, jestli by nebylo nejlepší ten velký script rozdělit na několik menších kroků např. podle tabulek v DB

7. 11. 2014 14:02:44

https://webtrh.cz/diskuse/velky-import-csv-mysql#reply1067042

Kovboj

(13 hodnocení)

7. 11. 2014 14:08:50

Jak je ten soubor velký a jak ho načítáš? Pokud je to "čisté" CSV bez žádných čuňáren (třeba odřádkování v nějakém textovém poli) tak prostě načti jeden řádek přes fgets a ten zpracuj. Nenačítal bych to celé do paměti.

Netuším co máš za server a co vše s daty děláš, ale aby ti už po 40 řádcích došel časový limit znamená něco špatného. Nechybí ti třeba ve tvé databázi indexy, takže dlouho trvá než skript zjistí zda má data aktualizovat nebo vložit nové? Nebo nezpracováváš tam třeba i nějak obrázky - jejich načítání z externího serveru a zpracování může být časově náročné.

7. 11. 2014 14:08:50

https://webtrh.cz/diskuse/velky-import-csv-mysql#reply1067041

Petr Holomoj

(22 hodnocení)

7. 11. 2014 14:27:50

Soubor se načítá přes:

Soubor samotný je malý, má asi 900KB a DB je klasická presta, takže tam by index měl být. Ale hodnoty s hodnotami z CSV se bohužel musí pracovat. Jelikož např. místo product_id je tam refereční kód, takže já u každého produktu musím vyselektovat jeho ID, to samé jsou např. výrobci, dodavatelé,dph, a vlastnosti

7. 11. 2014 14:27:50

https://webtrh.cz/diskuse/velky-import-csv-mysql#reply1067040

Kovboj

(13 hodnocení)

7. 11. 2014 14:56:27

Napsal Sketter;1138792
Soubor samotný je malý, má asi 900KB a DB je klasická presta, takže tam by index měl být. Ale hodnoty s hodnotami z CSV se bohužel musí pracovat. Jelikož např. místo product_id je tam refereční kód, takže já u každého produktu musím vyselektovat jeho ID, to samé jsou např. výrobci, dodavatelé,dph, a vlastnosti

Mezi "měly být" a jsou je velký rozdíl. Třeba můžeš porovnávat sloupečky, kde Presta běžně nehledá/neřadí a tak tam indexy nejsou. Pak je taky otázka jak co srovnáváš, hodně velké zpomalení může mít na svědomí třeba hledání typu LIKE '% něco %'. Samozřejmě ale hodně záleží i s jak velkou databází zboží pracuješ.

U nás něco podobného znamená třeba cca 30 tisíc položek v databázi a dvoumegový kontrolní CSV. Prvotní verze aktualizace trvala na serveru půl hodiny při které se z databáze jen kouřilo, po důkladné optimalizaci to trvá cca minutu :-)

7. 11. 2014 14:56:27

https://webtrh.cz/diskuse/velky-import-csv-mysql#reply1067039

Martin

(62 hodnocení)

7. 11. 2014 15:05:58

Takhle malý CSV musí bát zpracovaný za pár sekund. Prvně bych se zaměřil, zda jsou všude správně indexy. Pak může rapidní zrychlení přinést multi insert nebo uzavření do transakce. Jaká to je tabulka. MyISAM nebo InnoDb?

7. 11. 2014 15:05:58

https://webtrh.cz/diskuse/velky-import-csv-mysql#reply1067038

Petr Holomoj

(22 hodnocení)

7. 11. 2014 20:12:53

Tabulky jsou InnoDB, taky si myslím, že to musí být hned. Teď jsem si dal tu práci a prošel všechny zápisy do DB, někde bylo nadbytečné větvení, tak jsem ho odstranil. Zkusil jsem zápisy rozložit do 4 kroků automaticky se načítajících za sebou celkové zpracování na localním serveru bylo 46,47s na kompletní zapsaní všech údajů (549 produktů) do 6 tabulek v DB a z toho jsou mezi scripty 5s pauzy, takže celkový čas něco okolo 30s

Přesunul jsem to na hosting a opět totální kolaps, zkoušel jsem i jednoduchý zápis o jedné proměnné a na hostingu trvala funkce okolo 30s na 549 zápisů. Takže zakopaný pes bude asi tam.

7. 11. 2014 20:12:53

https://webtrh.cz/diskuse/velky-import-csv-mysql#reply1067037

Smazany ucet 253

(3 hodnocení)

14. 11. 2014 02:10:53

Zkus syntaxi SQL dotazu:

INSERT INTO tabulka (col1,col2,col3...atd...) VALUES

('data1','data2','data3'),

('data1','data2','data3')

to výrazně urychlí vkládání do databáze...

taky je dobré udělat dávkování a vkládat data postupně. názorná ukázka:

$baseQuery = "INSERT INTO tabulka (col1,col2,col3) VALUES ";

$query = $baseQuery;

// kolik dat najednou vložit:

$dose = 123;

$doseCounter = 0;

// cyklus for pro názornou ukázku .. místo toho se dá použít while ($data->read) nebo co používáš ..

for ($x=1; $x<=1000; $x++) {

$query .= " ( '{$x}. data pro col1' , '{$x}. data pro col2' , '{$x}. data pro col3' ) ,";

if ($x % $dose == 0) {

// provede se hromadné vložení dat 123 řádků najednou

//$mysqli->query( rtrim($query, ',') ); // samozřejmě je dobré kontrolovat provedení, logovat chyby atd...

printf( rtrim($query,',') . "
---------
"); // tady si můžeš zkontrolovat, jak ten hromadný insert vypadá ...

// nyní vynulujeme základní SQL příkaz

$query = $baseQuery;

// přičteme dávku

$doseCounter += $dose;

}

// na konci musíme zkontrolovat, jestli celkové množství dat není náhodou větší než počet vložených dat po určité dávce ...

if ($x > $doseCounter) {

// pokud nám ještě nějaká data zbyla, tak je vložíme posledním hromadným příkazem...

//$mysqli->query( rtrim($query, ',') );

printf( rtrim($query,',') . "
---------
");

}

---------- Příspěvek doplněn 14.11.2014 v 02:20 ----------

InnoDB je pro hromadné vkládání zkrátka pomalé (zkoušel jsem půl milionu insertů a rozdíl mezi InnoDB a MyISAM byl několik minut). Takže dávkovat a kontrolovat MAX_EXECUTION_TIME (získáš ho $maxTime = ini_get('max_execution_time'); ) ... poté v průběhu scriptu kontrolovat čas a pokud dosáhneš 90%, tak vytvořit soubor, kde bude nějaké ID posledního řádku ... pokud tento souboru bude existovat, tak ho na začátku scriptu otevřeš, načteš si ID, přeskočíš předchozí řádky a pokračuješ dokud nedojde script konce....) ... takto mi fungují například čtečky feedů, které mají přes 50MB a více než 20 tisíc produktů

snad to pomůže ...

14. 11. 2014 02:10:53

https://webtrh.cz/diskuse/velky-import-csv-mysql#reply1067036

Pro odpověď se přihlašte.

Přihlásit