simpod/clickhouse-client

PHP ClickHouse Client

0.8.3 2025-01-30 11:07 UTC

Requires

• php: ^8.2
• guzzlehttp/promises: ^2.0
• guzzlehttp/psr7: ^2.6
• php-http/client-common: ^2.0
• psr/http-client: ^1.0
• psr/http-factory: ^1.0
• psr/http-message: ^2.0
• psr/log: ^3

Requires (Dev)

• cdn77/coding-standard: ^7.0
• infection/infection: ^0.29.0
• kafkiansky/phpclick: dev-bump
• nyholm/psr7: ^1.2
• php-http/message-factory: ^1.1
• phpstan/extension-installer: ^1.1
• phpstan/phpstan: ^2.0.0
• phpstan/phpstan-phpunit: ^2.0.0
• phpstan/phpstan-strict-rules: ^2.0.0
• phpunit/phpunit: ^11.0
• symfony/http-client: ^7.0

MIT

• Simon Podlipsky

• php
• http
• http client
• client
• clickhouse

Published on Packagist.org on 2025-01-30 11:09 UTC

README

Motivation

The library is trying not to hide any ClickHouse HTTP interface specific details. That said everything is as much transparent as possible and so object-oriented API is provided without inventing own abstractions.
Naming used here is the same as in ClickHouse docs.

• Works with any HTTP Client implementation (PSR-18 compliant)
• All ClickHouse Formats support
• Logging (PSR-3 compliant)
• SQL Factory for parameters "binding"
• Native query parameters support

Contents

• Setup
  • Logging
  • PSR Factories who?
• Sync API
  • Select
  • Select With Params
  • Insert
• Async API
  • Select
• Parameters "binding"
• Native Query Parameters
  • Custom Query Parameter Value Conversion
  • Expression
• Snippets

Setup

composer require simpod/clickhouse-client

1. Read about ClickHouse HTTP Interface. It's short and useful for concept understanding.
2. Create a new instance of ClickHouse client and pass a PSR-18 HTTP client plus PSR-17 factories.
   1. Symfony HttpClient is recommended for the PSR-18 client.
   2. The plot twist is there's no endpoint/credentials etc. config in this library, provide it via client.
3. See tests

<?php

use Nyholm\Psr7\Factory\Psr17Factory;
use SimPod\ClickHouseClient\Client\PsrClickHouseClient;
use SimPod\ClickHouseClient\Client\Http\RequestFactory;
use SimPod\ClickHouseClient\Param\ParamValueConverterRegistry;
use Symfony\Component\HttpClient\HttpClient;
use Symfony\Component\HttpClient\Psr18Client;

$psr17Factory = new Psr17Factory;

$clickHouseClient = new PsrClickHouseClient(
    new Psr18Client(
        HttpClient::create([
            'base_uri' => 'http://localhost:8123',
            'headers' => [
                'X-ClickHouse-User' => 'default',
                'X-ClickHouse-Key' => '',
            ],
            'query' => ['database' => 'default'],
        ]),
    ),
    new RequestFactory(
        new ParamValueConverterRegistry(),
        $psr17Factory,
        $psr17Factory,
        $psr17Factory
    ),
);

Logging

Pass a SqlLogger implementation as the third PsrClickHouseClient constructor argument. The library provides a PSR-3 adapter:

<?php

use Psr\Http\Client\ClientInterface;
use Psr\Log\LoggerInterface;
use SimPod\ClickHouseClient\Client\Http\RequestFactory;
use SimPod\ClickHouseClient\Client\PsrClickHouseClient;
use SimPod\ClickHouseClient\Logger\PsrLogger;

/** @var ClientInterface $psr18Client */
/** @var RequestFactory $requestFactory */
/** @var LoggerInterface $logger */
$clickHouseClient = new PsrClickHouseClient(
    $psr18Client,
    $requestFactory,
    new PsrLogger($logger),
);

Symfony HttpClient Example

Configure HTTP Client

As said in ClickHouse HTTP Interface spec, we use headers to auth and e.g. set default database via query.

