Оглавление

• SQL

• Copy

• Prepared Statements

• Functions

• Extensions

  • PostgreSQL Extension

• Parquet

  • Parquet Import

  • Parquet Export

• Buena Vista

• Практика

  • Installation

  • Client API Python

    • In-memory

    • Physical database

    • Result Conversion

  • Data Input

    • Установка в JupyterLab

    • Создания локальной БД

    • .json

    • .parquet

    • .csv

  • Storage

  • Information_schema

  • S3

    • Настройка S3

    • Настройка подключения к S3 в DuckDB

    • Сохранение данных в S3

    • Чтение данных из S3

  • Tips

    • UNION

    • JOIN

    • DBeaver

    • Export / Import DataBase

• Заключение

DuckDB — это просто лучший инструмент для взаимодействия с данными (по моему мнению). Он позволяет через единый интерфейс (SQL) общаться с разными сервисами и форматами файлов.

DuckDB дорос до Stable-версии 1.0.0. Было решено множество мелких проблем и теперь мы имеем стабильный продукт, который можно интегрировать в свои проекты.

Так как DuckDB дорос до 1.0.0, хотел бы рассказать о том, что это за инструмент и как его можно использовать в работе дата-инженера.

Изначально я узнал о данном инструменте из видео Николай Голов, Николай Марков, Филлип Уваров: Big Data is Dead на канале { между скобок }.

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

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

Давайте для начала немного познакомимся с нашей уткой:

• Колоночная БД.

• Векторная БД.

• Имеет синтаксис PostgreSQL.

• Не имеет кластера.

• Позиционирует себя как «in‑process analytical database».

• Бесплатно («MIT License»).

• Имеет возможность расширений (PostgreSQL, S3, HTTP(S), etc.).

Я буду часто отсылать вас к документации DuckDB, потому что она очень хорошо написана и содержит множество примеров.

SQL

Начнем с общей информации. Как описано в пункте introduction DuckDB поддерживает синтаксис PostgreSQL, поэтому тут используется «классический» SQL. В DuckDB конечно же реализованы свои методы, но большая часть кода вам будет понятна.

Copy

DuckDB поддерживает мощный движок COPY, который позволяет быстро заполнять таблицы из файлов и также писать сразу в файлы наши таблицы.

Prepared Statements

В DuckDB есть классная возможность для создания состояний, которые позволяют создать более гибкий подход для работы с частыми запросами и исключить SQL-инъекции.

Functions

У DuckDB есть возможность создавать свои функции и также в DuckDB есть свои функции для работы с датами, массивами, строками и прочее.

Extensions

Как я писал ранее DuckDB имеет возможность установки расширений. Также можно писать свои расширения. Более подробно о них описано по ссылке.

PostgreSQL Extension

Благодаря PostgreSQL Extension мы можем читать любую базу данных, которая работает на движке PostgreSQL (PostgreSQL, GreenPlum).

Parquet

Так как DuckDB себя позиционирует OLAP база данных она поддерживает возможность для работы с файлами формата .parquet.

Parquet Import

DuckDB позволяет нативно читать любой .parquet файл. Об этом описано по ссылке.

Parquet Export

Также DuckDB имеет нативный и простой способ для сохранения любых вычислений сразу в .parquet. Об этом описано по ссылке.

Buena Vista

DuckDB позиционирует себя как «in‑process analytical database», но в open source есть библиотека, которая позволяет создать "кластер" из DuckDB и обеспечить подключение по выделенному хосту.

Практика

С теорий немного познакомились, поэтому давайте немного поработаем ручками.

Installation

DuckDB можно установить разными способами, но мы будем в данной статье использовать Python API и нативный .jar для DBeaver.

Client API Python

Так как DuckDB «in‑process analytical database» весь код при создании проекта можно не сохранять физически, но если нам снова понадобятся наши таблицы и расчёты, то мы не сможем получить к ним доступ. Поэтому есть два вариант того как можно работать с DuckDB.

In-memory

