php laravel 项目里面使用clickhouse 并新增 clickhouse的migration 操作指令
时间：2025-6-11 11:25 作者：wanzi 分类： php
前言
最近项目中会使用到clickhouse来存储一些数据量很大的入侵检测行为分析日志，因此也是在laravel 里面首次做了集成，同时也感觉有些地方可以给大家做一个分享。
简单介绍clickhouse
ClickHouse 是一个开源的列式数据库管理系统（Column-Oriented DBMS），专为在线分析处理（OLAP）设计，以高性能查询和实时数据分析能力著称。以下是详细解答：
开发者与历史
- 开发者：由俄罗斯互联网公司 Yandex 为内部数据分析需求开发，核心开发者包括 Alexey Milovidov（ClickHouse 数据库原型的主要设计者）。
- 发展历程：
- 起源于2011年前后，最初用于支持 Yandex 的网络分析服务 Yandex.Metrica（全球第二大网络分析平台），当时需处理超过13万亿条记录。
- 2016年6月正式开源，至今（截至2025年）已有约 9年历史。
应用场景
ClickHouse 主要应用于需要快速处理海量数据的场景，例如：
- 实时交互式报表：分析订单、收入、用户数等核心业务指标。
- 用户行为分析：跟踪各渠道的页面浏览量（PV）、独立访客（UV）来源。
- 日志分析：支持高吞吐量的数据写入与实时查询，适用于监控和日志系统。
- 大规模数据仓库：Yandex.Metrica 中存储了超过20万亿行数据，且90%的查询可在1秒内返回结果。
为何能持续发展
-
高性能优势：
- 针对OLAP场景优化，支持列式存储、向量化执行引擎和高效压缩算法，显著提升查询速度。
- 在万亿级数据规模下仍能保持亚秒级响应。
-
开源与生态支持：
- 开源策略吸引了全球开发者贡献代码，形成活跃的社区生态。
- 被广泛集成到云服务（如阿里云、AWS）和大数据平台中。
-
企业级需求驱动：
- Yandex 自身的高并发、低延迟分析需求推动了其技术迭代。
- 后续被多家公司采用，验证了其在复杂分析场景下的可靠性。
-
灵活扩展性：
- 支持分布式架构，可横向扩展至数千节点集群。
- 提供丰富的索引、分区和副本机制，适应多样化业务需求。
phpclickhouse-laravel
这是一个基于phpClickHouse包开发的适配laravel项目的扩展包，目前还是有更新。大家用它就行。也不做具体介绍了，传送门：phpclickhouse-laravel.
然后我在贴一个database.php的配置：
<?php
use Illuminate\Support\Str;
return [
/*
|--------------------------------------------------------------------------
| Default Database Connection Name
|--------------------------------------------------------------------------
|
| Here you may specify which of the database connections below you wish
| to use as your default connection for all database work. Of course
| you may use many connections at once using the Database library.
|
*/
'default' => env('DB_CONNECTION', 'mysql'),
/*
|--------------------------------------------------------------------------
| Database Connections
|--------------------------------------------------------------------------
|
| Here are each of the database connections setup for your application.
| Of course, examples of configuring each database platform that is
| supported by Laravel is shown below to make development simple.
|
|
| All database work in Laravel is done through the PHP PDO facilities
| so make sure you have the driver for your particular database of
| choice installed on your machine before you begin development.
|
*/
'connections' => [
'sqlite' => [
'driver' => 'sqlite',
'url' => env('DATABASE_URL'),
'database' => env('DB_DATABASE', database_path('database.sqlite')),
'prefix' => '',
'foreign_key_constraints' => env('DB_FOREIGN_KEYS', true),
],
'mysql' => [
'driver' => 'mysql',
'url' => env('DATABASE_URL'),
'host' => env('DB_HOST', '127.0.0.1'),
'port' => env('DB_PORT', '3306'),
'database' => env('DB_DATABASE', 'forge'),
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
'unix_socket' => env('DB_SOCKET', ''),
'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',
'prefix' => '',
'prefix_indexes' => true,
'strict' => true,
'engine' => null,
'options' => extension_loaded('pdo_mysql') ? array_filter([
PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
]) : [],
],
'pgsql' => [
'driver' => 'pgsql',
'url' => env('DATABASE_URL'),
'host' => env('DB_HOST', '127.0.0.1'),
'port' => env('DB_PORT', '5432'),
'database' => env('DB_DATABASE', 'forge'),
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
'charset' => 'utf8',
'prefix' => '',
'prefix_indexes' => true,
'schema' => 'public',
'sslmode' => 'prefer',
],
'sqlsrv' => [
'driver' => 'sqlsrv',
'url' => env('DATABASE_URL'),
'host' => env('DB_HOST', 'localhost'),
'port' => env('DB_PORT', '1433'),
'database' => env('DB_DATABASE', 'forge'),
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
'charset' => 'utf8',
'prefix' => '',
'prefix_indexes' => true,
],
'clickhouse' => [
'driver' => 'clickhouse',
'host' => env('CLICKHOUSE_HOST', 'localhost'),
'port' => env('CLICKHOUSE_PORT', 8123),
'database' => env('CLICKHOUSE_DB', 'default'),
'username' => env('CLICKHOUSE_USER', 'default'),
'password' => env('CLICKHOUSE_PASSWORD', ''),
'timeout_connect' => env('CLICKHOUSE_TIMEOUT_CONNECT',10),
'timeout_query' => env('CLICKHOUSE_TIMEOUT_QUERY',10),
'https' => (bool)env('CLICKHOUSE_HTTPS', null),
'retries' => env('CLICKHOUSE_RETRIES', 0),
'settings' => [ // optional
'max_partitions_per_insert_block' => 300,
],
],
],
/*
|--------------------------------------------------------------------------
| Migration Repository Table
|--------------------------------------------------------------------------
|
| This table keeps track of all the migrations that have already run for
| your application. Using this information, we can determine which of
| the migrations on disk haven't actually been run in the database.
|
*/
'migrations' => 'migrations',
/*
|--------------------------------------------------------------------------
| Redis Databases
|--------------------------------------------------------------------------
|
| Redis is an open source, fast, and advanced key-value store that also
| provides a richer body of commands than a typical key-value system
| such as APC or Memcached. Laravel makes it easy to dig right in.
|
*/
'redis' => [
'client' => env('REDIS_CLIENT', 'phpredis'),
'options' => [
'cluster' => env('REDIS_CLUSTER', 'redis'),
'prefix' => env('REDIS_PREFIX', Str::slug(env('APP_NAME', 'laravel'), '_') . '_database_'),
],
'default' => [
'url' => env('REDIS_URL'),
'host' => env('REDIS_HOST', '127.0.0.1'),
'password' => env('REDIS_PASSWORD', null),
'port' => env('REDIS_PORT', 6379),
'database' => env('REDIS_DB', 0),
],
'cache' => [
'url' => env('REDIS_URL'),
'host' => env('REDIS_HOST', '127.0.0.1'),
'password' => env('REDIS_PASSWORD', null),
'port' => env('REDIS_PORT', 6379),
'database' => env('REDIS_CACHE_DB', 1),
],
],
];扩展 migration
一开始，我还是在用phpclickhouse-laravel的migration，但是发现执行报关于事务使用的报错，clickhouse是不支持事务（搜了下，好像21年后有实验性质的事务功能），然后我用的这个包是最新的，不清楚是作者没处理完整还是我设置的问题，大致报错的地方代码：
`` 有兴趣的朋友可以去看下具体的原因：$this->getSchemaGrammar($connection)` 这里是null，可以追下代码。这里就说了，不是我的重点。
提供 make:clickhouse-migration 和 migrate:clickhouse指令
同样的，我提供了生成迁移sql文件的指令和执行迁移的指令。我做了测试并且用ai优化了代码大家可以放心用。然后是基本支持：create table、drop table 、 rename table 、alter table fields。最后，我也通过记录migration.log文件记录执行过的迁移sql文件来避免和记录迁移过程
代码
MakeClickhouseMigration
<?php
namespace App\Console\Commands;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\File;
use Illuminate\Support\Str;
class MakeClickhouseMigration extends Command
{
protected $signature = 'make:clickhouse-migration {name : The name of the migration (e.g. create_users_table)} {--T|timestamp : Add timestamp prefix to filename}';
protected $description = 'Create a new ClickHouse migration file in database/clickhouse_migrations';
public function handle()
{
$name = $this->argument('name');
if (!$this->isValidName($name)) {
$this->error("Invalid migration name. Use formats like: create_users_table, drop_logs_table, alter_xx_table, rename_old_to_new_table");
return;
}
$filename = $name . '.sql';
if ($this->option('timestamp')) {
$timestamp = now()->format('Ymd_His');
$filename = $timestamp . '_' . $filename;
}
$path = database_path('clickhouse_migrations/' . $filename);
if (File::exists($path)) {
$this->error("Migration file already exists: $path");
return;
}
File::ensureDirectoryExists(database_path('clickhouse_migrations'));
File::put($path, $this->getStubSql($name));
$this->info("Created ClickHouse migration: $path");
}
/**
* Validate migration name format.
*/
protected function isValidName(string $name): bool
{
return preg_match('/^(create|drop|alter|rename)_.+_table$/', $name);
}
/**
* Generate stub SQL content based on action.
*/
protected function getStubSql(string $name): string
{
if (Str::startsWith($name, 'create_')) {
$table = $this->extractTable($name);
return "CREATE TABLE $table (\n -- your columns here\n) ENGINE = MergeTree()\nORDER BY id;";
}
if (Str::startsWith($name, 'drop_')) {
$table = $this->extractTable($name);
return "DROP TABLE IF EXISTS $table;";
}
if (Str::startsWith($name, 'alter_')) {
$table = $this->extractTable($name);
return "ALTER TABLE $table\n -- your alter statements here;";
}
if (Str::startsWith($name, 'rename_')) {
$parts = explode('_to_', Str::after($name, 'rename_'));
if (count($parts) === 2) {
return "RENAME TABLE {$parts[0]} TO {$parts[1]};";
}
}
return "-- Write your ClickHouse SQL here";
}
/**
* Extract table name from migration name.
*/
protected function extractTable(string $name): string
{
return Str::beforeLast(Str::after($name, '_'), '_table');
}
}
MigrateClickhouse
<?php
namespace App\Console\Commands;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\File;
class MigrateClickhouse extends Command
{
protected $signature = 'migrate:clickhouse';
protected $description = 'Run ClickHouse migrations from .sql files. Supports create, drop, alter, rename.';
protected $help = <<<EOT
This command runs ClickHouse migrations by executing raw .sql files
located in the database/clickhouse_migrations directory.
Supported migration file naming conventions and corresponding actions:
1. create_{table}_table.sql => CREATE TABLE {table}
2. drop_{table}_table.sql => DROP TABLE {table}
3. alter_{table}_table.sql => ALTER TABLE {table}
4. rename_{old}_to_{new}_table.sql => RENAME TABLE {old} TO {new}
Examples:
- create_users_table.sql runs CREATE TABLE users ...
- drop_logs_table.sql runs DROP TABLE logs
- alter_orders_table.sql runs ALTER TABLE orders ...
- rename_old_to_new_table.sql runs RENAME TABLE old TO new
Usage:
php artisan migrate:clickhouse
Notes:
- Only files matching the above patterns will be executed.
- Files are processed in the order returned by File::files().
- CREATE operations are skipped if the table already exists.
- Already executed files are tracked in migration.log to avoid re-execution.
EOT;
protected string $migrationPath;
protected string $logFile;
protected array $executedMigrations = [];
public function handle()
{
$this->migrationPath = database_path('clickhouse_migrations');
$this->logFile = $this->migrationPath . '/migration.log';
$this->loadExecutedLog();
$files = File::files($this->migrationPath);
foreach ($files as $file) {
$filename = $file->getFilename();
if ($file->getExtension() !== 'sql') {
continue;
}
if (in_array($filename, $this->executedMigrations)) {
$this->line("Skipped (already run): $filename");
continue;
}
[$action, $tableName] = $this->parseMigrationFilename($filename);
if (!$action || !$tableName) {
$this->warn("Invalid migration file name: $filename");
continue;
}
try {
// rename_old_to_new_table.sql
if ($action === 'rename') {
[$oldTable, $newTable] = explode('_to_', $tableName);
if (!$this->tableExists($oldTable)) {
$this->warn("Table '$oldTable' does not exist. Skipping rename.");
continue;
}
$this->renameTable($oldTable, $newTable);
$this->logExecuted($filename);
continue;
}
// CREATE 跳过已存在表
if ($action === 'create' && $this->tableExists($tableName)) {
$this->line("Table '$tableName' already exists. Skipping create.");
$this->logExecuted($filename);
continue;
}
$sql = File::get($file->getPathname());
DB::connection('clickhouse')->statement($sql);
$this->info("Executed '$action' for table '$tableName'.");
$this->logExecuted($filename);
} catch (\Exception $e) {
$this->error("Failed to execute '$action' for table '$tableName', {$e->getMessage()}");
}
}
$this->info(" All ClickHouse migrations processed.");
}
protected function parseMigrationFilename(string $filename): array
{
$name = pathinfo($filename, PATHINFO_FILENAME);
if (preg_match('/^create_(.+?)_table$/', $name, $m)) {
return ['create', $m[1]];
}
if (preg_match('/^drop_(.+?)_table$/', $name, $m)) {
return ['drop', $m[1]];
}
if (preg_match('/^alter_(.+?)_table$/', $name, $m)) {
return ['alter', $m[1]];
}
if (preg_match('/^rename_(.+?_to_.+?)_table$/', $name, $m)) {
return ['rename', $m[1]];
}
return [null, null];
}
protected function renameTable(string $oldTable, string $newTable): void
{
$this->info("Renaming table '$oldTable' to '$newTable'");
DB::connection('clickhouse')->statement("RENAME TABLE `$oldTable` TO `$newTable`");
$this->info("Rename completed.");
}
protected function tableExists(string $table): bool
{
$result = DB::connection('clickhouse')->select("EXISTS TABLE `$table`");
return isset($result[0]['result']) && $result[0]['result'] == 1;
}
protected function loadExecutedLog(): void
{
if (!File::exists($this->logFile)) {
$this->executedMigrations = [];
return;
}
$this->executedMigrations = array_filter(array_map('trim', explode("\n", File::get($this->logFile))));
}
protected function logExecuted(string $filename): void
{
File::append($this->logFile, $filename . PHP_EOL);
}
}