framework:
    http_client:
        scoped_clients:
            click_house.client:
                base_uri: '%clickhouse.endpoint%'
                headers:
                    'X-ClickHouse-User': '%clickhouse.username%'
                    'X-ClickHouse-Key': '%clickhouse.password%'
                query:
                    database: '%clickhouse.database%'

PSR Factories who?

The library does not implement its own HTTP. That has already been done via PSR-7, PSR-17 and PSR-18. This library respects it and allows you to plug your own implementation (eg. Symfony HttpClient or Guzzle).

Recommended are composer require nyholm/psr7 for PSR-17 and composer require symfony/http-client for PSR-18 (used in the example above).

Sync API

Select

ClickHouseClient::select()

Intended for SELECT and SHOW queries. Appends FORMAT to the query and returns response in selected output format:

<?php

use SimPod\ClickHouseClient\Client\ClickHouseClient;
use SimPod\ClickHouseClient\Format\JsonEachRow;
use SimPod\ClickHouseClient\Output;
use SimPod\ClickHouseClient\Settings\ArraySettingsProvider;

/** @var ClickHouseClient $client */
/** @var Output\JsonEachRow $output */
$output = $client->select(
    'SELECT * FROM table',
    new JsonEachRow(),
    new ArraySettingsProvider(['force_primary_key' => 1])
);

Select With Params

ClickHouseClient::selectWithParams()

Same as ClickHouseClient::select() except it also allows parameter binding.

<?php

use SimPod\ClickHouseClient\Client\ClickHouseClient;
use SimPod\ClickHouseClient\Format\JsonEachRow;
use SimPod\ClickHouseClient\Output;
use SimPod\ClickHouseClient\Settings\ArraySettingsProvider;

/** @var ClickHouseClient $client */
/** @var Output\JsonEachRow $output */
$output = $client->selectWithParams(
    'SELECT * FROM table WHERE name = :name',
    ['name' => 'Alice'],
    new JsonEachRow(),
    new ArraySettingsProvider(['force_primary_key' => 1])
);

Insert

ClickHouseClient::insert()

<?php

use SimPod\ClickHouseClient\Client\ClickHouseClient;

/** @var ClickHouseClient $client */
$client->insert('table', $data, $columnNames);

If $columnNames is provided and is key->value array column names are generated based on it and values are passed as parameters:

$client->insert( 'table', [[1,2]], ['a' => 'Int8', 'b' => 'String'] ); generates INSERT INTO table (a,b) VALUES ({p1:Int8},{p2:String}) and values are passed along the query.

If $columnNames is provided column names are generated based on it:

$client->insert( 'table', [[1,2]], ['a', 'b'] ); generates INSERT INTO table (a,b) VALUES (1,2).

If $columnNames is omitted column names are read from $data:

$client->insert( 'table', [['a' => 1,'b' => 2]]); generates INSERT INTO table (a,b) VALUES (1,2).

Column names are read only from the first item:

$client->insert( 'table', [['a' => 1,'b' => 2], ['c' => 3,'d' => 4]]); generates INSERT INTO table (a,b) VALUES (1,2),(3,4).

If not provided they're not passed either:

$client->insert( 'table', [[1,2]]); generates INSERT INTO table VALUES (1,2).

Async API

Select

ClickHouseAsyncClient::select() returns an Amp Future:

<?php

use Amp\Http\Client\HttpClientBuilder;
use Amp\Http\Client\Psr7\PsrAdapter;
use Nyholm\Psr7\Factory\Psr17Factory;
use SimPod\ClickHouseClient\Client\Http\RequestFactory;
use SimPod\ClickHouseClient\Client\PsrClickHouseAsyncClient;
use SimPod\ClickHouseClient\Format\JsonEachRow;
use SimPod\ClickHouseClient\Param\ParamValueConverterRegistry;

$psr17Factory = new Psr17Factory();

