Всех приветствую! Зовут меня Павел, работаю в 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-echarts
Json-параметров огромное количество, разобраться в них с нуля сложно, но можно упростить себе жизнь и использовать 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? Есть все основания полагать, что да.
