DuckDB

Моделі та методи обробки великих даних

Ігор Мірошниченко

КНУ імені Тараса Шевченка, ФІТ

Що таке DuckDB?

Проблема: розрив між малими та великими даними

Малі дані (Pandas, R)

  • Просто використовувати
  • Працює на ноутбуці
  • Обмежено RAM
  • Повільно на великих обсягах

Великі дані (Spark, Hadoop)

  • Складна інфраструктура
  • Потребує кластер
  • Масштабується
  • Надмірно для “середніх” даних

А що якщо дані “досить великі” — 1-500 ГБ?

Рішення: DuckDB

DuckDB — це вбудована аналітична СУБД (OLAP), яка працює в процесі вашого додатка.

  • Як SQLite, але для аналітики
  • Не потребує сервера чи інфраструктури
  • Обробляє сотні мільйонів рядків за секунди
  • Працює на звичайному ноутбуці

flowchart TD
    A[Ваш Python/R скрипт] --> B[DuckDB Engine]
    B --> C[CSV / Parquet / JSON]
    B --> D[Pandas DataFrame]
    B --> E[Polars DataFrame]
    B --> F[PostgreSQL / MySQL]
    style B fill:#f9b928,stroke:#333,stroke-width:2px

Ключові характеристики DuckDB

Характеристика Опис
Вбудована Працює в процесі (in-process), без сервера
Стовпчаста Колоночне зберігання для швидкої аналітики
Векторизована Обробка даних пакетами (vectorized execution)
SQL Повна підтримка SQL з розширеннями
Мультиформатна CSV, Parquet, JSON, Arrow, PostgreSQL
Кросплатформна Python, R, Java, Node.js, C/C++, Rust
Безкоштовна MIT ліцензія, open source

Архітектура DuckDB

flowchart LR
    subgraph "Клієнт"
        A[Python / R / SQL]
    end
    subgraph "DuckDB Engine"
        B[Parser] --> C[Planner]
        C --> D[Optimizer]
        D --> E[Executor]
    end
    subgraph "Storage"
        F[Columnar Storage]
        G[Buffer Manager]
    end
    A --> B
    E --> F
    E --> G
    style D fill:#f9b928,stroke:#333

Ключові компоненти:

  • Parser — розбирає SQL запит
  • Planner — створює план виконання
  • Optimizer — оптимізує план (predicate pushdown, projection pushdown)
  • Executor — векторизоване виконання з потоковою обробкою

DuckDB vs SQLite vs Spark

DuckDB SQLite Spark
Тип OLAP (аналітика) OLTP (транзакції) Розподілений
Зберігання Колоночне Рядкове Колоночне
Інфраструктура Вбудована Вбудована Кластер
Дані До ~500 ГБ До ~1 ГБ Петабайти
Швидкість Дуже швидко Повільно для аналітики Швидко, але overhead
Складність Мінімальна Мінімальна Висока
Ціна Безкоштовно Безкоштовно Дорога інфраструктура

DuckDB ідеально підходить для задач, де дані занадто великі для Pandas, але занадто малі для Spark.

Коли використовувати DuckDB?

flowchart LR
    A[Мої дані] --> B{Поміщаються в RAM?}
    B -->|Так, < 1 ГБ| C[Pandas / Polars]
    B -->|Ні або повільно| D{Потрібен кластер?}
    D -->|Ні, < 500 ГБ| E["🦆 DuckDB"]
    D -->|Так, > 1 ТБ| F[Spark / BigQuery]
    style E fill:#f9b928,stroke:#333,stroke-width:3px

Встановлення та налаштування

Встановлення

За допомогою uv:

uv add duckdb polars pyarrow pandas matplotlib

Або pip:

pip install duckdb polars pyarrow pandas matplotlib

Перший запит

import duckdb

result = duckdb.sql("SELECT 'Привіт, DuckDB!' AS greeting")
result.show()
┌─────────────────┐
│    greeting     │
│     varchar     │
├─────────────────┤
│ Привіт, DuckDB! │
└─────────────────┘

DuckDB працює без створення з’єднання — за замовчуванням використовує in-memory базу.

З’єднання з базою даних

# In-memory база (за замовчуванням)
con = duckdb.connect()

# Або з файлом на диску (персистентна)
# con = duckdb.connect("my_database.duckdb")

con.sql("SELECT 42 AS answer, current_date AS today").show()
┌────────┬────────────┐
│ answer │   today    │
│ int32  │    date    │
├────────┼────────────┤
│     42 │ 2026-03-24 │
└────────┴────────────┘

Примітка

Персистентна база зберігає дані між сесіями і дозволяє працювати з даними, що перевищують обсяг RAM.

Основи SQL в DuckDB

Friendly SQL

DuckDB підтримує “дружній SQL” — розширення стандартного SQL для зручності:

Стандартний SQL:

SELECT
    passenger_count,
    AVG(tip_amount) AS mean_tip
FROM nyc_taxi
GROUP BY passenger_count
ORDER BY passenger_count

DuckDB Friendly SQL:

FROM nyc_taxi
SELECT
    passenger_count,
    AVG(tip_amount) AS mean_tip
GROUP BY ALL
ORDER BY ALL
  • FROM може стояти першим
  • GROUP BY ALL — автоматично групує за не-агрегатними стовпцями
  • ORDER BY ALL — сортує за всіма стовпцями результату

Створення таблиці

con.sql("""
    CREATE OR REPLACE TABLE employees AS
    SELECT * FROM (VALUES
        ('Олена', 'Data Science', 45000, 28),
        ('Андрій', 'Engineering', 52000, 32),
        ('Марія', 'Data Science', 48000, 25),
        ('Петро', 'Engineering', 55000, 35),
        ('Ірина', 'Marketing', 42000, 29),
        ('Сергій', 'Data Science', 51000, 31),
        ('Наталія', 'Marketing', 44000, 27),
        ('Олексій', 'Engineering', 58000, 38)
    ) AS t(name, department, salary, age)
""")

con.sql("FROM employees").show()
┌─────────┬──────────────┬────────┬───────┐
│  name   │  department  │ salary │  age  │
│ varchar │   varchar    │ int32  │ int32 │
├─────────┼──────────────┼────────┼───────┤
│ Олена   │ Data Science │  45000 │    28 │
│ Андрій  │ Engineering  │  52000 │    32 │
│ Марія   │ Data Science │  48000 │    25 │
│ Петро   │ Engineering  │  55000 │    35 │
│ Ірина   │ Marketing    │  42000 │    29 │
│ Сергій  │ Data Science │  51000 │    31 │
│ Наталія │ Marketing    │  44000 │    27 │
│ Олексій │ Engineering  │  58000 │    38 │
└─────────┴──────────────┴────────┴───────┘

SELECT та фільтрація

con.sql("""
    FROM employees
    SELECT name, department, salary
    WHERE salary > 48000
    ORDER BY salary DESC
""").show()
┌─────────┬──────────────┬────────┐
│  name   │  department  │ salary │
│ varchar │   varchar    │ int32  │
├─────────┼──────────────┼────────┤
│ Олексій │ Engineering  │  58000 │
│ Петро   │ Engineering  │  55000 │
│ Андрій  │ Engineering  │  52000 │
│ Сергій  │ Data Science │  51000 │
└─────────┴──────────────┴────────┘

Агрегація

