Привет, Хабр! Кто не знает, я уже почти пять лет развиваю свой проект sqlize.online - SQL песочницу, где можно быстро накидать SQL, погонять JOIN или скинуть кому-то воспроизводимый пример запроса.
Песочница уже поддерживает все основные реляционные базы данных от SQLite до Oracle, но я постоянно ищу как ещё можно расширить функционал, обновляю версии баз данных на актуальные, добавляю тестовые наборы данных и т.д. DuckDB я хотел прикрутить туда давно, с тех пор как узнал об этой легковесной аналитической базе. Когда я сам начал использовать её в рабочих проектах то понял, насколько круто иметь локальный OLAP под рукой.
Нмоного о DuckDB
Если совсем грубо - это «SQLite для аналитики». Векторизованный движок под OLAP, тяжёлые агрегации на одном ядре, без забот о транзакциях. Подобно SQLite, DuckDB не требует отдельного сервера и работает как библиотека внутри процесса языка программирования вызывающего ее функции. DuckDB читает данные из файлов CSV и Parquet на лету, хоть с диска, хоть по HTTP, без отдельного шага загрузки и позволяет использовать SQL для анализа этих данных. Для песочницы это прямо то что нужно - можно сразу дать людям что-то настоящее вместо таблицы из двух строк.
Останавливало меня лишь то, что бэкенд проекта написан на PHP и я добавляю базы данных которые поддерживаются драйвером PDO. До недавних пор нормального драйвера к DuckDB просто не существовало. MySQL, Postgres, SQLite - пожалуйста, через PDO из коробки, даже ClickHouse - благодаря совместимости интерфейса с MySQL. С небольшими ухищрениями подключил SQL Server и Oracle, а DuckDB - нет.
В очередной беседе ИИ подкинул мне ссылку на проект pdo_duckdb Ильи Альшанецкого. Реальный PDO-драйвер. Пазл сложился, дальше дело техники - архитектура бэкенда отработана так что добавление новой базы нужно только написать расширение базового класса с функциями поддержки новой базы.
Сборка контейнера
Каждая версия PHP в моём проекте живёт в своём Docker - контейнере, поэтому сначала пересобираю образ с поддержкой DuckDB. Библиотека pdo_duckdb требует PHP 8.1+. На phpize.online несколько пулов PHP под разные версии, PHP 7.6 и 8.0 сразу отваливается по версии. Но и на 8.1+ не всё гладко - готовые бинарники в последнем релизе есть только под 8.4/8.5. Пришлось собирать из исходников, phpize && ./configure --with-pdo-duckdb=... && make install, линковка против libduckdb.so. Процесс сборки хорошо описан в интернете, поэтому не буду подробно расписывать. У меня половина движков в проекте и так собирается руками, не привыкать.
А заодно поймал баг, вообще не связанный с DuckDB: обновил PHP 8.1 до последнего патч-релиза, и сборка образа развалилась на ровном месте. Оказалось, что базовый образ с DockerHub который я использую под капотом молча переехал с Debian bookworm на trixie, а там libaio1 переименовали в libaio1t64. Решил пока не тратить время на эту проблему и откатил базовый образ, но нервов помотало прилично - пока гадал, что вообще случилось.
Отдельно пришлось решить задачу с безопасностью. Как я писал выше, уточка (DuckDB) умеет читать файлы с диска и по сети, что опасно для публичной песочницы. Ниже я опишу как я не дал этому превратиться в дырку, через которую читают файлы сервера.
Главная проблема: DuckDB живёт прямо внутри процесса PHP
MySQL и Postgres крутятся в соседнем контейнере и физически не видят файловую систему PHP-воркера. DuckDB - другое дело, она встроенная, открывается прямо внутри PHP-FPM. Скорость приятная, ни сокетов, ни IPC. Зато по умолчанию у неё доступ ко всему, до чего дотягивается сам процесс: COPY ... TO '/etc/что-нибудь', read_csv с любого пути, read_parquet откуда угодно по сети, INSTALL произвольного расширения. Для песочницы где каждый может выполнить свойй SQL это не подходит, мягко говоря.
Почитал про параметры конфигурации и попробовал в лоб, настройки прямо в SQL при старте каждой сессии:
SET enable_external_access = false; SET allow_community_extensions = false; SET lock_configuration = true;
Работает. lock_configuration = true реально не даёт той же сессии откатить это потом. Но у драйвера своя защита: часть security-настроек - пути, автозагрузку расширений - он просто отказывается принимать через DSN или PDO::DUCKDB_ATTR_CONFIG в момент коннекта. Специально, чтобы приложение само себе не прострелило ногу.
Пришлось искать другой способ. Оказалась настоящая изоляция - это open_basedir в самом PHP. Выставляешь его, можно прямо в рантайме через ini_set(), и драйвер целиком выключает у DuckDB весь внешний доступ: read_csv, read_parquet, COPY, ATTACH, httpfs, INSTALL. Независимо от пути. Обычные CREATE TABLE/INSERT/SELECT над своим файлом сессии при этом работают как обычно.
ini_set('open_basedir', '/tmp/databases'); $pdo = new PDO("duckdb:{$sessionFile}");
Пробовал совместить оба слоя защиты, open_basedir плюс SET сверху, для надёжности. Не вышло: если open_basedir уже активен, драйвер прямо ругается на Invalid Input Error: Cannot change configuration option. Пришлось выбирать одно - оставил open_basedir, он и один закрывает всё что нужно.
В итоге удалось запустить контейнер и получить работающую базу данных: DuckDB 1.5.5
Далее: добавляем базу с данными
В сети существует множество баз данных в подходящих для работы с DuckDB. После недолгих поисков я выбрал публичный датасет NYC Yellow Taxi за январь 2024 года. Мой выбор был обусловлен тем что содержит записи о более двух миллионов реальных поездок и удобно упакован в формат Parquet.
Датасет грузится не на каждый чих
Импорт двух миллионов строк занимает от 5 до 35 секунд, поэтому данные загружаются один раз при деплое сервиса, а затем переиспользуются в каждой новой сессии. Дальше каждая сессия - просто copy() готового файла, доли секунды. После импорта данные помещаются в таблице yellow_tripdata и готовы в к анализу при помощи SQL запросов:
SELECT PULocationID, COUNT(*) AS total_trips, ROUND(AVG(total_amount), 2) AS avg_fare FROM yellow_tripdata GROUP BY PULocationID ORDER BY total_trips DESC LIMIT 10;
Итого
Теперь на sqlize.online можно не только гонять SQL по MySQL/Postgres, но и сравнить, как один и тот же аналитический запрос ведёт себя на классической реляционке против векторизованного движка - на реальных данных, а не на трёх строчках.
Пробуйте, ломайте, пишите в комментарии, если найдёте баг или знаете датасет получше.
Если сломаете - напишите, а не кладите сервис
Знаю, что среди читающих полно тех, для кого «нельзя обойти» звучит как приглашение. Ну и ладно, welcome.
Только если найдёте способ прочитать файл сервера или вылезти из open_basedir - напишите мне напрямую, а не роняйте сервис. Бюджета на bug bounty нет, но в посте и на сайте укажу с благодарностью.