the-tinderbox / clickhousebuilder Goto Github PK
View Code? Open in Web Editor NEWFluent queries builder for Clickhouse. Also has integration with Laravel / Lumen.
Fluent queries builder for Clickhouse. Also has integration with Laravel / Lumen.
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?
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()`
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.
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!
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
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()
Is it possible to set the session_id parameter?
Doesn't work with options array
'options' => [
'session_id' => "my_session_key"
]
Where:
https://github.com/the-tinderbox/ClickhouseBuilder/blob/master/src/Query/Enums/Operator.php
Solution:
public const NOT_EQUAL_TO = '<>';
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->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
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 @-
😭
When settings final on a From object as false, its being ignored in compile
How to make join on different column names?
select * from t1 all left join t2 on t1.v =t2.viewId;
Please add offset to limitBy and takeBy, this is one of the frequently used functions for me :)
thanks for the nice clickhouse package
DB::Exception: Unknown setting wait_end_of_query
I've check code in \Tinderbox\ClickhouseBuilder\Integrations\Laravel\Connection::assembleServer
I didn't find the code to set timeout
$builder->format() works only with formats in which each character in the name is in uppercase
Version 1.2.0
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
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?
is there any support mothod to update data?
Please add support for L7 and L8.
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
usersWHERE
id = '105' OR 1=1 OR id='105' LIMIT 50
output all results from the table
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?
"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)
Hi!
$ composer test
There were 2 errors:
ClickhouseBuilder/src/Integrations/Laravel/Connection.php:331
ClickhouseBuilder/tests/LaravelIntegrationTest.php:229
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.
Why is there no cross join
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 ?
Can generate a Join with on clause instead of USING?
It will be ok also by a raw expression.
Is it possibile?
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
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
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.