dplyrでデータベース操作

※下記の文章はDatabasesを参考にしていますが、一部省略したり、現在の状況に対応するように加筆したりしています。

前置き

dplyrを使えばデータベースをデータフレームやデータテーブルと同様に操作できる。データがメモリ内で扱えるサイズであるならば、通常はデータベースを使う利点はない。データベースは扱うのが面倒だし遅いからだ。それでもdplyrでデータベースを扱いたくなる理由は「すでにそれがデータベースになってしまっているから」とか、「データが大きくてメモリに収まらない」とかそんなものだ。dplyrは主要なオープンソースRDBMSのうち3つ(sqlite、mysql、そしてpostgresql)、それとGoogleのBigQueryをサポートしている(※現在BigQueryはbigrqueryパッケージでサポートされている)。

Rでデータを扱う場合はメモリに読み込んで使うのが一般的だが、もし多量のデータを含むデータベースが手元にあったとすると、それをそのままRに取り込むのはちょっと難しいだろう。それよりも、データの一部分や集計したものを使いたいと思うことだろう。dplyrはそれを簡単に行えるようにすることを目標としている。

dplyrがデータベースをサポートする主な目的は、データベースから完璧なデータのサブセットや集計結果を一発で引き出す、というものではない。一般的には、納得のいくデータセットを得るために、RとSQLの間で何度もやりとりする必要があるだろう。しかし、RとSQLは全然似ていないので、2つの言語の間を行き来するのは大変な作業だ。

dplyrを使えばRのコードを自動的にSQLに翻訳してくれる。ただし、dplyrは全てのSQLをRで置き換えようとしているわけではない。それは難しいし、エラーも起こりやすいだろう。dplyrはSELECT文を生成するのだ。データ解析という立場からすると、SELECT文を書く機会が一番多いはずだ。

なお、この文章を最大限に活用するためには、SELECT文を使ってデータベースに問い合わせを行う方法について精通している必要があるだろう。もしSQLについてある程度の知識があって、もう少し詳しくなりたいというのであれば、次のサイトが参考になるはずだ。

• Query Planning
• 10 Easy Steps to a Complete Understanding of SQL – Java, SQL and jOOQ.

やっていく

何はともあれ試しにdplyrでデータベースを扱ってみたい、という場合はSQLiteを使うのが手軽だ。なぜなら必要なものはすでにRのパッケージに揃っているからだ。追加で何かをインストールする必要はないし、データベースサーバーを立てる必要もない。データベースを設置する場所のパスと、データベースの作成を許可する引数create=TRUEを与えてsrc_sqlite()を実行すれば、新しいデータベースが作られる。下記の例ではカレントディレクトリにmy_db.sqlite3という空っぽのデータベースが作成されるはずだ。

library(dplyr)
my_db <- src_sqlite("my_db.sqlite3", create = TRUE)

ちなみに、src_sqlite()以外にもsrc_mysql()、src_postgres()、src_bigquery()があり、それぞれの関数名が示すRDBMSに接続できる(※src_bigquery()は{bigquery}パッケージに入っている)。

my_dbにはまだデータは入っていない。nycflights13パッケージのflightsデータを入れてみよう。それにはcopy_to()関数を使うと便利だ。ちょっとアレなやり方だが、下記のような感じでデータベースにデータを取り込める。なお、このやり方は大きなデータベースには向かないので注意しよう。

install.packages("nycflights13")
library(nycflights13)
flights_sqlite <- copy_to(my_db, flights, temporary = FALSE, indexes = list(
  c("year", "month", "day"), "carrier", "tailnum"))

copy_to()は引数でインデックスを指定できる。例のようにインデックスを指定しておくことで、日付や航空会社名、飛行機のナンバーなどでデータを高速に処理できるようになる。

ちなみに、今回例として使うデータセットに限っての話だが、キャッシュされているものを次のように読み込むこともできる。

flights_sqlite <- tbl(nycflights13_sqlite(), "flights")

ともあれ、これでflights_sqliteにflightsテーブルが読み込まれた。

