imjonos/clickhouse-migrations

PHP Clickhouse Migrations

dev-master 2023-08-22 07:57 UTC

Requires

• php: ^8.1
• imjonos/db-adapters: dev-master

MIT

• Eugeny Nosenko

• migrations
• php
• db
• mysql
• pdo
• clickhouse
• Clickhouse migrations

README

Simple PHP Clickhouse migrations
Supported 2 connection types:
PDO - \Nos\DbAdapters\MysqlDbClientAdapter::class
HTTP (smi2/phpclickhouse) - \Nos\DbAdapters\ClickHouseDbClientAdapter::class

Installation

Via Composer

$ composer require imjonos/clickhouse-migrations

How to use

1. Copy to the {ROOT_OF_THE_PROJECT}:
   {ROOT_OF_THE_PROJECT}/config/clickhouse-migrations.php - config file
   {ROOT_OF_THE_PROJECT}/database/migrations/ - migrations dir
   {ROOT_OF_THE_PROJECT}/database/clickhouse_migrations.log - migrations log file
   
   
2. Run
   

$ php ./vendor/bin/clickhouse-migrate [force]

Config example:

return [
    'adapter' => \Nos\DbAdapters\ClickHouseDbClientAdapter::class, // \Nos\DbAdapters\MysqlDbClientAdapter::class
    'host' => 'clickhouse',
    'port' => 8123,
    'database' => 'test',
    'username' => 'default',
    'password' => '',
    'dir' => __DIR__ . '/../database/migrations',
    'history_file' => __DIR__ . '/../database/clickhouse_migrations.log',
    'bindings' => ['redpanda:9092' => '{KAFKA_BROKER_LIST}', 'local_' => '{APP_ENV}']
];

Migration file example:

CREATE TABLE IF NOT EXISTS users_queue
(
user_id    UInt64,
name       String,
created_at DATETIME
) ENGINE = Kafka SETTINGS
kafka_broker_list = '{KAFKA_BROKER_LIST}',
kafka_topic_list = '{APP_ENV}users',
kafka_group_name = '{APP_ENV}clickhouse-group-users',
kafka_max_block_size = 1048576,
kafka_format = 'JSONEachRow';

CREATE TABLE IF NOT EXISTS users
(
user_id    UInt64,
name       String,
created_at DATETIME
) ENGINE = ReplacingMergeTree
PRIMARY KEY (user_id)
ORDER BY (user_id);

CREATE MATERIALIZED VIEW users_mv TO users
AS
SELECT *
FROM users_queue;

https://github.com/imjonos/clickhouse-migrations/blob/master/database/migrations/1_create_users_table.sql

Contributing

Please see contributing.md for details and a todolist.

License

license. Please see the license file for more information.