lizhichao / one-ck Goto Github PK

View Code? Open in Web Editor NEW

70.0 8.0 11.0 104 KB

php tcp client for clickhouse

License: Apache License 2.0

PHP 100.00%

clickhouse-client php-clickhouse php clickhouse tcp-client php-tcp-client

one-ck's People

Contributors

Stargazers

Watchers

Forkers

chelseg mio-next woeoio panda-men lccbiluox2 372s vicsdf ariews ulv humanmade rabbit-php

one-ck's Issues

count()问题

关闭

Async queries

Is it possible to execute async queries using this lib?

Insert error "read from fail"

Hello.

I have some interesting error after call insert function.

Tables structure:

CREATE TABLE IF NOT EXISTS categories_replica ON CLUSTER '{cluster}' (
    id UInt64,
    value String,
    category String,
    group Nullable(String) DEFAULT NULL
) engine = ReplicatedMergeTree(
  '/clickhouse/{installation}/{cluster}/tables/{shard}/{database}/categories_replica',
  '{replica}'
) ORDER BY id

CREATE TABLE IF NOT EXISTS categories ON CLUSTER '{cluster}' AS categories_replica
ENGINE = Distributed('{cluster}', '{database}', categories_replica, rand())

Inserting data:

Array
(
    [0] => Array
        (
            [id] => 1
            [value] => 100
            [category] => banner_cluster
            [group] => NULL
        )
    [1] => Array
        (
            [id] => 2
            [value] => viber
            [category] => messenger
            [group] => viber
        )
    [2] => Array
        (
            [id] => 5
            [value] => normal
            [category] => body_type
            [group] => normal
        )
)

Php code:
$client->insert('categories', $data);

After call function insert data was inserted in ClickHouse, but function exit with error!

Error trace:

local.ERROR: read from fail {"exception":"[object] (OneCk\\CkException(code: 10001): read from fail at /application/vendor/lizhichao/one-ck/src/Read.php:21)
[stacktrace]
#0 /application/vendor/lizhichao/one-ck/src/Read.php(24): OneCk\\Read->getChar(16)
#1 /application/vendor/lizhichao/one-ck/src/Read.php(76): OneCk\\Read->getChar(73)
#2 /application/vendor/lizhichao/one-ck/src/Client.php(364): OneCk\\Read->string()
#3 /application/vendor/lizhichao/one-ck/src/Client.php(108): OneCk\\Client->readErr()
#4 /application/vendor/lizhichao/one-ck/src/Client.php(344): OneCk\\Client->receive()
#5 /application/vendor/lizhichao/one-ck/src/Client.php(272): OneCk\\Client->writeEnd()
#6 /application/vendor/illuminate/support/Facades/Facade.php(261): OneCk\\Client->insert('categories', Array)

Bug with aggregate function suffix

Hello.

I'm reproduce very interesting bug 😀

Create test_data table:

CREATE TABLE IF NOT EXISTS test_data (
  some_uuid UUID,
  entity_id UInt32,
  parameter_id UInt64,
  creation_ts SimpleAggregateFunction(max, DateTime64) DEFAULT NOW(),
  number SimpleAggregateFunction(sum, UInt64) DEFAULT 1
) engine = AggregatingMergeTree()
PARTITION BY toYYYYMM(creation_ts)
ORDER BY (some_uuid, entity_id, parameter_id);

Insert some data into test_data table to reproduce bug:

INSERT INTO test_data
VALUES ('b957bda7-c368-4f43-bc06-640fc6edc466', 4, 4, now(), 1),
('b957bda7-c368-4f43-bc06-640fc6edc466', 4, 7, now(), 1),
('b957bda7-c368-4f43-bc06-640fc6edc466', 5, 5, now(), 1),
('b957bda7-c368-4f43-bc06-640fc6edc466', 6, 6, now(), 1),
('b957bda7-c368-4f43-bc06-640fc6edc466', 5, 5, now(), 1),
('b957bda7-c368-4f43-bc06-640fc6edc466', 6, 6, now(), 1);

Then run query in one of DB viewer:

SELECT
    entity_id,
    parameter_id,
    sumOrNullIf(number, some_uuid NOT IN ('b957bda7-c368-4f43-bc06-640fc6edc466')) AS number_0,
    maxOrNullIf(creation_ts, some_uuid NOT IN ('b957bda7-c368-4f43-bc06-640fc6edc466')) as creation_ts_0,
    sumOrNullIf(number, some_uuid NOT IN ('c34b4e9f-690e-4f24-b67c-206242a4287c')) AS number_1,
    maxOrNullIf(creation_ts, some_uuid NOT IN ('c34b4e9f-690e-4f24-b67c-206242a4287c')) as creation_ts_1
FROM test_data
WHERE some_uuid IN ('b957bda7-c368-4f43-bc06-640fc6edc466','c34b4e9f-690e-4f24-b67c-206242a4287c')
GROUP BY entity_id, parameter_id
ORDER BY entity_id;

Query result:

When we execute this query by PHP library:

$sql = "SELECT
    entity_id,
    parameter_id,
    sumOrNullIf(number, some_uuid NOT IN ('b957bda7-c368-4f43-bc06-640fc6edc466')) AS number_0,
    maxOrNullIf(creation_ts, some_uuid NOT IN ('b957bda7-c368-4f43-bc06-640fc6edc466')) as creation_ts_0,
    sumOrNullIf(number, some_uuid NOT IN ('c34b4e9f-690e-4f24-b67c-206242a4287c')) AS number_1,
    maxOrNullIf(creation_ts, some_uuid NOT IN ('c34b4e9f-690e-4f24-b67c-206242a4287c')) as creation_ts_1
FROM test_data
WHERE some_uuid IN ('b957bda7-c368-4f43-bc06-640fc6edc466','c34b4e9f-690e-4f24-b67c-206242a4287c')
GROUP BY entity_id, parameter_id
ORDER BY entity_id";

$result = $client->query($sql);

var_dump($result);

You see this:

Array
(
    [0] => Array
        (
            [entity_id] => 4
            [parameter_id] => 4
            [number_0] => NULL
            [creation_ts_0] => NULL
            [number_1] => NULL
            [creation_ts_1] => NULL
        )
    [1] => Array
        (
            [entity_id] => 4
            [parameter_id] => 7
            [number_0] => NULL
            [creation_ts_0] => NULL
            [number_1] => NULL
            [creation_ts_1] => NULL
        )
    [2] => Array
        (
            [entity_id] => 5
            [parameter_id] => 5
            [number_0] => NULL
            [creation_ts_0] => NULL
            [number_1] => NULL
            [creation_ts_1] => NULL
        )
     [3] => Array
        (
            [entity_id] => 6
            [parameter_id] => 6
            [number_0] => NULL
            [creation_ts_0] => NULL
            [number_1] => NULL
            [creation_ts_1] => NULL
        )
)

Information about number_0, creation_ts_0, number_1, creation_ts_1 always NULL.
I found why this error!
It's because in aggregate function I use suffix OrNull.

ClickHouse version: 20.8.12.2

$this->conn is not a valid stream resource

script:

$ cat run.php 
<?php

require __DIR__.'/vendor/autoload.php';

$ck = new \OneCk\Client('tcp://localhost:9000', 'default', '', 'switch');

$ck->__destruct();

$ php run.php 
PHP Warning:  stream_socket_shutdown(): supplied resource is not a valid stream resource in /home/arie/works/onck/vendor/lizhichao/one-ck/src/Client.php on line 81
PHP Warning:  fclose(): supplied resource is not a valid stream resource in /home/arie/works/onck/vendor/lizhichao/one-ck/src/Client.php on line 82

environments:

$ php -v
PHP 7.4.28 (cli) (built: Feb 26 2022 06:29:20) ( NTS )
Copyright (c) The PHP Group
Zend Engine v3.4.0, Copyright (c) Zend Technologies
    with Zend OPcache v7.4.28, Copyright (c), by Zend Technologies

$ grep 'one-ck' composer.json 
        "lizhichao/one-ck": "^0.2.9

Support Bool type

On this time Bool type isn't supported in your library.

Do You plan to add support Bool type in future?

It's very useful and often used type 🙂

Reconnect algorithm

Hello!

I have some case when I got exception Broken pipe.

I use this library in demonic process (background task, runtime worker and etc).

After connection timed out I got error:

ErrorException(code: 0): fwrite(): send of 8192 bytes failed with errno=32 Broken pipe at /code/vendor/lizhichao/one-ck/src/Write.php:82

Maybe need algorithm of reconnect broken connection.

What do you mint about that?

Possibility to set connection timeout and socket timeout

I think it's would be great set timeouts and some other connection settings like persistent connection or tcp no delay.

My example:

use OneCk\Client;

$client = new Client(
    'tcp://localhost:9000',
    'username',
    'password',
    'database',
    [
        'connect_timeout' => 3,
        'socket_timeout'  => 30,
        'tcp_nodelay'     => true,
        'persistent'      => true,
    ]
);

Support for parameterised queries

Hi, thanks for publishing this library, it looks really promising and straightforward to use.

I currently have an implementation using the HTTP interface which lets me do parameterised queries like so:

# query not URL encoded for ease of reading
http://localhost:8123/?query=select * from t where event_type = {type:String};&param_type=click

Would it be possible to introduce this feature? I know there are encoding functions but it would be good to make ClickHouse do the work, I'm just not sure how.

Possible interface:

$client->query( 'select * from t where event_type = {type:String}', [
    'type' => 'click',
] )

Support SimpleAggregateFunction column type

Hello.

I have a table:

CREATE TABLE IF NOT EXISTS table_name_replica on cluster '{cluster}'
(
    uuid         UUID,
    entity_id    UInt32,
    parameter_id UInt64,
    creation_ts  SimpleAggregateFunction(max, DateTime64) DEFAULT NOW(),
    number       SimpleAggregateFunction(sum, UInt64) DEFAULT 1
) engine = ReplicatedAggregatingMergeTree(
    '/clickhouse/{installation}/{cluster}/tables/{shard}/{database}/table_name_replica',
    '{replica}'
)
PARTITION BY toYYYYMM(creation_ts)
ORDER BY (uuid, entity_id, parameter_id)

When I run query:

$ck->query('SELECT * FROM table_name_replica');

I have an error:

OneCk\CkException:
not supported type :simpleaggregatefunction(sum, uint64)

  at /application/vendor/lizhichao/one-ck/src/Types.php:546
  at OneCk\Types->decode('simpleaggregatefunction(sum, uint64)', 2)
     (/application/vendor/lizhichao/one-ck/src/Types.php:604)
  at OneCk\Types->unpack('simpleaggregatefunction(sum, uint64)', 2)
     (/application/vendor/lizhichao/one-ck/src/Client.php:182)
  at OneCk\Client->readData()
     (/application/vendor/lizhichao/one-ck/src/Client.php:111)
  at OneCk\Client->receive()
     (/application/vendor/lizhichao/one-ck/src/Client.php:344)
  at OneCk\Client->writeEnd()
     (/application/vendor/lizhichao/one-ck/src/Client.php:259)
  at OneCk\Client->query('SELECT * FROM table_name_replica')

Can you provide support Clickhouse SimpleAggregateFunctions?

Bug with hash function sipHash64

Hello.

I have bug with function sipHash64.

$sql = "SELECT sipHash64(toString('1ace54')) AS result";

$result = $client->query($sql);

var_dump($result);

You see this:

Array
(
    [0] => Array
        (
            [result] => -8921094594927452419
        )
)

Then run query in one of DB viewer:

This bug happens because there are numbers in the input string.

ClickHouse version: 20.8.18.32

Close connection

Hello.

In my project I use this library for high load (~ 800-1000 rps) work with ClickHouse.

Sometimes open connections in ClickHouse increase, which leads to errors:

stream_socket_client(): unable to connect to tcp://clickhouse.services:9000 (Connection timed out).

This happened because connections don't closed after using, they closed by ClickHouse after tcp_keep_alive_timeout.

I'm propose forcibly close connection after use.

Something like this:

class Client
{
...
public function __destruct()
{
   fclose($this->conn);
}
...
}

What you mind about this?

lizhichao / one-ck Goto Github PK

one-ck's People

Contributors

Stargazers

Watchers

Forkers

one-ck's Issues

Recommend Projects

Recommend Topics

Recommend Org