ClickHouse

ClickHouse is an open-source columnar database built to answer analytical questions over very large tables: hundreds of millions to billions of rows, aggregated in milliseconds. This module lets Drupal query and append to one, so a site can report on data that has outgrown what MySQL or PostgreSQL will summarise comfortably.

Both directions are supported. Reads get a safe query API with server-side parameter binding; writes get first-class bulk INSERT, which is the operation ClickHouse is best at. Appending page views, events, logs or audit records from Drupal is a normal use of this module, not a workaround.

When ClickHouse is the right tool

The pattern is always the same: a table that only ever grows, that you query in aggregate rather than by primary key, and that you want to keep for longer than your OLTP database would like.

• Traffic and product analytics. Page views, sessions, referrers, funnels and retention over years of history, without a third-party tracker and without the data leaving your infrastructure.
• Event and behavioural telemetry. Searches, clicks, filter use and feature adoption: the high-volume signals that are useful in aggregate and uninteresting individually.
• Logs and audit trails. Application, access and security logs, or regulated audit records that must be retained for years and searched occasionally.
• Commerce and subscription reporting. Orders, carts, refunds and revenue rolled up across long periods, kept away from the transactional tables that serve checkout.
• Time series and monitoring. Sensor readings, IoT telemetry, queue depth, request latency, and anything else sampled on a schedule.
• Search and recommendation signals. Query logs, click-through and result quality, used to tune ranking.
• Media and learning platforms. Playback, progress and completion events, which arrive far faster than content does.

A useful signal that you have reached this point: a reporting query against a Drupal table has started needing its own index, its own cache, or its own cron job to precompute the answer.

When it is not

ClickHouse suits Keep in Drupal's database Append-only history Records that get edited Aggregates over many rows Single-entity reads and writes Long retention, TTL-based expiry Content, users, configuration Reporting and dashboards Anything needing a transaction

If your largest table has a few million rows and your reports are fast enough, you do not need this module. It earns its place when the volume is genuinely beyond what an OLTP engine aggregates well.

Features

• Connections declared in settings.php, reached through a clickhouse service in the same shape as Database::getConnection(). Several servers or credentials can be configured side by side.
• Server-side parameter binding. Values travel as param_* and are parsed by ClickHouse as data, never as SQL. The module therefore contains no value escaper, and so has none to get wrong.
• A query API: query(), queryAll(), queryColumn() and queryScalar(), plus a select builder for the common shapes, with pager support via rowsBeforeLimit().
• Bulk inserts. insert() appends many rows in one request over JSONEachRow, which is how ClickHouse wants to be written to.
• DDL and mutations through command(), so a module can ship and maintain its own tables.
• Writing is a property of the credential. Read-only is the default; a site can hold a read-only reporting connection and a writable events connection at once, and only the second can be made to write by a mistake elsewhere in the codebase. Read-only connections also send ClickHouse's own readonly setting, so the server refuses what the guard somehow missed.
• Result types that survive the round trip. Nullable, LowCardinality and Array are unwrapped, DateTime columns arrive as instants rather than time-zone-dependent strings, and a UInt64 beyond PHP_INT_MAX is handed back as a string instead of decaying into a float.
• No client library. It speaks ClickHouse's HTTP interface directly through Drupal's own HTTP client, so your Guzzle middleware, timeouts, logging and proxy configuration all apply.
• Testable without a server. A recording transport replays fixtures, so test suites do not need a live ClickHouse.

What it deliberately does not do

There is no Drupal\Driver\Database\clickhouse, and there will not be one. A database driver has to satisfy Drupal's storage contract, which asks for four things ClickHouse does not provide: UPDATE and DELETE statements (it has asynchronous background mutations), transactions, unique-key enforcement (the primary key is a sparse sort index, not a constraint), and read-your-writes. Such a driver would pass much of the database test suite and fail precisely on the operations entity storage is built from.

Mutations are supported, but they are not UPDATE statements. ALTER TABLE ... UPDATE and ... DELETE are queued as background work, so a SELECT immediately afterwards may still see the old rows. They are the right tool for backfills and erasure requests, and the wrong one for application logic that expects a result.

Requirements

• Drupal 11 or 12, PHP 8.3 or newer.
• A ClickHouse server reachable over its HTTP interface (port 8123 by default). ClickHouse Cloud and self-hosted both work.
• No PHP extensions and no third-party libraries.

Configuration

Connections live in settings.php, never in exported configuration, so credentials stay out of the config directory:

$settings['clickhouse']['default'] = [
  'host' => 'clickhouse.internal',
  'database' => 'analytics',
  'username' => 'drupal',
  'password' => getenv('CLICKHOUSE_PASSWORD'),
];

Add 'write' => TRUE to a connection that needs to append. For row-level access control, use ClickHouse row policies rather than filtering in PHP.

Roadmap

Released in stages so each is usable on its own. 1.0 is the connection management, parameter binding, type mapping, select builder and inserts described above.

1.1 adds a read-only EntityStorage handler

1.2 a Views query plugin

1.3 a binary insert format, which makes float writes exact and is also the fastest way to load rows.