Загрузка данных из MySQL® в витрину ClickHouse®
С помощью сервиса Data Transfer вы можете перенести базу данных из кластера-источника MySQL® в ClickHouse®.
Чтобы перенести данные:
- Подготовьте кластер-источник.
- Подготовьте и активируйте трансфер.
- Проверьте работоспособность трансфера.
- Выполните выборку данных в ClickHouse®.
Если созданные ресурсы вам больше не нужны, удалите их.
Перед началом работы
Подготовьте инфраструктуру:
-
Создайте кластер-источник Managed Service for MySQL® любой подходящей конфигурации. Для подключения к кластеру с локальной машины пользователя, а не из облачной сети Yandex Cloud, включите публичный доступ к кластеру при его создании.
-
Создайте кластер-приемник Managed Service for ClickHouse® любой подходящей конфигурации со следующими настройками:
- Количество хостов ClickHouse® — не меньше 2 (для включения репликации в кластере).
- Имя базы данных — такое же, как на кластере-источнике.
- Для подключения к кластеру с локальной машины пользователя, а не из облачной сети Yandex Cloud, включите публичный доступ к кластеру при его создании.
-
Если вы используете группы безопасности в кластерах, настройте их так, чтобы к кластерам можно было подключаться из интернета:
-
Если у вас еще нет Terraform, установите его.
-
Получите данные для аутентификации. Вы можете добавить их в переменные окружения или указать далее в файле с настройками провайдера.
-
Настройте и инициализируйте провайдер. Чтобы не создавать конфигурационный файл с настройками провайдера вручную, скачайте его
. -
Поместите конфигурационный файл в отдельную рабочую директорию и укажите значения параметров. Если данные для аутентификации не были добавлены в переменные окружения, укажите их в конфигурационном файле.
-
Скачайте в ту же рабочую директорию файл конфигурации data-transfer-mmy-mch.tf
.В этом файле описаны:
- сеть;
- подсеть;
- группа безопасности и правило, необходимое для подключения к кластеру Managed Service for MySQL®;
- кластер-источник Managed Service for MySQL®;
- кластер-приемник Managed Service for ClickHouse®;
- эндпоинт для источника;
- эндпоинт для приемника;
- трансфер.
-
Укажите в файле
data-transfer-mmy-mch.tf
:-
параметры кластера-источника Managed Service for MySQL®, которые будут использоваться как параметры эндпоинта-источника:
source_mysql_version
— версия MySQL®;source_db_name
— имя базы данных MySQL®, которое будет использоваться как имя базы данных Managed Service for ClickHouse®;source_user
иsource_password
— имя и пароль пользователя-владельца базы данных.
-
параметры кластера-приемника Managed Service for ClickHouse®, которые будут использоваться как параметры эндпоинта-приемника:
target_user
иtarget_password
— имя и пароль пользователя-владельца базы данных.
-
-
Проверьте корректность файлов конфигурации Terraform с помощью команды:
terraform validate
Если в файлах конфигурации есть ошибки, Terraform на них укажет.
-
Создайте необходимую инфраструктуру:
-
Выполните команду для просмотра планируемых изменений:
terraform plan
Если конфигурации ресурсов описаны верно, в терминале отобразится список изменяемых ресурсов и их параметров. Это проверочный этап: ресурсы не будут изменены.
-
Если вас устраивают планируемые изменения, внесите их:
-
Выполните команду:
terraform apply
-
Подтвердите изменение ресурсов.
-
Дождитесь завершения операции.
-
В указанном каталоге будут созданы все требуемые ресурсы. Проверить появление ресурсов и их настройки можно в консоли управления
. -
Подготовьте кластер-источник
-
Если вы создавали инфраструктуру вручную, подготовьте кластер-источник.
-
Подключитесь к кластеру-источнику Managed Service for MySQL®.
-
Наполните базу тестовыми данными.
- Создайте таблицу
x_tab
:
CREATE TABLE x_tab ( id INT, name TEXT, PRIMARY KEY (id) );
- Заполните таблицу данными:
INSERT INTO x_tab (id, name) VALUES (40, 'User1'), (41, 'User2'), (42, 'User3'), (43, 'User4'), (44, 'User5');
- Создайте таблицу
Подготовьте и активируйте трансфер
-
Создайте эндпоинт для источника:
-
Тип базы данных —
MySQL®
. -
Параметры эндпоинта → Настройки подключения —
Кластер Managed Service for MySQL
.Выберите кластер-источник из списка и укажите настройки подключения к нему.
-
-
Создайте эндпоинт для приемника:
-
Тип базы данных —
ClickHouse
. -
Параметры эндпоинта → Настройки подключения —
Managed кластер
.Выберите кластер-приемник из списка и укажите настройки подключения к нему.
-
-
Создайте трансфер типа Копирование и репликация, использующий созданные эндпоинты.
-
Активируйте его.
-
Укажите в файле
data-transfer-mmy-mch.tf
для переменнойtransfer_enabled
значение1
. -
Проверьте корректность файлов конфигурации Terraform с помощью команды:
terraform validate
Если в файлах конфигурации есть ошибки, Terraform на них укажет.
-
Создайте необходимую инфраструктуру:
-
Выполните команду для просмотра планируемых изменений:
terraform plan
Если конфигурации ресурсов описаны верно, в терминале отобразится список изменяемых ресурсов и их параметров. Это проверочный этап: ресурсы не будут изменены.
-
Если вас устраивают планируемые изменения, внесите их:
-
Выполните команду:
terraform apply
-
Подтвердите изменение ресурсов.
-
Дождитесь завершения операции.
-
Трансфер активируется автоматически после создания.
-
Проверьте работоспособность трансфера
-
Дождитесь перехода трансфера в статус Реплицируется.
-
Убедитесь, что в базу данных Managed Service for ClickHouse® перенеслись данные из кластера-источника Managed Service for MySQL®:
-
Подключитесь к кластеру с помощью
clickhouse-client
. -
Выполните запрос:
SELECT * FROM <имя_базы_данных_ClickHouse®>.x_tab
Результат:
┌─id─┬─name──┬─__data_transfer_commit_time─┬─__data_transfer_delete_time─┐ │ 40 │ User1 │ 1661952756538347180 │ 0 │ │ 41 │ User2 │ 1661952756538347180 │ 0 │ │ 42 │ User3 │ 1661952756538347180 │ 0 │ │ 43 │ User4 │ 1661952756538347180 │ 0 │ │ 44 │ User5 │ 1661952756538347180 │ 0 │ └────┴───────┴─────────────────────────────┴─────────────────────────────┘
Таблица также содержит столбцы с временными метками
__data_transfer_commit_time
и__data_transfer_delete_time
.
-
-
Удалите строку с
id
41
и измените сid
42
в таблицеx_tab
базы-источника MySQL®:-
Подключитесь к кластеру-источнику Managed Service for MySQL®.
-
Выполните запросы:
DELETE FROM x_tab WHERE id = 41; UPDATE x_tab SET name = 'Key3' WHERE id = 42;
-
-
Убедитесь, что в таблице
x_tab
на приемнике ClickHouse® отобразились изменения:SELECT * FROM <имя_базы_данных_ClickHouse®>.x_tab WHERE id in (41,42);
Результат:
┌─id─┬─name──┬─__data_transfer_commit_time─┬─__data_transfer_delete_time─┐ │ 41 │ User2 │ 1661952756538347180 │ 0 │ │ 42 │ User3 │ 1661952756538347180 │ 0 │ └────┴───────┴─────────────────────────────┴─────────────────────────────┘ ┌─id─┬─name─┬─__data_transfer_commit_time─┬─__data_transfer_delete_time─┐ │ 41 │ ᴺᵁᴸᴸ │ 1661953256000000000 │ 1661953256000000000 │ └────┴──────┴─────────────────────────────┴─────────────────────────────┘ ┌─id─┬─name─┬─__data_transfer_commit_time─┬─__data_transfer_delete_time─┐ │ 42 │ Key3 │ 1661953280000000000 │ 0 │ └────┴──────┴─────────────────────────────┴─────────────────────────────┘
Выполните выборку данных в ClickHouse®
На приемнике ClickHouse® с включенной репликацией для воссоздания таблиц используются движки ReplicatedReplacingMergeTree
-
__data_transfer_commit_time
— время изменения строки на это значение, в форматеTIMESTAMP
; -
__data_transfer_delete_time
— время удаления строки в форматеTIMESTAMP
, если строка удалена в источнике. Если строка не удалялась, то устанавливается значение0
.Столбец
__data_transfer_commit_time
необходим для работы движка ReplicatedReplacedMergeTree. Если запись удаляется или изменяется, в таблицу вставляется новая строка со значением в этом столбце. Запрос по одному первичному ключу возвращает несколько записей с разными значениями в столбце__data_transfer_commit_time
.
В статусе трансфера Реплицируется данные в источнике могут добавляться или удаляться. Чтобы обеспечить стандартное поведение команд SQL, когда первичный ключ указывает на единственную запись, дополните запросы к перенесенным таблицам в ClickHouse® конструкцией с фильтром по столбцу __data_transfer_delete_time
. Например, для таблицы x_tab
:
SELECT * FROM <имя_базы_данных_ClickHouse®>.x_tab FINAL
WHERE __data_transfer_delete_time = 0;
Для упрощения запросов SELECT
создайте представление с фильтром по столбцу __data_transfer_delete_time
и обращайтесь к нему. Например, для таблицы x_tab
:
CREATE VIEW x_tab_view AS SELECT * FROM <имя_базы_данных_ClickHouse®>.x_tab FINAL
WHERE __data_transfer_delete_time == 0;
Удалите созданные ресурсы
Примечание
Перед тем как удалить созданные ресурсы, деактивируйте трансфер.
Некоторые ресурсы платные. Чтобы за них не списывалась плата, удалите ресурсы, которые вы больше не будете использовать:
- Удалите трансфер.
- Удалите эндпоинты для источника и приемника.
- Удалите кластер Managed Service for MySQL®.
- Удалите кластер Managed Service for ClickHouse®.
-
В терминале перейдите в директорию с планом инфраструктуры.
-
Удалите конфигурационный файл
data-transfer-mmy-mch.tf
. -
Проверьте корректность файлов конфигурации Terraform с помощью команды:
terraform validate
Если в файлах конфигурации есть ошибки, Terraform на них укажет.
-
Подтвердите изменение ресурсов.
-
Выполните команду для просмотра планируемых изменений:
terraform plan
Если конфигурации ресурсов описаны верно, в терминале отобразится список изменяемых ресурсов и их параметров. Это проверочный этап: ресурсы не будут изменены.
-
Если вас устраивают планируемые изменения, внесите их:
-
Выполните команду:
terraform apply
-
Подтвердите изменение ресурсов.
-
Дождитесь завершения операции.
-
Все ресурсы, которые были описаны в конфигурационном файле
data-transfer-mmy-mch.tf
, будут удалены. -
ClickHouse® является зарегистрированным товарным знаком ClickHouse, Inc