Zpracování CSV v PHP pomocí SQL

K útrapám sysadmina provozujícího privátní virtuální webhosting patří i občasné ladění cizího kódu. Logování pomalých dotazů na databázi dokáže odhalit neoptimální návrhy tabulek a indexů, ale v případě dávkových importů a exportů, typicky právě z CSV, už velmi záleží i na kvalitě a optimalizaci samotného kódu. Jelikož PHP je jazyk od neprogramátorů pro neprogramátory, občas mě některé kousky kódu dojímají natolik, že mám dotyčného (ne)programátora chuť políbit betonovou tvárnicí.

Tak takhle ne

Před nějakým časem jsem nastavoval cronjob pro jeden takový import. Skript načetl CSV s informacemi o výrobcích a pak je porovnával s daty v databázi a aktualizoval, kde bylo potřeba. Hned první den mi přišla zpráva, že cronjob selhal, protože skriptu došla paměť. Na serveru mám memory_limit nastavený na 128 MB, což tedy u PHP rozhodně nepovažuji za málo. A už vůbec ne, pokud jediným úkolem skriptu je zpracovat 2,5 MB velké CSV. Letmé nahlédnutí do kódu ukázalo, že milý programátor používá jakýsi framework nejen pro frontend, ale i pro dávková zpracování a načítá s ním celou databázovou tabulku pro následné porovnání. A hloupý framework, místo aby pro práci s databází použil kurzory a data vracel, až budou potřeba, raději načte komplet celou tabulku jako dvourozměrné pole a celý gigaobjekt vrátí naráz. Tohle by bylo celkem pochopitelné u nějakého číselníku, ne však u tabulky zboží o desítkách sloupců a tisících řádků. Stejně tak i import z CSV probíhal tak, že se načetl úplně celý soubor a pak se teprve postupně, řádek po řádku, porovnávalo.

Po mém sáhodlouhém mailu protkaném několika invektivami byl skript optimalizován alespoň tak, že CSV se načítá po úsecích o 100 řádcích a korespondující data až poté načtou i z databáze. Světe div se, skriptu teď stačí jen nějakých 30 MB. Stále by to ale šlo ale lépe. Stejně jako jsem již před lety psal u článku o profilování pomocí XDebugu, i teď zopakuju, že mantry jako „výpočetní výkon je levný“, „paměť je levná”, „diskový prostor je levný” jsou jen chabými výmluvami pro nehorázné plýtvání. Proč by měl program sežrat 500 MB paměti, když může stejnou práci odvést i s pěti? Proč by měl propálit minuty procesorového času na exponenciálním algoritmu, když chytře navržený polynomiální by výpočet zvládl v řádu sekund? Jediná odpověď, která mě napadá, je prostě proto, že jsou lidi líní nebo hloupí. V horším případě oboje. Abych se tu nerozčiloval tak obecně, tak i v tomto konkrétním případě práce s CSV se dá ušetřit spousta vymýšlení prostě jen použitím správných nástrojů. Vždyť CSV je jen určitým způsobem formátovaná tabulka. A nástroj na efektivní správu tabulek a relací mezi nimi je v drtivé většině PHP instalací už dostupný. Je jím databázový systém, který nejen, že CSV umí načíst jako databázovou tabulku, ale je navržen a napsán se zřetelem na to, aby nad ní uměl efektivně provádět různá vyhledávání, seskupování a další úkony, které by se v samotném PHP prováděly jen velmi obtížně a sežraly další kvanta systémových prostředků. Databázové systémy často používané na VPS, tedy MySQL/MariaDB, PostgreSQL a SQLite s CSV soubory umí pracovat docela obstojně. Bohužel importy a exporty dat ze souborů nejsou nijak standardizovány a PHP dělá všechno proto, aby práci s těmito proprietárními funkcemi znepříjemnilo. Není tedy divu, že se jejich použití v aplikační divočině často nevidí.

Mikrobenchmark

Níže uvádím několik příkladů, kde onen zmiňovaný CSV soubor několika způsoby načítám k dalšímu zpracování (tj. nevypisuji nebo nezpracovávám jej rovnou, ale držím jej někde v paměti) a měřím, jak dlouho načtení trvá a pomocí memory_get_peak_usage() i kolik paměti při tom PHP spotřebuje. Paměť, kterou alokují jiné procesy, web server nebo databázové systémy mě v tomto případě nezajímá, protože je spravována daleko efektivněji a není zahrnuta direktivou memory_limit. CSV obsahuje citlivá data, takže jej nemůžu publikovat, ale mohu vám o něm říct, že obsahuje 42 sloupců a 8550 řádků. Je v Microsoftím formátu (hodnoty oddělené středníkem) a průměrná délka řádku je 313 bajtů. Testuji v PHP 7.0.8 CLI z repozitářů Ubuntu 16.04. Připomínám také, že simuluji v prostředí VPS, nikoliv VM nebo kontejnery, takže nějaké exec(), passthru(), popen(), system() a další hrůzy nepřichází v úvahu.

