Данные в ClickHouse на базе Docker и Python. Все коннекторы

Когда я работал в рекламном агентстве, данные были настоящей болью. Раньше мы пользовались PostgreSQL, который поддерживался разработчиками. Но из-за строгих правил транзакционной БД и необходимости вмешательства программиста постоянно возникали какие-то сложности. Затем я пробовал грузить данные напрямую через Power Query, но объемы данных часто превышали возможности API. Теперь, когда данные лежат в Clickhouse, понимаешь насколько это удобно. 

Нанять дата-инженера, который будет заниматься загрузкой данных в БД будет стоить от 100 000 рублей, но не всем компаниям это нужно. Многим требуется простое решение, которое позволит регулярно обновлять данные в базе, при этом храня все токены и доступы на своей стороне. Именно о таком решении и расскажу в этой статье🔥

Покупка сервера и файлы настроек

Вместо найма дата инженера за 100К мы будем использовать сервер за 350 рублей и подписку на Boosty, чтобы скачать все необходимые файлы и не создавать их самостоятельно.

Перечислю что нам потребуется:

1. Сервер на Linux (или локальный ПК с Docker Desktop)
2. Docker – стандартный инструмент для развертывания контейнеров. 
3. Файлы конфигурации ClickHouse (config.xml и users.xml)
4. Файл настроек загрузки данных settings.xlsx:

Сервер на Linux я обычно приобретаю на Hostland, первый месяц стоит 200 рублей - самая низкая цена, что я видел. Если брать на год, средняя цена за месяц составит около 350 рублей. Можно использовать свой ПК, но это не так удобно, потому что он не всегда включён, да и лишняя нагрузка может вам помешать. 

В случае сервера вам потребуется установить докер. Ниже последовательный набор команд для его установки на Linux. Последняя команда - проверка статуса, если докер активен - всё получилось:

sudo apt update

sudo apt install -y apt-transport-https ca-certificates curl software-properties-common

curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo gpg --dearmor -o /usr/share/keyrings/docker-archive-keyring.gpg

echo "deb [arch=$(dpkg --print-architecture) signed-by=/usr/share/keyrings/docker-archive-keyring.gpg] https://download.docker.com/linux/ubuntu $(lsb_release -cs) stable" | sudo tee /etc/apt/sources.list.d/docker.list > /dev/null

sudo apt update

sudo apt install -y docker-ce

sudo systemctl status docker

Если будете работать на обычном компьютере, сразу ставьте Docker Desktop и прописывайте BASE_DIR для удобства работы, я прописываю свою папку:

set BASE_DIR=C:/Github/exp_scripts/Применение Docker

Заполнение файлов настроек

Файлы конфигурации Clickhouse и пример файла настроек загрузки данных можно скачать на Boosty. 

Файл config.xml не требует никаких изменений, а вот в users.xml вам нужно выставить свой пароль пользователя и ограничить доступ по IP, чтобы повысить безопасность вашего Clickhouse:

Здесь я разрешил доступ со своего компьютера, а также с парочки IP, по которым приходил Power BI Service чтобы делать обновления: 40.74.30.203 и 20.50.0.21. 

Чтобы отследить IP Power BI я воспользовался утилитой tcpdump. Не знаю насколько часто Power BI меняет свои IP, но думаю вам подойдёт любая утилита для их отслеживания. 

Все остальные настройки делаются в settings.xslx. При заполнении settings обязательно проверяйте подключения в Power Query, чтобы исключить ошибки в токенах. Некорректные токены - одна из самых частых ошибок при заполнении.

Что нужно заполнить в файле настроек:

1. Первый лист – координаты базы данных (хост, порт, логин, пароль) и параметры Telegram-бота (токен, ID чата, категория уведомлений) для уведомлений.
2. Остальные листы – источники данных (Wildberries, Ozon, Yandex, GetCourse и др.) с указанием параметров: API-токены, ID кабинета, дата начала загрузки, необходимые отчёты, расписание обновления.