ちなみに、次のようにすれば任意のクエリを実行できる。

tbl(my_db, sql("SELECT * FROM flights"))

基本文法

select() filter() arrange() mutate() summarize()の5つの関数は、データベースを対象にしていないときと同じように動作する。

select(flights_sqlite, year:day, dep_delay, arr_delay)
filter(flights_sqlite, dep_delay > 240)
arrange(flights_sqlite, year, month, day)
mutate(flights_sqlite, speed = air_time / distance)
summarise(flights_sqlite, delay = mean(dep_time))

ただ、重要な違いがある。これらの関数は実際にはSQLに翻訳されてデータベース上で実行されるという点だ。そしてこれらの操作は完璧に動作するのだが、後に見るようにいくつかの制限がある。

dplyrは怠ける

データベースを相手にしているとき、dplyrは可能な限り怠ける。つまりこういうことだ。

• 明示的に求めなければRにデータを読み込まない。
• 作業は可能な限り遅らせる。操作は可能な限りまとめて、一度にデータベースに送る。

例えば次のようなコードを見てみよう。

c1 <- filter(flights_sqlite, year == 2013, month == 1, day == 1)
c2 <- select(c1, year, month, day, carrier, dep_delay, air_time, distance)
c3 <- mutate(c2, speed = distance / air_time * 60)
c4 <- arrange(c3, year, month, day, carrier)

このコードを実行した段階では、実はデータベースはまだ操作されていない。データを実際に要求するまで(例えばc4とコンソールに打ち込むそのときまで)、dplyrはSQLを生成しないし、データベースにリクエストを送ることもしない。

さらに、リクエストをしたとしてもデータは10件しか引き出されない。

