PHP code example of erikwang2013 / clickhouse-php

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

erikwang2013 / clickhouse-php example snippets

use Erikwang2013\ClickHouse\ClickHouse;

ClickHouse::bootstrap();   // 等价于 ClickHouse::setManager(Manager::fromEnv())

$rows = ClickHouse::table('logs')
    ->where('date', '>=', '2024-01-01')
    ->whereIn('level', ['error', 'warn'])
    ->orderBy('timestamp', 'desc')
    ->limit(100)
    ->get();

foreach ($rows as $row) {
    echo $row['message'];
}

use Erikwang2013\ClickHouse\ClickHouse;
use Erikwang2013\ClickHouse\Client\Manager;

// 配置
$config = [
    'default' => 'clickhouse',
    'connections' => [
        'clickhouse' => [
            'driver'   => 'http',        // http（推荐）或 native（开发中）
            'host'     => 'localhost',
            'port'     => 8123,          // HTTP 端口, Native 用 9000
            'database' => 'default',
            'username' => 'default',
            'password' => '',
            'timeout'  => 30,
            'https'    => false,         // true 走 HTTPS
        ],
    ],
    'pool' => [
        'min_connections'    => 2,
        'max_connections'    => 16,
        'connection_timeout' => 5.0,
    ],
];

// 初始化
$manager = new Manager($config);
ClickHouse::setManager($manager);

// 查询
$rows = ClickHouse::table('logs')
    ->where('date', '>=', '2024-01-01')
    ->whereIn('level', ['error', 'warn'])
    ->orderBy('timestamp', 'desc')
    ->limit(100)
    ->get();

foreach ($rows as $row) {
    echo $row['message'];
}

// 原生 SQL
$result = ClickHouse::query('SELECT count(*) AS cnt FROM logs WHERE date = ?', ['2024-01-01']);
echo $result->first()['cnt'];

// 聚合
$count = ClickHouse::table('logs')->where('level', 'error')->count();
$avgDuration = ClickHouse::table('logs')->avg('duration');

// 单行
ClickHouse::table('logs')->insert([
    'date'      => '2024-01-01',
    'level'     => 'info',
    'message'   => 'hello',
    'duration'  => 12.5,
]);

// 批量
ClickHouse::table('logs')->insert([
    ['date' => '2024-01-01', 'level' => 'info',  'message' => 'a', 'duration' => 1.2],
    ['date' => '2024-01-02', 'level' => 'error', 'message' => 'b', 'duration' => 3.4],
]);

ClickHouse::schema()->create('logs', function ($table) {
    $table->date('date');
    $table->dateTime('timestamp');
    $table->string('level');
    $table->string('message');
    $table->float64('duration');
    $table->engine('MergeTree')
          ->partitionBy('toYYYYMM(date)')
          ->orderBy(['date', 'timestamp', 'level']);
});

// 删除表
ClickHouse::schema()->drop('logs');

// 修改表
ClickHouse::schema()->alter('logs', function ($table) {
    $table->nullable('source', 'String');
});

use Erikwang2013\ClickHouse\Migration\Migration;

class CreateLogsTable extends Migration
{
    public function up(): void
    {
        $this->schema->create('logs', function ($table) {
            $table->date('date');
            $table->string('level');
            $table->engine('MergeTree')
                  ->partitionBy('toYYYYMM(date)')
                  ->orderBy(['date', 'level']);
        });
    }

    public function down(): void
    {
        $this->schema->drop('logs');
    }
}

use Erikwang2013\ClickHouse\Migration\Migrator;
use Erikwang2013\ClickHouse\Migration\Repository;

$client = ClickHouse::getManager()->connection();
$repository = new Repository($client);
$migrator = new Migrator($client, $repository, '/path/to/migrations');

$migrator->install();   // 创建迁移记录表
$migrator->run();       // 执行待迁移
$migrator->rollback();  // 回滚上一批
$migrator->refresh();   // 回滚后重新执行

use Erikwang2013\ClickHouse\ORM\Model;

class Log extends Model
{
    protected string $table = 'logs';
    protected string $connection = 'default';
}

// 查询
$logs = Log::where('level', 'error')
    ->orderBy('timestamp', 'desc')
    ->limit(50)
    ->get();

// 查找单条
$log = Log::find(123);

// 聚合
$total = Log::where('date', '>=', '2024-01-01')->count();

// 批量插入
Log::insert([
    ['date' => '2024-01-01', 'level' => 'info'],
    ['date' => '2024-01-02', 'level' => 'error'],
]);