Если вы хотите получать уведомления о логах в Telegram, на первом листе нужно указать ID чата куда они должны приходить и токен Telegram-бота. Бот создаётся через BotFather, а личный ID можно узнать у @userinfobot. 

Если вы хотите отправлять уведомления в общий чат, добавьте туда бота в качестве админа (доступ к управлению сообщениями) и отправьте несколько тестовых сообщений. Далее подставьте в следующую ссылку айдишник и токен бота и перейдите по ней: https://api.telegram.org/bot1234567:AGFnH-98pywSLbAuM/getupdates. В полученном ответе найдите chat_id - это и будет ID вашей беседы. Обычно он идёт со знаком минус.

Заполнение данных на листах хорошо отображено в видео и детально показано в примерах в самом файле. Внимательно следите за номером db_num. Если на первом листе у вас указан только номер 1, то и на остальных листах не должно быть других номеров. В противном случае система будет загружать данные в несуществующую БД и вы получите ошибку. Многие почему-то думают, что db_num - это просто нумерация строк как 1,2,3, но нет - это указатель на прописанную на первом листе БД.

Отдельно стоит остановиться на параметре host с первого листа. Если система развёрнута на домашнем компьютере, там можно указывать "localhost". Если же ваш контейнер загрузки никак не связан с контейнером Clickhouse, вам придётся указать IP сервера Clickhouse. В остальных случаях указывается название контейнера "my_clickhouse" и загрузка идёт по сети, созданной между контейнерами.

Развёртывание с помощью Docker 

Для подключения к серверу наберите данную команду: 

ssh root@IP-адрес_сервера

В первый раз нужно будет ввести "yes" для подтверждения, далее вводите пароль. При вводе пароля его будет не видно, обычно пароль копируют и вставляют правой кнопкой мыши, после чего можно нажать "enter". Если случайно нажали "no" и подключение не срабатывает, введите команду: ssh-keygen -R IP-адрес_сервера и попробуйте подключиться снова.

Все созданные файлы - settings.xlsx, users.xml, config.xml закидываем в папку "/home" на нашем сервере с помощью FileZilla. Обычно FileZilla сходу подключается к одной из локальных папок - из неё нужно выйти и найти папку "home".

Чтобы данные в Clickhouse не терялись после отключения контейнера вам потребуется создать volume. Кроме того, для прямого соединения с Clickhouse нужна будет сеть между контейнером загрузки и контейнером базы. Volume и сеть создаются так: 

docker volume create clickhouse_data
docker network create ch_net

Если вы работаете на Linux, то команда на запуск контейнера Clickhouse делается так:

docker run -d --name my_clickhouse --network chnet -p 8123:8123 -p 9000:9000 -v clickhouse_volume:/var/lib/clickhouse -v "/home/config.xml:/etc/clickhouse-server/config.xml" -v "/home/users.xml:/etc/clickhouse-server/users.xml" --user clickhouse morinad/my_clickhouse clickhouse-server --config-file=/etc/clickhouse-server/config.xml

Если у вас Windows, контейнер кликхауса запускается так (не забудьте выставить BASE_DIR заранее):

docker run -d --name my_clickhouse --network chnet -p 8123:8123 -p 9000:9000 -v clickhouse_volume:/var/lib/clickhouse -v "%BASE_DIR%/config.xml:/etc/clickhouse-server/config.xml" -v "%BASE_DIR%/users.xml:/etc/clickhouse-server/users.xml" --user clickhouse morinad/my_clickhouse clickhouse-server --config-file=/etc/clickhouse-server/config.xml

Запуск с помощью docker run делается только один раз. Если в результате возникла ошибка, вы корректируете файлы настроек и выполняете обычный перезапуск контейнера (подробно далее), второй раз docker run выполнять не нужно. Повторный docker run делается только если контейнер был удалён.

Проверить работу Clickhouse можно в DBeaver. Для подключения понадобятся: IP сервера, порт = 8123, название БД = default, пользователь = default, пароль юзера = тот, что вы задали в файле users. Работу Clickhouse нужно обязательно проверить, про ошибки и их устранение поговорим ниже.

