the-tinderbox / clickhousebuilder Goto Github PK

Clickhouse Server : v19.14.3.3

Error:

Old ANY INNER|RIGHT|FULL JOINs are disabled by default. Their logic would be changed. Old logic is many-to-one for all kinds of ANY JOINs. It's equil to apply distinct for right table keys. Default bahaviour is reserved for many-to-one LEFT JOIN, one-to-many RIGHT JOIN and one-to-one INNER JOIN. It would be equal to apply distinct for keys to right, left and both tables respectively. Set any_join_distinct_right_table_keys=1 to enable old bahaviour..

OLD: with ANY ( error )

SELECT 
	cart_id,
	product_id,
	added_time,
	paid_time
FROM 
	carts
ANY INNER JOIN 
	checkouts
USING 
	cart_id, product_id

NEW: without ANY ( success )

SELECT 
	cart_id,
	product_id,
	added_time,
	paid_time
FROM 
	carts
INNER JOIN 
	checkouts
USING 
	cart_id, product_id

...->join('table_name', '', 'inner', ['column'])...

EXCEPTION:

Value '' is not part of the enum Tinderbox\ClickhouseBuilder\Query\Enums\JoinStrict

Please add support for L7 and L8.

Hi!

$ composer test

There were 2 errors:

1. Tinderbox\ClickhouseBuilder\LaravelIntegrationTest::test_connection_select_async
   array_combine(): Both parameters should have an equal number of elements

ClickhouseBuilder/src/Integrations/Laravel/Connection.php:331
ClickhouseBuilder/tests/LaravelIntegrationTest.php:229

2. Tinderbox\ClickhouseBuilder\LaravelIntegrationTest::test_builder_async_get
   array_combine(): Both parameters should have an equal number of elements

ClickhouseBuilder/src/Integrations/Laravel/Connection.php:331
ClickhouseBuilder/src/Integrations/Laravel/Builder.php:44
ClickhouseBuilder/tests/LaravelIntegrationTest.php:358

ERRORS!
Tests: 91, Assertions: 360, Errors: 2.

DB::Exception: Unknown setting wait_end_of_query

😭

"the-tinderbox/clickhouse-builder": "^2.3"

Declaration of Tinderbox\\ClickhouseBuilder\\Integrations\\Laravel\\Connection::table($table) must be compatible with Illuminate\\Database\\Connection::table($table, $as = NULL)

Can generate a Join with on clause instead of USING?
It will be ok also by a raw expression.
Is it possibile?

Hello!

Now, where I want create table on cluster with this code:

$this->builder
            ->onCluster('test')
            ->createTableIfNotExists('test', 'ReplicatedMergeTree ORDER BY number', [
                'number' => 'UInt64',
            ]);

I have error, because suffix ON CLUSTER test can't added.

This also applies to the DROP TABLE query.

I'm using Uint64 in my where clause. In php I should use string for this type. In your wrap method is_string check goes before is_numeric. And all queries makes slower with string equality.
For example:
WHERE item_id = "1241214"
much slower than
WHERE item_id = 1241214

in http interface , the password can place in url with paramater

$ echo 'SELECT 1' | curl 'http://localhost:8123/?user=user&password=password' -d @-

however , the password and username will be exposed in log system when the log system will log the uri .
so , it should support login by place user and password in header instead of url

the example is blow
the user in url replace by X-ClickHouse-User in header and password in url replace by X-ClickHouse-Key in header

$ echo 'SELECT 1' | curl -H 'X-ClickHouse-User: user' -H 'X-ClickHouse-Key: password' 'http://localhost:8123/' -d @-

relative

$builder->from('table')->join(function ($query) {
    $query->select('column1', 'column2')->from('table2');
}, 'any', 'left', ['column1', 'column2']);

The above code execution error!!!

Fatal error: Uncaught Error: Call to undefined method Tinderbox\ClickhouseBuilder\Query\JoinClause::select() in /Users/jiao/ProjectPhp/test/TestCk/app/1.php:25

Where:
https://github.com/the-tinderbox/ClickhouseBuilder/blob/master/src/Query/Enums/Operator.php
Solution:
public const NOT_EQUAL_TO = '<>';

PHP 8 is officially released. It will be very nice to fix php version for this package.
I believe there will be no backward compatibility issues...
Thanks!

