PHPackages xeops/dbal-clickhouse - PHPackages - PHPackages [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register) 1. [Directory](/) 2. / 3. [Database & ORM](/categories/database) 4. / 5. xeops/dbal-clickhouse ActiveLibrary[Database & ORM](/categories/database) xeops/dbal-clickhouse ===================== Doctrine DBAL driver for ClickHouse v3.0.13(1y ago)22.2k↓100%4MITPHPPHP ^7.1 Since Mar 20Pushed 1y ago1 watchersCompare [ Source](https://github.com/xeops/dbalClickhouse)[ Packagist](https://packagist.org/packages/xeops/dbal-clickhouse)[ Docs](https://github.com/xeops/dbal-clickhouse)[ RSS](/packages/xeops-dbal-clickhouse/feed)WikiDiscussions master Synced 1mo ago READMEChangelog (10)Dependencies (7)Versions (33)Used By (0) [![Build Status](https://camo.githubusercontent.com/29a3ef52c59cb844b368d7ee7d68a69ac899cf6ff7863c383f799cbb6ea25dd4/68747470733a2f2f7472617669732d63692e6f72672f467269656e64734f66446f637472696e652f6462616c2d636c69636b686f7573652e7376673f6272616e63683d6d6173746572)](https://travis-ci.org/FriendsOfDoctrine/dbal-clickhouse)[![Total Downloads](https://camo.githubusercontent.com/bff4f611cfc01d3e467031b891d150305c1a80439b381e369a1eb0fee0cade85/68747470733a2f2f706f7365722e707567782e6f72672f667269656e64736f66646f637472696e652f6462616c2d636c69636b686f7573652f642f746f74616c2e737667)](https://packagist.org/packages/friendsofdoctrine/dbal-clickhouse)[![Latest Stable Version](https://camo.githubusercontent.com/eebf9523a702dda2a88106f87e1a7eebd0a35250988aa0f52d4ae50ae420f49d/68747470733a2f2f706f7365722e707567782e6f72672f667269656e64736f66646f637472696e652f6462616c2d636c69636b686f7573652f762f737461626c652e737667)](https://packagist.org/packages/friendsofdoctrine/dbal-clickhouse)[![License](https://camo.githubusercontent.com/c30d7ed9c56d33c9f5961ff4de69bb36cc2aae42edef681c2d499440e821f390/68747470733a2f2f706f7365722e707567782e6f72672f667269656e64736f66646f637472696e652f6462616c2d636c69636b686f7573652f6c6963656e73652e737667)](https://packagist.org/packages/friendsofdoctrine/dbal-clickhouse) Doctrine DBAL ClickHouse Driver =============================== [](#doctrine-dbal-clickhouse-driver) Doctrine DBAL driver for ClickHouse -- an open-source column-oriented database management system by Yandex () Driver is suitable for Symfony or any other framework using Doctrine. Installation ------------ [](#installation) ``` composer require friendsofdoctrine/dbal-clickhouse ``` Initialization -------------- [](#initialization) ### Custom PHP script [](#custom-php-script) ``` $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` are special `smi2/phpclickhouse` client [settings](https://github.com/smi2/phpClickHouse#settings) ### Symfony [](#symfony) configure... ``` # 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 ``` ...and get from the service container ``` $conn = $this->get('doctrine.dbal.clickhouse_connection'); ``` Usage ----- [](#usage) ### Create new table [](#create-new-table) ``` // ***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 ``` ### Insert [](#insert) ``` // 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(); ``` ### Select [](#select) ``` $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(); ``` ### Select via Dynamic Parameters and Prepared Statements [](#select-via-dynamic-parameters-and-prepared-statements) ``` $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; } ``` ### Additional types [](#additional-types) If you want to use [Array(T) type](https://clickhouse.yandex/reference_en.html#Array(T)), register additional DBAL types in your code: ``` // 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'); ``` or register them in Symfony configuration file: ``` # 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 ``` Additional type `BigIntType` helps you to store bigint values as [Int64/UInt64](https://clickhouse.yandex/reference_en.html#UInt8,%20UInt16,%20UInt32,%20UInt64,%20Int8,%20Int16,%20Int32,%20Int64) value type in ClickHouse. You can override DBAL type in your code: ``` Type::overrideType(Type::BIGINT, 'FOD\DBALClickHouse\Types\BigIntType'); ``` or use custom mapping types in Symfony configuration: ``` # app/config/config.yml doctrine: dbal: types: bigint: FOD\DBALClickHouse\Types\BigIntType ... ``` ### More information in Doctrine DBAL documentation: [](#more-information-in-doctrine-dbal-documentation) - [Data Retrieval And Manipulation](http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/data-retrieval-and-manipulation.html) - [SQL Query Builder](http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/query-builder.html) - [Schema-Representation](http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/schema-representation.html) ClickHouse Bundle ================= [](#clickhouse-bundle) бандл для работы с библиотекой Yandex ClickHouse ### Основные потребности в бандле [](#основные-потребности-в-бандле) - возможность работы с таблицами ClickHouse и библиотекой [dbal-clickhouse](https://github.com/FriendsOfDoctrine/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 нет уникальных полей ### Health Score 40 — FairBetter than 88% of packages Maintenance42 Moderate activity, may be stable Popularity22 Limited adoption so far Community14 Small or concentrated contributor base Maturity69 Established project with proven stability Bus Factor1 Top contributor holds 70.6% of commits — single point of failure How is this calculated?**Maintenance (25%)** — Last commit recency, latest release date, and issue-to-star ratio. Uses a 2-year decay window. **Popularity (30%)** — Total and monthly downloads, GitHub stars, and forks. Logarithmic scaling prevents top-heavy scores. **Community (15%)** — Contributors, dependents, forks, watchers, and maintainers. Measures real ecosystem engagement. **Maturity (30%)** — Project age, version count, PHP version support, and release stability. ### Release Activity Cadence Every ~95 days Recently: every ~436 days Total 31 Last Release 480d ago Major Versions 0.2.2 → 1.0.02017-04-17 v1.5.2 → v2.02018-10-19 v2.2 → v3.0.02019-02-18 PHP version history (2 changes)0.1.2PHP >=5.4.0 v1.2.0PHP ^7.1 ### Community Maintainers ![](https://www.gravatar.com/avatar/76201bdc5e3cc7dd0b6c5c412f0c13841f781006e7011b99e82f68ebf7924fcb?d=identicon)[xeops](/maintainers/xeops) --- Top Contributors [![mochalygin](https://avatars.githubusercontent.com/u/2054744?v=4)](https://github.com/mochalygin "mochalygin (115 commits)")[![argayash](https://avatars.githubusercontent.com/u/790535?v=4)](https://github.com/argayash "argayash (46 commits)")[![Vasary](https://avatars.githubusercontent.com/u/2060334?v=4)](https://github.com/Vasary "Vasary (1 commits)")[![xeops](https://avatars.githubusercontent.com/u/9568398?v=4)](https://github.com/xeops "xeops (1 commits)") --- Tags doctrinedbaldriverclickhouse ### Code Quality TestsPHPUnit ### Embed Badge ![Health badge](/badges/xeops-dbal-clickhouse/health.svg) ``` [![Health](https://phpackages.com/badges/xeops-dbal-clickhouse/health.svg)](https://phpackages.com/packages/xeops-dbal-clickhouse) ``` ### Alternatives [friendsofdoctrine/dbal-clickhouse Doctrine DBAL driver for ClickHouse 1141.2M1](/packages/friendsofdoctrine-dbal-clickhouse)[damienharper/auditor-bundle Integrate auditor library in your Symfony projects. 4542.8M](/packages/damienharper-auditor-bundle)[fresh/doctrine-enum-bundle Provides support of ENUM type for Doctrine2 in Symfony applications. 4636.8M12](/packages/fresh-doctrine-enum-bundle)[nemo64/dbal-rds-data rds-data driver for doctrine dbal 2713.2k](/packages/nemo64-dbal-rds-data) PHPackages © 2026 [Directory](/)[Categories](/categories)[Trending](/trending)[Changelog](/changelog)[Analyze](/analyze)