con.sql("""
    FROM employees
    SELECT
        department,
        COUNT(*) AS n_employees,
        ROUND(AVG(salary)) AS avg_salary,
        MIN(salary) AS min_salary,
        MAX(salary) AS max_salary
    GROUP BY ALL
    ORDER BY avg_salary DESC
""").show()
┌──────────────┬─────────────┬────────────┬────────────┬────────────┐
│  department  │ n_employees │ avg_salary │ min_salary │ max_salary │
│   varchar    │    int64    │   double   │   int32    │   int32    │
├──────────────┼─────────────┼────────────┼────────────┼────────────┤
│ Engineering  │           3 │    55000.0 │      52000 │      58000 │
│ Data Science │           3 │    48000.0 │      45000 │      51000 │
│ Marketing    │           2 │    43000.0 │      42000 │      44000 │
└──────────────┴─────────────┴────────────┴────────────┴────────────┘

HAVING та вкладені запити

con.sql("""
    FROM employees
    SELECT
        department,
        ROUND(AVG(salary)) AS avg_salary,
        COUNT(*) AS n
    GROUP BY ALL
    HAVING COUNT(*) >= 2
    ORDER BY ALL DESC
""").show()
┌──────────────┬────────────┬───────┐
│  department  │ avg_salary │   n   │
│   varchar    │   double   │ int64 │
├──────────────┼────────────┼───────┤
│ Marketing    │    43000.0 │     2 │
│ Engineering  │    55000.0 │     3 │
│ Data Science │    48000.0 │     3 │
└──────────────┴────────────┴───────┘

Віконні функції

con.sql("""
    FROM employees
    SELECT
        name,
        department,
        salary,
        RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept,
        salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
    ORDER BY department, rank_in_dept
""").show()
┌─────────┬──────────────┬────────┬──────────────┬───────────────┐
│  name   │  department  │ salary │ rank_in_dept │ diff_from_avg │
│ varchar │   varchar    │ int32  │    int64     │    double     │
├─────────┼──────────────┼────────┼──────────────┼───────────────┤
│ Сергій  │ Data Science │  51000 │            1 │        3000.0 │
│ Марія   │ Data Science │  48000 │            2 │           0.0 │
│ Олена   │ Data Science │  45000 │            3 │       -3000.0 │
│ Олексій │ Engineering  │  58000 │            1 │        3000.0 │
│ Петро   │ Engineering  │  55000 │            2 │           0.0 │
│ Андрій  │ Engineering  │  52000 │            3 │       -3000.0 │
│ Наталія │ Marketing    │  44000 │            1 │        1000.0 │
│ Ірина   │ Marketing    │  42000 │            2 │       -1000.0 │
└─────────┴──────────────┴────────┴──────────────┴───────────────┘

Порада

Віконні функції дозволяють обчислювати агрегати без згортання рядків — кожен рядок зберігається.

NTILE — розбиття на квантилі

con.sql("""
    FROM employees
    SELECT
        name,
        salary,
        NTILE(3) OVER (ORDER BY salary) AS salary_tercile
    ORDER BY salary
""").show()
┌─────────┬────────┬────────────────┐
│  name   │ salary │ salary_tercile │
│ varchar │ int32  │     int64      │
├─────────┼────────┼────────────────┤
│ Ірина   │  42000 │              1 │
│ Наталія │  44000 │              1 │
│ Олена   │  45000 │              1 │
│ Марія   │  48000 │              2 │
│ Сергій  │  51000 │              2 │
│ Андрій  │  52000 │              2 │
│ Петро   │  55000 │              3 │
│ Олексій │  58000 │              3 │
└─────────┴────────┴────────────────┘

Common Table Expressions (CTE)

CTE — це тимчасові табличні вирази, які існують лише в межах одного запиту.

con.sql("""
    WITH dept_stats AS (
        FROM employees
        SELECT
            department,
            AVG(salary) AS avg_salary
        GROUP BY ALL
    )
    FROM employees e
    JOIN dept_stats d ON e.department = d.department
    SELECT
        e.name,
        e.department,
        e.salary,
        ROUND(d.avg_salary) AS dept_avg,
        e.salary - ROUND(d.avg_salary) AS delta
    ORDER BY delta DESC
""").show()
┌─────────┬──────────────┬────────┬──────────┬─────────┐
│  name   │  department  │ salary │ dept_avg │  delta  │
│ varchar │   varchar    │ int32  │  double  │ double  │
├─────────┼──────────────┼────────┼──────────┼─────────┤
│ Сергій  │ Data Science │  51000 │  48000.0 │  3000.0 │
│ Олексій │ Engineering  │  58000 │  55000.0 │  3000.0 │
│ Наталія │ Marketing    │  44000 │  43000.0 │  1000.0 │
│ Марія   │ Data Science │  48000 │  48000.0 │     0.0 │
│ Петро   │ Engineering  │  55000 │  55000.0 │     0.0 │
│ Ірина   │ Marketing    │  42000 │  43000.0 │ -1000.0 │
│ Олена   │ Data Science │  45000 │  48000.0 │ -3000.0 │
│ Андрій  │ Engineering  │  52000 │  55000.0 │ -3000.0 │
└─────────┴──────────────┴────────┴──────────┴─────────┘

Робота з файлами

Читання CSV

DuckDB може напряму читати з файлів — без попереднього завантаження в пам’ять:

# Створимо тестовий CSV
import polars as pl

test_data = pl.DataFrame({
    "city": ["Київ", "Львів", "Одеса", "Харків", "Дніпро"] * 200,
    "temperature": np.random.normal(15, 10, 1000).round(1),
    "humidity": np.random.uniform(30, 95, 1000).round(1),
    "date": pl.date_range(pl.date(2024, 1, 1), pl.date(2026, 9, 26), eager=True)[:1000],
})
test_data.write_csv("weather.csv")

con.sql("""
    FROM 'weather.csv'
    SELECT city, AVG(temperature) AS avg_temp, AVG(humidity) AS avg_humidity
    GROUP BY ALL
    ORDER BY avg_temp DESC
""").show()
┌─────────┬────────────────────┬────────────────────┐
│  city   │      avg_temp      │    avg_humidity    │
│ varchar │       double       │       double       │
├─────────┼────────────────────┼────────────────────┤
│ Львів   │  16.17699999999999 │ 63.385499999999965 │
│ Одеса   │ 15.175500000000001 │  63.49400000000004 │
│ Дніпро  │ 14.936999999999996 │  61.47349999999999 │
│ Харків  │ 14.892000000000007 │            64.5985 │
│ Київ    │ 14.209499999999993 │             62.646 │
└─────────┴────────────────────┴────────────────────┘

Читання Parquet

Parquet — оптимальний формат для аналітичних запитів:

# Зберігаємо в Parquet
test_data.write_parquet("weather.parquet")

con.sql("""
    FROM 'weather.parquet'
    SELECT
        city,
        COUNT(*) AS n_records,
        ROUND(AVG(temperature), 1) AS avg_temp,
        ROUND(MIN(temperature), 1) AS min_temp,
        ROUND(MAX(temperature), 1) AS max_temp
    GROUP BY ALL
    ORDER BY avg_temp DESC
""").show()
┌─────────┬───────────┬──────────┬──────────┬──────────┐
│  city   │ n_records │ avg_temp │ min_temp │ max_temp │
│ varchar │   int64   │  double  │  double  │  double  │
├─────────┼───────────┼──────────┼──────────┼──────────┤
│ Львів   │       200 │     16.2 │    -11.7 │     47.0 │
│ Одеса   │       200 │     15.2 │    -13.9 │     39.5 │
│ Харків  │       200 │     14.9 │    -13.1 │     44.5 │
│ Дніпро  │       200 │     14.9 │    -10.3 │     37.1 │
│ Київ    │       200 │     14.2 │    -18.6 │     38.9 │
└─────────┴───────────┴──────────┴──────────┴──────────┘

Parquet vs CSV: чому Parquet швидший?

