✍️ 필사 모드: DuckDB Internals Deep Dive — Embedded OLAP、Vectorized Execution、Morsel-Driven Parallelism、Storage Format 完全攻略 (2025)

import duckdb

con = duckdb.connect('my.duckdb')  # または ':memory:'
con.execute("CREATE TABLE users (id INT, name VARCHAR)")
con.execute("INSERT INTO users VALUES (1, 'Alice')")
result = con.execute("SELECT * FROM users").fetchall()

my_data.duckdb  ← 1 つのファイルにすべてのテーブル、インデックス

Table "events":
  Row Group 1 (122,880 rows):
    Column "timestamp": [bytes...]
    Column "user_id": [bytes...]
    Column "event_type": [bytes...]
    Column "value": [bytes...]
  Row Group 2:
    ...

for each row:
    apply_filter(row)
    transform(row)
    aggregate(row)

for each batch of 1024 rows:
    filter_batch()
    transform_batch()
    aggregate_batch()

struct Vector {
    Type type;           // INT, VARCHAR, DOUBLE, etc.
    VectorType vtype;    // FLAT, CONSTANT, DICTIONARY, SEQUENCE
    ValidityMask nulls;  // 各行の null 有無
    void* data;          // 実際の値
    idx_t count;         // 現在の行数
};

struct DataChunk {
    vector<Vector> data;
    idx_t count;  // 現在の行数(最大 1024)
};

SELECT user_id, SUM(value)
FROM events
WHERE event_type = 'click'
GROUP BY user_id

HashAggregate (GROUP BY user_id, SUM(value))
    ↓
Filter (event_type = 'click')
    ↓
TableScan (events)

class Operator {
    virtual unique_ptr<DataChunk> GetChunk() = 0;
};

  Filter → Exchange → Aggregate
            (重い)

Pipeline 1: TableScan → Filter → PartialHashAggregate
Pipeline 2: FinalHashAggregate → Output

Input morsels:  [M1] [M2] [M3] [M4] [M5] ...

Worker 1: pull M1 → process → output
Worker 2: pull M2 → process → output
Worker 3: pull M3 → process → output
Worker 4: pull M4 → process → output
...

Phase 1: 各 worker がローカル morsel から partial hash table を構築
Phase 2: Partial hash table をマージ
Phase 3: Final hash table から出力

A (1M) JOIN B (100) JOIN C (10)

SELECT * FROM events WHERE timestamp > '2024-01-01'

SELECT name, email FROM users WHERE age > 30

import duckdb
import pandas as pd

df = pd.DataFrame({
    'name': ['Alice', 'Bob'],
    'age': [30, 25]
})

# DataFrame を SQL から直接クエリ
result = duckdb.sql("SELECT * FROM df WHERE age > 28").df()
#                                    ^^ Python 変数をテーブルとして
print(result)

import polars as pl
df = pl.DataFrame({'a': [1,2,3]})
duckdb.sql("SELECT * FROM df").pl()  # Polars の結果

import pyarrow as pa
tbl = pa.table({'a': [1,2,3]})
duckdb.sql("SELECT * FROM tbl").arrow()

rel = duckdb.sql("SELECT * FROM df")
    .filter("age > 28")
    .project("name, age * 2 as double_age")
    .aggregate("SUM(double_age)")

# cell 1
import duckdb
con = duckdb.connect(':memory:')

# cell 2
%load_ext sql  # ipython-sql 拡張
%sql duckdb:///my.duckdb

# cell 3
%%sql
SELECT * FROM my_table LIMIT 10

df = pd.read_parquet('data.parquet', dtype_backend='pyarrow')

SELECT * FROM 'data.parquet' WHERE year = 2024

SELECT * FROM 'logs/*.parquet'
SELECT * FROM read_parquet(['f1.parquet', 'f2.parquet'])

data/year=2024/month=01/day=01/part.parquet

SELECT * FROM 's3://bucket/data.parquet'
SELECT * FROM 'https://example.com/data.csv'

SELECT * FROM 'data.csv'  -- 自動スキーマ検出
SELECT * FROM read_json('data.json', auto_detect=true)

SELECT item->>'name' AS name, (item->>'price')::FLOAT AS price
FROM 'items.json'

result = con.execute("SELECT * FROM t").fetch_arrow_table()

<script type="module">
import * as duckdb from '@duckdb/duckdb-wasm';

const JSDELIVR_BUNDLES = duckdb.getJsDelivrBundles();
const bundle = await duckdb.selectBundle(JSDELIVR_BUNDLES);

const worker = new Worker(bundle.mainWorker);
const logger = new duckdb.ConsoleLogger();
const db = new duckdb.AsyncDuckDB(logger, worker);
await db.instantiate(bundle.mainModule, bundle.pthreadWorker);

const conn = await db.connect();
const result = await conn.query(`
    SELECT * FROM 'https://example.com/data.parquet' LIMIT 10
`);
</script>

import pandas as pd

df = pd.read_parquet('large.parquet')
grouped = df[df['event_type'] == 'click'].groupby('user_id')['value'].sum()
top10 = grouped.nlargest(10)

import duckdb

result = duckdb.sql("""
    SELECT user_id, SUM(value) AS total
    FROM 'large.parquet'
    WHERE event_type = 'click'
    GROUP BY user_id
    ORDER BY total DESC
    LIMIT 10
""").df()

