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

С целью изучения возможности построения аналитической системы с использованием БД 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 проблем у нас не возникло, но на всякий случай под катом напишу порядок установки.

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

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

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

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

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

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

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

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

$   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. В принципе, на сайте разработчиков есть достаточно подробная инструкция, но, тем не менее, напишу последовательность установки под катом, может кому пригодится.

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

$   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

#! /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

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

[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

$   sudo systemctl enable metabase.service

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

$   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.

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

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