Clickhouse Php Client Laravel Package

Getting Started

Minimal Setup

1. Installation

   composer require bavix/clickhouse-php-client
   

   Ensure your composer.json includes "minimum-stability": "dev" if using unreleased versions.

2. Basic Connection

   use Bavix\ClickHouse\Client;
   
   $client = new Client('http://localhost:8123');
   

   For secure connections (HTTPS), pass the full URL:

   $client = new Client('https://clickhouse.example.com');
   

3. First Query

   $result = $client->query('SELECT 1 + 1 AS result');
   $row = $result->fetchAssoc();
   // Output: ['result' => 2]
   

4. Key Files

   • src/Client.php: Core client logic.
   • src/Query.php: Query builder and execution.
   • src/Exception/ClickHouseException.php: Error handling.

Implementation Patterns

Workflows

1. Query Execution

• Simple Queries

  $result = $client->query('SELECT * FROM users WHERE id = {id}', ['id' => 1]);
  

  Use parameter binding to avoid SQL injection.

• Batch Queries

  $client->batch([
      'SELECT 1',
      'SELECT 2',
  ])->execute();
  

• Async Queries

  $promise = $client->queryAsync('SELECT sleep(1)');
  $result = $promise->wait();
  

2. Schema Management

• Create Table

  $client->query('
      CREATE TABLE IF NOT EXISTS users (
          id UInt32,
          name String,
          created_at DateTime
      ) ENGINE = MergeTree()
  ');
  

• Describe Table

  $result = $client->query('DESCRIBE TABLE users');
  $columns = $result->fetchAllAssoc();
  

3. Data Insertion

• Single Row

  $client->query('
      INSERT INTO users (id, name, created_at)
      VALUES ({id}, {name}, now())
  ', [
      'id' => 1,
      'name' => 'John Doe',
  ]);
  

• Batch Insert

  $client->query('
      INSERT INTO users (id, name)
      VALUES
          {1: 'John'},
          {2: 'Jane'}
  ', [
      1 => ['name' => 'John'],
      2 => ['name' => 'Jane'],
  ]);
  

4. Laravel Integration

• Service Provider

  // config/clickhouse.php
  return [
      'url' => env('CLICKHOUSE_URL', 'http://localhost:8123'),
      'timeout' => 5.0,
  ];
  
  // app/Providers/ClickHouseServiceProvider.php
  public function register()
  {
      $this->app->singleton(Client::class, function ($app) {
          return new Client(config('clickhouse.url'));
      });
  }
  

• Eloquent-like Query Builder (Custom)

  class ClickHouseQueryBuilder
  {
      protected $client;
  
      public function __construct(Client $client)
      {
          $this->client = $client;
      }
  
      public function select($columns = ['*'])
      {
          $this->query = 'SELECT ' . implode(', ', $columns);
          return $this;
      }
  
      public function from($table)
      {
          $this->query .= " FROM {$table}";
          return $this;
      }
  
      public function where($column, $operator, $value)
      {
          $this->query .= " WHERE {$column} {$operator} '{$value}'";
          return $this;
      }
  
      public function get()
      {
          return $this->client->query($this->query)->fetchAllAssoc();
      }
  }
  

  Usage:

  $query = new ClickHouseQueryBuilder($client);
  $users = $query->select(['id', 'name'])->from('users')->where('id', '=', 1)->get();
  

5. Event Handling

• Listen for Query Execution

  $client->on('query', function ($query, $params) {
      logger()->info("Executing query: {$query}", $params);
  });
  

Gotchas and Tips

Pitfalls

1. Connection Timeouts

   • Default timeout is 30 seconds. Adjust via:

     $client = new Client('http://localhost:8123', [
         'timeout' => 10.0, // 10 seconds
     ]);
     

   • For long-running queries, increase the timeout or use async methods.

2. Parameter Binding Quirks

   • Arrays in VALUES: Use explicit syntax:

     // Works:
     $client->query('INSERT INTO logs (data) VALUES ({data})', ['data' => ['key' => 'value']]);
     
     // Fails (serializes to JSON string):
     $client->query('INSERT INTO logs (data) VALUES ({data})', ['data' => ['key' => 'value']]); // May not work as expected
     

   • For nested structures, use JSON type in ClickHouse and pass strings:

     $client->query('INSERT INTO logs (data) VALUES ({data:raw})', [
         'data' => json_encode(['key' => 'value']),
     ]);
     

3. Large Result Sets

   • Avoid fetchAll() for large datasets. Use streaming:

     $result = $client->query('SELECT * FROM huge_table');
     while ($row = $result->fetchAssoc()) {
         process($row);
     }
     

4. HTTPS Self-Signed Certificates

   • Disable SSL verification (not recommended for production):

     $client = new Client('https://localhost', [
         'verify_peer' => false,
     ]);
     

   • For production, configure CA certificates properly.

5. Query Caching

   • The client does not cache queries by default. For repeated queries, implement a local cache layer (e.g., Redis):

     $cacheKey = md5($query . json_encode($params));
     $cached = cache()->get($cacheKey);
     if (!$cached) {
         $result = $client->query($query, $params);
         cache()->put($cacheKey, $result, now()->addMinutes(5));
     }
     

Debugging Tips

1. Enable Debug Mode

   $client = new Client('http://localhost:8123', [
       'debug' => true,
   ]);
   

   Logs HTTP requests/responses to storage/logs/clickhouse.log.

2. Raw HTTP Requests

   • Inspect the underlying HTTP client:

     $client->getHttpClient()->setDebug(true);
     

3. Common Errors

   • ClickHouseException: Catch and log:

     try {
         $result = $client->query('INVALID_QUERY');
     } catch (ClickHouseException $e) {
         logger()->error("ClickHouse Error: " . $e->getMessage());
     }
     

   • Network Errors: Ensure ClickHouse is accessible and the port (8123 by default) is open.

Extension Points

1. Custom HTTP Client

   • Replace the default Guzzle client:

     use Psr\Http\Client\ClientInterface;
     use Bavix\ClickHouse\Http\GuzzleHttpClient;
     
     $httpClient = new GuzzleHttpClient(['timeout' => 5]);
     $client = new Client('http://localhost:8123', [], $httpClient);
     

2. Middleware

   • Add request/response middleware:

     $client->getHttpClient()->addMiddleware(function ($request, $handler) {
         $request = $request->withHeader('X-Custom-Header', 'value');
         return $handler->handle($request);
     });
     

3. Query Logging

   • Extend the Query class to log slow queries:

     $client->on('query.start', function ($query, $params) {
         $start = microtime(true);
     });
     
     $client->on('query.end', function ($query, $params, $result) use ($start) {
         $duration = microtime(true) - $start;
         if ($duration > 1.0) {
             logger()->warning("Slow query ({$duration}s): {$query}");
         }
     });
     

4. Custom Data Types

   • Handle ClickHouse-specific types (e.g., DateTime, UUID) by extending the Result class:

     class CustomResult extends \Bavix\ClickHouse\Result
     {
         public function fetchAssoc()
         {
             $row = parent::fetchAssoc();
             if (isset($row['created_at'])) {
                 $row['created_at'] = (