すごいぞはやいぞDuckDB

2023-06-10 第106回R勉強会@東京
@eitsupi

はじめに

自己紹介

  • @eitsupi
  • 大手製造業勤務
  • Excelが嫌になりRを触り初めて4年
    • Dockerイメージrocker/r-ver他のメンテナー
  • データ基盤？の立ち上げを行い、SQLを触り始める
  • 最近はPRQL界隈によく出没

DuckDB

  • SQLiteのようなRDBMS、だけど分析用途に特化
  • 2019年、オランダ国立情報数学研究所（CWI）発
    • 現在は非営利団体DuckDB Foundationが保有
    • CWIからスピンオフしたDuckDB Labs社メインで開発中

https://duckdblabs.com/news/2021/07/14/spin-off-company-DuckDB-Labs.html

すごいぞDuckDB

  • 様々な環境・言語上で動作
    • C、C++、CLI、Julia、Java、Node.js、ODBC、Python、R、Rust、Scala、Swift、Wasm
  • リッチで使いやすいSQL
    • Postgres準拠 + 独自拡張
  • 豊富な入出力
    • PythonとR上でのpandas.DataFrame、R DataFrame、Arrow Tableに対する直接クエリ
  • 拡張機能

はやいぞDuckDB

  • 4月にDuckDB Labsが復活させた1H2O.aiベンチマークで
    DuckDB 0.8はトップクラスの成績。
  • 開発も早い
    • 約3ヶ月毎の機能リリース
    • バグ報告 → 4分で返信 → 詳細を伝えた後15分後にはPRでバグ修正 (duckdb/duckdb#5916)

DuckDBのSQL

DuckDBを動かしてみよう (1/2)

DuckDB Web Shell

  • ブラウザ上でDuckDBを動かして動作を確認できます
  • ファイルアップロードにも対応！
    • .files addコマンドでローカルのファイルをアップロードして
      クエリの対象にできます

DuckDBを動かしてみよう (2/2)

DuckDB CLI

  • 単一バイナリのCLI、DLしてその辺に置くだけで動きます
  • Homebrewやwingetとかでもインストールできます
  • 表示がリッチでオートコンプリートも効き2、使い勝手○
  • Parquetの中身を確認するだけでも便利です
  • duckdb -c 'from foo.csv' --markdown >foo.mdのようにMarkdownへの変換もおまかせ！

サンプルデータ

サンプルとしてdplyrに含まれているstarwarsデータの元になっているCSVファイルをダウンロードします。

curl -sL https://raw.githubusercontent.com/tidyverse/dplyr/v1.1.2/data-raw/starwars.csv -o starwars.csv

read_csv_auto関数でデータを読み込みます。

SELECT *
FROM read_csv_auto('starwars.csv', header=true, nullstr='NA')
LIMIT 3
┌────────────────┬────────┬────────┬───┬──────────────────────┬──────────────────────┐
│      name      │ height │  mass  │ … │       vehicles       │      starships       │
│    varchar     │ int64  │ double │   │       varchar        │       varchar        │
├────────────────┼────────┼────────┼───┼──────────────────────┼──────────────────────┤
│ Luke Skywalker │    172 │   77.0 │ … │ Snowspeeder, Imper…  │ X-wing, Imperial s…  │
│ C-3PO          │    167 │   75.0 │ … │                      │                      │
│ R2-D2          │     96 │   32.0 │ … │                      │                      │
├────────────────┴────────┴────────┴───┴──────────────────────┴──────────────────────┤
│ 3 rows                                                        14 columns (5 shown) │
└────────────────────────────────────────────────────────────────────────────────────┘

DuckDBのSQL: FROM句で開始するクエリ

DuckDBではSELECT句ではなくFROM句でクエリを開始できます。

これ↓は

SELECT *
FROM read_csv_auto('starwars.csv', header=true, nullstr='NA')
LIMIT 5

こう書けます。

FROM read_csv_auto('starwars.csv', header=true, nullstr='NA')
SELECT *
LIMIT 5

SELECT *の省略も許されています。

FROM read_csv_auto('starwars.csv', header=true, nullstr='NA')
LIMIT 5

DuckDBのSQL: 複数列の選択

dplyrだとtidyselectで↓のように書ける
「列名のパターンで複数列を選択する」という処理

dplyr::starwars |>
  dplyr::select(matches("s$")) |>
  head(5)

DuckDBのSQL: 複数列の選択

DuckDBはCOLUMNSを使ってこう書けます。

FROM read_csv_auto('starwars.csv', header=true, nullstr='NA')
SELECT COLUMNS('s$')
LIMIT 5
┌────────┬─────────┬───┬──────────────────────┬──────────────────────┐
│  mass  │ species │ … │       vehicles       │      starships       │
│ double │ varchar │   │       varchar        │       varchar        │
├────────┼─────────┼───┼──────────────────────┼──────────────────────┤
│   77.0 │ Human   │ … │ Snowspeeder, Imper…  │ X-wing, Imperial s…  │
│   75.0 │ Droid   │ … │                      │                      │
│   32.0 │ Droid   │ … │                      │                      │
│  136.0 │ Human   │ … │                      │ TIE Advanced x1      │
│   49.0 │ Human   │ … │ Imperial Speeder B…  │                      │
├────────┴─────────┴───┴──────────────────────┴──────────────────────┤
│ 5 rows                                         5 columns (4 shown) │
└────────────────────────────────────────────────────────────────────┘

DuckDBのSQL: 列の置換

dplyrだと↓のように書ける
「特定の列を置換する」という処理

dplyr::starwars |>
  dplyr::mutate(height = height * 1000) |>
  head(5)

DuckDBのSQL: 列の置換

DuckDBではREPLACEを使ってこう書けます。

FROM read_csv_auto('starwars.csv', header=true, nullstr='NA')
SELECT * REPLACE (height * 1000 AS height)
LIMIT 5
┌────────────────┬────────┬────────┬───┬──────────────────────┬──────────────────────┐
│      name      │ height │  mass  │ … │       vehicles       │      starships       │
│    varchar     │ int64  │ double │   │       varchar        │       varchar        │
├────────────────┼────────┼────────┼───┼──────────────────────┼──────────────────────┤
│ Luke Skywalker │ 172000 │   77.0 │ … │ Snowspeeder, Imper…  │ X-wing, Imperial s…  │
│ C-3PO          │ 167000 │   75.0 │ … │                      │                      │
│ R2-D2          │  96000 │   32.0 │ … │                      │                      │
│ Darth Vader    │ 202000 │  136.0 │ … │                      │ TIE Advanced x1      │
│ Leia Organa    │ 150000 │   49.0 │ … │ Imperial Speeder B…  │                      │
├────────────────┴────────┴────────┴───┴──────────────────────┴──────────────────────┤
│ 5 rows                                                        14 columns (5 shown) │
└────────────────────────────────────────────────────────────────────────────────────┘

DuckDBのSQL: DISTINCT ON

dplyrだと↓のように書ける
「グループ毎に最大の値を持つ行だけを残す」という処理

dplyr::starwars |>
  dplyr::select(sex, name, mass) |>
  dplyr::slice_max(mass, n = 1, by = sex)

DuckDBのSQL: DISTINCT ON

DuckDBはDISTINCT ONでこう書けます。

FROM read_csv_auto('starwars.csv', header=true, nullstr='NA')
SELECT DISTINCT ON (sex)
  sex, name, mass
ORDER BY mass DESC
┌────────────────┬───────────────────────┬────────┐
│      sex       │         name          │  mass  │
│    varchar     │        varchar        │ double │
├────────────────┼───────────────────────┼────────┤
│ hermaphroditic │ Jabba Desilijic Tiure │ 1358.0 │
│ male           │ Grievous              │  159.0 │
│ none           │ IG-88                 │  140.0 │
│ female         │ Beru Whitesun lars    │   75.0 │
│                │ Sly Moore             │   48.0 │
└────────────────┴───────────────────────┴────────┘

DuckDBのSQL: QUALIFY句

dplyrだと↓のように書ける
「グループ毎に上位n行を残す」という処理

dplyr::starwars |>
  dplyr::select(sex, name, mass) |>
  dplyr::slice_max(mass, n = 2, by = sex, with_ties = FALSE)

DuckDBのSQL: QUALIFY句

DuckDBはQUALIFY句でこう書けます。

FROM read_csv_auto('starwars.csv', header=true, nullstr='NA')
SELECT
  sex, name, mass
QUALIFY
  row_number() over (partition by sex order by mass desc) <= 2
┌────────────────┬───────────────────────┬────────┐
│      sex       │         name          │  mass  │
│    varchar     │        varchar        │ double │
├────────────────┼───────────────────────┼────────┤
│ female         │ Beru Whitesun lars    │   75.0 │
│ female         │ Shaak Ti              │   57.0 │
│                │ Sly Moore             │   48.0 │
│                │ Ric Olié              │        │
│ hermaphroditic │ Jabba Desilijic Tiure │ 1358.0 │
│ male           │ Grievous              │  159.0 │
│ male           │ Darth Vader           │  136.0 │
│ none           │ IG-88                 │  140.0 │
│ none           │ C-3PO                 │   75.0 │
└────────────────┴───────────────────────┴────────┘

DuckDBの速度

duckdb Rパッケージの速度を測ってみましょう。

テストデータ

DuckDBのリポジトリに置かれているいつものParquet3をダウンロードします。

curl::curl_download(
  "https://github.com/duckdb/duckdb-data/releases/download/v1.0/lineitemsf1.snappy.parquet",
  "lineitemsf1.snappy.parquet"
)

サクッと時間を計りたいのでtictocパッケージをロードします。

library(tictoc)

DuckDBとSQLiteのインメモリデータベーを作成しておきます。

con <- DBI::dbConnect(duckdb::duckdb())
con_sqlite <- DBI::dbConnect(RSQLite::SQLite())

参考：dplyr

事前にParquetからデータを読み込んでおきます。（duckdb使用）

コード 
tic()

df_lineitemsf1 <- duckdb:::sql("FROM lineitemsf1.snappy.parquet") |>
  dplyr::as_tibble()

toc()
22.707 sec elapsed
tic()

df_lineitemsf1 |>
  dplyr::filter(
    l_shipdate >= "1994-01-01", l_shipdate < "1995-01-01",
    l_discount >= 0.05, l_discount < 0.07,
    l_quantity < 24
  ) |>
  dplyr::summarise(revenue = sum(l_extendedprice * l_discount, na.rm = TRUE))
# A tibble: 1 × 1
    revenue
      <dbl>
1 75207768.
toc()
4.512 sec elapsed

参考：data.table (dtplyr)

tic()

df_lineitemsf1 |>
  dtplyr::lazy_dt() |>
  dplyr::filter(
    l_shipdate >= "1994-01-01", l_shipdate < "1995-01-01",
    l_discount >= 0.05, l_discount < 0.07,
    l_quantity < 24
  ) |>
  dplyr::summarise(revenue = sum(l_extendedprice * l_discount, na.rm = TRUE)) |>
  dplyr::collect()
# A tibble: 1 × 1
    revenue
      <dbl>
1 75207768.
toc()
5.58 sec elapsed

参考：Acero (arrow)

Parquetデータセットに対して直接クエリを実行できます。

tic()

arrow::open_dataset("lineitemsf1.snappy.parquet") |>
  dplyr::filter(
    l_shipdate >= "1994-01-01", l_shipdate < "1995-01-01",
    l_discount >= 0.05, l_discount < 0.07,
    l_quantity < 24
  ) |>
  dplyr::summarise(revenue = sum(l_extendedprice * l_discount, na.rm = TRUE)) |>
  dplyr::collect()
# A tibble: 1 × 1
    revenue
      <dbl>
1 75207768.
toc()
1.175 sec elapsed

参考：Polars (r-polars)

Parquetデータセットに対して直接クエリを実行できます。（Hive風パーティションは未対応）

tic()

polars::scan_parquet("lineitemsf1.snappy.parquet")$filter(
    polars::pl$col("l_shipdate") >= "1994-01-01" & polars::pl$col("l_shipdate") < "1995-01-01" &
    polars::pl$col("l_discount") >= 0.05 & polars::pl$col("l_discount") < 0.07 &
    polars::pl$col("l_quantity") < 24
  )$select(
    (polars::pl$col("l_extendedprice") * polars::pl$col("l_discount"))$sum()$alias("revenue")
  ) |>
  as.data.frame()
   revenue
1 75207768
toc()
0.855 sec elapsed

参考：SQLite

データをインメモリテーブルに書き込む必要があります。

コード 
tic()

DBI::dbWriteTable(con_sqlite, "lineitemsf1", df_lineitemsf1)

toc()
28.565 sec elapsed
tic()

DBI::dbGetQuery(con_sqlite, "
SELECT
  sum(l_extendedprice * l_discount) AS revenue
FROM lineitemsf1
WHERE
  l_shipdate >= '1994-01-01' AND l_shipdate < '1995-01-01'
  AND l_discount >= 0.05 AND l_discount < 0.07
  AND l_quantity < 24
")
   revenue
1 75207768
toc()
2.911 sec elapsed

参考：SQLite (dbplyr)

tic()

dplyr::tbl(con_sqlite, "lineitemsf1") |>
  dplyr::filter(
    l_shipdate >= "1994-01-01", l_shipdate < "1995-01-01",
    l_discount >= 0.05, l_discount < 0.07,
    l_quantity < 24
  ) |>
  dplyr::summarise(revenue = sum(l_extendedprice * l_discount, na.rm = TRUE)) |>
  dplyr::collect()
# A tibble: 1 × 1
    revenue
      <dbl>
1 75207768.
toc()
3.191 sec elapsed

DuckDB; CSV、Parquetへの直接クエリ

tic()

duckdb:::sql("
FROM lineitemsf1.snappy.parquet
SELECT
  sum(l_extendedprice * l_discount) AS revenue
WHERE
  l_shipdate >= '1994-01-01' AND l_shipdate < '1995-01-01'
  AND l_discount >= 0.05 AND l_discount < 0.07
  AND l_quantity < 24
", con
)
   revenue
1 75207768
toc()
0.401 sec elapsed

DuckDB; Data Frameへのクエリ

データフレームをDB内にコピーする必要はありません。

tic()

# 仮想テーブルとしてR DataFrameをDBに登録
duckdb::duckdb_register(con, "lineitemsf1", df_lineitemsf1, overwrite = TRUE)

duckdb:::sql("
FROM lineitemsf1
SELECT
  sum(l_extendedprice * l_discount) AS revenue
WHERE
  l_shipdate >= '1994-01-01' AND l_shipdate < '1995-01-01'
  AND l_discount >= 0.05 AND l_discount < 0.07
  AND l_quantity < 24
", con
)
   revenue
1 75207768
toc()
0.442 sec elapsed

DuckDB; CSV、Parquetへの直接クエリ (dbplyr)

tic()

dplyr::tbl(con, "lineitemsf1.snappy.parquet") |>
  dplyr::filter(
    l_shipdate >= "1994-01-01", l_shipdate < "1995-01-01",
    l_discount >= 0.05, l_discount < 0.07,
    l_quantity < 24
  ) |>
  dplyr::summarise(
    revenue = sum(l_extendedprice * l_discount, na.rm = TRUE)
  ) |>
  dplyr::collect()
# A tibble: 1 × 1
    revenue
      <dbl>
1 75207768.
toc()
0.581 sec elapsed

DuckDB; Data Frameへのクエリ (dbplyr)

tic()

# 仮想テーブルとしてR DataFrameをDBに登録
duckdb::duckdb_register(con, "lineitemsf1", df_lineitemsf1, overwrite = TRUE)

dplyr::tbl(con, "lineitemsf1") |>
  dplyr::filter(
    l_shipdate >= "1994-01-01", l_shipdate < "1995-01-01",
    l_discount >= 0.05, l_discount < 0.07,
    l_quantity < 24
  ) |>
  dplyr::summarise(revenue = sum(l_extendedprice * l_discount, na.rm = TRUE)) |>
  dplyr::collect()
# A tibble: 1 × 1
    revenue
      <dbl>
1 75207768.
toc()
1.222 sec elapsed

参考：duckplyr (1/2)

duckdblabs組織下で活発に開発されている謎のRパッケージ。

dbplyrがdplyrをSQLに翻訳するのに対し、duckplyrはDuckDBのリレーショナルAPI（？）に翻訳しクエリを実行する。

tic()

df_lineitemsf1 |>
  duckplyr::as_duckplyr_df() |>
  dplyr::filter(
    l_shipdate >= "1994-01-01", l_shipdate < "1995-01-01",
    l_discount >= 0.05, l_discount < 0.07,
    l_quantity < 24
  ) |>
  dplyr::summarise(revenue = sum(l_extendedprice * l_discount)) # 直接DuckDBの関数に変換されるため、na.rm引数を使用するとエラーになる

toc()

参考：duckplyr (2/2)

materializing:
---------------------
--- Relation Tree ---
---------------------
Distinct
  Aggregate [sum(*(l_extendedprice, l_discount))]
    Filter [(>=(l_shipdate, '1994-01-01') AND <(l_shipdate, '1995-01-01') AND >=(l_discount, 0.05) AND <(l_discount, 0.07) AND <(l_quantity, 24.0))]
      r_dataframe_scan(0x560b29736908)

---------------------
-- Result Columns  --
---------------------
- revenue (DOUBLE)

# A tibble: 1 × 1
    revenue
      <dbl>
1 75207768.
8.893 sec elapsed

まとめ

  • DuckDBは手軽 → すぐに使い始めましょう！
  • DuckDBは高速 → ちょっと速度測ってみましょう！

Enjoy!

#Tokyo.R

脚注

  1. The Return of the H2O.ai Database-like Ops Benchmark

  2. しかし現在最新の0.8.0ではオートコンプリートが壊れているようです。 (duckdb/duckdb#7564)

  3. DuckDB quacks Arrow: A zero-copy data integration between Apache Arrow and DuckDB