Макетирование ETL-DWH-BI с использованием ClickHouse и Metabase

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

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

Основные вводные для построения аналитической системы были сформулированы следующим образом:

1) Основная OLTP-система, из которой надо получать данные, в качестве БД использует PostgreSQL. Есть и другие системы, но объем данных в них довольно небольшой, поэтому для макетирования берем только эту – основную. В БД этой системы порядка 2 десятков таблиц, на основании которых надо строить аналитику. Из этих 2-х десятков таблиц есть 2 довольно большие, каждая из которых имеет примерно по 200 млн. записей. Ежесуточный прирост данных в этих таблицах – в среднем около 300 тыс. записей. Но бывают дни, когда прирост достигает 1 млн. записей. Редко, но бывает.

2) В целях упрощения макета мы решили, что данные в нашей OLTP-системе достаточно чистые, поэтому делать staging мы не будем, на процедуры очистки данных тоже пока забьем.

3) Чтобы не городить огородов, ETL реализуем с помощью врапперов исходных таблиц, используя возможности ClickHouse по интеграции с PostgreSQL и SQL-скриптов, реализующих вставку данных и запускаемых по расписанию.

Надо сказать, что до этого никто из нас не имел опыта работы ни с ClickHouse, ни с Metabase, так что, возможно, мы что-то и накостылили, не зная в достаточной мере возможности инструментария.

Для начала развернули две виртуальные машины, поставили CentOS 7.

С установкой ClickHouse проблем у нас не возникло, но на всякий случай под катом напишу порядок установки.

Hidden text

Установка:

1) Настройка репозитория:

$   sudo yum install -y yum-utils
$   sudo yum-config-manager --add-repo https://packages.clickhouse.com/rpm/clickhouse.repo

2) Установка сервера и клиента:

$	sudo yum install -y clickhouse-server clickhouse-client

Первичная настройка:

1) Создаем каталог для основных файлов сервера: /mnt/clickhouse

И подкаталоги:

Для хранения данных: /mnt/clickhouse/data

Для логов: /mnt/clickhouse/log

Для временных файлов: /mnt/clickhouse/tmp

Для пользовательских данных: /mnt/clickhouse/user_files

2) Назначаем владельца каталога:

$   cd /mnt
$   sudo chown -R clickhouse:clickhouse clickhouse

3) Редактируем файл /etc/clickhouse-server/config.xml для установки каталогов

...
        <log>/mnt/clickhouse/log/clickhouse-server.log</log>
        <errorlog>/mnt/clickhouse/log/clickhouse-server.err.log</errorlog>
...
    <!-- Path to data directory, with trailing slash. -->
    <path>/mnt/clickhouse/data/</path>
...
    <!-- Path to temporary data for processing hard queries. -->
    <tmp_path>/mnt/clickhouse/tmp/</tmp_path>
...
    <!-- Directory with user provided files that are accessible by 'file' table function. -->
    <user_files_path>/mnt/clickhouse/user_files/</user_files_path>

4) Редактируем файл /etc/clickhouse-server/config.xml для обеспечения доступа к серверу с удаленных компьютеров:

<!-- Same for hosts without support for IPv6: -->
<listen_host>0.0.0.0</listen_host>

5) Редактируем файл /etc/clickhouse-server/users.xml для предоставления возможности дефолтному пользователю создавать других пользователей:

           <!-- User can create other users and grant rights to them. -->
           <access_management>1</access_management>

6) Стартуем сервер и проверяем:

$   sudo service clickhouse-server start
$   sudo service clickhouse-server status

7) С помощью clickhouse-client'а создаем нового пользователя и устанавливаем ему все возможные права на создание объектов БД:

$   clickhouse-client
:)  create user admin host any identified with sha256_password by 'admin'
:)  grant all on *.* to admin with grant option
:)  exit

8) Не забываем открыть порт на сервере для доступа с удаленной машины:

$   sudo firewall-cmd --zone=public --permanent --add-port=8123/tcp
$   sudo firewall-cmd –reload

Далее, просто пишем скрипты по созданию структуры нашего хранилища. Структуру хранилища разбили на 2 БД, первая (SOURCE) содержит врапперы для постгресовых удаленных таблиц с использованием движка PostgreSQL, вторая (DWH) – таблицы с использованием движка MergeTree.

Таблицы созданы, пишем скрипты, реализующие выборку новых данных из таблиц БД SOURCE и вставку в соответствующие таблицы DWH. Было решено, что выбирать и грузить данные мы будем пачками по 1 млн записей, что с некоторым запасом соответствует суточному приросту данных в самых больших таблицах. И здесь мы столкнулись с первой проблемой: по привычке, чтобы ограничить кол-во выбираемых строк из исходной таблицы пишем выражение SELECT … FROM … WHERE … LIMIT 1000000. Но не тут-то было, на PostgreSQL приходит запрос без ограничения, в результате чего запрос пытается выбрать все 200 млн записей и падает. Читаем маны, и действительно – клауза WHERE такого селекта выполняется на постгресе, а вот LIMIT будет выполняться уже на стороне ClickHouse. В результате наши запросы принимают вид типа:

INSERT INTO DWH.tablename ( … )
SELECT
…
FROM SOURCE.tablename
WHERE
	id > ( SELECT MAX( id ) FROM DWH.tablename )
