1. Главная
  2. Хаб
  3. Сценарии
  4. Продуктовая аналитика на ClickHouse: дашборды в Metabase и пользователь только на чтение

Сценарий 29

Продуктовая аналитика на ClickHouse: дашборды в Metabase и пользователь только на чтение

Складываем события продукта в управляемый ClickHouse, поднимаем Metabase на облачном сервере и строим дашборд: активные пользователи, воронка и выручка по странам. BI ходит в базу от отдельного пользователя только на чтение и с ограничением на тяжёлые запросы.

50 минСредний7 шагов ClickHouse, Серверы обновлено

Содержание
  1. 01Создайте ClickHouse
  2. 02Заведите владельца данных и читателя для BI
  3. 03Создайте сервер для Metabase
  4. 04Загрузите события продукта
  5. 05Установите Metabase
  6. 06Подключите ClickHouse к Metabase
  7. 07Соберите дашборд
  8. Проверка результата
  9. Как удалить ресурсы
  10. Типичные ошибки
  11. Сколько стоит

Что получится

ClickHouse с таблицей событий product.events, сервер с Metabase, подключённым по TLS с проверкой сертификата, пользователь bi_reader с пресетом readonly, профилем small и квотой basic, и дашборд из трёх графиков.

Что понадобится

  • Аккаунт h3llo.cloud, проект и SSH-ключ в профиле консоли.
  • CLI h3 с API-ключом проекта (h3 init): пользователи ClickHouse создаются через API.
  • Если события уже идут через Kafka — сценарий о потоке событий из Kafka в ClickHouse: дашборд можно строить по его таблице.
  1. Шаг 1. Создайте ClickHouse

    Консоль

    1. В меню проекта откройте Базы данных и нажмите Создать базу данных (если базы уже есть — Создать).
    2. СУБД — ClickHouse 26.8. vCPU — 2, RAM — 4 ГБ, Диски — 20 ГБ.
    3. Имя базы данных — product-ch. Сеть оставьте предложенной, публичный IP не включайте: Metabase будет ходить в базу из той же сети.
    4. Нажмите Создать базу данных.

    CLI

    Терминал
    h3 db create --name product-ch --engine clickhouse --version 26.8 --cpu 2 --memory 4Gi --size 20

    Проверка

    Статус — «Работает». В блоке «Подключение» на вкладке «Внутри VPC» — Адрес в VPC: запишите его. Нажмите Скачать сертификат CA — файл h3llo-db-ca.pem понадобится клиентам.

  2. Шаг 2. Заведите владельца данных и читателя для BI

    product_admin владеет базой product (пресет admin: таблицы и представления). bi_reader — для Metabase: пресет readonly даёт SELECT, SHOW и dictGet и ничего не меняет. Профиль small ограничивает запрос четвертью памяти сервера и 60 секундами, квота basic — не больше 10 000 запросов и часа суммарного времени выполнения в час, так что случайный тяжёлый график не мешает загрузке данных.

    CLI

    Терминал
    h3 db user create <id ClickHouse> --name product_admin
    h3 db database create <id ClickHouse> --name product --owner product_admin
    echo '{"name":"bi_reader","grants":[{"database":"product","preset":"readonly"}],"profile":"small","quota":"basic"}' | \
      h3 db user create <id ClickHouse> --from-file -
    h3 db user list <id ClickHouse>

    Проверка

    Оба пользователя в статусе «READY». Пароли сохраните из ответов команд или посмотрите позже: h3 db user credentials <id ClickHouse> bi_reader.

  3. Шаг 3. Создайте сервер для Metabase

    Консоль

    1. Откройте Инстансы и нажмите Создать. ОС → Образы ОС → Ubuntu 24.04 LTS.
    2. vCPU — 2, RAM — 4 ГБ, Диски — 20 ГБ. Имя виртуальной машины — bi-1, Сеть — та же, что у базы.
    3. SSH-ключи — ваш ключ, Публичный IPv4 — «Вкл.», «Выделить новый IP». Нажмите Собрать и запустить.

    Terraform

    main.tf
    resource "h3_vm" "bi" {
      project_id = var.project_id
      name       = "bi-1"
      cpu        = 2
      memory     = "4Gi"
      image      = "ubuntu:24.04"
      disk_size  = "20Gi"
      ssh_key    = trimspace(var.ssh_public_key)
      white_ip   = true
    }

    CLI

    Терминал
    h3 vm create --name bi-1 --image ubuntu:24.04 --cpu 2 --memory 4Gi --disk-size 20Gi --ssh-key-ids <id ключа> --white-ip

    Проверка

    Сервер в статусе «Работает», ssh user@<IP сервера> пускает.

  4. Шаг 4. Загрузите события продукта

    Таблица событий — одна широкая таблица: кто, что сделал, когда и с какими свойствами. Для примера сгенерируем миллион событий за 30 дней; в жизни их пишет приложение (INSERT) или поток из Kafka.

    Сертификат базы выдан на имя db.h3llo.cloud — сопоставим его с адресом базы, чтобы клиенты проверяли сертификат строго.

    На сервере bi-1
    echo "<адрес в VPC> db.h3llo.cloud" | sudo tee -a /etc/hosts
    curl https://clickhouse.com/ | sh
    cat > ch.xml <<'EOF'
    <config>
      <openSSL><client>
        <loadDefaultCAFile>false</loadDefaultCAFile>
        <caConfig>/home/user/h3llo-db-ca.pem</caConfig>
        <verificationMode>strict</verificationMode>
        <invalidCertificateHandler><name>RejectCertificateHandler</name></invalidCertificateHandler>
      </client></openSSL>
    </config>
    EOF
    events.sql
    CREATE TABLE product.events
    (
        ts      DateTime,
        user_id UInt64,
        event   LowCardinality(String),
        country LowCardinality(String),
        amount  Decimal(12, 2)
    )
    ENGINE = MergeTree
    PARTITION BY toYYYYMM(ts)
    ORDER BY (event, ts, user_id);
    
    INSERT INTO product.events
    SELECT
        now() - toIntervalSecond(rand() % (30 * 86400)) AS ts,
        rand64() % 20000 AS user_id,
        ['visit', 'visit', 'visit', 'signup', 'add_to_cart', 'purchase'][1 + rand() % 6] AS event,
        ['RU', 'RU', 'KZ', 'BY', 'AM'][1 + rand() % 5] AS country,
        if(event = 'purchase', (rand() % 500000) / 100, 0) AS amount
    FROM numbers(1000000);
    Выполнить от product_admin
    ./clickhouse client --config-file ch.xml --secure --host db.h3llo.cloud --port 9440 \
      --user product_admin --password '<пароль product_admin>' --queries-file events.sql

    Проверка

    В таблице миллион строк:

    Терминал
    ./clickhouse client --config-file ch.xml --secure --host db.h3llo.cloud --port 9440 \
      --user bi_reader --password '<пароль bi_reader>' --query 'SELECT event, count() FROM product.events GROUP BY event'
  5. Шаг 5. Установите Metabase

    Metabase — открытая BI-система: запускается одним JAR-файлом и хранит свои настройки (вопросы, дашборды, пользователей) во встроенной базе. Порт 3000 наружу не открываем — заходим через SSH-туннель.

    На сервере bi-1
    sudo apt update && sudo apt install -y openjdk-21-jre-headless
    sudo useradd -r -m -d /opt/metabase metabase
    sudo mkdir -p /opt/metabase/plugins
    # metabase.jar — со страницы https://www.metabase.com/start/oss/jar
    sudo cp metabase.jar /opt/metabase/ && sudo cp h3llo-db-ca.pem /opt/metabase/
    sudo chown -R metabase: /opt/metabase
    /etc/systemd/system/metabase.service
    [Unit]
    Description=Metabase
    After=network-online.target
    
    [Service]
    User=metabase
    WorkingDirectory=/opt/metabase
    Environment=MB_JETTY_HOST=127.0.0.1
    Environment=MB_PLUGINS_DIR=/opt/metabase/plugins
    ExecStart=/usr/bin/java -Xmx2g -jar /opt/metabase/metabase.jar
    Restart=on-failure
    
    [Install]
    WantedBy=multi-user.target
    Запуск и туннель
    sudo systemctl daemon-reload && sudo systemctl enable --now metabase
    # на вашем компьютере:
    ssh -L 3000:127.0.0.1:3000 user@<IP сервера>

    Проверка

    Через минуту-две http://localhost:3000 на вашем компьютере открывает мастер первоначальной настройки Metabase. Создайте администратора.

    Если не получилось

    • ClickHouse нет в списке баз Metabase — в вашей версии драйвер не встроен. Скачайте clickhouse.metabase-driver.jar из релизов ClickHouse/metabase-clickhouse-driver на GitHub (версию по таблице совместимости в README драйвера), положите в /opt/metabase/plugins и перезапустите службу.
  6. Шаг 6. Подключите ClickHouse к Metabase

    Консоль

    1. В Metabase: Admin settings → Databases → Add database → ClickHouse.
    2. Host db.h3llo.cloud, Port 8443, Databases product, Username bi_reader, Password — пароль bi_reader.
    3. Включите Use a secure connection (SSL). В Additional JDBC connection string options укажите sslmode=strict&sslrootcert=/opt/metabase/h3llo-db-ca.pem.
    4. Нажмите Save.

    Проверка

    Metabase сообщает об успешном подключении и показывает таблицу events в разделе Browse data.

    Если не получилось

    • PKIX path building failed или ошибка сертификата — Java не видит CA: проверьте путь в sslrootcert и что файл читается пользователем metabase.
    • UnknownHostException db.h3llo.cloud — на сервере нет строки в /etc/hosts с адресом базы.
  7. Шаг 7. Соберите дашборд

    Создайте три вопроса через New → SQL query, сохраните их и добавьте на новый дашборд «Продукт». ClickHouse считает их за доли секунды даже на сотнях миллионов строк.

    Активные пользователи по дням (DAU)
    SELECT toDate(ts) AS day, uniq(user_id) AS dau
    FROM product.events
    GROUP BY day ORDER BY day
    Воронка: визит → регистрация → корзина → покупка за 7 дней
    SELECT level, count() AS users
    FROM (
        SELECT user_id,
               windowFunnel(7 * 86400)(ts, event = 'visit', event = 'signup', event = 'add_to_cart', event = 'purchase') AS level
        FROM product.events
        GROUP BY user_id
    )
    WHERE level > 0
    GROUP BY level ORDER BY level
    Выручка по странам
    SELECT country, sum(amount) AS revenue, uniqIf(user_id, event = 'purchase') AS buyers
    FROM product.events
    WHERE ts > now() - INTERVAL 30 DAY
    GROUP BY country ORDER BY revenue DESC

    Проверка

    На дашборде три графика. Фильтр по дате Metabase подставляет в запрос условие на ts — секционирование по месяцам отсекает лишние данные.

Проверка результата

BI не может испортить данные: у bi_reader только чтение. Попытка изменить таблицу завершается ACCESS_DENIED:

Терминал
./clickhouse client --config-file ch.xml --secure --host db.h3llo.cloud --port 9440 \
  --user bi_reader --password '<пароль bi_reader>' --query 'TRUNCATE TABLE product.events'

Как удалить ресурсы

Удаление базы удаляет все события. Дашборды хранятся в Metabase на сервере и удаляются вместе с ним.

CLI

Терминал
h3 db delete <id ClickHouse>
h3 vm delete <id сервера>
  1. Базы данных → product-ch → меню «…» → Удалить.
  2. Инстансы → bi-1 → Удалить. В разделе IP адреса удалите освободившийся адрес — свободные адреса оплачиваются.

Типичные ошибки

Где Metabase хранит свои настройки в продакшене?

Встроенная база подходит для пробы. Для продакшена храните настройки в Managed PostgreSQL: задайте службе `MB_DB_TYPE=postgres` и `MB_DB_CONNECTION_URI` со строкой подключения к базе в той же сети — тогда сервер с Metabase можно пересоздать без потери дашбордов.

Запросы Metabase падают с превышением памяти или времени

Это сработал профиль `small`. Оптимизируйте запрос (фильтр по `ts`, меньше колонок) или смените профиль: `h3 api PATCH /api/databases/v1/<id ClickHouse>/users/bi_reader -d '{"profile":"default"}'` — применяется без перезапуска.

