PHP code example of ysfkc / clickhouse

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

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

    

ysfkc / clickhouse example snippets

use Ysfkc\ClickHouse\ClickHouseClientService;

ClickHouseClientService::configure([
    'host'           => 'http://clickhouse-server:8123',
    'database'       => 'analytics',
    'username'       => 'default',
    'password'       => 'secret',
    'timeout'        => 30,   // seconds
    'connectTimeout' => 10,   // seconds
]);

ClickHouseClientService::configure([...], $myPsrLogger);

$client = ClickHouseClientService::getInstance();

use Ysfkc\ClickHouse\ClickHouseClientService;
use Ysfkc\ClickHouse\QueryBuilder;

// Direct client
$client   = ClickHouseClientService::getInstance();
$response = $client->select('SELECT 1');

// QueryBuilder — no manual getInstance() needed
$response = QueryBuilder::table('events')
    ->where('user_id', '=', 123, 'Int32')
    ->get();

// ORM Model — no manual getInstance() needed
$snapshots = MetricSnapshot::find(['status' => 'ok']);

use Ysfkc\ClickHouse\Model\ClickHouseBaseModel;

ClickHouseBaseModel::setDefaultLogger($myPsrLogger);

use Ysfkc\ClickHouse\QueryBuilder;

// All columns
$response = QueryBuilder::table('events')->get();

// Specific columns
$response = QueryBuilder::table('events')
    ->select(['user_id', 'event_type', 'created_at'])
    ->get();

// Aggregate functions and aliases
$response = QueryBuilder::table('events')
    ->select([
        'user_id',
        'event_type',
        'COUNT()          as total',
        'uniqExact(uuid)  as unique_users',
        'SUM(duration)    as total_duration',
        'toDate(created_at) as day',
    ])
    ->get();

$response = QueryBuilder::table('events')
    ->where('user_id',    '=', 123,     'Int32')
    ->where('event_type', '=', 'click', 'String')
    ->get();

// Allowed: =  !=  <>  >  <  >=  <=  LIKE  NOT LIKE  ILIKE  NOT ILIKE  IN  NOT IN
$response = QueryBuilder::table('events')
    ->where('user_id',      '>',    100,        'Int32')
    ->where('browser',      'LIKE', '%Chrome%', 'String')
    ->where('country_code', '!=',   'XX',       'String')
    ->get();

$response = QueryBuilder::table('events')
    ->where('event_type', 'IN',     ['click', 'scroll', 'view'], 'String')
    ->where('user_id',    'NOT IN', [0, -1],                     'Int32')
    ->get();

$response = QueryBuilder::table('events')
    ->whereBetween('event_date', '2026-01-01', '2026-03-31', 'Date')
    ->get();

// DateTime column
$response = QueryBuilder::table('events')
    ->whereBetween('created_at', '2026-01-01 00:00:00', '2026-01-31 23:59:59', 'DateTime')
    ->get();

QueryBuilder::table('events')->whereNull('referrer')->get();
QueryBuilder::table('events')->whereNotNull('utm_source')->get();

// ✅ Safe — no user input, column expression only
QueryBuilder::table('events')
    ->whereRaw('toYear(event_date) = toYear(today())')
    ->get();

// ✅ Safe — raw + parameterized
QueryBuilder::raw(
    'SELECT * FROM events WHERE toYear(event_date) = {yr:Int32}',
    ['yr' => 2026]
)->get();

// ❌ FORBIDDEN
QueryBuilder::table('events')
    ->whereRaw('user_id = ' . $userInput)
    ->get();

// INNER JOIN
$response = QueryBuilder::table('orders')
    ->select(['orders.user_id', 'users.name', 'COUNT() as cnt'])
    ->join('users', 'orders.user_id = users.id', 'INNER')
    ->where('orders.user_id', '=', 123, 'Int32')
    ->groupBy(['orders.user_id', 'users.name'])
    ->get();

// LEFT JOIN (shorthand)
$response = QueryBuilder::table('orders')
    ->select(['orders.uuid', 'sessions.started_at'])
    ->leftJoin('sessions', 'orders.session_id = sessions.id')
    ->get();

// ClickHouse ANY JOIN
$response = QueryBuilder::table('orders')
    ->join('sessions', 'orders.session_id = sessions.id', 'LEFT ANY')
    ->get();

// GROUP BY
$response = QueryBuilder::table('events')
    ->select(['user_id', 'event_type', 'COUNT() as cnt'])
    ->where('event_date', '>=', '2026-01-01', 'Date')
    ->groupBy(['user_id', 'event_type'])
    ->get();

// HAVING — parameterized (recommended)
$response = QueryBuilder::table('events')
    ->select(['user_id', 'COUNT() as cnt'])
    ->groupBy(['user_id'])
    ->having('cnt >', 100, 'Int64')   // → HAVING cnt > {having_0:Int64}
    ->get();

// HAVING — raw (injection-scanned)
$response = QueryBuilder::table('events')
    ->select(['user_id', 'COUNT() as cnt'])
    ->groupBy(['user_id'])
    ->having('cnt > 0')
    ->get();

