Привет! Меня зовут Александр Ежков, я Backend-разработчик в AGIMA. Занимаюсь созданием и поддержкой внутренних сервисов для компании. А конкретно сейчас — нашей DWH-системой. Мы построили ее из Open-source продуктов. В этой статье расскажу, какие сервисы мы используем, какие хитрости придумали для работы с ними и как вся система работает вместе.
Что такое DWH
DWH (Data Warehouse) — это специализированная база данных, предназначенная для подготовки и анализа управленческой отчетности. Она позволяет собирать и хранить информацию из разных источников. В нашем случае DWH помогает руководителям компании принимать управленческие решения.
В этой базе мы храним данные о расходах и доходах компаний, которые входят в экосистему AGIMA. Эти данные структурированы и их немного. Поэтому они не требуют чистки или серьезной обработки.
Ниже я расскажу о программах, которые мы выбрали для построения системы DWH.
Grist
Grist — современная реляционная электронная таблица. По сути, это гибрид таблицы из реляционной базы данных и электронной таблицы — такой же, как Google Sheet или Excel. Рассмотрим его основные особенности.
Столбцы в Grist работают так же, как в БД: они имеют имена и содержат один тип данных. При этом они могут быть заполнены по формуле — так же, как в электронной таблице: с автоматическим обновлением при изменении ячеек, на которые имеются ссылки.
В Grist поддерживается полный синтаксис Python, включая стандартную библиотеку. Также доступны многие функции Excel. В основе Grist SQLite. Поэтому любой инструмент, который способен читать SQLite, прочитает и данные из файла Grist.
Здесь легко создавать резервные копии, а также перемещать документы между разными хостами.
У Grist хорошо задокументированное API. А еще есть возможность экспортировать и импортировать Excel и Google-таблицы.
Одна из самых интересных возможностей в Grist — настройка правил доступа для пользователей. Grist позволяет делиться документами и рабочими областями с другими пользователями, управлять доступом к отдельным строкам, столбцам и таблицам на основе значений ячеек и атрибутов пользователя. Для управления пользователями нужно настроить SSO-логин (мы используем authentik) или использовать готовое решение. Можно ознакомиться с документацией по настройке Grist в authentik.
Чтобы попробовать Grist, можно использовать Docker:
docker pull gristlabs/grist
docker run -p 8484:8484 -v $PWD/persist:/persist -it gristlabs/gristGrist будет запущен по адресу http://localhost:8484. Вы сможете создавать, редактировать, импортировать и экспортировать документы. Чтобы сохранить работу, используется параметр - v$PWD/persist:/persist.
Почему Grist
Для учета расходов и доходов нам нужен был табличный редактор, который не требует серьезных внешних доработок. У нас были определенные требования к поиску:
правила доступа для разных пользователей;
возможность кастомизации таблиц, колонок, строк;
быстрая проверка гипотезы.
Grist удовлетворяет все требования. Он имеет гибкую систему доступа, позволяет кастомизировать таблицы, раскрасить строки, колонки, ячейки, высчитывать формулы, имеет шаблоны для разных пространств для быстрого старта работы, имеет гибкий API, а также библиотеку на Python.
Как мы используем Grist
Ниже приведу несколько примеров, все данные выдуманы.
В Grist есть рабочие пространства для объединения документов в рамках одного проекта или в рамках одной компании, как в нашем случае. Документами можно делиться с другими пользователями, для этого необходимо нажать на кнопку «Управление пользователями».
Откроется окно, в котором можно пригласить пользователей в документ и выбрать одну из ролей: владелец, редактор и наблюдатель.
В документе может быть несколько виджетов, мы используем таблицы.
В таблице «Расходы» менеджеры должны видеть строки только со своим именем, а бухгалтер должен видеть все. Чтобы это настроить, создаем таблицу «Команда» и прописываем там пользователей, которые используют этот документ.
Ниже в Grist есть кнопка «Правила доступа». На этой странице мы можем гибко настраивать правила доступа на уровне таблиц, колонок и строк.
Чтобы в таблице «Расходы» менеджер видел только свои строки, добавляем атрибут пользователя Team, нажав на кнопку «Добавить атрибут пользователя», по которому мы сможем настраивать доступы. Затем нажимаем на кнопку «Добавить правила таблицы» и выбрать в меню таблицу «Расходы».
В разделе «Условие» в первой строке я прописал правило и выставил разрешения Read, Update, Create, Delete:
user.Team.Role == "Менеджер" and user.Access != OWNER and user.Name == rec.responsible and user.Name == newRec.responsibleuser.Team.Role — это новый атрибут пользователя. По этому атрибуту мы по электронной почте текущего пользователя можем найти нужную нам строку в таблице «Команда». В данном случаем мы смотрим колонку Role в строке, где email == “a.ezhkov@agima.ru”.
user.Access — это атрибут пользователя, который мы задали, когда пригласили его в документ. В данном случае он равен EDITOR, так как пользователю «Александр Ежков» мы дали роль редактора.
user.Name — имя пользователя.
rec — переменная, позволяющая выражать правила, которые зависят от содержимого конкретной записи.
rec.responsible — колонка «Ответственный» в таблице «Расходы». Условие user.Name == rec.responsible говорит, что сравниваем имя у текущего пользователя с колонкой «Ответственный» в строке таблицы.
newRec — это вариант rec, доступный, когда пользователь предлагает изменить запись, причем rec содержит ее состояние до изменения, а newRec — ее состояние после предложенного изменения.
Во второй строке я прописал условие и запретил все разрешения CRUD:
user.Team.Role == "Менеджер" and user.Access != OWNER and user.Name != rec.responsibleВ разделе «Специальные правила» я убрал галочку «Позволять редакторам редактировать структуру» и в условиях прописал, чтобы это могли делать владельцы документа или пользователь с ролью «Бухгалтер».
Нажав на кнопку «Смотреть как» мы можем посмотреть, как видят таблицы конкретные пользователи.
Если выберем моего пользователя, то увидим строки, где я в роли «Ответственного».
Осталось сделать так, чтобы при создании новой строки в колонку «Ответственный» записывалось имя пользователя, который ее создает. Для этого заходим в таблицу «Расходы», открываем параметры столбца «Ответственный», нажимаем на кнопку «Задать триггерную формулу». В появившемся поле пишем формулу и проставляем галочки, чтобы формула вычислялась при создании записи и при изменении записи:
user.Name if Table4.lookupOne(Email=user.Email).Role == "Менеджер" else value
Метод Table4.lookupOne ищет текущего пользователя в таблице «Команда» по электронной почте и сравнивает значение в колонке Role. Таким образом, если запись создает или изменяет менеджер, то в колонке «Ответственный» проставляется его имя, а если кто-то другой, то проставляется значение, которое вводит пользователь.
Создадим строку с описанием «Создание статьи» и нажмем Enter. В поле «Ответственный» автоматически появилось мое имя.
Теперь мы запретим видеть таблицу «Команда» всем, кроме владельца документа и бухгалтера. Для этого в разделе «Правила доступа» создадим правило для этой таблицы и пропишем условие для пользователей со всеми разрешениями, а для остальных всё запретим:
user.Access == OWNER or user.Team.Role in ['Бухгалтер']
Зайдем под моим пользователем и увидим, что у нас пропала таблица «Команда».
Так как название таблиц у нас на русском, то в поле триггерных формул мы использовали не название таблицы, а ее ID. Идентификаторы таблиц можно посмотреть в разделе «Исходные данные».
Grist API и Airflow
Apache Airflow — это открытая платформа управления рабочими процессами (Workflow Management Platform), разработанная Airbnb. Она используется для создания, планирования и мониторинга рабочих процессов (Workflows) в комплексных системах обработки данных. Это уже отраслевой стандарт для оркестрации и обработки данных.
Airflow можно легко запустить рядом с Grist с помощью Docker. У Grist есть клиент, написанный на Python, который мы используем в дагах Airflow, чтобы собирать данные.
Чтобы использовать библиотеку py_grist_api, нужно прописать адрес до сервера, идентификатор документа, который можно найти в разделе «Настройки», и добавить переменную окружения GRIST_API_KEY с ключом API. Также вместо переменной окружения можно прописать ключ в файл ~/.grist-api-key. Ключ можно найти в настройках профиля пользователя.
У нас есть два вида дагов для каждой таблицы из Grist. Первый вид перезаписывает таблицу как есть и срабатывает каждый час. Второй вид дагов скачивает таблицу раз в сутки и добавляет новую версию таблицы к старой, но с сегодняшней датой. Таким образом у нас есть актуальная версия таблицы и история, какой была таблица в Grist раньше за каждый день. Мы называем их DIM-таблицами или снапшотами. Всё это дело мы храним в PostgreSQL.
Для настройки времени запуска дагов используются два параметра: schedule_interval и start_date, где первый это Cron-выражение, а второй принимает Datetime-объект.
Код в дагах Airflow для загрузки таблиц из Grist каждый час:
from airflow.models import Variable
from grist_api import GristDocAPI
@dag(
dag_id=dag_id,
params=params,
schedule_interval='0 * * * *',
start_date=pendulum.datetime(2022, 6, 30, 0, 0, 0, tz='UTC'),
catchup=False,
render_template_as_native_obj=True,
tags=tags,
)
def grist_T_pg():
"""
### GAD ETL: Все таблицы
Выгрузка данных из Gtist в PostgreSQL
"""
@task()
def extract(**context):
"""
#### Extract task
Получем данные из Grist по API
"""
SERVER = Variable.get('API_URL_grist')
DOC_ID = Variable.get('GRIST_DOC_ID')
api = GristDocAPI(
DOC_ID,
server=SERVER
)
data = api.call(f"tables/{context['params'].get('grist')}/data")
return dataЗдесь в context['params'].get('grist') подставляется идентификатор таблицы.
Код в дагах Airflow для создания истории таблицы. Этот код запускается раз в сутки:
@dag(
dag_id=dag_id,
params=params,
schedule_interval='30 1 * * *',
start_date=pendulum.datetime(2022, 6, 30, 1, 0, 0, tz='UTC'),
catchup=False,
render_template_as_native_obj=True,
tags=tags,
)
def dimension_tables():
"""
### GAD Dimension Tables
Создание dim-таблиц
"""
@task()
def read(**context):
"""
#### Read task
Чтение данных из таблицы
"""
hook = PostgresHook('dwh')
engine = hook.get_sqlalchemy_engine()
conn = engine.connect()
sql = f"SELECT * FROM {context['params'].get('dwh')}"
try:
df = pd.read_sql(
sql,
conn
)
finally:
conn.close()
return df.to_dict()
@task()
def copy(data: dict, **context):
"""
#### Copy task
Создание копии таблицы
"""
updated_today = was_update_today(table=f"{context['params'].get('dwh')}__dim")
if updated_today: return
hook = PostgresHook('dwh')
engine = hook.get_sqlalchemy_engine()
conn = engine.connect()
try:
df = pd.DataFrame.from_dict(
data=data
)
# Дата и время создания снапшота таблицы
df['ds'] = pd.to_datetime('today').normalize()
df.to_sql(
f"{context['params'].get('dwh')}__dim",
conn,
index=False,
if_exists='append'
)
finally:
conn.close()
data = read()
copy(data)С помощью этих таблиц мы можем смотреть разницу в доходах и расходах с прошлыми периодами и строить тренды на дашбордах в Metabase. Как построить такой отчет в Metabase, ньюбиз-директор AGIMA Виталий Дощенко рассказывает в разделе «Создаем SQL-отчет в Metabase, добавляем сравнение с периодами для анализа истории в динамике».
Metabase и psql-http
Metabase — это открытое программное обеспечение для анализа данных, которое позволяет пользователям визуализировать свои данные и создавать интерактивные отчеты. Это инструмент, который облегчает работу с данными, делает их понятными и доступными для принятия решений.
Metabase легко начать пользоваться. Он имеет низкий барьер входа, хорошую документацию, интуитивный интерфейс, систему запросов, позволяющую обращаться к базе данных посредством SQL либо через визуальный редактор запросов. Также Metabase можно подключить к серверу LDAP для аутентификации и настроить права доступа для разных пользователей.
Для локального запуска Metabase можно использовать Docker:
docker pull metabase/metabase:latest
docker run -d -p 3000:3000 --name metabase metabase/metabaseНа Хабре много статей, о том, что такое Metabase, как рисовать в нем дашборды, как сделать соединение с базой данных и прочее. Поэтому я лучше расскажу, как мы сделали обновление дагов в Airflow прямо из дашборда Metabase, чтобы видеть актуальные данные.
Как-то раз пришел директор и сказал: «Мы добавляем данные в Grist, а потом ждем час, когда они появятся на дашбордах в Metabase. Нужно сделать так, чтобы можно было по кнопке обновлять дашборды сразу, как только были добавлены новые данные».
Очевидно, нужно было вызывать API Airflow прямо из Metabase и оформить это красиво для пользователя. Чтобы в Airflow запустить даг, нужно отправить POST запрос на этот эндпоинт:
Осталось понять, как это можно было делать из Metabase. После изучения документации была найдена интересная фича Actions. Actions — это сущности в Metabase, которые позволяют создавать собственные формы и бизнес-логику. Они позволяют писать параметризованный SQL, который выполняет запись в вашу базу данных. Actions можно прикреплять к кнопкам на дашбордах для создания пользовательских рабочих процессов. Если вкратце, то Actions позволяют сделать CRUD-интерфейс прямо на дашборде.
На данный момент Actions доступны только для PostgreSQL и MySQL. Документацию по Actions можно прочитать здесь.
Следующий инструмент, который помог мне в решении задачи это psql-http — HTTP-клиент для PostgreSQL, который позволяет делать HTTP-запросы в базе данных.
Скрипт для установки расширения:
wget -O pgsqlhttp.tar.gz "https://github.com/pramsey/pgsql-http/archive/refs/tags/${PGSQL_HTTP_VERSION}.tar.gz"
mkdir -p /usr/src/pgsqlhttp
tar --extract --file pgsqlhttp.tar.gz --directory /usr/src/pgsqlhttp --strip-components 1
rm pgsqlhttp.tar.gz
cd /usr/src/pgsqlhttp
make
make install Затем нужно выполнить запрос:
CREATE EXTENSION IF NOT EXISTS "http";Пример того, как работает psql-http:
SELECT status, content_type
FROM http_get('http://httpbin.org/');Ответ:
status | content_type
--------+--------------------------
200 | text/html; charset=utf-8
(1 row)Я использую psql-http, чтобы вызывать API Airflow прямо из базы данных, через Actions в Metabase. Вот и всё.
Как создать кнопку на дашборде
Чтобы создать кнопку на дашборде, нужно разрешить «Действия с Моделью» в настройках подключения базы данных Metabase.
После этого заходим на дашборд, нажимаем на кнопку «Изменить дашборд», затем «Добавить кнопку действия».
Ввести текст кнопки и нажать на кнопку «Выбрать действие». Выбрать модель, к которому будет прикреплено действие, и нажать «Создать новое действие». Откроется окно, в котором пишется SQL-запрос. Этот запрос будет выполнен при нажатии на кнопку.
На картинке выше написан странный код. Дело в том, что Actions позволяет писать только CREATE-, INSERT- и DELETE-запросы. А мне нужно просто вызвать функцию через SELECT. Поэтому я использую CTE и вызываю его в запросе UPDATE … WHERE false = true. Таким образом у меня запускается функция start_dags, и в таблице Table1 ничего не меняется.
Для ознакомления приведу код функции start_dags, чтобы легче понять, как вызвать API Airflow с помощью psql-http.
CREATE OR REPLACE FUNCTION start_dags(dags text[]) RETURNS boolean LANGUAGE PLPGSQL AS $$
DECLARE
current_dag text;
login text := 'login';
pass text := 'password';
result http_response array;
BEGIN
FOREACH current_dag IN ARRAY dags LOOP
result := array_append(result, start_dag_run(login, pass, current_dag));
END LOOP;
return true;
END;
$$;Функция start_dag_run:
CREATE OR REPLACE FUNCTION start_dag_run(login text, pass text, dag_id text) RETURNS http_response LANGUAGE PLPGSQL AS $$ declare
result http_response;
url text;
headers http_header array;
logpass text;
begin
url := concat('http://airflow-webserver/api/v1/dags/', dag_id, '/dagRuns');
logpass := concat(login, ':', pass);
headers := array[http_header('Authorization', concat('Basic ', encode(logpass::bytea, 'base64')))];
select
*
into
result
from
http(('POST',
url,
headers,
'application/json',
'{}')::http_request);
return result;
end;
$$;Полезная ссылка и демо-версия
Чтобы вам было проще построить такую же систему, я создал репозиторий из Docker-compose файлов. Пользуйтесь!
А если хотите просто разобраться, как Grist работает, и пощупать его руками, вот демо-сайт.
Логин: demo@example.com
Пароль: demo1234
Демо-версию вы можете только посмотреть. Но также можно создать копию документа и работать в ней.
Заключение
Я поделился хитростями работы с инструментами, которые мы используем в AGIMA для хранения корпоративных данных. Надеюсь, разработчикам будет полезно. О том, как настроить уже готовый дашборд и как с ним работать, в отдельной статье рассказывает мой коллега, ньюбиз-директор AGIMA Виталий Дощенко.
Этот инструмент будет полезен продакт-менеджерам, исполнительным и коммерческим директорам, бухгалтерам. В общем, всем, кто хотел бы следить за финансами компании в реальном времени. Главное преимущество инструмента, когда он настроен, — что пользоваться им можно без помощи разработчиков или аналитиков.
Если есть вопросы, задавайте в комментариях. Постараюсь на все ответить. А еще подписывайтесь на наш телеграм-канал для продактов. Там мы рассказываем о других инструментах и дашбордах.