Laravel Clickhouse Laravel Package

Getting Started

Minimal Setup

1. Installation:

   composer require one23/laravel-clickhouse
   

   Publish the config file:

   php artisan vendor:publish --provider="One23\LaravelClickhouse\ClickhouseServiceProvider"
   

2. Configure .env:

   CLICKHOUSE_HOST=127.0.0.1
   CLICKHOUSE_PORT=9000
   CLICKHOUSE_DATABASE=your_db
   CLICKHOUSE_USERNAME=user
   CLICKHOUSE_PASSWORD=pass
   

3. First Use Case: Extend your Eloquent model to use ClickHouse:

   use One23\LaravelClickhouse\Eloquent\Model;
   
   class User extends Model
   {
       protected $connection = 'clickhouse';
   }
   

   Define the connection in config/database.php:

   'clickhouse' => [
       'driver' => 'clickhouse',
       'host' => env('CLICKHOUSE_HOST'),
       'port' => env('CLICKHOUSE_PORT'),
       'database' => env('CLICKHOUSE_DATABASE'),
       'username' => env('CLICKHOUSE_USERNAME'),
       'password' => env('CLICKHOUSE_PASSWORD'),
   ],
   

4. Run Migrations: Ensure your migrations are compatible with ClickHouse syntax (e.g., DateTime instead of timestamp).

Implementation Patterns

Model Integration

• Base Model: Extend One23\LaravelClickhouse\Eloquent\Model for all ClickHouse models.
• Query Builder: Use DB::connection('clickhouse')->table() for raw queries.
• Relationships: Define relationships like standard Eloquent, but ensure foreign keys align with ClickHouse’s schema.

Common Workflows

1. Data Ingestion:

   // Bulk insert
   User::insert([
       ['name' => 'John', 'email' => 'john@example.com'],
       ['name' => 'Jane', 'email' => 'jane@example.com'],
   ]);
   

   Use ClickHouse’s INSERT INTO ... VALUES syntax for efficiency.

2. Time-Series Data: Leverage ClickHouse’s strengths with DateTime fields and Array types:

   schema()->create('metrics', function (Blueprint $table) {
       $table->dateTime('recorded_at');
       $table->array('values')->nullable();
   });
   

3. Aggregations:

   $results = User::selectRaw('
       toDate(recorded_at) as day,
       avg(value) as avg_value
   ')
   ->groupBy('day')
   ->get();
   

4. Materialized Views: Create materialized views via migrations or raw SQL for performance-critical queries.

Integration Tips

• Hybrid Databases: Use ClickHouse for analytics and PostgreSQL/MySQL for transactions.
• Queue Jobs: Offload heavy ClickHouse writes to queues (e.g., Laravel Queues).
• Caching: Cache frequent aggregations in Redis to reduce ClickHouse load.

Gotchas and Tips

Pitfalls

1. Schema Mismatches:

   • ClickHouse lacks traditional ALTER TABLE for columns. Use MODIFY COLUMN or recreate tables.
   • Avoid unsignedBigInteger for auto-increments; use UUID or DateTime + sortingKey instead.

2. Data Types:

   • ClickHouse’s String has a max length of 2^32 - 1 (vs. MySQL’s 255). Use String for large texts.
   • DateTime is preferred over timestamp for time-series data.

3. Transactions:

   • ClickHouse supports limited ACID transactions. Avoid complex transactions; use retries for failures.

4. Eloquent Quirks:

   • Soft deletes require a deleted_at column, but ClickHouse’s DELETE is not atomic. Use a is_deleted boolean instead.
   • increment()/decrement() may not work as expected; use raw SQL or update().

Debugging

• Logs: Enable ClickHouse query logging in .env:

  CLICKHOUSE_LOG_QUERIES=true
  

• Connection Issues: Verify firewall rules and ClickHouse’s users.xml for user permissions.
• Query Performance: Use EXPLAIN in raw SQL to analyze query plans.

Extension Points

1. Custom Query Builder: Extend the query builder to add ClickHouse-specific functions (e.g., arrayJoin, groupArray):

   DB::connection('clickhouse')->select('arrayJoin(values) as value from metrics');
   

2. Event Listeners: Listen for eloquent.saved to trigger ClickHouse-specific post-save logic (e.g., updating materialized views).

3. Testing: Use Docker for local ClickHouse instances in tests:

   # docker-compose.yml
   services:
       clickhouse:
           image: clickhouse/clickhouse-server
           ports:
               - "9000:9000"
   

4. Seeding: Seed large datasets efficiently with ClickHouse’s INSERT INTO ... SELECT or clickhouse-client CLI tool.