ClickHouse – установка и настройка самостоятельно

Одним из важных инструментов в арсенале аналитика данных являются базы данных. Поэтому свой небольшой цикл технических статей я начну именно с установки и настройки ClickHouse.

Сразу оговорюсь, я не инженер данных и могу описывать некоторые термины и понятия так, как я их понимаю, но стараюсь делать фактчек по мере возможностей.

Проблема

Зачем же мне понадобилась база данных и почему я мой выбор пал на ClickHouse? Все достаточно просто — дело в том как устроена Яндекс Метрика и коннекторы к ней из Yandex DataLens.

В Logs API Яндекс Метрики данные о достижении целей собраны в несколько массивов, которые содержат информацию о них:

ym:s:goalsID — номера целей, достигнутых за данный визит
ym:s:goalsSerialNumber — порядковые номера достижений цели с конкретным идентификатором
ym:s:goalsDateTime — время достижения каждой цели (в часовом поясе UTC+3)

А в коннекторе DataLens к Яндекс метрике доступны только:

Общее количество достижений любой цели;
Конверсия в достижение любой цели;
И текстовое поле с названием достигнутой цели;

И вот если при построении чарта сделать фильтрацию по этому текстовому (т.е. попытаться вывести количество достижений цели и отфильтровать их по названию достигнутой цели) — то данные с метрикой разойдутся, причем значительно.

Также не получится посчитать и производные метрики, вроде конверсии. Данные по отдельным целям доступны только в Logs Api Яндекс Метрики.

Есть один лайфхак как обойти это не выгружая данные, но но он работает только если нужно отслеживать какую-то одну макро-конверсию. Об этом я расскажу в отдельном материале.

Почему ClickHouse

Теперь разберемся почему именно ClickHouse.

ClickHouse — это высокопроизводительная аналитическая СУБД с открытым исходным кодом. ClickHouse была разработана в Яндексе для решения задач веб-аналитики в Яндекс.Метрике.

Что на мой взгляд важно в этом выборе:

Она быстрая и заточена под работу с большими массивами данных;
Она используется в крупных продуктовых командах, что делает навыки работы плюсом в резюме;
Она опенсорсная;

К тому же ClickHouse использует собственный диалект SQL близкий к стандартному, но содержащий различные расширения: массивы и вложенные структуры данных, функции высшего порядка, вероятностные структуры, функции для работы с URI.

Подробнее про особенности ClickHouse можно почитать в официальной документации.

И так, проблема есть и актуальна. Инструмент мы выбрали. Осталось только разобраться как начать им пользоваться. И тут нам доступны несколько вариантов:

Вариант 1, простой: развернуть ClickHouse в Yandex Cloud. За простоту придется платить, причем около 5 тысяч рублей в месяц в минимальной конфигурации. Но зато все можно сделать в простом и наглядном веб-интерфейсе.
Вариант 2, продвинутый: развернуть ClickHouse на собственной виртуальной машине (VDS).

Вариант с тем, как развернуть ClickHouse локально рассматривать не буду, поскольку нам нужно иметь возможность обращаться к базе извне.

ClickHouse в облаке

Кратко процедура создания ClickHouse в облаке описана в кейсе «Веб-аналитика с расчетом воронок и когорт на данных Яндекс Метрики», но там не хватает скриншотов — поэтому кратко пробегусь по процессу настройки.

Переходим в Managed Service for ClickHouse и выбираем Создать кластер ClickHouse.

Выбираем конфигурацию сервера. Под простенькие задачи хватает и минимальной.

Важно не пропустить пункт и сделать кластер публичным. Для этого в блоке Хосты нажимаем на карандаш и включаем опцию Публичный доступ.

В блоке Настройки СУБД нам предлагается выключить управление пользователями через SQL, а также указать имя пользователя, пароль и имя Базы Данных.

В блоке Сервисные настройки необходимо включить опции: Доступ из DataLens, Доступ из консоли управления, Доступ из Метрики и AppMetrica, Доступ из Serverless.

В течении нескольких минут кластер ClickHouse будет создан.

ClickHouse на своем сервере

Теперь рассмотрим более сложный вариант — установку на VDS-сервере.

Покупка VDS

Нам понадобится виртуальная машина. Мой выбор пал на хостинг NetAngels. Сразу стоит оговориться о минимальных требованиях — в документации рекомендуется не менее 4 ГБ оперативной памяти. Я пробовал запускать на 2 ГБ — машина зависала во время выполнения даже несложных SQL-запросов.

После выбора в течении нескольких минут создается виртуальная машина, а на почту поступят реквизиты для доступа по SSH. Я выбираю сборку от NetAngels на базе Debian 11.

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

Я не заморачивался и сделал все в веб-версии.

Установка ClickHouse

Для установки выполняем следующие команды.

Сначала устанавливаем / проверяем установлены ли следующие пакеты:

apt-transport-https — для возможности взаимодействовать с репозиторими по https;
ca-certificates — набор корневых сертификатов;
dirmngr — для управления сетевыми сертификатами.

sudo apt-get install -y apt-transport-https ca-certificates dirmngr

