Аналитика небольших данных: как совместить Excel, Python и SQL с помощью инструментов с открытым исходным кодом

Введение

Однажды Джоэл Спольски в своей знаменитой презентации «You Suck at Excel» сказал, что существуют сотни коммерческих продуктов, вместо которых можно было бы использовать табличку Excel.

В этой шутке очень высокая доля правды. Excel благородя интуитивному UI, формулам и VBA дает возможность пользователям самим решать широкий спектр задач, избавляя от необходимости каждый раз обращаться за помощью к разработчикам или внедрять в компании новый продукт. Excel используется везде от мелкой розницы до Fortune 500 и CERN.

Пользовательский интерфейс Excel оказался, настолько удачным – ни один из конкурентов так и отошел от привычных таблиц. Для пользователя Excel интерфейс Google Spreadsheet будет привычным и интуитивно понятным. А вот по своим функциональным возможностям Excel до сих пор превосходит большинство конкурентов.

Как с помощью двух мощных инструментов с открытым исходным кодом можно совместить привычный для пользователей интерфейс, надежность и мощь SQL, гибкость Python и командную работу как в Google Spreadsheet? Давайте рассмотрим это на простом примере.

Grist

https://github.com/gristlabs/grist-core

Это аналог Google Spreadsheet который можно хостить на своей инфраструктуре. Интерфейс Grist похож на Excel, но вместо VBA используется Python, а вместо инфраструктуры Google каждый документ Grist представляет из себя базу SQLite.

В каждой ячейке можно вводить формулы на Питоне:

https://support.getgrist.com/formulas

По сути дела весь документ Grist – это скрипт на Питоне, который сохраняет данные в базу SQLite:

У этого решения есть огромный плюс – ваши данные всегда остаются у вас. Для SQLite очень легко настроить резервное копирование – в отличии от «серьезных» баз данных, SQLite не требует особого ухода, но при этом почти ничем не уступает по функционалу MySQL или Postgres.

Не будем подробно разбирать все возможности Grist, разработки этого продукта ушли гораздо дальше Google Spreadsheet и Excel. По сути дела, Grist можно использовать в качестве мини CRM в небольшой организации. И это решение будет мало чем уступать дорогим (в плане обслуживания) решениям вроде Salesforce.

В плане администрирования Grist очень неприхотлив, сервис можно развернуть в контейнере следующим образом:

mkdir persist docker run -p 8484:8484 -v $$PWD/persist:/persist -it gristlabs/grist 

В директории persist будут сохранятся данные в SQLite – это необходимо для того, чтобы они могли «пережить» перезагрузку сервиса.

Metabase

Girst – это отличный вариант для небольшой команды специалистов, но что делать бизнес-аналитику или дата-инженеру, которому нужно анализировать данные из десятков различных источников и необходимо создавать сложные отчеты и витрины данных?

Для этих целей отлично подходит другой инструмент открытым исходным кодом – Metabase.

https://www.metabase.com/data_sources

В Metabase есть мониторинг и возможность рассылки по почте. Графики Metabase можно встраивать на сторонних ресурсах. Существует возможность настройки интерактивных витрин данных и эверистика (Query Builder) с помощью, которого пользователь может задавать вопросы, которые автоматически конвертируются в SQL-запросы.

Настройка Metabase

Для установки вам понадобится Docker compose. Нужно создать файл docker-compose.yml со следующим содержимым:

version: '3.9' services:   metabase:     image: metabase/metabase:latest     container_name: metabase     hostname: metabase     volumes:     - /dev/urandom:/dev/random:ro     - /srv/grist:/srv/grist/persist:ro     ports:       - 3000:3000     environment:       MB_DB_TYPE: postgres       MB_DB_DBNAME: metabase       MB_DB_PORT: 5432       MB_DB_USER_FILE: /run/secrets/db_user       MB_DB_PASS_FILE: /run/secrets/db_password       MB_DB_HOST: postgres     networks:       - metanet1     secrets:       - db_password       - db_user     healthcheck:       test: curl --fail -I http://localhost:3000/api/health || exit 1       interval: 15s       timeout: 5s       retries: 5   postgres:     image: postgres:latest     container_name: postgres     hostname: postgres     environment:       POSTGRES_USER_FILE: /run/secrets/db_user       POSTGRES_DB: metabase       POSTGRES_PASSWORD_FILE: /run/secrets/db_password     networks:       - metanet1     secrets:       - db_password       - db_user networks:   metanet1:     driver: bridge secrets:    db_password:      file: db_password.txt    db_user:      file: db_user.txt 

Затем создать в текущей директории два файла:

• db_user.txt

• db_password.txt

В этих файлах вам нужно записать имя пользователя и пароль к базе данных Metabase.
Пускай это будет postgres и postgrespassword.

Следующая строчка конфига очень важна: мы монтируем директорию в которой Grist сохраняет свои документы в контейнер Metabase. Затем этот путь будет использоваться Metabase для подключения к базе данных:

    volumes:     - /dev/urandom:/dev/random:ro     - /srv/grist/persist:/srv/grist:ro 

В настройках Metabase в разделе «Admin Settings» нужно добавить новую баз данных SQLite указав путь до примонтированной директории Grist /srv/grist/doc/НАЗВАНИЕ_ДОКУМЕНТА_GRIST. В этом же разделе можно задать настройки автоматического сканирования схемы и таблиц.