Подготовка и импорт данных в Clickhouse достаточно обширная тема. В данной заметке приведу рецепт быстрого экспорта данных с джойнами из MySQL в CSV с последующим импортом этих CSV в Clickhouse. В MySQL есть очень полезная директива: INTO OUTFILE, которая в контексте SELECT запросов сохраняет данные в файл на сервере, а не отдаёт клиенту. Важно, что выполнить SELECT с опцией INTO OUTFILE сможет только пользователь имеющий привилегию FILE.
Всё было бы хорошо, если нужно экспортировать одну плоскую таблицу без джойнов даже с десятками миллионов строк это было бы очень быстро. Однако, как только в запрос для экспорта добавляются джойны — время экспорта такого запроса вырастает в десятки и сотни раз, что неприемлемо. Обойти это можно с помощью экспорта данных небольшими пачками, например по 100 000 записей за раз.
Конечно, делать это ручками ещё более неприемлемо, поэтому я подготовил такой скрипт:
<?php
$host = 'mariadb';
$db = 'mydb';
$user = 'appuser';
$pass = 'passforapp';
$charset = 'utf8';
$dsn = "mysql:host={$host};dbname={$db};charset={$charset}";
$pdo = new PDO($dsn, $user, $pass);
$table = 'mytable';
$file_prefix = 'old';
$pk = 'id';
$limit = 100000;
$stmt = $pdo->query("SELECT MIN({$pk}) min, MAX({$pk}) max FROM {$table};");
$extr = $stmt->fetchAll(PDO::FETCH_ASSOC)[0];
$range = range($extr['min'], $extr['max'], $limit);
$k = 0;
foreach($range as $i => $v) {
$n = $v + $limit - 1;
$k++;
$sql = "SELECT ....
FROM {$table}
JOIN ...
WHERE log_id BETWEEN {$v} AND {$n}
INTO OUTFILE '/tmp/{$file_prefix}-{$k}.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '\"'
LINES TERMINATED BY '\n';";
echo $sql . "\n\n";
}В скрипте нужно указать данные для доступа к базе на чтение, а также название таблицы для экспорта $table, префикс файла с дампом $file_prefix, название поля с первичным ключём таблицы $pk, количество строк экспортируемых за раз: $limit. Данная реализация использует значения первичного ключа для разбиения запроса на небольшие пачки, это гораздо производительнее, чем делать LIMIT и OFFSET.
Запускаем скрипт для генерации SQL запросов для экспорта:
php dump.php > dump.sql
Выполняем запросы из файла на сервере MySQL:
cat dump.sql | mysql -u root -D mydb -p
На всякий случай проверяем сколько строк было экспортировано:
cat /tmp/data* | wc -l
Перед экспортом данных в Clickhouse смотрим сколько строк в таблице имеется сейчас:
clickhouse client -q "select count(*) from mydb.mytable;"
Импортируем данные в кликхаус:
cat /tmp/data* | clickhouse client --query="INSERT INTO myapp.mytable FORMAT CSV
Снова проверяем количество записей в таблице:
clickhouse client -q "select count(*) from mydb.mytable;"
В идеале в таблице должно стать ровно на столько записей больше, сколько было экспортировано в CSV файлы. Делать эти проверки не обязательно, но желательно, чтобы вовремя заметить потерю данных.