PHP code example of slkxmail / clickhouse-client

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

slkxmail / clickhouse-client example snippets

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' => '']
);

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

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 ]
);

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');

use ClickhouseClient\Client\Client;

$client= new Client($config);

# 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();

# 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
);

 # 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);
        }
     }
 );
 

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

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

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

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

# 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
);

# 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"');

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

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

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);

$client->ping();

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();
}