Search

Replication from MySQL to ClickHouse

Updated: Apr 29, 2019

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.


1,183 views0 comments

Recent Posts

See All

What are the future prospects of Java

According to a survey conducted, nearly 63% programmers mentioned that they will likely continue to work with Java along with coding with Python, SQL and HTML/CSS. In addition, the giants in the corpo

Deleting Duplicate Rows in MySQL

In this blog, you will study several ways to delete duplicate rows in MySQL. In this blog, I have shown you how to find duplicate values in a table. Once the duplicates rows are recognized, you may ne

Upload Data to MySQL tables using mysqlimport

Uploading quite a lot of rows of data from a text, csv, and excel file into a MySQL table is a repetitive task for sysadmins and DBAs who are handling MySQL database. This blog clarifies 4 applied exa