$response = QueryBuilder::table('events')
    ->select(['user_id', 'event_date', 'event_type'])
    ->where('user_id', '=', 123, 'Int32')
    ->orderBy('event_date', 'DESC')
    ->orderBy('event_type', 'ASC')
    ->limit(50)
    ->offset(100)
    ->get();

$row = QueryBuilder::table('events')
    ->where('user_id', '=', 123, 'Int32')
    ->orderBy('event_date', 'DESC')
    ->first();  // array|null, LIMIT 1 added automatically

$total    = QueryBuilder::table('events')->where('user_id', '=', 123, 'Int32')->count();
$distinct = QueryBuilder::table('events')->where('user_id', '=', 123, 'Int32')->count('uuid');

$response = QueryBuilder::table('events')
    ->insertData([
        'uuid'       => ['value' => 'a1b2c3d4-...', 'type' => 'String'],
        'user_id'    => ['value' => 123,             'type' => 'Int32'],
        'event_type' => ['value' => 'click',         'type' => 'String'],
        'event_date' => ['value' => '2026-04-06',    'type' => 'Date'],
    ]);

$columns = ['uuid', 'user_id', 'event_type', 'event_date'];
$types   = ['String', 'Int32', 'String', 'Date'];
$rows    = [
    ['uuid-1', 123, 'click',  '2026-04-06'],
    ['uuid-2', 456, 'scroll', '2026-04-06'],
];

$response = QueryBuilder::table('events')
    ->insertBatch($columns, $rows, $types);

$response = QueryBuilder::raw(
    'SELECT
        toDate(event_date)  AS day,
        COUNT()             AS total,
        uniqExact(uuid)     AS unique_users
     FROM events
     WHERE user_id    = {userId:Int32}
       AND event_date BETWEEN {start:Date} AND {end:Date}
     GROUP BY day
     ORDER BY day ASC',
    ['userId' => 123, 'start' => '2026-01-01', 'end' => '2026-03-31']
)->get();

$debug = QueryBuilder::table('events')
    ->select(['user_id', 'COUNT() as cnt'])
    ->where('user_id', '=', 123, 'Int32')
    ->groupBy(['user_id'])
    ->toSql();

// [
//   'query'  => 'SELECT user_id, COUNT() as cnt FROM events WHERE user_id = {user_id_0:Int32} GROUP BY user_id',
//   'params' => ['user_id_0' => 123],
// ]

use Ysfkc\ClickHouse\Model\ClickHouseBaseModel;

class MetricSnapshot extends ClickHouseBaseModel
{
    protected static string $_tableName = 'metric_snapshots';

    protected static array $_columns = [
        'uuid'        => 'String',
        'source'      => 'LowCardinality(String)',
        'metric_name' => 'String',
        'value'       => 'Float64',
        'status'      => 'LowCardinality(String)',
        'event_date'  => 'Date',
        'recorded_at' => 'DateTime',
    ];

    public function getAverageBySource(string $startDate, string $endDate): array
    {
        return static::query()
            ->select(['source', 'AVG(value) as avg_value', 'COUNT() as cnt'])
            ->whereBetween('event_date', $startDate, $endDate, 'Date')
            ->groupBy(['source'])
            ->orderBy('avg_value', 'DESC')
            ->get()
            ->getData();
    }
}

$snapshots = MetricSnapshot::all();       // up to 1 000 rows (default)
$snapshots = MetricSnapshot::all(500);

foreach ($snapshots as $snapshot) {
    echo $snapshot->source;
    echo $snapshot->metricName;
    echo $snapshot->recordedAt;
}

// camelCase or snake_case keys are both accepted
$snapshots = MetricSnapshot::find(['source' => 'api']);

$snapshots = MetricSnapshot::find(
    conditions:       ['source' => 'api', 'status' => 'ok'],
    limit:            200,
    orderByColumn:    'recordedAt',
    orderByDirection: 'DESC'
);

// Array value → IN condition
$snapshots = MetricSnapshot::find(['status' => ['ok', 'warning', 'error']]);

$data = $snapshots->toArray();             // camelCase keys
$data = $snapshots->toArray(snake: true);  // snake_case keys

$snapshot = MetricSnapshot::findFirst(['source' => 'api', 'status' => 'error']);

if ($snapshot !== null) {
    echo $snapshot->uuid;
    $json = json_encode($snapshot); // logger/DI never leaks
}

$total    = MetricSnapshot::countBy(['source' => 'api']);
$filtered = MetricSnapshot::countBy(['source' => 'api', 'status' => 'error']);

$response = MetricSnapshot::query()
    ->select(['source', 'metric_name', 'AVG(value) as avg_value', 'COUNT() as cnt'])
    ->where('event_date', '>=', '2026-01-01', 'Date')
    ->where('event_date', '<=', '2026-03-31', 'Date')
    ->where('status',     '=',  'ok',         'LowCardinality(String)')
    ->groupBy(['source', 'metric_name'])
    ->orderBy('avg_value', 'DESC')
    ->limit(100)
    ->get();

