Особливості міграції SQL-запитів з MySQL на ClickHouse

ClickHouse чудово підходить для зберігання та аналізу великих обсягів статистичних даних. У тих випадках, коли MySQL не справляється навіть із простими вибірками, ClickHouse виконує аналогічні запити за лічені секунди. Під великими обсягами тут маються на увазі таблиці розміром у десятки та сотні гігабайт і мільйони записів.

Чому не варто використовувати ClickHouse як універсальну БД

Поширена помилка розробників — вважати, що раз ClickHouse швидкий і функціональний, то можна зберігати в ньому все. Технічно це можливо, але такий підхід ускладнить розробку, а виграш у швидкості на звичайних запитах буде непомітним.

Підключення до ClickHouse

ClickHouse підтримує кілька протоколів для взаємодії з базою даних, кожен протокол має свій окремий порт. Крім цього, база даних має веб-інтерфейс для виконання запитів.   

Посилання на веб-інтерфейс виконання запитів

Типи таблиць в ClickHouse

Вивчення ClickHouse варто починати з сімейства таблиць MergeTree. Їх поведінка найбільш схожа з класичними СУБД — рядки просто додаються в кінець таблиці. Потім можна переходити до SummingMergeTree, а при необхідності більш складної агрегації — до AggregatingMergeTree.

Таблиця SummingMergeTree

Для збору статистики, де дані потрібно підсумувати, підійде тип SummingMergeTree. Такі таблиці автоматично агрегують числові поля з групуванням за первинним ключем (зазначеним у полі ORDER BY).

CREATE TABLE stat_ip (
  date Date, 
  ip IPv6, 
  requests UInt64
) ORDER BY (date, ip)

Наприклад, потрібно збирати статистику за кількістю запитів на сайт з кожного IP за день. Після вставки декількох рядків з однаковим ключем таблиця автоматично підсумує числові поля і збереже один рядок:

INSERT INTO stat_ip (date, ip, requests) 
VALUES 
  ('2025-11-16', '127.0.0.1', 200),
  ('2025-11-16', '127.0.0.1', 100),
  ('2025-11-16', '127.0.0.1', 300),
  ('2025-11-16', '127.0.0.1', 500)

Умова ORDER BY при створенні таблиці аналогічна PRIMARY KEY в MySQL, за винятком того, що в MergeTree-таблицях вона не обов'язково має бути унікальною.

У SummingMergeTree поля з ORDER BY використовуються як ключ агрегації.

Як правильно вибирати дані з SummingMergeTree

Оскільки відразу після вставки рядки ще можуть бути не агреговані, при вибірках рекомендується явно вказувати GROUP BY і SUM():   

SELECT
  date,
  ip,
  SUM(requests) AS req
FROM stat_ip
GROUP BY date, ip

Без використання GROUP BY можна отримати кілька рядків з однаковим ключем, якщо агрегація всередині таблиці ще не виконана.

Стиснення даних у таблицях

У документації зазначено, що можна задавати алгоритм стиснення для кожного поля. Робити це на перших етапах не має сенсу — за замовчуванням таблиці в ClickHouse вже використовують оптимальне стиснення. Вказання альтернативних алгоритмів потрібне лише для тонкого налаштування дуже великих наборів даних.

Типи даних

При створенні таблиці типи даних починаються з великої літери, кожне нове слово в типі даних пишеться з великої літери. Наприклад: FixedString.   

• Цілі типи даних мають такі ж назви, як і в MySQL. Точніше, вони мають вигляд Int8 - Int256, UInt8 - UInt256, але підтримуються і аліаси, аналогічні типам даних в MySQL.
• Char(x) - FixedString(x) - використовувати потрібно тільки якщо довжина тексту, що зберігається в полі, завжди однакова. Якщо додати коротший текст, то дані будуть доповнені символом NULL. \0.
• Для зберігання varchar, text, blob використовується єдиний тип String.
• Поля Enum мають такий самий синтаксис, як і в MySQL.
• IPv6 використовується для зберігання IP обох версій. IPv4 автоматично перетворюється у формат IPv6: 127.0.0.1 → ::ffff:127.0.0.1

