使用dplyr与数据库

开始

使用数据库dplyr，您需要先安装dbplyr：

install.packages(“dbplyr”)

您还需要安装一个DBI后端包。的DBI包提供了一个公共接口，允许dplyr使用相同的代码处理多个不同的数据库。DBI会自动安装dbplyr，但是需要为要连接的数据库安装特定的后端。

五种常用的后端是:

• RMySQL连接MySQL和MariaDB

• RPostgreSQL连接Postgres和Redshift。

• RSQLite嵌入一个SQLite数据库。

• odbc通过开放数据库连接协议连接到许多商业数据库。

• bigrquery连接到谷歌的BigQuery。

如果这里没有列出需要连接的数据库，则需要自己进行一些调查。

在这个小插曲中，我们将使用RSQLite后端，它会在安装时自动安装dbplyr．SQLite是开始使用数据库的好方法，因为它完全嵌入到R包中。与大多数其他系统不同，您不需要设置单独的数据库服务器。SQLite非常适合演示，但它的功能也非常强大，只要稍加练习，就可以使用它轻松处理许多gb的数据。

连接到数据库

中的数据库dplyr，您必须先连接到它，使用DBI: dbConnect ()．我们不打算深入讨论细节DBI包在这里，但这是基础dbplyr是建立。您将需要了解更多关于是否需要对数据库执行超出的范围的操作dplyr．

library(dplyr) con <- DBI::dbConnect(RSQLite::SQLite()， path = ":dbname:")

的参数DBI: dbConnect ()不同的数据库有所不同，但第一个参数总是数据库后端。这是RSQLite: SQLite ()RSQLite,RMySQL: MySQL ()RMySQL,RPostgreSQL: PostgreSQL ()RPostgreSQL,odbc: odbc ()对于odbc,bigrquery: bigquery ()BigQuery。SQLite只需要另一个参数:到数据库的路径。这里我们使用特殊的字符串，”:记忆:“，这会导致SQLite在内存中创建一个临时数据库。

大多数现有的数据库并不存在于文件中，而是存在于另一个服务器中。在现实生活中，你的代码看起来更像这样:

con <- DBI::dbConnect(RMySQL::MySQL()， host = " databe.r英格兰vs伊朗让球studio.com "， user = "hadley"， password = rstudioapi::askForPassword("数据库密码"))

(如果您没有使用RStudio，您将需要其英格兰vs伊朗让球他方法安全地检索您的密码。您不应该在您的分析脚本中记录它或将它输入控制台。)

我们的临时数据库中没有数据，所以我们将从复制开始nycflights13:航班使用方便的copy_to ()函数。这是将数据放入数据库的一种快速而简单的方法，主要用于演示和其他小型作业。

copy_to(con, nycflights13::flights， "flights"， temporary = FALSE, indexes = list(c("year"， "month"， "day")， "carrier"， "tailnum"， "dest"))

如你所见，这个copy_to ()操作有一个附加参数，允许您为表提供索引。在这里，我们设置了索引，使我们能够按日期、航空公司、飞机和目的地快速处理数据。创建写索引是良好数据库性能的关键，但不幸的是，这超出了本文的范围。

现在我们已经复制了数据，我们可以使用台()引用:引用它:

Flights_db <- tbl(con， "flights")

当你把它打印出来的时候，你会注意到它看起来像一个普通的tibble:

flights_db #> # Source: table [??]x 19] # > #数据库:sqlite 3.34.1[] # >年月日dep_time sched_dep_time dep_delay arr_time sched_arr_time # > < int > < int > < int > < int > < int > <双> < int > < int > # > 1 2013年1 1 517 515 2013 830 819 # > 2 1 1 533 529 4 850 830 # > 3 2013年1 1 542 540 2013 923 850 # > 4 1 1 1 544 545 1004 1022 1 # > 5 2013 554 600 6 812 837 1 # > 6 2013 554 558 740 728  #> # ... 与更多的行,和11个变量:Arr_delay ， carrier ， #> # flight ， tailnum ， origin ， dest ， air_time ， #> # distance ， hour ， minute ， time_hour 

主要的区别在于，您可以看到它是SQLite数据库中的远程源代码。

生成的查询

要与数据库交互，通常使用SQL(结构化查询语言)。SQL已经有40多年的历史了，几乎所有数据库都在使用它。的目标dbplyr是自动为您生成SQL，这样您就不会被迫使用它。然而，SQL是一种非常大的语言，而且dbplyr不做任何事。它关注选择语句，即作为分析师最常编写的SQL。

大多数情况下，您不需要了解SQL的任何内容，可以继续使用dplyr你已经很熟悉的动词:

flights_db %>% select(year:day, dep_delay, arr_delay) #> # Source: lazy query [??]x 5] # > #数据库:sqlite 3.34.1[] # >年月日dep_delay arr_delay # > < int > < int > < int > <双> <双> > 1 2013年1 1 2 # 11 # > 2 2013年1 1 4 20 # > 3 2013 2013 1 2 33 # > 4 1 1 1 -18 # > 5 2013年1 1 6 -25 # > 6 2013 1 1 4 12  #> # ... 多行flights_db % > %过滤器(dep_delay > 240) # > #来源:懒惰的查询(? ?x 19] # > #数据库:sqlite 3.34.1[] # >年月日dep_time sched_dep_time dep_delay arr_time sched_arr_time # > < int > < int > < int > < int > < int > <双> < int > < int > # > 1 2013年1 1 848 1835 853 1001 1950 2013 # > 2 1 1 1815 1325 290 2120 1542 2013 # > 3 1 1 1842 1422 260 1958 1535 # > 4 1 1 2115 1700 255 2330 1920 2013 # > 5 2013 2040 1 2205 1720 285 46 # > 6 2013年1 1 2343 1724 379 314 1938  #> # ... 与更多的行,和11个变量:arr_delay ， carrier ， #> # flight ， tailnum ， origin ， dest ， air_time ， #> # distance ， hour ， minute ， time_hour  flights_db %>% group_by(dest) %>% summarise(delay = mean(dep_time)) #>警告:SQL中总是删除缺失的值。使用' mean(x, na。#>此警告在每个会话中只显示一次。#>源:lazy query [??]x 2] #> # Database: sqlite 3.34.1 [] #> dest delay #>   #> 1 ABQ 2006。#> 2 ack 1033。#> 3 alb 1627。 #> 4 ANC 1635. #> 5 ATL 1293. #> 6 AUS 1521. #> # … with more rows