Как видно в моем случае пакеты уже установлены.

Настраиваем ключи.

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

Указываем репозиторий.

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

Обновляем кэш.

sudo apt-get update

Запускаем установку сервера и клиента. Тут возможны разные варианты.

Быстрая установка без лишних вопросов (команда -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.

Запускаем сервер.

sudo clickhouse start

Авторизуемся как пользователь default (понадобится пароль, заданный ранее).

clickhouse-client

Выполняем SQL-запрос, чтобы убедиться что все работает.

SELECT 1

Авторизуемся как пользователь default (понадобится пароль, заданный ранее).

Конфигурация сервера

Файлы конфигурации в формате .xml лежат в папке /etc/clickhouse-server/ , причем можно либо напрямую править основной файл конфигурации, либо создать подпапку config.d по адресу etc/clickhouse-server/config.d/ и в нее положить свой файл конфигурации. Эти файлы будут объединены до того как кофигурация вступит в силу.

Важно не забывать, что после изменения настроек нужно перезапускать сервер. Для этого могут быть использованы команды:

# Перезагрузка конфигурации сервера
sudo service clickhouse-server reload

# Полная перезагрузка сервера
sudo service clickhouse-server restart

Подробнее о конфигурации ищите в официальной справке.

Создание пользователей

Теперь наша задача создать пользователей, из-под которых мы будем выполнять все SQL-запросы. Для этого нужно пройти несколько шагов

Включить 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>

После этого перезапускаем сервер, чтобы настройки применились.

sudo service clickhouse-server restart

Создание администратора для базы данных

Теперь входим как пользователь default чтобы создать администратора для базы данных, который сможет создавать базы, таблицы и пользователей, а также выдавать доступы.

CREATE USER clickhouse_admin IDENTIFIED BY 'qwerty';

И выдадим этому пользователю права на все базы и таблицы.

GRANT ALL ON *.* TO clickhouse_admin WITH GRANT OPTION;

Подробнее про SQL-пользователей и роли в справке ClickHouse.

Проверка админа и создание базы данных

Теперь перезайдем под новым пользователем и проверим что все работает.

clickhouse-client --user clickhouse_admin --password qwerty

Создадим базу данных и пользователя для неё.

CREATE DATABASE test_database;

Создадим таблицу.

CREATE TABLE test_database.test_table (
    id UInt64,
    column1 String,
    column2 String
)
ENGINE MergeTree
ORDER BY id;

Заполним таблицу данными.

INSERT INTO test_database.test_table
    (id, column1, column2)
VALUES
    (1, 'A', 'abc'),
    (2, 'A', 'def'),
    (3, 'B', 'abc'),
    (4, 'B', 'def');

Посмотрим содержимое таблицы.

SELECT *
FROM test_database.test_table

Создание и проверка пользователя

Создадим отдельного пользователя с правами только к созданной базе.

CREATE USER clickhouse_user IDENTIFIED BY 'password';

GRANT ALL ON test_database.* TO clickhouse_user WITH GRANT OPTION;

Подробнее про выдачу прав пользователям в справке ClickHouse.

Проверим пользователя. Для этого сначала авторизуемся.

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-password.xml.

Также мы можем польностью отключить пользователя default. Для этого достаточно скорректировать открывающий тег .

<default remove="remove">
</default>

Активация возможности подключаться по сети

По умолчанию, кликхаус слушает запросы на локальной петле и не принимает запросов по сети. Для решения задачи нам нужно разрешить обработку сетевых запросов.

В папке etc/clickhouse-server/config.d/ создадим файл default-config.xml и добавим в него следующие строки.

<clickhouse>
    <listen_host>::</listen_host>
</clickhouse>

После этого также перезагружаем сервер.

sudo service clickhouse-server restart

Теперь ClickHouse доступен по IP-адресу сервера через порт 8123. Безопасное соединение через https я не настраивал.

# В моем случае это
http://213.189.220.34:8123/

Активация веб-интерфейса Tabix

Если мы хотим иметь веб-интерфейс по адресу нашего сервера — необходимо активировать Tabix.

Для этого скорректируем наш файл конфигурации 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>

И перезапустим сервер.

sudo service clickhouse-server restart

Теперь веб-интерфейс доступен по ссылке, останется только ввести логин и пароль.

При попытке авторизоваться как пользователь я столкнулся с ошибкой в консоли разработчика — 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;

При попытке авторизоваться как пользователь я столкнулся с ошибкой в консоли разработчика — Tabix ругался в консоли на права к таблице system.dictionaries.

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

Подключение к серверу

Также проверим подключение с помощью 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

Также проверим подключение с помощью Python.

Подробнее про clickhouse_connect в официальной справке ClickHouse.

Вместо заключения

Конечно данная статья не сделает из вас инженера данных, но разобраться с тем как развернуть базу данных ClickHouse для своих нужд поможет.

Также у Яндекса есть мини-курсы:

Витрина данных для веб‑аналитики в ClickHouse^®

Managed Service for ClickHouse^®

Инженер на минималках: установка и настройка ClickHouse