Clickhouse Builder Laravel Package

Getting Started

Minimal Setup

1. Install the package:

   composer require the-tinderbox/clickhouse-builder
   

2. Initialize the client and builder (required for all queries):

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

Key First Use Cases

• Basic CRUD: Use select(), from(), where(), and get() for reads.
• Complex queries: Chain methods like join(), groupBy(), or orderBy().
• Subqueries: Pass closures to select(), from(), or where() for nested queries.

Implementation Patterns

Query Construction Workflow

1. Method chaining for readability:

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

2. Closure-based subqueries (for dynamic or reusable logic):

   $builder->select(function ($column) {
       $column->as('total_orders')
           ->query(function ($query) {
               $query->select('count(*)')->from('orders')->where('user_id', '=', $userId);
           });
   });
   

3. Reusable query builders (e.g., for shared logic):

   $userQuery = $builder->select('*')->from('users')->where('active', true);
   $activeUsers = $userQuery->get();
   $recentOrders = $builder->from('orders')
       ->whereIn('user_id', $userQuery)
       ->orderBy('created_at', 'desc')
       ->get();
   

Integration with Laravel/Lumen

1. Register the service provider (Laravel):

   // config/app.php
   'providers' => [
       \Tinderbox\ClickhouseBuilder\Integrations\Laravel\ClickhouseServiceProvider::class,
   ],
   

2. Configure the connection (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 DB facade:

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

Temporary Tables and File Handling

1. Upload and query local files:

   $builder->addFile(new TempTable('numbersTable', 'numbers.tsv', ['number' => 'UInt64'], Format::TSV));
   $results = $builder->select('*')->from('main_table')->whereIn('id', 'numbersTable')->get();
   

2. Bulk inserts from files:

   $builder->table('logs')->insertFiles(['timestamp', 'message'], [
       'logs_2023.tsv',
       'logs_2024.tsv',
   ], Format::TSV);
   

Gotchas and Tips

Common Pitfalls

1. Column alias syntax:

   • Use ['column' => 'alias'] or 'column as alias' for clarity.
   • Avoid spaces in aliases (e.g., 'column as alias' works, but 'column as alias' with spaces may fail).

2. Subquery closures:

   • Ensure closures passed to select(), from(), or where() return valid queries.
   • Example of a broken subquery:

     // ❌ Avoid: Missing `from()` in closure
     $builder->where('column', function ($query) {
         $query->select('value'); // Fails: No FROM clause
     });
     

3. Temporary tables:

   • Call addFile() before using the table in whereIn() or join().
   • Temporary tables are not persisted; ensure files are uploaded before querying.

4. Async queries:

   • Results from asyncWithQuery() are returned as an array of results (indexed by query order).
   • Example:

     $results = $builder->asyncWithQuery(function ($query) {
         $query->select('*')->from('table1');
     })->asyncWithQuery(function ($query) {
         $query->select('*')->from('table2');
     })->get();
     // $results[0] = table1 data, $results[1] = table2 data
     

Debugging Tips

1. Inspect raw SQL: Use toSql() to debug queries before execution:

   $sql = $builder->select('*')->from('users')->toSql();
   

2. Handle errors:

   • Wrap get() in a try-catch for ClickHouse exceptions:

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

3. Check for deprecated methods:

   • The README mentions "Functions on columns is not stable and under development." Avoid relying on Column class methods like sumIf() for production.

Performance Quirks

1. Avoid SELECT *: Explicitly list columns to reduce data transfer:

   // ❌ Inefficient
   $builder->select('*')->from('large_table');
   // ✅ Better
   $builder->select('id', 'name')->from('large_table');
   

2. Use LIMIT early: Apply limit() before complex joins or aggregations to reduce intermediate result sets.

3. Leverage SAMPLE for analytics: Use sample(0.1) for approximate queries on large datasets:

   $builder->select('avg(value)')->from('metrics')->sample(0.1)->get();
   

Extension Points

1. Custom query builders: Extend the Builder class to add domain-specific methods:

   class UserBuilder extends Builder {
       public function active() {
           return $this->where('active', true);
       }
   }
   

2. Override SQL generation: Extend the Builder class and override methods like compileSelect() or compileWhere() for custom syntax.

3. Add helper methods: Create static methods for common queries (e.g., getActiveUsers()):

   class QueryHelper {
       public static function getActiveUsers(Builder $builder) {
           return $builder->select('*')->from('users')->where('active', true);
       }
   }