このノートでは、SQLサーバーに非常に大きなデータ(例えば観測数が1億以上)が格納されているときに、RStudioを使ってどのように分析を行うかについて解説していきます。想定している環境は以下のとおりです。
SQLサーバーに格納されているデータが非常に大きく、RStudio Serverにデータを落とすことができない、もしくは落としても作業に非常に時間がかかる状況があります。
一つの方法としては、SQLのクエリーを書いて、データを加工・集約した上で、最終的にRStudio Server上で図表を作成する・回帰分析をするというものになります。 この場合、SQLのクエリーを別途学ぶ必要があり、SQL初学者にはコストが高いものとなります。
本ノートでは、Rのdplyrパッケージの文法を使ってSQLデータフレームを加工できるdbplyrパッケージの活用方法について紹介していきます。
最初にdplyrの使い方について最低限の事項を説明しますが、既にご存知の方はスキップして大丈夫です。 その上で、dbplyr及び関連するデータベースパッケージについて説明していきます。 最後に、Rstudio Serverで作業する際の注意点、特にメモリ管理などについて説明します。
library()でパッケージを読み込んだあとにそのパッケージ内の関数を使う、(2) パッケージ名::関数名という表記で関数を呼び出す、という2つの方法があります。後者の方が冗長になりますが、どのパッケージからその関数を呼んでいるかという対応関係がわかりやすくなります。以下のノートでは2通りの表記を混ぜておりますが、ご了承下さい。dplyr によるデータ整形dplyrパッケージはtidyverseに含まれているデータ加工・整形のためのパッケージで、直感的な作業がしやすく、データ分析においては事実上標準パッケージと読んでも差し支えないでしょう。
library("tidyverse")## -- Attaching packages --------------------------------------- tidyverse 1.3.1 --## v ggplot2 3.3.3 v purrr 0.3.4
## v tibble 3.1.1 v dplyr 1.0.5
## v tidyr 1.1.3 v stringr 1.4.0
## v readr 1.4.0 v forcats 0.5.1## -- Conflicts ------------------------------------------ tidyverse_conflicts() --
## x dplyr::filter() masks stats::filter()
## x dplyr::lag() masks stats::lag()なお、ここでは必要最低限の説明しかしておりません。dplyrの解説はオンラインで多数ありますが、Jaehyun Songさんの https://www.jaysong.net/dplyr_intro/ がオススメです。
ここでは、Rに最初から入っているデータirisを使います。
mutate変数を作成します。STATAでのgenコマンドです。 1つ目の引数にデータフレーム、2つ目の引数に「新しい変数名 ＝ 変数の定義」を書きます。
dt <- mutate(iris, lengthsq = Sepal.Length^2)ここでは、新しいデータフレームdtを作っており、その中には元の変数に加えて、lengthsqが入っています。
同じことをやる別の書き方として
mutate(iris, lengthsq = Sepal.Length^2) -> dt があります。
また、元のデータフレームirisに上書きしたい場合には、irisそのものをアウトプットを格納する変数とします。
iris <- mutate(iris, lengthsq = Sepal.Length^2)filterある条件を満たす行のみを取得します。STATAのif文です。 1つ目の引数にデータフレーム、2つ目以降に条件文を書きます。
iris2 <- filter(iris, Sepal.Length > 5)select必要な変数だけをKeepします。Stataのkeepコマンドです。 1つ目の引数にデータフレーム、2つ目以降にKeepする変数名を入れます。
iris3 <- select(iris2, Sepal.Length, Petal.Width, Species)%>%パイプ演算子は、パイプの前の要素を、パイプの後の関数の1つ目の引数に入れるというものです。 これまで紹介してきた関数はすべて1つ目の引数としてデータフレームをとります。 従って、これらの操作はすべてパイプ演算子でつなぐことができます。
iris %>%
mutate( lengthsq = Sepal.Length^2 ) %>%
filter( Sepal.Length > 5) %>%
select( Sepal.Length, Petal.Width, Species) -> iris3
# もしくは、最終的なアウトプットである変数iris3を最初に書く
iris3 <- iris %>%
mutate( lengthsq = Sepal.Length^2 ) %>%
filter( Sepal.Length > 5) %>%
select( Sepal.Length, Petal.Width, Species) irisの各種変数を、Speciesというグループごとに集約しましょう
iris %>%
group_by(Species) %>%
summarise( mean_length = mean(Sepal.Length),
mean_width = mean(Sepal.Width)) %>%
ungroup() -> mean_iris
print(mean_iris)## # A tibble: 3 x 3
## Species mean_length mean_width
## <fct> <dbl> <dbl>
## 1 setosa 5.01 3.43
## 2 versicolor 5.94 2.77
## 3 virginica 6.59 2.97ここでは、Speciesごとの平均値ということでアウトプットが3行となっています。
では、もしデータを集約せずに、あくまで各行ごとに「該当するグループの平均値」という変数を新たに追加する場合はどうしましょう？このときはmutateを使います。
iris %>%
group_by(Species) %>%
mutate( mean_length = mean(Sepal.Length),
mean_width = mean(Sepal.Width)) %>%
ungroup() -> mutate_iris
print(mutate_iris)## # A tibble: 150 x 8
## Sepal.Length Sepal.Width Petal.Length Petal.Width Species lengthsq
## <dbl> <dbl> <dbl> <dbl> <fct> <dbl>
## 1 5.1 3.5 1.4 0.2 setosa 26.0
## 2 4.9 3 1.4 0.2 setosa 24.0
## 3 4.7 3.2 1.3 0.2 setosa 22.1
## 4 4.6 3.1 1.5 0.2 setosa 21.2
## 5 5 3.6 1.4 0.2 setosa 25
## 6 5.4 3.9 1.7 0.4 setosa 29.2
## 7 4.6 3.4 1.4 0.3 setosa 21.2
## 8 5 3.4 1.5 0.2 setosa 25
## 9 4.4 2.9 1.4 0.2 setosa 19.4
## 10 4.9 3.1 1.5 0.1 setosa 24.0
## # ... with 140 more rows, and 2 more variables: mean_length <dbl>,
## # mean_width <dbl>なお、group_byをして集約した後には、必ずungroup()をしましょう。しないと変なことが起きたりします。
2つのデータフレームを、特定の変数をキーとして結合しましょう。 説明は少し力つきたので、細かい説明は以下のページを参照してください。わかりやすいです。 https://qiita.com/matsuou1/items/b1bd9778610e3a586e71
個人的なアドバイスとしては、left_join()だけを使うようにするというものです。4つもありますが、多くの場合はleft_join()で事足ります。あと、left_join()はパイプ演算にも組み込みやすいです。
それでは、dplyrパッケージを活用して、SQLサーバー上のデータの処理を
まずSQLデータベースへ接続しましょう。接続には、データベース接続用のDBIパッケージを使います。 ここでは、具体的にPostgreSQLサーバーを念頭において作業します。以下のパッケージをインストール及びロードします。
library('DBI')
library('RPostgreSQL')
library('dbplyr')##
## Attaching package: 'dbplyr'## The following objects are masked from 'package:dplyr':
##
## ident, sqllibrary('RSQLite')なお、RPostgreSQLを読み込むとDBIも自動に読み込まれるため、library('DBI')はなくても大丈夫です。また、RSQLiteはこのノートで「ローカルなSQLサーバー」を用意するためだけに使うので、実際の環境での分析の際には読み込む必要はない(と思います)。
では、まずRをPostgreSQLサーバに接続しましょう。接続にはdbConnect()を使います。 関数の中身には接続情報を適宜入れます。
con <- dbConnect(PostgreSQL(), host="XXXX",
port=9999,
user= rstudioapi::showPrompt("UserID", "Put your userID"),
password=rstudioapi::askForPassword(""),
dbname="db_name")ここで、dbConnect関数におけるuserとpasswordについては、ポップアップ画面において入力する形になります。決してコードにユーザーIDやパスワードを直書きしてはいけません。ここでできた変数conには当該サーバーへの接続情報が入ることになります。
さて、本ノートでは便宜上、パソコン内に仮置の「SQLサーバー」を作成し、そこへの接続を用意しましょう。
con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")今この仮置の「SQLサーバー」（これをin-memory serverと呼びます）には何も入っていません。ですので、copy_to()を使って、mtcarsとirisというRに最初から入っているデータフレームを入れましょう。
dplyr::copy_to(con, mtcars)
dplyr::copy_to(con, iris)では、この接続したSQLサーバーの中身を見てみましょう。
dbListTables(con) # テーブル一覧取得## [1] "iris" "mtcars" "sqlite_stat1" "sqlite_stat4"ここで、サーバーの中に入っている要素をテーブルと呼びます。irisとmtcarsがテーブルとして入っているのがわかります。(他の2つはひとまず無視しましょう)
では、このSQLサーバーに入ったデータをどのようにしてRで加工・分析していきましょう？ 一番単純であり、実践で決してやってはいけないことは、このテーブルそのものをSQLサーバーからRへダウンロードすることです。DBI::dbReadTableを使いましょう。
cars_db = DBI::dbReadTable(con, "mtcars")こうすることで、SQLサーバー内にあったmtcarsというテーブルが、cars_dbという変数でR上で利用可能になっていることがわかります。 しかしながら、実践上はSQLサーバー内のテーブルは非常に大きいことが多々あり、元のデータを手元にダウンロードすることはほぼほぼ不可能です。 (逆にいうと、元データが手元で操作可能なサイズであればわざわざSQLサーバーにデータが格納されていることはありません。)
そこで、SQLサーバーからデータを呼び出す際には、クエリと呼ばれるデータ抽出・操作のための命令を書くこととなります。クエリ自体はSQLにおける文法ですが、Rにおいてもクエリを実行するための関数があります。
以下ではdbGetQuery関数を使います。1つ目の引数がサーバーへの接続con、2つ目が実行したいクエリを文字列で書きます。
cars_2 <- dbGetQuery(con, "SELECT * FROM mtcars WHERE cyl = 8")ここでは、mtcarsというテーブルから、“cyl = 8”という条件を満たすものだけを抽出するという作業を行っています。抽出したデータはcars_2という変数でRに保存されます。
ということで、SQLに詳しい方でしたら、説明はここで終了です。 しかしながら、Rユーザーの多くはSQLの使い方に慣れているとは限りませんし、また、より複雑な抽出やテーブル間の結合を行う場合、SQLで書くとよりクエリが複雑になっていきます。
そこでdbplyr()パッケージの出番です。このパッケージはdplyrと同じ文法でSQLデータベース上のテーブルの加工・抽出を行います。より具体的には、ユーザーがdplyrの文法で行いたい加工抽出を書くと、それをSQLのクエリに変換し、SQLサーバー上で実行するという仕組みになっています。
なお、dbplyrで対応できないような加工もありますので、SQLのクエリの文法で一部コードを書く必要も出てくるかもしれません。dbplyr()
dbplyr() パッケージ基本的な文法はdplyrと同じですが、いくつか特殊なポイントがあります。 以下の三段階で説明していきます。
まず、SQLサーバー上にあるテーブルへの「参照」を作ります。
dt_car = tbl(con, "mtcars")ここでdt_carという変数ができました。さて、ここで注意すべきはdt_carはデータフレームそのものではなく、SQLサーバー内にあるテーブルへの参照となっているということです。すなわち、この時点で、mtcarsのデータそのものがRに落ちているわけではないのです。
例えば、dt_carのなかの変数mpgをベクトルとして取得してみましょう。すると、
dt_car$mpg## NULLとなり、NULLが返ってきます。これは、dt_carにはデータの中身自体が入っていないためです。 あくまでdt_carはSQL内のテーブルmtcarsへのリンクになっているのです。
実際にSQLサーバーからRにどのようにデータを落とすかは後にして、まずdbplyrを用いたデータの加工について説明していきましょう。例えば、cyl=8のもののみを抽出するという作業はfilterを使ってできます。
dt_car %>%
filter(cyl == 8) -> dt_car2ここでfilterを適用したあとの結果をdt_car2として保存しています。 このdt_car2も上と同様、データそのものではありません。 実はこのdt_car2には、filterをかける作業に該当するSQLクエリが含まれているのです。 これを見るために、show_queryを使いましょう。
show_query(dt_car2)## <SQL>
## SELECT *
## FROM `mtcars`
## WHERE (`cyl` = 8.0)このクエリ文は、前にdbGetQueryで書いたものとほぼ同じですね。
ということで、dplyr()を使うことで、それと同義のクエリ指示を自動に作成してくれるのがdplyr()の肝となります。より細かい＆複雑なクエリ指示も書くことができます。
dt_car %>%
filter(cyl == 8) %>%
filter(mpg > 15) %>%
select(mpg, cyl, disp, hp) %>%
mutate( mpg_squared = mpg*mpg) -> dt_car3
show_query(dt_car3)## <SQL>
## SELECT `mpg`, `cyl`, `disp`, `hp`, `mpg` * `mpg` AS `mpg_squared`
## FROM (SELECT *
## FROM `mtcars`
## WHERE (`cyl` = 8.0))
## WHERE (`mpg` > 15.0)上で書いたdt_car2とdt_car3はあくまでSQLクエリであり、実際のデータとはなっていません。 そのクエリを実行をどうするかについてここで説明していきます。
まず、「クエリを実行し、実行結果をSQLサーバーに保存する」関数としてcompute()があります。
compute(dt_car3, name = "car_computed")## # Source: lazy query [?? x 5]
## # Database: sqlite 3.35.5 [:memory:]
## mpg cyl disp hp mpg_squared
## <dbl> <dbl> <dbl> <dbl> <dbl>
## 1 18.7 8 360 175 350.
## 2 16.4 8 276. 180 269.
## 3 17.3 8 276. 180 299.
## 4 15.2 8 276. 180 231.
## 5 15.5 8 318 150 240.
## 6 15.2 8 304 150 231.
## 7 19.2 8 400 175 369.
## 8 15.8 8 351 264 250.ここで、SQLサーバーの中身を見てみましょう。
dbListTables(con) # テーブル一覧取得## [1] "car_computed" "iris" "mtcars" "sqlite_stat1" "sqlite_stat4"ということで、新しいテーブルcar_computedができているのがわかります。 ここで留意点としては、ここで作ったcar_computedは「一時的」なテーブルであり、DBI::dbConnectなどでSQLサーバーへ接続し直したりするとテーブルは消えています。
では、手元のRStudioにダウンロードして変数として扱うにはどうすれば良いでしょうか？ 一つは、computeしてSQLサーバーに作成したテーブルをdbReadTableで落とすという方法です。 もう一つの方法が、computeとダウンロードを同時に行うcollect()というものです。
dt_car3 %>%
collect() -> computed_car
#他の書き方として
# computed_car <- collect(dt_car3)すると、computed_carという新しいデータフレームがRに落ちているのが確認できます。
以上がdbplyr()を使った作業の大枠のStepです。 基本的には、dbplyr()を使って加工・抽出・集約を繰り返し、十分テーブルが小さくなった時点で、collect()で手元に落とすというのが肝です。
大原則は、collect()する前に、SQLで極限まで加工することです。同じ加工でも、SQL上でやった方が、Rstudioでやるよりも早いです。
(以下個人的な経験。あくまで参考。) 観測数が1億・変数（列数）が４のデータフレームで、RStudio上でおおよそ1GBになりました。このフレームをRStudio上で加工することも可能でしたが、そこそこ時間がかかる、そしてサーバー落ちにつながるなどもしばしばありました。なお、メモリは32GBです。
以下では、私が作業していて気がついたコツのようなものを説明していきます。
中身の先頭をみる方法としてhead()があります。
head(dt_car3, 3)## # Source: lazy query [?? x 5]
## # Database: sqlite 3.35.5 [:memory:]
## mpg cyl disp hp mpg_squared
## <dbl> <dbl> <dbl> <dbl> <dbl>
## 1 18.7 8 360 175 350.
## 2 16.4 8 276. 180 269.
## 3 17.3 8 276. 180 299.なお、ここでは実際にSQLサーバー上でdt_car3のクエリを実行し、実行した結果の先頭3行のみを返しています。クエリをSQLで実行しているので時間が多少かかるかもしれませんが、Rの方に落としているのはわずか3行分なのでダウンロードにはほとんど時間がかかっていません。（この点はTips 2でも触れます。）
また、変数の長さを見たい場合にはcount関数を使います。
count(dt_car3)## # Source: lazy query [?? x 1]
## # Database: sqlite 3.35.5 [:memory:]
## n
## <int>
## 1 8少し細かい点ですが、計算や分析をスムーズに行うために重要な点です。
dbplyr()での作業は
という3ステップです。
1つめのステップはコードを書くだけなのでほとんど時間はかかりません。たとえば、dt_car3はデータフレームそのものではなく、あくまでSQLクエリが入っているだけです。
2つ目のステップでは、実際にクエリを実行します。データのサイズやクエリの複雑さにもよりますが、そこそこ時間がかかります。これがcompute()に対応する部分です。
そして3つ目のステップが肝です。ここで落とすテーブルが大きい場合、実行に長時間かかります。同時に、メモリの消費も非常に大きくなります。ですので、ここで落とすファイルを以下に小さくするかが、作業において重要となってきます。
(個人的経験)例えば同じクエリをcompute()してSQLサーバーにテーブルで保管する場合と、collect()をしてRにダウンロードする場合、それぞれを比較した際、前者が120秒程度で終わったのに対し、後者では600秒くらいかかったこともありました。
具体例を出して説明します。
dt_car %>%
filter(cyl == 8) %>%
filter(mpg > 15) %>%
select(mpg, cyl, disp, hp) %>%
mutate( mpg_squared = mpg*mpg) -> dt_car3
# 別の書き方
dt_car3 <- dt_car %>%
filter(cyl == 8) %>%
filter(mpg > 15) %>%
select(mpg, cyl, disp, hp) %>%
mutate( mpg_squared = mpg*mpg) これは、加工した結果をdt_car3に保存しています。 では、最後の-> dt_car3がなかった場合どうなるでしょうか？
dt_car %>%
filter(cyl == 8) %>%
filter(mpg > 15) %>%
select(mpg, cyl, disp, hp) %>%
mutate( mpg_squared = mpg*mpg) ## # Source: lazy query [?? x 5]
## # Database: sqlite 3.35.5 [:memory:]
## mpg cyl disp hp mpg_squared
## <dbl> <dbl> <dbl> <dbl> <dbl>
## 1 18.7 8 360 175 350.
## 2 16.4 8 276. 180 269.
## 3 17.3 8 276. 180 299.
## 4 15.2 8 276. 180 231.
## 5 15.5 8 318 150 240.
## 6 15.2 8 304 150 231.
## 7 19.2 8 400 175 369.
## 8 15.8 8 351 264 250.これは内部的には、SQLサーバー上で「クエリを実行」していることになっています。 このノートの例では非常に小さいデータセットなので問題ありませんが、大きいデータの場合にはクエリの実行にも時間がかかります。
これはcollect()をする際により重要となります。 例えば、
dt_car3 %>%
collect() -> computed_carはcollect()したものをcomputed_carに保存していますが、
dt_car3 %>%
collect() ## # A tibble: 8 x 5
## mpg cyl disp hp mpg_squared
## <dbl> <dbl> <dbl> <dbl> <dbl>
## 1 18.7 8 360 175 350.
## 2 16.4 8 276. 180 269.
## 3 17.3 8 276. 180 299.
## 4 15.2 8 276. 180 231.
## 5 15.5 8 318 150 240.
## 6 15.2 8 304 150 231.
## 7 19.2 8 400 175 369.
## 8 15.8 8 351 264 250.の場合は、collectしたテーブルをそのままコンソールに流していることになります。こうすると、せっかく時間をかけてダウンロードしたものが無駄になってしまいます。必ずアウトプットを変数に格納するようにしましょう。
あるグループに該当する観測数が何個あるかを示すものです。
dt_car %>%
group_by(cyl) %>%
tally() %>%
collect() -> cyl_group
print(cyl_group)## # A tibble: 3 x 2
## cyl n
## <dbl> <int>
## 1 4 11
## 2 6 7
## 3 8 14SQLサーバー上のテーブルから加工した新たなテーブル同士を結合（マージ）するときなどにはcomputeしてSQLサーバーに一旦テーブルを置いておくと良いでしょう。
なお、dbplyrを使ってテーブル同士をマージする際には、そのテーブルは両方ともSQLサーバーにないといけません。つまり、SQLサーバーにあるテーブルと、RStudio上にあるテーブルを結合はできません。もしRstudioで作ったテーブルを、SQL上にあるものとマージしたい場合には、Rstudioのものをcopy_to関数でSQLサーバーに移す必要があります。
topとタイプすると、現在稼働しているユーザーのメモリ消費量を見ることができます。rm()で変数を消しただけでは、メモリが開かない場合があります。その時は、gc()を2回やりましょう。gc(reset = TRUE)
gc(reset = TRUE)gcをやってもメモリ消費量が減らない場合があります。そのときには、一旦Rstudioを再起動することを勧めます。