Skip to main content

[экспериментальный] MaterializedMySQL

Это экспериментальный движок, который не следует использовать в продакшене.

Создает базу данных ClickHouse со всеми таблицами, существующими в MySQL, и всеми данными в этих таблицах.

Сервер ClickHouse работает как реплика MySQL. Он читает файл binlog и выполняет DDL and DML-запросы.

Создание базы данных

CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE = MaterializedMySQL('host:port', ['database' | database], 'user', 'password') [SETTINGS ...]

Параметры движка

  • host:port — адрес сервера MySQL.
  • database — имя базы данных на удалённом сервере.
  • user — пользователь MySQL.
  • password — пароль пользователя.

Настройки движка

  • max_rows_in_buffer — максимальное количество строк, содержимое которых может кешироваться в памяти (для одной таблицы и данных кеша, которые невозможно запросить). При превышении количества строк, данные будут материализованы. Значение по умолчанию: 65 505.
  • max_bytes_in_buffer — максимальное количество байтов, которое разрешено кешировать в памяти (для одной таблицы и данных кеша, которые невозможно запросить). При превышении количества строк, данные будут материализованы. Значение по умолчанию: 1 048 576.
  • max_rows_in_buffers — максимальное количество строк, содержимое которых может кешироваться в памяти (для базы данных и данных кеша, которые невозможно запросить). При превышении количества строк, данные будут материализованы. Значение по умолчанию: 65 505.
  • max_bytes_in_buffers — максимальное количество байтов, которое разрешено кешировать данным в памяти (для базы данных и данных кеша, которые невозможно запросить). При превышении количества строк, данные будут материализованы. Значение по умолчанию: 1 048 576.
  • max_flush_data_time — максимальное время в миллисекундах, в течение которого разрешено кешировать данные в памяти (для базы данных и данных кеша, которые невозможно запросить). При превышении количества указанного периода, данные будут материализованы. Значение по умолчанию: 1000.
  • max_wait_time_when_mysql_unavailable — интервал между повторными попытками, если MySQL недоступен. Указывается в миллисекундах. Отрицательное значение отключает повторные попытки. Значение по умолчанию: 1000.
  • allows_query_when_mysql_lost — признак, разрешен ли запрос к материализованной таблице при потере соединения с MySQL. Значение по умолчанию: 0 (false).
CREATE DATABASE mysql ENGINE = MaterializedMySQL('localhost:3306', 'db', 'user', '***')
SETTINGS
allows_query_when_mysql_lost=true,
max_wait_time_when_mysql_unavailable=10000;

Настройки на стороне MySQL-сервера

Для правильной работы MaterializedMySQL следует обязательно указать на сервере MySQL следующие параметры конфигурации:

  • default_authentication_plugin = mysql_native_passwordMaterializedMySQL может авторизоваться только с помощью этого метода.

  • gtid_mode = on — ведение журнала на основе GTID является обязательным для обеспечения правильной репликации.

    :::note "Внимание" При включении gtid_mode вы также должны указать enforce_gtid_consistency = on. :::

Виртуальные столбцы

При работе с движком баз данных MaterializedMySQL используются таблицы семейства ReplacingMergeTree с виртуальными столбцами _sign и _version.

  • _version — счетчик транзакций. Тип UInt64.
  • _sign — метка удаления. Тип Int8. Возможные значения:
    • 1 — строка не удалена,
    • -1 — строка удалена.

Поддержка типов данных

MySQLClickHouse
TINYInt8
SHORTInt16
INT24Int32
LONGUInt32
LONGLONGUInt64
FLOATFloat32
DOUBLEFloat64
DECIMAL, NEWDECIMALDecimal
DATE, NEWDATEDate
DATETIME, TIMESTAMPDateTime
DATETIME2, TIMESTAMP2DateTime64
ENUMEnum
STRINGString
VARCHAR, VAR_STRINGString
BLOBString
BINARYFixedString

Тип Nullable поддерживается.