con = duckdb.connect('warehouse.duckdb')

# Extract: API/ファイルから
con.execute("""
    CREATE OR REPLACE TABLE raw_events AS
    SELECT * FROM read_parquet('s3://bucket/events/*.parquet')
""")

# Transform
con.execute("""
    CREATE OR REPLACE TABLE clean_events AS
    SELECT
        user_id,
        event_type,
        CAST(timestamp AS TIMESTAMP) AS ts,
        value
    FROM raw_events
    WHERE value > 0
""")

# Load
con.execute("COPY clean_events TO 'output.parquet' (FORMAT PARQUET)")

%load_ext sql
%sql duckdb:///analysis.duckdb

%%sql
SELECT
    date_trunc('month', purchase_date) AS month,
    category,
    SUM(amount) AS revenue
FROM sales
GROUP BY 1, 2
ORDER BY 1, 2

# profiles.yml
my_project:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: /tmp/my.duckdb

import polars as pl
import duckdb

# Polars DataFrame
df = pl.read_parquet('data.parquet')

# DuckDB で SQL クエリ
result = duckdb.sql("SELECT name, SUM(value) FROM df GROUP BY name").pl()
#                                                                    ^^
#                                                         Polars 結果として返す

INSTALL httpfs;
LOAD httpfs;

-- これで S3 アクセス可能
SELECT * FROM 's3://bucket/file.parquet';

INSTALL 'my_extension' FROM 'https://example.com/';
LOAD 'my_extension';

SELECT * FROM iceberg_scan('s3://bucket/iceberg-table');

ローカル(ノート PC):
  - ユーザー対話
  - 小さなデータキャッシュ
  - 最近のクエリ

クラウド(MotherDuck):
  - 大きなデータセット保存
  - 共有データ
  - 重い計算

DuckDB:      1x (baseline)
PostgreSQL:  20-50x slower
SQLite:      100x+ slower
ClickHouse:  0.5-0.8x (やや速い)

Pandas:  45 秒(メモリ不足リスク)
Polars:  12 秒
DuckDB:  8 秒

入力: 10 GB CSV

Spark (local mode): 120 秒(JVM 起動 + plan + execute)
Spark (cluster):    40 秒(クラスタコストあり)
DuckDB:             8 秒

SET memory_limit='8GB';

SET threads=8;

SET temp_directory='/fast/ssd/tmp';

EXPLAIN ANALYZE SELECT * FROM t WHERE x > 10;

PRAGMA enable_profiling;

┌─────────────────────────────────────────────────────┐
│                DuckDB Cheat Sheet                    │
├─────────────────────────────────────────────────────┤
│ 哲学:                                                  │
│   Embedded OLAP database                             │
│   "SQLite for analytics"                             │
│   単一ファイル、単一プロセス                            │
│   No server, no lock                                 │
│                                                       │
│ ストレージ:                                            │
│   columnar                                           │
│   Row groups (122,880 rows)                          │
│   Compression: RLE, bitpack, dict, FSST              │
│   Checkpoint + WAL                                   │
│                                                       │
│ 実行:                                                  │
│   Vector-based (1024 rows per chunk)                 │
│   SIMD friendly                                      │
│   Late materialization                               │
│   Pipeline operators                                 │
│                                                       │
│ 並列:                                                  │
│   Morsel-Driven Parallelism                          │
│   ~100K rows per morsel                              │
│   Work stealing                                      │
│   ほぼ線形スケール                                     │
│                                                       │
│ Optimizer:                                            │
│   Rule-based (pushdown, pruning, CSE)                │
│   Cost-based join ordering                           │
│   HyperLogLog distinct count                         │
│   Row group skipping (min/max)                       │
│                                                       │
│ Python:                                               │
│   pip install duckdb                                 │
│   Zero-copy DataFrame アクセス (Pandas, Polars, Arrow)│
│   Relational API (lazy)                              │
│   Jupyter ipython-sql 統合                            │
│                                                       │
│ ファイル統合:                                           │
│   Parquet (predicate pushdown)                       │
│   Arrow (zero-copy)                                  │
│   CSV (smart parser)                                 │
│   JSON (nested 対応)                                  │
│   S3 / HTTP (httpfs extension)                       │
│                                                       │
│ ブラウザ:                                              │
│   DuckDB-WASM                                        │
│   Observable, Malloy, Evidence                       │
│                                                       │
│ Extensions:                                           │
│   httpfs, postgres_scanner, iceberg, delta           │
│   fts, spatial, vss (vector search)                  │
│                                                       │
│ MotherDuck:                                           │
│   マネージドサービス                                    │
│   Hybrid execution (local + cloud)                   │
│   無料ティア                                           │
│                                                       │
│ vs 競合:                                               │
│   Pandas: 10 倍速い、大きなデータ OK                   │
│   Polars: 似た哲学、DataFrame API                     │
│   SQLite: OLAP でずっと速い                            │
│   ClickHouse: DuckDB は embedded                     │
│   Spark: DuckDB は単一マシンで速い                     │
│                                                       │
│ チューニング:                                          │
│   memory_limit, threads                              │
│   EXPLAIN ANALYZE                                    │
│   Projection + predicate pushdown                    │
│   COPY for bulk load                                 │
└─────────────────────────────────────────────────────┘