ClickHouse

yf can query ClickHouse through db()'s usual API with the driver clickhouse (yf_db_driver_clickhouse, plugin db). It talks to the server's HTTP interface with the curl extension and needs no ClickHouse library. The driver is read-mostly and built for analytics: it covers SELECT through the usual helpers and the query builder, inserts (one statement, or large batches with insert_rows()), and DDL through query(). It is meant for a second connection next to the site's MySQL / MariaDB database, never for the main one: ClickHouse has no transactions, no auto increment and no row-by-row updates.

Connecting

$ch = new yf_db('clickhouse');
$ch->connect([
    'host' => 'https://ch.example.com', // or 'ch.example.com' with 'ssl' => true; plain http when no scheme is given
    'port' => 8443,                     // default 8123, or 8443 for https; 'https://ch.example.com:8443' works too
    'user' => 'analytics',
    'pswd' => getenv('CLICKHOUSE_PASSWORD'),
    'name' => 'stats',                  // the database
    'prefix' => '',                     // no table prefix (otherwise DB_PREFIX of the site applies)
]);
$ch->ESCAPE_BUILDER_VALUES = true;      // recommended: the builder escapes the values it quotes

Pass every parameter: a parameter you leave out falls back to the site's DB_* constants, which belong to the MySQL database (its user and password included). The user and password go in request headers, never in the URL.

Driver settings are public properties of $ch->db (or of a project class db_driver_clickhouse extends yf_db_driver_clickhouse):

PropertyDefaultMeaning
SETTINGS[]ClickHouse settings sent with every query, e.g. ['max_execution_time' => 30, 'max_memory_usage' => 2e9]. Each query is its own HTTP request, so SET x = 1 and temporary tables do not outlive it: put settings here. Parameters of the HTTP interface itself (query, database, user, param_* ...) are not settings and are left out.
READONLYfalseSends readonly=2: the server refuses every write; no setting can undo it.
ALLOW_MUTATIONSfalseLets the query builder compile UPDATE and DELETE (see below).
TIMEOUT / CONNECT_TIMEOUT60 / 5Seconds.
INSERT_CHUNK10000Rows per request of insert_rows().
VERIFY_TLS, CA_FILE, PROXYtrue, '', ''https checks; the driver uses no proxy unless one is named here.

Reading

$day = $ch->get_2d('SELECT toDate(ts) AS d, count() FROM events GROUP BY d ORDER BY d');
$top = $ch->select('page', 'count() AS hits')->from('events')
    ->where('site_id', '=', $site_id)->where('ts', '>=', '2026-11-01 00:00:00')
    ->group_by('page')->order_by('hits', 'desc')->limit(20)->get_all();
$one = $ch->get('SELECT * FROM events WHERE id = ' . (int) $id);   // get() adds LIMIT 1

get() appends LIMIT 1 unless the query ends with a LIMIT. A query that ends with a SETTINGS or FORMAT clause needs its own LIMIT there; better put the settings into SETTINGS.

Values come back as strings (NULL as null), as with mysqli; arrays and maps come back as their text form ([1,2]). The builder writes MySQL-style SQL with these differences: LIKE becomes ILIKE (case-insensitive, as on MySQL), REGEXP / RLIKE become match(), union() is UNION DISTINCT, and row locks are left out. A result is held in PHP memory, so aggregate in SQL. For a large export, $ch->db->raw($sql) returns the body in the format the query's own FORMAT clause names (CSV, JSONEachRow, Parquet ...). The other helpers expect no FORMAT clause.

Writing

// a few rows: INSERT ... VALUES, as on MySQL
$ch->insert('events', ['site_id' => 1, 'page' => $page, 'ts' => date('Y-m-d H:i:s')]);
// many rows: one request per 10000 rows, values sent as data, not as SQL
$written = $ch->query_builder()->insert_rows('events', $rows);           // ['chunk' => 50000] for bigger requests

ClickHouse is built for few large inserts: each INSERT makes a data part on disk. Collect rows and write them in batches (a queue, a cron job), or add 'async_insert' => 1 to SETTINGS when many processes write single rows. insert_rows() takes raw values: never _es() them. NULL is NULL, a PHP array goes into an Array() column, and chunks are not atomic (a failure in chunk 3 keeps chunks 1 and 2). replace(), insert_ignore() and insert_on_duplicate_key_update() are plain INSERTs here, because ClickHouse has no unique keys; deduplicate with a ReplacingMergeTree table. insert_id() is 0. insert_rows() returns the number of rows sent. After an insert(), affected_rows() is what the server reports as written, which also counts the rows of materialized views and is 0 with async_insert.

UPDATE and DELETE

In ClickHouse these are mutations: they rewrite whole data parts in the background. The builder refuses them with a warning until you set $ch->db->ALLOW_MUTATIONS = true. Then update() compiles ALTER TABLE t UPDATE ... WHERE ... and delete() compiles a lightweight DELETE FROM t WHERE ...; both need a WHERE and take no LIMIT. Add 'mutations_sync' => 1 to SETTINGS to wait for an update to finish. update_batch(), increment() and decrement() are always refused: counters belong in a SummingMergeTree table. To empty a table, run TRUNCATE TABLE through query().

Escaping

The rules are the same as on every yf driver: $ch->es($value) escapes a value for a literal you write yourself. The builder and insert() / update() only quote values unless ESCAPE_BUILDER_VALUES is on. Turn the flag on for a ClickHouse connection; with it on, do not escape builder values yourself. Use $ch->es(), not _es(): _es() escapes for the site's main database. ClickHouse reads backslash escapes in literals, so the driver puts a backslash before \ and ' (doubling the quote alone would not be safe). The server also refuses more than one statement per request.

A value can also skip SQL altogether as a server-side parameter: $ch->db->raw('SELECT count() FROM events WHERE page = {page:String} FORMAT TSV', ['param_page' => $page]) returns the body.

Tables and catalog

Write ClickHouse DDL yourself through query() (CREATE TABLE ... ENGINE = MergeTree ORDER BY ...). The installer, the migrator and the DDL helpers of utils() speak MySQL and are not supported here. These catalog reads work: utils()->list_databases(), list_tables(), table_exists(), list_tables_details() (engine, rows, bytes), and meta_columns() / list_columns() (the base type, nullable, default, primary, which means "in the primary key", not unique, and virtual for MATERIALIZED / ALIAS columns). begin() / commit() return false: there are no transactions.

Tests

make test-clickhouse YF_CLICKHOUSE_URL=http://127.0.0.1:8123 runs the driver against a server (it skips while the variable is unset). The unit suite checks the escaping and the SQL without a server.