flowchart LR
    subgraph CSV["CSV (рядкове)"]
        direction TB
        R1["Київ, 15.2, 60.1, 2024-01-01"]
        R2["Львів, 10.3, 72.5, 2024-01-01"]
        R3["Одеса, 18.7, 55.3, 2024-01-01"]
    end
    subgraph Parquet["Parquet (колоночне)"]
        direction TB
        C1["city: [Київ, Львів, Одеса, ...]"]
        C2["temp: [15.2, 10.3, 18.7, ...]"]
        C3["humidity: [60.1, 72.5, 55.3, ...]"]
    end
    style Parquet fill:#d9f6ec,stroke:#28a87d
    style CSV fill:#fde8e8,stroke:#c10000

CSV Parquet
Стиснення Немає 2-10x менше
Projection pushdown Читає все Читає лише потрібні стовпці
Predicate pushdown Читає все Пропускає непотрібні row groups
Типи даних Текст Нативні типи
Метадані Немає Min/Max статистики

Читання JSON

import json

# Створимо тестовий JSON
records = [
    {"name": "DuckDB", "type": "OLAP", "year": 2019},
    {"name": "SQLite", "type": "OLTP", "year": 2000},
    {"name": "PostgreSQL", "type": "OLTP", "year": 1996},
    {"name": "ClickHouse", "type": "OLAP", "year": 2016},
]
with open("databases.json", "w") as f:
    json.dump(records, f)

con.sql("FROM 'databases.json'").show()
┌────────────┬─────────┬───────┐
│    name    │  type   │ year  │
│  varchar   │ varchar │ int64 │
├────────────┼─────────┼───────┤
│ DuckDB     │ OLAP    │  2019 │
│ SQLite     │ OLTP    │  2000 │
│ PostgreSQL │ OLTP    │  1996 │
│ ClickHouse │ OLAP    │  2016 │
└────────────┴─────────┴───────┘

Glob-патерни та HTTP

DuckDB може читати файли за шаблоном та з Інтернету:

# Декілька файлів за шаблоном
con.sql("FROM 'data/year=*/month=*/*.parquet'")

# З Інтернету (HTTP/S3)
con.sql("FROM 'https://example.com/data.parquet'")

# З Amazon S3
con.sql("FROM 's3://bucket/path/data.parquet'")

Порада

DuckDB автоматично розпізнає Hive-style partitioning (year=2024/month=01/data.parquet) і використовує його для оптимізації запитів.

DuckDB + Python

DuckDB та Pandas

DuckDB може напряму запитувати Pandas DataFrame як таблицю:

import pandas as pd

sales_pd = pd.DataFrame({
    "product": ["A", "B", "A", "C", "B", "A", "C", "B"] * 125,
    "region": ["Північ", "Південь", "Схід", "Захід"] * 250,
    "revenue": np.random.exponential(1000, 1000).round(2),
    "quantity": np.random.randint(1, 50, 1000),
})

# DuckDB напряму запитує pandas DataFrame!
con.sql("""
    FROM sales_pd
    SELECT
        product,
        region,
        ROUND(SUM(revenue), 2) AS total_revenue,
        SUM(quantity) AS total_qty
    GROUP BY ALL
    ORDER BY total_revenue DESC
    LIMIT 8
""").show()
┌─────────┬─────────┬───────────────┬───────────┐
│ product │ region  │ total_revenue │ total_qty │
│ varchar │ varchar │    double     │  int128   │
├─────────┼─────────┼───────────────┼───────────┤
│ B       │ Північ  │     145173.53 │      3136 │
│ B       │ Захід   │      140923.1 │      2905 │
│ C       │ Схід    │     137328.16 │      2795 │
│ A       │ Схід    │     123997.95 │      3299 │
│ B       │ Південь │     122091.65 │      3257 │
│ A       │ Південь │     117944.41 │      3439 │
│ C       │ Захід   │     116722.63 │      3120 │
│ A       │ Північ  │     113174.61 │      3228 │
└─────────┴─────────┴───────────────┴───────────┘

DuckDB та Polars

Те саме працює з Polars DataFrame:

sales_pl = pl.DataFrame({
    "product": ["A", "B", "A", "C", "B", "A", "C", "B"] * 125,
    "region": ["Північ", "Південь", "Схід", "Захід"] * 250,
    "revenue": np.random.exponential(1000, 1000).round(2),
    "quantity": np.random.randint(1, 50, 1000),
})

# DuckDB напряму запитує Polars DataFrame!
con.sql("""
    FROM sales_pl
    SELECT
        product,
        ROUND(AVG(revenue), 2) AS avg_revenue,
        ROUND(STDDEV(revenue), 2) AS std_revenue,
        COUNT(*) AS n
    GROUP BY ALL
    ORDER BY avg_revenue DESC
""").show()
┌─────────┬─────────────┬─────────────┬───────┐
│ product │ avg_revenue │ std_revenue │   n   │
│ varchar │   double    │   double    │ int64 │
├─────────┼─────────────┼─────────────┼───────┤
│ C       │     1048.41 │     1048.14 │   250 │
│ A       │      994.54 │      1010.3 │   375 │
│ B       │      937.95 │      954.21 │   375 │
└─────────┴─────────────┴─────────────┴───────┘

Конвертація результатів

query = """
    FROM sales_pl
    SELECT product, SUM(revenue) AS total
    GROUP BY ALL
"""

# До Polars DataFrame
result_pl = con.sql(query).pl()
print("Polars:", type(result_pl))
print(result_pl)
Polars: <class 'polars.dataframe.frame.DataFrame'>
shape: (3, 2)
┌─────────┬───────────┐
│ product ┆ total     │
│ ---     ┆ ---       │
│ str     ┆ f64       │
╞═════════╪═══════════╡
│ A       ┆ 372953.72 │
│ C       ┆ 262103.6  │
│ B       ┆ 351732.03 │
└─────────┴───────────┘
# До Pandas DataFrame
result_pd = con.sql(query).df()
print("\nPandas:", type(result_pd))
print(result_pd)

Pandas: <class 'pandas.DataFrame'>
  product      total
0       C  262103.60
1       A  372953.72
2       B  351732.03

Конвертація результатів (продовження)

# До PyArrow Table
result_arrow = con.sql(query).arrow()
print("Arrow:", type(result_arrow))
print(result_arrow)
Arrow: <class 'pyarrow.lib.RecordBatchReader'>
<pyarrow.lib.RecordBatchReader object at 0x0000022A0BA3EDF0>
# До NumPy масиву
result_np = con.sql(query).fetchnumpy()
print("\nNumPy:", type(result_np))
print(result_np)

NumPy: <class 'dict'>
{'product': array(['B', 'C', 'A'], dtype=object), 'total': array([351732.03, 262103.6 , 372953.72])}

Аналіз даних: практичний приклад

Генерація даних

Створимо датасет з інформацією про замовлення інтернет-магазину:

np.random.seed(73)
n = 500_000

orders = pl.DataFrame({
    "order_id": range(1, n + 1),
    "customer_id": np.random.randint(1, 10001, n),
    "product_category": np.random.choice(
        ["Електроніка", "Одяг", "Книги", "Продукти", "Спорт"], n
    ),
    "order_date": pl.date_range(
        pl.date(2022, 1, 1), pl.date(2024, 12, 31), eager=True
    ).sample(n, with_replacement=True).sort(),
    "amount": np.round(np.random.exponential(500, n), 2),
    "city": np.random.choice(
        ["Київ", "Львів", "Одеса", "Харків", "Дніпро",
         "Запоріжжя", "Вінниця", "Полтава"], n
    ),
})

