ClickHouse is a columnar database designed for analytical queries over large volumes of data, such as events, logs and metrics. It answers questions such as “how many orders did each region place in each hour of the last month” across billions of rows in a fraction of a second. The ClickHouse driver lets a Kipchak API run those queries, return the results to its clients, and write new data into ClickHouse.
The driver is free and MIT-licensed. It was listed on the public roadmap, and was tested against ClickHouse 25.8 over HTTP and HTTPS. It pairs with the Enterprise Kafka driver: events are published to Kafka, loaded into ClickHouse, and queried through the API.
Queries
$result = ClickHouse::get()->select(
'SELECT toDate(at) AS day, count() AS hits
FROM events
WHERE user_id = {user:UInt64} AND at >= {from:DateTime}
GROUP BY day ORDER BY day',
['user' => $userId, 'from' => new DateTimeImmutable('-7 days')],
);
Each value is written in the SQL as a placeholder with its ClickHouse type, and sent to ClickHouse separately from the query. ClickHouse parses the value as that type, so a value taken from a request cannot change the query. Dates are sent as Unix timestamps, so that a difference in time zone between the API and the database cannot change their meaning.
Results are returned as PHP values with their column types. Integers that exceed PHP’s integer range, and decimals, are returned as strings, so that no precision is lost. Each result also reports how many rows ClickHouse read and the identifier of the query, which can be found in ClickHouse’s query log.
Results of any size
An export or report can return millions of rows. each() returns rows as ClickHouse sends them, and holds one row in memory at a time:
foreach ($clickhouse->each('SELECT * FROM events WHERE day = {day:Date}', ['day' => $day]) as $row) {
fwrite($csv, implode(',', $row) . "\n");
}
The timeout applies to each wait for the next part of the result, not to the whole stream, so a long export is not cut off while data is still arriving. If the loop stops early, the connection is closed and ClickHouse cancels the query, so the server does not continue work that nobody will read.
Inserting data
insert() accepts rows from an array or a generator, and sends them in batches of 10,000 rows by default, compressed with gzip. ClickHouse works best with fewer, larger inserts. When many requests each insert a few rows, the driver can ask ClickHouse to collect them and write them together:
$clickhouse->insert('events', [$event], ['async_insert' => 1, 'wait_for_async_insert' => 1]);
Statements such as table changes and INSERT ... SELECT are run with execute(), and a long-running query can be cancelled from another request by its identifier.
Failover and caching
A connection can list several ClickHouse hosts. When a host refuses the connection, the statement never reached it, so the driver moves on to the next host. Read queries also move on after other connection failures. Timeouts are never retried, because the query may still be running on the server. A host that has failed is skipped for 30 seconds, and the failure is logged.
Results of selected queries can be cached for a given number of seconds, in worker memory or through Kipchak’s Filecache or Valkey drivers. This suits dashboards that many users request with the same parameters.
Built on Kipchak’s drivers
The driver uses ClickHouse’s HTTP interface, so it needs no PHP extension and runs on the hardened runtime image. Requests are sent through Kipchak’s HTTP driver and warnings through the Logger driver. Under FrankenPHP’s worker mode, nothing is held open between requests except the list of hosts that have recently failed.
In worker-mode testing with eight workers, the driver served 2,600 mixed concurrent requests without error, ran approximately 150,000 queries in a ten-minute test, and recovered when ClickHouse was restarted under load.
Installation
The ClickHouse driver is free and MIT-licensed:
composer require kipchak/driver-clickhouse
The configuration reference, including how each PHP type is sent to ClickHouse, is in the ClickHouse driver documentation.