Clickhouse Builder Laravel Package

Getting Started

Minimal Setup

1. Install the package:

   composer require the-tinderbox/clickhouse-builder
   

2. Initialize the builder (requires clickhouse-php-client):

   use Tinderbox\Clickhouse\Client;
   use Tinderbox\Clickhouse\Server;
   use Tinderbox\Clickhouse\ServerProvider;
   use Tinderbox\ClickhouseBuilder\Builder;
   
   $server = new Server('127.0.0.1', '8123', 'default', 'user', 'pass');
   $serverProvider = (new ServerProvider())->addServer($server);
   $client = new Client($serverProvider);
   $builder = new Builder($client);
   

3. First query (e.g., fetch data from a table):

   $results = $builder->select('column1', 'column2')->from('table')->get();
   

Laravel/Lumen Integration (Optional)

1. Register the service provider in config/app.php (Laravel) or bootstrap/app.php (Lumen):

   \Tinderbox\ClickhouseBuilder\Integrations\Laravel\ClickhouseServiceProvider::class
   

2. Configure config/database.php:

   'connections' => [
       'clickhouse' => [
           'driver' => 'clickhouse',
           'host' => '127.0.0.1',
           'port' => '8123',
           'database' => 'default',
           'username' => 'user',
           'password' => 'pass',
       ],
   ]
   

3. Use the builder via Laravel's DB facade:

   $results = DB::connection('clickhouse')->query()->select('*')->from('table')->get();
   

Implementation Patterns

Query Composition

1. Fluent Interface: Chain methods for readability and maintainability:

   $results = $builder
       ->select('user_id', 'name')
       ->from('users')
       ->where('active', true)
       ->orderBy('created_at', 'desc')
       ->limit(10)
       ->get();
   

2. Subqueries: Use closures or nested builders for complex logic:

   $subQuery = $builder->select('order_id')->from('orders')->where('status', 'completed');
   $results = $builder
       ->select('users.*')
       ->from('users')
       ->whereIn('id', $subQuery)
       ->get();
   

3. Dynamic Conditions: Build conditions dynamically (e.g., for filtering):

   $filters = ['active' => true, 'role' => 'admin'];
   $query = $builder->from('users');
   foreach ($filters as $field => $value) {
       $query->where($field, $value);
   }
   

Common Workflows

1. Aggregations:

   $results = $builder
       ->select('department', raw('count(*) as user_count'))
       ->from('users')
       ->groupBy('department')
       ->get();
   

2. Joins:

   $results = $builder
       ->select('users.name', 'orders.total')
       ->from('users')
       ->leftJoin('orders', 'all', ['users.id' => 'orders.user_id'])
       ->get();
   

3. Temporary Tables:

   use Tinderbox\ClickhouseBuilder\TempTable;
   use Tinderbox\Clickhouse\Format;
   
   $builder->addFile(new TempTable('temp_numbers', 'data.tsv', ['id' => 'UInt64'], Format::TSV));
   $results = $builder
       ->select('users.*')
       ->from('users')
       ->whereIn('id', 'temp_numbers')
       ->get();
   

4. Async Queries:

   $builder->from('table1')->asyncWithQuery(function ($query) {
       $query->from('table2');
   });
   $results = $builder->get(); // Returns array of results for each async query
   

Laravel-Specific Patterns

1. Query Scoping: Extend the builder for reusable logic:

   namespace App\Scopes;
   
   use Tinderbox\ClickhouseBuilder\Builder;
   
   class ActiveScope {
       public function apply(Builder $builder) {
           return $builder->where('active', true);
       }
   }
   

2. Eloquent-like Usage: Combine with Laravel's Eloquent for hybrid queries:

   $query = DB::connection('clickhouse')->query();
   $users = User::whereIn('id', function ($q) use ($query) {
       $q->select('id')->from('clickhouse_users')->where('active', true);
   })->get();
   

Gotchas and Tips

Pitfalls

1. Column Functions:

   • The Column class for complex expressions (e.g., sumIf) is unstable and under development. Avoid relying on it for production-critical logic.
   • Example of unstable usage:

     $builder->select(function ($column) {
         $column->name('time')->sumIf('time', '>', 10); // May break in future versions
     });
     

2. Async Queries:

   • Results from asyncWithQuery are returned as an array of results, not a single result set. Ensure your code handles this:

     $results = $builder->asyncWithQuery(...)->get();
     foreach ($results as $resultSet) {
         // Process each result set
     }
     

3. Temporary Tables:

   • Order matters: Call addFile() before using the temp table in whereIn, join, etc. Otherwise, the table won't be recognized.
   • Memory tables: The into_memory_table() helper drops and recreates the table. Use cautiously in production to avoid accidental data loss.

4. Dictionary Functions:

   • whereDict requires the dictionary to exist in ClickHouse. Test your queries first to avoid runtime errors:

     $builder->whereDict('user_roles', 'role', 'admin', '=', 'value');
     

5. Raw SQL Injection:

   • Use raw() for dynamic SQL, but validate inputs to prevent injection:

     $builder->where('column', raw("'value'")); // Safe if 'value' is trusted
     

Debugging Tips

1. Inspect the Query: Use toSql() to debug the generated SQL:

   $sql = $builder->select('*')->from('table')->toSql();
   dd($sql); // Dump the SQL for inspection
   

2. Error Handling: Wrap queries in try-catch blocks to handle ClickHouse-specific errors:

   try {
       $results = $builder->select('*')->from('nonexistent_table')->get();
   } catch (\Exception $e) {
       \Log::error("ClickHouse error: " . $e->getMessage());
   }
   

3. Performance:

   • Avoid SELECT *: Explicitly list columns to reduce data transfer.
   • Use LIMIT: Always limit results during development to avoid overwhelming your local machine.
   • Indexing: Ensure your ClickHouse tables are properly indexed for the queries you run.

Extension Points

1. Custom Query Macros: Add reusable query logic via macros (Laravel-specific):

   Builder::macro('activeUsers', function () {
       return $this->where('active', true)->where('role', 'user');
   });
   

   Usage:

   $builder->activeUsers()->get();
   

2. Override Default Behavior: Extend the Builder class to customize query building:

   class CustomBuilder extends Builder {
       public function customJoin($table, $columns) {
           return $this->join($table, 'all', 'inner', $columns);
       }
   }
   

3. Integrate with Laravel Events: Listen to query events (e.g., illuminate.query) to log or modify queries:

   \DB::listen(function ($query) {
       if ($query->connectionName === 'clickhouse') {
           \Log::debug("ClickHouse Query: " . $query->sql);
       }
   });
   

Configuration Quirks

1. Cluster vs. Single Server:

   • Configure servers for clusters or a single host for standalone instances in config/database.php.
   • Example for clusters:

     'servers' => [
         ['host' => 'ch-00.domain.com', 'port' => '8123'],
         ['host' => 'ch-01.domain.com', 'port' => '8123'],
     ]
     

2. Protocol:

   • Explicitly set protocol to https if using secure connections:

     'options' => ['protocol' => 'https']
     

3