dplyrパッケージはただ速いだけではない。
そのバックエンドにデータベースを設定することで、Rで使いづらい大容量のデータをこれまで紹介したような文法で統一的に扱えるようになっている。
ここではそういったデータベースとの接続周りの話について解説する。
今回の内容は下記vignetteの情報を簡単にまとめているので詳しいことを知りたい場合は下記を参照のこと。
http://cran.rstudio.com/web/packages/dplyr/vignettes/databases.html
また、dplyrの基本関数の使い方については前に書いたのでそちらを参照されたい。 http://rpubs.com/dichika/dplyr_intro
使い方に入る前にdplyrにおけるデータベースの考え方について述べる。
dplyrではintroで紹介したような各種関数をSQLに翻訳してデータベースに投げるようになっている。
ただしなんでも翻訳するわけではなくて、原則としてSELECT文のみである。
その理由は分析やる時に投げるSQLはSELECT中心だから事足りるでしょうという割り切りにある。まあ、それ以外についても翻訳というより直接の形で投げられるようになっているので必要があればそちらで対応すればよい。
以下目次
接続にはsrc_db (dbにはmysqlやpostgres、sqliteなどデータベースの名前が入る)関数を用いる。
なお、vignetteではSQLiteを使っているが、ここではPostgreSQLを使っている。
src_postgresはPostgreSQLに接続するために裏でRPostgreSQLパッケージを用いているので事前にインストールしておくこと。これは他データベースにおいても同様。MySQLであればRMySQL、SQLiteであればRSQLiteパッケージが必要になる。
※ちなみにRMySQLはWindows環境ではインストールが面倒なので注意。
下記では、PostgreSQLに蓄積したRstudioのパッケージダウンロードログ（１年分）を取得している。
# install.packages("RPostgreSQL")
library(dplyr)
# 下記は各自の環境に従って設定
my_db <- src_postgres(dbname="mydb",
host = "localhost",
port = "5432",
user = "postgres",
password = "hoge")
rstudiolog_postgres <- tbl(my_db, "rstudiolog")
上記はデータベース内にデータが既に入っている前提だが、仮にR内のデータをデータベースに格納したい場合はcopy_to関数を使う。
しかしR上で扱えないのでデータベースに一旦格納したいわけだから今回のようなサンプルコード以外では使わないかもしれない。
# dataが格納したいR上のデータ
rstudiolog_postgres <- copy_to(my_db, data, temprary=FALSE)
上記でデータベースには接続できているので後はクエリを投げてデータを取得するだけ。
introで紹介した基本関数を組み合わせるだけでdplyrがよしなに翻訳して投げてくれる。
クエリは作っただけではデータを全件取得しているわけではなく一部のみが取得される。これをlazinessと表現している。
q <- rstudiolog_postgres %.%
group_by(package) %.%
summarise(count=n()) %.%
filter(count>10 & !is.na(package)) %.%
arrange(desc(count))
str(q)
## List of 8
## $ src :List of 3
## ..$ con :Formal class 'PostgreSQLConnection' [package "RPostgreSQL"] with 1 slots
## .. .. ..@ Id: int [1:2] 4924 0
## ..$ info :List of 8
## .. ..$ host : chr "localhost"
## .. ..$ port : chr "5432"
## .. ..$ user : chr "postgres"
## .. ..$ dbname : chr "mydb"
## .. ..$ serverVersion : chr "9.3.2"
## .. ..$ protocolVersion: int 3
## .. ..$ backendPId : int 4931
## .. ..$ rsId : list()
## ..$ disco:Reference class 'DbDisconnector' [package "dplyr"] with 3 fields
## .. ..$ con :Formal class 'PostgreSQLConnection' [package "RPostgreSQL"] with 1 slots
## .. .. .. ..@ Id: int [1:2] 4924 0
## .. ..$ name : chr "postgres"
## .. ..$ quiet: logi FALSE
## .. ..and 13 methods, of which 1 are possibly relevant:
## .. .. finalize
## ..- attr(*, "class")= chr [1:3] "src_postgres" "src_sql" "src"
## $ from :Classes 'sql', 'character' chr "(SELECT \"package\", count(*) AS \"count\"\nFROM \"rstudiolog\"\nGROUP BY \"package\") AS \"_W1\""
## $ select :List of 2
## ..$ : symbol package
## ..$ : symbol count
## $ summarise: logi FALSE
## $ mutate : logi FALSE
## $ where :List of 1
## ..$ : language count > 10 & !is.na(package)
## $ order_by :List of 1
## ..$ : language desc(count)
## $ query :Reference class 'Query' [package "dplyr"] with 5 fields
## ..$ con :Formal class 'PostgreSQLConnection' [package "RPostgreSQL"] with 1 slots
## .. .. ..@ Id: int [1:2] 4924 0
## ..$ sql :Classes 'sql', 'character' chr "SELECT \"package\", \"count\"\nFROM (SELECT \"package\", count(*) AS \"count\"\nFROM \"rstudiolog\"\nGROUP BY \"package\") AS \"| __truncated__
## ..$ .vars: chr [1:2] "package" "count"
## ..$ .res : NULL
## ..$ .nrow: NULL
## ..and 21 methods, of which 9 are possibly relevant:
## .. fetch, fetch_paged, from, ncol, nrow, run, save_into,
## .. show#envRefClass, vars
## - attr(*, "class")= chr [1:3] "tbl_postgres" "tbl_sql" "tbl"
クエリの性能分析についてはexplain関数を使う。一般的なデータベースに用意されているEXPLAIN（もしくはEXPLAIN PLAN）を投げているので使用しているデータベースによって得られる結果は変わってくる。
explain(q)
## <SQL>
## SELECT "package", "count"
## FROM (SELECT "package", count(*) AS "count"
## FROM "rstudiolog"
## GROUP BY "package") AS "_W1"
## WHERE "count" > 10.0 AND NOT("package"IS NULL)
## ORDER BY "count" DESC
##
## <PLAN>
## Sort (cost=428966.27..428978.01 rows=4693 width=15)
## Sort Key: (count(*))
## -> HashAggregate (cost=428562.76..428633.16 rows=4693 width=7)
## Filter: ((count(*))::numeric > 10.0)
## -> Seq Scan on rstudiolog (cost=0.00..394307.15 rows=4567415 width=7)
## Filter: (package IS NOT NULL)
collect関数を使ってクエリを評価することでデータを全件取得することができる。
collect(q)
## Source: local data frame [5,846 x 2]
##
## package count
## 1 plyr 252391
## 2 ggplot2 251392
## 3 colorspace 247898
## 4 digest 246043
## 5 stringr 239716
## 6 RColorBrewer 205811
## 7 reshape2 197961
## 8 scales 180794
## 9 zoo 180774
## 10 proto 177381
## .. ... ...
PostgreSQLなど一部のデータベースはwindow関数が使える。これについては稿を分けて別途紹介する。