Можно ли вместо Metabase взять Grafana или Superset?

Да. Подключение то же: хост `db.h3llo.cloud` с адресом базы в `/etc/hosts`, порт `8443` (HTTPS) или `9440` (нативный протокол), TLS с файлом CA и пользователь с пресетом `readonly`.

Нужно открыть дашборды коллегам без SSH

Поставьте перед Metabase веб-сервер с HTTPS (как в сценарии «Сайт на облачном сервере») и ограничьте вход группой безопасности по адресам офиса.

Связанные продукты и материалы

Сколько стоит

≈ 10,60 ₽ в час · 7 632 ₽ в месяц при работе 24/7

vCPU ClickHouse2 × 1 035 ₽
RAM ClickHouse, ГБ4 × 414 ₽
Диск ClickHouse до 20 000 IOPS*, ГБ20 × 14,40 ₽
vCPU сервера Metabase2 × 900 ₽
RAM сервера Metabase, ГБ4 × 360 ₽
Диск сервера до 20 000 IOPS*, ГБ20 × 14,40 ₽
Публичный IPv4 сервера1 × 90 ₽

Metabase открытый и бесплатный, оплачивается только сервер под него. Трафик между сервером и базой внутри VPC бесплатен.

Цены из прайс-листа, по которому работает биллинг; списание поминутное, месяц — 720 часов. Если удалить ресурсы после проверки, сценарий обойдётся в рубли.

Новым пользователям — 4 000 ₽ на 30 дней на любые сервисы: бонуса хватит, чтобы попробовать этот сценарий целиком. Бонус начисляется после подтверждения телефона и привязки карты.

* IOPS растут с объёмом диска; точные значения для вашего объёма видны в консоли при выборе.