但是，从长远来看，我强烈建议您至少学习SQL的基础知识。对于任何数据科学家来说，这都是一项有价值的技能，它将帮助您调试遇到的问题dplyr自动翻译。如果您对SQL完全陌生，您可以从这里开始codeacademy教程．如果您对SQL有一定的了解，并且想了解更多，我发现索引在SQLite中是如何工作的而且完整理解SQL的10个简单步骤特别有帮助。

普通数据帧和远程数据库查询之间最重要的区别是，您的R代码被翻译成SQL并在数据库中执行，而不是在R中。dplyr尽可能的懒惰:

• 它从不将数据拉入R，除非你明确要求它。

• 它将任何工作延迟到最后可能的时刻:它收集您想要做的所有事情，然后一步将其发送到数据库。

例如，取以下代码为例:

Tailnum_delay_db <- flights_db %>% group_by(tailnum) %>% summarise(delay = mean(arr_delay)， n = n()) %>% arrange(desc(delay)) %>% filter(n > 100)

令人惊讶的是，这个操作序列从不涉及数据库。直到你要求得到数据(例如，打印)tailnum_delay),dplyr生成SQL并从数据库请求结果。即使这样，它也尽量少做一些工作，只拉下几行。

tailnum_delay_db #>警告:ORDER BY在没有LIMIT #>的子查询中被忽略ℹ您需要在管道中稍后移动arrange()或使用window_order()代替吗?#>源:lazy query [??]x 3] #> #数据库:sqlite 3.34.1 [] #> # Ordered by: desc(delay) #> tailnum delay n #>    #> 1  NA 2512 #> 2 N0EGMQ 9.98 371 #> 3 N10156 12.7 153 #> 4 N10575 20.7 289 #> 5 N11106 14.9 129 #> 6 N11107 15.0 148 #> # with more rows

在幕后,dplyr就是把你的R代码转换成SQL。您可以看到它生成的SQLshow_query ()：

tailnum_delay_db %>% show_query() #>警告:ORDER BY在没有LIMIT的子查询中被忽略#>ℹ您是否需要在管道中稍后移动arrange()或使用window_order()代替?#>  #> SELECT * #> FROM (SELECT ' tailnum '， AVG(' arr_delay ') AS ' delay '， COUNT(*) AS ' n ' #> FROM ' flights ' #> GROUP BY ' tailnum ') #> WHERE (' n ' > 100.0)

如果您熟悉SQL，这可能不是您亲手编写的内容，但它可以完成任务。您可以在中了解更多关于SQL转换的信息装饰图案(“sql-translation”)．

通常，在确定需要从数据库中获得哪些数据之前，需要进行几次迭代。一旦你想明白了，就使用收集()将所有数据拉入本地tibble:

tailnum_delay <- tailnum_delay_db %>% collect() #>警告:ORDER BY在没有LIMIT的子查询中被忽略#>ℹ您是否需要在管道中稍后移动arrange()或使用window_order()代替?tailnum_delay #> # A tibble: 1,201 x 3 #> tailnum delay n #>    #> 1  NA 2512 #> 2 N0EGMQ 9.98 371 #> 3 N10156 12.7 153 #> 4 N10575 20.7 289 #> 5 N11106 14.9 129 #> 6 N11107 15.0 148 #> #…with 1,195 more rows

收集()需要数据库做一些工作，因此可能需要很长时间才能完成。否则,dplyr试图防止你不小心执行昂贵的查询操作:

• 因为通常没有办法确定一个查询将返回多少行，除非你实际运行它，nrow ()总是NA．

• 因为在不执行整个查询的情况下无法找到最后几行，所以不能使用尾()．

nrow(tailnum_delay_db) #> [1] NA tail(tailnum_delay_db) #> Error: tail() is not 卡塔尔世界杯欧洲预选赛赛程表supported by sql sources .

您还可以询问数据库计划如何执行该查询解释()．输出是依赖于数据库的，而且可能很深奥，但是了解一点它会非常有用，因为它可以帮助您理解数据库是否可以有效地执行查询，或者是否需要创建新的索引。

创建自己的数据库

如果您还没有一个数据库，下面是我从设置和运行所有数据库的经验中得到的一些建议。到目前为止，SQLite是最容易上手的，但是由于缺少窗口函数，它在数据分析方面受到了限制。PostgreSQL并不难使用，并且有广泛的内置函数。在我看来，你不应该用MySQL/MariaDB;它设置起来很痛苦，文档质量很差，功能也不如Postgres丰富。如果您有非常大的数据，或者如果您愿意支付(少量)钱给负责管理您的数据库的人，那么谷歌BigQuery可能非常适合。

所有这些数据库都遵循客户机-服务器模型——连接到数据库的计算机和运行数据库的计算机(两者可能是同一台，但通常不是)。安装并运行其中一个数据库超出了本文的范围，但是在web上有大量的教程。