При таком подходе все наши таблицы и расчёты не будут сохраняться физически и будут удалены при закрытии коннекта.

import duckdb

cursor = duckdb.connect()
print(cursor.execute('SELECT 42').fetchall())

Physical database

При данном подходе мы создаём физически базу данных и все наши таблицы и расчеты сохранятся. При закрытие коннекта мы ничего не потеряем. А если снова подключимся к БД, то сможем прочитать ранее созданные таблицы. Также стоит отметить, что эту БД можно передавать между собой. Она не имеет никаких зависимостей. Поработав на своей машине можно её отправить другому человеку, как все привыкли перекидывать "эксельки", точно также можно передавать и БД.

import duckdb

# create a connection to a file called 'demo_duckdb.db'
cursor = duckdb.connect('demo_duckdb.duckdb')
print(cursor.execute('SELECT 42').fetchall())
cursor.close()

Result Conversion

Результат выполнения запроса можно получить в любом удобном для вас способе:

import duckdb

duckdb.sql("SELECT 42").fetchall()   # Python objects
duckdb.sql("SELECT 42").df()         # Pandas DataFrame
duckdb.sql("SELECT 42").pl()         # Polars DataFrame
duckdb.sql("SELECT 42").arrow()      # Arrow Table
duckdb.sql("SELECT 42").fetchnumpy() # NumPy Arrays

Data Input

Весь код вы можете выполнять в .py файлах, но там не самый красивый вывод данных для демонстрации. Поэтому для данного проекта я предлагаю вам воспользоваться JupyterLab. Для того чтобы развернуть наш проект в Docker достаточно в корне репозитория выполнить команду:

docker-compose up -d

И наш сервис будет доступен по адресу http://localhost:8888/

После того как мы развернули наш сервис мы можем почитать разные форматы данных.

Установка в JupyterLab

Для установки DuckDB внутрь JupyterLab необходимо выполнить команду в терминале или в «ноутбуке»:

pip install duckdb==1.0.0

Создания локальной БД

Давайте создадим и сохраним отдельно несколько команд, который нам понадобятся.

Создание коннекта к БД и создание самой БД физически:

import duckdb

# create a connection to a file called 'demo_duckdb.db'
con = duckdb.connect('demo_duckdb.duckdb')

Закрытие коннекта к БД:

con.close()

.json

Документация:

• JSON Loading

• JSON extensions

Мы можем прочитать локальный glossary.json и получить сразу результат, с которым можно работать:

con.sql(
    """
    SELECT * FROM read_json("glossary.json")
    """
).df()

Мы можем в удобном формате через точку получить нужные нам значения по ключу:

con.sql(
    """
    SELECT glossary.GlossDiv.GlossList.GlossEntry.ID FROM read_json("glossary.json")
    """
).df()

И таким способом мы можем «развернуть» весь .json

Также мы можем прочитать .json с указанием url (удалённый сервер, S3, etc):

con.sql(
    """
    SELECT * FROM read_json('https://support.oneskyapp.com/hc/en-us/article_attachments/202761627')
    """
).df()

.parquet

Документация:

• Reading and Writing Parquet Files. Как мы читали наш .json файл, точно также мы можем прочитать и .parquet.

con.sql(
    """
    SELECT * FROM read_parquet('https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2024-01.parquet')
    """
).df()

.csv

Интересных примеров для демонстрации нет, поэтому рекомендую ознакомиться с официальной документацией по работе с .csv.

Storage

Важно: Все дальнейшие примеры выполнялись через DBeaver. Но если вы захотите выполнить данный код внутри API Python, то вам необходимо обернуть SQL-код в кавычки и прописать коннект к вашей БД.
Пример:

con.sql(
    """
    <your SQL-query>
    """
)

---

Мы отлично поработали с данными, которые загружались к нам в оперативную память. Но давайте теперь попробуем поработать именно с самим хранилищем DuckDB, потому что оно обладает крутыми скоростями.

Для начала возьмём ранее использовавшийся файлик с поездками такси и создадим из него таблицу:

CREATE TABLE yellow_tripdata_2024_01
SELECT * FROM read_parquet('https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2024-01.parquet')

Создание данной таблицы у нас заняло 5.77 сек. Большая часть времени ушла на сетевые загрузки. Потому что если мы скачаем этот файл локально и выполним тот же самый скрипт с указанием локального файла, то его загрузка в нашу БД займёт 1.27 сек.

Посчитаем количество строк в нашей таблице:

SELECT COUNT(*) FROM yellow_tripdata_2024_01

И это занимает в среднем менее миллисекунды.

Если мы захотим посчитать что-то посложнее, то получим примерно те же результаты – менее миллисекунды на запрос:

SELECT 
    tpep_pickup_datetime::date AS date_, 
    SUM(total_amount) AS sum_,
    COUNT(*) AS count_,
    SUM(total_amount) / COUNT(*) AS avg_ 
FROM 
    yellow_tripdata_2024_01
WHERE
    tpep_pickup_datetime BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY 1
ORDER BY 1

Благодаря таким скоростям DuckDB может быть хорошим инструментом для аналитики и проведения исследований.

Information_schema

Всю информацию про information_schema можно прочитать по ссылке.

В DuckDB реализована information_schema по SQL-стандартам. Благодаря ей можно получить множество информации по нашей БД.

Для примера мы можем посмотреть информацию по колонкам в ранее созданной таблице:

SELECT * FROM information_schema.columns
WHERE table_name = 'yellow_tripdata_2024_01'

И в результате мы получим 45 колонок, который позволяют получить информацию по каждой из колонок в таблице.

S3

В прошлой статье «Инфраструктура для data engineer S3» мы рассматривали S3 как сервис для дата-инженеров. А сейчас при помощи DuckDB мы попробуем немного воссоздать работу дата-инженера при работе с S3.

Для этого мы в наш существующий docker-compose.yml добавим сервис minio из прошлой статьи, чтобы организовать необходимую инфраструктуру для демонстрации. После добавления сервиса minio необходимо выполнить команду docker-compose up -d.

И теперь оба сервиса находятся в одном проекте и готовы общаться друг с другом.

Настройка S3

Для корректной работы DuckDB необходимо:

1. Настроить access key и secret key.

2. Создать бакет. Я создам через веб-интерфейс, чтобы было попроще. Но а если вы хотите это сделать через Python, то в статье «Инфраструктура для data engineer S3» описан данный процесс. Важно: Если вы хотите работать с S3 через сервис, развернутый в Docker, то вам необходимо прописывать s3_endpoint как minio:9000. А если вы работаете локально через тот же DBeaver, то необходимо указывать localhost:9000 в s3_endpoint.

Настройка подключения к S3 в DuckDB

Для создания подключения необходимо установить необходимые параметры
Важно: Я сейчас покажу метод, который считается устаревшим. Но из-за некоторых проблем при работе с локальным S3 я не могу воспользоваться новым методом. Информация про старый метод; Информация про новый метод.

Чтобы организовать подключение к S3 через DuckDB необходимо выполнить следующий код:

INSTALL httpfs;
LOAD httpfs;
SET s3_url_style = 'path';
SET s3_endpoint = 'localhost:9000';
SET s3_access_key_id = 'rxZaCuukKWeuN4oF08mX';
SET s3_secret_access_key = 'yWJYVFkVxGxuIwDyD1hxepSrO86E816nVkONtgAf';
SET s3_use_ssl = FALSE;

Сохранение данных в S3

Давайте теперь сохраним ранее созданную нами таблицу yellow_tripdata_2024_01 в S3. Для этого необходимо выполнить команду:

COPY
(
  SELECT
    *
  FROM
    main.yellow_tripdata_2024_01
) TO 's3://test-duckdb/yellow_tripdata_2024_01.gzip.parquet'

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

SELECT count(*) FROM 's3://test-duckdb/yellow_tripdata_2024_01.gzip.parquet'

И мы сможем увидеть тоже самое количество строк, что и в yellow_tripdata_2024_01.

