Clickhouse Client

A simple version of clickhouse client (using HTTP interface). This version provides the closest access to HTTP interface, allowing you to use maximum of the Clickhouse Database capabilities in your PHP applications.

Installation

Basic installation with composer

composer require bozerkins/clickhouse-client

Configurations

use ClickhouseClient\Client\Config;

$config = new Config(
    // basic connection information
    ['host' => '127.0.0.1', 'port' => '8123', 'protocol' => 'http'],
    // settings
    ['database' => 'default'],
    // credentials
    ['user' => 'default', 'password' => '']
);

You can pass additional settings in 2nd parameter, along with database name.

The full list of settings can be found in Settings section of the Clickhouse documentation.

You do not need do define all of this in case you are using default configurations.

For example if you need to create a client object with readonly access, pass "readonly" parameter.

$config = new Config(
    // basic connection information - set to default
    [],
    // settings
    [ 'database' => 'my_shiny_database', 'readonly' => 1 ]
    // credentials - set to defauult
    []
);

Communication with Clickhouse Database happens using php-curl library. If you wish to control query execution more closely, you can pass a 4th parameter to Config constructor, containing php-curl library parameters.

For example if we would like to set a 5 second connection timeout, we would create a following config:

use ClickhouseClient\Client\Config;

$config = new Config(
    // basic connection information - set to default
    [],
    // settings
    [ 'database' => 'my_shiny_database', 'readonly' => 1 ]
    // credentials - set to defauult
    [],
    // additional CURL options
    [ CURLOPT_TIMEOUT => 5 ]
);

Full list of supported constants can be found in curl_setopt function documentation.

You can define credentials or settings after creating the config object

use ClickhouseClient\Client\Config;

$config = new Config(
    // basic connection information
    ['host' => '127.0.0.1', 'port' => '8123', 'protocol' => 'http']
);
$config->setUser('user');
$config->setPassword('password');
$config->change('database', 'new-db');

Functionality for changing basic connection information and curl settings is not implemented, because changing those settings should both conceptually and technically be considered a new client.

Client

Creating a client is fairly simple.

use ClickhouseClient\Client\Client;

$client= new Client($config);

Reading from Clickhouse

There are several methods for reading data from Clickhouse.

Simple Query

This method is primarily used for getting statistics, aggregated data from Clickhouse.

# perform select
$response = $client->query(
    'SELECT * FROM system.numbers LIMIT 10'
);
# get decoded output - database response
$response->getContent();
# get raw output string - raw string received from clickhouse
$response->getOutput();
# get communication details - curl defails for the request
$response->getDetails();
# and a neat shortcut for getting http response code
$response->getHttpCode();

Each client query returns a response with all the information about the connection performed and response.

Query data into Stream

It is possible to read data from Clickhouse directly into a stream - a file for example.

# create a stream - open a file
$stream = fopen('/tmp/file-to-read-data-into', 'r+');
# query data into the file
$client->queryStream(
    "SELECT * FROM system.numbers LIMIT 5", 
    $stream
);

Query data into Closure (function-callable)

This method is useful when you intend to divide one Clickhouse response into several destinations.

# open file 1
$file1 = fopen('/tmp/file-to-read-data-into-1', 'r+');
# open file 2
$file2 = fopen('/tmp/file-to-read-data-into-2', 'r+');

# query data, process response with anonymous function
$client->queryClosure(
   "SELECT * FROM system.numbers LIMIT 100", 
   function($line) use ($file1, $file2) {
       $row = json_decode($line);
       if ($row['number'] % 2 === 0) {
           fwrite($file1, $line . PHP_EOL);
       } else {
           fwrite($file2, $line . PHP_EOL);
       }
    }
);

Writing into Clickhouse

There are several ways to writing to the database as well.

Simple Insert

Most common way of writing to a database.

# write data to a table
$client->write('INSERT INTO myTable VALUES (1), (2), (3)');

NOTE: clickhouse does not have escape mechanisms like MySQL / Oracle / etc. For safe inserts please see other insert methods.

Rows Insert

The safest and easiest way to insert data into clickhouse table is to use "writeRows" method. The method takes first half of the insert statement as the first parameter and php array of rows as second. When inserting data method "writeRows" encodes the data into appropriate format for Clickhouse database to interpret. By default it is JSON format. Such approach does not require explicit escaping.

# write data to a table
$client->writeRows('INSERT INTO myTable',
    [
        ['number' => 5],
        ['number' => 6],
        ['number' => 7]
    ]
);

File Insert

Another way of inserting data is directly from a file.

NOTE: the format of the data in the file should match the one clickhouse is expecting

$stream = fopen('my/local/file.lines.json','r');

$client->writeStream(
    'INSERT INTO t',
    $stream
);

This method actually accepts data not only from a file, but from any php stream resource. Thus we can import data from other places, like memory (or anything that can be represented as a stream, really).

# create memory stream
$stream = fopen('php://memory','r+');
# write some data into it
fwrite($stream, '{"a":8}'.PHP_EOL.'{"a":9}'.PHP_EOL );
# rewind pointer to the beginning
rewind($stream);

# insert the data
$client->writeStream(
    'INSERT INTO t',
    $stream
);

System Queries

Client object supports system queries. Such queries can manage database schema, processes and more.

# drop table
$client->system('DROP TABLE IF EXISTS t');
# create table
$client->system('CREATE TABLE IF NOT EXISTS t  (a UInt8) ENGINE = Memory');
# kill query
$client->system('KILL QUERY WHERE query_id = "SOME-QUERY-ID"');

In case of failure to perform the operation client throws an Exception.

It is also possible to change the database for the whole client.

# change database
$client->config()->change('database', 'new-database');

This uses the same config object which is passed on "Client" object creation, so this code will also work.

# create config
$config = new Config();
# create client
$client = new Client($config);
# change database
$config->change('database', 'new-database');

Formats

There are several formats that Clickhouse support. This is used for retrieving and inserting data. Default format is set to JSON. When you perform simple select / insert queries data is encoded into JSON and transferred between client and Clickhouse. This does not work for Stream / Closure queries/writes. When performing any query/write you can change format of communication by passing a class name as the last parameter.

use ClickhouseClient\Client\Format;

# select using default JSON format
$client->query('SELECT * FROM system.numbers LIMIT 5');
# select using TabSeparated format
$client->query('SELECT * FROM system.numbers LIMIT 5', Format\TabSeparatedFormat::class);

# insert usin JSON format
$client->writeRows('INSERT INTO myTable',
    [
        ['number' => 5],
        ['number' => 6],
        ['number' => 7]
    ]
);
# insert usin TabSeparated format
$client->writeRows('INSERT INTO myTable',
    [
        ['number' => 5],
        ['number' => 6],
        ['number' => 7]
    ], 
    Format\TabSeparatedFormat::class
);

# create client with differrent default format
$client = new Client($config, Format\TabSeparatedFormat::class);
# create client without default format (which would result in errors in some cases)
$client = new Client($config, null);

Ping

Clickhouse Database supports a ping method.

You can check if the database responds properly using "ping" method of the client.

$client->ping();

In case of connection problems an Exception will be thrown.

Exception handling

Any request the client class makes can throw an exception.

It is a good practice to check for exceptions when performing query.

use ClickhouseClient\Exception\Exception;

try {
    $client->ping();
} catch (Exception $ex) {
    # get configurations of the connector
    $ex->getConfig();
    # get repsonse 
    $ex->getResponse();
    # and get the message, ofc
    $ex->getMessage();
}

Support

In case of ANY issues with the library please create an Issue on GitHub or contact by email [email protected]