🊆

🊆🊆🊆🊆🊆🊆DuckDB入門🊆🊆🊆🊆🊆🊆

2022/09/24に公開・玄13,400字

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ずしおの機胜
  • 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;

ナヌスケヌス

觊っおみる

むンストヌル

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

ログむンするずコメントできたす