Приветствуем вас в третьей части нашего гида по ClickHouse! В предыдущих статьях мы заложили прочный фундамент: разобрались, что такое ClickHouse (далее CH), почему он так хорош для аналитики, а также глубоко погрузились в типы данных и движки таблиц, научившись создавать оптимизированные таблицы. Теперь пришло время перейти от теории к самой сути — работе с данными.В этом уроке мы научимся эффективно загружать данные в наши таблицы и, что самое важное, извлекать из них ценную информацию с помощью базовых, но мощных запросов.
Как вставлять данные в ClickHouse: от простого к эффективному
Загрузка данных — это первый шаг к их анализу. ClickHouse предлагает несколько способов вставки, и выбор правильного метода зависит от ваших задач и объема данных.
Классический INSERT INTO ... VALUES
Самый знакомый для всех, кто работал с SQL, способ — это прямая вставка строк с помощью VALUES. Он отлично подходит для тестов, отладки или добавления нескольких записей вручную. Давайте добавим пару строк в нашу таблицу access_logs, которую мы создали на прошлом уроке .
INSERT INTO my_first_db.access_logs (timestamp, event_type, user_id, ip_address, url, duration_ms) VALUES
('2024-07-02 11:00:00.000', 'page_view', 105, '8.8.8.8', '/pricing', 450),
('2024-07-02 11:01:15.123', 'click', 105, '8.8.8.8', '/buy_now_button', 30);
Однако важно понимать: для ClickHouse вставка данных по одной строке — крайне неэффективная операция. Он спроектирован для обработки данных большими пачками (батчами). Использование INSERT ... VALUES для загрузки миллионов строк приведет к низкой производительности.
Профессиональный подход: Вставка из форматов (INSERT ... FORMAT)
На практике данные почти всегда поступают из внешних файлов или систем. CH блестяще справляется с этим, поддерживая вставку данных напрямую из различных форматов. Самые популярные из них — это CSV и JSONEachRow.
CSV (Comma-Separated Values): Простой текстовый формат, где значения разделены запятыми.
JSONEachRow: В этом формате каждая строка файла представляет собой отдельный JSON-объект. Это очень удобно для интеграции с веб-сервисами и системами логирования.
Представьте, у вас есть файл logs.json со следующим содержимым:
Вы можете загрузить его одной командой через консольный клиент clickhouse-client:
clickhouse-client --query="INSERT INTO my_first_db.access_logs FORMAT JSONEachRow" < logs.json
Этот способ значительно эффективнее, так как все данные из файла отправляются на сервер единым пакетом.
Извлечение данных: Искусство SELECT запросов
Теперь, когда в наших таблицах есть данные, давайте научимся их извлекать и анализировать. Основой всего является оператор SELECT.
Выборка, фильтрация и сортировка
Давайте рассмотрим базовые компоненты запроса на примере нашей таблицы access_logs.
- SELECT: Указывает, какие столбцы мы хотим получить.
- FROM: Указывает таблицу, из которой мы извлекаем данные.
- WHERE: Накладывает фильтры для выбора только нужных нам строк.
- ORDER BY: Сортирует результат по одному или нескольким столбцам.
- LIMIT: Ограничивает количество возвращаемых строк.
Например, найдем 5 самых долгих событий типа ‘page_view‘ за июнь 2024 года.
SELECT
timestamp,
user_id,
url,
duration_ms
FROM
my_first_db.access_logs
WHERE
event_type = 'page_view' AND toYYYYMM(timestamp) = 202406
ORDER BY
duration_ms DESC
LIMIT 5;
Ключевой момент для производительности: ClickHouse работает молниеносно, когда ваши WHERE условия используют ключ партиционирования (в нашем случае toYYYYMM(timestamp)) и ключ сортировки (у нас это timestamp и user_id). В примере запроса CH сначала отсечет все партиции, кроме 202406, что многократно ускорит выполнение запроса.
Первые аналитические запросы: Сила агрегации
Простая выборка данных — это хорошо, но настоящая магия аналитики начинается с агрегации. Агрегатные функции позволяют вычислять статистику по группам данных. Основные агрегатные функции, которые нужно знать:
- COUNT(): подсчет количества строк.
- SUM(): сумма значений в столбце.
- AVG(): среднее значение.
- MIN() и MAX(): минимальное и максимальное значения.
- COUNT(DISTINCT …): подсчет уникальных значений.
Эти функции используются вместе с оператором GROUP BY, который объединяет строки с одинаковыми значениями в указанных столбцах в одну группу.
Практический пример 1: Посчитать количество событий каждого типа.
SELECT
event_type,
count() AS event_count
FROM
my_first_db.access_logs
GROUP BY
event_type;
Практический пример 2: Найти количество уникальных пользователей и среднюю продолжительность события для каждого типа.
SELECT
event_type,
count(DISTINCT user_id) AS unique_users,
avg(duration_ms) AS avg_duration_ms
FROM
my_first_db.access_logs
GROUP BY
event_type;
Оператор HAVING используется для фильтрации уже сгруппированных данных. Например, оставим только те типы событий, где было более 5 событий.
SELECT
event_type,
count() AS event_count
FROM
my_first_db.access_logs
GROUP BY
event_type
HAVING
event_count > 5;
Заключение
Поздравляем! Вы сделали огромный шаг вперед. Сегодня мы научились выполнять две фундаментальные операции в ClickHouse: загружать данные различными способами и извлекать из них осмысленную информацию с помощью SELECT и агрегатных функций. Вы теперь не просто создаете таблицы, а можете задавать им вопросы и получать ответы.
В следующей статье мы продолжим совершенствовать наши SQL-навыки и погрузимся в продвинутые функции: будем мастерски работать со строками и датами, использовать условные выражения и многое другое, что позволит решать еще более сложные аналитические задачи.
Использованные референсы и материалы
- Официальная документация ClickHouse: https://clickhouse.com/docs/en/
- Документация по установке через Docker: https://hub.docker.com/r/clickhouse/clickhouse-server
- Статья по типам данных и движкам ClickHouse
