🦆

🦆🦆🦆🦆🦆🦆DuckDB入門🦆🦆🦆🦆🦆🦆

2022/09/24に公開

Python

tl;dr

SQLiteのOLAP版だよ
OLAP系のクエリにおいて、PandasやSQLiteより早いらしいよ
CSV・Parquet・Pandas DataFrameの読み書きできて便利だよ

背景

ポジション・競合

一言で言うとSQLiteのOLAP版です。位置づけとしては、論文(DuckDB: an Embeddable Analytical Database (SIGMOD 2019 Demo))記載のSystem Landscapeがわかりやすいです。

（DuckDB: an Embeddable Analytical Database (SIGMOD 2019 Demo)より）

このLandscapeでは、データベースを

Standalone（クライアント・サーバモデル）か、組み込み（シングルマシン・インプロセス）か
OLTPかOLAPか

の二軸に分割しています。その上で、

クライアント・サーバモデルのOLTPにはPostgreSQL等
クライアント・サーバモデルのOLAPにはTeradata等
組み込みのOLTPにはSQLite

が存在するのに対し、組み込みのOLAPがあまり無い（※）点に注目したのがDuckDBです。
※ ただし、MonetDB LiteやHyPer（Tableauが買収したDB）など、DuckDBがリリースされた時点でもいくつか存在しており、DuckDBが初めての組み込みOLAP DBではありません

このポジショニングから、DuckDBの競合・代替は

SQLite
分散OLAP・DWHのデータベース（ClickHouseとかBigQuery）
Pandas・dplyrのようなデータ操作のライブラリ

あたりになるかと思います。

DuckDBのドキュメントや論文(DuckDB: an Embeddable Analytical Database (SIGMOD 2019 Demo))では、

SQLiteよりもOLAPに特化
OLAP・DWHのデータベースに比べて、データソース（Edge）で動ける
- データ転送や設定が不要
Pandas・dplyrなどに比べ、高速（クエリの最適化を行う）、SQL（表現力や移植性）が使える、トランザクションが使える

という点で、差別化されるとのことです。

想定ユースケース

このポジション（組み込みOLAP）の製品が無いだけでは、需要がないのでは？というツッコミもできそうです。それに対し、上述の論文では、

Edge Computing
- 例えば、電力計のデータ集めるとして、そのまま送るのではなく集計（=OLAPクエリ）して送ると帯域が弱くても大丈夫になる
Interactive Data Analysis（ローカルでのデータ分析）
　　 * Pandasやdplyrの代わりや、ダッシュボードのバックエンドとして

が想定ユースケースとして記載されています。

また、スマホのSQLiteの利用を調べた論文(Kennedy, Oliver et al. “Pocket Data: The Need for TPC-MOBILE.” TPCTC (2015).)では

数としてはOLTPが多い
ただし、無視できない量のOLAP・複雑なクエリがある

と報告されており、これも組み込みのOLAP DBのニーズの傍証と言えそうです。

機能・特徴

OLAPに特化した実装
- Vectorized Query Engine
- Columnar Storage
シンプルな運用（実行時の外部依存や設定がほぼ無い等）
一般的なSQLとしての機能
- ACIDトランザクション
- PythonやRなどのAPI
- Window関数や、集計
ParquetやCSVの読み書き、Pands DataFrameの読み書き
- ローカルだけではなく、HTTP(S)、S3からも取得できるようです

性能

ポジショニングや競合の話では、SQLite・Pandasに比べ特化しており、性能有利（を目指している）と記載しました。性能が有利でないのであれば、そもそもSQLite・Pandas使えという話になりますので、ベンチマークについて調べてみました。

SQLiteとの比較

SQLiteとの比較は、SQLite側の人が論文（SQLite: Past, Present, and Future. PVLDB,. 15(12): 3535 - 3547, 2022. doi:10.14778/3554821.3554842）にまとめてくれており、

OLTPのベンチマーク（TATP）ではSQLiteの方が有利
OLAPのベンチマーク（TPC-Hを少し変形したもの）ではDuckDBが有利
Blob Manipulation（※）でもDuckDBが有利
- フィールドの中に大きなデータを突っ込んだテーブル・クエリ
foot print（ライブラリのサイズやコンパイルの時間）ではSQLiteが有利

と、概ねDuckDBのアピール通りの結果となっています。

また、FOSDEM 2020のHannes Mühleisenさん（DuckDBの開発者）の発表でも、SQLite・PostgreSQL・MySQLに対して優位（TPC-H）としています。

Pandasとの比較

DuckDBのブログで、

計算（SUMとかMAX）
集計（GROUP BY）
JOIN
をTPC-Hのデータを用いて比較し、概してDuckDBが有利という結果をまとめています。

（ソートの比較は別に論文（Kuiper, Laurens, Mark Raasveldt, and Hannes Mühleisen. "Efficient External Sorting in DuckDB." (2021).）も）

体感

定量的なベンチマークについては上記の論文等を見ていただくとして、22GBのCSVで下のクエリ試したところ、一度DuckDBに取り込んだ後は確かに早かった（集計が数秒）です。

# （試したクエリ。これは数分かかりました）
D CREATE TABLE transactions AS SELECT * FROM read_csv_auto('transactions.csv');
# 年毎の集計。これは数秒
D SELECT YEAR,COUNT(*),MAX(AMOUNT) FROM transactions GROUP BY YEAR;

ユースケース

Airbnb
Rill（BI？）
Malloy

触ってみる

インストール

PythonとCLIは別の話で、どちらかだけでも動くはずです。

Python

> python3 -m pip install duckdb==0.5.0

（Pythonインタプリタ内）

In [1]: import duckdb
In [2]: cursor = duckdb.connect()
In [3]: print(cursor.execute("SELECT 42").fetchall())
[(42,)]

CLI

（macOSの場合。WindowsやLinuxは適当に読み替えてください）

> wget  https://github.com/duckdb/duckdb/releases/download/v0.5.0/duckdb_cli-osx-universal.zip
> unzip duckdb_cli-osx-universal.zip
Archive:  duckdb_cli-osx-universal.zip
  inflating: duckdb
> ./duckdb
v0.5.0 109f932c4
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
D SELECT 42;
┌────┐
│ 42 │
├────┤
│ 42 │
└────┘

（SELECTの左にある「D」はプロンプトです）

以下は、特に記載がない限り、CLIかPythonのcursor.executeで実行するクエリを記載します。

テーブル作成、挿入、選択

他のRDB同様テーブル作成(CREATE TABLE)、挿入(INSERT)、選択（SELECT）できます。
（例は省略しますが、DELETEやUPDATEもできるはず）

D CREATE TABLE t1(i INTEGER, j INTEGER);
D SHOW TABLES;
┌──────┐
│ name │
├──────┤
│ t1   │
└──────┘
D INSERT INTO t1 VALUES (1,2), (2,3), (3,4);
D SELECT * FROM t1;
┌───┬───┐
│ i │ j │
├───┼───┤
│ 1 │ 2 │
│ 2 │ 3 │
│ 3 │ 4 │
└───┴───┘

ファイルに永続化する

起動時に

Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

とある通り、DuckDB CLI（Pythonでも）デフォルトではin-memoryにテーブルを保存するため、CLIを終了するとデータは消えます。

データを永続化するには、起動時にファイルを指定するか、CLIセッションの途中で.openでファイルを指定します。

> ./duckdb buzz.db
v0.5.0 109f932c4
Enter ".help" for usage hints.
D CREATE TABLE t1(i INTEGER, j INTEGER);
D INSERT INTO t1 VALUES (1,2), (2,3), (3,4);
D SELECT * FROM t1;
┌───┬───┐
│ i │ j │
├───┼───┤
│ 1 │ 2 │
│ 2 │ 3 │
│ 3 │ 4 │
└───┴───┘
D .exit
# 再起動してもファイル残っている
> ./duckdb buzz.db
v0.5.0 109f932c4
Enter ".help" for usage hints.
D SELECT * FROM t1;
┌───┬───┐
│ i │ j │
├───┼───┤
│ 1 │ 2 │
│ 2 │ 3 │
│ 3 │ 4 │
└───┴───┘

Pythonの場合はconnectの引数にファイル名を渡します。

In [4]: cursor = duckdb.connect('python.db')
In [5]: cursor.execute("CREATE TABLE t2(i INTEGER, j INTEGER)")
In [6]: cursor.execute("INSERT INTO t2 VALUES (1,2), (2,3), (3,4)")

Pythonインタプリタを再起動してもテーブルを閲覧できます

In [2]: import duckdb
In [3]: cursor = duckdb.connect("python.db")
In [4]: print(cursor.execute("SELECT * FROM t2").fetchall())
[(1, 2), (2, 3), (3, 4)]

外部ファイルの読み込み・DuckDB内への保存

DuckDBではCSV・Parquetのファイルを読み込むことができます。

CSVの読み込み

# 動作確認のダミーファイル
echo -e "a,1,c\naa,2,cc" > hoge.csv
echo -e "a,b,c\nd,e,f" > hoge2.csv

CSVファイルを読み込む

D SELECT * FROM read_csv_auto('hoge.csv');
┌─────────┬─────────┬─────────┐
│ column0 │ column1 │ column2 │
├─────────┼─────────┼─────────┤
│ a       │ 1       │ c       │
│ aa      │ 2       │ cc      │
└─────────┴─────────┴─────────┘

