泰迪R - 连接数据库

R 操作数据是先将数据载入内存，当数据超过内存限制时，一种解决办法是，将大数据存放在远程数据库（远程服务器或本地硬盘），然后建立与R 的连接，再从 R 中执行查询、探索、建模等。
dplyr 是 tidyverse 操作数据的最核心包，而 dbplyr 包是用于数据库的 dplyr 后端，能够操作远程数据库中的数据表，就像它们是内存中的数据框一样。
DBI 包提供了通用的接口，使得能够使用相同的代码与许多不同的数据库连用。
常见的主流数据库软件： SQL Server, MySQL, Oracle 等都能支持，但还需要为其安装特定的驱动，比如RPostgres 包：连接到postgres数据库。

建立R与postgres的连接

先加载 RPostgres 包，再用 dbConnect() 来建立连接，需要提供数据库后端、数据库名、用户名、密码

<PqResult>
  SQL  set search_path=public,mimic_core,mimic_hosp,mimic_icu;
  ROWS Fetched: 0 [complete]
       Changed: 0

 [1] "admissions"         "patients"           "transfers"         
 [4] "d_hcpcs"            "diagnoses_icd"      "d_icd_diagnoses"   
 [7] "d_icd_procedures"   "d_labitems"         "drgcodes"          
[10] "emar_detail"        "emar"               "hcpcsevents"       
[13] "labevents"          "microbiologyevents" "pharmacy"          
[16] "poe_detail"         "poe"                "prescriptions"     
[19] "procedures_icd"     "services"           "omr"               
[22] "chartevents"        "datetimeevents"     "d_items"           
[25] "icustays"           "inputevents"        "outputevents"      
[28] "procedureevents"    "ingredientevents"   "icd9_10"

数据表引用

用函数 tbl() 获取数据表的引用，引用是一种浅拷贝机制，能够不做物理拷贝而使用数据，一般处理大数据都采用该策略。

df = tbl(con, "patients")
df

# Source:   table<patients> [?? x 6]
# Database: postgres  [postgres@localhost:5432/mimiciv]
   subject_id gender anchor_age anchor_year anchor_year_group dod       
        <int> <chr>       <int>       <int> <chr>             <date>    
 1   10000032 F              52        2180 2014 - 2016       2180-09-09
 2   10000048 F              23        2126 2008 - 2010       NA        
 3   10000068 F              19        2160 2008 - 2010       NA        
 4   10000084 M              72        2160 2017 - 2019       2161-02-13
 5   10000102 F              27        2136 2008 - 2010       NA        
 6   10000108 M              25        2163 2014 - 2016       NA        
 7   10000115 M              24        2154 2017 - 2019       NA        
 8   10000117 F              48        2174 2008 - 2010       NA        
 9   10000178 F              59        2157 2017 - 2019       NA        
10   10000248 M              34        2192 2014 - 2016       NA        
# … with more rows

输出数据表引用，看起来和 tibble 几乎一样，主要区别就是它是来自远程 postgres 数据库。

数据表查询

与数据库交互，通常是用 SQL（结构化查询语言），几乎所有的数据库都在使用 SQL。 dbplyr 包让 R 用户用 dplyr 语法就能执行 SQL 查询，就像用在 R 中操作数据框一样：

df %>% filter( gender == "M" & anchor_age == 52)

# Source:   SQL [?? x 6]
# Database: postgres  [postgres@localhost:5432/mimiciv]
   subject_id gender anchor_age anchor_year anchor_year_group dod       
        <int> <chr>       <int>       <int> <chr>             <date>    
 1   10005428 M              52        2143 2008 - 2010       NA        
 2   10005808 M              52        2118 2011 - 2013       NA        
 3   10006053 M              52        2111 2014 - 2016       2111-11-15
 4   10007920 M              52        2132 2008 - 2010       NA        
 5   10013049 M              52        2114 2011 - 2013       NA        
 6   10017852 M              52        2158 2008 - 2010       NA        
 7   10018599 M              52        2112 2017 - 2019       NA        
 8   10020435 M              52        2146 2014 - 2016       NA        
 9   10032876 M              52        2138 2014 - 2016       NA        
10   10033143 M              52        2137 2008 - 2010       NA        
# … with more rows

当与数据库一起工作时， dplyr 试图尽可能地懒惰：

除非明确要求（接 collect()），否则它不会把数据拉到 R 中
它把任何工作都尽可能地推迟到最后一刻：把想做的所有事情合在一起，然后一步送到数据库中
dbplyr 包还提供了将 dplyr 代码翻译成 SQL 查询代码的函数 show_query(). 可以进一步用于 MySQL, 或 dbSendQuery(), dbGetQuery()

df %>% filter( gender == "M" & anchor_age == 52) %>% show_query()

<SQL>
SELECT *
FROM "patients"
WHERE ("gender" = 'M' AND "anchor_age" = 52.0)

最后，关闭连接。

dbDisconnect(con)