AND	id < ( SELECT MAX( id ) + 1000000 FROM DWH.tablename )
ORDER BY id;

Теперь надо написать скрипты по изменению данных в DWH. В исходной OLTP-системе данные не только добавляются, но и бывает модифицируются. Немного, но все же. А ClickHouse не поддерживает UPDATE. Как быть? Помогает Хабр: вот решение - Обновление данных в ClickHouse, спасибо автору.

Написали, проверили, все работает. Делаем набор баш-скриптов, запускающих SQL-скрипты через clickhouse-client, типа такого:

clickhouse-client --queries-file=/mnt/clickhouse/user_files/etl/tablename-import.sql -n -t 

И настраиваем расписание в cron. Для первоначальной загрузки будем запускать наши скрипты раз в 5 минут, потом перейдем на 1 раз в сутки, ночью.

Как результат, первоначальная загрузка 2-х больших таблиц пачками по 1 млн записей была запущена в пятницу вечером и к понедельнику благополучно завершена. Точный тайминг я не засекал, но это было на удивление быстро.

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

Hidden text

1) Для хранения своих данных Metabase поддерживает несколько СУБД. Мы ставили на PostgreSQL 14, просто потому, что мы его любим и в него умеем. Порядок установки PostgreSQL легко гуглится, поэтому расписывать подробности не буду.

2) Ставим OpenJDK 11. Тоже проблем не представляет.

3) Создаем группу и пользователя для Metabase, ставить будем в /opt/metabase:

$   sudo groupadd metabase
$   sudo useradd -s /bin/false -g metabase -d /opt/metabase metabase

4) Создаем на PostgreSQL пользователя и базу:

$   su postgres
$   psql
postgres=# CREATE USER metabase WITH LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION PASSWORD 'metabase';
CREATE ROLE
postgres=# CREATE DATABASE metabase_db WITH OWNER metabase;
CREATE DATABASE

5) В каталог /opt/metabase копируем основной файл приложения metabase.jar

6) В каталоге /opt/metabase создаем подкаталог plugins и копируем в него драйвер для ClickHouse clickhouse.metabase-driver.jar, который берем с GitHub’а (https://github.com/enqueue/metabase-clickhouse-driver)

7) В каталоге /opt/metabase создаем файл metabase.sh:

#! /bin/bash
export MB_DB_TYPE=postgres
export MB_DB_DBNAME=metabase_db
export MB_DB_PORT=5432
export MB_DB_USER=metabase
export MB_DB_PASS=metabase
export MB_DB_HOST=localhost
export MB_JETTY_PORT=9010
java -jar metabase.jar

и делаем его исполняемым:

$   sudo chmod +x metabase.sh

8) Назначаем права на рабочий каталог:

$   cd /opt
$   sudo chown -R metabase:metabase metabase

9) В каталоге /etc/systemd/system создаем файл metabase.service:

[Unit]
Description=Metabase server
After=network.target
 
[Service]
WorkingDirectory=/opt/metabase
ExecStart=/opt/metabase/metabase.sh
User=metabase
Type=simple
SuccessExitStatus=143
TimeoutStopSec=120
Restart=always
 
[Install]
WantedBy=multi-user.target

10) Включаем сервис в автозагрузку:

$   sudo systemctl enable metabase.service

11) Открываем на сервере рабочий порт приложения:

$   sudo firewall-cmd --zone=public --permanent --add-port=9010/tcp
success
$   sudo firewall-cmd --reload
success

12) Стартуем сервис:

$   sudo service metabase start

Дальше вводим в браузере на удаленной машине адрес и порт Metabase и проводим первоначальную настройку BI-сервера, там уже совсем все просто.

Настраиваем подключение к нашему DWH на ClickHouse и собственно, начинаем работать – делаем запросы, гистограммы, сводные отчеты, дашборды и все такое. Документация достаточно подробная, с картинками – разобраться не сложно.

С какими проблемами пришлось еще столкнуться:

1) Оказалось, что тип Date в ClickHouse это совсем не тот же тип date, как в PostgreSQL (сюрпрайз, да). Тип Date в ClickHouse хранит даты, начиная с 1970-01-01, а нам надо было глубже. Пришлось переделывать схему на тип Date32, который может хранить даты, начиная с 1900-01-01. И вроде бы все импортировалось нормально, но … При формировании отчетов на BI нам надо было довольно часто считать разницу дат. А функция DATE_DIFF в качестве аргумента принимает только тип Date и DateTime. А если закастить Date32 в Date, то результат для дат ранее 1970-01-01 оказывается смешным, особенно в графическом отчете. В общем, здесь запросы пришлось костылить. Не страшно, но как-то неаккуратненько получилось.

2) При попытке выборки данных, содержащих тип Bool и имеющих значение null, в отчете возникает исключение. Здесь, видимо, недоработка в драйвере соединения с ClickHouse. Решили проблему созданием вьюхи, с кастингом типа Bool в тип UInt8.

Безусловно, проблемы еще всплывут, особенно, если решение пойдет в прод. Но, основная функциональность работает стабильно и ожидаемо, т.е. решение вполне себе рабочее.

Спасибо за внимание. Замечания, комментарии, пожелания конечно же приветствуются.