php_clickhouse

Native PHP extension for ClickHouse, built on the official clickhouse-cpp v2.6.2 client.

Speaks the native binary TCP protocol with LZ4 / ZSTD compression and optional TLS. This guide covers how to read and write every supported data type, configure the client, stream CSV/TSV, and use the full method surface. For install and benchmarks, see the README.

PHP 7.4 – 8.5 native TCP protocol LZ4 / ZSTD TLS multi-endpoint failover

Install #

Via PIE (the PHP Foundation's PECL successor):

pie install iliaal/php_clickhouse
# with TLS:
pie install iliaal/php_clickhouse --enable-clickhouse-openssl

Then add extension=clickhouse.so to your php.ini. Build-from-source instructions, platform support, and the Docker test-server recipe are in the README.

Quick start #

<?php
$ch = new ClickHouse([
    "host"        => "127.0.0.1",
    "port"        => 9000,
    "database"    => "test",
    "user"        => "test",
    "passwd"      => "test",
    "compression" => "lz4",   // or "zstd" / true / false
]);

$ch->execute("CREATE TABLE IF NOT EXISTS events (
    id UInt32, ts DateTime64(3), tag LowCardinality(String)
) ENGINE = Memory");

$ch->insert("events", ["id", "ts", "tag"], [
    [1, time(), "alpha"],
    [2, time(), "beta"],
]);

// Filter by an in-memory list of IDs without bloating the SQL. The
// server reads `ext_ids` as a named temp table for this query only.
$hits = $ch->selectWithExternalData(
    "SELECT id, tag FROM events WHERE id IN ext_ids",
    [["name" => "ext_ids",
      "columns" => ["id" => "UInt32"],
      "rows" => [[1], [42], [1337]]]]
);

foreach ($ch->select("SELECT id, ts, tag FROM events ORDER BY id",
                     [], ClickHouse::DATE_AS_STRINGS) as $row) {
    print_r($row);
}

Data types #

The extension maps ClickHouse column types to PHP values on both the write path (insert(), insertAssoc(), write()) and the read path (select() and the streaming readers). The matrix below is the authoritative per-type reference.

Read / write matrix #

Each row gives what insert() accepts for a column of that type, what select() hands back, whether Nullable(T) is supported, and any caveats. Unless noted, a null into a non-Nullable column is rejected, and a value of the wrong shape throws rather than silently coercing to 0.

Map key/value support

Both the read decoder and the write path accept these scalar key and value types: String, Int8 … Int64, UInt8 … UInt64, Float32, Float64, UUID. The write path additionally accepts LowCardinality(String) keys and values. UInt64 entries above 2⁶³−1 come back as strings; UUID keys/values read back as bare 32-char hex by default (hyphenated with UUID_WITH_DASHES).

PHP associative arrays cannot represent duplicate keys and coerce an in-range numeric string key such as "1" to the integer key 1. The default decoder throws when either would lose ClickHouse data. Pass ClickHouse::MAP_AS_PAIRS to receive an ordered list of [key, value] pairs; this preserves order, duplicates, and key types and can be passed back to insert() unchanged.

Write-side coercion #

Numeric columns (the integer, float, and decimal families) accept an int, an integral float, or a numeric string, and reject anything that doesn't fully parse as a number. The string-shaped columns are the exceptions:

• Date, DateTime, IPv4, IPv6 treat a PHP string as a textual representation (a Y-m-d date, a dotted-quad/colon address), not a numeric form. To pass the integer form, pass an actual int.
• Time and Time64 accept numeric seconds only; a "HH:MM:SS" string is rejected on write.
• DateTime64 / Time64 at precision ≥ 7 (sub-microsecond) reject float input, since a float can't represent nanosecond-since-epoch values exactly. Pass an integer or a formatted string instead. An integer is read as whole seconds and scaled to the column's precision on write, not as raw ticks. (Reads return raw ticks by default; see the coercion table above for the write-seconds / read-ticks asymmetry.)

Nested-type recursion (arrays, tuples, maps) is capped at depth 32.

Fetch modes #

fetch_mode is a bitmask combining the constants below. select, selectStatement, selectStream, selectStreamCallback, and selectWithExternalData all accept it. There are two categories: shape flags restructure the whole result, and value flags change how a single cell is rendered.

Configuration #

All keys go in the array passed to new ClickHouse([...]).

Connection #

Compression #

Timeouts and retry #

TLS #

Requires a build with --enable-clickhouse-openssl. Building without it and passing ssl => true raises ClickHouseException at construct time. Any other ssl_* option also requires ssl => true; setting one with TLS off raises ClickHouseException instead of connecting in plaintext. null, empty CA paths ("", [], or false for ssl_ca_directory), and booleans left at their default (ssl_skip_verify => false, ssl_use_default_ca => true) are allowed.

Configured CA files and directories extend the system trust store; they do not replace it. To pin the server to your own CA, set ssl_use_default_ca => false alongside ssl_ca_files, otherwise any certificate a public CA issues for the host name also verifies.

Boolean options (ssl, ssl_skip_verify, ssl_use_default_ca, tcp_nodelay, tcp_keepalive, ping_before_query) accept true, false, 0, 1, and the strings "0", "1", "true", "false" (case-insensitive), plus "" as false (what INI files yield for off). Any other value raises ClickHouseException. Path options reject strings that contain NUL bytes.

Methods #

Every call signature is grouped below. $params, $query_id, and $settings mean the same thing everywhere: client-side / server-side placeholders, an optional query id propagated to the server, and a per-call settings override.

Constructor & DDL #

$ch = new ClickHouse(array $config);

// Run any statement that returns no result set (DDL, INSERT ... SELECT, etc.)
$ch->execute(string $sql,
             array $params = [],
             string $query_id = "",
             array $settings = []);

$ch->ping();   // returns true on success, throws on failure

Reading #

// Buffered read into a PHP array. $fetch_mode is the bitmask from "Fetch modes".
$rows = $ch->select(string $sql,
                    array $params = [],
                    int $fetch_mode = 0,
                    string $query_id = "",
                    array $settings = []);

// Read with external in-memory tables sent alongside the query.
// Each entry: ['name' => 'ext_x', 'columns' => ['c' => 'Type', ...],
//              'rows' => [[...], [...], ...]]. The query body references
// the external table by name (e.g. `WHERE id IN ext_ids`). Keeps the
// SQL small when filtering by big lists; multiple externals per call.
$rows = $ch->selectWithExternalData(string $sql,
                                    array  $externals,
                                    array  $params = [],
                                    int    $fetch_mode = 0,
                                    string $query_id = "",
                                    array  $settings = []);

Inserting #

// Bulk insert (entire dataset in one call), rows as positional arrays
// matching $columns.
$ch->insert(string $table, array $columns, array $values,
            string $query_id = "",
            array $settings = []);

// Same as insert(), but rows are associative arrays and the column list
// is derived from the first row's keys.
$ch->insertAssoc(string $table, array $rows,
                 string $query_id = "",
                 array $settings = []);

Streaming reads & writes #

For datasets larger than memory, or when you want to pipe rows to/from a file. The CSV/TSV stream helpers are documented with examples under CSV / TSV streaming.

// Streaming insert: open a block, append rows, close. write() flushes
// blocks to the server as you go.
$ch->writeStart(string $table, array $columns,
                string $query_id = "",
                array $settings = []);
$ch->write(array $values);
$ch->write(array $more_values);
$ch->writeEnd();

// Parse a TSV / CSV stream and insert in batches of $batch_rows. Returns
// rows inserted. See "CSV / TSV streaming".
$ch->insertFromStream(string $table, array $columns, mixed $stream,
                      string $format = "TabSeparated",
                      int    $batch_rows = 10000,
                      string $query_id = "",
                      array  $settings = []);

// Lazy read. selectStream() buffers raw result blocks for the iterator's
// lifetime so it stays re-iterable (Countable, rewind); it does NOT reduce
// peak memory for a huge result. For a genuinely streaming read holding one
// row at a time, use selectStreamCallback(). $fetch_mode is the trailing arg.
$iter = $ch->selectStream(string $sql, array $params = [],
                          string $query_id = "", array $settings = [],
                          int $fetch_mode = 0);
foreach ($iter as $row) { /* ... */ }   // ClickHouseRowIterator: Iterator + Countable

$ch->selectStreamCallback(string $sql, callable $cb,
                          array $params = [], string $query_id = "",
                          array $settings = [],
                          int $fetch_mode = 0);  // true per-row stream

// Write query rows straight to a PHP stream as TSV / CSV. Returns rows
// written. See "CSV / TSV streaming".
$ch->selectToStream(string $sql, array $params, mixed $stream,
                    string $format = "TabSeparated",
                    string $query_id = "", array $settings = []);

Result wrapper #

// smi2-style result wrapper: returns a ClickHouseStatement (Iterator,
// Countable, ArrayAccess, JsonSerializable) carrying a per-call stats
// snapshot. Use it for fetchOne / fetchKeyPair / fetchColumn on the result,
// or to keep stats around after running other queries. Plain $ch->select()
// is faster when you just need the array.
$stmt = $ch->selectStatement(string $sql, array $params = [],
                             string $query_id = "", array $settings = [],
                             int $fetch_mode = 0);
foreach ($stmt as $row) { /* ... */ }
$stmt[0]; count($stmt); json_encode($stmt);
$stmt->fetchOne(); $stmt->fetchKeyPair(); $stmt->fetchColumn();
$stmt->toArray(); $stmt->statistics();

• fetchOne() returns null on an empty result, the scalar cell for a single-column row, and the full assoc row otherwise.
• fetchKeyPair() builds a column-0 → column-1 map ([] when the result is empty). It throws when any row holds fewer than 2 columns or the key column is not scalar (array / object keys are rejected rather than collapsing onto one key).
• The wrapper is read-only: offsetSet() / offsetUnset() always throw ClickHouseException. Only the value-shaping fetch flags apply (see Fetch modes).
• The legacy SeasClick / SeasClickException class names stay registered as source-compat aliases; using them raises no runtime deprecation at this time.

Settings & observability #

$ch->setSettings(array $settings);            // client-wide; per-call overrides; chainable
$ch->setSetting(string $key, mixed $value);  // single-key sugar, chainable
$ch->setDatabase(string $database);          // reconnects with this default DB (survives reconnects); drops session temp tables/SET; chainable
$ch->setProgressCallback(?callable $cb); // callback throws propagate; the connection resets automatically
$ch->setProfileCallback(?callable $cb);  // callback throws propagate; the connection resets automatically
$ch->setVerbose(true);                 // JSON lifecycle lines on STDERR
$ch->setVerbose(fn($e, $ctx) => ...);  // or custom sink
$ch->setVerbose(false);                // disable
$stats = $ch->getStatistics();         // last query: rows, bytes, elapsed_ms, query_id

Connection & server info #

$ch->resetConnection();
$info = $ch->getServerInfo();          // name, version_*, revision, timezone, display_name
$ep   = $ch->getCurrentEndpoint();     // {host, port} of the active endpoint, or null
$ch->enableLogQueries(bool $enabled = true);
$log = $ch->getLogQueries();           // returns and clears the buffer (see Query log)

DDL & introspection helpers #

$ch->isExists(string $database, string $table);
$ch->showDatabases();
$ch->showProcesslist();
$ch->getServerVersion();
$ch->databaseSize(?string $database = null);     // {bytes_on_disk, rows}
$ch->tablesSize(?string $database = null);
$ch->tableSize(string $table);                   // {rows, bytes_on_disk, partitions, modification_time}
$ch->partitions(string $table);
$ch->showTables(?string $database = null, ?string $like = null);
$ch->showCreateTable(string $table);
$ch->getServerUptime();                // seconds
$ch->truncateTable(string $table);
$ch->dropPartition(string $table, string $partition);

JSON columns #

The JSON column type round-trips through PHP.

$ch->setSettings([
    "allow_experimental_json_type"              => 1, // only needed to CREATE a JSON column
    "output_format_native_write_json_as_string" => 1, // required to SELECT JSON
]);

// Insert: a PHP array or object is JSON-encoded; a string is stored as
// raw JSON text (validated client-side). PHP null is accepted only by
// Nullable(JSON); a non-Nullable JSON column rejects it.
$ch->insert("events", ["id", "payload"], [
    [1, ["level" => "info", "tags" => ["a", "b"]]],
    [2, '{"level":"warn"}'],
]);

// Read: raw JSON string by default.
$ch->select("SELECT payload FROM events");

// JSON_AS_ARRAY decodes to a nested associative array;
// JSON_AS_OBJECT decodes to nested stdClass.
$ch->select("SELECT payload FROM events", [], ClickHouse::JSON_AS_ARRAY);
$ch->select("SELECT payload FROM events", [], ClickHouse::JSON_AS_OBJECT);

CSV / TSV streaming #

Two methods move rows directly between a ClickHouse table and a PHP stream resource, formatting or parsing the bytes in C++ so neither path builds a full PHP result array. Both accept these formats: TabSeparated (alias TSV), TabSeparatedWithNames (alias TSVWithNames), CSV, and CSVWithNames.

Reading: selectToStream()

Writes query rows to a stream as TSV/CSV, block-by-block, and returns the number of rows written. Dates and datetimes emit ISO strings; Decimal / Int128 / UInt128 / UUID / IPv4 / IPv6 emit their string forms. NULL becomes \N in TSV and an unquoted \N in CSV (a real string equal to "\N" is quoted so it can't be misread as NULL).

$f = fopen("/tmp/events.tsv", "wb");
$n = $ch->selectToStream(
    "SELECT id, ts, tag FROM events ORDER BY id",
    [], $f, "TabSeparatedWithNames"
);
fclose($f);
echo "$n rows written\n";

Writing: insertFromStream()

Parses a TSV/CSV stream and inserts rows in batches of $batch_rows (default 10000), returning the number of rows inserted. Only one batch of per-column zvals exists at a time, so the total stream can exceed memory; one complete record and the configured batch must still fit. Cells arrive as strings and run through the same write coercion as insert() for each server-supplied column type, so a numeric column still needs a valid numeric string.

$f = fopen("/tmp/events.csv", "rb");
$n = $ch->insertFromStream(
    "events", ["id", "ts", "tag"], $f,
    "CSVWithNames", 5000
);
fclose($f);
echo "$n rows inserted\n";

• NULL marker: TSV uses the \N escape (a literal \\N round-trips as the two-byte string); CSV uses an unquoted literal \N cell. An empty CSV cell becomes an empty string, not NULL.
• A \N cell aimed at a non-Nullable column throws; reaching NULL needs a Nullable(T) destination.
• The *WithNames formats skip the first row as a header.
• batch_rows < 1, a non-stream argument, or an empty column list all throw.

Placeholders #

Two placeholder syntaxes are supported in select / execute:

• {name} is client-side identifier substitution. Two value shapes:
  • Scalar (string / int / float): coerces to a single token, validated as either an identifier ([A-Za-z_][A-Za-z0-9_]*, optionally db-qualified by one dot like db.tbl) or a numeric literal. Whitespace, commas, quotes, semicolons, backslashes, and other SQL meta-characters are rejected. That check covers the value, not the text it lands in: a negative value placed directly after - (as in 0-{n}) would compose --, which starts a comment, so it throws. Write 0 - {n} or 0-({n}) instead. A placeholder that follows $ and any run of letters, digits, or underscores (as in ${t}$) also throws, because the value would become part of a $tag$ heredoc delimiter.
  • Array: each element is validated as a single scalar token, then joined with ", " for the SQL replacement. Use this for legitimate column lists; an element with internal whitespace or commas is still rejected. A scalar value containing commas is rejected; that ambiguity (single identifier vs. list) is the point of the array-shape API.
• {name:Type} is a server-side typed parameter. The SQL text is passed through unchanged; the value is bound via Query::SetParam and the server quotes and parses it according to Type. Pass PHP arrays for Array(T) types; null becomes a server NULL.
• Reserved syntax: both placeholder forms are matched anywhere in the SQL text, including inside string literals and comments. Avoid writing text shaped like {identifier} or {identifier:Type} inside a literal when a params key of the same name exists; it will be treated as a placeholder. A params key must not contain :, {, or }; such a key throws, because {a:b} already reads as a typed placeholder.

// Single-identifier substitution.
$ch->select("SELECT * FROM {tbl}", ["tbl" => "users"]);

// Column-list substitution via array value.
$ch->select("SELECT {cols} FROM users",
            ["cols" => ["id", "name", "email"]]);

// Server-side typed parameters, no client-side quoting needed.
$ch->select("SELECT * FROM users WHERE id IN ({ids:Array(UInt32)})",
            ["ids" => [1, 2, 3]]);

Settings #

setSettings() applies client-wide. Per-call settings are the 5th argument on select / insert, and the 4th argument on execute / writeStart. Both accept plain string => string pairs; PHP scalars are stringified for you.

$ch->setSettings(["max_execution_time" => "30"]);

// Per-call override.
$ch->select("SELECT * FROM big_table",
            [], 0, "",
            ["max_execution_time" => "5",
             "max_memory_usage"   => "1000000000"]);

Statistics and progress #

$ch->setProgressCallback(function (array $p) {
    fprintf(STDERR, "rows=%d bytes=%d\n", $p["rows"], $p["bytes"]);
});

$ch->select("SELECT count() FROM big_table");

$stats = $ch->getStatistics();
// rows_read, bytes_read, total_rows, written_rows, written_bytes,
// blocks, rows_before_limit, applied_limit, elapsed_ms

Query log #

enableLogQueries(true) turns on a per-client buffer that records each completed select / insert / execute / streaming writeEnd. Each entry is {sql, query_id, elapsed_ms, rows_read, bytes_read, error_code, error_message}. sql is the pre-substitution template with string literals ('...', $$...$$, $tag$...$tag$, x'...', and Unicode-quoted strings) replaced by '?'-style markers, including quoted text inside comments; identifiers and comment text are kept. The same redaction applies to the sql field of verbose events. sql is capped before retention. error_code is 0 on success, the server error code on a ServerException, or -1 on client/network failure. getLogQueries() returns the buffer and clears it.

$ch->enableLogQueries(true);
$ch->select("SELECT count() FROM users");
$ch->insert("logins", ["user_id", "ts"], $batch);

foreach ($ch->getLogQueries() as $q) {
    fprintf(STDERR, "[%.1fms] %s\n", $q["elapsed_ms"], $q["sql"]);
}

Structured exceptions #

ClickHouseException carries three extra public properties:

• server_code: ClickHouse error code (e.g. 159 = TIMEOUT_EXCEEDED). 0 for client-side errors.
• server_name: server-reported exception name (e.g. DB::Exception). null for client-side errors.
• query_id: the query id associated with the failed call, when one was supplied. null otherwise.