Когда Clickhouse работает, можно запускать загрузку данных:

docker run -d --name upload_data --network chnet -v "/home/settings.xlsx:/app/settings.xlsx" morinad/upload_data

Для Windows это выглядит так:

docker run -d --name upload_data --network chnet -v "%BASE_DIR%/settings.xlsx:/app/settings.xlsx" morinad/upload_data

Что тут важно знать: после двоеточия идёт /app/settings.xlsx - этот адрес всегда должен оставаться неизменным, по нему внутри контейнера мы обращаемся к файлу настроек. При этом название контейнера - upload_data и адрес файла до двоеточия - /home/settings.xlsx могут меняться, поэтому для них можете придумать свои названия.

Важная вещь, которая вытекает из сказанного: можно запускать много параллельных загрузок и давать каждой своё название. Это удобно если у вас много клиентов, много аккаунтов, много источников, и параллельный запуск не создаёт проблем для API (так как аккаунты разные). Простой пример запуска второго контейнера загрузки:

docker run -d --name upload_data2 --network chnet -v "/home/settings2.xlsx:/app/settings.xlsx" morinad/upload_data

Команды и разбор ошибок

Если изучать докер на уровне пользователя, он максимально прост: вы скачиваете образ, на его основе запускаете контейнер, в контейнере выполняется нужная программа. Всего одной командой "docker run" вы скачали образ и запустили контейнер - очень удобно. Но когда приходится исправлять ошибки, часто возникает непонимание даже на этом уровне. 

Разберём самые простые команды Docker:

• docker images – посмотреть список скаченных образов
• docker ps – посмотреть список запущенных контейнеров
• docker ps -a – список всех контейнеров (включая остановленные)

Если всё хорошо, по команде docker ps у вас должно быть активно 2 контейнера - контейнер кликхауса "my_clickhouse" и контейнер загрузки "upload_data" (а возможно и больше если вы запустили много загрузок).

Эти команды выводят вам список образов или контейнеров. У каждого контейнера или образа есть ID, который тоже выводится. Для команд удаления лучше пользоваться именно ID, чтобы всё удалялось корректно. Ниже команды работы с контейнерами и образами: 

• docker stop название_контейнера – остановка контейнера
• docker start название_контейнера – запуск контейнера
• docker restart название_контейнера – перезапуск контейнера
• docker logs название_контейнера – просмотр логов
• docker rm -f id_контейнера  – удаление контейнера
• docker rmi -f id_образа  – удаление образа
• docker update --restart unless-stopped название_контейнера  – включить автоматический перезапуск после падения контейнера

Важно: каждый раз после изменения файла загрузки settings.xlsx или настроек кликхауса нужно перезапустить соответствующий контейнер. Поменяли settings - перезапускаем upload_data, поменяли настройки кликхауса - перезапускаем my_clickhouse:

 docker restart название_контейнера

Устранение проблем Clickhouse и загрузки

Разберём разные кейсы поломок и определим что делать при каждом из них. 

Clickhouse не подключается через DBeaver: сравните пароль из users.xml и вбиваемый пароль, возможно они разные. Если вы меняли юзера, возможно это не default. Обязательно проверьте работу контейнера - если контейнер Clickhouse не работает, надо смотреть его логи - docker logs название_контейнера. Также вы могли прописать в users ограничения по IP, еще раз проверьте свой IP, внесите в список разрешенных и перезапустите контейнер кликхауса чтобы изменения применились.

Проверка работы контейнеров: жмём docker ps вызывая список запущенных контейнеров. my_clickhouse и upload_data должны быть активны. Если один из них не активен, проверяем весь список - docker ps -a. 

Один из контейнеров остановился: смотрим логи остановившегося контейнера docker logs название_контейнера - в самом конце логов может быть ошибка, с которой нужно разобраться. Если ошибки как таковой нет (контейнер мог остановиться при перезагрузке сервера), просто запускаем его снова - docker restart название_контейнера.