This package's tap function (https://github.com/the-tinderbox/ClickhouseBuilder/blob/master/src/functions.php#L13) is not compatible with Laravel's tap function (https://github.com/laravel/framework/blob/9.x/src/Illuminate/Support/helpers.php#L300) which causes errors in Laravel if the tap function from this package is registered before Laravel's version (The inverse works fine since this package always sends a callback to the function)

Error example:

ArgumentCountError 

  Too few arguments to function tap(), 1 passed in /application/vendor/laravel/framework/src/Illuminate/Support/Facades/Storage.php on line 74 and exactly 2 expected

  at vendor/the-tinderbox/clickhouse-builder/src/functions.php:13
      9▕      * @param callable $callback
     10▕      *
     11▕      * @return mixed
     12▕      */
  ➜  13▕     function tap($value, $callback)
     14▕     {
     15▕         $callback($value);
     16▕ 
     17▕         return $value;

      +20 vendor frames 
  21  artisan:37
      Illuminate\Foundation\Console\Kernel::handle()

I've check code in \Tinderbox\ClickhouseBuilder\Integrations\Laravel\Connection::assembleServer
I didn't find the code to set timeout

is there any support mothod to update data?

Is it possible to set the session_id parameter?
Doesn't work with options array

'options' => [
    'session_id' => "my_session_key"
]

When settings final on a From object as false, its being ignored in compile

I can't understand how to use aggregate function in select.

For example:

->select("sum(click_cnt) as click_cnt, zone_id")

or this

->select("sum(click_cnt) as click_cnt","zone_id")

converts to

`sum(click_cnt)` as `click_cnt`, `zone_id`

But i want:

converts to sum(`click_cnt`) as `click_cnt`, `zone_id`

How to solve this case?

my code

$test =  DB::connection('clickhouse')
            ->table('users')
            ->where('id', "105' OR 1=1 OR id='105")
            ->take(50);
die($test->toSql());

output:
SELECT * FROM usersWHEREid = '105' OR 1=1 OR id='105' LIMIT 50

output all results from the table

https://www.w3schools.com/sql/sql_injection.asp

Please add offset to limitBy and takeBy, this is one of the frequently used functions for me :)
thanks for the nice clickhouse package

Why is there no cross join

sometimes I have an error and log throw with authentication. I don't want the password to appear in the log file.

I see package https://github.com/smi2/phpClickHouse has AUTH_METHOD_HEADER
Is this package has a function to support it?

[2021-06-10 08:58:13] production.ERROR: cURL error 7: Failed to connect to clickhouse-host: Connection refused (see https://curl.haxx.se/libcurl/c/libcurl-errors.html) for https://clickhouse-host?wait_end_of_query=1&database=database&user=user&password=password{"exception":"[object] (GuzzleHttp\\Exception\\ConnectException(code: 0): cURL error 7: Failed to connect to clickhouse-host: Connection refused (see https://curl.haxx.se/libcurl/c/libcurl-errors.html) for https://clickhouse-host?wait_end_of_query=1&database=database&user=user&password=password at /var/www/vendor/guzzlehttp/guzzle/src/Handler/CurlFactory.php:210)

$builder->format() works only with formats in which each character in the name is in uppercase
Version 1.2.0

Hi,

Great work on the code base, its difficult to find any laravel implementations with clickhouse.

Would it be possible to support the Builder method offset?

EG:

DB::table()->where()->limit()->offset()->get();

Laravel supports this by default in their query builder

Method Tinderbox\ClickhouseBuilder\Integrations\Laravel\Builder::offset does not exist.

Thanks

When trying to do

$builder->join(function (JoinClause $join) {
            $join->query()
                ->select('main_cpm')
                ->table('campaigns')
                ->on('campaigns.campaign_id', '=', 'user_event_agr.campaign_id');
        })

I get the following

   ` Call to undefined method PhpClickHouseLaravel\Builder::newQuery()`

I would like to fork and modify this repo to better suit my needs but seems like I can not legaly modify and use your code without any open source licence. Can you please licence this repo or make it clear that you are not offering ony license?

https://choosealicense.com/no-permission/

Could you please explain if I run the query:
DB::connection('clickhouse')->select(raw('select count(*) as summary from table'));
I get the result like this
array:1 [
0 => array:1 [
"summary" => "4309900"
]
]

Why "summary" get "4309900" as string, not a number ?

How to make join on different column names?
select * from t1 all left join t2 on t1.v =t2.viewId;

In your composer.json you have "the-tinderbox/clickhouse-php-client": "^1.0" which now update to 1.0.12 (minor!!!) version and require 4 parameters (before was 3) in this class:

the-tinderbox/clickhouse-php-client/src/Query/QueryStatistic.php on line 56

So, maybe you update your library for new code of library or bind version of this requirement library to 1.0.10?

Tinderbox\Clickhouse\Exceptions\TransportException : Host [] returned error: Code: 27, e.displayText() = DB::Exception: Cannot parse input: expected ( before: FORMAT JSON: (at row 2)

"laravel/framework": "5.7.*",
"php": "^7.1.3",

my try
DB::connection('clickhouse')->using('XXX')->select("INSERT INTO db.table (pid, price, timestamp) VALUES (1, 2, 3)");

I run this in tabix "INSERT INTO db.table (pid, price, timestamp) VALUES (1, 2, 3)" and it was executed successfully!

if i try
DB::connection('clickhouse')->using('XXX')->select("ISELECT pid, price, timestampFROM db.table"); and it was executed successfully but INSERT return error