上記の例ではスキーマを推測してくれていますが、オプションで明示することもできます。

# hoge2.csvはスキーマ推測ではおかしな結果になります
SELECT * FROM read_csv_auto('hoge2.csv');
┌───┬────────────┬───────┐
│ a │     b      │   c   │
├───┼────────────┼───────┤
│ d │ 1970-01-01 │ false │
└───┴────────────┴───────┘
# カラム定義を明示的にわたすと期待どおりになります
D SELECT * FROM read_csv('hoge2.csv', header=False, AUTO_DETECT=False, COLUMNS={'col1': 'STRING', 'col2': 'STRING', 'col3': 'STRING'});
┌──────┬──────┬──────┐
│ col1 │ col2 │ col3 │
├──────┼──────┼──────┤
│ a    │ b    │ c    │
│ d    │ e    │ f    │
└──────┴──────┴──────┘

保存

CSVから読み込んだデータを、DuckDBのテーブルとして保存できます。
（下例ではいわゆるCTAS(CREAT TABLE AS SELECT)で保存していますが、既存データに追加するときはCOPY文も使えます）

D CREATE TABLE hoge_internal  AS SELECT * FROM read_csv_auto('hoge.csv');
D SELECT * FROM hoge_internal;
┌─────────┬─────────┬─────────┐
│ column0 │ column1 │ column2 │
├─────────┼─────────┼─────────┤
│ a       │ 1       │ c       │
│ aa      │ 2       │ cc      │
└─────────┴─────────┴─────────┘

この後、元ファイル（hoge.csv）を削除してもhoge_internalのデータはそのままです。

> rm hoge.csv

D SELECT * FROM hoge_internal;
┌─────────┬─────────┬─────────┐
│ column0 │ column1 │ column2 │
├─────────┼─────────┼─────────┤
│ a       │ 1       │ c       │
│ aa      │ 2       │ cc      │
└─────────┴─────────┴─────────┘

書き出し

DuckDBのテーブルをCSV・Parquetに書き出すこともできます

D COPY hoge_internal TO 'output.csv' (HEADER, DELIMITER ',')
D COPY hoge_internal TO 'output.parquet' (FORMAT PARQUET);

> cat output.csv
column0,column1,column2
a,1,c
> file output.parquet
output.parquet: Apache Parquet

DuckDB CLI限定ではありますが、.output、.modeで結果をファイルに保存することもできます（使い分けは不明）。

D .mode markdown
D .output hoge.md
D SELECT * FROM hoge_internal;

> cat hoge.md                                                                   
| column0 | column1 | column2 |
|---------|---------|---------|
| a       | 1       | c       |
| aa      | 2       | cc      |

Parquetファイルの読みこみ

CSV -> DuckDBテーブル -> Parquetファイルの流れで書き込んだParquetファイルを、再度DuckDBで読み込むこともできます

D SELECT * FROM read_parquet('output.parquet');
┌─────────┬─────────┬─────────┐
│ column0 │ column1 │ column2 │
├─────────┼─────────┼─────────┤
│ a       │ 1       │ c       │
│ aa      │ 2       │ cc      │
└─────────┴─────────┴─────────┘

over HTTP

ドキュメントの例ではParquetしかありませんが、CSVもHTTP(S)経由で読み取ることができます。

INSTALL httpfs;
D LOAD httpfs;
D SELECT * FROM read_csv_auto('https://people.sc.fsu.edu/~jburkardt/data/csv/addresses.csv');
┌───────────────────────┬──────────┬──────────────────────────────────┬─────────────┬─────────┬─────────┐
│        column0        │ column1  │             column2              │   column3   │ column4 │ column5 │
├───────────────────────┼──────────┼──────────────────────────────────┼─────────────┼─────────┼─────────┤
│ John                  │ Doe      │ 120 jefferson st.                │ Riverside   │  NJ     │ 8075    │
│ Jack                  │ McGinnis │ 220 hobo Av.                     │ Phila       │  PA     │ 9119    │
│ John "Da Man"         │ Repici   │ 120 Jefferson St.                │ Riverside   │  NJ     │ 8075    │
│ Stephen               │ Tyler    │ 7452 Terrace "At the Plaza" road │ SomeTown    │ SD      │ 91234   │
│                       │ Blankman │                                  │ SomeTown    │  SD     │ 298     │
│ Joan "the bone", Anne │ Jet      │ 9th, at Terrace plc              │ Desert City │ CO      │ 123     │
└───────────────────────┴──────────┴──────────────────────────────────┴─────────────┴─────────┴─────────┘

Pandas

PandasのDataFrameを読み込んだり、結果をDataFrameにすることも出来ます。
（当たり前ですが、Python上でDuckDB実行する場合のみの話です）

# PythonのDuckDBからの接続の続き
In [7]: import pandas as pd
In [8]: mydf = pd.DataFrame({"a": [1, 2, 3]})
In [10]: resultdf = duckdb.query("SELECT SUM(a) FROM mydf").to_df()
In [11]: type(resultdf)
Out[11]: pandas.core.frame.DataFrame
In [12]: resultdf
Out[12]:
   sum(a)
0     6.0

この例では、

PandasのDataFrameを作成
DataFrameに対してクエリを実行
結果をDataFrameとして保存

しています。

DuckDBのテーブルとして保存したり、DuckDBのテーブルを読み込んでDataFrameにすることもできます。

# DataFrameをDuckDBのテーブルにする例
In [19]: cursor.execute("CREATE TABLE df_table AS SELECT * FROM mydf")
Out[19]: <duckdb.DuckDBPyConnection at 0x105e54a30>

In [20]: print(cursor.execute("SELECT * FROM df_table").fetchall())
[(1,), (2,), (3,)]

# DuckDBのテーブルをDataFarmeにする例
In [28]: t2_df = cursor.execute("SELECT * FROM t2").fetch_df()

In [29]: type(t2_df)
Out[29]: pandas.core.frame.DataFrame

In [30]: t2_df
Out[30]:
   i  j
0  1  2
1  2  3
2  3  4

(Multi-statement)トランザクション

ドキュメントに記載を見つけられなかったのですが、(Multi-statement)トランザクションもあるようです。

# トランザクション前は空
D SELECT * FROM t1;
# トランザクション開始
D BEGIN TRANSACTION;
D INSERT INTO t1 VALUES (1,2), (2,3), (3,4);
# 途中でABORT
D ABORT;
D END;
Error: TransactionContext Error: cannot commit - no transaction is active
# テーブルにデータが格納されていません
D SELECT * FROM t1;

ABORTしないでENDするとデータが追加されます

D BEGIN TRANSACTION;
D INSERT INTO t1 VALUES (1,2), (2,3), (3,4);
D END;
D SELECT * FROM t1;
┌───┬───┐
│ i │ j │
├───┼───┤
│ 1 │ 2 │
│ 2 │ 3 │
│ 3 │ 4 │
└───┴───┘

複数のクライアントのアクセス

DuckDBはサーバー・クライアントモデルではなく、単一のプロセスからのアクセスを想定したデータベースです。
そのため、あるプロセス（e.g. CLI）で開いているデータベースを別のプロセスで開くとエラーになります。

# DuckDB CLIでpython.dbを開く
D .open python.db

# Pythonで同じデータベースを開くとエラー
In [2]: cursor = duckdb.connect("python.db")
---------------------------------------------------------------------------
IOException                               Traceback (most recent call last)
Input In [2], in <module>
----> 1 cursor = duckdb.connect("python.db")

IOException: IO Error: Could not set lock on file "python.db": Resource temporarily unavailable

また、テーブル・レコード単位のロックはありません（常にデータベース単位のロックされている状態なので）。

アーキテクチャ・実装

DuckDBはC++で実装されており、論文(DuckDB: an Embeddable Analytical Database (SIGMOD 2019 Demo))によると、下の組み合わせのコンポーネント・アルゴリズムが実装されています。

（DuckDB: an Embeddable Analytical Database (SIGMOD 2019 Demo)より）

論文の言葉を借りると「ここのコンポーネント自体は革命的というより」（「none of DuckDB's components is revolutionary in its regard」）、DuckDBのユースケースに適したState of the Artな組み合わせで性能を出しているようです。

DuckDB-WASM

DuckDB-WASM（論文、ブログ）というWASMに対応したバージョンもあります。Webブラウザで試す事もできます。

SQLiteに比べDuckDBがOLAPで有利なように、sql.js（SQLiteのWASM版）に比べてDuckDB-WASMはOLAPで有利らしいです。

なんで「Duck」

泳げる
歩ける
飛べる
うえに、環境変化にも強い素晴らしい動物だからだそうです。あと可愛い🦆🦆🦆🦆🦆🦆🦆🦆🦆🦆🦆🦆🦆🦆🦆

tl;dr

背景

ポジション・競合

想定ユースケース

機能・特徴

性能

SQLiteとの比較

Pandasとの比較

体感

ユースケース

触ってみる

インストール

Python

CLI

テーブル作成、挿入、選択

ファイルに永続化する

外部ファイルの読み込み・DuckDB内への保存

CSVの読み込み

保存

書き出し

Parquetファイルの読みこみ

over HTTP

Pandas

(Multi-statement)トランザクション

複数のクライアントのアクセス

アーキテクチャ・実装

DuckDB-WASM

なんで「Duck」

Discussion