orders.write_parquet("orders.parquet")
con.sql("FROM 'orders.parquet' LIMIT 5").show()
┌──────────┬─────────────┬──────────────────┬────────────┬────────┬─────────┐
│ order_id │ customer_id │ product_category │ order_date │ amount │  city   │
│  int64   │    int32    │     varchar      │    date    │ double │ varchar │
├──────────┼─────────────┼──────────────────┼────────────┼────────┼─────────┤
│        1 │        5015 │ Одяг             │ 2022-01-01 │ 699.09 │ Харків  │
│        2 │        8339 │ Спорт            │ 2022-01-01 │  329.4 │ Вінниця │
│        3 │        4015 │ Продукти         │ 2022-01-01 │ 1894.0 │ Київ    │
│        4 │        8587 │ Спорт            │ 2022-01-01 │ 134.88 │ Вінниця │
│        5 │        4420 │ Книги            │ 2022-01-01 │ 271.64 │ Одеса   │
└──────────┴─────────────┴──────────────────┴────────────┴────────┴─────────┘

Загальна статистика

con.sql("""
    FROM 'orders.parquet'
    SELECT
        COUNT(*) AS total_orders,
        COUNT(DISTINCT customer_id) AS unique_customers,
        ROUND(SUM(amount), 2) AS total_revenue,
        ROUND(AVG(amount), 2) AS avg_order,
        MIN(order_date) AS first_date,
        MAX(order_date) AS last_date
""").show()
┌──────────────┬──────────────────┬───────────────┬───────────┬────────────┬────────────┐
│ total_orders │ unique_customers │ total_revenue │ avg_order │ first_date │ last_date  │
│    int64     │      int64       │    double     │  double   │    date    │    date    │
├──────────────┼──────────────────┼───────────────┼───────────┼────────────┼────────────┤
│       500000 │            10000 │  249456496.43 │    498.91 │ 2022-01-01 │ 2024-12-31 │
└──────────────┴──────────────────┴───────────────┴───────────┴────────────┴────────────┘

Топ категорій за виручкою

cat_stats = con.sql("""
    FROM 'orders.parquet'
    SELECT
        product_category,
        COUNT(*) AS orders,
        ROUND(SUM(amount)) AS revenue,
        ROUND(AVG(amount), 2) AS avg_order
    GROUP BY ALL
    ORDER BY revenue DESC
""").pl()

cat_stats
shape: (5, 4)
product_category orders revenue avg_order
str i64 f64 f64
"Книги" 100247 5.0079629e7 499.56
"Одяг" 100454 5.0018952e7 497.93
"Електроніка" 99690 4.9989381e7 501.45
"Продукти" 99669 4.9834368e7 500.0
"Спорт" 99940 4.9534167e7 495.64

Візуалізація: Виручка за категоріями

fig, ax = plt.subplots(figsize=(8, 5))
colors = [red_pink, turquoise, orange, purple, green]

bars = ax.barh(
    cat_stats["product_category"].to_list(),
    cat_stats["revenue"].to_list(),
    color=colors
)
ax.set_xlabel("Виручка, грн")
ax.set_title("Виручка за категоріями")
ax.xaxis.set_major_formatter(
    mticker.FuncFormatter(lambda x, p: f"{x/1e6:.1f}M")
)
ax.invert_yaxis()
plt.tight_layout()
plt.show()

Місячна динаміка

monthly = con.sql("""
    FROM 'orders.parquet'
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        COUNT(*) AS orders,
        ROUND(SUM(amount)) AS revenue
    GROUP BY ALL
    ORDER BY month
""").pl()

monthly.head()
shape: (5, 3)
month orders revenue
datetime[μs] i64 f64
2022-01-01 00:00:00 14217 7.106615e6
2022-02-01 00:00:00 12700 6.341328e6
2022-03-01 00:00:00 14074 7.201531e6
2022-04-01 00:00:00 13714 6.83609e6
2022-05-01 00:00:00 14062 6.974282e6

Візуалізація: Місячна динаміка

fig, ax1 = plt.subplots(figsize=(10, 5))

months = monthly["month"].to_list()
revenue = monthly["revenue"].to_list()
orders_count = monthly["orders"].to_list()

ax1.fill_between(months, revenue, alpha=0.3, color=turquoise)
ax1.plot(months, revenue, color=turquoise, linewidth=2, label="Виручка")
ax1.set_ylabel("Виручка, грн", color=turquoise)
ax1.yaxis.set_major_formatter(
    mticker.FuncFormatter(lambda x, p: f"{x/1e6:.1f}M")
)

ax2 = ax1.twinx()
ax2.plot(months, orders_count, color=red_pink, linewidth=2,
         linestyle="--", label="Замовлення")
ax2.set_ylabel("Кількість замовлень", color=red_pink)

ax1.set_title("Місячна динаміка")
fig.legend(loc="upper left", bbox_to_anchor=(0.12, 0.95))
plt.tight_layout()
plt.show()

Когортний аналіз

Когортний аналіз — це потужний інструмент для розуміння поведінки користувачів. Він дозволяє відстежувати, як групи користувачів (когорти), що прийшли в різний час, поводяться протягом свого життєвого циклу.

cohort = con.sql("""
    WITH first_purchase AS (
        FROM 'orders.parquet'
        SELECT
            customer_id,
            DATE_TRUNC('quarter', MIN(order_date)) AS cohort
        GROUP BY ALL
    )
    FROM 'orders.parquet' o
    JOIN first_purchase f ON o.customer_id = f.customer_id
    SELECT
        f.cohort,
        DATE_TRUNC('quarter', o.order_date) AS order_quarter,
        COUNT(DISTINCT o.customer_id) AS active_customers
    GROUP BY ALL
    ORDER BY cohort, order_quarter
""").pl()

cohort.head(8)
shape: (8, 3)
cohort order_quarter active_customers
datetime[μs] datetime[μs] i64
2022-01-01 00:00:00 2022-01-01 00:00:00 9845
2022-01-01 00:00:00 2022-04-01 00:00:00 9692
2022-01-01 00:00:00 2022-07-01 00:00:00 9695
2022-01-01 00:00:00 2022-10-01 00:00:00 9680
2022-01-01 00:00:00 2023-01-01 00:00:00 9689
2022-01-01 00:00:00 2023-04-01 00:00:00 9683
2022-01-01 00:00:00 2023-07-01 00:00:00 9713
2022-01-01 00:00:00 2023-10-01 00:00:00 9692

Віконні функції: накопичувальна сума

running = con.sql("""
    WITH monthly AS (
        FROM 'orders.parquet'
        SELECT
            DATE_TRUNC('month', order_date) AS month,
            ROUND(SUM(amount)) AS monthly_revenue
        GROUP BY ALL
    )
    FROM monthly
    SELECT
        month,
        monthly_revenue,
        ROUND(SUM(monthly_revenue) OVER (ORDER BY month)) AS cumulative_revenue
    ORDER BY month
""").pl()

running.tail()
shape: (5, 3)
month monthly_revenue cumulative_revenue
datetime[μs] f64 f64
2024-08-01 00:00:00 6.99839e6 2.21797944e8
2024-09-01 00:00:00 6.834567e6 2.28632511e8
2024-10-01 00:00:00 7.057067e6 2.35689578e8
2024-11-01 00:00:00 6.791554e6 2.42481132e8
2024-12-01 00:00:00 6.975364e6 2.49456496e8

Візуалізація: Накопичувальна виручка

fig, ax = plt.subplots(figsize=(10, 5))
months = running["month"].to_list()
cumulative = running["cumulative_revenue"].to_list()

