Some data grows faster than anything else on a site: page views, uptime checks, audit events, measurements. Each row is small and never edited. The questions asked of it are aggregates: how many, how fast, per hour, over the last month. MariaDB and PostgreSQL store these rows well. Aggregating across a very large number of them for every report is a different job, and it is the job a columnar database is built for.
We use ClickHouse for that job, and we maintain drupal/clickhouse to reach it from Drupal. In our own platform, records stay in MariaDB and machine observations go to ClickHouse. This article walks through the module as we use it: configuration, parameterized queries, table design, rollups and batched writes. The examples are based on the first release, 1.0.0-alpha1, on Drupal 11.
It reads and appends, and it is not a database driver
The first design decision is what the module leaves out. There is no Drupal database driver for ClickHouse, and the README says there will not be one. Drupal's storage contract expects UPDATE and DELETE statements, transactions, unique keys and read-your-writes. ClickHouse runs updates and deletes as asynchronous background mutations, offers transactions only as an experimental feature, uses its primary key as a sparse sort index rather than a constraint, and may not show an insert immediately. A driver would pass much of the database test suite and then fail on exactly the operations entity storage is built from.
So the module offers what the engine is good at. query() and its shorthands read. insert() appends rows in bulk, which is ClickHouse's strongest operation. command() runs statements that return no rows: table definitions and mutations. The module talks to ClickHouse over its HTTP interface (port 8123 by default) through Drupal's own HTTP client, so it needs no PHP extension and no extra Composer library.
Connections live in settings.php
Connections are declared in settings.php, like $databases, so credentials never travel through configuration export into a repository. We declare two: one that reads, and one that may write.
// Reports and pages read through this one.
$settings['clickhouse']['reporting'] = [
'host' => getenv('CLICKHOUSE_HOST'),
'database' => 'metrics',
'username' => 'drupal_ro',
'password' => getenv('CLICKHOUSE_RO_PASSWORD'),
'settings' => ['max_execution_time' => 10],
];
// Appends, table definitions and erasure. Only this one may write.
$settings['clickhouse']['events'] = [
'host' => getenv('CLICKHOUSE_HOST'),
'database' => 'metrics',
'username' => 'drupal',
'password' => getenv('CLICKHOUSE_PASSWORD'),
'write' => TRUE,
];
A few things to notice. database is required: a ClickHouse session does not default to the database you created, and a connection without one reports that your tables do not exist. Writing is off unless the entry says 'write' => TRUE. A read-only connection also sends ClickHouse's own readonly=2 setting. Pair it with a ClickHouse user that may only read, so the server refuses writes whatever the code sends. Credentials travel as request headers rather than in the URL, which keeps them out of access logs. use_proxy defaults to FALSE, so internal queries are not routed through an egress proxy set in the environment.
Code reaches a connection through the clickhouse service, \Drupal::service('clickhouse')->get('reporting'), or through ClickHouseFactory injected into your own service. Called with no name, get() returns the connection named default.
Reading with bound parameters
Values are bound on the server. The statement says {path:String}, the request carries the value separately, and ClickHouse parses it as data. No value is ever escaped into the SQL string. This is the query behind an hourly traffic chart for one page:
use Drupal\clickhouse\Query\Param;
$rows = \Drupal::service('clickhouse')->get('reporting')->queryAll(
'SELECT toStartOfHour(viewed) AS hour_start,
count() AS views,
quantile(0.95)(duration_ms) AS p95_ms
FROM page_view
WHERE path = {path:String} AND viewed >= {since:DateTime}
GROUP BY hour_start
ORDER BY hour_start',
['path' => '/pricing', 'since' => Param::dateTime(time() - 7 * 86400)],
);
The values in each row follow the column types. Every request asks for date_time_output_format=unix_timestamp, so hour_start arrives as an integer instant that does not depend on the server's time zone. count() is a UInt64 and comes back as an integer when it fits PHP's range; a 64-bit value that does not fit comes back as a string rather than a corrupted number, so code that compares large ids must expect strings. The quantile is a float.
Before anything is sent, the binder checks the statement against the parameters. A placeholder with no value, or a value with no placeholder, throws right away instead of becoming a filter that silently does nothing. Table and column names cannot be bound in any engine, so they go through Identifier::quote(), which accepts plain identifiers and refuses everything else.
For the shapes that repeat, there is a small builder. Anything analytical stays SQL through addExpression(), which takes its own bindings:
$clickhouse = \Drupal::service('clickhouse')->get('reporting');
$since = time() - 86400;
$result = $clickhouse->select('page_view')
->addExpression('toStartOfInterval(viewed, INTERVAL {step:UInt32} SECOND)', 'bucket', ['step' => 900])
->addExpression('count()', 'views')
->condition('path', ['/pricing', '/contact'], 'IN')
->condition('viewed', Param::dateTime($since), '>=')
->groupBy(['bucket'])
->orderBy('bucket')
->execute();
$rows = $result->rows();
$cost = $result->statistics();
condition() with IN binds the list as one array parameter and renders it with has(), because ClickHouse treats an array and a tuple differently. The builder has no joins, on purpose: write a join in SQL and pass it to query(). The ResultSet also carries the server's statistics (elapsed time, rows and bytes read) and rowsBeforeLimit(), the count a pager needs without a second query (a lower bound unless the exact_rows_before_limit setting is on). On a ReplacingMergeTree table, call final(), or you read duplicates until a background merge has run.
Tables: the sort key and retention decide most of it
A module can ship its own ClickHouse tables through command() on the writable connection. CREATE TABLE IF NOT EXISTS makes the statement safe to run again, from an update hook or a deploy step; we run ours on every deploy. In this example it runs once, on install:
/**
* Implements hook_install().
*/
function my_module_install(bool $is_syncing): void {
\Drupal::service('clickhouse')->get('events')->command(
"CREATE TABLE IF NOT EXISTS page_view (
path String,
viewed DateTime('UTC'),
duration_ms UInt32
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(viewed)
ORDER BY (path, viewed)
TTL viewed + INTERVAL 90 DAY"
);
}
ORDER BY is the decision that matters most. It is the sort key and the sparse primary index, so a query that filters on path and a time range reads one narrow strip of the table instead of all of it. Put the columns your questions filter on first, in the order they narrow. PARTITION BY keeps each month in parts of its own; parts from different partitions are never merged together. TTL sets the retention, 90 days in this example: ClickHouse removes expired rows when it merges parts in the background, so the table does not grow without limit and nobody has to write a cleanup job. Expired rows can stay readable until that merge has run. DateTime('UTC') pins the column's time zone, so toStartOfHour() and friends mean the same thing on every server.
Rollups with a materialized view
A dashboard that shows a year of daily totals does not need to scan a year of raw rows every time. A materialized view keeps a rollup current as rows arrive. This is plain ClickHouse SQL; send each statement through its own command() call:
CREATE TABLE IF NOT EXISTS page_view_daily (
day Date,
path String,
views UInt64,
duration_ms_sum UInt64
)
ENGINE = SummingMergeTree
ORDER BY (path, day)
TTL day + INTERVAL 2 YEAR;
CREATE MATERIALIZED VIEW IF NOT EXISTS page_view_daily_mv
TO page_view_daily AS
SELECT
toDate(viewed) AS day,
path,
count() AS views,
sum(duration_ms) AS duration_ms_sum
FROM page_view
GROUP BY day, path;
The view runs on every insert into page_view and writes the aggregated rows into page_view_daily. It only sees rows inserted after it exists, so backfill older data with an INSERT … SELECT if you add it later. SummingMergeTree adds up rows with the same sort key when parts merge, and merges happen in the background, so reads still use sum() with GROUP BY. Store sums and counts, not averages: an average of averages is wrong, while sum(duration_ms_sum) / sum(views) is right. The rollup has its own, longer retention (two years here, again an example), so the raw table can stay short.
Writing: batch, never one insert per request
ClickHouse writes every insert as at least one new part and merges parts in the background. A thousand single-row inserts therefore cost far more than one insert of a thousand rows, which is the opposite of what a transactional database teaches. Every write path should collect rows and send them together.
When one process produces many rows, such as a cron run, a queue worker or an import, collect them in a service and send them per table. Drupal calls destruct() on services tagged needs_destruction when the request ends, after the response is sent, so the remainder goes out without anyone remembering to flush. This is the pattern we use for machine observations:
namespace Drupal\my_module;
use Drupal\clickhouse\ClickHouseFactory;
use Drupal\Core\DestructableInterface;
final class ClickHouseBuffer implements DestructableInterface {
private const BATCH = 1000;
/** @var array<string, list<array<string, scalar>>> */
private array $waiting = [];
public function __construct(private readonly ClickHouseFactory $clickhouse) {}
public function add(string $table, array $row): void {
$this->waiting[$table][] = $row;
if (count($this->waiting[$table]) >= self::BATCH) {
$this->flush();
}
}
public function flush(): void {
$waiting = $this->waiting;
$this->waiting = [];
foreach ($waiting as $table => $rows) {
$this->clickhouse->get('events')->insert($table, $rows);
}
}
public function destruct(): void {
$this->flush();
}
}
The service is registered with the module's clickhouse service and the destruction tag:
services:
Drupal\my_module\ClickHouseBuffer:
arguments: ['@clickhouse']
tags:
- { name: needs_destruction }
A web request usually produces one row, a page view, so buffering inside the request would still mean one insert per request. For those rows, record each one in a Drupal queue with createItem(). The default queue lives in the SQL database, which handles single-row writes well. Cron then drains the queue in batches (hook classes need Drupal 11.1 or later; 5000 rows per run is an example):
namespace Drupal\my_module\Hook;
use Drupal\clickhouse\ClickHouseFactory;
use Drupal\Core\Hook\Attribute\Hook;
use Drupal\Core\Queue\QueueFactory;
use Symfony\Component\DependencyInjection\Attribute\Autowire;
final class PageViewHooks {
public function __construct(
private readonly QueueFactory $queueFactory,
#[Autowire(service: 'clickhouse')]
private readonly ClickHouseFactory $clickhouse,
) {}
#[Hook('cron')]
public function sendPageViews(): void {
$queue = $this->queueFactory->get('my_module_page_view');
$claimed = [];
while (count($claimed) < 5000 && ($item = $queue->claimItem())) {
$claimed[] = $item;
}
$rows = array_map(static fn (object $item): array => $item->data, $claimed);
$this->clickhouse->get('events')->insert('page_view', $rows);
foreach ($claimed as $item) {
$queue->deleteItem($item);
}
}
}
The rows are sent before any queue item is deleted. A failed insert throws, the items stay claimed, and they return to the queue when their lease runs out. An empty batch costs nothing: insert() with no rows returns without a request. The rows travel as JSONEachRow in the request body, so a batch is not limited by URL length. If you cannot batch upstream at all, ClickHouse has an async_insert setting; read what it does to durability before you turn it on.
Errors, tests and how we wire it
Errors are built for production. Exception messages never contain SQL or bound values; the server's reason is logged with its query id, and the exception points at the log. The class tells you what happened. ConnectionException means nothing ran, so a retry may help. QueryException means the server refused the statement, which is a fault in code. TimeoutException means the query hit its time limit. Query logging is off by default, and logging bound values is a separate switch, also off.
In our platform, one service owns both connections, and product code goes through it rather than holding a connection of its own. It adds the tenant to every read and every row, it treats an unreachable store as a clear "unavailable" state, and it still lets a refused query fail loudly.
Testing follows the same split we described in kernel tests vs unit tests. The transport behind the clickhouse.transport service is the module's one seam: its unit tests build connections over a transport that records what it was asked to send, so they run with no server anywhere. Its kernel test runs against a real ClickHouse and skips unless CLICKHOUSE_HOST is set, because wire formats and types surviving the round trip can only be checked against a server.
Limits and trade-offs
- No edits, no read-your-writes.
ALTER TABLE … DELETEthroughcommand()queues a mutation and returns before it has finished. It suits backfills and erasure requests, not application logic. If you want one per user action, the data belongs in the SQL database. - Floats are close, not exact. The module writes floats in plain decimal, because ClickHouse's JSON reader handles exponent notation less accurately. That makes inserts much more accurate but not exact. Use a
Decimalcolumn when exact values matter. - Not yet in Views or entity storage. 1.0 is the query and write layer, released as an alpha. A read-only entity storage handler, a Views query plugin and binary inserts are on the roadmap.
- Another service to run. ClickHouse needs its own memory, disk, backups and monitoring. When the SQL database answers a site's reports comfortably, it adds operations work without adding much value.
If your reporting has outgrown the SQL database, or you would like a second pair of eyes on a table design, talk to us. The module, its issue queue and its roadmap are on drupal.org.
