Importing from MySQL dump to Clickhouse

Question

I want to import from MySQL dump to Clickhouse. I've tried going through the official docs but cannot find anything. I've tried importing using CSV following Stack Overflow answer. Any help appreciated. I've an Ubuntu 16.04 LTS.

Flying

Answer 1

On small data, the export to tsv will work but at large it will not work, because only export will take a lot of time.

In this case, you need to import directly from stdout and clickhouse knows how to do it perfectly.

Example code:

mysql -u user  -ppass --compress -ss -e "SELECT * FROM table WHERE id >0  AND id <=1000000" db_name | sed 's/\"//g;s/\t/","/g;s/^/"/;s/$/"/' | clickhouse-client --query="INSERT INTO db_name.table FORMAT CSV"

Using this method, I import 500 GB and 1,9 billion rows in 7-10 hours in a clickhouse

Soso

Answer 2

You can export data from MySQL into TSV file using MySQL command line:

mysql -Bse "select * from TABLE_NAME" > table.tsv

And then import data to ClickHouse:

cat table.tsv | clickhouse-client --query="INSERT INTO TABLE_NAME FORMAT TabSeparated"

Pulcheria

Thanks @mikhail, is there no direct approach available to import a SQL to Clickhouse? – Flying

What does 'no direct approach' mean? – Pulcheria

By direct approach I meant without first converting it into a TSV file – Flying

Answer 3

My SQL data dump can be done by the following query:

mysql --protocol tcp -u clickhouse_user_name -p -P 9004 your_db_name < data.sql

Triphibious