ax.fill_between(months, cumulative, alpha=0.3, color=purple)
ax.plot(months, cumulative, color=purple, linewidth=2)
ax.set_ylabel("Накопичувальна виручка, грн")
ax.set_title("Накопичувальна виручка")
ax.yaxis.set_major_formatter(
    mticker.FuncFormatter(lambda x, p: f"{x/1e9:.2f}B")
)
plt.tight_layout()
plt.show()

Топ клієнтів

top_customers = con.sql("""
    FROM 'orders.parquet'
    SELECT
        customer_id,
        COUNT(*) AS orders,
        ROUND(SUM(amount), 2) AS total_spent,
        ROUND(AVG(amount), 2) AS avg_order,
        MIN(order_date) AS first_order,
        MAX(order_date) AS last_order
    GROUP BY ALL
    ORDER BY total_spent DESC
    LIMIT 10
""")

top_customers.show()
┌─────────────┬────────┬─────────────┬───────────┬─────────────┬────────────┐
│ customer_id │ orders │ total_spent │ avg_order │ first_order │ last_order │
│    int32    │ int64  │   double    │  double   │    date     │    date    │
├─────────────┼────────┼─────────────┼───────────┼─────────────┼────────────┤
│        1113 │     63 │    45540.18 │    722.86 │ 2022-01-25  │ 2024-12-10 │
│        4078 │     64 │    44317.93 │    692.47 │ 2022-02-18  │ 2024-11-24 │
│        4073 │     72 │    44290.73 │    615.15 │ 2022-02-21  │ 2024-12-08 │
│         565 │     65 │    43471.51 │    668.79 │ 2022-01-07  │ 2024-11-18 │
│        2202 │     67 │    43428.53 │    648.19 │ 2022-01-07  │ 2024-10-27 │
│        3162 │     69 │    43286.97 │    627.35 │ 2022-01-12  │ 2024-12-28 │
│        1010 │     73 │    43097.43 │    590.38 │ 2022-01-06  │ 2024-12-24 │
│        8587 │     73 │    43056.51 │    589.82 │ 2022-01-01  │ 2024-12-20 │
│        9774 │     66 │    42951.04 │    650.77 │ 2022-01-06  │ 2024-12-30 │
│        3314 │     56 │    42650.19 │    761.61 │ 2022-01-18  │ 2024-12-31 │
└─────────────┴────────┴─────────────┴───────────┴─────────────┴────────────┘
  10 rows                                                         6 columns

PIVOT / UNPIVOT

PIVOT — з довгого у широкий формат

con.sql("""
    WITH quarterly AS (
        FROM 'orders.parquet'
        SELECT
            product_category,
            QUARTER(order_date) AS quarter,
            ROUND(SUM(amount)) AS revenue
        WHERE YEAR(order_date) = 2024
        GROUP BY ALL
    )
    PIVOT quarterly
    ON quarter
    USING SUM(revenue)
    GROUP BY product_category
    ORDER BY product_category
""").show()
┌──────────────────┬───────────┬───────────┬───────────┬───────────┐
│ product_category │     1     │     2     │     3     │     4     │
│     varchar      │  double   │  double   │  double   │  double   │
├──────────────────┼───────────┼───────────┼───────────┼───────────┤
│ Електроніка      │ 4075066.0 │ 4145202.0 │ 4260372.0 │ 4157913.0 │
│ Книги            │ 4183344.0 │ 4108797.0 │ 4120961.0 │ 4185915.0 │
│ Одяг             │ 4200287.0 │ 4029928.0 │ 4209363.0 │ 4148832.0 │
│ Продукти         │ 4067066.0 │ 4228794.0 │ 4193956.0 │ 4197146.0 │
│ Спорт            │ 4173379.0 │ 4140737.0 │ 4147538.0 │ 4134179.0 │
└──────────────────┴───────────┴───────────┴───────────┴───────────┘

UNPIVOT — з широкого у довгий формат

con.sql("""
    WITH wide_data AS (
        SELECT 'Електроніка' AS category, 1000 AS q1, 1200 AS q2, 1100 AS q3, 1500 AS q4
        UNION ALL
        SELECT 'Одяг', 800, 900, 1000, 1300
    )
    UNPIVOT wide_data
    ON q1, q2, q3, q4
    INTO NAME quarter VALUE revenue
""").show()
┌─────────────┬─────────┬─────────┐
│  category   │ quarter │ revenue │
│   varchar   │ varchar │  int32  │
├─────────────┼─────────┼─────────┤
│ Електроніка │ q1      │    1000 │
│ Електроніка │ q2      │    1200 │
│ Електроніка │ q3      │    1100 │
│ Електроніка │ q4      │    1500 │
│ Одяг        │ q1      │     800 │
│ Одяг        │ q2      │     900 │
│ Одяг        │ q3      │    1000 │
│ Одяг        │ q4      │    1300 │
└─────────────┴─────────┴─────────┘

Примітка

PIVOT і UNPIVOT в DuckDB — нативні SQL-оператори, що працюють набагато швидше, ніж відповідні операції в Pandas.

Візуалізація: Heatmap виручки

heatmap_data = con.sql("""
    FROM 'orders.parquet'
    SELECT
        city,
        product_category,
        ROUND(SUM(amount)) AS revenue
    GROUP BY ALL
    ORDER BY city, product_category
""").pl()

pivot_df = heatmap_data.pivot(
    on="product_category",
    index="city",
    values="revenue"
).sort("city")

cities = pivot_df["city"].to_list()
categories = [c for c in pivot_df.columns if c != "city"]
values = pivot_df.select(categories).to_numpy()

fig, ax = plt.subplots(figsize=(9, 5))
im = ax.imshow(values, cmap="YlOrRd", aspect="auto")
ax.set_xticks(range(len(categories)))
ax.set_xticklabels(categories, rotation=45, ha="right")
ax.set_yticks(range(len(cities)))
ax.set_yticklabels(cities)
plt.colorbar(im, label="Виручка, грн")
ax.set_title("Виручка за містами та категоріями")
plt.tight_layout()
plt.show()

Продуктивність DuckDB

Lazy Evaluation

DuckDB оптимізує запити перед виконанням. Подивимось на план виконання:

con.sql("""
    EXPLAIN
    FROM 'orders.parquet'
    SELECT city, ROUND(AVG(amount), 2) AS avg_amount
    WHERE product_category = 'Електроніка'
    GROUP BY ALL
    ORDER BY avg_amount DESC
""").show()
┌───────────────┬────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│  explain_key  │                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   explain_value                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    │
│    varchar    │                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      varchar                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       │
├───────────────┼────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ physical_plan │ ┌───────────────────────────┐\n│          ORDER_BY         │\n│    ────────────────────   │\n│ round(avg(orders.amount), │\n│           2) DESC         │\n└─────────────┬─────────────┘\n┌─────────────┴─────────────┐\n│         PROJECTION        │\n│    ────────────────────   │\n│             #0            │\n│         avg_amount        │\n│                           │\n│        ~90,634 rows       │\n└─────────────┬─────────────┘\n┌─────────────┴─────────────┐\n│       HASH_GROUP_BY       │\n│    ────────────────────   │\n│         Groups: #0        │\n│    Aggregates: avg(#1)    │\n│                           │\n│        ~90,634 rows       │\n└─────────────┬─────────────┘\n┌─────────────┴─────────────┐\n│         PROJECTION        │\n│    ────────────────────   │\n│            city           │\n│           amount          │\n│                           │\n│       ~100,000 rows       │\n└─────────────┬─────────────┘\n┌─────────────┴─────────────┐\n│        PARQUET_SCAN       │\n│    ────────────────────   │\n│         Function:         │\n│        PARQUET_SCAN       │\n│                           │\n│        Projections:       │\n│            city           │\n│           amount          │\n│                           │\n│          Filters:         │\n│ product_category='Електрон│\n│            іка'           │\n│                           │\n│       ~100,000 rows       │\n└───────────────────────────┘\n │
└───────────────┴────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