Контейнеры регулярно выключаются без ошибок: тут вам поможет команда на включение автоматического перезапуска - docker update --restart unless-stopped название_контейнера. Команда выполняется один раз, но зато контейнер будет перезапускаться всегда. Правда если он будет падать с ошибкой, он тоже будет перезапускаться. Это может повторяться бесконечно, так что если данные не грузятся - проверьте логи.

Контейнер пропал: если команда docker ps -a отображает только один контейнер (а было два), можно повторно сделать "docker run...". Но такая ситуация очень редка.

Хотите загружать новый источник, но у вас старый образ: тут проще удалить контейнер загрузки и снова запустить его. Кликхаус можно не трогать, он вообще не имеет к загрузке никакого отношения. Удаление делается по ID контейнера и образа, для их уточнения можно использовать список образов docker images и список контейнеров docker ps -a. Сначала останавливаем загрузку - docker stop upload_data, далее удаляем контейнер загрузки - docker rm -f id_контейнера, а затем удаляем и образ - docker rmi -f id_образа. После чего запускаем соответстующий docker run заново.

Не приходят уведомления в Telegram: начните с того, что поставите только свой личный ID в список получателей, узнать его можно по ссылке. Если вы точно поставили свой личный ID, а сообщения не приходят - загляните в логи контейнера, что-то может не работать. Если вам всё приходит, но вы хотите получать уведомления в общий чат - выше я давал инструкцию как действовать. Групповой ID беседы должен быть со знаком минус и это должен быть именно chat_id а не update_id или что-то еще. Бот должен быть добавлен в групповой чат и должен иметь там админские права на управление сообщениями.

Работа с данными в ClickHouse

После загрузки данные доступны для анализа. Как забрать их в Power BI и Excel можно прочитать здесь. Таблиц в Clickhouse может быть много и вам потребуется объединять их разными способами, для этого есть несколько инструментов. Два основных инструмента объединения - представления View и таблицы Merge.

Когда у вас есть много таблиц с одинаковыми полями, очень удобно сделать единую таблицу на движке Merge, сцепив однотипные таблицы в одну большую:

CREATE TABLE yandex_direct_all AS yandex_direct_test_1
ENGINE = Merge(currentDatabase(), '^yandex_direct_');

Если вам требуется создать что-то более сложное, например произвести объединение или выполнить фильтрацию, можно создать представление:

CREATE VIEW my_view AS
SELECT column1, column2 FROM my_table WHERE condition GROUP BY column1;

Представление (как и таблица Merge) - всего лишь виртуальная сущность, которая не хранит данные. Все данные тянутся из оригинальных таблиц, так что нагрузка при выполнении сложного запроса может быть значительной. Хотя таблицы Merge работают быстрее, чем View и более удобны за счёт регулярок - в Merge не нужно руками прописывать добавляемые таблицы.

Как вы уже видели при загрузке, система создаёт таблицу "collection" под каждого клиента. Туда прописываются даты, за которые происходил сбор данных. Если источник собирает данные по датам, а не текущий срез ежедневно, то удаляя дату в таблице collection вы ставите задачу на пересбор данных за эту дату. Если система не видит какую-то дату среди собранных, она начнёт сбор по ней. Удалить дату можно так:

DELETE FROM yd_сollection WHERE date = 'YYYY-MM-DD';

Заключение

Данные грузятся, уведомления приходят, а вы сэкономили 100 000 рублей (а возможно и миллионы) на зарплатах программистам. В этой статье мы не рассматриваем вопросы бекапирования, но рекомендую обязательно решить этот вопрос и организовать бекапы вашей базы данных. 

Отдельно нужно сказать про безопасность - ставьте более сложный пароль и блокируйте доступ к базе на уровне IP. Так вы сильно затрудните доступ злоумышленникам и не потеряете ваши данные.

Обязательно прочитайте статью про подключение к Clickhouse из Excel и Power BI, чтобы быстро построить качественную отчётность и использовать ваши преимущества на максимум.