fopen() fgetcsv()

Bavíme-li o mnou odsuzované variantě načítání celého CSV, pak i zde existuje několik různých způsobů, jak úkol provést. Ye olde way kompatibilní s PHP 4 by vypadala zhruba následovně:

$table = [];
if ($handle = fopen('file.csv', 'r')) {
	while (!feof($handle)) {
		$table[] = fgetcsv($handle, 8192, ';');
	}
	fclose($handle);
}

Tento způsob má hned několik úskalí. Jednak si programátor musí sám pohlídat, co se má dít v případě, že soubor neexistuje, protože pokud dojde k pokusu o otevření souboru, který neexistuje, fopen() vyhodí pouze warning a nepřeruší tak běh skriptu. Další prostor pro problémy číhá u druhého parametru funkce fgetcsv(). Ten totiž říká, jak velký buffer se má alokovat a kolik bajtů se má načíst a pokud řádek přesáhne tento limit, zbývající data se načtou jako další řádek a problémy s konzistencí jsou na světě. V PHP 5.1.0 se tento parametr stal volitelným, a pokud je nastaven na nulu, PHP si samo najde konec řádku. Bohužel ale spousta pravěkých neudržovaných skriptů používá stále variantu s pevně zadanou délkou.

Průměrný čas zpracování: 103,25 ms, Využití paměti PHP: 34,03 MB.

SplFileObject

Pokrokovější programátoři použijí objektový přístup. Principiálně se jedná o totéž jako v případě výše, ale SplFileObject mnoho věcí zjednoduší. Neúspěšné otevření souboru vyvolá výjimku. Pokud ji programátor neošetří, zpracování skriptu se zastaví, což je v tomto případě pravděpodobně žádoucí. Dále není potřeba zadávat žádný počet bajtů u metody fgetcsv(), protože si sama hledá konce řádků.

$table = [];
$handle = new SplFileObject('file.csv');
while (!$handle->eof()) {
	$table[] = $handle->fgetcsv(';');
}

Jediná věc, která se mi u tohoto přístupu úplně nelíbí, je nemožnost explicitního zavření handle souboru. V PHP se taková věc řeší nastavením proměnné na null a garbage collector se pak někdy nějak sám postará o úklid. Výkon i spotřeba paměti jsou prakticky stejné jako u tradičního způsobu. Zápis mi ale připadá přehlednější.

Průměrný čas zpracování: 103,84 ms, Využití paměti PHP: 34,02 MB

MySQL / MariaDB

Tady už to začíná být zajímavé. MySQL od verze 5.5 umí zaříkadlo LOAD DATA INFILE. Jedná se o proprietární rozšíření umožňující načíst jakýkoliv textový soubor jako data tabulky. Syntaxe je celkem šílená, ale s trochou zápasení lze naroubovat i na Microsoftí CSV formát. Zrada ovšem nastává, když si uvědomíte, že je soubor nějakým způsobem potřeba dopravit přímo databázovému enginu. V případě, že se soubor i databáze nachází na stejném serveru a uživatel, pod kterým mysqld proces běží má práva ke čtení tohoto souboru, může si server soubor načíst přímo. Databázový uživatel, který tuto akci provádí, ale potřebuje globální oprávnění FILE, které na VPS zpravidla přidělováno není. Všechny tyto problémy odstraní kouzelné slovíčko LOCAL. To totiž instruuje databázový server, že nemá soubor hledat na disku, ale že mu jej přes socket dopraví přímo klient, tedy PHP. Aby LOCAL fungoval, musí na serveru být povolena proměnná local_infile, což ve výchozím stavu je.

Dalším problémem pak je, že data musí být načtena do existující tabulky s existující strukturou. V zásadě se nabízí tři možná řešení. Buď víte, že CSV na prvním řádku obsahuje názvy sloupců a bezmezně mu věříte, takže pomocí fgetcsv() přečtete pouze první řádek a vytvoříte dočasnou tabulku se stejnou strukturou a všemi sloupci typu TEXT. Jelikož vkládání čehokoliv z externích zdrojů bez jakýchkoliv sanity checků je cesta do pekel, druhá varianta zahrnuje také načtení prvního řádku a spočtení sloupců, ale struktura pak bude vytvořena generickými názvy jako col1, col2, col3, atd. Třetí, a pokud dopředu znáte strukturu CSV tak pravděpodobně i nejefektivnější způsob, je vytvoření persistentní tabulky se strukturou obsahující přesné datové typy, délky a případně i indexy. Takovou tabulku při každém užití jen přes TRUNCATE vysypete a naplníte daty z CSV. Pokud je struktura skutečně dobře optimalizována, můžete v klidu načíst miliony řádků a pak se v nich přehrabovat rychlostí blesku. V příkladu níže je použita druhá varianta, tj. přečtení prvního řádku (který obsahuje nyní nepotřebné názvy sloupců) a vytvoření struktury s generickými názvy. Obrovskou výhodou je fakt, že LOAD DATA INFILE funguje i při použití PDO. Pouze se při vytváření spojení musí nastavit parametr PDO::MYSQL_ATTR_LOCAL_INFILE na hodnotu TRUE. A pozor, v některých verzích PHP existuje bug vinou kterého se nastavení parametru neprojeví. Zatím jsem se s tím setkal pouze na PHP 5.5.9 na Ubuntu 14.04. PHP 7.0.8 na Ubuntu 16.04 funguje bez problému.

