Базы данных — один из важнейших инструментов в арсенале аналитика. А 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