smi2 / phpclickhouse

php ClickHouse wrapper

PHP ClickHouse wrapper

Features

• No dependency, only Curl (support php >=7.1 )
• Select parallel queries (asynchronous)
• Asynchronous bulk inserts from CSV file
• Http compression (Gzip), for bulk inserts
• Find active host, check cluster
• Select WHERE IN ( local csv file )
• SQL conditions & template
• tablesSize & databaseSize
• listPartitions
• truncateTable in cluster
• Insert array as column
• Get master node replica in cluster
• Get tableSize in all nodes
• Async get ClickHouse progress function
• streamRead/Write & Closure functions

Russian articles habr.com 1 on habr.com 2

Install composer

composer require smi2/phpclickhouse

In php

// vendor autoload 
$db = new ClickHouseDB\Client(['config_array']);

if (!$db->ping()) echo 'Error connect';

Last stable version for

• php 5.6 <= 1.1.2
• php 7.2 <= 1.3.10
• php 7.3 >= 1.4.x

Packagist

Start

Connect and select database:

$config = [
    'host' => '192.168.1.1',
    'port' => '8123',
    'username' => 'default',
    'password' => '',
    'https' => true
];
$db = new ClickHouseDB\Client($config);
$db->database('default');
$db->setTimeout(1.5);      // 1 second , support only Int value
$db->setTimeout(10);       // 10 seconds
$db->setConnectTimeOut(5); // 5 seconds
$db->ping(true); // if can`t connect throw exception  

Show tables:

print_r($db->showTables());

Create table:

$db->write('
    CREATE TABLE IF NOT EXISTS summing_url_views (
        event_date Date DEFAULT toDate(event_time),
        event_time DateTime,
        site_id Int32,
        site_key String,
        views Int32,
        v_00 Int32,
        v_55 Int32
    )
    ENGINE = SummingMergeTree(event_date, (site_id, site_key, event_time, event_date), 8192)
');

Show create table:

echo $db->showCreateTable('summing_url_views');

Insert data:

$stat = $db->insert('summing_url_views',
    [
        [time(), 'HASH1', 2345, 22, 20, 2],
        [time(), 'HASH2', 2345, 12, 9,  3],
        [time(), 'HASH3', 5345, 33, 33, 0],
        [time(), 'HASH3', 5345, 55, 0, 55],
    ],
    ['event_time', 'site_key', 'site_id', 'views', 'v_00', 'v_55']
);

If you need to insert UInt64 value, you can wrap the value in ClickHouseDB\Type\UInt64 DTO.

$statement = $db->insert('table_name',
    [
        [time(), UInt64::fromString('18446744073709551615')],
    ],
    ['event_time', 'uint64_type_column']
);
UInt64::fromString('18446744073709551615')

Select:

$statement = $db->select('SELECT * FROM summing_url_views LIMIT 2');

Work with Statement:

// Count select rows
$statement->count();

// Count all rows
$statement->countAll();

// fetch one row
$statement->fetchOne();

// get extremes min
print_r($statement->extremesMin());

// totals row
print_r($statement->totals());

// result all
print_r($statement->rows());

// totalTimeRequest
print_r($statement->totalTimeRequest());

// raw answer JsonDecode array, for economy memory
print_r($statement->rawData());

// raw curl_info answer
print_r($statement->responseInfo());

// human size info
print_r($statement->info());

// if clickhouse-server version >= 54011
$db->settings()->set('output_format_write_statistics',true);
print_r($statement->statistics());


// Statement Iterator
$state=$this->client->select('SELECT (number+1) as nnums FROM system.numbers LIMIT 5');
foreach ($state as $key=>$value) {
    echo $value['nnums'];
}

Select result as tree:

$statement = $db->select('
    SELECT event_date, site_key, sum(views), avg(views)
    FROM summing_url_views
    WHERE site_id < 3333
    GROUP BY event_date, url_hash
    WITH TOTALS
');

print_r($statement->rowsAsTree('event_date.site_key'));

/*
(
    [2016-07-18] => Array
        (
            [HASH2] => Array
                (
                    [event_date] => 2016-07-18
                    [url_hash] => HASH2
                    [sum(views)] => 12
                    [avg(views)] => 12
                )
            [HASH1] => Array
                (
                    [event_date] => 2016-07-18
                    [url_hash] => HASH1
                    [sum(views)] => 22
                    [avg(views)] => 22
                )
        )
)
*/

Drop table:

$db->write('DROP TABLE IF EXISTS summing_url_views');

Features

Select parallel queries (asynchronous)

$state1 = $db->selectAsync('SELECT 1 as ping');
$state2 = $db->selectAsync('SELECT 2 as ping');

// run
$db->executeAsync();

// result
print_r($state1->rows());
print_r($state2->fetchOne('ping'));

Parallelizing massive inserts from CSV file

$file_data_names = [
    '/tmp/clickHouseDB_test.1.data',
    '/tmp/clickHouseDB_test.2.data',
    '/tmp/clickHouseDB_test.3.data',
    '/tmp/clickHouseDB_test.4.data',
    '/tmp/clickHouseDB_test.5.data',
];

// insert all files
$stat = $db->insertBatchFiles(
    'summing_url_views',
    $file_data_names,
    ['event_time', 'site_key', 'site_id', 'views', 'v_00', 'v_55']
);

Parallelizing errors

selectAsync without executeAsync

$select = $db->selectAsync('SELECT * FROM summing_url_views LIMIT 1');
$insert = $db->insertBatchFiles('summing_url_views', ['/tmp/clickHouseDB_test.1.data'], ['event_time']);
// 'Exception' with message 'Queue must be empty, before insertBatch, need executeAsync'

see example/exam5_error_async.php

Gzip & enable_http_compression

On fly read CSV file and compress zlib.deflate.

$db->settings()->max_execution_time(200);
$db->enableHttpCompression(true);

$result_insert = $db->insertBatchFiles('summing_url_views', $file_data_names, [...]);


foreach ($result_insert as $fileName => $state) {
    echo $fileName . ' => ' . json_encode($state->info_upload()) . PHP_EOL;
}

see speed test example/exam08_http_gzip_batch_insert.php

Max execution time

$db->settings()->max_execution_time(200); // second

Connection without port

$config['host']='blabla.com';
$config['port']=0;
// getUri() === 'http://blabla.com'


$config['host']='blabla.com/urls';
$config['port']=8765;
// getUri() === 'http://blabla.com/urls'

$config['host']='blabla.com:2224';
$config['port']=1234;
// getUri() === 'http://blabla.com:2224'

tablesSize & databaseSize

Result in human size

print_r($db->databaseSize());
print_r($db->tablesSize());
print_r($db->tableSize('summing_partions_views'));

Partitions

$count_result = 2;
print_r($db->partitions('summing_partions_views', $count_result));

Select WHERE IN ( local csv file )

$file_name_data1 = '/tmp/temp_csv.txt'; // two column file [int,string]
$whereIn = new \ClickHouseDB\Query\WhereInFile();
$whereIn->attachFile($file_name_data1, 'namex', ['site_id' => 'Int32', 'site_hash' => 'String'], \ClickHouseDB\Query\WhereInFile::FORMAT_CSV);
$result = $db->select($sql, [], $whereIn);

// see example/exam7_where_in.php

Bindings

Bindings:

$date1 = new DateTime("now"); // DateTimeInterface

$Bindings = [
  'select_date' => ['2000-10-10', '2000-10-11', '2000-10-12'],
  'datetime'=>$date,
  'limit' => 5,
  'from_table' => 'table'
];

$statement = $db->selectAsync("SELECT FROM {from_table} WHERE datetime=:datetime limit {limit}", $Bindings);

// Double bind in {KEY}
$keys=[
            'A'=>'{B}',
            'B'=>':C',
            'C'=>123,
            'Z'=>[':C',':B',':C']
        ];
$this->client->selectAsync('{A} :Z', $keys)->sql() // ==   "123 ':C',':B',':C' FORMAT JSON",

Simple sql conditions & template

Conditions is deprecated, if need use: $db->enableQueryConditions();

Example with QueryConditions:

$db->enableQueryConditions();

$input_params = [
  'select_date' => ['2000-10-10', '2000-10-11', '2000-10-12'],
  'limit' => 5,
  'from_table' => 'table'
];

$select = '
    SELECT * FROM {from_table}
    WHERE
    {if select_date}
        event_date IN (:select_date)
    {else}
        event_date=today()
    {/if}
    {if limit}
    LIMIT {limit}
    {/if}
';

$statement = $db->selectAsync($select, $input_params);
echo $statement->sql();

/*
SELECT * FROM table
WHERE
event_date IN ('2000-10-10','2000-10-11','2000-10-12')
LIMIT 5
FORMAT JSON
*/

$input_params['select_date'] = false;
$statement = $db->selectAsync($select, $input_params);
echo $statement->sql();

/*
SELECT * FROM table
WHERE
event_date=today()
LIMIT 5
FORMAT JSON
*/

$state1 = $db->selectAsync(
    'SELECT 1 as {key} WHERE {key} = :value',
    ['key' => 'ping', 'value' => 1]
);

// SELECT 1 as ping WHERE ping = "1"

Example custom query Degeneration in exam16_custom_degeneration.php

SELECT {ifint VAR} result_if_intval_NON_ZERO{/if}
SELECT {ifint VAR} result_if_intval_NON_ZERO {else} BLA BLA{/if}

Settings

3 way set any settings

// in array config
$config = [
    'host' => 'x',
    'port' => '8123',
    'username' => 'x',
    'password' => 'x',
    'settings' => ['max_execution_time' => 100]
];
$db = new ClickHouseDB\Client($config);

// settings via constructor
$config = [
    'host' => 'x',
    'port' => '8123',
    'username' => 'x',
    'password' => 'x'
];
$db = new ClickHouseDB\Client($config, ['max_execution_time' => 100]);

// set method
$config = [
    'host' => 'x',
    'port' => '8123',
    'username' => 'x',
    'password' => 'x'
];
$db = new ClickHouseDB\Client($config);
$db->settings()->set('max_execution_time', 100);

// apply array method
$db->settings()->apply([
    'max_execution_time' => 100,
    'max_block_size' => 12345
]);

// check
if ($db->settings()->getSetting('max_execution_time') !== 100) {
    throw new Exception('Bad work settings');
}

// see example/exam10_settings.php

Use session_id with ClickHouse

useSession() - make new session_id or use exists useSession(value)

// enable session_id
$db->useSession();
$sesion_AA=$db->getSession(); // return session_id

$db->write(' CREATE TEMPORARY TABLE IF NOT EXISTS temp_session_test (number UInt64)');
$db->write(' INSERT INTO temp_session_test SELECT number*1234 FROM system.numbers LIMIT 30');

// reconnect to continue with other session
$db->useSession($sesion_AA);

Array as column

$db->write('
    CREATE TABLE IF NOT EXISTS arrays_test_string (
        s_key String,
        s_arr Array(String)
    )
    ENGINE = Memory
');

$db->insert('arrays_test_string',
    [
        ['HASH1', ["a", "dddd", "xxx"]],
        ['HASH1', ["b'\tx"]],
    ],
    ['s_key', 's_arr']
);

// see example/exam12_array.php

Class for FormatLine array

var_dump(
    ClickHouseDB\Quote\FormatLine::CSV(
        ['HASH1', ["a", "dddd", "xxx"]]
    )
);

var_dump(
    ClickHouseDB\Quote\FormatLine::TSV(
        ['HASH1', ["a", "dddd", "xxx"]]
    )
);

// example write to file
$row=['event_time'=>date('Y-m-d H:i:s'),'arr1'=>[1,2,3],'arrs'=>["A","B\nD\nC"]];
file_put_contents($fileName,ClickHouseDB\Quote\FormatLine::TSV($row)."\n",FILE_APPEND);

HTTPS

$db = new ClickHouseDB\Client($config);
$db->settings()->https();

getServer System.Settings & Uptime

print_r($db->getServerUptime());

print_r($db->getServerSystemSettings());

print_r($db->getServerSystemSettings('merge_tree_min_rows_for_concurrent_read'));

ReadOnly ClickHouse user

$config = [
    'host' => '192.168.1.20',
    'port' => '8123',
    'username' => 'ro',
    'password' => 'ro',
    'readonly' => true
];

Direct write to file

Send result from clickhouse, without parse json.

$WriteToFile=new ClickHouseDB\WriteToFile('/tmp/_1_select.csv');
$db->select('select * from summing_url_views',[],null,$WriteToFile);
// or
$db->selectAsync('select * from summing_url_views limit 4',[],null,new ClickHouseDB\WriteToFile('/tmp/_3_select.tab',true,'TabSeparatedWithNames'));
$db->selectAsync('select * from summing_url_views limit 4',[],null,new ClickHouseDB\WriteToFile('/tmp/_4_select.tab',true,'TabSeparated'));
$statement=$db->selectAsync('select * from summing_url_views limit 54',[],null,new ClickHouseDB\WriteToFile('/tmp/_5_select.csv',true,ClickHouseDB\WriteToFile::FORMAT_CSV));

Stream

streamWrite() : Closure stream write

$streamWrite=new ClickHouseDB\Transport\StreamWrite($stream);

$client->streamWrite(
        $streamWrite,                                   // StreamWrite Class
        'INSERT INTO {table_name} FORMAT JSONEachRow',  // SQL Query
        ['table_name'=>'_phpCh_SteamTest']              // Binds
    );

streamWrite & custom Closure & Deflate

$stream = fopen('php://memory','r+');

for($f=0;$f<23;$f++) {  // Make json data in stream
        fwrite($stream, json_encode(['a'=>$f]).PHP_EOL );
}

rewind($stream); // rewind stream


$streamWrite=new ClickHouseDB\Transport\StreamWrite($stream);
$streamWrite->applyGzip();   // Add Gzip zlib.deflate in stream

$callable = function ($ch, $fd, $length) use ($stream) {
    return ($line = fread($stream, $length)) ? $line : '';
};
// Apply closure
$streamWrite->closure($callable);
// Run Query
$r=$client->streamWrite($streamWrite,'INSERT INTO {table_name} FORMAT JSONEachRow', ['table_name'=>'_phpCh_SteamTest']);
// Result
print_r($r->info_upload());

streamRead

streamRead is like WriteToFile

$stream = fopen('php://memory','r+');
$streamRead=new ClickHouseDB\Transport\StreamRead($stream);

$r=$client->streamRead($streamRead,'SELECT sin(number) as sin,cos(number) as cos FROM {table_name} LIMIT 4 FORMAT JSONEachRow', ['table_name'=>'system.numbers']);
rewind($stream);
while (($buffer = fgets($stream, 4096)) !== false) {
    echo ">>> ".$buffer;
}
fclose($stream); // Need Close Stream



// Send to closure

$stream = fopen('php://memory','r+');
$streamRead=new ClickHouseDB\Transport\StreamRead($stream);
$callable = function ($ch, $string) use ($stream) {
    // some magic for _BLOCK_ data
    fwrite($stream, str_ireplace('"sin"','"max"',$string));
    return strlen($string);
};

$streamRead->closure($callable);

$r=$client->streamRead($streamRead,'SELECT sin(number) as sin,cos(number) as cos FROM {table_name} LIMIT 44 FORMAT JSONEachRow', ['table_name'=>'system.numbers']);

insert Assoc Bulk

 $oneRow = [
            'one' => 1,
            'two' => 2,
            'thr' => 3,
            ];
            $failRow = [
                'two' => 2,
                'one' => 1,
                'thr' => 3,
            ];

$db->insertAssocBulk([$oneRow, $oneRow, $failRow])

Auth methods

   AUTH_METHOD_HEADER       = 1;
   AUTH_METHOD_QUERY_STRING = 2;
   AUTH_METHOD_BASIC_AUTH   = 3;

In config set auth_method

$config=[
    'host'=>'host.com',
    //...
    'auth_method'=>1,
];

progressFunction

// Apply function

$db->progressFunction(function ($data) {
    echo "CALL FUNCTION:".json_encode($data)."\n";
});
$st=$db->select('SELECT number,sleep(0.2) FROM system.numbers limit 5');


// Print
// ...
// CALL FUNCTION:{"read_rows":"2","read_bytes":"16","total_rows":"0"}
// CALL FUNCTION:{"read_rows":"3","read_bytes":"24","total_rows":"0"}
// ...

ssl CA

$config = [
    'host' => 'cluster.clickhouse.dns.com', // any node name in cluster
    'port' => '8123',
    'sslCA' => '...', 
];

Cluster

$config = [
    'host' => 'cluster.clickhouse.dns.com', // any node name in cluster
    'port' => '8123',
    'username' => 'default', // all node have one login+password
    'password' => ''
];


// client connect first node, by DNS, read list IP, then connect to ALL nodes for check is !OK!


$cl = new ClickHouseDB\Cluster($config);
$cl->setScanTimeOut(2.5); // 2500 ms, max time connect per one node

// Check replica state is OK
if (!$cl->isReplicasIsOk())
{
    throw new Exception('Replica state is bad , error='.$cl->getError());
}

// get array nodes, and clusers
print_r($cl->getNodes());
print_r($cl->getClusterList());


// get node by cluster
$name='some_cluster_name';
print_r($cl->getClusterNodes($name));

// get counts
echo "> Count Shard   = ".$cl->getClusterCountShard($name)."\n";
echo "> Count Replica = ".$cl->getClusterCountReplica($name)."\n";

// get nodes by table & print size per node
$nodes=$cl->getNodesByTable('shara.adpreview_body_views_sharded');
foreach ($nodes as $node)
{
    echo "$node > \n";
    // select one node
    print_r($cl->client($node)->tableSize('adpreview_body_views_sharded'));
    print_r($cl->client($node)->showCreateTable('shara.adpreview_body_views'));
}

// work with one node

// select by IP like "*.248*" = `123.123.123.248`, dilitmer `;`  , if not fount -- select first node
$cli=$cl->clientLike($name,'.298;.964'); // first find .298 then .964 , result is ClickHouseDB\Client

$cli->ping();



// truncate table on cluster
$result=$cl->truncateTable('dbNane.TableName_sharded');

// get one active node ( random )
$cl->activeClient()->setTimeout(500);
$cl->activeClient()->write("DROP TABLE IF EXISTS default.asdasdasd ON CLUSTER cluster2");


// find `is_leader` node
$cl->getMasterNodeForTable('dbNane.TableName_sharded');


// errors
var_dump($cl->getError());


//

Return Extremes

$db->enableExtremes(true);

Enable Log Query

You can log all query in ClickHouse

$db->enableLogQueries();
$db->select('SELECT 1 as p');
print_r($db->select('SELECT * FROM system.query_log')->rows());

isExists

$db->isExists($database,$table);

Debug & Verbose

$db->verbose();

Verbose to file|steam:

    // init client
    $cli = new Client($config);
    $cli->verbose();
    // temp stream
    $stream = fopen('php://memory', 'r+');
    // set stream to curl
    $cli->transport()->setStdErrOut($stream);
    // exec curl
    $st=$cli->select('SElect 1 as ppp');
    $st->rows();
    // rewind 
    fseek($stream,0,SEEK_SET);
    
    // output
    echo stream_get_contents($stream);

Dev & PHPUnit Test

• Don't forget to run composer install. It should setup PSR-4 autoloading.
• Then you can simply run vendor/bin/phpunit and it should output the following

cp phpunit.xml.dist phpunit.xml
mcedit phpunit.xml

Edit in phpunit.xml constants:

<php>
    <env name="CLICKHOUSE_HOST" value="127.0.0.1" />
    <env name="CLICKHOUSE_PORT" value="8123" />
    <env name="CLICKHOUSE_USER" value="default" />
    <env name="CLICKHOUSE_DATABASE" value="phpChTestDefault" />
    <env name="CLICKHOUSE_PASSWORD" value="" />
    <env name="CLICKHOUSE_TMPPATH" value="/tmp" />
</php>

Run docker ClickHouse server

cd ./tests
docker-compose up

Run test

./vendor/bin/phpunit

./vendor/bin/phpunit --group ClientTest

./vendor/bin/phpunit --group ClientTest --filter testInsertNestedArray

./vendor/bin/phpunit --group ConditionsTest

Run PHPStan

# Main
./vendor/bin/phpstan analyse src tests --level 7
# SRC only
./vendor/bin/phpstan analyse src --level 7



# Examples
./vendor/bin/phpstan analyse example -a ./example/Helper.php

License

MIT

ChangeLog

See changeLog.md

phpclickhouse's Issues

Не существующий метод: setConnectTimeOut()

Ребята, приветсвтую!
При использовании драйвера phpClickHouse возникла необходимость увеличить connection time out, т.е как очень часто появляеться сообщение об ошибке: "Exception: Connection timed out after 1001 milliseconds".
На странице документации есть код:
$db->setTimeout(1.5); // 1500 ms
$db->setTimeout(10); // 10 seconds
$db->setConnectTimeOut(5); // 5 seconds

но, это приводит к ошибке: Call to undefined method ClickHouseDB\Client::setConnectTimeOut().

Поиски соотв. методов в коде не дали результатов.
Обновление к новой версии библиотеки composer install тоже не помогло.

Как можно увеличить время соединения к ClickHouse, желательно это делать через конфиг (или setter-метод), чтобы настройка "не слетала" каждый раз при обновлении.

Спасибо!

Search with cyrillic symbols in 'LIKE' returns no results

I have a query, which has condition concat(name, ' ', last_name) LIKE '%some_string%' and I have found out, that if some_string has cyrillic symbols, the result has no rows. But when I use the same query in clickhouse-client, it returns correct results.
Such behavior makes me think, that there is some problem with the body of request somewhere in phpClickHouse. I tried to debug it myself but have had no luck in detecting a place with the issue.

Invalid user exception

I'm tried this library, it work fine when we submit correct during connect database, it work well. if we submit invalid user, php will failed. Hope it can use others handling like return false or etc to prevent php exit.

Strings in array are not escaped correctly

To get that error you should to execute function
FormatLine::CSV();

Simple example:

$str = '"name", "age", "['\"That does not\"', 'That work']"';
FormatLine::CSV($str);

To fix this case I replaced all double quotes to single quotes for array

setHost with array, picking random?

Hi, what is the purpose of this line?

phpClickHouse/src/Client.php

Line 157 in b661da0

$host = array_rand(array_flip($host));

It's not covered with tests nor documentation so I didn't manage to find out how to use it.

Thanks!

phpclickhouse consume too much memory

Hi,
I notice phpclickhouse consume significant of resources, example:
I have a table have roughly 750,000 rows of records.

Init workerman, before run sql select() memory usage: 2.47 mb
after run SQL select($sql) (but fetch rows() ), memory usage: 719.42 mb
after rows(), memory usage: 3.47 gb
Is there any method to reduce into minimal?
Thanks,
KS Tan

Bug: Binding array values

Replace binding values from largest key to smallest.
To avoid wrong replacing.

Example:

[
   ':key1' => 100500,
   ':key123' => 500100,
]

:key1, :key123

Expected:
100500, 500100

Returned:
100500, 10050023

Does not work inserting with the database name in the table

Table default.db.table_name doesn't exist.                            
  IN:INSERT INTO `db.table_name` (`column1`,`column2`...

Broken in #83

Переписать checkServerReplicas

checkServerReplicas in http - wtf ?

New features

1. One function query auto route select or write
2. Closure stream read

curl_setopt($ch, CURLOPT_WRITEFUNCTION, function($curl, $str) use ($closureFunction, &$buffer) { ....
});

3. $client->getSystemSettings() -> 'SELECT * FROM system.settings'
4. $client->getUptime() -> 'SELECT uptime()'
5. $clent->insertBatchStream() - $columns_array can empty

Connection without port

Hi guys,

I've got a problem with connection. My clickhouse database is running on subpage (it is a reverse proxy) like this: https://example.com/clickhouse

The problem is when I init a client it creates this URL https://example.com/clickhouse:0 which is completely wrong. The only solution for me is to remove port from here It would be great if you could change settings and append port only when it is defined.

Use Semantic versioning

This library uses versions like 0.year.month.increment

It would be better to follow standard and use Semantic versioning so it can be safely bound in composer, eg. ^0.18 that won't install any breaking changes but only patches.

few improvements

Hi!
We use this library in own project, this works very well! But missing a few things, that it was production-ready.
I propose to make a few improvements:

• add composer.json
• \ClickHouseDB\Settings - there is a need to set any settings. Now in my fork I made set() as public. Maybe better way - pass settings via constructor in Client
• error handling as exceptions
• unit-tests

Please let me know, what you think about it and what is your roadmap?

HHVM & zlib.deflate + stream_filter_append

Не работает в HHVM

           stream_filter_append($this->infile_handle, 'zlib.deflate', STREAM_FILTER_READ, ["window" => 30]);

Need test for direct_write_result.php

see example/exam15_direct_write_result.php

 $request->setResultFileHandle($fout, $isGz)->setCallbackFunction(function (CurlerRequest $request) {

in

public function getRequestRead(Query $query, $whereInFile = null, $writeToFile = null)

Move changelog to Releases

I propose to remove Changelog section from README and document changes in https://github.com/smi2/phpClickHouse/releases. What do you think?

Personally, I think readme is too long and would be fine to clean it up a little.

Extremes in readonly mode

PHP Fatal error:  Uncaught ClickHouseDB\DatabaseException: 
    Cannot override setting (extremes) in readonly mode
IN:show databases 
    FORMAT JSON in /home/u/api/vendor/smi2/phpclickhouse/src/Statement.php:166

Maybe can u do not send extremes query arg if this does not set?

Same with: max_execution_time, enable_http_compression and readonly

Insert max UInt64

How do you insert 18446744073709551615 value to column of type UInt64? Thx

Cluster SELECT * FROM system.replicas

Если запрашивать все столбцы, то таблица может работать слегка медленно, так как на каждую строчку делается несколько чтений из ZK.

Если не запрашивать последние 4 столбца (log_max_index, log_pointer, total_replicas, active_replicas), то таблица работает быстро.

PHP-alpine: При включение компрессии запись в БД валится

При включение компрессии
$client->enableHttpCompression(true);

Вываливается ошибка:
Warning: curl_setopt_array(): cannot cast a filtered stream on this system in /var/www/analytics/vendor/smi2/phpclickhouse/src/Transport/CurlerRequest.php on line 695

Думаю что проблема в том, что для загрузки файла используется ключ curl CURLOPT_SAFE_UPLOAD который в версии php 7 удален.
Есть ли какие патчи на эту багу?

After 0.18.05.09 possible problems with FORMAT JSON

We use smi2/phpClickHouse for Doctrine/DBAL driver
After recent 0.18.05.09 got strange error
It seems what queries ended with ' FORMAT JSON' work unproper

details: FriendsOfDoctrine/dbal-clickhouse#9

/cc @argayash

Drop partitions in cluster

Создание Query Builder'a

В первую очередь хочу поблагодарить Вас за Ваш труд!

Подскажите пожалуйста, планируется ли создание Query Builder'a Вашей командой?

Дело в том, что такой пакет мог бы существенно упростить интеграцию Вашего клиента с большинством веб-фреймворков и ORM.

Например, сейчас я использую Laravel и Ваш клиент. Написание тестов дается довольно сложно, так как клиент никак не интегрируется с фреймворком. Нет возможности создания моделей и прочих удобств.

Нашел решение в виде сочетания пакетов:

• the-tinderbox/ClickhouseClient <-- аналог Вашего клиента, реализован используя Guzzle
• the-tinderbox/ClickhouseBuilder <-- Query Builder (которого не хватает) на основе клиента
• esazykin/laravel-clickhouse <-- Интеграция с фреймворком на основе Query Builder'a

Недостаток этого решения в том, что там есть доп. зависимости + пакеты похоже заброшены.

Coding standard

Would you be interested in setting up unified code style for this library? Personally, I try to use https://github.com/doctrine/coding-standard in every project as IMHO it is the best CS available. The whole Doctrine project is being migrated to it.

We can add it as a check into Travis after it's added. Having coding standard is great because every contribution to the library is consistent with the rest of the codebase and when automated, we don't have to bother with it during code reviews.

I'm willing to add it in next PR if interested.

Отсутствие документации по развертыванию кластера

В документации отсутствует описание развартывания кластера с несколькими серверами. Неплохой пример есть тут:
https://github.com/the-tinderbox/ClickhouseClient#cluster

Передача данных через класс StreamInsert

Пробую загружать данные в КХ через класс StreamInsert, но данные передаются не консистентно.

clickhouse | Column 0,   name: date,  type: Date,  parsed text: "2018-12-11"
clickhouse | Column 1,   name: app,  type: UInt32,  parsed text: "12"
...
...
clickhouse | Column 11,  name: os,  type: String,  parsed text: "ios"
clickhouse | Column 12,  name: os_version,  type: String,  parsed text: "11.2.2"
clickhouse | Column 13,  name: device,  type: String,  parsed text: "Ap2018-12-11"
clickhouse | Column 14,  name: device_model, type: String, parsed text: "12"

Обратите внимание, на то как была вставлена колонка 13, в ней данные из одной строки были перекрыты данными из следующей, т.е все в данном случае все строки которые идут после 13 не корректные.

HHVM (VM 3.9.1): CURLOPT_SAFE_UPLOAD

• HHVM (VM 3.9.1): CURLOPT_SAFE_UPLOAD : hhvm example/exam7_where_in.php

Работа с Cluster

• Определение работы / доступности всего Cluster
• Оправка запроса Write на каждый узел Cluster
• Оправка запроса Write на каждый узел Cluster по названию кластера/реплики/шардов

Отработка состояний:

• Создание/Удаление таблиц - все ноды = шарды
• Создание/Удаление таблиц - 50% ноды = шарды и 50% нод = реплики

Определение рабочей ноды из списка DNS, авто переключение на рандомную

[ClickHouseDB\QueryException] connect() timed out!

Привет, делаю в бесконечном цикле кучу селектов и инсертов и рандомно (может через 5 секунд, а может через 10 минут) вылетает ексепшен.
Причём поиском по коду такой фрагмент месаджа нигде не находится. Так что за эксепшен такой, о каком таймауте речь и как его увеличить?

  [ClickHouseDB\QueryException]
  connect() timed out!

Exception trace:
 () at /var/www/vendor/smi2/phpclickhouse/src/Statement.php:178
 ClickHouseDB\Statement->error() at /var/www/vendor/smi2/phpclickhouse/src/Transport/Http.php:426
 ClickHouseDB\Transport\Http->write() at /var/www/vendor/smi2/phpclickhouse/src/Client.php:228

        $config = [
            'host' => env('DB_CLICKHOUSE_HOST'),
            'port' => env('DB_CLICKHOUSE_PORT'),
            'username' => env('DB_CLICKHOUSE_USERNAME'),
            'password' => env('DB_CLICKHOUSE_PASSWORD')
        ];

        $Clickhouse = new Client($config);
        $Clickhouse->database('db');

exception 'ClickHouseDB\QueryException' with message 'HttpCode:0 ; '

Возможно timeout ?

Why is dropOldPartitions() deprecated

Hello, why is dropOldPartitions() deprecated? Thanks!

drop partition error

Hello. I have an error during removing partition
Message= String is too long for Date: '2017-12-26'
IN:ALTER TABLE operation_log DROP PARTITION ''2017-12-26''

Partition list :

Array
(
    [20171226_10_10_0] => Array
        (
            [partition] => '2017-12-26'
            [name] => 20171226_10_10_0
            [active] => 1
            [marks] => 1
            [marks_size] => 144
            [rows] => 1
            [bytes] => 1311
            [modification_time] => 2017-12-26 17:06:31
            [remove_time] => 0000-00-00 00:00:00
            [refcount] => 1
            [min_date] => 2017-12-26
            [max_date] => 2017-12-26
            [min_block_number] => 10
            [max_block_number] => 10
            [level] => 0
            [primary_key_bytes_in_memory] => 4
            [primary_key_bytes_in_memory_allocated] => 4096
            [database] => netflow
            [table] => operation_log
            [engine] => MergeTree
        )

    [20171226_9_9_0] => Array
        (
            [partition] => '2017-12-26'
            [name] => 20171226_9_9_0
            [active] => 1
            [marks] => 1
            [marks_size] => 144
            [rows] => 1
            [bytes] => 1311
            [modification_time] => 2017-12-26 17:06:30
            [remove_time] => 0000-00-00 00:00:00
            [refcount] => 1
            [min_date] => 2017-12-26
            [max_date] => 2017-12-26
            [min_block_number] => 9
            [max_block_number] => 9
            [level] => 0
            [primary_key_bytes_in_memory] => 4
            [primary_key_bytes_in_memory_allocated] => 4096
            [database] => netflow
            [table] => operation_log
            [engine] => MergeTree
        )

    [20171226_8_8_0] => Array
        (
            [partition] => '2017-12-26'
            [name] => 20171226_8_8_0
            [active] => 1
            [marks] => 1
            [marks_size] => 144
            [rows] => 1
            [bytes] => 1311
            [modification_time] => 2017-12-26 17:06:29
            [remove_time] => 0000-00-00 00:00:00
            [refcount] => 1
            [min_date] => 2017-12-26
            [max_date] => 2017-12-26
            [min_block_number] => 8
            [max_block_number] => 8
            [level] => 0
            [primary_key_bytes_in_memory] => 4
            [primary_key_bytes_in_memory_allocated] => 4096
            [database] => netflow
            [table] => operation_log
            [engine] => MergeTree
        )

    [20171226_7_7_0] => Array
        (
            [partition] => '2017-12-26'
            [name] => 20171226_7_7_0
            [active] => 1
            [marks] => 1
            [marks_size] => 144
            [rows] => 1
            [bytes] => 1311
            [modification_time] => 2017-12-26 17:06:28
            [remove_time] => 0000-00-00 00:00:00
            [refcount] => 1
            [min_date] => 2017-12-26
            [max_date] => 2017-12-26
            [min_block_number] => 7
            [max_block_number] => 7
            [level] => 0
            [primary_key_bytes_in_memory] => 4
            [primary_key_bytes_in_memory_allocated] => 4096
            [database] => netflow
            [table] => operation_log
            [engine] => MergeTree
        )

    [20171226_6_6_0] => Array
        (
            [partition] => '2017-12-26'
            [name] => 20171226_6_6_0
            [active] => 0
            [marks] => 1
            [marks_size] => 144
            [rows] => 1
            [bytes] => 1311
            [modification_time] => 2017-12-26 16:56:50
            [remove_time] => 2017-12-26 17:06:07
            [refcount] => 1
            [min_date] => 2017-12-26
            [max_date] => 2017-12-26
            [min_block_number] => 6
            [max_block_number] => 6
            [level] => 0
            [primary_key_bytes_in_memory] => 4
            [primary_key_bytes_in_memory_allocated] => 4096
            [database] => netflow
            [table] => operation_log
            [engine] => MergeTree
        )

    [20171226_5_5_0] => Array
        (
            [partition] => '2017-12-26'
            [name] => 20171226_5_5_0
            [active] => 0
            [marks] => 1
            [marks_size] => 144
            [rows] => 1
            [bytes] => 1311
            [modification_time] => 2017-12-26 16:56:49
            [remove_time] => 2017-12-26 17:06:07
            [refcount] => 1
            [min_date] => 2017-12-26
            [max_date] => 2017-12-26
            [min_block_number] => 5
            [max_block_number] => 5
            [level] => 0
            [primary_key_bytes_in_memory] => 4
            [primary_key_bytes_in_memory_allocated] => 4096
            [database] => netflow
            [table] => operation_log
            [engine] => MergeTree
        )

    [20171226_4_4_0] => Array
        (
            [partition] => '2017-12-26'
            [name] => 20171226_4_4_0
            [active] => 0
            [marks] => 1
            [marks_size] => 144
            [rows] => 1
            [bytes] => 1311
            [modification_time] => 2017-12-26 16:56:48
            [remove_time] => 2017-12-26 17:06:07
            [refcount] => 1
            [min_date] => 2017-12-26
            [max_date] => 2017-12-26
            [min_block_number] => 4
            [max_block_number] => 4
            [level] => 0
            [primary_key_bytes_in_memory] => 4
            [primary_key_bytes_in_memory_allocated] => 4096
            [database] => netflow
            [table] => operation_log
            [engine] => MergeTree
        )

    [20171226_3_3_0] => Array
        (
            [partition] => '2017-12-26'
            [name] => 20171226_3_3_0
            [active] => 0
            [marks] => 1
            [marks_size] => 144
            [rows] => 1
            [bytes] => 1311
            [modification_time] => 2017-12-26 16:56:47
            [remove_time] => 2017-12-26 17:06:07
            [refcount] => 1
            [min_date] => 2017-12-26
            [max_date] => 2017-12-26
            [min_block_number] => 3
            [max_block_number] => 3
            [level] => 0
            [primary_key_bytes_in_memory] => 4
            [primary_key_bytes_in_memory_allocated] => 4096
            [database] => netflow
            [table] => operation_log
            [engine] => MergeTree
        )

)
Here is my table
CREATE TABLE IF NOT EXISTS `operation_log` (
    `event_date` Date default toDate(time),
    `time` DateTime default  now(),
    `event` String  DEFAULT '',
    `service_name` String  DEFAULT '',
    `runtime` UInt8 DEFAULT 0,
    `service_type` UInt8 DEFAULT 0,
    `response_text` String  DEFAULT '',
    `debug_level` String  DEFAULT '',
    `debug_response_text` String  DEFAULT ''
) ENGINE=MergeTree ORDER BY time PARTITION BY event_date

This is insert query
INSERT INTO operation_log (event, service_name, runtime, service_type, response_text,debug_level,debug_response_text) 
 VALUES ('request', 'Delete Tables', 0, 1, 'Deleted tables: netflow_v4_3,netflow_v4_1', 'normal', '');

This is how to reproduce
1- create table...
2- insert data
3- drop partiton 
4- select * from table => nothing
5 - insert ...
6- select -> some records
7 - drop partition
8 - select => nothing
........
20 - drop partition ..
21- select -> nothing
22- insert 
23 - seelct -> some records

cURL + fopen/fclose

Добрый день.

Есть необходимость использовать прямую запись в файл (именно через простой select):

$WriteToFile=new ClickHouseDB\WriteToFile('/tmp/_1_select.csv');
$db->select('select * from summing_url_views',[],null,$WriteToFile);

Однако, мне бы хотелось иметь возможность писать в уже существующий файл (с данными), а также дополнять его сразу после записи.

Огромная просьба вывести в параметры WriteToFile мод fopen в getRequestRead() в Http.php.

Заранее большое спасибо!

date_default_timezone_set

Disable default_timezone_set

date_default_timezone_set('Europe/Moscow');

Убрать Conditions и Bindings в отдельные классы

• Отказаться от Bindings
• Отказаться от ConditionsIfElse
• Выполнение нативных запросов без использования обертки

Сделать класс изменяемых запросов - допустим \ClickHouseDB\Query\Degeneration

interface Degeneration
{
    public function process($sql);
    public function bindParams(array $bindings);
}

Добавить в ClickHouseDB\Transport\Http

 public function cleanQueryDegeneration()
    {
        $this->_query_degenerations=[];
    }
    public function addQueryDegeneration(Query\Degeneration $degeneration)
    {
        $this->_query_degenerations[]=$degeneration;
    }

Изменить prepareWrite , если передать $db->write(new Query("SELECT BLABLA")); выполнить без оберток

      if ($sql instanceof Query)
        {
            return $this->getRequestWrite($sql);
        }

Auth error on empty username and password

With empty login and password, I get an authorization error.
Fix in CurlerRequest.php::438
if (!empty($username)) { $this->options[CURLOPT_USERPWD] = sprintf("%s:%s", $username, $password); }

tablesize - formatReadableSize - not working

Hello
I faced an issue:
$client -> tableSize($table);
http://prntscr.com/jwxtbs
and as a result i got empty response
If i remove formatReadableSize(sum(bytes)) as size, from select quesry - all works ok

Прямая запись результата

Clickhouse dump СSV в file, для mysql load local

• gzip

Неправильная подстановка bindings

Вот такой код:

$db = new ClickHouseDB\Client([
    "host" => "127.0.0.1",
    "port" => 8123,
    "username" => "default",
    "password" => ""
]);

$db->select("SELECT :a, :b", [
    "a" => ":b",
    "b" => ":a"
])->rows();

выдаёт ошибку:

PHP Fatal error:  Uncaught ClickHouseDB\DatabaseException: Syntax error: failed at position 10: :a'', ':a' FORMAT JSON. Expected one of: identifier, alias, AS, token, AND, OR, BETWEEN, IS, LIKE, NOT LIKE, IN, NOT IN, GLOBAL IN, GLOBAL NOT IN, Comma, QuestionMark, ORDER BY, SETTINGS, INTO OUTFILE, FORMAT, FROM, PREWHERE, WHERE, GROUP BY, WITH, HAVING, LIMIT, UNION ALL
IN:SELECT '':a'', ':a' FORMAT JSON in /home/user/test/phpcl_bug/phpClickHouse/src/Statement.php:162
Stack trace:
#0 /home/user/test/phpcl_bug/phpClickHouse/src/Statement.php(196): ClickHouseDB\Statement->error()
#1 /home/user/test/phpcl_bug/phpClickHouse/src/Statement.php(210): ClickHouseDB\Statement->check()
#2 /home/user/test/phpcl_bug/phpClickHouse/src/Statement.php(459): ClickHouseDB\Statement->init()
#3 /home/user/test/phpcl_bug/test.php(15): ClickHouseDB\Statement->rows()
#4 {main}
  thrown in /home/user/test/phpcl_bug/phpClickHouse/src/Statement.php on line 162

Другой пример:

$db->select("SELECT :a, :a2", [
    "a" => "x",
    "a2" => "x"
])->rows();

PHP Fatal error:  Uncaught ClickHouseDB\DatabaseException: Syntax error: failed at position 16: 2 FORMAT JSON. Expected one of: identifier, alias, AS, token, AND, OR, BETWEEN, IS, LIKE, NOT LIKE, IN, NOT IN, GLOBAL IN, GLOBAL NOT IN, Comma, QuestionMark, ORDER BY, SETTINGS, INTO OUTFILE, FORMAT, FROM, PREWHERE, WHERE, GROUP BY, WITH, HAVING, LIMIT, UNION ALL
IN:SELECT 'x', 'x'2 FORMAT JSON in /home/user/test/phpcl_bug/phpClickHouse/src/Statement.php:162
Stack trace:
#0 /home/user/test/phpcl_bug/phpClickHouse/src/Statement.php(196): ClickHouseDB\Statement->error()
#1 /home/user/test/phpcl_bug/phpClickHouse/src/Statement.php(210): ClickHouseDB\Statement->check()
#2 /home/user/test/phpcl_bug/phpClickHouse/src/Statement.php(459): ClickHouseDB\Statement->init()
#3 /home/user/test/phpcl_bug/test.php(22): ClickHouseDB\Statement->rows()
#4 {main}
  thrown in /home/user/test/phpcl_bug/phpClickHouse/src/Statement.php on line 162

Problems with "Not set `FORMAT JSON` if set FORMAT in sql"

Hi.

I've got query like "select format_id from ...", and "$query->setFormat('JSON');" in prepareSelect function.

Because of the commit "bc810d0", such queries had been broken.

Suggested solution:

Change the line 55 to if (stripos($this->sql, ' FORMAT ') < 1) {

Maybe there is more elegance solution, such as regexp "FORMAT\s(\w)*$", but I am not sure about perfomance.

Drop support for legacy PHP versions

I have mentioned it few times ago, creating this issue just to be notified, when it is dropped so I don't have to come back regularly and read commits :)

Current PHP version is 7.2, 7.3 will be released soon.

5.6 is ~4 years old, currently with security support only, EOL in 5 months.
7.0 EOL in 6 months

Therefore, old php versions support should be dropped and 7.1 set as minimum requirement.

https://secure.php.net/supported-versions.php

Invalid version in composer.json

You have invalid version in https://github.com/smi2/phpClickHouse/blob/0.17.08/composer.json

Method `setConnectTimeOut` doesn't exist

Method setConnectTimeOut mentioned in readme file is not implemented in source code. Actually, now there is no way to change the timeout setting for cURL, which is 1 second by default.
There is method connectTimeOut implemented in CurlerRequest.php, but it has no interface in Client nor Http classes to make it possible to affect the connection timeout.

-

Поддержка NULL в экспорте данных через класс \ClickHouseDB\FormatLine

На данный момент тип NULL заменяется пустой строкой
'CSV'=>[ 'EnclosureArray'=>'"', 'EncodeEnclosure'=>'"', 'Enclosure'=>'"', 'Null'=>"", 'Delimiter'=>",", 'TabEncode'=>false, ],

думаю есть смысл изменить на \N так как ClickHouse поддерживает NULL
'CSV'=>[ 'EnclosureArray'=>'"', 'EncodeEnclosure'=>'"', 'Enclosure'=>'"', 'Null'=>"\N", 'Delimiter'=>",", 'TabEncode'=>false, ],

Hardcoded http protocol or necessary add https transport

I configured my clickhouse server http connection with ssl option, i.e. I try connect to https://localhose:8443/
it's fail like:
'Recv failure: Connection reset by peer'
https://github.com/smi2/phpClickHouse/blob/master/src/Transport/Http.php#L116

Escape values

How to escape values?
Found that \ClickHouseDB\Query\Degeneration\Bindings::process doesnt escape string values

if (is_string($value)) {
$valueSet = $value;
$valueSetText = "'" . $value . "'";
}

What the way to proper escape values to avoid sql injections?

executeAsync & simultaneousLimit

If $simultaneousLimit reached, some task freez without performing

Truncate table

Удалить все партиции таблицы на одной ноде

Inserting empty values

When calling Client->insert() with empty $values array, I believe it should not fail but pass. I propose to handle it via following condition but I'm wondering what to return 🤔

    /**
     * Insert Array
     *
     * @param mixed[][] $values
     * @param string[]  $columns
     * @return Statement
     * @throws Exception\TransportException
     */
    public function insert(string $table, $values, $columns = [])
    {
+       if (empty($values)) {
+           // What to return here?
+       }

Any idea?