$conn = new PDO('mysql:host=localhost;dbname=somedb', 'someuser', 'somepass', [PDO::MYSQL_ATTR_LOCAL_INFILE => TRUE]);

$handle = new SplFileObject('file.csv');
$max_columns = count($handle->fgetcsv(';'));
$columns = array_map(function($i){return 'col'.$i.' TEXT';}, range(1, $max_columns));
$conn->query('CREATE TEMPORARY TABLE temp_csv ('.implode(',', $columns).')');

$conn->query("LOAD DATA LOCAL INFILE '".$handle->getRealPath()."' INTO TABLE temp_csv FIELDS TERMINATED BY ';' ENCLOSED BY '\"' LINES TERMINATED BY '\\r\\n' IGNORE 1 LINES");

Výkon je v tomto případě ovlivněn spoustou faktorů. Vyrovnávací pamětí disků, cache operačního systému, nastavením a škálovatelností databázového engine, strukturou tabulky a případnou přítomností nebo absencí indexů. Během mých testů se prvních 25 spuštění plácalo kolem 800 ms, ale když MariaDB 10.0.28 pochopila, že s ní chci laškovat a udělala si na mě místo, import se podstatě zrychlil.

Průměrný čas zpracování: 98,66 ms, Využití paměti PHP: 0,39 MB

PostgreSQL

Princip načítání CSV v PostgreSQL je stejný jako u MySQL. Kouzelné slovíčko je tentokrát COPY FROM. Opět buď načítá lokálně přímo server, v kterémžto případě databázový uživatel požadující takovou akci musí mít práva superusera. Anebo se přihodí slůvko STDIN a postgres pak očekává data na socketu. Tady se nedokonalosti PHP driverů projeví na plné čáře. PDO sice nabízí donedávna nedokumentovanou metodu pgsqlCopyFromFile(), ale ta v současné verzi neumí nastavit CSV formát a data se pokouší kopírovat jako plaintext pouze oddělený příslušným oddělovačem, což jaksi samozřejmě nemá kýžený efekt. Workaroundem v tomto případě je použití non-PDO funkcí a manuálního natlačení souboru do databáze.

Aby toho nebylo málo, tak ani non-PDO driver se nechová úplně korektně. Dočasné tabulky mají dle dokumentace totiž zanikat ve chvíli, kdy se ukončí session. Člověk by čekal, že to je přesně to, co funkce pg_close() dělá, ale v PHP-FPM se session ukončí teprve tehdy, až chcípne worker, který skript zpracovával, což v závislosti na nastavení poolů nemusí být nikdy. Workaround k tomuto je buď explicitně použít transakce a tabulku vytvářet s ON COMMIT DROP anebo jednoduše tabulku pokaždé zahodit a vytvořit znova.

$conn = pg_pconnect('dbname=somedb user=someuser password=somepass');

$handle = new SplFileObject('file.csv');
$max_columns = count($handle->fgetcsv(';'));
$columns = array_map(function($i){return 'col'.$i.' TEXT';}, range(1, $max_columns));
pg_query($conn, 'DROP TABLE IF EXISTS temp_csv');
pg_query($conn, 'CREATE TEMPORARY TABLE temp_csv ('.implode(',', $columns).')');

pg_query($conn, "COPY temp_csv FROM STDIN DELIMITER ';' CSV");
while (!$handle->eof()) {
	pg_put_line($conn, $handle->fgets());
}
pg_end_copy($conn);

Stejně jako u MySQL/MariaDB je výkon velmi variabilní podle nastavení databázového serveru, nicméně stejně jako u MariaDB i PostgreSQL strčil rychlostí zpracování PHP do kapsy a to i přesto, že PHP brzdilo tím, že soubor muselo samo přečíst a po kouskách PostgreSQL serveru nasoukat.

Průměrný čas zpracování: 35,57 ms, Využití paměti PHP: 0,37 MB

