数据库连接

首先安装 PostgreSQL 的 ODBC 驱动

apt-get install odbc-postgresql

查看配置系统文件路径

odbcinst -j 
unixODBC 2.3.6
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

不推荐修改全局配置文件，可设置 ODBCSYSINI 环境变量指定配置文件路径，如 ODBCSYSINI=~/ODBC http://www.unixodbc.org/odbcinst.html

安装完驱动程序，/etc/odbcinst.ini 文件内容自动更新，我们可以不必修改，如果你想自定义不妨手动修改，我们查看在 R 环境中注册的数据库，可以看到 PostgreSQL 的驱动已经配置好

odbc::odbcListDrivers()
                 name   attribute                                    value
1     PostgreSQL ANSI Description    PostgreSQL ODBC driver (ANSI version)
2     PostgreSQL ANSI      Driver                             psqlodbca.so
3     PostgreSQL ANSI       Setup                          libodbcpsqlS.so
4     PostgreSQL ANSI       Debug                                        0
5     PostgreSQL ANSI     CommLog                                        1
6     PostgreSQL ANSI  UsageCount                                        1
7  PostgreSQL Unicode Description PostgreSQL ODBC driver (Unicode version)
8  PostgreSQL Unicode      Driver                             psqlodbcw.so
9  PostgreSQL Unicode       Setup                          libodbcpsqlS.so
10 PostgreSQL Unicode       Debug                                        0
11 PostgreSQL Unicode     CommLog                                        1
12 PostgreSQL Unicode  UsageCount                                        1

系统配置文件 /etc/odbcinst.ini 已经包含有 PostgreSQL 的驱动配置，无需再重复配置

[PostgreSQL ANSI]
Description=PostgreSQL ODBC driver (ANSI version)
Driver=psqlodbca.so
Setup=libodbcpsqlS.so
Debug=0
CommLog=1
UsageCount=1

[PostgreSQL Unicode]
Description=PostgreSQL ODBC driver (Unicode version)
Driver=psqlodbcw.so
Setup=libodbcpsqlS.so
Debug=0
CommLog=1
UsageCount=1

只需将如下内容存放在 ~/.odbc.ini 文件中，

[PostgreSQL]
Driver              = PostgreSQL Unicode
Database            = postgres
Servername          = 192.168.99.101
UserName            = postgres
Password            = default
Port                = 8080

最后，一行命令 DNS 配置连接 https://github.com/r-dbi/odbc 这样就实现了代码中无任何敏感信息，这里为了展示这个配置过程故而把相关信息公开。

注意下面的内容需要在容器中运行， Windows 环境下的配置 PostgreSQL 的驱动有点麻烦就不搞了，意义也不大，现在数据库基本都是跑在 Linux 系统上

docker-machine.exe ip default 可以获得本地 Docker 的 IP，比如 192.168.99.101。 Travis 上 ip addr 可以查看 Docker 的 IP，如 172.17.0.1

library(DBI)
con <- dbConnect(RPostgres::Postgres(),
  dbname = "postgres",
  host = ifelse(is_on_travis, Sys.getenv("DOCKER_HOST_IP"), "192.168.99.101"),
  port = 8080,
  user = "postgres",
  password = "default"
)
library(DBI)
con <- dbConnect(odbc::odbc(), "PostgreSQL")

列出数据库中的所有表

dbListTables(con)
## character(0)

第一次启动从 Docker Hub 上下载的镜像，默认的数据库是 postgres 里面没有任何表，所以将 R 环境中的 mtcars 数据集写入 postgres 数据库

将数据集 mtcars 写入 PostgreSQL 数据库中，基本操作，写入表的操作也不能缓存，即不能缓存数据库中的表 mtcars

dbWriteTable(con, "mtcars", mtcars, overwrite = TRUE)

现在可以看到数据表 mtcars 的各个字段

dbListFields(con, "mtcars")
##  [1] "row_names" "mpg"       "cyl"       "disp"      "hp"       
##  [6] "drat"      "wt"        "qsec"      "vs"        "am"       
## [11] "gear"      "carb"

最后执行一条 SQL 语句

res <- dbSendQuery(con, "SELECT * FROM mtcars WHERE cyl = 4") # 发送 SQL 语句
dbFetch(res) # 获取查询结果
##         row_names  mpg cyl  disp  hp drat    wt  qsec vs am gear carb
## 1      Datsun 710 22.8   4 108.0  93 3.85 2.320 18.61  1  1    4    1
## 2       Merc 240D 24.4   4 146.7  62 3.69 3.190 20.00  1  0    4    2
## 3        Merc 230 22.8   4 140.8  95 3.92 3.150 22.90  1  0    4    2
## 4        Fiat 128 32.4   4  78.7  66 4.08 2.200 19.47  1  1    4    1
## 5     Honda Civic 30.4   4  75.7  52 4.93 1.615 18.52  1  1    4    2
## 6  Toyota Corolla 33.9   4  71.1  65 4.22 1.835 19.90  1  1    4    1
## 7   Toyota Corona 21.5   4 120.1  97 3.70 2.465 20.01  1  0    3    1
## 8       Fiat X1-9 27.3   4  79.0  66 4.08 1.935 18.90  1  1    4    1
## 9   Porsche 914-2 26.0   4 120.3  91 4.43 2.140 16.70  0  1    5    2
## 10   Lotus Europa 30.4   4  95.1 113 3.77 1.513 16.90  1  1    5    2
## 11     Volvo 142E 21.4   4 121.0 109 4.11 2.780 18.60  1  1    4    2
dbClearResult(res) # 清理查询通道

