ClickHouse

ClickHouse — столбцовая система управления базами данных (СУБД) для онлайн обработки аналитических запросов (OLAP).
Официальный сайт.

ClickHouse максимально использует все доступные системные ресурсы для максимально быстрой обработки каждого аналитического запроса. Это стало возможным благодаря уникальному сочетанию аналитических возможностей и внимания к низкоуровневым деталям, необходимым для реализации самой быстрой базы данных OLAP.

Типовой кейс использования продукта

Wide data marts — работы с быстрыми витринами данных. Если витрины, созданные в Greenplum, выгружаются в ClickHouse, куда поступают однотипные предсказуемые запросы пользователей, запросы к быстрым витринам обрабатываются за миллисекунды.
Wide fact tables — работы с широкими денормализованными таблицами фактов. Когда необходимо сохранить огромные массивы сырых данных, агрегировать их и дальше работать с агрегатами. Позволяет быстро делать сложные агрегации, запросы к таблицам обрабатываются за миллисекунды.
AD-HOC — работы с несложными специализированными запросами к витринам данных, когда необходимо сохранить огромный массив сырых данных.
Full-scan операции — работы с full-scan операциями при условии использовании фильтров, а также работы со структурированными логами и событиями.
Аналитика работы мобильных приложений: анализ количества скачиваний и регистраций, активности и вовлечённости пользователей, длительности сессий, количества рассылаемых приглашений и т. д.
Web-аналитика: источники трафика, отказы, новые, постоянные и вернувшиеся посетители, длительность сессии, среднее количество просмотра страниц за один визит, конверсия, выполняемые действия, используемые устройства (мобильные или десктопные).
Реклама и торги в реальном времени (технология продажи и покупки рекламных показов через аукцион).
Розничная и электронная торговля: анализ покупательского спроса, учёт и анализ товарных запасов на складе, сбор и анализ данных онлайн-покупок пользователей и др.
Бизнес-аналитика, банковские и финансовые операции: клиенты, финансы, продукты.
Мониторинг различных технических и бизнес-метрик.
Телекоммуникации и информационная безопасность: сбор и анализ информации об актуальных угрозах, выдача рекомендаций по их предотвращению, определение уязвимых мест в ИТ-инфраструктуре, сбор данных об используемом ПО и сроках действия лицензий.
Онлайн-игры: активные пользователи, длительность сессии, отток, цена за установку, платежи и т. д.
Обработка данных с IoT-устройств (Internet of Things, интернет вещей) и промышленных датчиков (обработка показателей с промышленных роботов, мониторинг линий производства в реальном времени).

Пререквизиты для работы с ClickHouse

На рабочем месте должен быть установлен любой клиент поддерживающий ClickHouse.

Получить доступ к кластеру ClickHouse, открыв сетевой доступ на порт/порты 9000 (tcp/binary), 8123 (tcp/http) или 8443 (tcp/https).

Особенности клиента DBeaver

Рекомендуемым клиентом для подключения к ClickHouse является Dbeaver. Драйвер этого ПО использует tcp/http протокол. Также можно использовать tcp/https, однако в настройках драйвера необходимо отключить проверку валидности сертификата.

Подключение к ClickHouse

К кластеру можно подключиться через порты 9000 для clickhouse-client и 8123 для HTTP-интерфейса.

clickhouse-client


clickhouse-client --host IP  \
                  --user binding_login \
                  --port 9000 \
                  --ask-password

Описание параметров:

IP - внешний IP адрес СУБД;
binding_login - имя пользователя указанное при создании биндинга.
После выполнения команды введите пароль пользователя для завершения процедуры подключения.
Выполните команду SELECT version();.

cURL


curl --header "X-ClickHouse-User: binding_login" \
     --header "X-ClickHouse-Key: binding_password" \
     'http://IP:8123/?&query=SELECT%20version()'

или

echo "SELECT version();" | curl --header "X-ClickHouse-User: binding_login" \
   --header "X-ClickHouse-Key: binding_password" \
   'http://IP:8123' \
   --data-binary @-

Описание параметров:

IP - внешний IP адрес СУБД;
binding_login - имя пользователя указанное при создании биндинга.
binding_password - пароль пользователя, полученный при создании биндинга.

Встроенный SQL-редактор

Для подключения из встроенного SQL-редактора, укажите в браузере адресную строку:


https://IP:8123/play

Описание параметров:
IP - внешний IP адрес СУБД;

Для выполнения запросов к базе данных укажите имя пользователя и пароль в правом верхнем углу страницы.

DBeaver

Предварительно необходимо самостоятельно скачать и установить драйвер Clickhouse для DBeaver.

Cоздайте новое соединение с БД.
Выберите в меню База данных пункт Новое соединение.
Выберите из списка БД ClickHouse.
Укажите параметры подключения на вкладке Главное:
- Хост — внешний IP адрес СУБД;
- Порт — 8123;
- БД/Схема — можно оставить пустым;
- В блоке Аутентификация укажите имя и пароль пользователя БД.
Нажмите Тест соединения для проверки подключения.
Нажмите Готово, чтобы сохранить настройки соединения с БД.

Различные клиенты и драйвера

Полный перечень поддерживаемых клиентов и драйверов, а так же примеры их использования модно посмотреть в официальной документации ClickHouse integrations.

Работа с данными

MergeTree - основной движок для аналитики

Пример создания таблицы для хранения логов веб-сервера

CREATE TABLE web_logs (
    timestamp DateTime,
    user_id UInt32,
    page_url String,
    response_code UInt16,
    response_time_ms UInt32,
    user_agent String
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(timestamp)
ORDER BY (timestamp, user_id)
SETTINGS index_granularity = 8192;

Distributed - для распределенных таблиц

Пример создания локальной таблицы на каждом сервере кластера

CREATE TABLE events_local (
    event_time DateTime,
    event_type String,
    user_id UInt32,
    event_data String
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_time, user_id);

Пример создания распределенной таблицы

CREATE TABLE events_distributed AS events_local
ENGINE = Distributed(cluster_name, default, events_local, rand());

Примеры аналитических запросов

Топ-10 самых посещаемых страниц за последний месяц

SELECT 
    page_url,
    count() as visits,
    avg(response_time_ms) as avg_response_time
FROM web_logs 
WHERE timestamp >= now() - INTERVAL 1 MONTH
GROUP BY page_url 
ORDER BY visits DESC 
LIMIT 10;

Продажи по категориям с накопительным итогом

SELECT
date,
category_id,
sum(sales_amount) as daily_sales,
sum(sum(sales_amount)) OVER (PARTITION BY category_id ORDER BY date) as cumulative_sales
FROM sales_metrics
WHERE date >= '2024-01-01'
GROUP BY date, category_id
ORDER BY category_id, date;

Полезные ссылки

Clickhouse Docs - официальная документация Перейти ↗
DBeaver - SQL клиент Перейти ↗

Движок	Когда использовать	Особенности
MergeTree	Основной движок для аналитики	Быстрые запросы, партиционирование
ReplacingMergeTree	Нужна дедупликация данных	Автоматическое удаление дубликатов
SummingMergeTree	Агрегация числовых метрик	Автоматическое суммирование
CollapsingMergeTree	Нужна отмена операций	Поддержка операций отмены
Distributed	Кластерная установка	Распределение данных по серверам
Memory	Временные вычисления	Высокая скорость, данные в RAM