本文介绍如何在 PHP 开发环境连接并访问 ByteHouse 云数仓。

获取ClickHouse驱动后，可以通过composer进行安装：

composer require smi2/phpclickhouse

可参考下面代码样例，通过PHP驱动连接ByteHouse。

<?php
// Include the ClickHouse PHP driver
require_once __DIR__ . '/../vendor/autoload.php';

// ByteHouse connection configuration
$config = [
    // ByteHouse gateway host
    'host' => 'tenant-{TENANT_ID}-{REGION}-public.bytehouse.volces.com',

    // ByteHouse HTTP port
    'port' => '8123',

    // Your database name
    'database' => 'your_database',

    // ByteHouse uses fixed username 'bytehouse'
    'username' => 'bytehouse',

    // Your ByteHouse API key
    'password' => 'your_api_key',

    // HTTPS is required for ByteHouse
    'https' => true,
   
    // use settings()->set()
    $db->settings()->set('send_timeout', (int)300); // Socket发送超时时间（单位：秒）
    $db->settings()->set('receive_timeout', (int)300); // Socket发接收时时间（单位：秒）
    $db->settings()->set('virtual_warehouse', 'virtual_warehouse id'); // 指定计算组id
    $db->setTimeout(300); // 查询超时时间（单位：秒）
    $db->setConnectTimeOut(5); // 连超时时间（单位：秒）
];

try {
    // Initialize the client
    $db = new ClickHouseDB\Client($config);

    // Test connection with ping
    $db->ping();
    echo "Successfully connected to ByteHouse!\n";

    // Set the working database (optional if specified in config)
    $db->database('your_database');

    $result = $db->select('SELECT 1');
    print_r($result->rows());

} catch (\Exception $e) {
    echo "Connection failed: " . $e->getMessage() . "\n";
}

连接ByteHouse后，您可以通过ClickHouse Rust (HTTP) Driver进行建表、数据读写等操作，以下为简单的操作示例。

示例1：建表、数据读写

<?php
require_once __DIR__ . '/../vendor/autoload.php';

$config = [
    // Fill in ByteHouse connection configuration
];

try {
    $db = new ClickHouseDB\Client($config);
    $db->ping();

    // Create table
    $db->write('DROP TABLE IF EXISTS demo_table');
    $db->write('
        CREATE TABLE demo_table (
            event_date Date,
            user_id UInt32,
            message String
        ) ENGINE = CnchMergeTree ORDER BY tuple()
    ');

    // Insert data
    $db->insert('demo_table', [
        [date('Y-m-d'), 1001, 'Hello ByteHouse'],
        [date('Y-m-d'), 1002, 'Another row'],
    ], ['event_date', 'user_id', 'message']);

    // Select data
    $result = $db->select('SELECT * FROM demo_table');
    print_r($result->rows());

} catch (\Exception $e) {
    echo "Error: " . $e->getMessage() . "\n";
}

示例2：通过CSV文件批量写入数据

您可以使用insertBatchFiles()来并行插入多个CSV文件，可以有效地将数据批量加载到ByteHouse。

<?php
require_once __DIR__ . '/../vendor/autoload.php';

$config = [
    // Fill in ByteHouse connection configuration
];

// Helper function to generate CSV files with demo data
function generateDemoCSV($filename, $numRows): void
{
    $fp = fopen($filename, 'w');
    for ($i = 0; $i < $numRows; $i++) {
        fputcsv($fp, [
            date('Y-m-d'), // event_date
            rand(1000, 9999), // user_id
            'Message ' . $i, // message
            rand(1, 100) // value
        ], ',', '"', "\\", "\n");
    }
    fclose($fp);
    echo "Generated $numRows rows in $filename\n";
}

try {
    $db = new ClickHouseDB\Client($config);

    $db->write('DROP TABLE IF EXISTS demo_table');
    $db->write('
        CREATE TABLE demo_table (
            event_date Date,
            user_id UInt32,
            message String,
            value UInt32
        ) ENGINE = CnchMergeTree ORDER BY tuple()
    ');

    // Generate CSV files
    $files = [
        __DIR__ . '/demo_data_1.csv' => 1000,
        __DIR__ . '/demo_data_2.csv' => 1000
    ];

    foreach ($files as $file => $rows) {
        generateDemoCSV($file, $rows);
    }

    // Insert data via Batch Operation
    $result = $db->insertBatchFiles(
        'demo_table',
        array_keys($files),
        ['event_date', 'user_id', 'message', 'value']
    );

    $totalCount = $db->select('SELECT count() as count FROM demo_table')->fetchOne('count');
    echo "Total rows: $totalCount\n";

} catch (\Exception $e) {
    echo "Error: " . $e->getMessage() . "\n";
}

示例3：参数绑定

当前支持通过':param'语法、'{param}'语法，使用占位符安全地将参数值传递给查询，示例如下。

<?php
require_once __DIR__ . '/../vendor/autoload.php';

$config = [
    // Fill in ByteHouse connection configuration
];

$db = new ClickHouseDB\Client($config);

try {
    // Create a sample table
    $db->write('DROP TABLE IF EXISTS user_events');
    $db->write('
        CREATE TABLE user_events (
            event_date Date,
            event_type String,
            user_id UInt32,
            score Float32,
            tags Array(String)
        ) ENGINE = CnchMergeTree ORDER BY tuple()
    ');

    // Insert sample data
    $db->insert('user_events', [
        [
            '2024-01-01', 'login', 101, 0.5, ['new', 'mobile']
        ],
        [
            '2024-01-01', 'purchase', 102, 0.8, ['vip', 'desktop']
        ],
        [
            '2024-01-02', 'login', 101, 0.3, ['mobile']
        ]
    ], [
        'event_date', 'event_type', 'user_id', 'score', 'tags'
    ]);

    // Example 1: Basic parameter binding
    $result = $db->select(
        'SELECT * FROM user_events WHERE user_id = :id',
        ['id' => 101]
    );
    echo "Events for user 101:\n";
    print_r($result->rows());

    // Example 2: Multiple parameter binding
    $result = $db->select(
        'SELECT * FROM user_events 
         WHERE event_date = :date AND event_type = :type',
        [
            'date' => '2024-01-01',
            'type' => 'login'
        ]
    );
    echo "\nLogin events on 2024-01-01:\n";
    print_r($result->rows());

    // Example 3: Array binding
    $result = $db->select(
        'SELECT * FROM user_events 
         WHERE user_id IN (:users) AND score >= :min_score',
        [
            'users' => [101, 102],
            'min_score' => 0.5
        ]
    );
    echo "\nHigh-scoring events for selected users:\n";
    print_r($result->rows());

    // Example 4: Template binding for column selection
    $columns = 'event_date, event_type, user_id';
    $result = $db->select(
        'SELECT {cols} FROM user_events WHERE score > :score',
        [
            'cols' => $columns,
            'score' => 0.4
        ]
    );
    echo "\nSelected columns for high scores:\n";
    print_r($result->rows());

} catch (\Exception $e) {
    echo "Error: " . $e->getMessage() . "\n";
}