Заполнение базы данных ClickHouse данными из amoCRM в Yandex Cloud

В данном кейсе — рассмотрим пример заполнения базы данных ClickHouse на примере данных из amocrm. Когда сделка в amocrm будет уходит на успешный этап — будем заполнять базу данных ClickHouse

Содержание

Что такое ClickHouse ?

ClickHouse — это колоночная система управления базами данных (СУБД), разработанная в «Яндекс» для очень быстрых аналитических запросов по большим объёмам данных.

Если упростить, то это что-то вроде “SQL-базы на стероидах” для аналитики, но с другим подходом к хранению и обработке данных.

Основная идея ClickHouse состоит в том, что Данные хранятся по колонкам, а не по строкам, как в обычных SQL-базах (MySQL, PostgreSQL).

Это значит, что если вам нужно посчитать среднее значение по одному столбцу в таблице с миллиардами строк, ClickHouse будет читать только этот столбец, а не всю строку целиком.

Где чаще всего применяется?

BI-системы
Веб-аналитика
Логирование и мониторинг
Машинное обучение

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

PostgreSQL и MySQL будет читать всю таблицу или хотя бы нужный индекс, но всё равно прогонять много строк.
ClickHouse просто возьмёт один столбец user_id и нужные даты, обработает их параллельно и выдаст результат за секунды.

Yandex Cloud

В данном примере, все будет делаться в YandexCloud — это веб-интерфейс управления облачными сервисами Яндекса, и для ClickHouse там есть готовое управляемое решение под названием Yandex Managed Service for ClickHouse.

Если купить хост в YandexCloud

ClickHouse разворачивается в кластере за пару минут (не надо вручную ставить и настраивать).
Обновления и патчи накатываются автоматически.
Есть встроенный мониторинг (метрики, графики).
Можно легко масштабировать (добавлять узлы, реплики).
Хранение и бэкапы — автоматически в облаке.
Есть готовая защита (авторизация, SSL, VPC).
Можно работать прямо из браузера в SQL-редакторе консоли.

Настройка ClickHouse

Переходим в Managed Service for ClickHouse в меню Yandex Cloud в левом верхнем углу. На этой странице будут показаны наши кластеры, на которых будут располагаться хосты и сервер с будущей базой данных.

Создание кластера

Создаем новый кластер. Заполняем имя нашего кластера. В разделе Ресурсы нужно выбрать конфигурацию сервера. ClickHouse в плане ресурсов ведёт себя не так, как обычные SQL-базы, и выбор железа зависит от того, какие задачи нужно решить.

В данном случае — будет выбрана минимальная конфигурация,

Тип: burstable (неполная гарантия CPU) — подойдёт только для тестов, в продакшене лучше standard или cpu-optimized

Сетевые настройки

Также очень важная часть, здесь предстоит настроить группу безопасности. Передавать данные мы будем по защищенному HTTPS соединению, для этого создадим новую группу безопасности, в которой откроем порт 9440 для входящих соединений, иначе не сможем получить доступ к базе данных

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

Настройки СУБД

Далее — настраиваем доступы к СУБД

Здесь важно обратить внимание на параметр Управление пользователями через SQL. Ставим в положение Выключено, иначе управление пользователями будет осуществляться с помощью SQL-запросов, а управление ими через консоль, CLI и API Yandex Cloud будет недоступно.

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

Сервисные настройки

Здесь нужно выбрать параметры доступа и создать сервисный аккаунт, от имени которого будут выполняться запросы в базу данных. В данном случае — роль будет admin

Остальные галочки оставляем для возможности управления базой данных из панели управления Yandex Cloud

После выставления всех настроек — нажимаем на кнопку Создать кластер

Настройка групп безопасности

После создания кластера — переходим в него и настраиваем группы безопасности

Создаем новое правило для входящего трафика

Для работы MoreKit требуется открытый порт 9440 для безопасного HTTPS-соединения

Здесь есть два варианта — менее безопасный и более безопасный

Менее безопасный — CIDR указываем 0.0.0.0/0 — это даст доступ к порту всем, у кого есть адрес сервера.
Более безопасный — перечислить адреса MoreKit в формате 123.45.67.89/32

IP Адреса MoreKit, с которых отправляются запросы:

185.93.111.16 185.41.163.246

Группа безопасности настроена

Включаем публичный доступ

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

Создаем таблицу для для данных

Далее необходимо создать подключение в разделе WebSQL для подключения к базе данных из Yandex Cloud.

В разделе WebSQL создаем новое подключение, вводим данные ранее созданного пользователя и название базы для подключения

