Replication from MySQL to ClickHouse

Step-1

How to setup clickhouse on ubuntu bionic

MySQLdb package is used for communication with MySQL:

mysql-replication package is used for communication with MySQL also: https://github.com/noplay/python-mysql-replication

clickhouse-driver package is used for communication with ClickHouse: https://github.com/mymarilyn/clickhouse-driver

Step-2

Starting clickhouse server

Step-3

How to generate table and ingress data into clickhouse

SQL Commands are generally same in clickhouse and mysql so it will be informal for a MySQL related person to query exclusive clickhouse CREATE TABLE, CREATE DATABASE, SHOW TABLES, SHOW DATABASES, USE DBNAME

Here are the specifics of SQL orientations in clickhouse https://clickhouse.yandex/docs/en/query_language/select/

Here is the command to create table in clickhouse

MATERIALIZED is a way to generate to column that is repeatedly set when a row is inserted built on the function after the keyword. In this circumstance, the function I selected to use is now(), in command to get the insertion timestamp for each row.

Here you can get the data to insert into this table

Some queries to understand the data and what we can do with it using data aggregation functions.

Data replication from mysql to clickhouse

MySQL setup

Also the following (at least one of) MySQL privileges are required for this operation:

SUPER, REPLICATION CLIENT

Also the following MySQL config options are required:

Run data reader as following:

Expected results are:

Automatically create target table in ClickHouse (if possible)

migrate existing data from MySQL to ClickHouse

after migration completed, listen for new events to come and pump data from MySQL into ClickHouse

Options description

• --src-server-id - Master's server id

• --src-wait - wait for new data to come

• --nice-pause=1 - when no data available sleep for 1 second

• --src-host=127.0.0.1 - MySQL source host

• --src-user=reader - MySQL source user (remember about PRIVILEGES for this user)

• --src-password=qwerty - MySQL source password (remember about PRIVILEGES for this user)

• --src-tables=airline.ontime - list of MySQL source tables to process

• --dst-host=127.0.0.1 - ClickHouse host

• --dst-create-table - create target table automatically

• --migrate-table - migrate source tables

• --pump-data - pump data from MySQL into ClickHouse after data migrated

• --csvpool - make pool of csv files while pumping data (assumes --mempool also)

You can get the full description about Options by Typing --help with clickhouse-mysql.