$response = MetricSnapshot::queryBetweenDates('2026-01-01', '2026-03-31', 'event_date')
    ->select(['source', 'AVG(value) as avg_value'])
    ->where('status', '=', 'ok', 'LowCardinality(String)')
    ->groupBy(['source'])
    ->get();

$response = MetricSnapshot::insertRow([
    'uuid'        => 'a1b2c3d4-e5f6-...',
    'source'      => 'api',
    'metric_name' => 'response_time',
    'value'       => 142.5,
    'status'      => 'ok',
    'event_date'  => '2026-04-06',
    'recorded_at' => '2026-04-06 12:00:00',
]);

$columns = ['uuid', 'source', 'metric_name', 'value', 'status', 'event_date'];
$rows    = [
    ['uuid-1', 'api',  'response_time', 142.5, 'ok',      '2026-04-06'],
    ['uuid-2', 'web',  'load_time',     320.0, 'warning', '2026-04-06'],
    ['uuid-3', 'cron', 'job_duration',   58.3, 'ok',      '2026-04-06'],
];

// Types are resolved from $_columns automatically
$response = MetricSnapshot::insertBatch($columns, $rows);

// ✅ Correct
$response = MetricSnapshot::rawQuery(
    'SELECT toDate(recorded_at) AS day, source, AVG(value) AS avg_value
     FROM metric_snapshots
     WHERE status     = {status:String}
       AND event_date BETWEEN {start:Date} AND {end:Date}
     GROUP BY day, source ORDER BY avg_value DESC LIMIT {lim:Int32}',
    ['status' => 'ok', 'start' => '2026-01-01', 'end' => '2026-03-31', 'lim' => 50]
);

// ❌ Incorrect
$response = MetricSnapshot::rawQuery('SELECT * FROM metric_snapshots WHERE source = ' . $userInput);

MetricSnapshot::rawCommand('OPTIMIZE TABLE metric_snapshots FINAL');

MetricSnapshot::rawCommand(
    'ALTER TABLE metric_snapshots DROP PARTITION {part:String}',
    ['part' => '202601']
);

$response = MetricSnapshot::query()->where('status', '=', 'ok', 'LowCardinality(String)')->get();

$response->isSuccess();                 // bool
$response->getData();                   // array[] — all rows
$response->first();                     // array|null
$response->isEmpty();                   // bool
$response->count();                     // int
$response->pluck('metric_name');        // ['response_time', 'load_time', ...]
$response->getRowsRead();               // int
$response->getRowsBeforeLimitAtLeast(); // int — total before LIMIT
$response->getStatistics();             // ['elapsed' => ..., ...]
$response->getHttpStatus();             // int
$response->getRequestParameters();     // ['query' => '...', 'params' => [...]]

$snapshots = MetricSnapshot::find(['source' => 'api']);

count($snapshots);                       // int
$snapshots->isEmpty();                   // bool
$snapshots->first();                     // ClickHouseBaseModel|null

foreach ($snapshots as $snapshot) {
    echo $snapshot->metricName;
}

$snapshots[0]->uuid;
$snapshots->toArray();              // camelCase keys
$snapshots->toArray(snake: true);   // snake_case keys
json_encode($snapshots);            // logger/DI never leaks

use Ysfkc\ClickHouse\ClickHouseClientService;

$client = ClickHouseClientService::getInstance();

$response = $client->select(
    'SELECT source, AVG(value) as avg FROM metric_snapshots WHERE status = {s:String} GROUP BY source',
    ['s' => 'ok']
);

$response = $client->insert(
    'INSERT INTO metric_snapshots (uuid, source, metric_name, value, event_date) VALUES ({u:String},{s:String},{m:String},{v:Float64},{d:Date})',
    ['u' => 'some-uuid', 's' => 'api', 'm' => 'response_time', 'v' => 142.5, 'd' => '2026-04-06']
);

// SSRF/LFI/exfiltration vectors are automatically blocked
$response = $client->command('OPTIMIZE TABLE metric_snapshots FINAL');

// ✅ Preferred
->where('user_id', '=', $userId, 'Int32')

// ⚠️ Last resort — column expressions only, no user input
->whereRaw('toYear(event_date) = toYear(today())')

use Ysfkc\ClickHouse\Logger\PhalconLoggerAdapter;
use Ysfkc\ClickHouse\ClickHouseClientService;
use Ysfkc\ClickHouse\Model\ClickHouseBaseModel;

$adapter = new PhalconLoggerAdapter($di->get('logger'));

ClickHouseClientService::configure(
    $di->getConfig()->clickhouse->toArray(),
    $adapter
);

ClickHouseBaseModel::setDefaultLogger($adapter);

POST http://clickhouse:8123/?database=analytics&param_userId=123&default_format=JSON
Body: SELECT * FROM events WHERE user_id = {userId:Int32}