1 はじめに

このノートでは、SQLサーバーに非常に大きなデータ(例えば観測数が1億以上、変数数十個)が格納されているときに、RStudioを使ってどのように分析を行うかについて解説していきます。想定している環境は以下のとおりです。

  1. 手元のPC:ブラウザで以下のRStudio Server
  2. RStudio Serverが入っている外部サーバー
  3. データが格納されているSQLサーバー

SQLサーバーに格納されているデータが非常に大きく、RStudio Serverにデータを落とすことができない、もしくは落としても作業に非常に時間がかかる状況があります。

一つの方法としては、SQLのクエリーを書いて、データを加工・集約した上で、最終的にRStudio Server上で図表を作成する・回帰分析をするというものになります。 この場合、SQLのクエリーを別途学ぶ必要があり、SQL初学者にはコストが高いものとなります。

本ノートでは、Rのdplyrパッケージの文法を使ってSQLデータフレームを加工できるdbplyrパッケージの活用方法について紹介していきます。

最初にdplyrの使い方について最低限の事項を説明しますが、既にご存知の方はスキップして大丈夫です。 その上で、dbplyr及び関連するデータベースパッケージについて説明していきます。 最後に、Rstudio Serverで作業する際の注意点、特にメモリ管理などについて説明します。

1.1 読む上での注意点

  • パッケージ内の関数を読み込む際には、(1) library()でパッケージを読み込んだあとにそのパッケージ内の関数を使う、(2) パッケージ名::関数名という表記で関数を呼び出す、という2つの方法があります。後者の方が冗長になりますが、どのパッケージからその関数を呼んでいるかという対応関係がわかりやすくなります。以下のノートでは2通りの表記を混ぜておりますが、ご了承下さい。
  • 多分に我流な方法になっていると思いますので、気がついた点・サジェストなどありましたら、お気軽にメールを頂ければと思います。ホームページ:https://yutatoyama.github.io/
  • あくまで個人的なノートであり、授業資料ではありません。このノートに従って発生した損害等についてはいかなる責任も負いません。

2 dplyr によるデータ整形

dplyrパッケージはtidyverseに含まれているデータ加工・整形のためのパッケージで、直感的な作業がしやすく、データ分析においては事実上標準パッケージと読んでも差し支えないでしょう。

library("tidyverse")
## -- Attaching packages ------------------------------------------------------------- tidyverse 1.3.0 --
## √ ggplot2 3.3.2     √ purrr   0.3.4
## √ tibble  3.0.3     √ dplyr   1.0.2
## √ tidyr   1.1.2     √ stringr 1.4.0
## √ readr   1.3.1     √ forcats 0.5.0
## -- Conflicts ---------------------------------------------------------------- tidyverse_conflicts() --
## x dplyr::filter() masks stats::filter()
## x dplyr::lag()    masks stats::lag()

なお、ここでは必要最低限の説明しかしておりません。dplyrの解説はオンラインで多数ありますが、Jaehyun Songさんの https://www.jaysong.net/dplyr_intro/ がオススメです。

2.1 データの変形

ここでは、Rに最初から入っているデータirisを使います。

2.1.1 変数の作成: 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)

2.1.2 観測(行)の選択: filter

ある条件を満たす行のみを取得します。STATAのif文です。 1つ目の引数にデータフレーム、2つ目以降に条件文を書きます。

iris2 <- filter(iris, Sepal.Length > 5)

2.1.3 変数(列)の選択: select

必要な変数だけをKeepします。Stataのkeepコマンドです。 1つ目の引数にデータフレーム、2つ目以降にKeepする変数名を入れます。

iris3 <- select(iris2, Sepal.Length, Petal.Width, Species)

2.2 パイプ演算子%>%

パイプ演算子は、パイプの前の要素を、パイプの後の関数の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) 

2.3 データフレームの集約

irisの各種変数を、Speciesというグループごとに集約しましょう

iris %>% 
    group_by(Species) %>% 
    summarise( mean_length = mean(Sepal.Length), 
               mean_width  = mean(Sepal.Width)) %>%
    ungroup() -> mean_iris
## `summarise()` ungrouping output (override with `.groups` argument)
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.4 データフレームのマージ(結合)