Также DuckDB позволяет сохранять таблицы в формате партиций. Для примера мы также будем использовать yellow_tripdata_2024_01 и сделаем сохранение таблицы с партицированием по дням следующим кодом:

COPY (
    SELECT
		*,
		tpep_pickup_datetime::date AS tpep_pickup_datetime_date
	FROM
		yellow_tripdata_2024_01
	WHERE
		tpep_pickup_datetime BETWEEN '2024-01-01' AND '2024-01-31'
)    
TO 's3://test-duckdb/yellow_tripdata_partition'
(
  FORMAT PARQUET,
  COMPRESSION gzip,
  PARTITION_BY (tpep_pickup_datetime_date),
  OVERWRITE_OR_IGNORE,
  FILENAME_PATTERN "file_{i}"
);

Важно: Если вы будете использовать маску file_{uuid}, то при каждой вставке в вашу партицию будет добавляться новый новый файл, с новым уникальным uuid. А если вы выберете маску file_{i}, то при каждой вставке в партицию вы будете перезаписывать ранее созданный файл.

Стоит также отметить крутое свойство для DuckDB – это работа с масками. Мы ранее сохранили наши данные в партицированном виде и теперь имеем такую структуру в S3:

test-duckdb/yellow_tripdata_partition/tpep_pickup_datetime_date=2024-01-01/file_0.parquet
test-duckdb/yellow_tripdata_partition/tpep_pickup_datetime_date=2024-01-02/file_0.parquet
...

И каждый из элементов нашего пути можно замаскировать при помощи звезды *.

Чтение данных из S3

Чтобы прочитать все файлы, не смотря на партиции мы выполним такой код:

SELECT
	count(*)
FROM
	's3://test-duckdb/yellow_tripdata_partition/*/*.parquet'

При помощи наших звёзд мы замаскировали весь путь к файлу и в данном случае нам не важно в каких папках находятся данные, как они разбиты. Просто зная структуру мы маскируем её. Если мы захотим прочитать определенный путь, то мы его сможем спокойно указать.

Важно: также работает частичная маска. К примеру можно прописать маску такого вида: s3://test-duckdb/*-foo/*/*.parquet

Также стоит отметить, что в данном запросе работает корректно партицирование и фильтрация данных, если мы выполним этот код:

SELECT
	count(*)
FROM
	's3://test-duckdb/yellow_tripdata_partition/*/*.parquet'
WHERE
	tpep_pickup_datetime_date = '2024-01-10'

