🦆

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

2022/09/24に公開

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のニーズの傍証と言えそうです。

機能・特徴

性能

ポジショニングや競合の話では、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;

ユースケース

触ってみる

インストール

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」

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

Discussion