2つのデータフレームを、特定の変数をキーとして結合しましょう。 説明は少し力つきたので、細かい説明は以下のページを参照してください。わかりやすいです。 https://qiita.com/matsuou1/items/b1bd9778610e3a586e71

個人的なアドバイスとしては、left_join()だけを使うようにするというものです。4つもありますが、多くの場合はleft_join()で事足ります。あと、left_join()はパイプ演算にも組み込みやすいです。

3 dbplyrを用いたSQLサーバー上のデータの処理

それでは、dplyrパッケージを活用して、SQLサーバー上のデータの処理を

3.1 下準備: SQLデータベースへの接続

まずSQLデータベースへ接続しましょう。接続には、データベース接続用のDBIパッケージを使います。 ここでは、具体的にPostgreSQLサーバーを念頭において作業します。以下のパッケージをインストール及びロードします。

library('DBI')
library('RPostgreSQL')
library('dbplyr')
## 
## Attaching package: 'dbplyr'
## The following objects are masked from 'package:dplyr':
## 
##     ident, sql
library('RSQLite')

なお、RPostgreSQLを読み込むとDBIも自動に読み込まれるため、library('DBI')はなくても大丈夫です。また、RSQLiteはこのノートで「ローカルなSQLサーバー」を用意するためだけに使うので、実際の環境での分析の際には読み込む必要はない(と思います)。

では、まずRをPostgreSQLサーバに接続しましょう。接続にはdbConnect()を使います。 関数の中身には接続情報を適宜入れます。

con <- dbConnect(PostgreSQL(), host="XXXX", 
                 port=5432, user= "postgres", password="*****", dbname="db_name")

ここでできた変数conには当該サーバーへの接続情報が入っています。

さて、本ノートでは便宜上、パソコン内に仮置の「SQLサーバー」を作成し、そこへの接続を用意しましょう。

con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")

今この仮置の「SQLサーバー」(これをin-memory serverと呼びます)には何も入っていません。ですので、copy_to()を使って、mtcarsirisというRに最初から入っているデータフレームを入れましょう。

dplyr::copy_to(con, mtcars)
dplyr::copy_to(con, iris)

では、この接続したSQLサーバーの中身を見てみましょう。

dbListTables(con)  # テーブル一覧取得
## [1] "iris"         "mtcars"       "sqlite_stat1" "sqlite_stat4"

ここで、サーバーの中に入っている要素をテーブルと呼びます。irisとmtcarsがテーブルとして入っているのがわかります。(他の2つはひとまず無視しましょう)

3.2 SQLサーバーでのデータの加工

では、この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()

3.3 dbplyr() パッケージ

基本的な文法はdplyrと同じですが、いくつか特殊なポイントがあります。 以下の三段階で説明していきます。

  1. SQLサーバー上のテーブルへの「リンク」を作成
  2. dplyr文法で抽出・加工を指示する
  3. 上の指示を実際に「SQLサーバー上で実行」し、「Rにダウンロード」する。

3.3.1 Step 1: テーブルへの参照を作成

まず、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へのリンクになっているのです。

3.3.2 Step 2: データの加工・抽出の指示

実際に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)

3.3.3 Step 3: 指示を「SQLサーバーで実行」し、「Rにダウンロード」する。

上で書いたdt_car2とdt_car3はあくまでSQLクエリであり、実際のデータとはなっていません。 そのクエリを実行をどうするかについてここで説明していきます。

まず、「クエリを実行し、実行結果をSQLサーバーに保存する」関数としてcompute()があります。

compute(dt_car3, name = "car_computed")
## # Source:   table<car_computed> [?? x 5]
## # Database: sqlite 3.30.1 [: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に落ちているのが確認できます。

3.4 細かいTips

以上がdbplyr()を使った作業の大枠のStepです。 基本的には、dbplyr()を使って加工・抽出・集約を繰り返し、十分テーブルが小さくなった時点で、collect()で手元に落とすというのが肝です。

大原則は、collect()する前に、SQLで極限まで加工することです。同じ加工でも、SQL上でやった方が、Rstudioでやるよりも早いです。

