README

Laravel ClickHouse integration package based on smi2/phpclickhouse client with advanced features including migration system, cluster support, and comprehensive exception handling.

• Vendor: deflinhec
• Package: laravel-clickhouse
• Composer: composer require deflinhec/laravel-clickhouse

Features

• ???? ClickHouse connection management with multiple connection support
• ???? Migration system with Laravel integration
• ???? Query builder and custom query execution
• ???? Testing tools and CLI interface
• ???? Complete logging and monitoring
• ⚡ High-performance query support
• ???? Cluster mode with load balancing (round-robin, random, failover)
• ????️ Comprehensive exception handling with custom error types
• ???? Artisan commands for management and testing
• ???? Composer package with proper autoloading

PHP Compatibility

This package is compatible with:

• PHP 7.3+ (with full type hint support)
• Laravel 5.0+ through Laravel 12.0+

Installation

1. Install Package

composer require deflinhec/laravel-clickhouse

2. Publish Configuration Files

php artisan vendor:publish --tag=clickhouse-config

3. Set Environment Variables

Add the following to your .env file:

# ClickHouse Connection Settings CLICKHOUSE_HOST=localhost CLICKHOUSE_PORT=8123 CLICKHOUSE_USERNAME=default CLICKHOUSE_PASSWORD=clickhouse CLICKHOUSE_DATABASE=default CLICKHOUSE_SSL=false CLICKHOUSE_READONLY=true CLICKHOUSE_TIMEOUT=30 # Logging Settings CLICKHOUSE_LOGGING_ENABLED=true CLICKHOUSE_LOGGING_CHANNEL=clickhouse # Migration Settings CLICKHOUSE_MIGRATIONS_PATH=database/migrations/clickhouse # Cluster Mode Settings (Optional) CLICKHOUSE_CONNECTION=cluster CLICKHOUSE_CLUSTER_MODE=round_robin CLICKHOUSE_CLUSTER_NODES=node1,node2 CLICKHOUSE_CLUSTER_PORTS=8123,8123 CLICKHOUSE_CLUSTER_WEIGHTS=1,1 CLICKHOUSE_CLUSTER_RETRY_ATTEMPTS=3 CLICKHOUSE_CLUSTER_RETRY_DELAY=1000 CLICKHOUSE_CLUSTER_HEALTH_CHECK_INTERVAL=30 CLICKHOUSE_CLUSTER_FAILOVER_TIMEOUT=5000

Basic Usage

Service-based Approach

use Deflinhec\LaravelClickHouse\Services\Service; $clickHouse = new Service(); // Execute custom query $result = $clickHouse->executeQuery('SELECT * FROM your_table LIMIT 10'); // Test connection if ($clickHouse->testConnection()) { echo "Connection successful!"; }

Exception Handling

The package provides a custom ClickHouseException class with comprehensive error types:

use Deflinhec\LaravelClickHouse\Exceptions\ClickHouseException; try { $result = $clickHouse->executeQuery('SELECT * FROM non_existent_table'); } catch (ClickHouseException $e) { echo "Error Type: " . $e->getErrorType(); echo "Error Code: " . $e->getErrorCode(); echo "Error Message: " . $e->getMessage(); echo "Error Context: " . json_encode($e->getContext()); } // Available error types ClickHouseException::connectionError($message, $context); ClickHouseException::queryError($message, $context); ClickHouseException::configurationError($message, $context); ClickHouseException::migrationError($message, $context); ClickHouseException::clusterError($message, $context); ClickHouseException::authenticationError($message, $context); ClickHouseException::permissionError($message, $context); ClickHouseException::timeoutError($message, $context); ClickHouseException::syntaxError($message, $context); ClickHouseException::resourceError($message, $context);

Artisan Commands

Interactive CLI

# Open ClickHouse interactive CLI php artisan clickhouse

Connection Testing

# Open interactive CLI (includes connection test) php artisan clickhouse # Execute single query php artisan clickhouse --query="SELECT COUNT(*) FROM your_table" # Specify connection php artisan clickhouse --connection=local

Cluster Management

# Check cluster status php artisan clickhouse:cluster:status # Detailed cluster status php artisan clickhouse:cluster:status --detailed # Check cluster status with specific connection php artisan clickhouse:cluster:status --connection=cluster

Migration Management

# Create migration file php artisan make:clickhouse-migration create_users_table php artisan make:clickhouse-migration create_orders_table --table=orders php artisan make:clickhouse-migration create_products_table --create --columns="name:string,price:decimal,is_active:bool" # Run migrations php artisan clickhouse:migrate # Preview migration SQL php artisan clickhouse:migrate --pretend # Specify migration path php artisan clickhouse:migrate --path=database/migrations/clickhouse # Rollback migrations php artisan clickhouse:migrate:rollback # Rollback specific number of migrations php artisan clickhouse:migrate:rollback --step=3

Migration System

Important Notes

ClickHouse migrations use Laravel's default migrations table to track migration status, rather than creating additional tables in ClickHouse. This ensures migration records are consistent with other Laravel migrations.

Creating Migration Files

Use the make:clickhouse-migration command to quickly create migration files:

# Basic usage php artisan make:clickhouse-migration create_users_table # Specify table name php artisan make:clickhouse-migration create_orders_table --table=orders # Create table (explicitly specified) php artisan make:clickhouse-migration create_products_table --create # Custom fields php artisan make:clickhouse-migration create_analytics_table --columns="user_id:int,name:string,score:float,is_active:bool,tags:array" # Specify path php artisan make:clickhouse-migration create_test_table --path=database/migrations/custom