В панели слева — нажимаем на наше подключение. Если все сделано верно — вы увидите консоль SQL

Следующим шагом — создадим таблицу с помощью SQL запроса. Подробнее об SQL запросах читайте в официальной документации синтаксиса ClickHouse

CREATE TABLE amo
(
    lead_id        Int32,
    date_closed_at DateTime,
    contact_id     Int32,
    contact_phone  Int64,
    contact_email  String,
    contact_name   String
)
ENGINE = MergeTree
ORDER BY lead_id;

Пояснения по выбору типов:

Int32 — для lead_id , contact_id , данный тип поля лучше подходит для обычных ID, счётчиков, небольших чисел (экономит место).
Int64 — для contact_phone, если нужно хранить именно номер как число — тогда Int64 (до 18 цифр, занимает меньше места по сравнению с типом String).
String — в ClickHouse это текст любого размера (замена TEXT из обычного SQL).
DateTime — стандартный тип даты и времени.
ENGINE MergeTree — основной движок в ClickHouse для структурированных таблиц.
ORDER BY lead_id — ClickHouse требует ORDER BY при создании MergeTree-таблиц, обычно указывают первичный ключ или поле, по которому чаще фильтруют.

Нажимаем Выполнить, увидим созданную таблицу, если не допущено синтаксических ошибок

Таблица создана. Теперь подключим нашу базу данных в MoreKit, выстроим процесс и заполним базу данных !

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

Переходим в список сервисов в MoreKit, ищем среди списка ClickHouse, нажимаем Добавить

Вводим данные для подключения

Хост — скопируйте FQDN хоста из панели ClickHouse

Порт — указываем 9440,который открывали в группе безопасности
Логин — логин созданного пользователя с доступом к БД
Пароль — пароль пользователя с доступом к БД
Название базы — название созданной базы (не таблицы)

Нажимаем Добавить

Подключение amoCRM

В MoreKit в разделе Сервисы — ищем в списке сервисов amoCRM, нажимаем на кнопку Добавить, произойдет перенаправление на страницу переадресации amoCRM, где необходимо выбрать нужную CRM, которую надо подключить в MoreKit

Создание процесса

Отправка из amoCRM будет осуществляться по триггеру из воронки. Когда происходит закрытие сделки на успешный этап, запрос из амо будет уходить в MoreKit, где мы соберем нужные данные из контакта и сделки и отправим их в базу данных

Создаем процесс amoCRM DP

Создаем новый процесс с типом amoCRM DP

После создания процесса — копируем ссылку на вебхук из первоначального блока и идем устанавливать триггер в воронке AmoCrm

После установки веб-хука, осталось выстроить логическую схему передачи данных из амо в ClickHouse

Выстраивание схемы

Схема в MoreKit будет выглядеть следующим образом

Здесь мы берем данные из сдекли и контакта, который к ней привязан и выполняем операцию INSERT в нашу таблицу в ClickHouse с данными из сущностей amoCRM

Описание блоков схемы

#1 Найти сделки — ищем сделку по ID из входящих данных для получения полных данных о сделке

#2 Найти контакты — ищем контакт, который привязан к сделке

#5 Регулярные выражения — очищаем номер телефона контакта от префиксов и лишних символов, оставляя только цифры

#3 INSERT — заполнение данных в таблицу. В каждом поле можно выбрать тип отправляемого поля. Для полей с типом Int32 и Int64 нужно поставить целое число

Тестируем процесс

Двинем сделку на успешный этап, процесс в MoreKit должен запуститься и выполниться

Проверим события

Проверяем результат

Сделаем полную выборку данных в WebSQL консоли в Yandex Cloud

SELECT * from amo;

Выполнив SELECT нашей таблицы, увидим, что данные успешно ушли, таблица начала заполняться

Подведем итоги

В рамках выполненных работ была настроена интеграция между amoCRM и ClickHouse, развернутым в Yandex Cloud
При переводе сделки в успешный этап данные по сделке и контакту автоматически передаются в базу данных ClickHouse с помощью MoreKit

Развернут кластер ClickHouse в Yandex Cloud
Настроена группа безопасности открыты необходимые CIDR-правила
Созданы база данных amoLEADS и таблица amo с необходимыми полями
Настроен процесс передачи данных в MoreKit
Проведено тестирование передачи и записи данных

В результате система обеспечивает:

автоматический сбор информации о закрытых сделках в amoCRM
централизованное хранение данных в ClickHouse
возможность быстрой аналитики и построения отчётов по всем завершённым сделкам