Predicate Pushdown

flowchart TD
    subgraph "Без оптимізації"
        A1[Читання всіх рядків] --> B1[Фільтрація]
        B1 --> C1[Агрегація]
    end
    subgraph "З Predicate Pushdown"
        A2["Читання лише<br>category='Електроніка'"] --> C2[Агрегація]
    end
    style A2 fill:#d9f6ec,stroke:#28a87d
    style A1 fill:#fde8e8,stroke:#c10000

DuckDB не читає дані, які не потрібні:

  • Predicate pushdown — фільтри застосовуються до читання
  • Projection pushdown — читаються лише потрібні стовпці
  • Partition pruning — пропускаються цілі файли/партиції

Продуктивність: Pandas vs DuckDB

import time

# Pandas
start = time.time()
sales_pd_big = pd.DataFrame({
    "category": np.random.choice(["A", "B", "C", "D", "E"], 2_000_000),
    "value": np.random.exponential(100, 2_000_000),
    "region": np.random.choice(["N", "S", "E", "W"], 2_000_000),
})
pd_result = (
    sales_pd_big
    .groupby(["category", "region"])["value"]
    .agg(["mean", "sum", "count"])
)
pandas_time = time.time() - start

# DuckDB
start = time.time()
duck_result = con.sql("""
    FROM sales_pd_big
    SELECT category, region,
           AVG(value) AS mean, SUM(value) AS sum, COUNT(*) AS count
    GROUP BY ALL
""").df()
duckdb_time = time.time() - start

print(f"Pandas:  {pandas_time:.3f} с")
print(f"DuckDB:  {duckdb_time:.3f} с")
print(f"Прискорення: {pandas_time / duckdb_time:.1f}x")
Pandas:  0.320 с
DuckDB:  0.089 с
Прискорення: 3.6x

Out-of-Core обробка

DuckDB може працювати з даними, що перевищують обсяг RAM:

flowchart LR
    A["Файл 10 ГБ<br>(Parquet)"] --> B[DuckDB Engine]
    B --> C["Buffer Manager<br>(потокова обробка)"]
    C --> D["Результат<br>(малий)"]

    E["RAM: 8 ГБ"] -.-> C
    F["Disk: спілювання<br>тимчасових даних"] -.-> C

    style B fill:#f9b928,stroke:#333

  • Читання файлів потоково (streaming)
  • Проміжні дані спілюються на диск за потреби
  • Результат агрегації зазвичай поміщається в пам’ять

Поради з продуктивності

Формат даних:

  1. Використовуйте Parquet замість CSV
  2. Партиціонуйте великі датасети
  3. Використовуйте відповідні типи даних
  4. Зберігайте стиснені дані (zstd)

Запити:

  1. Вибирайте лише потрібні стовпці
  2. Фільтруйте якомога раніше
  3. Використовуйте EXPLAIN для аналізу
  4. Уникайте SELECT * на великих даних
-- ❌ Повільно
SELECT * FROM huge_table WHERE date > '2024-01-01'

-- ✅ Швидко
SELECT col1, col2 FROM huge_table WHERE date > '2024-01-01'

DuckDB + Ibis (Python API)

Що таке Ibis?

Ibis — це Python-фреймворк, що дозволяє писати аналітичні запити у Pythonic стилі, які автоматично транслюються в SQL для різних бекендів.

flowchart LR
    A["Ibis API<br>(Python)"] --> B{Бекенд}
    B --> C[DuckDB]
    B --> D[PostgreSQL]
    B --> E[BigQuery]
    B --> F[PySpark]
    B --> G[Polars]
    style A fill:#f9b928,stroke:#333

  • Один API — багато бекендів
  • Lazy evaluation за замовчуванням
  • Автоматична трансляція в SQL

Підключення через Ibis

import ibis
from ibis import _

ibis.options.interactive = True

con_ibis = ibis.duckdb.connect()

# Зчитування Parquet файлу
orders_ibis = con_ibis.read_parquet("orders.parquet")
orders_ibis.head()
┏━━━━━━━━━━┳━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━┓
┃ order_id ┃ customer_id ┃ product_category ┃ order_date ┃ amount  ┃ city    ┃
┡━━━━━━━━━━╇━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━┩
│ int64    │ int32       │ string           │ date       │ float64 │ string  │
├──────────┼─────────────┼──────────────────┼────────────┼─────────┼─────────┤
│        1 │        5015 │ Одяг             │ 2022-01-01 │  699.09 │ Харків  │
│        2 │        8339 │ Спорт            │ 2022-01-01 │  329.40 │ Вінниця │
│        3 │        4015 │ Продукти         │ 2022-01-01 │ 1894.00 │ Київ    │
│        4 │        8587 │ Спорт            │ 2022-01-01 │  134.88 │ Вінниця │
│        5 │        4420 │ Книги            │ 2022-01-01 │  271.64 │ Одеса   │
└──────────┴─────────────┴──────────────────┴────────────┴─────────┴─────────┘

Агрегація через Ibis

result = (
    orders_ibis
    .group_by("product_category")
    .agg(
        total_revenue=_.amount.sum(),
        avg_order=_.amount.mean().round(2),
        n_orders=_.count()
    )
    .order_by(ibis.desc("total_revenue"))
)
result
┏━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━━━┓
┃ product_category ┃ total_revenue ┃ avg_order ┃ n_orders ┃
┡━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━━━┩
│ string           │ float64       │ float64   │ int64    │
├──────────────────┼───────────────┼───────────┼──────────┤
│ Книги            │  5.007963e+07 │    499.56 │   100247 │
│ Одяг             │  5.001895e+07 │    497.93 │   100454 │
│ Електроніка      │  4.998938e+07 │    501.45 │    99690 │
│ Продукти         │  4.983437e+07 │    500.00 │    99669 │
│ Спорт            │  4.953417e+07 │    495.64 │    99940 │
└──────────────────┴───────────────┴───────────┴──────────┘

Перегляд SQL

print(ibis.to_sql(result))
SELECT
  *
FROM (
  SELECT
    "t0"."product_category",
    SUM("t0"."amount") AS "total_revenue",
    CAST(ROUND(AVG("t0"."amount"), 2) AS DOUBLE) AS "avg_order",
    COUNT(*) AS "n_orders"
  FROM "ibis_read_parquet_p2tru4puijgcxecoeuvdnxl2pi" AS "t0"
  GROUP BY
    1
) AS "t1"
ORDER BY
  "t1"."total_revenue" DESC

Порада

ibis.to_sql() дозволяє побачити, який SQL генерує Ibis. Це корисно для навчання та дебагу.

Фільтрація та трансформації

(
    orders_ibis
    .filter(_.city.isin(["Київ", "Львів", "Одеса"]))
    .mutate(
        year=_.order_date.year(),
        quarter=_.order_date.quarter()
    )
    .group_by(["city", "year"])
    .agg(revenue=_.amount.sum().round())
    .order_by(["city", "year"])
    .head(12)
)
┏━━━━━━━━┳━━━━━━━┳━━━━━━━━━━┓
┃ city   ┃ year  ┃ revenue  ┃
┡━━━━━━━━╇━━━━━━━╇━━━━━━━━━━┩
│ string │ int32 │ int64    │
├────────┼───────┼──────────┤
│ Київ   │  2022 │ 10483928 │
│ Київ   │  2023 │ 10265533 │
│ Київ   │  2024 │ 10471177 │
│ Львів  │  2022 │ 10367258 │
│ Львів  │  2023 │ 10332669 │
│ Львів  │  2024 │ 10378558 │
│ Одеса  │  2022 │ 10335905 │
│ Одеса  │  2023 │ 10295239 │
│ Одеса  │  2024 │ 10479660 │
└────────┴───────┴──────────┘