或者一条命令搞定

dbGetQuery(con, "SELECT * FROM mtcars WHERE cyl = 4")
##         row_names  mpg cyl  disp  hp drat    wt  qsec vs am gear carb
## 1      Datsun 710 22.8   4 108.0  93 3.85 2.320 18.61  1  1    4    1
## 2       Merc 240D 24.4   4 146.7  62 3.69 3.190 20.00  1  0    4    2
## 3        Merc 230 22.8   4 140.8  95 3.92 3.150 22.90  1  0    4    2
## 4        Fiat 128 32.4   4  78.7  66 4.08 2.200 19.47  1  1    4    1
## 5     Honda Civic 30.4   4  75.7  52 4.93 1.615 18.52  1  1    4    2
## 6  Toyota Corolla 33.9   4  71.1  65 4.22 1.835 19.90  1  1    4    1
## 7   Toyota Corona 21.5   4 120.1  97 3.70 2.465 20.01  1  0    3    1
## 8       Fiat X1-9 27.3   4  79.0  66 4.08 1.935 18.90  1  1    4    1
## 9   Porsche 914-2 26.0   4 120.3  91 4.43 2.140 16.70  0  1    5    2
## 10   Lotus Europa 30.4   4  95.1 113 3.77 1.513 16.90  1  1    5    2
## 11     Volvo 142E 21.4   4 121.0 109 4.11 2.780 18.60  1  1    4    2

再复杂一点的 SQL 查询操作

dbGetQuery(con, "SELECT cyl, AVG(mpg) AS mpg FROM mtcars GROUP BY cyl ORDER BY cyl")
##   cyl      mpg
## 1   4 26.66364
## 2   6 19.74286
## 3   8 15.10000
aggregate(mpg ~ cyl, data = mtcars, mean)
##   cyl      mpg
## 1   4 26.66364
## 2   6 19.74286
## 3   8 15.10000

得益于 knitr (Xie 2015) 开发的钩子，这里直接写 SQL 语句块，打印出来见表 \@ref(tab:mtcars)，交叉引用图表是 bookdown 的功能， rmarkodwn 暂不支持，值得注意的是 SQL 代码块不能启用缓存，数据库连接通道也不能缓存，如果数据库中还没有写入表，那么写入表的操作也不能缓存

SELECT cyl, AVG(mpg) AS mpg FROM mtcars GROUP BY cyl ORDER BY cyl
表格标题
cyl mpg
4 26.66364
6 19.74286
8 15.10000

如果将查询结果导出到变量，在 Chunk 设置 output.var = "agg_cyl" 可以使用缓存，下面将 mpg 按 cyl 分组聚合的结果打印出来

agg_cyl
##   cyl      mpg
## 1   4 26.66364
## 2   6 19.74286
## 3   8 15.10000

这种基于 odbc 的方式的好处就不需要再安装 R 包 RPostgres 和相关系统依赖，最后关闭连接通道

dbDisconnect(con)

运行环境

复现步骤，克隆 repo，拉取容器镜像 xiangyunhuang/rgraphics

git clone https://github.com/XiangyunHuang/db-in-rmd.git
cd db-in-rmd
docker pull xiangyunhuang/rgraphics
docker-compose up
xfun::session_info(c("rmarkdown", "odbc"))
## R version 3.6.1 (2019-07-05)
## Platform: x86_64-pc-linux-gnu (64-bit)
## Running under: Debian GNU/Linux 10 (buster)
## 
## Locale:
##   LC_CTYPE=en_US.UTF-8       LC_NUMERIC=C              
##   LC_TIME=en_US.UTF-8        LC_COLLATE=en_US.UTF-8    
##   LC_MONETARY=en_US.UTF-8    LC_MESSAGES=en_US.UTF-8   
##   LC_PAPER=en_US.UTF-8       LC_NAME=C                 
##   LC_ADDRESS=C               LC_TELEPHONE=C            
##   LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C       
## 
## Package version:
##   assertthat_0.2.1  backports_1.1.4   base64enc_0.1.3  
##   BH_1.69.0.1       bit_1.1.14        bit64_0.9.7      
##   blob_1.2.0        DBI_1.0.0         digest_0.6.20    
##   ellipsis_0.2.0.1  evaluate_0.14     glue_1.3.1       
##   graphics_3.6.1    grDevices_3.6.1   highr_0.8        
##   hms_0.5.0         htmltools_0.3.6   jsonlite_1.6     
##   knitr_1.23        magrittr_1.5      markdown_1.0     
##   methods_3.6.1     mime_0.7          odbc_1.1.6       
##   pkgconfig_2.0.2   prettyunits_1.0.2 Rcpp_1.0.1       
##   rlang_0.4.0       rmarkdown_1.14    stats_3.6.1      
##   stringi_1.4.3     stringr_1.4.0     tinytex_0.14     
##   tools_3.6.1       utils_3.6.1       vctrs_0.2.0      
##   xfun_0.8          yaml_2.2.0        zeallot_0.1.0    
## 
## Pandoc version: 2.7.3

参考文献

Xie, Yihui. 2015. Dynamic Documents with R and Knitr. 2nd ed. Boca Raton, Florida: Chapman; Hall/CRC. http://yihui.name/knitr/.