Phpclickhouse Laravel Package

Getting Started

Minimal Setup

1. Installation:

   composer require smi2/phpclickhouse
   

2. Basic Connection (in config/clickhouse.php or service provider):

   $client = new \ClickHouseDB\Client([
       'host'     => env('CLICKHOUSE_HOST', '127.0.0.1'),
       'port'     => env('CLICKHOUSE_PORT', 8123),
       'username' => env('CLICKHOUSE_USER', 'default'),
       'password' => env('CLICKHOUSE_PASSWORD', ''),
       'database' => env('CLICKHOUSE_DB', 'default'),
   ]);
   

3. First Query (in a controller or service):

   $users = $client->select('SELECT * FROM users WHERE id = :id', ['id' => 1]);
   return $users->fetchOne(); // Returns first row as associative array
   

First Use Case: Laravel Eloquent Integration

Create a custom query builder or repository:

// app/Repositories/ClickHouseRepository.php
class ClickHouseRepository
{
    protected $client;

    public function __construct()
    {
        $this->client = new \ClickHouseDB\Client(config('clickhouse'));
    }

    public function findById($id)
    {
        return $this->client->select('SELECT * FROM users WHERE id = :id', ['id' => $id])
            ->fetchOne();
    }
}

Implementation Patterns

1. Repository Pattern

// app/Repositories/UserRepository.php
class UserRepository
{
    protected $client;

    public function __construct(\ClickHouseDB\Client $client)
    {
        $this->client = $client;
    }

    public function findActiveUsers()
    {
        return $this->client->selectGenerator(
            'SELECT * FROM users WHERE is_active = 1'
        );
    }

    public function bulkInsert(array $users)
    {
        $columns = ['id', 'name', 'email', 'created_at'];
        return $this->client->insert('users', $users, $columns);
    }
}

2. Query Builder Facade

// app/Facades/ClickHouse.php
class ClickHouse extends \Illuminate\Support\Facades\Facade
{
    protected static function getFacadeAccessor()
    {
        return 'clickhouse.client';
    }
}

// Register in AppServiceProvider
public function register()
{
    $this->app->singleton('clickhouse.client', function () {
        return new \ClickHouseDB\Client(config('clickhouse'));
    });
}

3. Async Operations with Laravel Queues

// app/Jobs/ProcessClickHouseData.php
class ProcessClickHouseData implements ShouldQueue
{
    protected $client;

    public function __construct(\ClickHouseDB\Client $client)
    {
        $this->client = $client;
    }

    public function handle()
    {
        $statement = $this->client->selectAsync(
            'SELECT * FROM large_table WHERE processed = 0'
        );

        foreach ($statement as $row) {
            // Process row
            $this->processRow($row);
        }
    }
}

4. Model Events with ClickHouse

// app/Models/User.php
class User extends Model
{
    protected static function booted()
    {
        static::saved(function ($user) {
            $client = app('clickhouse.client');
            $client->insert('user_activity', [
                [time(), $user->id, 'created']
            ], ['timestamp', 'user_id', 'action']);
        });
    }
}

5. Streaming Data Processing

// app/Services/DataStreamProcessor.php
class DataStreamProcessor
{
    public function processStream($filePath)
    {
        $client = new \ClickHouseDB\Client(config('clickhouse'));

        $client->streamWrite(
            'logs',
            $filePath,
            ['timestamp', 'level', 'message'],
            \ClickHouseDB\Query\StreamWrite::FORMAT_TSV
        );
    }
}

Gotchas and Tips

Common Pitfalls

1. Type Mismatches:

   • Always specify types for native parameters: {id:UInt64} instead of :id
   • ClickHouse will reject queries with type mismatches at the protocol level

2. Memory Issues with Large Results:

   • Use selectGenerator() instead of rows() for large datasets
   • Example: foreach ($client->selectGenerator('SELECT * FROM huge_table') as $row) { ... }

3. Connection Timeouts:

   • Set appropriate timeouts: $client->setTimeout(30) and $client->setConnectTimeOut(10)
   • For production, consider implementing retry logic with exponential backoff

4. Async Query Handling:

   • Async queries return immediately but need proper iteration:

   $statement = $client->selectAsync('SELECT * FROM large_table');
   foreach ($statement as $row) { /* process */ }
   

5. Cluster Awareness:

   • For multi-node setups, use the cluster configuration:

   $client = new \ClickHouseDB\Client([
       'cluster' => [
           'nodes' => ['node1:8123', 'node2:8123'],
           'replica' => 'replica1'
       ]
   ]);
   

Debugging Tips

1. Enable Query Logging:

   $client->setDebug(true);
   // View last query with: $client->getLastQuery()
   

2. Progress Tracking:

   $client->select(
       'SELECT * FROM large_table',
       [],
       null,
       function ($progress) {
           logger()->info("Progress: {$progress->rows} rows processed");
       }
   );
   

3. Error Handling:

   try {
       $result = $client->select('SELECT * FROM nonexistent_table');
   } catch (\ClickHouseDB\Exception\ClickHouseException $e) {
       logger()->error("ClickHouse Error: {$e->getMessage()}");
       logger()->error("Query ID: {$e->getQueryId()}");
   }
   

Performance Optimization

1. Bulk Inserts:

   // Insert 1000 rows in a single request
   $data = [];
   for ($i = 0; $i < 1000; $i++) {
       $data[] = [time(), "key$i", rand(1, 100)];
   }
   $client->insert('metrics', $data, ['timestamp', 'key', 'value']);
   

2. Compression:

   $client->setCompression(true); // Enable gzip compression
   

3. Batch Processing:

   // Process in batches of 1000
   $offset = 0;
   $batchSize = 1000;
   do {
       $result = $client->select(
           "SELECT * FROM large_table LIMIT {$batchSize} OFFSET {$offset}"
       );
       $rows = $result->rows();
       // Process $rows
       $offset += $batchSize;
   } while (!empty($rows));
   

Configuration Quirks

1. Database Switching:

   • Set database once at connection or per-query:

   $client->database('analytics'); // Set default
   // OR
   $client->select('SELECT * FROM analytics.table', [], [], [], 'analytics');
   

2. Authentication Methods:

   • Supported methods: none, header, basic auth, query string
   • Configure in client constructor:

   $client = new \ClickHouseDB\Client([
       'auth' => [
           'method' => 'header',
           'header' => 'X-ClickHouse-User: admin'
       ]
   ]);
   

3. SSL Configuration:

   $client = new \ClickHouseDB\Client([
       'ssl' => [
           'verify_peer' => true,
           'cafile' => '/path/to/ca.pem',
           'local_cert' => '/path/to/client.crt',
           'local_pk' => '/path/to/client.key'
       ]
   ]);
   

Extension Points

1. Custom Query Transformations:

   // Implement custom degeneration for complex conditions
   $client->addDegenerator('ifempty', function ($value) {
       return empty($value) ? 'IS NULL' : 'IS NOT NULL';
   });
   
   // Usage in query:
   $client->select('SELECT * FROM table WHERE field {ifempty:condition}');
   

2. Custom Type Handlers:

   // Register custom type mapping
   $client->addTypeHandler('CustomType', function ($value) {
       return ['type' => 'CustomType', 'value' => $value];
   });
   

3. Event Listeners:

   // Listen to query execution
   $client->addListener('query.execute', function ($event) {
       logger()->debug("Executing query: {$event->getQuery()}");
   });