То мы как раз получим необходимое количество строк за 2024-01-10 (95'000).

И если выполнить Query Profiling, то мы увидим следующую информацию:
наш запрос просканировал партиции и выдал нужный файл.

Tips

Ниже будут перечислены интересные фишки и советы по DuckDB, которые не могу выделить в отдельную тему.

UNION

Довольно частая проблема при работе с данными — это изменение модели данных и в DuckDB реализована крутая фишка с чтением данных, которые имеют разную модель.

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

COPY
(
	SELECT
		1 AS id,
		'Anna' AS first_name,
		'Petrova' AS last_name
) TO 's3://test-duckdb/users/1-model/0.gzip.parquet';

COPY
(
	SELECT
		2 AS id,
		'Igor' AS first_name,
		'Ivanov' AS last_name,
		'2001-01-01'::date AS bd 
) TO 's3://test-duckdb/users/2-model/0.gzip.parquet';	

COPY
(
	SELECT
		3 AS id,
		'Sergey' AS first_name,
		'Shilov' AS last_name,
		'1999-01-01'::date AS bd,
		'2024-01-01'::date AS created_at
) TO 's3://test-duckdb/users/3-model/0.gzip.parquet';

После выполнения мы сделали запись разных моделей данных одной и той же таблицы.

Если выполнить запрос:

SELECT * FROM 's3://test-duckdb/users/*/*.gzip.parquet'

То получим следующий результат:

Как мы видим он объединил информацию только по тем столбцам, которые совпадают, но а если мы хотим получить все колонки, то необходимо выполнить следующий код:

SELECT
	*
FROM
	read_parquet(
		's3://test-duckdb/users/*/*.gzip.parquet',
		union_by_name = TRUE
	)

И получим такой результат:

В данном виде мы уже можем работать с таблицей, не завися от модели данных.

JOIN

На самом деле довольно очевидный хак, но может он будет кому-то полезен: в DuckDB есть возможность делать JOIN между данными, которые хранятся в самой DuckDB и на удалённом ресурсе. В примере ниже мы рассмотрим работу локальной БД и S3.

Для демонстрации давайте подготовим данные. Выполните код ниже:

CREATE TABLE users
(
	id int4,
	first_name varchar(50),
	last_name varchar(50),
	staff_project int4
);

INSERT INTO users
SELECT
	1 AS id,
	'Anna' AS first_name,
	'Petrova' AS last_name,
	10 AS staff_project;

COPY
(	
	SELECT
		10 AS id_project,
		'foo' AS name_project
) TO 's3://test-duckdb/project/9999-12-31/0.gzip.parquet'	

И теперь если мы выполним запрос ниже, то мы соединим данные между таблицей, которая сохранена локально в нашей БД и .parquet файлом, который находится в S3.

SELECT
	*
FROM
	users AS u
JOIN read_parquet('s3://test-duckdb/project/9999-12-31/0.gzip.parquet') AS p
	ON u.staff_project = p.id_project

Результат после выполнения JOIN:

DBeaver

С DuckDB можно работать не только через Python, но и через привычный инструмент для многих — DBeaver.

Чтобы создать подключение к DuckDB мы его просто выбираем через общий интерфейс для создания подключений (вилка) и выбираем DuckDB. Изначально драйвер DuckDB не скачан, поэтому DBeaver предложит его скачать. Но если вы захотите скачать другую версию драйвера или у вас возникли проблемы с установкой, то можно скачать драйвер самостоятельно с сайта Maven и при подключении указать данный драйвер.

Через DBeaver можно подключаться как к ранее созданным БД DuckDB. К примеру я могу подключиться к БД DuckDB, которую создавал ранее через Python и продолжить работу с ней уже через DBeaver.

Также у нас есть возможность создать свою БД, для этого необходимо нажать «Создать...» и выбрать путь для сохранения БД.

Важно: Если не создавать БД локально, то все созданные таблицы во время сессии будут удалены. Таблицы и данные удаляются при закрытии коннекта к БД.

Export / Import DataBase

Во время работы DuckDB может «распухать» и чтобы уменьшить размер самой БД можно воспользоваться экспортом БД.

Экспорт можно сделать в удобном для вас формате.

Чтобы выполнить экспорт БД в JupyterLab необходимо выполнить код:

con.sql(
    """
    EXPORT DATABASE 'main' (
        FORMAT PARQUET,
        COMPRESSION ZSTD
    );	
    """
)

И рядом с вашим «ноутбуком» создатся папка main, в которой будут все данные и схема данных.

Для того, чтобы выполнить экспорт БД через DBeaver необходимо прописывать полный путь для экспорта, пример ниже:

EXPORT DATABASE '<path_to_export>' (
    FORMAT PARQUET,
    COMPRESSION ZSTD
);

Также данный экспорт позволит вам передавать результаты своих расчетов.

Подробнее о экспорте и импорте можно узнать из документации.

Заключение

DuckDB — очень простой и удобный инструмент. Он позволяет работать со всеми современными форматами данных. Общаться с Data Lake. А если использовать внутренний движок таблиц DuckDB, то ваши OLAP запросы будут быстро выполняться. Ну и не стоит забывать, что SQL выучить легче, чем Python, Pandas, PySpark, etc., все аналитики знакомы с SQL и поэтому я считаю, что это будет отличным инструментом для аналитики.

Также если вам необходима консультация/менторство/мок-собеседование и другие вопросы по дата-инженерии, то вы можете обращаться ко мне. Все контакты указаны по ссылке.