海量数据分析遇瓶颈？Composer助你用DoctrineDBAL轻松驾驭ClickHouse！

<code class="bash">composer require friendsofdoctrine/dbal-clickhouse</code>

<code class="php"><?php

require 'vendor/autoload.php';

use Doctrine\DBAL\DriverManager;
use Doctrine\DBAL\Configuration;
use FOD\DBALClickHouse\Driver;
use FOD\DBALClickHouse\Connection;

$connectionParams = [
    'host' => 'localhost',
    'port' => 8123,
    'user' => 'default',
    'password' => '',
    'dbname' => 'default',
    'driverClass' => Driver::class, // 指定ClickHouse驱动类
    'wrapperClass' => Connection::class, // 指定ClickHouse连接包装类
    'driverOptions' => [ // ClickHouse客户端特定选项
        'extremes' => false,
        'readonly' => true,
        'max_execution_time' => 30,
        'enable_http_compression' => 1, // 开启HTTP压缩
        'https' => false,
    ],
];

$conn = DriverManager::getConnection($connectionParams, new Configuration());

try {
    $result = $conn->fetchColumn('SELECT 1');
    echo "成功连接到ClickHouse: " . $result . PHP_EOL;
} catch (\Exception $e) {
    echo "连接ClickHouse失败: " . $e->getMessage() . PHP_EOL;
}
</code>

<code class="yaml"># config/packages/doctrine.yaml
doctrine:
    dbal:
        connections:
            clickhouse: # 定义一个名为clickhouse的连接
                host:     localhost
                port:     8123
                user:     default
                password: ""
                dbname:   default
                driver_class: FOD\DBALClickHouse\Driver
                wrapper_class: FOD\DBALClickHouse\Connection
                options:
                    enable_http_compression: 1
                    max_execution_time: 60
                    # sslCA: '/usr/local/share/ca-certificates/Yandex/YandexInternalRootCA.crt' # 如果使用HTTPS，需要配置SSL证书</code>

<code class="php"><?php

// 在一个Symfony控制器或服务中
use Doctrine\DBAL\Connection;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\Response;

class MyController extends AbstractController
{
    public function index(): Response
    {
        /** @var Connection $clickhouseConn */
        $clickhouseConn = $this->container->get('doctrine.dbal.clickhouse_connection');

        try {
            $result = $clickhouseConn->fetchColumn('SELECT count() FROM my_log_table');
            return new Response("ClickHouse中的日志总数: " . $result);
        } catch (\Exception $e) {
            return new Response("查询ClickHouse失败: " . $e->getMessage(), 500);
        }
    }
}</code>

<code class="php"><?php

// ... (获取$conn连接对象) ...

$fromSchema = $conn->getSchemaManager()->createSchema();
$toSchema = clone $fromSchema;

$newTable = $toSchema->createTable('my_analytics_data');
$newTable->addColumn('id', 'integer', ['unsigned' => true]);
$newTable->addColumn('event_date', 'date', ['default' => 'toDate(now())']); // ClickHouse分区日期
$newTable->addColumn('user_id', 'integer');
$newTable->addColumn('action', 'string', ['length' => 50]);
$newTable->addColumn('timestamp', 'datetime');

$newTable->setPrimaryKey(['id']);
// 为ClickHouse指定MergeTree引擎和分区键
$newTable->addOption('engine', 'MergeTree');
$newTable->addOption('eventDateColumn', 'event_date'); // 指定日期列用于分区
$newTable->addOption('samplingExpression', 'intHash32(user_id)'); // 采样表达式
$newTable->addOption('indexGranularity', 8192); // 索引粒度

$sqlArray = $fromSchema->getMigrateToSql($toSchema, $conn->getDatabasePlatform());
foreach ($sqlArray as $sql) {
    echo "执行SQL: " . $sql . PHP_EOL;
    $conn->exec($sql);
}

echo "表 'my_analytics_data' 创建成功或已存在。" . PHP_EOL;</code>

<code class="php"><?php

// ... (获取$conn连接对象) ...

// 方式一：直接执行SQL
$conn->exec("INSERT INTO my_analytics_data (id, event_date, user_id, action, timestamp) VALUES (1, '2023-10-26', 101, 'view_product', now())");

// 方式二：使用DBAL的insert方法
$conn->insert('my_analytics_data', [
    'id' => 2,
    'event_date' => new \DateTime('2023-10-26'),
    'user_id' => 102,
    'action' => 'add_to_cart',
    'timestamp' => new \DateTime(),
], [
    \PDO::PARAM_INT, // id
    'date',          // event_date
    \PDO::PARAM_INT, // user_id
    \PDO::PARAM_STR, // action
    'datetime',      // timestamp
]);

// 方式三：使用QueryBuilder
$qb = $conn->createQueryBuilder();
$qb
    ->insert('my_analytics_data')
    ->setValue('id', ':id')
    ->setValue('event_date', ':event_date')
    ->setValue('user_id', ':user_id')
    ->setValue('action', ':action')
    ->setValue('timestamp', ':timestamp')
    ->setParameter('id', 3, \PDO::PARAM_INT)
    ->setParameter('event_date', new \DateTime('2023-10-26'), 'date')
    ->setParameter('user_id', 103, \PDO::PARAM_INT)
    ->setParameter('action', 'checkout', \PDO::PARAM_STR)
    ->setParameter('timestamp', new \DateTime(), 'datetime');

$qb->execute();

echo "数据插入成功。" . PHP_EOL;</code>

<code class="php"><?php

// ... (获取$conn连接对象) ...

// 简单查询
$totalActions = $conn->fetchColumn('SELECT count() FROM my_analytics_data');
echo "总操作数: " . $totalActions . PHP_EOL;

// 带参数的预处理查询
$stmt = $conn->prepare('SELECT user_id, count() AS total_actions FROM my_analytics_data WHERE event_date = :date GROUP BY user_id ORDER BY total_actions DESC LIMIT 5');
$stmt->bindValue('date', new \DateTime('2023-10-26'), 'date');
$stmt->execute();

echo "2023-10-26 最活跃的用户：" . PHP_EOL;
while ($row = $stmt->fetchAssociative()) {
    echo "用户ID: " . $row['user_id'] . ", 操作数: " . $row['total_actions'] . PHP_EOL;
}</code>

<code class="php"><?php
use Doctrine\DBAL\Types\Type;
use FOD\DBALClickHouse\Types\ArrayInt32Type;
use FOD\DBALClickHouse\Types\BigIntType;

// 注册自定义数组类型
Type::addType('array(int32)', ArrayInt32Type::class);
// 覆盖默认的bigint类型以更好地兼容ClickHouse的Int64/UInt64
Type::overrideType(Type::BIGINT, BigIntType::class);

// ... (获取$conn连接对象) ...
// 现在你可以在你的Schema定义中使用 'array(int32)' 和 'bigint' 类型了</code>