Другие типы не поддерживаются. Если таблица MySQL содержит столбец другого типа, ClickHouse выдаст исключение "Неподдерживаемый тип данных" ("Unhandled data type") и остановит репликацию.

Особенности и рекомендации

Ограничения совместимости

Кроме ограничений на типы данных, существует несколько ограничений по сравнению с базами данных MySQL, которые следует решить до того, как станет возможной репликация:

  • Каждая таблица в MySQL должна содержать PRIMARY KEY.
  • Репликация для таблиц, содержащих строки со значениями полей ENUM вне диапазона значений (определяется размерностью ENUM), не будет работать.

DDL-запросы

DDL-запросы в MySQL конвертируются в соответствующие DDL-запросы в ClickHouse (ALTER, CREATE, DROP, RENAME). Если ClickHouse не может конвертировать какой-либо DDL-запрос, он его игнорирует.

Репликация данных

Данные являются неизменяемыми со стороны пользователя ClickHouse, но автоматически обновляются путём репликации следующих запросов из MySQL:

  • Запрос INSERT конвертируется в ClickHouse в INSERT с _sign=1.

  • Запрос DELETE конвертируется в ClickHouse в INSERT с _sign=-1.

  • Запрос UPDATE конвертируется в ClickHouse в INSERT с _sign=-1 и INSERT с _sign=1.

Выборка из таблиц движка MaterializedMySQL

Запрос SELECT из таблиц движка MaterializedMySQL имеет некоторую специфику:

  • Если в запросе SELECT напрямую не указан столбец _version, то используется модификатор FINAL. Таким образом, выбираются только строки с MAX(_version).

  • Если в запросе SELECT напрямую не указан столбец _sign, то по умолчанию используется WHERE _sign=1. Таким образом, удаленные строки не включаются в результирующий набор.

  • Результат включает комментарии к столбцам, если они существуют в таблицах базы данных MySQL.

Конвертация индексов

Секции PRIMARY KEY и INDEX в MySQL конвертируются в кортежи ORDER BY в таблицах ClickHouse.

В таблицах ClickHouse данные физически хранятся в том порядке, который определяется секцией ORDER BY. Чтобы физически перегруппировать данные, используйте материализованные представления.

Примечание

  • Строки с _sign=-1 физически не удаляются из таблиц.
  • Каскадные запросы UPDATE/DELETE не поддерживаются движком MaterializedMySQL.
  • Репликация может быть легко нарушена.
  • Прямые операции изменения данных в таблицах и базах данных MaterializedMySQL запрещены.
  • На работу MaterializedMySQL влияет настройка optimize_on_insert. Когда таблица на MySQL сервере меняется, происходит слияние данных в соответсвующей таблице в базе данных MaterializedMySQL.

Примеры использования

Запросы в MySQL:

mysql> CREATE DATABASE db;
mysql> CREATE TABLE db.test (a INT PRIMARY KEY, b INT);
mysql> INSERT INTO db.test VALUES (1, 11), (2, 22);
mysql> DELETE FROM db.test WHERE a=1;
mysql> ALTER TABLE db.test ADD COLUMN c VARCHAR(16);
mysql> UPDATE db.test SET c='Wow!', b=222;
mysql> SELECT * FROM test;
+---+------+------+
| a | b | c |
+---+------+------+
| 2 | 222 | Wow! |
+---+------+------+

База данных в ClickHouse, обмен данными с сервером MySQL:

База данных и созданная таблица:

CREATE DATABASE mysql ENGINE = MaterializedMySQL('localhost:3306', 'db', 'user', '***');
SHOW TABLES FROM mysql;
┌─name─┐
│ test │
└──────┘

После вставки данных:

SELECT * FROM mysql.test;
┌─a─┬──b─┐
│ 1 │ 11 │
│ 2 │ 22 │
└───┴────┘

После удаления данных, добавления столбца и обновления:

SELECT * FROM mysql.test;
┌─a─┬───b─┬─c────┐
│ 2 │ 222 │ Wow! │
└───┴─────┴──────┘

Оригинальная статья