DEV Community

hamdallah jodah
hamdallah jodah

Posted on Fully Autonomous

BatchFlow: sum, filter and group a huge CSV in PHP without building arrays

I just released BatchFlow 1.0.0, a PHP extension written in C. It scans CSV files and streams, filters rows, counts, sums and groups in native code, and returns only the result to PHP.

The loop we all write

$totals = [];
$h = fopen($path, 'rb');
fgetcsv($h);
while (($row = fgetcsv($h)) !== false) {
    $totals[$row[0]] = ($totals[$row[0]] ?? 0) + (int) $row[2];
}
Enter fullscreen mode Exit fullscreen mode

Every row becomes a PHP array, even when you only want a few totals.

The BatchFlow version

The examples use this sales.csv:

region,product,units,price
north,apple,10,1.5
south,pear,4,2.25
north,pear,7,2.25
east,apple,3,1.5
south,apple,8,1.5
north,fig,,4
Enter fullscreen mode Exit fullscreen mode
use BatchFlow\Csv;

$schema = ['region' => 'string', 'product' => 'string', 'units' => 'int64', 'price' => 'float64'];
$query = Csv::scan($path, $schema);

echo $query->count(), "\n";
echo $query->sum('units'), "\n";
echo $query->avg('units'), "\n";
echo json_encode($query->groupSum('region', 'units')), "\n";
Enter fullscreen mode Exit fullscreen mode
6
32
6.4
[["north",3,17],["south",2,12],["east",1,3]]
Enter fullscreen mode Exit fullscreen mode

An empty field is null and is skipped by sum, min, max and avg. groupSum returns [key, rows, sum] in first-seen order.

Filters and rows

$apples = Csv::scan($path, $schema)->whereEq('product', 'apple')->whereGte('units', 5);

echo $apples->count(), "\n";
echo json_encode($apples->toArray()), "\n";
Enter fullscreen mode Exit fullscreen mode
2
[{"region":"north","product":"apple","units":10,"price":1.5},{"region":"south","product":"apple","units":8,"price":1.5}]
Enter fullscreen mode Exit fullscreen mode

Filters are immutable and chainable: whereEq, whereNe, whereGt, whereGte, whereLt, whereLte, whereIn.

Many aggregates in one pass

echo json_encode($query->groupAgg(['region', 'product'], [['sum', 'units'], ['max', 'price']])), "\n";
Enter fullscreen mode Exit fullscreen mode
[["north","apple",1,10,1.5],["south","pear",1,4,2.25],["north","pear",1,7,2.25],["east","apple",1,3,1.5],["south","apple",1,8,1.5],["north","fig",1,0,4]]
Enter fullscreen mode Exit fullscreen mode

One to four keys and up to 16 aggregates.

Streams and cursors

The schema lists every column of the file, in file order. This example uses a second, two-column file.

$path2 = sys_get_temp_dir() . '/units.csv';
file_put_contents($path2, "region,units\nnorth,10\nsouth,4\nnorth,7\neast,3\nsouth,8\n");

$stream = fopen($path2, 'rb');
$total = Csv::scanStream($stream, ['region' => 'string', 'units' => 'int64'])->sum('units');
echo $total, "\n";

$cursor = Csv::scan($path2, ['region' => 'string', 'units' => 'int64'])->cursor();
while ($rows = $cursor->fetch(2)) {
    echo json_encode($rows), "\n";
}
$cursor->close();
Enter fullscreen mode Exit fullscreen mode
32
[{"region":"north","units":10},{"region":"south","units":4}]
[{"region":"north","units":7},{"region":"east","units":3}]
[{"region":"south","units":8}]
Enter fullscreen mode Exit fullscreen mode

scanStream reads any PHP stream. A cursor returns rows in batches, so memory stays flat.

Load once with Table

use BatchFlow\Table;

$table = Table::fromCsv($path, $schema);
echo json_encode($table->whereEq('product', 'apple')->groupSum('region', 'units')), "\n";
Enter fullscreen mode Exit fullscreen mode
[["north",1,10],["east",1,3],["south",1,8]]
Enter fullscreen mode Exit fullscreen mode

The file is read once and kept as typed columns. Repeated queries do not read it again.

Errors you can handle

For a file with the lines region,units, north,10 and south,many, and the schema ['region' => 'string', 'units' => 'int64'], sum('units') throws:

BatchFlow\CsvException: row 2 (line 3, offset 22): column "units": expected int64, got malformed value
Enter fullscreen mode Exit fullscreen mode

Messages carry row, line, offset and column. They never carry record bytes, so they are safe to log. BatchFlow checks memory_limit before it allocates, so a result that is too big is a LimitException and not a fatal error (best effort for small blocks on a fragmented heap; see the threat model).

Benchmarks (with the conditions)

One machine: Intel Xeon Platinum 8358 (Linux x86-64, AVX2), PHP 8.3.36 NTS. Files of 1,000,000 rows. One core per timed job, nothing else on it. Each range covers all tested cases, in a long-running process (warm) and a new process (fresh). "Fastest JIT'd PHP loop" means PHP with opcache and the tracing JIT on. Results are checked to be identical to the PHP loops.

What Result
Group-by (groupCount, groupSum, 10 to 100,000 keys) vs the fastest JIT'd PHP loop 3.7x to 13.6x faster
Group-by vs DuckDB CLI, one thread 2.6x to 5.5x faster
Streaming sum vs the fastest JIT'd PHP loop 5.2x to 15.4x faster. Peak memory 0.84 times that of the streaming PHP loop (about 16% lower), constant at 23,280 KB in every case
toArray vs the fastest PHP CSV loader 2.8x to 7.5x faster

Where it loses

  • On group-by with 10,000,000 rows where most rows are kept, 4-thread DuckDB on four cores is faster: it takes 0.75 to 0.88 of BatchFlow's time. BatchFlow has no threads. On the other 10,000,000-row group-by cases it ties (0.97 to 1.00).
  • At 100,000 rows, one fresh-process cell was only 2.7x faster than the PHP loop. The 3x claim is for 1,000,000 rows and up.
  • No arm64 speed claim. The arm64 build is tested for correct results only.
  • ZTS is not supported.

What it is NOT

Not a CSV writer, not a general CSV parsing library, and not a database. No joins, no SQL. It reads CSV and answers questions about it.

What is next

JSON input (NDJSON and top-level JSON arrays) with the same API is coming soon, in the next release. No speed or memory claim for it yet.

Install

pie install hamdallah90/batchflow
Enter fullscreen mode Exit fullscreen mode

On minimal images such as php:8.3-cli, install libtool first. Then add extension=batchflow to php.ini. Or build from source: phpize && ./configure --enable-batchflow && make && sudo make install.

PHP 8.3, 8.4 and 8.5, non-thread-safe. Linux x86-64 and arm64, and macOS. MIT license.

Repo: https://github.com/hamdallah90/php-batchflow (issues and stars welcome)

Release notes: https://github.com/hamdallah90/php-batchflow/releases/tag/v1.0.0

Full results and method: benchmarks/RESULTS.md

Top comments (0)