データベースの基本操作

データ分析に特化したデータベースである DuckDBを使用する。 このデータベースは列指向（columnar）で、 オンライン分散処理（OLAP）を行い高速なSQL処理が可能。 CSV、Parquet形式のフィイルからデータ入力できる。 サーバレスDBとして有名なSQLiteと同等以上の性能があると報告されている。

if ( !require(duckdb) ) install.packages("duckdb")
## Warning: パッケージ 'duckdb' はバージョン 4.3.3 の R の下で造られました
library(duckdb)

# インメモリデータベースに接続
#con <- dbConnect(duckdb())

# データベースに接続
# (DBを他のプロセスと共有するときは、read_only = TRUEにし書き込みを禁止する。)
con <- dbConnect(duckdb(), dbdir = 'a.duckdb', read_only = FALSE)

# データベースにあるテーブルを表示
dbListTables(con)
## [1] "items"
# 入力するデータを作成
d <- data.frame(name   = c('Taro', 'Jiro'),
                salary = c(600, 550))

# 既にテーブルが存在している場合は削除
#if ( dbExistsTable(con, 'items') ) dbRemoveTable(con, 'items')

# データベースにテーブルを作成
#【オプション】追記モード： append = T、#上書きモード： overwrite = T
dbWriteTable(con, 'items', d, append = T)

# テーブルからデータを取得
res <- dbGetQuery(con, "SELECT * FROM items")

# 取得したデータを表示
# 本コードでは追記モードにしているのでプログラム実行のたびに同じレコード追加される。
print(res)
##   name salary
## 1 Taro    600
## 2 Jiro    550
## 3 Taro    600
## 4 Jiro    550
## 5 Taro    600
## 6 Jiro    550
dbDisconnect(con, shutdown = TRUE)

データ処理

ニューヨークの飛行場データを用いて、 出発地（dest）ごとに、目的地（origin）別のフライト数をカウント、 上位3つの出発地（アルファベット順）、および目的地を表示する。

データ

library(nycflights13)
data("flights", package = "nycflights13") # データの取得

SQLクエリによる取得

参考文献：On DuckDB and R

次のようにSQLクエリが複雑になる。

con <- dbConnect(duckdb()) # インメモリデータベースを作成、接続

duckdb_register(con, "flights", flights) # filightsを紐付け（DuckDBのテーブルとして扱う）

res <- dbGetQuery(con,
'SELECT origin, dest, n
  FROM (
    SELECT q01.*, RANK() OVER (PARTITION BY origin ORDER BY n DESC) AS col01
    FROM (
      SELECT origin, dest, COUNT(*) AS n
      FROM flights
      GROUP BY origin, dest
    ) q01
  ) q01
  WHERE (col01 <= 3) ORDER BY origin')

print(res) # 結果表示
##   origin dest     n
## 1    EWR  ORD  6100
## 2    EWR  BOS  5327
## 3    EWR  SFO  5127
## 4    JFK  LAX 11262
## 5    JFK  SFO  8204
## 6    JFK  BOS  5898
## 7    LGA  ATL 10263
## 8    LGA  ORD  8857
## 9    LGA  CLT  6168
duckdb_unregister(con, "flights") # fligthtsの紐付け解除

dbDisconnect(con, shutdown = TRUE) # データベースの接続解除

dplyrによる取得

Rのdplyrパッケージを用いると分かりやすく記述できる。

[Rチートシート] https://github.com/rstudio/cheatsheets/tree/main/translations/japanese
data-wrangling_ja.pdfにdplyの使い方があるので参照すること。

library(tidyverse)
## Warning: パッケージ 'ggplot2' はバージョン 4.3.3 の R の下で造られました
con <- dbConnect(duckdb()) # インメモリデータベースを作成、接続

duckdb_register(con, "flights", flights) # filightsを紐付け（DuckDBのテーブルとして扱う）

#tbl(con, 'flights') |> group_by(origin) |> count(dest) |> slice_max(n, n = 3) |> arrange(origin) |> show_query()
tbl(con, 'flights') |> group_by(origin) |> count(dest) |> slice_max(n, n = 3) |> arrange(origin) -> res

print(res) # 結果表示
## # Source:     SQL [9 x 3]
## # Database:   DuckDB v0.10.1 [hss@Windows 10 x64:R 4.3.2/:memory:]
## # Groups:     origin
## # Ordered by: origin
##   origin dest      n
##   <chr>  <chr> <dbl>
## 1 EWR    ORD    6100
## 2 EWR    BOS    5327
## 3 EWR    SFO    5127
## 4 JFK    LAX   11262
## 5 JFK    SFO    8204
## 6 JFK    BOS    5898
## 7 LGA    ATL   10263
## 8 LGA    ORD    8857
## 9 LGA    CLT    6168
res |> collect() |> as.data.frame() -> d.out # Rオブジェクトにするときはcollect関数を使う。

duckdb_unregister(con, "flights") # fligthtsの紐付け解除

dbDisconnect(con, shutdown = TRUE) # データベースの接続解除

演習課題

次の演習課題を行え。

dplyr処理演習

https://rpubs.com/tkdhss111/dplyr_exercise