Подготовка и импорт данных в 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 файлы. Делать эти проверки не обязательно, но желательно, чтобы вовремя заметить потерю данных.

 


Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *