DuckDB. OLAP-куб в кармане

Привет, меня зовут Антон, я старший инженер в департаменте аналитических решений ЮMoney. В компании мы используем технологию MSSQL OLAP-кубов SSAS, которая хорошо себя зарекомендовала — она сравнительно легко развёртывается и достаточно производительная. Но есть ряд минусов: Microsoft прекратил развивать её примерно в прошлом десятилетии, технология требует производительных серверов, ну и, конечно, вопрос зависимости от иностранного вендора тоже стоит остро. Поэтому, посматривая по сторонам в поисках альтернативы, я решил попробовать недавно появившуюся технологию DuckDB. Особых надежд не было, но хотелось понять, на каком она уровне по сравнению с привычными для меня инструментами. 

Краткий обзор технологии

DuckDB — это опенсорс-проект портативной и высокопроизводительной аналитической СУБД. Реализует диалект SQL с функциями, выходящими за рамки базового SQL, поддерживает произвольные и вложенные коррелированные подзапросы, оконные функции, сопоставления, сложные типы и тому подобное.

DuckDB дополнен более энтерпрайзовым решением MotherDuck, которое решает вопросы дистрибуции и интеграции, однако не является опенсорсным.

Далее во всех примерах использую Python.

Начать работу очень просто:

con = duckdb.connect(database = "cube.duckdb", read_only = False)  ''' параметр read_only определяет, мы открываем в режиме чтения или записи. Чтение предполагает множественные обращения, а запись всегда эксклюзивна.  ''' 

Подключение в режиме записи может быть только одно, в режиме чтения — множество.

Также можно создать inmemory olap БД:

con = duckdb.connect()

Быстро пополняется данными из разных источников. Ниже пример для CSV:

con.execute("CREATE TABLE ExampleTable AS SELECT * FROM read_csv_auto('C:\MyProject\duckdb\seed\ExampleTable.csv');") 

И можно сразу выполнять запросы:

df = con.execute("""SELECT * FROM ExampleTable""").df()

Имеет расширение для работы с s3, а также может дистрибутироваться через s3:

-- Install extensions    INSTALL httpfs;    LOAD httpfs;    -- Minimum configuration for loading S3 dataset if the bucket is public    SET s3_region='us-east-1';      CREATE TABLE netflix AS SELECT * FROM read_parquet('s3://duckdb-md-dataset-121/netflix_daily_top_10.parquet');

Имеет расширение для работы с книгами Excel:

INSTALL spatial; LOAD spatial;   SELECT * FROM st_read('test_excel.xlsx', layer = 'Sheet1');       COPY tbl TO 'output.xlsx' WITH (FORMAT GDAL, DRIVER 'xlsx');

Хороший туториал и обзор особенностей DuckDB есть вот тут: Всё что нужно знать про DuckDB. А в этом материале я хотел бы провести сравнительный эксперимент по производительности.

Подготовка эксперимента

Продуктивнее всего проверять технологии на каких-то реальных рабочих примерах, и для этого эксперимента я выбрал один их наших OLAP-кубов. Но в финтехе важно соблюдать требования безопасности, так что я сгенерировал суррогатный набор данных, максимально близкий по структуре и характеристикам к тому, что есть у нас на проде.

Знакомое продакшн-решение облегчило дальнейший выбор сценариев. Я взял наиболее типичный запрос, который пользователи посылают в наш OLAP-куб.

Запрос среза данных по суммам, сгруппированный по типам транзакций и датам, в варианте MDX:

SELECT     NON EMPTY { [Measures].[Сумма] } ON COLUMNS,     NON EMPTY { ([Дата операции].[Дни].[День].ALLMEMBERS * [Типы транзакций].[Тип транзакции].[Тип транзакции].ALLMEMBERS ) } ON ROWS FROM (         SELECT ( [Дата операции].[Дни].[День].&[44927] : [Дата операции].[Дни].[День].&[45443] ) ON COLUMNS         FROM [OLAP]      )

В варианте SQL:

SELECT     t.TRANSACTION_TYPE_NAME     ,f.DATE     ,SUM(f.BONUS_SUM) AS xSUM FROM dbo.OLAP AS f INNER JOIN dbo.DIM_TRANSACTION_TYPE AS t     ON f.TRANSACTION_TYPE = t.TRANSACTION_TYPE_ID GROUP BY GROUPING SETS ((t.TRANSACTION_TYPE_NAME , f.DATE), (t.TRANSACTION_TYPE_NAME ), (f.DATE), ())

Чтобы оценить технологию, я решил протестировать в первую очередь скорость выполнения этих запросов. Но интересно было посмотреть и объёмы хранения информации в разных вариантах, и скорость загрузки данных.

Для эксперимента я решил использовать свой ноутбук. Все тестовые запуски производил на нём, так что соотношение метрик позволит мне сделать выводы о технологиях.

Конфигурация железа: 

Processor: 11th Gen Intel(R) Core(TM) i7-1165G7 @ 2.80GHz   2.80 GHz

RAM: 32.0 GB

HDD: 500Gb SSD

Эксперимент

DuckDB подкупает простотой запуска. Сначала создадим виртуальное окружение:

python.exe -m pip install --upgrade pip pip install virtualenv virtualenv venv venv\Scripts\activate.bat   pip install pandasgui   pip install duckdb --upgrade

Я заранее заготовил CSV-файлы со сгенерированными данными, загрузим их. Самый крупный весит 15Gb (110млн строк):

from datetime import datetime, date, time import duckdb   print (datetime.now()) con = duckdb.connect(database = "cube.duckdb", read_only = False)   con.execute("CREATE TABLE OLAP AS SELECT * FROM read_csv_auto('C:\MyProject\duckdb\seed\OLAP.csv');")   con.execute("CREATE TABLE DIM_TRANSACTION_TYPE AS SELECT * FROM read_csv_auto('C:\MyProject\duckdb\seed\DIM_TRANSACTION_TYPE.csv');") print (datetime.now())

Прошло две минуты, наш карманный OLAP-куб готов, можно запрашивать данные. Полученный файл весит 5 Gb:

from datetime import datetime, date, time import duckdb import pandas from pandasgui import show   print (datetime.now())   con = duckdb.connect(database = "cube.duckdb", read_only = True)   df = con.execute("""SELECT  t.TRANSACTION_TYPE_NAME ,f.DATE ,sum(f.BONUS_SUM) AS SUM FROM "cube".main.OLAP as f INNER JOIN "cube".main.DIM_TRANSACTION_TYPE t ON f.TRANSACTION_TYPE = t.TRANSACTION_TYPE_ID GROUP BY GROUPING SETS ((t.DIM_TRANSACTION_TYPE_NAME , f.DATE), (t.DIM_TRANSACTION_TYPE_NAME ), (f.DATE), ())""").df()   print (datetime.now())   show(df)

Готово!

Семь секунд, и с данными можно работать.

Скорость использования, конечно, приятно впечатляет: пара десятков строчек кода плюс около получаса на то, чтобы разобраться и загрузить данные, и OLAP-база готова. 

Сравнительные тесты

Для сравнения аналогичные тесты я запустил для OLAP-куба, для БД MSSQL с плоской таблицей и уровнем компрессии page, для БД MSSQL с таблицей columnstore и parquet.

Выводы

Конечно, метрики DuckDB уступают и OLAP от MSSQL, и технологии колоночного хранения от MS SQL. Однако и та и другая технологии требуют дорогих лицензий и производительной серверной части.

Для решения, которое развёртывается из библиотеки Python за десятки минут, я бы назвал метрики отличными. Открытым остаётся вопрос дистрибуции. Конечно, Mother Duck решает этот вопрос, но сразу возникает зависимость от вендора и необходимость тратиться на лицензии. Скорее всего, следующий эксперимент я проведу с использованием s3.

Второй открытый вопрос — простота пользовательского вхождения. SSAS позволяет использовать Excel как клиент, что делает аналитику доступной широкому кругу пользователей. DuckDB и множество других OLAP-решений на рынке такого не позволяют и предъявляют более высокие требования к пользователям. Возможно, с этим может помочь такой интересный проект, как Mondrian. Скорее всего, он станет объектом одного из моих исследований в будущем.