(以下個人的な経験。あくまで参考。) 観測数が1億・変数(列数)が4のデータフレームで、RStudio上でおおよそ1GBになりました。このフレームをRStudio上で加工することも可能でしたが、そこそこ時間がかかる、そしてサーバー落ちにつながるなどもしばしばありました。なお、メモリは32GBです。

以下では、私が作業していて気がついたコツのようなものを説明していきます。

3.4.1 Tips 1: compute/collectする前のデータの様子を見る方法

中身の先頭をみる方法としてhead()があります。

head(dt_car3, 3)
## # Source:   lazy query [?? x 5]
## # Database: sqlite 3.30.1 [: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.30.1 [:memory:]
##       n
##   <int>
## 1     8

3.4.2 Tips 2: どこの部分で計算に時間がかかるか?

少し細かい点ですが、計算や分析をスムーズに行うために重要な点です。

dbplyr()での作業は

  1. dplyrの文法で抽出加工集約の指示を出す
  2. その指示を、SQLサーバー上で実行する
  3. 実行した結果できるテーブルをRstudioに落とす

という3ステップです。

1つめのステップはコードを書くだけなのでほとんど時間はかかりません。たとえば、dt_car3はデータフレームそのものではなく、あくまでSQLクエリが入っているだけです。

2つ目のステップでは、実際にクエリを実行します。データのサイズやクエリの複雑さにもよりますが、そこそこ時間がかかります。これがcompute()に対応する部分です。

そして3つ目のステップが肝です。ここで落とすテーブルが大きい場合、実行に長時間かかります。同時に、メモリの消費も非常に大きくなります。ですので、ここで落とすファイルを以下に小さくするかが、作業において重要となってきます。

(個人的経験)例えば同じクエリをcompute()してSQLサーバーにテーブルで保管する場合と、collect()をしてRにダウンロードする場合、それぞれを比較した際、前者が120秒程度で終わったのに対し、後者では600秒くらいかかったこともありました。

3.4.3 Tips 3: アウトプットは極力変数に格納しよう。

具体例を出して説明します。

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.30.1 [: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したテーブルをそのままコンソールに流していることになります。こうすると、せっかく時間をかけてダウンロードしたものが無駄になってしまいます。必ずアウトプットを変数に格納するようにしましょう。

3.4.4 Tips 4: よく使う集計方法

あるグループに該当する観測数が何個あるかを示すものです。

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    14

3.4.5 Tips 5: compute()の使い所及びマージの際の注意点

SQLサーバー上のテーブルから加工した新たなテーブル同士を結合(マージ)するときなどにはcomputeしてSQLサーバーに一旦テーブルを置いておくと良いでしょう。

なお、dbplyrを使ってテーブル同士をマージする際には、そのテーブルは両方ともSQLサーバーにないといけません。つまり、SQLサーバーにあるテーブルと、RStudio上にあるテーブルを結合はできません。もしRstudioで作ったテーブルを、SQL上にあるものとマージしたい場合には、Rstudioのものをcopy_to関数でSQLサーバーに移す必要があります。

4 RStudio Serverの作業時の注意点。特にメモリ管理について。

  1. SQLからRstudioに落としたデータが大きい場合、メモリ消費量が非常に大きくなります。Rstudio server環境では、サーバー落ちなどのトラブルに繋がる可能性が高くなります。
  2. 大きいファイルを落とした場合(目安としてはサンプルサイズ数千万で変数が5-6個以上)には、メモリの消費量を常に気にするようにしましょう。
  3. メモリ消費量は”Terminal"というパネルでtopとタイプすると、現在稼働しているユーザーのメモリ消費量を見ることができます。
  4. メモリ消費量が多い場合(例えば50%以上)には、大きなデータフレームを変数から消すようにしましょう。ただし、rm()で変数を消しただけでは、メモリが開かない場合があります。その時は、gc()を2回やりましょう。
gc(reset = TRUE)
gc(reset = TRUE)
  1. gcをやってもメモリ消費量が減らない場合があります。そのときには、一旦Rをquitして入り直すことを進めます。

5 今後の課題

  • SQL上で回帰分析を行うには?特に大量の固定効果をコントロールできるような回帰分析を行いたい。(Rではlfeパッケージやfixestパッケージなどがある。)