背景

最近的一些软件包改进可以更轻松地将数据库与R一起使用。下面的查询示例演示了这些R软件包的一些功能。

DBI。该DBI规范经历了许多最近的改进。使用数据库时，应始终使用符合要求DBI的包。
dplyr和dbplyr。该dplyr软件包现在具有用于与数据库通信的通用SQL后端，新dbplyr软件包将R代码转换为特定于数据库的SQL。在撰写R代码时，以下数据库支持SQL转化：Oracle，Microsoft SQL Server，PostgreSQL，Amazon Redshift，Apache Hive和Apache Impala。随着时间的推移会有更多。
odbc。odbc只要安装了ODBC驱动程序，R包就提供了连接到任何数据库的标准方法。所述odbcř包是DBI兼容的，并建议用于ODBC连接。

RStudio最近对其产品进行了改进，因此它们可以更好地与数据库配合使用。

RStudio IDE（v1.1）。使用最新版本的RStudio IDE，可以连接，浏览和查看各种数据库中的数据。IDE具有用于设置新连接的向导，以及用于浏览已建立连接的选项卡。这些新功能是可扩展的，可以与任何具有连接契约的 R包一起使用。
RStudio 。如果使用的是RStudio专业产品，可以免费下载RStudio Professional Drivers。以下示例使用Oracle ODBC驱动程序。如果使用的是开源工具，则可以使用自己的驱动程序或使用社区程序包 - 存在许多用于连接各种数据库的开源驱动程序和社区程序包。

将数据库与R一起使用是一个广泛的主题，还有更多工作要做。早期的博客文章讨论了我们的愿景。该愿景的一部分是创建一个网站，可以在一个地方找到有关数据库和R的所有内容。要了解更多信息，请访问我们的网站db.rstudio.com。

示例：查询Oracle数据库中的库数据

在此示例中，我们将查询Oracle数据库中的银行数据。我们使用DBI和odbc包连接到数据库。此特定连接需要数据库驱动程序和数据源名称（DSN），这两者都由系统管理员配置。您的连接可能使用其他方法。

library(DBI)
library(dplyr)
library(dbplyr)
library(odbc)
con <- dbConnect(odbc::odbc(), "Oracle DB")

1.使用查询 DBI

您可以DBI使用该dbGetQuery()功能查询数据。只需将SQL代码粘贴到R函数中作为带引号的字符串。此方法有时称为传递SQL代码，可能是查询数据的最简单方法。应谨慎使用，以根据需要逃避您的报价。例如，'yes'写成'yes'。

dbGetQuery(con,'
  select "month_idx", "year", "month",
  sum(case when "term_deposit" = \'yes\' then 1.0 else 0.0 end) as subscribe,
  count(*) as total
  from "bank"
  group by "month_idx", "year", "month"
')

2.使用dplyr语法进行查询

您可以用dplyr语法编写代码，并将dplyr代码转换为SQL。在dplyr语法中编写查询有几个好处：您可以为R对象和数据库表保持相同的一致语言，不需要SQL或特定的SQL变体，您可以利用dplyr使用延迟评估的事实。dplyr语法很容易阅读，但您始终可以使用该show_query()函数检查SQL转换。

q1 <- tbl(con, "bank") %>%
  group_by(month_idx, year, month) %>%
  summarise(
    subscribe = sum(ifelse(term_deposit == "yes", 1, 0)),
    total = n())
show_query(q1)
<SQL>
SELECT "month_idx", "year", "month", SUM(CASE WHEN ("term_deposit" = 'yes') THEN (1.0) ELSE (0.0) END) AS "subscribe", COUNT(*) AS "total"FROM ("bank") 
GROUP BY "month_idx", "year", "mo

3.使用Rnote book 查询
您是否知道可以在R Notebook代码块中运行SQL代码？要使用SQL，请在“ 文件”>“新建文件”菜单下的RStudio IDE中打开R Notebook。使用，启动新的代码块，并使用代码块选项指定您的连接。如果要将查询输出发送到R数据帧，请在代码块选项中使用。指定时，您将能够在后续R代码块中使用输出。在这个例子中，我们使用输出。{sql}connection=conoutput.var = "mydataframe"output.varggplot2

SELECT "month_idx", "year", "month", SUM(CASE WHEN ("term_deposit" = 'yes') THEN (1.0) ELSE (0.0) END) AS "subscribe",

COUNT(*) AS "total"FROM ("bank") 

GROUP BY "month_idx", "year", "month"

library(ggplot2)

ggplot(mydataframe, aes(total, subscribe, color = year)) +

  geom_point() +

  xlab("Total contacts") +

  ylab("Term Deposit Subscriptions") +

  ggtitle("Contact volume")

image.png

摘要

使用R查询数据没有单一的最佳方法。您可以选择多种方法，每种方法都有其优点。以下是使用本文中介绍的方法的一些优点。
方法
好处

DBI :: dbGetQuery

需要的依赖性更少

dplyr语法

对R和数据库对象使用相同的语法
不需要SQL知识
代码是SQL变体的标准
懒惰的评价

R Notebook SQL引擎

复制并粘贴SQL - 无需格式化
SQL语法突出显示

作者：Liam_ml
链接：https://www.jianshu.com/p/19e77e7b05ac

使用R的数据库查询

背景

示例：查询Oracle数据库中的库数据

摘要