$client = new PsrClickHouseAsyncClient(
    HttpClientBuilder::buildDefault(),
    new RequestFactory(
        new ParamValueConverterRegistry(),
        $psr17Factory,
        $psr17Factory,
        $psr17Factory,
        'http://localhost:8123?database=default',
    ),
    new PsrAdapter($psr17Factory, $psr17Factory),
    [
        'X-ClickHouse-User' => 'default',
        'X-ClickHouse-Key' => '',
    ],
);

$output = $client->select('SELECT 1 AS data', new JsonEachRow())->await();

Parameters "binding"

<?php

use SimPod\ClickHouseClient\Sql\SqlFactory;
use SimPod\ClickHouseClient\Sql\ValueFormatter;

$sqlFactory = new SqlFactory(new ValueFormatter());

$sql = $sqlFactory->createWithParameters(
    'SELECT :param',
    ['param' => 'value']
);

This produces SELECT 'value' and it can be passed to ClickHouseClient::select().

Supported types are:

• scalars
• null
• arrays, including IN (:list) values and tuple arrays
• backed enums
• DateTimeInterface
• Expression
• objects implementing __toString()

Native Query Parameters

Tip

Official docs

<?php

use SimPod\ClickHouseClient\Client\PsrClickHouseClient;
use SimPod\ClickHouseClient\Format\JsonEachRow;

$client = new PsrClickHouseClient(...);

$output = $client->selectWithParams(
    'SELECT {p1:String}',
    ['p1' => 'value'],
    new JsonEachRow(),
);

All types are supported (except AggregateFunction, SimpleAggregateFunction and Nothing by design). You can also pass DateTimeInterface into Date* types or native array into Array, Tuple, Nested and Geo types (Point, Polygon, Geometry etc.).

Custom Query Parameter Value Conversion

Query parameters passed to selectWithParams() are converted into an HTTP-API-compatible format. To overwrite an existing value converter or provide a converter for a type that the library does not (yet) support, pass these to the SimPod\ClickHouseClient\Param\ParamValueConverterRegistry constructor:

<?php

use SimPod\ClickHouseClient\Client\Http\RequestFactory;
use SimPod\ClickHouseClient\Client\PsrClickHouseClient;
use SimPod\ClickHouseClient\Exception\UnsupportedParamValue;
use SimPod\ClickHouseClient\Param\ParamValueConverterRegistry;
use DateTimeInterface;

$paramValueConverterRegistry = new ParamValueConverterRegistry([
    'datetime' => static fn (mixed $v, mixed ...$unused) => $v instanceof DateTimeInterface
        ? $v->format('c')
        : throw UnsupportedParamValue::type($v),
]);

$client = new PsrClickHouseClient(..., new RequestFactory($paramValueConverterRegistry, ...));

Be aware that the library cannot ensure that passed values have a certain type. They are passed as-is and closures must accept mixed values.

Throw an exception of type UnsupportedParamValue if your converter does not support the passed value type.

Expression

To represent complex expressions there's SimPod\ClickHouseClient\Sql\Expression class. When passed to SqlFactory its value gets evaluated.

To pass eg. UUIDStringToNum('6d38d288-5b13-4714-b6e4-faa59ffd49d8') to SQL:

<?php

use SimPod\ClickHouseClient\Sql\Expression;

Expression::new("UUIDStringToNum('6d38d288-5b13-4714-b6e4-faa59ffd49d8')");

<?php

use SimPod\ClickHouseClient\Sql\ExpressionFactory;
use SimPod\ClickHouseClient\Sql\ValueFormatter;

$expressionFactory = new ExpressionFactory(new ValueFormatter());

$expression = $expressionFactory->templateAndValues(
    'UUIDStringToNum(%s)',
    '6d38d288-5b13-4714-b6e4-faa59ffd49d8'
);

Snippets

There are handy queries like getting database size, table list, current database etc.

To prevent Client API pollution, those are extracted into Snippets.

Example to obtain current database name:

<?php

use SimPod\ClickHouseClient\Snippet\CurrentDatabase;

$currentDatabaseName = CurrentDatabase::run($client);

List

• CurrentDatabase
• DatabaseSize
• Parts
• ShowCreateTable
• ShowDatabases
• TableSizes
• Version