Supported Field Types

The command supports the following field type mappings:

• string → String
• int / integer → Int32
• bigint → Int64
• float → Float32
• double → Float64
• decimal → Decimal(10,2)
• bool / boolean → UInt8
• date → Date
• datetime / timestamp → DateTime
• array → Array(String)
• json → String

Manual Migration File Creation

<?php use Deflinhec\LaravelClickHouse\Database\Migration; class CreateExampleTable extends Migration { public function up() { return <<<SQL  CREATE TABLE IF NOT EXISTS example_table (  id UInt32,  name String,  value Float64,  is_active UInt8 DEFAULT 1,  tags Array(String),  metadata String,  created_at DateTime DEFAULT now(),  updated_at DateTime DEFAULT now()  ) ENGINE = MergeTree()  ORDER BY (id, created_at)  SQL; } public function down() { return <<<SQL  DROP TABLE IF EXISTS example_table  SQL; } }

Configuration

Connection Settings

'connections' => [ 'default' => [ 'host' => env('CLICKHOUSE_HOST', 'localhost'), 'port' => env('CLICKHOUSE_PORT', '8123'), 'username' => env('CLICKHOUSE_USERNAME', 'default'), 'password' => env('CLICKHOUSE_PASSWORD', ''), 'database' => env('CLICKHOUSE_DATABASE', 'default'), 'options' => [ 'timeout' => env('CLICKHOUSE_TIMEOUT', 30), 'ssl' => env('CLICKHOUSE_SSL', false), 'verify' => env('CLICKHOUSE_VERIFY', false), ], ], 'cluster' => [ 'mode' => env('CLICKHOUSE_CLUSTER_MODE', 'round_robin'), 'nodes' => [ 'host' => explode(',', env('CLICKHOUSE_CLUSTER_NODES', 'node1,node2')), 'port' => explode(',', env('CLICKHOUSE_CLUSTER_PORTS', '8123,8123')), 'weight' => explode(',', env('CLICKHOUSE_CLUSTER_WEIGHTS', '1,1')), 'username' => env('CLICKHOUSE_USERNAME', 'default'), 'password' => env('CLICKHOUSE_PASSWORD', 'clickhouse'), 'database' => env('CLICKHOUSE_DATABASE', 'default'), 'options' => [ 'timeout' => env('CLICKHOUSE_TIMEOUT', 30), 'ssl' => env('CLICKHOUSE_SSL', false), 'readonly' => env('CLICKHOUSE_READONLY', true), ], ], 'options' => [ 'retry_attempts' => env('CLICKHOUSE_CLUSTER_RETRY_ATTEMPTS', 3), 'retry_delay' => env('CLICKHOUSE_CLUSTER_RETRY_DELAY', 1000), // milliseconds 'health_check_interval' => env('CLICKHOUSE_CLUSTER_HEALTH_CHECK_INTERVAL', 30), // seconds 'failover_timeout' => env('CLICKHOUSE_CLUSTER_FAILOVER_TIMEOUT', 5000), // milliseconds ], ], ],

Table Structure Examples

Basic Table

Standard ClickHouse table structure example:

CREATE TABLE example_table ( id UInt32, name String, value Float64, created_at DateTime DEFAULT now() ) ENGINE = MergeTree() ORDER BY (id, created_at)

Nested Data Type Example

Using ClickHouse's Nested data type to handle complex hierarchical structures:

CREATE TABLE nested_example ( id UInt32, depth Nested(identify String, ratio Decimal(8,2), rebate Decimal(8,2)), created_at DateTime DEFAULT now() ) ENGINE = MergeTree() ORDER BY (id, created_at)

Benefits include:

• More compact data structure, saving storage space
• More flexible queries (can use has(), arrayJoin() and other ClickHouse strengths)
• Easier analysis and maintenance of deep structures

Supported ClickHouse Functions

• has() - Check if array contains specific value
• arrayJoin() - Expand arrays
• length() - Get array length
• toDate() - Date conversion
• row_number() OVER (PARTITION BY ... ORDER BY ...) - Window functions

Testing

# Open interactive CLI for testing php artisan clickhouse # Execute test query php artisan clickhouse --query="SELECT has(depth.identify, 'agent1') FROM your_table LIMIT 1" # Set up ClickHouse server (if not already running) docker run -d --name clickhouse-server -p 8124:8123 -p 9001:9000 \ -e CLICKHOUSE_USER=default -e CLICKHOUSE_PASSWORD=password \ -e CLICKHOUSE_DB=default clickhouse/clickhouse-server:latest # Run tests with proper environment variables CLICKHOUSE_HOST=host.docker.internal CLICKHOUSE_PORT=8124 \ CLICKHOUSE_PASSWORD=password vendor/bin/phpunit

Recent Updates

• Enhanced Migration System: Full Laravel migration integration with custom commands
• Cluster Support: Multi-node ClickHouse cluster with load balancing and health checks
• Exception Handling: Comprehensive error handling with custom exception types
• CLI Interface: Interactive ClickHouse client and management commands
• Performance Optimization: Configurable performance settings and connection pooling
• Documentation: Complete documentation with examples and best practices

Credits

This package was originally created by bavix and has been significantly enhanced with modern Laravel features, cluster support, and comprehensive testing.

License

MIT License