$config = [
    'default' => 'default',
    'connections' => [
        'default' => ['driver' => 'http', 'host' => 'ch1.example.com', 'port' => 8123],
        'analytics' => ['driver' => 'http', 'host' => 'ch2.example.com', 'port' => 8123],
    ],
];

$manager = new Manager($config);

// 使用指定连接
ClickHouse::connection('analytics')->table('events')->get();
ClickHouse::table('events', 'analytics')->get();

use Erikwang2013\ClickHouse\Laravel\Facades\ClickHouse;

ClickHouse::table('logs')->where('level', 'error')->get();
ClickHouse::connection('native')->select('SELECT * FROM logs LIMIT 10');

return [
    \Erikwang2013\ClickHouse\ThinkPHP\ClickHouseService::class,
];

use think\facade\ClickHouse;

ClickHouse::table('logs')->get();

use Erikwang2013\ClickHouse\Webman\ClickHouse;

ClickHouse::table('logs')->where('level', 'error')->get();

use Erikwang2013\ClickHouse\Hyperf\ClickHouseConnection;

class LogController
{
    public function __construct(
        private ClickHouseConnection $clickhouse
    ) {}

    public function index()
    {
        return $this->clickhouse->table('logs')->get();
    }
}

// config/web.php、config/console.php
'bootstrap' => ['Erikwang2013\ClickHouse\Yii2\Bootstrap'],

// config/web.php
'components' => [
    'clickhouse' => [
        'class' => \Erikwang2013\ClickHouse\Yii2\ClickHouse::class,
        'config' => 

Yii::$app->clickhouse->table('logs')->where('level', 'error')->get();
ClickHouse::table('logs')->get();   // 静态门面：组件初始化后同样可用

// 应用侧 config/params.php 覆盖连接参数
return [
    'erikwang2013/clickhouse-php' => [
        'default' => 'default',
        'connections' => [
            'default' => [
                'driver' => 'http',
                'host' => 'ch.internal',
                'port' => 8123,
                'database' => 'logs',
            ],
        ],
    ],
];

use Erikwang2013\ClickHouse\Yii3\ClickHouseConnection;

class LogController
{
    public function __construct(private ClickHouseConnection $clickhouse) {}

    public function index()
    {
        return $this->clickhouse->table('logs')->where('level', 'error')->get();
    }
}

use Erikwang2013\ClickHouse\Client\StreamingClientInterface;

$client = ClickHouse::client();           // 需要底层客户端时用它（connection() 返回的是构造器）
if ($client instanceof StreamingClientInterface) {
    foreach ($client->stream('SELECT * FROM logs') as $row) {   // FORMAT JSONEachRow
        echo $row['message'], PHP_EOL;
    }
}

// 自带 FORMAT 的查询（CSV/TSV 等）用 raw()，原样拿响应体
$csv = $client->raw('SELECT * FROM logs FORMAT CSV');

[
    'default' => 'clickhouse',
    'connections' => [
        'clickhouse' => [
            'driver'   => 'http',     // http（推荐）| native（开发中）
            'host'     => 'localhost',
            'port'     => 8123,       // HTTP 8123, Native 9000
            'database' => 'default',
            'username' => 'default',
            'password' => '',
            'timeout'  => 30,
            'https'    => false,
        ],
    ],
    'pool' => [
        'min_connections'    => 2,
        'max_connections'    => 16,
        'connection_timeout' => 5.0,
    ],
    'query_log' => false,
];

use Erikwang2013\ClickHouse\Exceptions\{
    ClickHouseException,
    ConnectionException,
    QueryException,
    TimeoutException,
    PoolException,
};

try {
    ClickHouse::table('logs')->get();
} catch (ConnectionException | TimeoutException $e) {
    // 连接或超时问题
} catch (QueryException $e) {
    echo $e->getMessage();
    echo $e->getSql();      // 原始 SQL
} catch (ClickHouseException $e) {
    // 其他异常
}

use Erikwang2013\ClickHouse\Support\Pet;

Pet::forms();          // 12 个形态名
Pet::svg('smile');     // 返回 SVG 字符串
Pet::path('smile');    // SVG 绝对路径
Pet::lines('smile');   // 终端完整横幅
Pet::face('smile');    // 终端紧凑表情

bash
composer 

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

bash
php artisan clickhouse:table-list
php artisan clickhouse:migration:install
php artisan clickhouse:migration:run

bash
php think clickhouse:table-list

bash
php bin/hyperf.php vendor:publish erikwang2013/clickhouse-php