Ibis vs SQL vs Pandas

SQL:

SELECT city,
       SUM(amount)
FROM orders
WHERE city = 'Київ'
GROUP BY city

Ibis:

(orders
 .filter(_.city == 'Київ')
 .group_by('city')
 .agg(total=_.amount.sum())
)

Pandas:

(orders
 .query("city == 'Київ'")
 .groupby('city')['amount']
 .sum()
)

Ibis поєднує зручність Pandas з продуктивністю DuckDB.

Конвертація результатів Ibis

query = (
    orders_ibis
    .group_by("city")
    .agg(total=_.amount.sum())
    .order_by(ibis.desc("total"))
)

# До Pandas
df_pd = query.to_pandas()
print("Pandas:", type(df_pd))

# До Polars
df_pl = query.to_polars()
print("Polars:", type(df_pl))
Pandas: <class 'pandas.DataFrame'>
Polars: <class 'polars.dataframe.frame.DataFrame'>

Розширені можливості

Об’єднання кількох файлів

# Створимо кілька файлів
for year in [2022, 2023, 2024]:
    subset = orders.filter(pl.col("order_date").dt.year() == year)
    subset.write_parquet(f"orders_{year}.parquet")

# DuckDB читає всі файли одним запитом
con.sql("""
    FROM 'orders_*.parquet'
    SELECT
        YEAR(order_date) AS year,
        COUNT(*) AS orders,
        ROUND(SUM(amount)) AS revenue
    GROUP BY ALL
    ORDER BY year
""").show()
┌───────┬────────┬────────────┐
│ year  │ orders │  revenue   │
│ int64 │ int64  │   double   │
├───────┼────────┼────────────┤
│  2022 │ 166485 │ 83056670.0 │
│  2023 │ 166821 │ 83291052.0 │
│  2024 │ 166694 │ 83108774.0 │
└───────┴────────┴────────────┘

CREATE TABLE AS SELECT (CTAS)

con.sql("""
    CREATE OR REPLACE TABLE city_summary AS
    FROM 'orders.parquet'
    SELECT
        city,
        product_category,
        COUNT(*) AS orders,
        ROUND(SUM(amount), 2) AS revenue,
        ROUND(AVG(amount), 2) AS avg_order
    GROUP BY ALL
""")

con.sql("FROM city_summary WHERE city = 'Київ' ORDER BY revenue DESC").show()
┌─────────┬──────────────────┬────────┬────────────┬───────────┐
│  city   │ product_category │ orders │  revenue   │ avg_order │
│ varchar │     varchar      │ int64  │   double   │  double   │
├─────────┼──────────────────┼────────┼────────────┼───────────┤
│ Київ    │ Книги            │  12772 │ 6343764.08 │    496.69 │
│ Київ    │ Одяг             │  12636 │ 6297329.25 │    498.36 │
│ Київ    │ Електроніка      │  12579 │ 6241425.16 │    496.18 │
│ Київ    │ Спорт            │  12571 │ 6213461.72 │    494.27 │
│ Київ    │ Продукти         │  12320 │ 6124656.79 │    497.13 │
└─────────┴──────────────────┴────────┴────────────┴───────────┘

Експорт результатів

# До Parquet
con.sql("""
    COPY (
        FROM 'orders.parquet'
        SELECT city, product_category, SUM(amount) AS total
        GROUP BY ALL
    ) TO 'summary.parquet' (FORMAT PARQUET)
""")

# До CSV
con.sql("""
    COPY (
        FROM 'orders.parquet'
        SELECT city, product_category, SUM(amount) AS total
        GROUP BY ALL
    ) TO 'summary.csv' (HEADER, DELIMITER ',')
""")

print("Експорт завершено!")
Експорт завершено!

SAMPLE — вибірка даних

# Випадкова вибірка 1%
con.sql("""
    WITH sampled AS (
        SELECT * FROM 'orders.parquet' USING SAMPLE 1 PERCENT (bernoulli)
    )
    FROM sampled
    SELECT product_category, COUNT(*) AS n
    GROUP BY ALL
    ORDER BY n DESC
""").show()
┌──────────────────┬───────┐
│ product_category │   n   │
│     varchar      │ int64 │
├──────────────────┼───────┤
│ Одяг             │  1044 │
│ Електроніка      │  1014 │
│ Продукти         │   980 │
│ Спорт            │   973 │
│ Книги            │   960 │
└──────────────────┴───────┘

Порада

USING SAMPLE корисний для швидкого дослідження великих датасетів та прототипування запитів.

QUALIFY — фільтрація після віконних функцій

con.sql("""
    FROM 'orders.parquet'
    SELECT
        city,
        product_category,
        ROUND(SUM(amount)) AS revenue,
        RANK() OVER (PARTITION BY city ORDER BY SUM(amount) DESC) AS rank
    GROUP BY city, product_category
    QUALIFY rank <= 2
    ORDER BY city, rank
""").show()
┌───────────┬──────────────────┬───────────┬───────┐
│   city    │ product_category │  revenue  │ rank  │
│  varchar  │     varchar      │  double   │ int64 │
├───────────┼──────────────────┼───────────┼───────┤
│ Вінниця   │ Одяг             │ 6340806.0 │     1 │
│ Вінниця   │ Електроніка      │ 6306532.0 │     2 │
│ Дніпро    │ Спорт            │ 6393544.0 │     1 │
│ Дніпро    │ Продукти         │ 6337657.0 │     2 │
│ Запоріжжя │ Одяг             │ 6323460.0 │     1 │
│ Запоріжжя │ Книги            │ 6290974.0 │     2 │
│ Київ      │ Книги            │ 6343764.0 │     1 │
│ Київ      │ Одяг             │ 6297329.0 │     2 │
│ Львів     │ Електроніка      │ 6363023.0 │     1 │
│ Львів     │ Книги            │ 6274669.0 │     2 │
│ Одеса     │ Електроніка      │ 6321642.0 │     1 │
│ Одеса     │ Продукти         │ 6285241.0 │     2 │
│ Полтава   │ Продукти         │ 6253350.0 │     1 │
│ Полтава   │ Електроніка      │ 6248764.0 │     2 │
│ Харків    │ Продукти         │ 6350397.0 │     1 │
│ Харків    │ Книги            │ 6289532.0 │     2 │
└───────────┴──────────────────┴───────────┴───────┘
  16 rows                                4 columns

QUALIFY — DuckDB-розширення, що замінює вкладений запит для фільтрації по віконних функціях.

ASOF JOIN — з’єднання за найближчим значенням

# Приклад: курс валют на найближчу дату
exchange_rates = con.sql("""
    CREATE OR REPLACE TABLE rates AS
    SELECT * FROM (VALUES
        ('2024-01-01'::DATE, 37.5),
        ('2024-04-01'::DATE, 38.2),
        ('2024-07-01'::DATE, 41.0),
        ('2024-10-01'::DATE, 41.5)
    ) AS t(rate_date, usd_uah)
""")

