Базы данных — один из важнейших инструментов в арсенале аналитика. А ClickHouse — это высокопроизводительная аналитическая СУБД, которая заточена на то, чтобы переваривать огромные массивы данных. Поэтому полезно будет разобраться, как самостоятельно установить ClickHouse в Yandex Cloud или на VDS-сервере, как создать пользователей и активировать веб-интерфейс и доступ по сети. Этим и займемся в статье.
Дисклеймер: Я Женя Кузнецов, увлеченный диджитал-стратег, который любит копаться в данных и визуализировать их. Но я не инженер данных, поэтому могу вольно интерпретировать некоторые понятия — хотя факты стараюсь проверять.
Зачем мне впервые понадобилась база данных?
Дело в том, как устроена Яндекс Метрика и коннекторы к ней из Yandex DataLens.
В Logs API Яндекс Метрики данные о достижении целей собраны в несколько массивов:
ym:s:goalsID — номера целей, достигнутых за данный визит;
ym:s:goalsSerialNumber — порядковые номера достижений цели с конкретным идентификатором;
ym:s:goalsDateTime — время достижения каждой цели (в часовом поясе UTC+3).
А в коннекторе DataLens к Яндекс Метрике доступны только:
общее количество достижений любой цели,
конверсия в достижение любой цели,
текстовое поле с названием достигнутой цели.
И если при построении чарта сделать фильтрацию по этому тексту (т. е. попытаться вывести количество достижений цели и отфильтровать их по названию достигнутой цели) — то данные с метрикой разойдутся, причем значительно.
Также не получится посчитать и производные метрики вроде конверсии. Данные по отдельным целям доступны только в Logs Api Яндекс Метрики.
Есть лайфхак, как обойти это, не выгружая данные, но он работает, только если нужно отслеживать какую-то одну макроконверсию. Об этом я расскажу в отдельном материале.
Почему ClickHouse?
ClickHouse — это высокопроизводительная аналитическая система управления базами данных (СУБД) с открытым исходным кодом. Яндекс разработал ее для решения задач веб-аналитики в Яндекс Метрике.
Что важно, на мой взгляд:
система быстрая и заточена под работу с большими массивами данных,
используется в крупных продуктовых командах, поэтому уметь в ней работать — плюс к резюме,
она опенсорсная.
К тому же ClickHouse использует собственный диалект SQL, близкий к стандартному, но содержащий различные расширения: массивы и вложенные структуры данных, функции высшего порядка, вероятностные структуры, функции для работы с URI.
Подробнее про особенности ClickHouse можно почитать в официальной документации.
Итак, есть проблема, мы выбрали инструмент. Осталось только разобраться, как им пользоваться. И тут нам доступны варианты:
Простой: развернуть ClickHouse в Yandex Cloud. За простоту придется платить, причем около 5 тысяч рублей в месяц в минимальной конфигурации. Но зато все можно сделать в простом и наглядном веб-интерфейсе.
Продвинутый: развернуть ClickHouse на собственной виртуальной машине (VDS).
Мне в работе нужно обращаться к базе извне, поэтому вариант, как развернуть ClickHouse локально, я не рассматриваю.
ClickHouse в облаке
Кратко процедура создания ClickHouse в облаке описана в кейсе «Веб-аналитика с расчетом воронок и когорт на данных Яндекс Метрики», но там не хватает скриншотов — поэтому кратко пробегусь по процессу настройки.
1. Переходим в Managed Service for ClickHouse и выбираем «Создать кластер ClickHouse».
2. Выбираем конфигурацию сервера. Под простенькие задачи хватит и минимальной.
3. Важно сделать кластер публичным — в блоке «Хосты» нажимаем на карандаш и включаем опцию «Публичный доступ».
4. В блоке «Настройки СУБД» можно выключить управление пользователями через SQL, указать имя пользователя, пароль и имя базы данных.
5. В блоке «Сервисные настройки» нужно включить опции: «Доступ из DataLens», «Доступ из консоли управления», «Доступ из Метрики и AppMetrica», «Доступ из Serverless».
В течение нескольких минут кластер ClickHouse будет создан.
ClickHouse на своем сервере
Этот путь делится на два шага: покупку VDS и собственно установку ClickHouse.
Покупка VDS
1. Для покупки виртуальной машины нужно определиться с хостингом — я выбрал NetAngels.
Обратите внимание на минимальные требования из документации — не менее 4 ГБ оперативной памяти. Я пробовал запускать на 2 ГБ — машина зависала во время выполнения даже несложных SQL-запросов.
2. После выбора через нескольких минут создастся виртуальная машина, а на почту придут реквизиты для доступа по SSH. Я выбрал сборку от NetAngels на базе Debian 11.
3. Переходим в терминал и выполняем все шаги установки.
Я не заморачивался и сделал всё в веб-версии.
Установка ClickHouse
1. Устанавливаем или проверяем, установлены ли следующие пакеты:
apt-transport-https — для возможности взаимодействовать с репозиториями по https;
ca-certificates — набор корневых сертификатов;
dirmngr — для управления сетевыми сертификатами.
sudo apt-get install -y apt-transport-https ca-certificates dirmngr |
В моем случае пакеты уже установлены.
2. Настраиваем ключи.
GNUPGHOME=$(mktemp -d) sudo GNUPGHOME="$GNUPGHOME" gpg --no-default-keyring --keyring /usr/share/keyrings/clickhouse-keyring.gpg --keyserver hkp://keyserver.ubuntu.com:80 --recv-keys 8919F6BD2B48D754 sudo rm -rf "$GNUPGHOME" sudo chmod +r /usr/share/keyrings/clickhouse-keyring.gpg |
3. Указываем репозиторий.
echo "deb [signed-by=/usr/share/keyrings/clickhouse-keyring.gpg] https://packages.clickhouse.com/deb stable main" | sudo tee \ /etc/apt/sources.list.d/clickhouse.list |
4. Обновляем кэш.
sudo apt-get update |
5. Запускаем установку сервера и клиента. Тут возможны разные варианты.
Быстрая установка без лишних вопросов (команда -y говорит отвечать YES во всех вопросах). Для пользователя default не будет установлен пароль, но, с другой стороны, мы скоро его деактивируем.
sudo apt-get install -y clickhouse-server clickhouse-client |
Установка без пропуска вопросов — нам будет предложено задать пароль для пользователя default.
sudo apt-get install clickhouse-server clickhouse-client |
Также мы можем задать специфическую версию при установке. Первый раз я ставил именно так, поскольку без принудительного указания ставились достаточно старые версии (что-то из серии 19.1.*)
sudo apt-get install clickhouse-server=24.1.5.6 clickhouse-client=24.1.5.6 clickhouse-common-static=24.1.5.6 |
Список всех версий можно найти на GitHub.
Все, ClickHouse установлен!
Подробная справка по установке — в официальной документации ClickHouse.
Запуск сервера и авторизация
Тут же нам подсказывают две основные команды — для запуска сервера и для подключения как пользователь default.
1. Запускаем сервер.
sudo clickhouse start |
2. Авторизуемся как пользователь default (понадобится заданный ранее пароль).
clickhouse-client |
3. Выполняем SQL-запрос, чтобы убедиться, что все работает.
SELECT 1 |
Конфигурация сервера
Файлы конфигурации в формате .xml лежат в папке /etc/clickhouse-server/. Причем можно либо напрямую править основной файл конфигурации, либо создать подпапку config.d по адресу etc/clickhouse-server/config.d/ и в нее положить свой файл конфигурации. Эти файлы будут объединены до того, как конфигурация вступит в силу.
Важно не забывать, что после изменения настроек нужно перезапускать сервер. Для этого могут быть использованы команды:
# Перезагрузка конфигурации сервера sudo service clickhouse-server reload |
# Полная перезагрузка сервера sudo service clickhouse-server restart |
Подробнее о конфигурации — в официальной справке.
Создание пользователей
Теперь наша задача создать пользователей, из-под которых мы будем выполнять все SQL-запросы. Для этого нужно пройти несколько шагов.
1. Включаем SQL User Mode для пользователя Default. Когда мы задали пароль при установке — в папке users.d создался файл default-password.xml с паролем пользователя. Необходимо добавить в него команды:
<clickhouse> <users> <default> <password remove='1' /> <password_sha256_hex>65e84be33532fb784c48129675f9eff3a682b27168c0ea744b2cf58ee02337c5</password_sha256_hex>
<access_management>1</access_management> <named_collection_control>1</named_collection_control> <show_named_collections>1</show_named_collections> <show_named_collections_secrets>1</show_named_collections_secrets> </default> </users> </clickhouse> |
2. Перезапускаем сервер, чтобы настройки применились.
sudo service clickhouse-server restart |
Создание администратора для базы данных
1. Входим как пользователь default, чтобы создать администратора для базы данных, который сможет создавать базы, таблицы и пользователей, а также выдавать доступы.
CREATE USER clickhouse_admin IDENTIFIED BY 'qwerty'; |
2. Выдаем этому пользователю права на все базы и таблицы.
GRANT ALL ON *.* TO clickhouse_admin WITH GRANT OPTION; |
Подробнее про SQL-пользователей и роли — в справке ClickHouse.
Проверка администратора и создание базы данных
1. Перезайдем под новым пользователем и проверим, что все работает.
clickhouse-client --user clickhouse_admin --password qwerty |
2. Создадим базу данных и пользователя для нее.
CREATE DATABASE test_database; Создадим таблицу. CREATE TABLE test_database.test_table ( id UInt64, column1 String, column2 String ) ENGINE MergeTree ORDER BY id; |
3. Заполним таблицу данными.
INSERT INTO test_database.test_table (id, column1, column2) VALUES (1, 'A', 'abc'), (2, 'A', 'def'), (3, 'B', 'abc'), (4, 'B', 'def'); |
4. Посмотрим содержимое таблицы.
SELECT * FROM test_database.test_table |
Создание и проверка пользователя
1. Создадим отдельного пользователя с правами только к созданной базе.
CREATE USER clickhouse_user IDENTIFIED BY 'password'; GRANT ALL ON test_database.* TO clickhouse_user WITH GRANT OPTION; |
Подробнее про выдачу прав пользователям — в справке ClickHouse.
2. Проверим пользователя. Для этого сначала авторизуемся.
clickhouse-client --user clickhouse_user --password password |
А затем выполним запрос к нашей базе.
SELECT * FROM test_database.test_table |
На этом мы закончили с созданием пользователей:
создали админа, из-под которого мы можем создавать пользователей и базы данных,
создали пользователя с доступом только к созданной базе test_database.
Отключение SQL User Mode и ограничение прав пользователя default
Чтобы отозвать права на управление у пользователя default, изменим настройки в файле default-password.xml.
<clickhouse> <users> <default> <password remove='1' /> <password_sha256_hex>65e84be33532fb784c48129675f9eff3a682b27168c0ea744b2cf58ee02337c5</password_sha256_hex> <profile>readonly</profile> <access_management>0</access_management> <!-- <named_collection_control>1</named_collection_control> <show_named_collections>1</show_named_collections> <show_named_collections_secrets>1</show_named_collections_secrets> --> </default> </users> </clickhouse> |
Справка по управлению доступом в ClickHouse.
Также мы можем полностью отключить пользователя default. Для этого достаточно скорректировать открывающий тег.
<default remove="remove"> </default> |
Активация возможности подключаться по сети
По умолчанию ClickHouse слушает запросы на локальной петле и не принимает запросов по сети. Для решения задачи нам нужно разрешить обработку сетевых запросов.
1. В папке etc/clickhouse-server/config.d/ создадим файл default-config.xml и добавим в него следующие строки.
<clickhouse> <listen_host>::</listen_host> </clickhouse> |
2. Перезагрузим сервер.
sudo service clickhouse-server restart |
Теперь ClickHouse доступен по IP-адресу сервера через порт 8123. Безопасное соединение через https я не настраивал.
В моем случае это:
http://213.189.220.34:8123/ |
Активация веб-интерфейса Tabix
Если мы хотим иметь веб-интерфейс по адресу нашего сервера, необходимо активировать Tabix (опенсорсный визуальный интерфейс для ClickHouse).
1. Скорректируем наш файл конфигурации default-config.xml.
<clickhouse> <listen_host>::</listen_host> <http_server_default_response><![CDATA[<html ng-app="SMI2"><head><base href="http://ui.tabix.io/"></head><body><div ui-view="" class="content-ui"></div><script src="http://loader.tabix.io/master.js"></script></body></html>]]></http_server_default_response> </clickhouse> |
2. Перезапустим сервер.
sudo service clickhouse-server restart |
Теперь веб-интерфейс доступен по ссылке — осталось только ввести логин и пароль.
Авторизация пользователя в Tabix
При попытке авторизоваться как пользователь я столкнулся с ошибкой в консоли разработчика — Tabix ругался на права к таблице system.dictionaries.
Code: 497. DB::Exception: clickhouse_user: Not enough privileges. To execute this query, it's necessary to have the grant SELECT(name, `attribute.names`, `attribute.types`, key) ON system.dictionaries. (ACCESS_DENIED) (version 24.1.5.6 (official build)) |
Для решения этой ошибки надо зайти под админом и выдать пользователю права на соответствующую таблицу.
GRANT SELECT ON system.dictionaries TO clickhouse_user WITH GRANT OPTION; |
После авторизации можно полноценно пользоваться базой данных.
Подключение к серверу
Проверим подключение с помощью Python. Для этого импортируем библиотеку clickhouse_connect.
import clickhouse_connect Укажем реквизиты сервера, попробуем подключиться и узнать версию базы данных. client = clickhouse_connect.get_client( host='213.189.220.34', port=8123, username='clickhouse_user', password='password' ) client.server_version |
Подробнее про clickhouse_connect — в официальной справке ClickHouse.
Вместо заключения
Мы используем ClickHouse в своем стеке технологий, чтобы:
— строить дашборды на данных Метрики, рекламных систем и CRM (для нас и для заказчиков),
— визуализировать спрос (про это читайте другую мою статью DIY-маркетинг: как проанализировать спрос на рынке с помощью KeyCollector, Python и DataLens),
— собирать SEO-дашборды на данных API панелей вебмастеров,
— исследовать модели атрибуции (строить отчет в разных срезах).
В общем, если хотите работать с данными, нужна база, в которой можно их собирать, хранить и исследовать. Не всегда под рукой есть девопс, который все сам сделает. Не всегда есть 5 тысяч в месяц на базу в Яндекс Облаке, чтобы все было в визуальном интерфейсе и без сложностей. Поэтому стоит научиться разворачивать базу для себя.
Причем ClickHouse — не единственный вариант, есть куча других БД, которые можно использовать. Но именно ClickHouse справляется даже с огромными массивами данных о крупных продуктах, так что опыт работы с ним будет очень полезен.
Если после прочтения моей статьи вам захотелось узнать больше подробностей о том, как развернуть ClickHouse для своих нужд, — вы можете пройти мини-курсы Яндекса:
Витрина данных для веб‑аналитики в ClickHouse®,
Managed Service for ClickHouse®.
