README

phpClickHouse-laravel

最受欢迎的库的 Laravel 和 Lumen 适配器

https://github.com/smi2/phpClickHouse - 用于连接和执行查询
https://github.com/the-tinderbox/ClickhouseBuilder - 良好的查询构建器

特性

无依赖项，仅使用 Curl（支持 php >=8.0）

先决条件

PHP 8.0
Laravel/Lumen 7+
Clickhouse 服务器

安装

1. 使用 composer 安装

$ composer require glushkovds/phpclickhouse-laravel

2. 在您的 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,
],

然后修改您的 .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. 在您的 config/app.php (对于 Laravel 11+ 的 bootstrap/providers.php 文件) 的 providers 部分中添加服务提供者

\PhpClickHouseLaravel\ClickhouseServiceProvider::class,

它应该放置在 App\Providers\AppServiceProvider::class 和 App\Providers\EventServiceProvider::class 之前。

使用方法

您可以直接使用 smi2/phpClickHouse 的功能

/** @var \ClickHouseDB\Client $db */
$db = DB::connection('clickhouse')->getClient();
$statement = $db->select('SELECT * FROM summing_url_views LIMIT 2');

有关 $db 的更多信息，请参阅此处： https://github.com/smi2/phpClickHouse/blob/master/README.md

或者使用 Eloquent ORM 的功能（将完全实现）

1. 添加模型

<?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. 添加迁移

<?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. 然后您可以插入数据

单行

$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();

或批量插入

# 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. 现在查看查询构建器

$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();

已知问题

一些问题在此处描述.

高级使用

列转换

在插入之前，列将转换为在字段 $casts 中指定的所需数据类型。
此功能不适用于数据选择。
支持的转换类型是： 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],
]);

事件

事件的工作方式与 eloquent 模型事件相同
可用事件： creating，created，saved

重试

您可能需要启用在收到非 200 响应时重试请求的功能，可能是由于网络连接问题。

修改您的 .env 文件

CLICKHOUSE_RETRIES=2

retries 是可选的，默认值为 0。
0 表示只尝试一次。
1 表示一次尝试加上一个错误的重试（总共两次尝试）。

处理大量行

您可以使用 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";
        }
    });

插入查询的缓冲引擎

请参阅 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';
}

如果您还想从您的缓冲表中读取，请将其名称放在 $table 中

<?php

namespace App\Models\Clickhouse;

use PhpClickHouseLaravel\BaseModel;

class MyTable extends BaseModel
{
    protected $table = 'my_table_buffer';
}

OPTIMIZE 语句

请参阅 https://clickhouse.ac.cn/docs/ru/sql-reference/statements/optimize/

MyTable::optimize($final = false, $partition = null);

TRUNCATE 语句

从表中删除所有数据。

MyTable::truncate();

删除

请参阅 https://clickhouse.ac.cn/docs/en/sql-reference/statements/alter/delete/

MyTable::where('field_one', 123)->delete();

使用缓冲引擎并执行 OPTIMIZE 或 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';
}

更新

请参阅 https://clickhouse.ac.cn/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'])]]);

在项目中使用多个Clickhouse实例

1. 将第二个连接添加到您的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. 添加模型

<?php

namespace App\Models\Clickhouse;

use PhpClickHouseLaravel\BaseModel;

class MyTable2 extends BaseModel
{
    protected $connection = 'clickhouse2';
    
    protected $table = 'my_table2';
}

3. 添加迁移

<?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');
    }
};

集群模式

重要！

每个ClickHouse节点必须有一个数据库名称、登录名和密码。
对于读写操作，连接将建立到第一个可用的节点。
迁移将在所有节点上执行。如果其中一个节点不可用，迁移将抛出异常。

您的config/database.php应如下所示

'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,
],

迁移是

<?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');
    }
};

您可以获取当前节点的宿主和切换到下一个活动节点

$row = new MyTable();
echo $row->getThisClient()->getConnectHost();
// will print 'clickhouse01'
$row->resolveConnection()->getCluster()->slideNode();
echo $row->getThisClient()->getConnectHost();
// will print 'clickhouse02'

glushkovds / phpclickhouse-laravel

维护者

详细信息