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.

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

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"