> c4
Source:   query [?? x 8]
Database: sqlite 3.16.2 [/var/folders/58/lgj_q_yj6f919b_m3r0bg7jc0000gn/T//Rtmp60T5Op/nycflights13.sqlite]

    year month   day carrier dep_delay air_time distance    speed
   <int> <int> <int>   <chr>     <dbl>    <dbl>    <dbl>    <dbl>
1   2013     1     1      9E         0      189     1029 326.6667
2   2013     1     1      9E        -9       57      228 240.0000
3   2013     1     1      9E        -3       68      301 265.5882
4   2013     1     1      9E        -6       57      209 220.0000
5   2013     1     1      9E        -8       66      264 240.0000
6   2013     1     1      9E         0       40      184 276.0000
7   2013     1     1      9E         6      146      740 304.1096
8   2013     1     1      9E         0      139      665 287.0504
9   2013     1     1      9E        -8      150      765 306.0000
10  2013     1     1      9E        -6       41      187 273.6585
# ... with more rows

もし全てのデータがほしければ、collect()を使い、collect(c4)のようにする。

また、実際に発行されるクエリを知りたければshow_query()を使う。

> show_query(c4)
<SQL>
SELECT *
FROM (SELECT `year`, `month`, `day`, `carrier`, `dep_delay`, `air_time`, `distance`, `distance` / `air_time` * 60.0 AS `speed`
FROM (SELECT `year` AS `year`, `month` AS `month`, `day` AS `day`, `carrier` AS `carrier`, `dep_delay` AS `dep_delay`, `air_time` AS `air_time`, `distance` AS `distance`
FROM (SELECT *
FROM `flights`
WHERE ((`year` = 2013.0) AND (`month` = 1.0) AND (`day` = 1.0)))))
ORDER BY `year`, `month`, `day`, `carrier`

データベースがどのようにクエリを実行しようと計画しているかを知りたければexplain()が使える。SQLiteの場合にexplain()の出力の詳細を知りたければ、EXPLAIN QUERY PLANが参考になるだろう。

計算させる

計算を強制するには次の3つの手段がある。

• collect()はクエリを実行し結果をRに出力する。
• compute()はクエリを実行し結果をデータベース内の一時テーブルに出力する。
• collapse()はクエリをテーブル表現で返す。

おそらくcollect()をよく使うことになるだろう。求める処理を組み立てることができたら、collect()を使ってローカルにtbl_df形式でデータを落とせばよい。もしSQLに精通しているのであれば、compute()やcollapse()を使ってパフォーマンスを向上させることもできるだろう。

パフォーマンス

dplyrはコストの高いクエリを誤って実行しないように努力する。従って次のような点に注意が必要となる。

• クエリが何行の結果を返すかは実際にクエリが実行されるまで分からないのが普通である。従ってnrow()は常にNAを返す。
• tblをプリントした場合、最初の10行のみを表示する。
• クエリを最後まで実行しなければ、実行結果の最後の数行を得るというようなことは不可能である。従ってtail()は使えない。

SQLへの翻訳

フィルタリングや変形、集計に使われるような数学的な操作を、RからSQLに(あるいは他の言語に)翻訳するのはそれほど難しくない。

translate_sql()を使うとRとSQLの違いを確認できる。

例えば変数名はダブルクオートで、文字列はシングルクオートでエスケープされる。

> translate_sql(x)
<SQL> "x"
> translate_sql("x")
<SQL> 'x'

多くの関数は少し違った名前に変換される。

> translate_sql(x == 1 && (y < 2 || z > 3))
<SQL> "x" = 1.0 AND ("y" < 2.0 OR "z" > 3.0)
> translate_sql(x ^ 2 < 10)
<SQL> POWER("x", 2.0) < 10.0
> translate_sql(x %% 2 == 10)
<SQL> "x" % 2.0 = 10.0

dplyrは次のような関数についてSQLへの変換方法を知っている。

• 基本的な演算子: + - * / %% ^
• 数学関数: abs acos acosh asin asinh atan atan2 atanh ceiling cos cosh cot coth exp floor log log10 round sign sin sinh sqrt tan tanh
• 比較演算子: < <= != >= > == %in%
• 論理演算子: & && | || ! xor
• 基本的な集計関数: mean sum min max sd var

数値については少々注意が必要である。Rでは1ははrealであり、integerであることを明示するには1Lとする必要がある。SQLは1はintegerであり、realにするために1.0と記述する必要がある。

> translate_sql(1)
<SQL> 1.0
> translate_sql(1L)
<SQL> 1

translate_sql()に関わる基本的な技術はDomain specific languages · Advanced R.で詳細を述べている。translate_sql()はRの組み込みパースエンジンにより正しいSQLを発行するように注意深く設計されている。文字列や名前を適切にエスケープすることで、SQLインジェクションからも守られる。

データベースはRの関数を全て備えているわけではないので、当然ではあるが完全な翻訳は不可能である。例えばmean()はtrimオプションを設定することでトリム平均を求めることができるが、このような機能はデータベースで使うことはできない。

また、dplyrが変換方法を知らない関数は、そのまま渡される。これはつまり、dplyrがカバーしていないデータベース関数はそのまま入力すればそのまま使えるということである。例えば次の例で出力されるクエリはいずれもSQLiteで動作する。

> translate_sql(glob(x, y))
<SQL> GLOB("x", "y")
> translate_sql(x %like% "ab*")
<SQL> "x" LIKE 'ab*'

グループ化

SQLiteはWindow関数をサポートしていない。したがって、グループ化したテーブルに対するsummarize()の適用はとっても便利だ。グループ化しての集計はちゃんと翻訳されるのだが、データベースを対象にした場合に通常と異なるのはデータベースは常にNULLを除外するという点である。従って、na.rm=TRUEは指定する必要がない。

by_tailnum <- group_by(flights_sqlite, tailnum)
delay <- summarize(by_tailnum,
                   count = n(),
                   dist = mean(distance),
                   delay = mean(arr_delay))
delay <- filter(delay, count > 20, dist < 2000)
delay_local <- collect(delay)

他のデータベースではWindow関数がサポートされているものがある。Window関数ついては他のVignetteを参照してほしい(cf. Window functions and grouped mutate/filter)。

他のデータベースについて

ワークフローの本質的な部分はデータベースの種類によらないが、これから後のセクションでは、それぞれのデータベースについて特徴的な部分を詳しく説明する。これらのデータベースは全てクライアント・サーバーモデル、つまりコンピュータがデータベースの稼働しているコンピュータに接続する(2つのコンピュータは同一の場合もあるが、大抵は違う)というモデルである。これらのデータベースを設定し稼働させるというのは本記事の範疇を超えるが、Webを探せばやり方はいくらでも見つかるはずだ。

PostgreSQL

src_postgres()は5つの引数、すなわちdbname, host, port, userそしてpasswordを持つ。もしローカルでデフォルト設定のpostgresデータベースを動かしているのであれば、dbnameだけを指定すれば良い。ただ、大抵の場合は5つ全てを指定する必要があるだろう。dplyrはpostgresデータベースに接続するためにRPostgreSQLパッケージを使う。これはすなわち、現時点ではHerokuのようなSSL接続を要するリモートデータベースには接続できないということである。

テストのためにflightsデータのコピーを含むPostgreSQLデータベースをローカルに作成しておこう。Rで作業する前にローカルでPostgreSQLサーバーを起動しておき、nycflights13という名前のデータベースを作成しておいてほしい。この状態でデータを流し込む。

copy_to(src_postgres("nycflights13"), flights, temporary = FALSE, indexes = list(
  c("year", "month", "day"), "carrier", "tailnum"))

しかる後、データベースに接続する。

flights_postgres <- tbl(src_postgres("nycflights13"), "flights")

ちなみにPostgreSQLはSQLiteより機能が充実している。例えば次の点だ。

• 組み込み関数が豊富である。
• Window関数をサポートしている。

では、グループごとの集計がどのように行われるのか例を見てみよう。

daily <- group_by(flights_postgres, year, month, day)

# Find the most and least delayed flight each day
bestworst <- daily %>%
  select(flight, arr_delay) %>%
  filter(arr_delay == min(arr_delay) || arr_delay == max(arr_delay))
show_query(bestworst)

# Rank each flight within a daily
ranked <- daily %>%
  select(arr_delay) %>%
  mutate(rank = rank(desc(arr_delay)))
show_query(ranked)

クエリを見ると分かるように、生成されるSQLは非常に複雑であり、そのため自分でWindow関数を直接操作するということはできない。もし必要があればサブクエリを使う必要がある。

MySQLとMariaDB

MySQLとMariaDB(これはMySQLの派生である)にはsrc_mysql()を使って接続できる。これは{RMySQL}パッケージを使用している。接続の際に必要な引数はPostgreSQLのときと同じである。

機能的にはMySQLはSQLiteとPostgreSQLの中間みたいなものである。組み込み関数はたくさんあるが、Window関数はサポートしていない。

BigQuery

※前述の通り、BigQueryは現在{bigrquery}パッケージでサポートされている。

BigQueryはGoogleによってホストされているデータベースサーバーである。接続のためにはproject, dataset, billing(オプション)。最初にsrc_bigquery()を実行すると、ブラウザが開いて認証を求められる。認証情報はローカルにキャッシュされるので、この作業は1度で済む。

BigQueryはSELECT文しかサポートしていないが、データ解析の目的であればこれで十分である。BigQueryのSELECT文はPostgreSQLと同じくらいの水準をカバーしている。

データベースの選択

もしデータベースをまだ使っていなくて、これから使うのだとすれば、今回説明した全てのRDBMSをセットアップして運用してみた私の経験からいくつかのアドバイスがある。

• SQLiteは始めるのは簡単だが、Window関数がないのでデータ解析には限界がある。
• PostgreSQLは使うのはそれほど難しくなく、幅広い組み込み関数を備えている。
• MySQLとMariaDBのことを気にするのはやめよう。セットアップは苦痛だしドキュメントも標準以下だ。
• Google BigQueryは非常に大きなデータベースを扱う場合や、データベースを管理してくれるということに対して(少額の)お金を払っても良いならば利用の価値はある。