evaldor Sep 3 at 17:32

DuckDB. Колоночная OLAP СУБД в кармане

Medium

5 min

3.9K

ЮMoney corporate blogIT Infrastructure*IT-companiesData Engineering*

Привет, меня зовут Антон, я старший инженер в департаменте аналитических решений Ю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.

	Скорость выполнения запроса (сек.)	Занимаемое место (Gb)
изначальный CSV	-	15
parquet	360	1,5
OLAP	1	7
mssql(compression page) -	93	2,5
mssql(columnstore)	2	0,37
DuckDB (persistant)	7	5
DuckDB (inmemory)	6	-

Выводы

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

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

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

Tags:

Hubs: