RMarkdownファイルの中でSQLを実行する際のtips ~knitr language engine

公式ページに書いている内容なのだけど、覚え書き。

RMarkdownファイルでSQLを実行させるという状況があったとき、{DBI}や{odbc} といった各種のデータベース接続用パッケージや{dplyr}の接続関数を利用してデータを呼び出しても良いけど、実はチャンクコード内でSQL直書きできるという話。

過去に

uribo.hatenablog.com

を書いたが、こっちの方がより簡単。

まずはデータベースへの接続を行う。

```{r}
library(DBI)
con <- dbConnect(odbc::odbc(), "MySQL")
```

データベースへ接続された状態がconオブジェクトに保存された。このコネクションを利用して、RMarkdown内で生SQLを実行させていく。肝心なのはチャンク内でエンジンをデフォルトのrではなくてsqlにしておくこと。そしてチャンクオプションのconnectionにconオブジェクトを指定することの2点。

```{sql, connection = con, max.print = 10}
SELECT * FROM mtcars
```

出力件数はSQLのLIMITを使っても良いし、チャンクオプションのmax.printで制御しても良い。

```{sql, connection = con, output.var="sql.mtcars"}
SELECT * FROM mtcars
```

のようにoutput.varオプションを使うとSQLで取得したデータをRオブジェクトとして扱えるようになるので便利。一方でRオブジェクトの値をSQLチャンクに渡すことも可能で、その際は以下のようなコードを書く。手順としては、Rオブジェクトを作り、SQLチャンク内でSQLに引き渡すRオブジェクト名の前に?をつけるだけ。

```{r}
limits <- 7
```

```{sql, connection = con}
SELECT * FROM mtcars LIMIT ?limits
```

コネクションを固定する

都度SQLチャンクにconnectionを指定するのも手間なので、接続状態を維持しておきたいという時にはknitr::opts_chunk()の値にコネクションを残しておけば良い。こうしておくことでSQLチャンクでは常にconnectionで参照しているRオブジェクトを対象とするようになるのでconnectionを書く必要がなくなる。

```{r}
library(DBI)
con <- dbConnect(odbc::odbc(), "MySQL")
knitr::opts_chunk$set(connection = "con")
```

ちなみにknitrの言語エンジンでは、SQL以外にも複数の言語が実行できる環境が整っている。

• Python
• SQL
• Bash
• Rcpp
• Stan
• JavaScript
• CSS

チャンクで言語を指定するのとengineによる指定による違いがわかっていないのだが。ここにあるものの一部はチャンクで使えるみたい

Language engines - Yihui Xie | 谢益辉

Enjoy!