phpclickhouse-laravel

Migrations in this package extend \PhpClickHouseLaravel\Migration. You can write raw SQL or use the Schema Builder.

Raw SQL Migrations

Use static::write() to execute raw DDL statements.

class CreateMyTable extends \PhpClickHouseLaravel\Migration
{
    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)
        ");
    }

    public function down()
    {
        static::write('DROP TABLE my_table');
    }
}

Schema Builder Migrations

Use the createMergeTree method for a more programmatic approach.

use PhpClickHouseLaravel\MergeTree;
use PhpClickHouseLaravel\Expression;

class CreateMyTable extends \PhpClickHouseLaravel\Migration
{
    public function up()
    {
        static::createMergeTree('my_table', fn(MergeTree $table) => $table
            ->columns([
                $table->uInt32('id'),
                $table->datetime('created_at', 3)->default(new Expression('now64()')),
                $table->string('field_one'),
                $table->int32('field_two'),
            ])
            ->orderBy('id')
        );
    }

    public function down()
    {
        static::write('DROP TABLE my_table');
    }
}

<?php

class CreateMyTable extends \PhpClickHouseLaravel\Migration
{
    public function up()
    {
        static::createMergeTree('my_table', fn(\PhpClickHouseLaravel\MergeTree $table) => $table
            ->columns([
                $table->uInt32('id'),
                $table->datetime('created_at', 3)->default(new \PhpClickHouseLaravel\Expression('now64()')),
                $table->string('field_one'),
                $table->int32('field_two'),
            ])
            ->orderBy('id')
        );
    }

    public function down()
    {
        static::write('DROP TABLE my_table');
    }
}

The package provides a BaseModel to interact with ClickHouse tables using an Eloquent-like syntax.

1. Define a Model

Extend PhpClickHouseLaravel\BaseModel. The table name is resolved from the $table property (defaults to the snake_case version of the class name).

namespace App\Models\Clickhouse;

use PhpClickHouseLaravel\BaseModel;

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

2. Insert Data

Single Row Insertion:

// Using create
$model = MyTable::create(['model_name' => 'model 1', 'some_param' => 1]);

// Using make and save
$model = MyTable::make(['model_name' => 'model 1']);
$model->some_param = 1;
$model->save();

// Using new and fill
$model = new MyTable();
$model->fill(['model_name' => 'model 1', 'some_param' => 1])->save();

Bulk Insertion:

// Non-associative (positional values)
MyTable::insertBulk([['model 1', 1], ['model 2', 2]], ['model_name', 'some_param']);

// Associative (key-value pairs)
MyTable::insertAssoc([['model_name' => 'model 1', 'some_param' => 1], ['some_param' => 2, 'model_name' => 'model 2']]);

3. Query Data

Use a fluent interface to build queries. Use RawColumn for aggregate functions and getRows() to execute.

use PhpClickHouseLaravel\RawColumn;

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

The package automatically registers a clickhouse connection. You can configure it using environment variables in your .env file or by explicitly defining the connection in config/database.php to override defaults or add advanced options.

Environment Variables:

Manual Configuration in config/database.php:

'connections' => [
    'clickhouse' => [
        'driver' => 'clickhouse',
        'host' => env('CLICKHOUSE_HOST'),
        'port' => (int) 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', false),
        'retries' => env('CLICKHOUSE_RETRIES', 0),
        'settings' => [
            'max_partitions_per_insert_block' => 300,
        ],
        'fix_default_query_builder' => true,
    ],
],

The PhpClickHouseLaravel\Cluster class manages a collection of ClickHouse nodes, providing high availability by automatically selecting an active node during instantiation and allowing manual switching between nodes.

Initialization

When you instantiate Cluster, it iterates through the provided $nodeConfigs and attempts to connect to each node using ping(true). The first node that responds successfully becomes the activeNode. If no nodes are reachable, a TransportException is thrown.

Writing Data

The write() method executes a SQL statement across all configured nodes in the cluster. This is useful for ensuring data consistency across a distributed setup. It returns the last successful Statement object.

Node Management

• getActiveNode(): Returns the ClickHouseDB\Client instance for the currently active node. Use this for read operations or when you need direct access to the client.
• slideNode(): Attempts to switch the active node to the next available node in the configuration list. It iterates through the nodes and selects the first one that responds to a ping(). If there is only one node configured, this method does nothing.

use PhpClickHouseLaravel\Cluster;

$nodeConfigs = [
    [
        'host' => '127.0.0.1',
        'database' => 'default',
        'timeout_query' => 30,
        'timeout_connect' => 10,
    ],
    [
        'host' => '127.0.0.2',
        'database' => 'default',
        'timeout_query' => 30,
        'timeout_connect' => 10,
    ]
];

// Initializes and finds the first available node
$cluster = new Cluster($nodeConfigs);

// Writes to ALL nodes in the cluster
$cluster->write('INSERT INTO my_table (col1) VALUES (?)', ['value']);

// Switch to the next available node if the current one is problematic
$cluster->slideNode();

// Get the client for the current active node
$client = $cluster->getActiveNode();