README

Doctrine DBAL ClickHouse Driver

Doctrine DBAL driver for ClickHouse -- an open-source column-oriented database management system by Yandex (https://clickhouse.yandex/)

Driver is suitable for Symfony or any other framework using Doctrine.

Installation

composer require friendsofdoctrine/dbal-clickhouse

Initialization

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

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

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

// 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

$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

$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

If you want to use Array(T) type, 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 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:

• Data Retrieval And Manipulation
• SQL Query Builder
• Schema-Representation

ClickHouse Bundle

бандл для работы с библиотекой Yandex ClickHouse

Основные потребности в бандле

• возможность работы с таблицами 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 нет уникальных полей