con.sql("""
    FROM 'orders.parquet' o
    ASOF JOIN rates r ON o.order_date >= r.rate_date
    SELECT
        o.order_date,
        o.amount,
        r.usd_uah,
        ROUND(o.amount / r.usd_uah, 2) AS amount_usd
    WHERE YEAR(o.order_date) = 2024
    ORDER BY o.order_date
    LIMIT 8
""").show()
┌────────────┬─────────┬──────────────┬────────────┐
│ order_date │ amount  │   usd_uah    │ amount_usd │
│    date    │ double  │ decimal(3,1) │   double   │
├────────────┼─────────┼──────────────┼────────────┤
│ 2024-01-01 │   12.59 │         37.5 │       0.34 │
│ 2024-01-01 │  612.27 │         37.5 │      16.33 │
│ 2024-01-01 │  198.83 │         37.5 │        5.3 │
│ 2024-01-01 │ 1130.99 │         37.5 │      30.16 │
│ 2024-01-01 │ 1085.78 │         37.5 │      28.95 │
│ 2024-01-01 │  266.54 │         37.5 │       7.11 │
│ 2024-01-01 │  251.66 │         37.5 │       6.71 │
│ 2024-01-01 │  287.03 │         37.5 │       7.65 │
└────────────┴─────────┴──────────────┴────────────┘

Практика: Аналітичний пайплайн

Повний пайплайн

flowchart LR
    A["📁 Raw Data<br>(CSV / JSON)"] --> B["🦆 DuckDB<br>ETL"]
    B --> C["📊 Parquet<br>(чисті дані)"]
    C --> D["📈 Аналітика<br>(SQL / Ibis)"]
    D --> E["📉 Візуалізація<br>(matplotlib)"]
    style B fill:#f9b928,stroke:#333,stroke-width:2px

ETL з DuckDB

# Extract: Читаємо "сирі" дані
con.sql("""
    CREATE OR REPLACE TABLE raw_orders AS
    FROM 'orders.parquet'
""")

# Transform: Очищення та збагачення
con.sql("""
    CREATE OR REPLACE TABLE clean_orders AS
    FROM raw_orders
    SELECT
        *,
        YEAR(order_date) AS year,
        MONTH(order_date) AS month,
        DAYOFWEEK(order_date) AS day_of_week,
        CASE
            WHEN amount < 100 THEN 'small'
            WHEN amount < 1000 THEN 'medium'
            ELSE 'large'
        END AS order_size
""")

# Load: Зберігаємо результат
con.sql("COPY clean_orders TO 'clean_orders.parquet' (FORMAT PARQUET)")
print("ETL завершено!")
ETL завершено!

Аналітика на чистих даних

con.sql("""
    FROM 'clean_orders.parquet'
    SELECT
        order_size,
        COUNT(*) AS orders,
        ROUND(AVG(amount), 2) AS avg_amount,
        ROUND(SUM(amount)) AS total_revenue
    GROUP BY ALL
    ORDER BY total_revenue DESC
""").show()
┌────────────┬────────┬────────────┬───────────────┐
│ order_size │ orders │ avg_amount │ total_revenue │
│  varchar   │ int64  │   double   │    double     │
├────────────┼────────┼────────────┼───────────────┤
│ medium     │ 341497 │     421.52 │   143946676.0 │
│ large      │  67474 │     1498.5 │   101109791.0 │
│ small      │  91029 │      48.34 │     4400030.0 │
└────────────┴────────┴────────────┴───────────────┘

Візуалізація: День тижня

dow_data = con.sql("""
    FROM 'clean_orders.parquet'
    SELECT
        day_of_week,
        COUNT(*) AS orders,
        ROUND(AVG(amount), 2) AS avg_amount
    GROUP BY ALL
    ORDER BY day_of_week
""").pl()

days_ua = ["Пн", "Вт", "Ср", "Чт", "Пт", "Сб", "Нд"]

fig, ax1 = plt.subplots(figsize=(8, 5))
x = range(len(days_ua))

ax1.bar(x, dow_data["orders"].to_list(), color=turquoise, alpha=0.7, label="Замовлення")
ax1.set_ylabel("Кількість замовлень", color=turquoise)
ax1.set_xticks(x)
ax1.set_xticklabels(days_ua)

ax2 = ax1.twinx()
ax2.plot(x, dow_data["avg_amount"].to_list(), color=red_pink,
         linewidth=2, marker="o", label="Сер. чек")
ax2.set_ylabel("Середній чек, грн", color=red_pink)

ax1.set_title("Замовлення за днями тижня")
fig.legend(loc="upper right", bbox_to_anchor=(0.88, 0.95))
plt.tight_layout()
plt.show()

Візуалізація: Розподіл замовлень

hist_data = con.sql("""
    FROM 'orders.parquet'
    SELECT amount
    WHERE amount < 3000
""").pl()

fig, ax = plt.subplots(figsize=(8, 5))
ax.hist(
    hist_data["amount"].to_list(),
    bins=50, color=purple, alpha=0.7, edgecolor="white"
)
ax.axvline(
    hist_data["amount"].mean(),
    color=red_pink, linestyle="--", linewidth=2,
    label=f"Середнє: {hist_data['amount'].mean():.0f} грн"
)
ax.set_xlabel("Сума замовлення, грн")
ax.set_ylabel("Кількість")
ax.set_title("Розподіл сум замовлень")
ax.legend()
plt.tight_layout()
plt.show()

Порівняння з іншими інструментами

Екосистема інструментів

flowchart TD
    subgraph "Малі дані (< 1 ГБ)"
        A[Pandas]
        B[Polars]
    end
    subgraph "Середні дані (1-500 ГБ)"
        C["🦆 DuckDB"]
        D[Polars]
    end
    subgraph "Великі дані (> 500 ГБ)"
        E[Spark]
        F[BigQuery]
        G[Snowflake]
    end
    subgraph "API шари"
        H[Ibis]
    end
    H --> A
    H --> C
    H --> E
    H --> F
    style C fill:#f9b928,stroke:#333,stroke-width:3px

DuckDB vs Polars

DuckDB Polars
Мова SQL Python API
Тип Вбудована СУБД DataFrame бібліотека
Персистентність Так (файл .duckdb) Ні (in-memory)
SQL Нативний Через DuckDB / SQLContext
Сканування файлів Parquet, CSV, JSON Parquet, CSV, JSON
Lazy Завжди scan_* / lazy()
Швидкість Дуже швидко Дуже швидко
Кращий для SQL-аналітики, ETL DataFrame-трансформацій

DuckDB і Polars — комплементарні інструменти, що чудово працюють разом!

Коли що використовувати?

Задача Найкращий інструмент
Ad-hoc SQL запити до файлів DuckDB
DataFrame-трансформації в Python Polars
ETL пайплайни DuckDB або Polars
Робота з >100 ГБ на ноутбуці DuckDB
Інтеграція з ML-фреймворками Polars / Pandas
Портативний аналіз (один файл) DuckDB (.duckdb файл)
Крос-платформний API Ibis + DuckDB бекенд

Підсумок

Що ми вивчили

  1. Що таке DuckDB і коли його використовувати
  2. Архітектура та ключові переваги
  3. Основи SQL в DuckDB
  4. “Friendly SQL” розширення
  5. Робота з файлами (CSV, Parquet, JSON)
  1. Інтеграція з Python (Pandas, Polars)
  2. Віконні функції та CTE
  3. PIVOT / UNPIVOT
  4. Ibis як Python API
  5. Продуктивність та оптимізація

Ресурси

Домашнє завдання

  1. Встановіть DuckDB та Ibis
  2. Завантажте будь-який великий датасет (>1M рядків):
  3. Виконайте:
    • Базову статистику (COUNT, AVG, SUM)
    • GROUP BY з віконними функціями
    • PIVOT/UNPIVOT
    • Порівняйте час виконання DuckDB vs Pandas
  4. Збережіть результати у Parquet
  5. Створіть 2-3 візуалізації

Дякую за увагу!



Матеріали курсу

ihor.miroshnychenko@knu.ua

Data Mirosh

@ihormiroshnychenko

@aranaur

aranaur.rbind.io