Всех приветствую! Зовут меня Павел, работаю в Datapulse. Создаем различные решения для автоматизации DWH.
Мы живем в переходном периоде, когда на смену традиционным инструментам и подходам в data-engineer приходят новые. То, что еще вчера казалось стандартом де-факто, сегодня все чаще воспринимается как анахронизм. Пример тому - война GUI («гуевых») ETL инструментов со скриптовыми закончилась безоговорочной победой последних (все сказанное здесь является мнением автора и не претендует на истину в последней инстанции). Скрипты взяли верх благодаря своей масштабируемости, возможности применения Git и лучшей интеграции в процессы CI/CD.
А что же с BI? Мы привыкли к мастодонтам: PowerBI, Tableau, Qlik. Либо open-source: Superset, Metabase. Они в свое время стали стандартом, захватив большую часть рынка, а sales manager получали очень неплохие премии за их внедрение. И, казалось, время молочных рек и кисельных берегов не закончится никогда. Компании продолжат тратить кучу денег на красивенькие графики, которыми не пользуются, а в сообществе аналитиков будут лишь рассуждать о том, как доказать упертому менеджеру, что дашборд-вундервафля в PowerBI гораздо удобнее старого доброго Excel (шутка).
А времена эти, если и не прошли, то близятся к закату. Старые короли чахнут и умирают. Приветствуем новых!
Чем плохи стандартные BI-инструменты? Посмею перечислить их недостатки:
сложное внедрение и обслуживание
минимальные возможности интеграции с Git (или вообще их отсутствие)
слабые возможности кастомизации (потребуется неслабая экспертиза)
высокая/очень высокая стоимость (если проприетарное ПО)
сложно разобраться и поддерживать (если open-source)
- Довольно предисловия! Даже если я соглашусь со сказанным, чем заменить стандартный BI? – спросит читатель.
- У меня есть для вас пара блюд! – отвечу я.
Скриптовые BI или BI as a code. Появились не так давно, но уже набирают бешенную популярность. Сравнивать различные решения в этой статья я не буду. Есть замечательная обзорная статья, где описываются наиболее популярные фреймворки. Все примеры скриптового BI в этой статье будут основаны на фреймворке streamlit, потому что автор более чем хорошо с ним знаком.
Сперва определим явные достоинства скриптового BI в отношении стандартного BI:
Элементарная установка в пару команд
Простой обмен дашбордами (обмениваетесь лишь файлами)
Априори дружит с Git
Безграничные возможности кастомизации (можно реализовать полноценный интерфейс!)
Бесплатно (условно)
Практическое применение
Перейдем же к непосредственным примерам.
Установка более чем простая.
pip install streamlitПишем элементарный код для графика, который читает витрину с заказами из PostgreSQL и верстает график.
from sqlalchemy import create_engine
import pandas as pd
import streamlit as st
# Создаем движок подключения
engine = create_engine('postgresql+psycopg2://postgres:admin@localhost:5432/database')
# Выполняем запрос
query = """
select
order_month,
sum(total_orders) as orders_count
from dm.mart_order
group by
order_month
"""
df = pd.read_sql_query(query, engine)
# График
st.bar_chart(df, x="order_month", y="orders_count", x_label="Месяц", y_label="Кол-во заказов")Запускаем streamlit в браузере
streamlit run app.py
Конечно, элементарный bar chart – слишком скучно. Давайте добавим сюда фильтры по периоду и продукту. Обращаю внимание, что из коробки streamlit умеет кэшировать данные, чтобы в нашем случае не выполнять тяжелые SQL по нескольку раз.
Измененный python-скрипт
from sqlalchemy import create_engine
import pandas as pd
import streamlit as st
# Создаем движок подключения
engine = create_engine('postgresql+psycopg2://postgres:admin@localhost:5432/databaase')
# получаем и кэшируем данные
spinner_text = "Чтение данных..."
@st.cache_data(ttl=300, max_entries=1000, show_spinner=spinner_text)
def get_unique_values(column):
"""Получаем уникальные значения столбца витрины"""
return pd.read_sql_query(
f"select distinct {column} from dm.mart_order order by {column}",
engine)
@st.cache_data(ttl=300, max_entries=1000, show_spinner=spinner_text)
def get_order_data(
date_from = None,
date_to = None,
city = None,
product = None
):
"""Получаем данные из витрины"""
filter = ''
if date_from:
filter += f" and order_month>='{str(date_from)}'"
if date_to:
filter += f" and order_month<='{str(date_to)}'"
if city:
city = "','".join(city)
filter += f" and city_name in ('{city}')"
if product:
product = "','".join(product)
filter += f" and product_category in ('{str(product)}')"
query = f"""
select
order_month,
sum(total_orders) as orders_count
from dm.mart_order
where 1=1
{filter}
group by
order_month
"""
return pd.read_sql_query(query, engine)
Набор визуализаций в streamlit скудный, но можно использовать сторонние библиотеки. Такие как plotly, altair, vega lite и другие. Мне лично по душе echarts. У него большие возможности, но достаточно сложный интерфейс в виде json-параметров.
pip install streamlit-echartsJson-параметров огромное количество, разобраться в них с нуля сложно, но можно упростить себе жизнь и использовать ChatGPT.
Пример promt и ответа
Есть pandas.dataframe со столбцами
order_month,orders_countиaverage_order_amount. Сформируй json с параметрами для графикаstreamlit_echarts, по оси Xorders_month, по оси Yorders_countс типом bar, цветorders_count-#1E90FF. По дополнительной оси Yaverage_order_amountc типом line, цвет -#B0E0E6. У графика должен быть title - Заказы по месяцам.
Ответ ChatGPT
{
"title": {
"text": "Заказы по месяцам",
"left": "center",
"textStyle": {
"color": "#FFFFFF" # Цвет заголовка
}
},
"tooltip": {
"trigger": "axis"
},
"legend": {
"data": ["Количество заказов", "Средний чек"],
"bottom": "0%", # Легенда внизу
"left": "center", # Выравнивание по центру по горизонтали
"orient": "horizontal", # Горизонтальная ориентация
"textStyle": {
"color": "#FFFFFF" # Цвет заголовка
}
},
"xAxis": {
"type": "category",
"data": df['order_month'].tolist()
},
"yAxis": [
{
"type": "value",
"name": "Количество заказов"
},
{
"type": "value",
"name": "Средний чек",
"position": "right"
}
],
"series": [
{
"name": "Количество заказов",
"type": "bar",
"data": df['orders_count'].tolist(),
"itemStyle": {
"color": "#1E90FF"
},
"yAxisIndex": 0
},
{
"name": "Средний чек",
"type": "line",
"data": df['average_order_amount'].tolist(),
"itemStyle": {
"color": "#B0E0E6"
},
"yAxisIndex": 1
}
]
}
SQL-запросы лучше хранить отдельно и вызывать их как jinja-скрипты. И перенести туда всю логику формирования SQL-запроса на основании фильтров. Также большие json с параметрами echarts-графиков тоже в отдельные файлы.
Пример jinja-скрипта
select
order_month,
sum(total_orders) as orders_count,
sum(total_order_amount)/sum(total_orders) as average_order_amount
from dm.mart_order
where 1=1
{% if date_from %}and order_month>='{{ date_from }}'{% endif %}
{% if date_to %}and order_month<='{{ date_to }}'{% endif %}
{% if city %}and city_name in ({% for item in city %}'{{ item }}'{% if not loop.last %},{% endif %}{% endfor %}){% endif %}
{% if product %}and product_category in ({% for item in product %}'{{ item }}'{% if not loop.last %},{% endif %}{% endfor %}){% endif %}
group by
order_monthПереписанный python-скрипт с использованием jinja
@st.cache_data(ttl=300, max_entries=1000, show_spinner=spinner_text)
def get_order_data(
date_from = None,
date_to = None,
city = None,
product = None
):
"""Получаем данные из витрины"""
with open('get_order_data.sql', "r", encoding="utf-8") as f:
file = f.read()
script = jinja2.Template(file).render(
date_from=str(date_from) if date_from else None,
date_to=str(date_to) if date_to else None,
city=city,
product=product
)
st.write(script)
return pd.read_sql_query(script, engine)Добавим на дашборд еще графиков для более красочного примера.
Python-скрипт
from sqlalchemy import create_engine
import pandas as pd
import streamlit as st
from streamlit_echarts import st_echarts
import jinja2
import datetime
import json
from typing import Literal
st.set_page_config(layout="wide")
# Создаем движок подключения
engine = create_engine('postgresql+psycopg2://postgres:admin@localhost:5432/database')
# получаем и кэшируем данные
spinner_text = "Чтение данных..."
@st.cache_data(ttl=300, max_entries=1000, show_spinner=spinner_text)
def get_unique_values(column):
"""Получаем уникальные значения столбца витрины"""
return pd.read_sql_query(
f"select distinct {column} from dm.mart_order order by {column}",
engine)
@st.cache_data(ttl=300, max_entries=1000, show_spinner=spinner_text)
def get_extreme_value(column, type: Literal["min", "max"]):
"""Получаем экстремумы"""
return pd.read_sql_query(
f"select {type}({column}) from dm.mart_order",
engine).iloc[0, 0]
@st.cache_data(ttl=300, max_entries=1000, show_spinner=spinner_text)
def get_order_data(
date_from = None,
date_to = None,
city = None,
product = None,
price = None
):
"""Получаем данные из витрины"""
with open('get_order_data.sql', "r", encoding="utf-8") as f:
file = f.read()
script = jinja2.Template(file).render(
date_from=str(date_from) if date_from else None,
date_to=str(date_to) if date_to else None,
city=city,
product=product,
price=price
)
return pd.read_sql_query(script, engine)
@st.cache_data(ttl=300, max_entries=1000, show_spinner=spinner_text)
def get_total_data(
date_from = None,
date_to = None,
city = None,
product = None,
price = None
):
"""Получаем данные из витрины"""
with open('get_total_data.sql', "r", encoding="utf-8") as f:
file = f.read()
script = jinja2.Template(file).render(
date_from=str(date_from) if date_from else None,
date_to=str(date_to) if date_to else None,
city=city,
product=product,
price=price
)
return pd.read_sql_query(script, engine)
@st.cache_data(ttl=300, max_entries=1000, show_spinner=spinner_text)
def get_order_speed(
date_from = None,
date_to = None,
city = None,
product = None,
last = False
):
"""Получаем данные из витрины"""
with open('speed.sql', "r", encoding="utf-8") as f:
file = f.read()
script = jinja2.Template(file).render(
date_from=str(date_from) if date_from else None,
date_to=str(date_to) if date_to else None,
city=city,
product=product
)
return pd.read_sql_query(script, engine)
# фильтрация
sidebar = st.sidebar
col1, col2 = sidebar.columns((1,1))
with col1:
from_date = st.date_input("От", value=None)
with col2:
to_date = st.date_input("До", value=None)
city = sidebar.multiselect("Город", options=get_unique_values('city_name'))
product = sidebar.multiselect("Продукт", options=get_unique_values('product_category'))
price = sidebar.slider(
"Цена",
min_value=get_extreme_value("total_order_amount", 'min'),
max_value=get_extreme_value("total_order_amount", 'max'),
step=1.0
)
# метрики
st.caption('За последний месяц')
total_current_data = get_total_data(
from_date,
to_date,
city,
product,
price=price
)
col1, col2, col3, col4 = st.columns((1,1,1,1))
with col1:
total_order = "{:,.0f}".format(total_current_data['total_orders'].tolist()[0])
st.metric("Кол-во заказов", value=total_order, delta=f"{total_current_data['delta_total_orders'].tolist()[0]} %")
with col2:
total_order_amount = "{:,.0f}".format(total_current_data['total_order_amount'].tolist()[0])
st.metric("Объем продаж", value=total_order_amount, delta=f"{total_current_data['delta_total_order_amount'].tolist()[0]} %")
with col3:
average_order_amount = "{:,.0f}".format(total_current_data['average_order_amount'].tolist()[0])
st.metric("Среднее кол-во позиций в заказе", value=average_order_amount, delta=f"{total_current_data['delta_average_order_amount'].tolist()[0]} %")
with col4:
returned_orders = "{:,.0f}".format(total_current_data['returned_orders'].tolist()[0])
st.metric("Кол-во возвратов", value=returned_orders, delta=f"{total_current_data['delta_returned_orders'].tolist()[0]} %", delta_color='inverse')
# График
df = get_order_data(
from_date,
to_date,
city,
product,
price=price
)
df['order_month'] = df['order_month'].apply(lambda x: x.strftime('%Y-%m'))
last_speed_df = get_order_speed(
from_date,
to_date,
city,
product,
last=True
)
speed_df = get_order_speed(
from_date,
to_date,
city,
product
)
speed_df['order_month'] = speed_df['order_month'].apply(lambda x: x.strftime('%Y-%m'))
col1, col2 = st.columns((1,1))
with col1:
with open('orders_by_month.json', "r", encoding="utf-8") as f:
orders_by_month_json = f.read()
orders_by_month_options = jinja2.Template(orders_by_month_json).render(
order_month=df['order_month'].tolist(),
orders_count=df['orders_count'].tolist(),
average_order_amount=df['average_order_amount'].tolist()
)
st_echarts(options=json.loads(orders_by_month_options), height="400px")
with col2:
with open('returned_by_month.json', "r", encoding="utf-8") as f:
returned_by_month = f.read()
returned_by_month_options = jinja2.Template(returned_by_month).render(
order_month=df['order_month'].tolist(),
returned_count=df['returned_count'].tolist(),
returned_percent=df['returned_percent'].tolist(),
max_count=df['orders_count'].max()
)
st_echarts(options=json.loads(returned_by_month_options), height="400px")
col1, col2 = st.columns((1,4))
with col1:
with open('order_speed.json', "r", encoding="utf-8") as f:
last_speed_json = f.read()
last_speed_options = jinja2.Template(last_speed_json).render(
speed=last_speed_df['avg_speed'].tolist()[0]
)
st_echarts(options=json.loads(last_speed_options), height="400px")
with col2:
with open('order_speed_by_month.json', "r", encoding="utf-8") as f:
speed_by_month_json = f.read()
speed_by_month_options = jinja2.Template(speed_by_month_json).render(
speed=speed_df['avg_speed'].tolist(),
order_month=speed_df['order_month'].tolist()
)
st_echarts(options=json.loads(speed_by_month_options), height="400px")
Вообще, у echarts огромное количество различных вариантов визуализаций, что на одно лишь знакомство с ними уйдет несколько часов.
Есть возможность верстать не только графики, но и таблицы.
В streamlit есть возможность создавать многостраничные приложения, если логика вашего дашборда не помещается на одну страницу.
Также при переходе на новую страницу можно передавать дополнительные параметры в url, тем самым воссоздавая подобие drill-down. К примеру, у вас есть приведенная выше таблица, и чтобы посмотреть продажи в деталях по конкретному продукту можно добавить ссылку на новую страницу, в url которой будут передано наименование продукта.
table_data["link"] = table_data.apply(
lambda row: f"http://localhost:8501/product_detail/?name={row['product_category']}",
axis=1
)
Развертывание
Сценарий внедрения подобного инструмента на множество пользователей мне лично видится следующий – создание под каждый дашборд репозиторий в Git, и пользователи запускают дашборды на своих машинах. У пользователя должно быть доступ в СУБД и установленный python. Тем самым мы нивелируем множество проблем с нагрузкой на сервер BI, а ролевой доступ реализуется через раздачу прав конкретным пользователям к конкретным репозиториям.
Streamlit можно развернуть и на сервере, даже можно реализовать аутентификацию в интерфейсе и реализовать кэширование данных под конкретную сессию, но при большом количестве одновременных пользователей наверняка будут проблемы.
С вариантом развертывания на собственной машине и запуска готового дашборда (по инструкции) в состоянии справиться даже пользователь без каких-либо знаний.
Источники данных
В примерах выше был показан вариант с получением данных из хранилища на PostgreSQL. Конечно же, можно читать данные из всех СУБД, благо драйвера python есть практически под каждую.
Также можно читать данные из CSV, Excel: в Streamlit есть встроенный функционал загрузки файлов.
В том числе можно продвинуться дальше и сделать дашборды над датасетами из DuckDB, который сейчас произвел, на мой скромный взгляд, революцию в data-движках. Но здесь материала хватит на новую статью, поэтому не будем углубляться.
Применение и ограничения
Какое же применение скриптовым BI? Могут ли они на текущий момент являться заменой стандартным решениям?
Варианты применения:
нужно быстро для менеджмента реализовать дашборд
много data-команд работают над дашбордами, поэтому требуется контроль версий и возможность отката
бесплатный инструмент с быстрым внедрением
автоматизация развертывания (CI/CD)
перенос нагрузки на машины пользователей
когда требуются широкие возможности кастомизации
прекрасно вписывается в Data mesh
Ограничения, конечно же, тоже есть:
можно, но сложно развернуть на сервере (потребуется аутентификация, кэш в рамках сессии и, скорее всего, большого количества пользователей не выдержит)
не подходит для Self-service аналитики по причине своей сложности (хотя я лично в Self-service аналитику в принципе не верю)
некоторые фичи отсутствуют: drag-n-drop, drill-though или drill-down (хотя, вроде бы, во фреймворке Rill можно сделать последние два)
более сложно рассчитать вычисляемые меры (допустим, в DAX весьма просто посчитать дельту к прошлому периоду, в SQL это сделать гораздо сложнее)
сложно реализовать row-level доступ
Заключение
Буквально каждый год появляются новые и новые фреймворки по работе с данными, в том числе по визуализации, снижая порог входа аналитикам и инженерам. Размывается главное преимущество стандартных BI-инструментов – простота построения дашборда (правда, условная). Преимущество, будто любой бизнес-аналитик или даже менеджер могут набросать несколько графиков, хотя чаще всего именно data-аналитики делают это.
Но data-аналитики сейчас все чаще имеют экспертизу в python. И все чаще используют pandas или другие фреймворки. Пересесть с громоздкого монолита с небольшими возможностями кастомизации на визуализацию в том же python они смогут. При этом, смогут делать чуть ли не полноценные интерфейсы, превращая дашборды в космолеты.
И тогда встает главный вопрос – зачем компании тратить уйму денег на лицензии проприетарного решения, рискуя при этом получить vendor-lock?
Придем ли мы к тому же, к чему пришли в противостоянии GUI ETL и ETL as a code? Есть все основания полагать, что да.
