Разработка для отдела Business Intelligence: автоматизируем ad hoc задачи по выгрузкам из БД
Часто в повседневной работе продуктовой компании в отделе Business Intelligence (или его зародыше) могут встречаться ad hoc задачи по выгрузкам из БД. Если их много, то они могут занимать почти все рабочее время без перерывов на более инновационную и результативную работу. В результате страдает как бизнес, так и сотрудники компании.
В статье мы рассмотрим, как взять под контроль поток задач, используя идею CRM-системы, в которой есть функции по автоматизации как легких запросов (обновления задач по cron), так и достаточно тяжелых (обновление по запросу клиента) с возможностью управлять входящими параметрами.
Зачем это нужно
В отделе Business Intelligence OLX мы работаем с отделами монетизации, маркетинга, продукта, безопасности, поддержки пользователей в
Каждый день приходят разнообразные задачи на выгрузку данных, автоматизации отчетности, которые делятся на быстрые ad hoc задачи и трудоемкие, которые требуют больше времени для анализа.
Количество обращений может достигать десятки в день, и к каждой задаче необходим индивидуальный подход. Однако встречаются однотипные задачи, которые можно автоматизировать, дав возможность менеджерам не стоять в очереди для получения данных, а самим управлять операционным процессом.
Кроме того, автоматизация дала бы возможность прозрачно вести учет обращений от каждого менеджера, делать историю обращений и времени обработки запросов в БД. До этого под каждую задачу писались скрипты на Python, R, PHP. Все скрипты были в разных местах на сервере, что создавало определенные неудобства и многочисленные дубли кода. Стоит отметить, что в компании используются разные инструменты под разные типы задач. Я же сторонник использования BI CRM.
За первый месяц внедрения BI CRM обработала около 500 запросов менеджеров, не считая автоматических выгрузок по cron. Что позволило значительно снизить нагрузку на BI отдел, увеличить скорость получения данных для менеджеров, построить аналитику по частотности SQL-запросов. С точки зрения разработчика, это кажется вполне логичным и обоснованным решением — создать единую контролируемую точку входа и сохранять различные метрики по использованию. Однако к этой идее мы пришли не сразу. Итак, начнем с истории.
Каталог SQL-запросов
Вначале было создано нечто вроде каталога SQL-запросов. Принцип довольно прост. Есть таблица с SQL-скриптами, есть PHP-скрипт, который по cron выполняет каждый запрос SQL. У такого решения есть недостатки. Нет нормального фронтенд-интерфейса для работы, нет статистики по запросам, отсутствует интерактивность и т. п. Мы все еще продолжали часто пользоваться отдельными скриптами, которые запускались по cron.
API для работы с БД в Excel
Когда стало приходить много задач типа «выгрузи мне данные по такому-то параметру», появилась идея API для работы с БД в Excel. Что уже лучше, впервые менеджер сам мог использовать API, не обращаясь к аналитику. Можно контролировать обращения менеджеров, нагрузку на БД. API состоял из двух типов SQL-запросов и представлял собой изнутри, по сути, все тот же каталог SQL-запросов. Первый тип запроса взял символ «R» из CRUD и позволял прочитать все, что находилось в базе по какому-то ID. Второй тип — предопределенные запросы, составленные аналитиком. Например, аналитик создает функцию посчитать количество объявлений пользователя и называет ее «get_count_ads». После этого к этой функции можно обращаться по GET-запросу.
У такого подхода был существенный минус — отсутствовала возможность пакетной обработки данных, что серьезно нагружало базу данных. Например, менеджер не мог добавить в функции сразу десятки тысяч пользователей и приходилось делать много маленьких запросов. Таким образом, информацию можно было получить только по одному объявлению или пользователю. Также менеджер должен разбираться с API интерфейсом, что было очень неудобно, и не у всех версия Excel поддерживала функцию webservice.
Идея BI CRM
И вот пришла идея сделать это цивилизованно, через CRM. По сути, отдел аналитики является интеллектуальной поддержкой для других отделов. Менеджеры — наши клиенты, с которыми можно работать по аналогии с технической поддержкой, но на более высоком уровне.
CRM дала возможность вести полную аналитику обращений менеджеров, SQL-запросов, хранить все запросы в одном месте, делать анализ самих запросов, их истории, контролировать нагрузки на базы.
Инструменты создания: PHP, MySQL
Базы, с которыми работает система: Amazon Redshift (PostgreSQL), MS SQL, Amazon MySQL.
Архитектура интерфейса
Все задачи подразделяются на автоматически обновляемые по cron или в ручном режиме.
Если первые очень похожи на старый, добрый автообновляемый каталог запросов SQL, то вторые — это новое custom-решение. Здесь задача называется «Шаблон». Аналитик создает шаблон SQL-запроса и задает плейсхолдеры. Выглядит шаблон таким образом:
SELECT * FROM payments WHERE id_user IN (#1) AND created_at BETWEEN #2 AND #3
Здесь #1 — это плейсхолдер для подстановки набора user_id, #2 и #3 — интервал дат.
Существует несколько типов плейсхолдеров, которые может добавлять аналитик:
Примеры: Text — textarea для ввода списка чисел или списка слов. Input — небольшое текстовое поле, Category — предопределенный набор данных в виде списка, который хранится в системе.
В результате сохранения шаблона, CRM создаст «Представление» — View этого шаблона. Это интерфейс для менеджера, в который он будет вводить данные, в нашем случае — это список user_id и даты.
После ввода входящих данных менеджер получает файл с исходящими данными.
Отдельно стоит упомянуть более сложные типы плейсхолдеров: категории, города и т. п. Технически они хранятся в таблице predefined в формате: id, element_id, name, type, id_country, value. Где type — это тип, например category, value — это список id через запятую. Он представляет из себя список значений id, например категорий. Впоследствии значения используются в запросе: SELECT * FROM predefined_items WHERE id IN (values). То есть все сложные плейсхолдеры имеют стандартный формат записи.
Что под капотом
Внутренняя часть основывается на самописной CRM (техническое название движка — crud crm), которая уже использовалась в OLX ранее и хорошо себя зарекомендовала. Система представляет собой конструктор CRM для быстрого развертывания (в течение часа) CRM разных видов, будь то для техподдержки пользователей или колл-центра. Именно на этой CRM обрабатываются вопросы и баг-репорты клиентов по OLX-доставке. Внутри это CRUD-система (PHP, Twig, MySQL), ядро которой состоит из соответственно добавления, чтения, обновления, удаления любых типов данных. Есть предустановленный шаблон, который автоматически генерирует визуальные элементы на основе последовательности, заданной в конфигурации. Есть возможность использовать свои custom-шаблоны, что мы и делали, так как менеджеры в основном хотят видеть только то, что им нужно.
Вдохновением послужил PHP CrudKit (к сожалению, сейчас он уже не поддерживается). Мы даже использовали его на некоторых своих проектах, но столкнулись впоследствии со сложностями кастомизации интерфейса, так как он был немного недоработанным. Однако его простота и скорость развертывания вдохновила создать свою CRUD CRM.
Пример обновления столбцов в ядре:
foreach ($listColumns as $column) {
$sql = "UPDATE $table SET `$column`='{$itemArr[$column]}' WHERE id=$id";
$this->db->exec($sql);
}
Пример инициализации CRUD CRM в index.php:
require_once "init.php";
/*
* Подключение ядра
*/
/* ... */
/*
* Инициализация ядра
*/
$appCrud = new Crud($connections);
$appCrud->addTwig($twig);
// Создание URL
$appCrud->addPath("bicrm");
/*
* Создание страницы (в админке, в левой части появится пункт меню)
* table_name - название таблицы для CRUD, column_names - человеческие названия столбцов в БД
*/
$appCrud->addPage(array(
"title" => "BI CRM SQL",
"table_name" => "query",
"column_names" => array("id" => "Id","id_db" => "DB","title" => "Title",
"id_schedule"=>"Scedule","id_category"=>"Category",
"id_rewrite_report"=>"Write Type",
"running"=>"Running",
"last_update_date" => "Last update","email" => "Email"),
));
/*
* Сколько элементов выводить на страницу
*/
$appCrud->setMaxItemsOnPage(20);
/*
* Задаем отображение столбцов из БД в интерфейс пользователя (HTML)
*/
$appCrud->setTypeField("query","text");
/*
* Повесим на столбец first_date_start JS календарь
*/
$appCrud->setTypeField("first_date_start", "datepicker");
/*
* id_db будет выпадающим списком, данные брать из таблицы db (в таблице db данные содержатся в формате id, value)
*/
$appCrud->setTypeField("id_db", "combobox",array("table" => array("db")));
$appCrud->setTypeField("id_schedule", "combobox",array("table" => array("schedule")));
$appCrud->setTypeField("id_category", "combobox",array("table" => array("category")));
$appCrud->setTypeField("id_rewrite_report", "combobox",array("table" => array("rewrite_report")));
$appCrud->addPage(array(
"title" => "BI CRM Template",
"table_name" => "template",
"column_names" => array("id" => "Id","id_db" => "DB","title" => "Title",
"id_category"=>"Category",
"id_rewrite_report"=>"Write Type",
"running"=>"Running",
"email" => "Email"),
));
if($appCrud->isLoggedUser()) {
$appCrud->render("index");
} else {
$appCrud->render("login");
}
Таким образом можно создавать различные типы полей и управлять ими.
Внутренняя архитектура BI CRM
Обновление запросов организовано следующим образом:
По cron запускается scheduler, который ищет задачи для выполнения по условиям:
- задача активирована и не выполняется в данный момент;
- дата и время запуска задачи меньше либо равна сегодняшней.
По каждой задаче вычисляется интервал между временем последнего запуска в минутах. Если он больше заданной частоты обновления в интерфейсе или равен ей, то выполняется метод startTask, который в свою очередь запускает процесс по GET-запросу.
Задачи запускаются по URL, чтобы не нарушать целостность всего процесса. Если какая-то из задач выведет ошибки, то на другие задачи это не повлияет.
Что в итоге
- Систематизирована работа аналитиков и менеджеров, ведется учет полного цикла их работы.
- Менеджеры получают данные моментально.
- У коллег появилось время для работы над более сложными задачами. Решена проблема потери времени и ресурсов из-за ad hoc задач.
В будущем запланированы:
- интеграция PivotTable.js для построения pivot и визуализации данных прямо в «Представлении»;
- умный редактор SQL-кода;
- интеграция Python, R, PHP-скриптов в BI CRM.
