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
No dependency, only Curl (support php >=7.1 )
More: https://github.com/smi2/phpClickHouse#features
- PHP 7.1, 8.0
- Laravel/Lumen 7+
- Clickhouse server
1. Install via composer
$ composer require glushkovds/phpclickhouse-laravel2. 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,
],
],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=true3. Add service provider into your config/app.php file providers section:
\PhpClickHouseLaravel\ClickhouseServiceProvider::class,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
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')
->getRows();Events work just like an eloquent model events
Available events: creating, created, saved
You may enable ability to retry requests while received not 200 response, maybe due network connectivity problems.
Patch your .env:
CLICKHOUSE_RETRIES=2retries is optional, default value is 0.
0 mean only one attempt.
1 mean one attempt + 1 retry while error (total 2 attempts).
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";
}
});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';
}See https://clickhouse.com/docs/ru/sql-reference/statements/optimize/
MyTable::optimize($final = false, $partition = null);Removes all data from a table.
MyTable::truncate();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';
}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')")]);// Array data type
MyTable::insertAssoc([[1, 'str', new InsertArray(['a','b'])]]);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,
],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');
}
};