Базы данных — один из важнейших инструментов в арсенале аналитика. А ClickHouse — это высокопроизводительная аналитическая СУБД, которая заточена на то, чтобы переваривать огромные массивы данных. Поэтому полезно будет разобраться, как самостоятельно установить ClickHouse в Yandex Cloud или на VDS-сервере, как создать пользователей и активировать веб-интерфейс и доступ по сети. Этим и займемся в статье.
![](https://habrastorage.org/getpro/habr/upload_files/d3f/3bf/d4d/d3f3bfd4d486ed2535a3fd3b69ebb819.png)
Дисклеймер: Я Женя Кузнецов, увлеченный диджитал-стратег, который любит копаться в данных и визуализировать их. Но я не инженер данных, поэтому могу вольно интерпретировать некоторые понятия — хотя факты стараюсь проверять.
Зачем мне впервые понадобилась база данных?
Дело в том, как устроена Яндекс Метрика и коннекторы к ней из Yandex DataLens.
В Logs API Яндекс Метрики данные о достижении целей собраны в несколько массивов:
ym:s:goalsID
— номера целей, достигнутых за данный визит;ym:s:goalsSerialNumber
— порядковые номера достижений цели с конкретным идентификатором;ym:s:goalsDateTime
— время достижения каждой цели (в часовом поясе UTC+3).
А в коннекторе DataLens к Яндекс Метрике доступны только:
общее количество достижений любой цели,
конверсия в достижение любой цели,
текстовое поле с названием достигнутой цели.
И если при построении чарта сделать фильтрацию по этому тексту (т. е. попытаться вывести количество достижений цели и отфильтровать их по названию достигнутой цели) — то данные с метрикой разойдутся, причем значительно.
Также не получится посчитать и производные метрики вроде конверсии. Данные по отдельным целям доступны только в Logs Api Яндекс Метрики.
![](https://habrastorage.org/getpro/habr/upload_files/d56/a09/ff5/d56a09ff5bea38c03579015f1003b5d7.png)
Есть лайфхак, как обойти это, не выгружая данные, но он работает, только если нужно отслеживать какую-то одну макроконверсию. Об этом я расскажу в отдельном материале.
Почему ClickHouse?
ClickHouse — это высокопроизводительная аналитическая система управления базами данных (СУБД) с открытым исходным кодом. Яндекс разработал ее для решения задач веб-аналитики в Яндекс Метрике.
Что важно, на мой взгляд:
система быстрая и заточена под работу с большими массивами данных,
используется в крупных продуктовых командах, поэтому уметь в ней работать — плюс к резюме,
она опенсорсная.
К тому же ClickHouse использует собственный диалект SQL, близкий к стандартному, но содержащий различные расширения: массивы и вложенные структуры данных, функции высшего порядка, вероятностные структуры, функции для работы с URI.
Подробнее про особенности ClickHouse можно почитать в официальной документации.
Итак, есть проблема, мы выбрали инструмент. Осталось только разобраться, как им пользоваться. И тут нам доступны варианты:
Простой: развернуть ClickHouse в Yandex Cloud. За простоту придется платить, причем около 5 тысяч рублей в месяц в минимальной конфигурации. Но зато все можно сделать в простом и наглядном веб-интерфейсе.
Продвинутый: развернуть ClickHouse на собственной виртуальной машине (VDS).
Мне в работе нужно обращаться к базе извне, поэтому вариант, как развернуть ClickHouse локально, я не рассматриваю.
ClickHouse в облаке
Кратко процедура создания ClickHouse в облаке описана в кейсе «Веб-аналитика с расчетом воронок и когорт на данных Яндекс Метрики», но там не хватает скриншотов — поэтому кратко пробегусь по процессу настройки.
1. Переходим в Managed Service for ClickHouse и выбираем «Создать кластер ClickHouse».
![](https://habrastorage.org/getpro/habr/upload_files/e96/beb/5db/e96beb5db086d8dc5ec3e2cd0c33374f.png)
2. Выбираем конфигурацию сервера. Под простенькие задачи хватит и минимальной.
![](https://habrastorage.org/getpro/habr/upload_files/0a6/65b/ce6/0a665bce6ca5bc87740f03e670978eef.png)
3. Важно сделать кластер публичным — в блоке «Хосты» нажимаем на карандаш и включаем опцию «Публичный доступ».
![](https://habrastorage.org/getpro/habr/upload_files/fd3/661/00b/fd366100bd25cb9f112657ffab5f4187.png)
![](https://habrastorage.org/getpro/habr/upload_files/715/ee7/532/715ee7532b6c20593c829650a4c4cda7.png)
4. В блоке «Настройки СУБД» можно выключить управление пользователями через SQL, указать имя пользователя, пароль и имя базы данных.
![](https://habrastorage.org/getpro/habr/upload_files/917/ce6/281/917ce62813b19d3f9f2c7d2cbe5b9a3a.png)
5. В блоке «Сервисные настройки» нужно включить опции: «Доступ из DataLens», «Доступ из консоли управления», «Доступ из Метрики и AppMetrica», «Доступ из Serverless».
![](https://habrastorage.org/getpro/habr/upload_files/822/200/c96/822200c966c2eb3170e8878b475a8c10.png)
В течение нескольких минут кластер ClickHouse будет создан.
![](https://habrastorage.org/getpro/habr/upload_files/953/22c/131/95322c131595434310c67efc972d8417.png)
ClickHouse на своем сервере
Этот путь делится на два шага: покупку VDS и собственно установку ClickHouse.
Покупка VDS
1. Для покупки виртуальной машины нужно определиться с хостингом — я выбрал NetAngels.
Обратите внимание на минимальные требования из документации — не менее 4 ГБ оперативной памяти. Я пробовал запускать на 2 ГБ — машина зависала во время выполнения даже несложных SQL-запросов.
![](https://habrastorage.org/getpro/habr/upload_files/834/5b7/744/8345b7744359ed9a30a435479309329a.png)
2. После выбора через нескольких минут создастся виртуальная машина, а на почту придут реквизиты для доступа по SSH. Я выбрал сборку от NetAngels на базе Debian 11.
![](https://habrastorage.org/getpro/habr/upload_files/d52/f60/63b/d52f6063b8633b6165c5781a84e83351.png)
3. Переходим в терминал и выполняем все шаги установки.
![](https://habrastorage.org/getpro/habr/upload_files/aa3/6f0/b34/aa36f0b349f521a59b135ea9575f57fb.png)
Я не заморачивался и сделал всё в веб-версии.
![](https://habrastorage.org/getpro/habr/upload_files/164/9c0/4e6/1649c04e6793470d9585e865025db05f.png)
Установка ClickHouse
1. Устанавливаем или проверяем, установлены ли следующие пакеты:
apt-transport-https — для возможности взаимодействовать с репозиториями по https;
ca-certificates — набор корневых сертификатов;
dirmngr — для управления сетевыми сертификатами.
sudo apt-get install -y apt-transport-https ca-certificates dirmngr
В моем случае пакеты уже установлены.
![](https://habrastorage.org/getpro/habr/upload_files/da7/74d/7de/da774d7de6a8d0e85a61b90e265e7a4b.png)
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
![](https://habrastorage.org/getpro/habr/upload_files/9d9/602/41f/9d960241f2d1f480f95bd086b2025a00.png)
4. Обновляем кэш.
sudo apt-get update
![](https://habrastorage.org/getpro/habr/upload_files/a3c/948/f87/a3c948f870d39d1322c23ecfc4cb66ea.png)
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.
![](https://habrastorage.org/getpro/habr/upload_files/739/657/7b0/7396577b096ed5a9a39616ff2efac557.png)
![](https://habrastorage.org/getpro/habr/upload_files/738/804/5cc/7388045cc90386579ca1d3371375ef1e.png)
Все, ClickHouse установлен!
Подробная справка по установке — в официальной документации ClickHouse.
Запуск сервера и авторизация
Тут же нам подсказывают две основные команды — для запуска сервера и для подключения как пользователь default.
1. Запускаем сервер.
sudo clickhouse start
![](https://habrastorage.org/getpro/habr/upload_files/016/4d9/c04/0164d9c04f23a99b2b08d62cbd3258ff.png)
2. Авторизуемся как пользователь default (понадобится заданный ранее пароль).
clickhouse-client
3. Выполняем SQL-запрос, чтобы убедиться, что все работает.
SELECT 1
![](https://habrastorage.org/getpro/habr/upload_files/ffa/cfb/4d0/ffacfb4d0fcf8ffe01beec2caa860d62.png)
Конфигурация сервера
Файлы конфигурации в формате .xml лежат в папке /etc/clickhouse-server/
. Причем можно либо напрямую править основной файл конфигурации, либо создать подпапку config.d
по адресу etc/clickhouse-server/config.d/
и в нее положить свой файл конфигурации. Эти файлы будут объединены до того, как конфигурация вступит в силу.
![](https://habrastorage.org/getpro/habr/upload_files/c01/adb/aba/c01adbabaacc752fb0e333fb7913572e.png)
Важно не забывать, что после изменения настроек нужно перезапускать сервер. Для этого могут быть использованы команды:
# Перезагрузка конфигурации сервера
sudo service clickhouse-server reload
![](https://habrastorage.org/getpro/habr/upload_files/a89/7dd/f9d/a897ddf9db7820bcd672fdb920707212.png)
# Полная перезагрузка сервера
sudo service clickhouse-server restart
![](https://habrastorage.org/getpro/habr/upload_files/d7e/b55/03e/d7eb5503e57be424b3a91fd12a12ce0a.png)
Подробнее о конфигурации — в официальной справке.
Создание пользователей
Теперь наша задача создать пользователей, из-под которых мы будем выполнять все 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';
![](https://habrastorage.org/getpro/habr/upload_files/ad5/c28/2d0/ad5c282d0e2fc8a1f39251c8df35b358.png)
2. Выдаем этому пользователю права на все базы и таблицы.
GRANT ALL ON *.* TO clickhouse_admin WITH GRANT OPTION;
![](https://habrastorage.org/getpro/habr/upload_files/6a7/298/4e3/6a72984e3ba2c4d5c4b25d6d8a0a920d.png)
Подробнее про SQL-пользователей и роли — в справке ClickHouse.
Проверка администратора и создание базы данных
1. Перезайдем под новым пользователем и проверим, что все работает.
clickhouse-client --user clickhouse_admin --password qwerty
![](https://habrastorage.org/getpro/habr/upload_files/05b/ee6/0b9/05bee60b9832689dcc0d4872771d098a.png)
2. Создадим базу данных и пользователя для нее.
CREATE DATABASE test_database;
Создадим таблицу.
CREATE TABLE test_database.test_table (
id UInt64,
column1 String,
column2 String
)
ENGINE MergeTree
ORDER BY id;
![](https://habrastorage.org/getpro/habr/upload_files/8b9/1c6/f95/8b91c6f9529b3b22ea249cac0a89718b.png)
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
![](https://habrastorage.org/getpro/habr/upload_files/8ba/374/0dc/8ba3740dcdc36b1eccea4dd43b9ee92b.png)
Создание и проверка пользователя
1. Создадим отдельного пользователя с правами только к созданной базе.
CREATE USER clickhouse_user IDENTIFIED BY 'password';
GRANT ALL ON test_database.* TO clickhouse_user WITH GRANT OPTION;
![](https://habrastorage.org/getpro/habr/upload_files/940/41b/404/94041b404884fe3d6c58657cda80aa20.png)
Подробнее про выдачу прав пользователям — в справке ClickHouse.
2. Проверим пользователя. Для этого сначала авторизуемся.
clickhouse-client --user clickhouse_user --password password
А затем выполним запрос к нашей базе.
SELECT *
FROM test_database.test_table
![](https://habrastorage.org/getpro/habr/upload_files/4a4/2bb/178/4a42bb178400f1a2cb364328d0530518.png)
На этом мы закончили с созданием пользователей:
создали админа, из-под которого мы можем создавать пользователей и базы данных,
создали пользователя с доступом только к созданной базе 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.
![](https://habrastorage.org/getpro/habr/upload_files/5c1/c8b/dcc/5c1c8bdcc21dc84bc0c0155effaf2f06.png)
Также мы можем полностью отключить пользователя default. Для этого достаточно скорректировать открывающий тег.
<default remove="remove">
</default>
![](https://habrastorage.org/getpro/habr/upload_files/e44/bcc/ebc/e44bccebcfd2cb2c1d3fdf08a43ca583.png)
Активация возможности подключаться по сети
По умолчанию 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/
![](https://habrastorage.org/getpro/habr/upload_files/cdc/ca0/f79/cdcca0f798ea7d1486bd61386ce83405.png)
Активация веб-интерфейса 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
Теперь веб-интерфейс доступен по ссылке — осталось только ввести логин и пароль.
![](https://habrastorage.org/getpro/habr/upload_files/324/a2a/255/324a2a25571cfa47d79e5f71718cab04.png)
Авторизация пользователя в 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;
![](https://habrastorage.org/getpro/habr/upload_files/ea7/ea3/470/ea7ea347016782594946a61e687240d0.png)
После авторизации можно полноценно пользоваться базой данных.
![](https://habrastorage.org/getpro/habr/upload_files/43d/19a/ea9/43d19aea994f4c659b906f78da07d2c0.png)
Подключение к серверу
Проверим подключение с помощью 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
![](https://habrastorage.org/getpro/habr/upload_files/d3d/3aa/f59/d3d3aaf5962ba5cdd06d1ec6cd3c26c5.png)
Подробнее про clickhouse_connect — в официальной справке ClickHouse.
Вместо заключения
Мы используем ClickHouse в своем стеке технологий, чтобы:
строить дашборды на данных Метрики, рекламных систем и CRM (для нас и для заказчиков),
визуализировать спрос (про это читайте другую мою статью DIY-маркетинг: как проанализировать спрос на рынке с помощью KeyCollector, Python и DataLens),
собирать SEO-дашборды на данных API панелей вебмастеров,
исследовать модели атрибуции (строить отчет в разных моделях).
В общем, если хотите работать с данными, нужна база, в которой можно их собирать, хранить и исследовать. Не всегда под рукой есть девопс, который все сам сделает. Не всегда есть 5 тысяч в месяц на базу в Яндекс Облаке, чтобы все было в визуальном интерфейсе и без сложностей. Поэтому стоит научиться разворачивать базу для себя.
Причем ClickHouse — не единственный вариант, есть куча других БД, которые можно использовать. Но именно ClickHouse справляется даже с огромными массивами данных о крупных продуктах, так что опыт работы с ним будет очень полезен.
Если после прочтения моей статьи вам захотелось узнать больше подробностей о том, как развернуть ClickHouse для своих нужд, — вы можете пройти мини-курсы Яндекса: