通过 
搜索
搜索xeops / dbal-clickhouse
Doctrine DBAL ClickHouse 驱动
v3.0.12
2024-01-17 07:33 UTC
Requires
- php: ^7.1
- ext-pcre: *
- ext-pdo: *
- doctrine/dbal: ^2.7
- doctrine/doctrine-bundle: ^1.6
- doctrine/doctrine-cache-bundle: ^1.2
- doctrine/orm: ^2.5
- smi2/phpclickhouse: 1.5.1
Requires (Dev)
- doctrine/coding-standard: ^4.0
- phpunit/phpunit: ^7.0
README
Doctrine DBAL ClickHouse 驱动
Doctrine DBAL 驱动用于 ClickHouse -- 由 Yandex 开发的开源列式数据库管理系统 (https://clickhouse.yandex/)
驱动适用于 Symfony 或任何使用 Doctrine 的其他框架。
安装
composer require friendsofdoctrine/dbal-clickhouse
初始化
自定义 PHP 脚本
$connectionParams = [ 'host' => 'localhost', 'port' => 8123, 'user' => 'default', 'password' => '', 'dbname' => 'default', 'driverClass' => 'FOD\DBALClickHouse\Driver', 'wrapperClass' => 'FOD\DBALClickHouse\Connection', 'driverOptions' => [ 'extremes' => false, 'readonly' => true, 'max_execution_time' => 30, 'enable_http_compression' => 0, 'https' => false, ], ]; $conn = \Doctrine\DBAL\DriverManager::getConnection($connectionParams, new \Doctrine\DBAL\Configuration());
driverOptions 是特殊的 smi2/phpclickhouse 客户端 设置
Symfony
配置...
# app/config/config.yml # Doctrine Configuration doctrine: dbal: default_connection: default connections: # mysql as default default: driver: pdo_mysql host: "%database_host%" port: "%database_port%" dbname: "%database_name%" user: "%database_user%" password: "%database_password%" charset: UTF8 mapping_types: enum: string clickhouse: host: "%clickhouse_host%" port: "%clickhouse_port%" user: "%clickhouse_user%" password: "%clickhouse_password%" dbname: "%clickhouse_dbname%" driver_class: FOD\DBALClickHouse\Driver wrapper_class: FOD\DBALClickHouse\Connection types: array(int8): FOD\DBALClickHouse\Types\ArrayInt8Type array(int16): FOD\DBALClickHouse\Types\ArrayInt16Type array(int32): FOD\DBALClickHouse\Types\ArrayInt32Type array(int64): FOD\DBALClickHouse\Types\ArrayInt64Type array(uint8): FOD\DBALClickHouse\Types\ArrayUInt8Type array(uint16): FOD\DBALClickHouse\Types\ArrayUInt16Type array(uint32): FOD\DBALClickHouse\Types\ArrayUInt32Type array(uint64): FOD\DBALClickHouse\Types\ArrayUInt64Type array(float32): FOD\DBALClickHouse\Types\ArrayFloat32Type array(float64): FOD\DBALClickHouse\Types\ArrayFloat64Type array(string): FOD\DBALClickHouse\Types\ArrayStringType array(datetime): FOD\DBALClickHouse\Types\ArrayDateTimeType array(date): FOD\DBALClickHouse\Types\ArrayDateType date_id: FOD\DBALClickHouse\Types\DateIdType # ovverided type for unique hash float: FOD\DBALClickHouse\Types\FloatType # type dismatch, because standart driver set float to string orm: auto_generate_proxy_classes: "%kernel.debug%" default_entity_manager: default entity_managers: default: connection: default naming_strategy: doctrine.orm.naming_strategy.underscore auto_mapping: true dql: datetime_functions: date_format: DoctrineExtensions\Query\Mysql\DateFormat clickhouse: connection: clickhouse naming_strategy: doctrine.orm.naming_strategy.underscore mappings: AcmeBundle: type: annotation
...并从服务容器中获取
$conn = $this->get('doctrine.dbal.clickhouse_connection');
使用
创建新表
// ***quick start*** $fromSchema = $conn->getSchemaManager()->createSchema(); $toSchema = clone $fromSchema; // create new table object $newTable = $toSchema->createTable('new_table'); // add columns $newTable->addColumn('id', 'integer', ['unsigned' => true]); $newTable->addColumn('payload', 'string', ['notnull' => false]); // *option 'notnull' in false mode allows you to insert NULL into the column; // in this case, the column will be represented in the ClickHouse as Nullable(String) $newTable->addColumn('hash', 'string', ['length' => 32, 'fixed' => true]); // *option 'fixed' sets the fixed length of a string column as specified; // if specified, the type of the column is FixedString //set primary key $newTable->setPrimaryKey(['id']); // execute migration SQLs to create table in ClickHouse $sqlArray = $fromSchema->getMigrateToSql($toSchema, $conn->getDatabasePlatform()); foreach ($sqlArray as $sql) { $conn->exec($sql); }
// ***more options (optional)*** //specify table engine $newTable->addOption('engine', 'MergeTree'); // *if not specified -- default engine 'ReplacingMergeTree' will be used // add Date column for partitioning $newTable->addColumn('event_date', 'date', ['default' => 'toDate(now())']); $newTable->addOption('eventDateColumn', 'event_date'); // *if not specified -- default Date column named EventDate will be added $newTable->addOption('eventDateProviderColumn', 'updated_at'); // *if specified -- event date column will be added with default value toDate(updated_at); // if the type of the provider column is `string`, the valid format of provider column values must be either `YYYY-MM-DD` or `YYYY-MM-DD hh:mm:ss` // if the type of provider column is neither `string`, nor `date`, nor `datetime`, provider column values must contain a valid UNIX Timestamp $newTable->addOption('samplingExpression', 'intHash32(id)'); // samplingExpression -- a tuple that defines the table's primary key, and the index granularity //specify index granularity $newTable->addOption('indexGranularity', 4096); // *if not specified -- default value 8192 will be used
插入
// 1 $conn->exec("INSERT INTO new_table (id, payload) VALUES (1, 'dummyPayload1')");
// 2 $conn->insert('new_table', ['id' => 2, 'payload' => 'dummyPayload2']); // INSERT INTO new_table (id, payload) VALUES (?, ?) [2, 'dummyPayload2']
// 3 via QueryBuilder $qb = $conn->createQueryBuilder(); $qb ->insert('new_table') ->setValue('id', ':id') ->setValue('payload', ':payload') ->setParameter('id', 3, \PDO::PARAM_INT) // need to explicitly set param type to `integer`, because default type is `string` and ClickHouse doesn't like types mismatchings ->setParameter('payload', 'dummyPayload3'); $qb->execute();
// 4 via entity manager $orm = $this->getContainer()->get('doctrine.orm.clickhouse_entity_manager'); $new_object = new \AcmeBundle\Entity\AcmeTable(); $new_object->setId(44)->setDate(new \DateTimeToString()); $orm->persist($new_object); $orm->flush();
选择
$orm = $this->getContainer()->get('doctrine.orm.clickhouse_entity_manager'); $repo = $orm->getRepository('AcmeBundle:AcmeTable'); $object = $repo->findOneBy(['id' => 45, 'date' => new \DateTime('2019-02-07')]); echo $output->getDate();
通过动态参数和预处理语句选择
$stmt = $conn->prepare('SELECT authorId, SUM(views) AS total_views FROM articles WHERE category_id = :categoryId AND publish_date = :publishDate GROUP BY authorId'); $stmt->bindValue('categoryId', 123); $stmt->bindValue('publishDate', new \DateTime('2017-02-29'), 'datetime'); $stmt->execute(); while ($row = $stmt->fetch()) { echo $row['authorId'] . ': ' . $row['total_views'] . PHP_EOL; }
附加类型
如果您想使用 Array(T) 类型，请在您的代码中注册额外的 DBAL 类型
// register all custom DBAL Array types ArrayType::registerArrayTypes($conn->getDatabasePlatform()); // register one custom DBAL Array(Int8) type Type::addType('array(int8)', 'FOD\DBALClickHouse\Types\ArrayInt8Type');
或在 Symfony 配置文件中注册它们
# app/config/config.yml doctrine: dbal: connections: ... types: array(int8): FOD\DBALClickHouse\Types\ArrayInt8Type array(int16): FOD\DBALClickHouse\Types\ArrayInt16Type array(int32): FOD\DBALClickHouse\Types\ArrayInt32Type array(int64): FOD\DBALClickHouse\Types\ArrayInt64Type array(uint8): FOD\DBALClickHouse\Types\ArrayUInt8Type array(uint16): FOD\DBALClickHouse\Types\ArrayUInt16Type array(uint32): FOD\DBALClickHouse\Types\ArrayUInt32Type array(uint64): FOD\DBALClickHouse\Types\ArrayUInt64Type array(float32): FOD\DBALClickHouse\Types\ArrayFloat32Type array(float64): FOD\DBALClickHouse\Types\ArrayFloat64Type array(string): FOD\DBALClickHouse\Types\ArrayStringType array(datetime): FOD\DBALClickHouse\Types\ArrayDateTimeType array(date): FOD\DBALClickHouse\Types\ArrayDateType date_id: FOD\DBALClickHouse\Types\DateIdType # ovverided type for unique hash float: FOD\DBALClickHouse\Types\FloatType # type dismatch, because standart driver set float to string
附加类型 BigIntType 帮助您将 bigint 值存储为 ClickHouse 中的 Int64/UInt64 值类型。您可以在代码中覆盖 DBAL 类型
Type::overrideType(Type::BIGINT, 'FOD\DBALClickHouse\Types\BigIntType');
或在使用 Symfony 配置自定义映射类型
# app/config/config.yml doctrine: dbal: types: bigint: FOD\DBALClickHouse\Types\BigIntType ...
更多信息请参阅 Doctrine DBAL 文档
ClickHouse Bundle
用于处理 Yandex ClickHouse 库的 Bundle
Bundle 的主要需求
- 能够操作 ClickHouse 表和 dbal-clickhouse 库
- 能够设置类似于 ORM/Doctrine 的注释
- 能够从注释中创建统一模式的表
- 能够在不指定显式查询的情况下插入和选择数据
表格注释
/** * Class EventTable * @package iikoEventsBundle\Model * @ORM\Table(name="event_log", schema="ReplacingMergeTree") */ class EventTable extends ClickHouseTableBase { /** * @ORM\Column(name="id", type="guid",unique=true) * @var string */ private $id;
unique=true 用于表示主键。ClickHouse 中没有唯一字段