Co se týče PostgreSQL a PDO, koukal jsem do zdrojáků a v pgsql_driver.c, kde je metoda pgsqlCopyFromFile() definována a kde ovladač vytváří samotný SQL dotaz, není o formátu ani zmínka. Jelikož momentálně nemám tendence se oddávat tak bohulibým činnostem jako je vrtání se v kódu PHP, otevřel jsem na to request. Třeba se toho nějaký jiný blázen chopí. Až to jednou bude fungovat, bude to vypadat zhruba takto

$conn = new PDO('pgsql:dbname=somedb', 'someuser', 'somepass');

$handle = new SplFileObject('file.csv');
$max_columns = count($handle->fgetcsv(';'));
$columns = array_map(function($i){return 'col'.$i.' TEXT';}, range(1, $max_columns));
$conn->query('CREATE TEMPORARY TABLE temp_csv ('.implode(',', $columns).')');

$conn->pgsqlCopyFromFile('temp_csv', $handle->getRealPath(), ';', "\\\\N", null, 'CSV');

SQLite

SQLite 3 samo o sobě umí importovat CSV velmi jednoduchým způsobem skrze CLI. Dokonce jde tak daleko, že pokud tabulka, do které se pokoušíte importovat, ještě neexistuje, přečte si první řádek CSV souboru a za použití nalezených řetězců strukturu automaticky vytvoří. Prostě napíšete jen

.separator ';'
.import file.csv temp_csv

Jednoduché, elegantní, magické. Škoda jen, že PHP z toho neumí lautr nic, protože tato vlastnost je implementovaná pouze v shellu SQLite klienta a nikoliv v jeho knihovnách, které PHP používá. SQLite ale taky umí pracovat s in-memory databázemi, které se pro účel zpracování CSV pořád hodí více než pole v PHP. Bohužel je ale nutno je nakrmit ručně, což naštěstí s použitím prepared statement není nic těžkého ani výpočetně náročného.

$conn = new PDO('sqlite::memory:');

$handle = new SplFileObject('file.csv');
$max_columns = count($handle->fgetcsv(';'));
$columns = array_map(function($i){return 'col'.$i.' TEXT';}, range(1, $max_columns));
$conn->query('CREATE TABLE temp_csv ('.implode(',', $columns).')');

$stmt = $conn->prepare('INSERT INTO temp_csv VALUES ('.implode(',', array_fill(0, $max_columns, '?')).')');
while (!$handle->eof()) {
	$stmt->execute($handle->fgetcsv(';'));
}

Průměrný čas zpracování: 144,92 ms, Využití paměti PHP: 0,38 MB

Čas je poněkud nepřekvapivě nejhorší ze všech testovaných, ale pořád s ledovým klidem použitelný pro cronjob, který běží jednou denně. Co se nám to ale stalo s pamětí? Byla použita in-memory databáze. SQLite nemá žádný server. A přece nebylo použito více než půl mega? Kam se ta databáze ztratila? Inu neztratila se, milé děti. PHP prostě nehorázně kecá. PHP je totiž strašně hloupoučké a dokáže počítat jen paměť, kterou používá samotný kód. SQLite je ale do procesu PHP načteno jako extension a PHP nejen že jeho paměť nevidí, ale dokonce jej ani nedokáže omezit. Pokud sysadmin neomezil paměť jiným způsobem, typicky přes ulimit nebo cgroups, z PHP se může stát Otesánek a sníst mámu a tátu i s celou serverovnou, nehledě na nějaký srandovní memory_limit. Server, který žádné takové omezení nemá je pak jedinou pitomoučkou SQLite databází možno sundat, protože se OOM killerem umlátí sám.

Takže...

Jak je vidno, použití databázových systémů dokáže práci s CSV tabulkami zpříjemnit, zrychlit a paměťově zeštíhlit. Předpokládá se ovšem, že serverové služby s tímto nepříliš standardním využitím počítají a také, že je použita buguprostá verze PHP. Vrátím-li se k poznámce o volbě vhodných nástrojů k určitým typům úkonů, ukazuje se, že s databázovými enginy víceméně žádné problémy nejsou a nevhodným nástrojem se v tomto případě zdá být PHP. Srovnám-li výše uvedené příklady třeba s pythonem, užití MySQL / MariaDB vypadá stejně, až na to, že neexistovala verze, kde by se něco šlendriánstvím rozbilo k nepoužitelnosti. Pro PostgreSQL má pythoní psycopg2 funkci copy_expert(), která CSV nebo jakékoliv jiné užití COPY FROM / TO umožňuje a to dokonce i bez problémů s ukončením session. A SQLite je svým návrhem tak jednoduché, že se musí krmit ručně vždycky, ale užovka je aspoň schopna říct, kolik paměti žere celý proces. A to nezmiňuji csv modul, který celou záležitost kolem zpracování CSV řeší jasným a uceleným způsobem.