Search code examples
mysqlubuntu-16.04clickhouse

Importing from MySQL dump to Clickhouse

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.

Solution

  • 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