glushkovds / phpclickhouse-laravel
Adapter of the most popular library https://github.com/smi2/phpClickHouse to Laravel
Installs: 300 751
Dependents: 1
Suggesters: 0
Security: 0
Stars: 138
Watchers: 3
Forks: 26
Open Issues: 0
Requires
- php: >=8.0
- illuminate/database: >=7
- illuminate/support: >=7
- smi2/phpclickhouse: ^1.4.2
- the-tinderbox/clickhouse-builder: ^6.0
Requires (Dev)
- laravel/framework: >=9
- phpunit/phpunit: ^9
- dev-master
- v2.1.1
- v2.1.0
- v2.0.0
- v1.x-dev
- v1.20.0
- v1.19.3
- v1.19.2
- v1.19.1
- v1.19.0
- v1.18.1
- v1.18.0
- v1.17.2
- v1.17.1
- v1.17.0
- v1.16.4
- v1.16.3
- v1.16.2
- v1.16.1
- v1.16.0
- v1.15.4
- v1.15.3
- v1.15.2
- v1.15.1
- v1.15.0
- v1.14.2
- v1.14.1
- v1.14.0
- v1.12.1
- v1.12.0
- v1.11.0
- v1.10.0
- v1.9.0
- v1.8.0
- v1.7.0
- v1.6.0
- v1.5.4
- v1.5.3
- v1.5.2
- v1.5.1
- v1.5.0
- v1.4.0
- v1.3.1
- v1.3.0
- v1.2.0
- v1.1.0
- v1.0.2
- v1.0.1
- v1.0.0
This package is auto-updated.
Last update: 2024-10-13 08:51:14 UTC
README
phpClickHouse-laravel
Adapter to Laravel and Lumen of the most popular libraries:
- https://github.com/smi2/phpClickHouse - for connections and perform queries
- https://github.com/the-tinderbox/ClickhouseBuilder - good query builder
Features
No dependency, only Curl (support php >=8.0 )
More: https://github.com/smi2/phpClickHouse#features
Prerequisites
- PHP 8.0
- Laravel/Lumen 7+
- Clickhouse server
Installation
1. Install via composer
$ composer require glushkovds/phpclickhouse-laravel
2. Add new connection into your config/database.php:
'clickhouse' => [ 'driver' => 'clickhouse', 'host' => env('CLICKHOUSE_HOST'), 'port' => env('CLICKHOUSE_PORT','8123'), 'database' => env('CLICKHOUSE_DATABASE','default'), 'username' => env('CLICKHOUSE_USERNAME','default'), 'password' => env('CLICKHOUSE_PASSWORD',''), 'timeout_connect' => env('CLICKHOUSE_TIMEOUT_CONNECT',2), 'timeout_query' => env('CLICKHOUSE_TIMEOUT_QUERY',2), 'https' => (bool)env('CLICKHOUSE_HTTPS', null), 'retries' => env('CLICKHOUSE_RETRIES', 0), 'settings' => [ // optional 'max_partitions_per_insert_block' => 300, ], 'fix_default_query_builder' => true, ],
Then patch your .env:
CLICKHOUSE_HOST=localhost CLICKHOUSE_PORT=8123 CLICKHOUSE_DATABASE=default CLICKHOUSE_USERNAME=default CLICKHOUSE_PASSWORD= CLICKHOUSE_TIMEOUT_CONNECT=2 CLICKHOUSE_TIMEOUT_QUERY=2 # only if you use https connection CLICKHOUSE_HTTPS=true
3. Add service provider into your config/app.php (bootstrap/providers.php for Laravel 11+) file providers section:
\PhpClickHouseLaravel\ClickhouseServiceProvider::class,
It should be placed before App\Providers\AppServiceProvider::class, and App\Providers\EventServiceProvider::class.
Usage
You can use smi2/phpClickHouse functionality directly:
/** @var \ClickHouseDB\Client $db */ $db = DB::connection('clickhouse')->getClient(); $statement = $db->select('SELECT * FROM summing_url_views LIMIT 2');
More about $db
see here: https://github.com/smi2/phpClickHouse/blob/master/README.md
Or use dawnings of Eloquent ORM (will be implemented completely)
1. Add model
<?php namespace App\Models\Clickhouse; use PhpClickHouseLaravel\BaseModel; class MyTable extends BaseModel { // Not necessary. Can be obtained from class name MyTable => my_table protected $table = 'my_table'; }
2. Add migration
<?php class CreateMyTable extends \PhpClickHouseLaravel\Migration { /** * Run the migrations. * * @return void */ public function up() { static::write(' CREATE TABLE my_table ( id UInt32, created_at DateTime, field_one String, field_two Int32 ) ENGINE = MergeTree() ORDER BY (id) '); } /** * Reverse the migrations. * * @return void */ public function down() { static::write('DROP TABLE my_table'); } }
3. And then you can insert data
One row
$model = MyTable::create(['model_name' => 'model 1', 'some_param' => 1]); # or $model = MyTable::make(['model_name' => 'model 1']); $model->some_param = 1; $model->save(); # or $model = new MyTable(); $model->fill(['model_name' => 'model 1', 'some_param' => 1])->save();
Or bulk insert
# Non assoc way MyTable::insertBulk([['model 1', 1], ['model 2', 2]], ['model_name', 'some_param']); # Assoc way MyTable::insertAssoc([['model_name' => 'model 1', 'some_param' => 1], ['some_param' => 2, 'model_name' => 'model 2']]);
4. Now check out the query builder
$rows = MyTable::select(['field_one', new RawColumn('sum(field_two)', 'field_two_sum')]) ->where('created_at', '>', '2020-09-14 12:47:29') ->groupBy('field_one') ->settings(['max_threads' => 3]) ->getRows();
Known issues
Some of the problems are described here.
Advanced usage
Columns casting
Before insertion, the column will be converted to the required data type specified in the field $casts
.
This feature does not apply to data selection.
The supported cast types are: boolean
.
namespace App\Models\Clickhouse; use PhpClickHouseLaravel\BaseModel; class MyTable extends BaseModel { /** * The columns that should be cast. * * @var array */ protected $casts = ['some_bool_column' => 'boolean']; } // Then you can insert the data like this: MyTable::insertAssoc([ ['some_param' => 1, 'some_bool_column' => false], ]);
Events
Events work just like an eloquent model events
Available events: creating, created, saved
Retries
You may enable ability to retry requests while received not 200 response, maybe due network connectivity problems.
Patch your .env:
CLICKHOUSE_RETRIES=2
retries is optional, default value is 0.
0 mean only one attempt.
1 mean one attempt + 1 retry while error (total 2 attempts).
Working with huge rows
You can chunk results like in Laravel
// Split the result into chunks of 30 rows $rows = MyTable::select(['field_one', 'field_two']) ->chunk(30, function ($rows) { foreach ($rows as $row) { echo $row['field_two'] . "\n"; } });
Buffer engine for insert queries
See https://clickhouse.tech/docs/en/engines/table-engines/special/buffer/
<?php namespace App\Models\Clickhouse; use PhpClickHouseLaravel\BaseModel; class MyTable extends BaseModel { // Not necessary. Can be obtained from class name MyTable => my_table protected $table = 'my_table'; // All inserts will be in the table $tableForInserts // But all selects will be from $table protected $tableForInserts = 'my_table_buffer'; }
If you also want to read from your buffer table, put its name in $table
<?php namespace App\Models\Clickhouse; use PhpClickHouseLaravel\BaseModel; class MyTable extends BaseModel { protected $table = 'my_table_buffer'; }
OPTIMIZE Statement
See https://clickhouse.com/docs/ru/sql-reference/statements/optimize/
MyTable::optimize($final = false, $partition = null);
TRUNCATE Statement
Removes all data from a table.
MyTable::truncate();
Deletions
See https://clickhouse.com/docs/en/sql-reference/statements/alter/delete/
MyTable::where('field_one', 123)->delete();
Using buffer engine and performing OPTIMIZE or ALTER TABLE DELETE
<?php namespace App\Models\Clickhouse; use PhpClickHouseLaravel\BaseModel; class MyTable extends BaseModel { // All SELECT's and INSERT's on $table protected $table = 'my_table_buffer'; // OPTIMIZE and DELETE on $tableSources protected $tableSources = 'my_table'; }
Updates
See https://clickhouse.com/docs/ru/sql-reference/statements/alter/update/
MyTable::where('field_one', 123)->update(['field_two' => 'new_val']); // or expression MyTable::where('field_one', 123) ->update(['field_two' => new RawColumn("concat(field_two,'new_val')")]);
Helpers for inserting different data types
// Array data type MyTable::insertAssoc([[1, 'str', new InsertArray(['a','b'])]]);
Working with multiple Clickhouse instances in a project
1. Add second connection into your config/database.php:
'clickhouse2' => [ 'driver' => 'clickhouse', 'host' => 'clickhouse2', 'port' => '8123', 'database' => 'default', 'username' => 'default', 'password' => '', 'timeout_connect' => 2, 'timeout_query' => 2, 'https' => false, 'retries' => 0, 'fix_default_query_builder' => true, ],
2. Add model
<?php namespace App\Models\Clickhouse; use PhpClickHouseLaravel\BaseModel; class MyTable2 extends BaseModel { protected $connection = 'clickhouse2'; protected $table = 'my_table2'; }
3. Add migration
<?php return new class extends \PhpClickHouseLaravel\Migration { protected $connection = 'clickhouse2'; public function up() { static::write('CREATE TABLE my_table2 ...'); } public function down() { static::write('DROP TABLE my_table2'); } };
Cluster mode
Important!
- Each ClickHouse node must have one database name and login and password.
- For reading and writing, the connection is made to the first available node.
- Migrations executes on all nodes. If one of the nodes is unavailable, the migration will throw an exception.
Your config/database.php should look like:
'clickhouse' => [ 'driver' => 'clickhouse', 'cluster' => [ [ 'host' => 'clickhouse01', 'port' => '8123', ], [ 'host' => 'clickhouse02', 'port' => '8123', ], ], 'database' => env('CLICKHOUSE_DATABASE','default'), 'username' => env('CLICKHOUSE_USERNAME','default'), 'password' => env('CLICKHOUSE_PASSWORD',''), 'timeout_connect' => env('CLICKHOUSE_TIMEOUT_CONNECT',2), 'timeout_query' => env('CLICKHOUSE_TIMEOUT_QUERY',2), 'https' => (bool)env('CLICKHOUSE_HTTPS', null), 'retries' => env('CLICKHOUSE_RETRIES', 0), 'settings' => [ // optional 'max_partitions_per_insert_block' => 300, ], 'fix_default_query_builder' => true, ],
Migration is:
<?php return new class extends \PhpClickHouseLaravel\Migration { /** * Run the migrations. * * @return void */ public function up() { static::write(' CREATE TABLE my_table ( id UInt32, created_at DateTime, field_one String, field_two Int32 ) ENGINE = ReplicatedMergeTree('/clickhouse/tables/default.my_table', '{replica}') ORDER BY (id) '); } /** * Reverse the migrations. * * @return void */ public function down() { static::write('DROP TABLE my_table'); } };
You can get the host of the current node and switch the active connection to the next node:
$row = new MyTable(); echo $row->getThisClient()->getConnectHost(); // will print 'clickhouse01' $row->resolveConnection()->getCluster()->slideNode(); echo $row->getThisClient()->getConnectHost(); // will print 'clickhouse02'