При перенесенні запитів і статистики з MySQL в ClickHouse важливо враховувати кілька ключових відмінностей в синтаксисі та логіці виконання:

Видалення застарілих даних

У ClickHouse можна вказати термін зберігання даних у параметрі TTL безпосередньо під час створення таблиці. Наприклад, якщо вам потрібні дані тільки за останні 2 місяці, немає необхідності створювати окремий скрипт для очищення — система сама видалить застарілі рядки.

CREATE TABLE log (
  dtime DateTime,
  ip IPv6,
  url String
) 
ORDER BY (dtime)
TTL dtime + INTERVAL 2 MONTH; -- хранить данные только 2 месяца, затем удалять

-- Изменить в существующей таблице срок хранения данных 
ALTER TABLE log TTL dtime + INTERVAL 2 MONTH;

Видалення записів з таблиці

Якщо виконати звичайний запит на видалення даних, то дані не будуть позначені як видалені без фактичного видалення з таблиці. Якщо необхідно звільнити місце на диску і видалити повністю записи з таблиці, то виконується запит:

ALTER TABLE stat_ip DELETE WHERE date < '2025-10-01';

-- вместо стандартного 

DELETE FROM stat_ip WHERE date < '2025-10-01';

Сегментування таблиці

Якщо даних багато, а вибірки зазвичай робляться за якийсь певний проміжок часу, то такі таблиці краще розбити на сегменти. Наприклад, ви зберігаєте статистику за місяць, але запити на отримання даних робляться за один день.

CREATE TABLE stat_ip (
  date Date,
  ip IPv6,
  requests UInt32
) ORDER BY (date, ip)
PARTITION BY toYYYYMMDD(date) -- сегментирование по дням

Найпопулярніші функції для визначення сегментів: toYYYYMMDD(), toYYYYMM()

Форматування дати

У ClickHouse замість функції DATE_FORMAT() використовується аналог formatDateTime().

-- MySQL
DATE_FORMAT(created_at, '%Y-%m-%d')

-- ClickHouse
formatDateTime(created_at, '%Y-%m-%d')

Фільтрація обчислюваних полів

У ClickHouse поля, які обчислюються в блоці SELECT, можна фільтрувати тільки в блоці HAVING. У MySQL ті ж вирази часто допускається використовувати в WHERE, але в ClickHouse це призведе до помилки.

-- MySQL
SELECT user_id, COUNT(*) AS cnt
FROM events
WHERE cnt > 10
GROUP BY user_id;

-- ClickHouse
SELECT user_id, COUNT(*) AS cnt
FROM events
GROUP BY user_id
HAVING cnt > 10;

Використання агрегуючих функцій

Якщо в запиті присутній GROUP BY, то всі поля, зазначені в SELECT, але не перераховані в GROUP BY, повинні бути обгорнуті в агрегуючу функцію. Якщо ви хочете вивести значення поля без агрегації, використовуйте функцію any().

-- MySQL
SELECT user_id, name, COUNT(*) FROM users GROUP BY user_id;

-- ClickHouse
SELECT user_id, any(name), COUNT(*) FROM users GROUP BY user_id;

Регістр символів у назвах функцій

Назви функцій в ClickHouse є регістрозалежними, тому їх назви потрібно писати так, як зазначено в документації.

-- Запрос выдающий ошибку так как название функции md5() должно быть в верхнем регистре
CREATE TABLE test (
  name String,
  idx FixedString(32) MATERIALIZED md5(name)
)

-- Корректный вариант
CREATE TABLE test (
  name String,
  idx FixedString(32) MATERIALIZED MD5(name)
)

GROUP_CONCAT(DISTINCT ...)

ClickHouse вміє працювати з масивами, тому для побудови запиту зручніше використовувати конструкцію toJSONString(groupArrayDistinct(ip)), яка поверне згруповані рядки без дублюючих значень у вигляді JSON.

-- MySQL
SELECT user, GROUP_CONCAT(DITINCT ip) FROM sessions GROUP BY user

-- ClickHouse
SELECT user, toJSONString(groupArrayDistinct(ip)) FROM sessions GROUP BY user