时间戳

首页 > 解决方案 > 您如何使用数据框、列表、向量等过滤到 R 中数据库中的表？

sql - 您如何使用数据框、列表、向量等过滤到 R 中数据库中的表？

问题描述

我有一大组 id-s 在 csv 文件中。如何仅使用 csv 文件中的单列表过滤数据库表？

例如，在 ODBC 数据库中，我们有：

表格1

+---------+------+
|   ID    | TYPE |
+---------+------+
| 43PRJIF | A    |
| 35IRPFJ | A    |
| 452JSU  | B    |
| 78JFIER | B    |
| 48IRUW  | C    |
| 89UEJDU | C    |
| 784NFJR | D    |
| 326NFR  | D    |
| 733ZREW | E    |
+---------+------+

在 CSV 文件中，我们有：

+---------+
|   ID    |
+---------+
| 89UEJDU |
| 784NFJR |
| 326NFR  |
| 733ZREW |
+---------+

如果可能的话，基本上我想使用 dbplyr 包中的一些东西。例如，将 csv 表导入数据框，然后在 dbplyr 中使用如下语法：

new_table <- TABLE1 %>%
filter(id == "ROWS IN THE CSV")

要获得这样的输出：

+---------+------+
|   ID    | TYPE |
+---------+------+
| 89UEJDU | C    |
| 784NFJR | D    |
| 326NFR  | D    |
| 733ZREW | E    |
+---------+------+

提前谢谢你的帮助！

标签： sqlrdplyrodbcdbplyr

解决方案

一般来说，加入或合并表需要它们共享相同的环境。因此，这里有三个通用选项：

  1. 将远程表加载到 R 的本地工作区
  2. 将 CSV 表加载到数据库中并使用半连接。
  3. 将 CSV 中的 ID 列表“走私”到数据库中

让我们依次考虑：

选项1

这可能是最简单的选择，但它要求远程/ODBC 表足够小以适合 R 的工作内存。如果是这样，您可以调用local_table = collect(remote_table)加载数据库表。

选项 2

dbplyr包括一个命令copy_to( ref )，可让您通过 odbc 将本地表复制到数据库/远程连接。您将需要具有在远程环境中创建表的权限。

这种方法利用了 DBI 包。在编写 CRAN 上的 DBI v1.0.0 时，在写入非默认模式时存在一些限制。所以你可能需要升级到 GitHub 上的开发版本（这里）。

您的代码将类似于：

DBI::dbWriteTable(db_connection,
                  DBI::Id(schema = "schema", table = "name")),
                  r_table_name)

选项 3

通过表定义将 ID 列表偷运到数据库中。这与这里的想法相同，如果 ID 列表很短，效果最好。

远程表本质上是由获取其结果的代码/查询定义的。因此，ID 列表可以出现在定义远程表的代码中。考虑以下示例：

library(dplyr)
library(dbplyr)
data(mtcars)

list_of_ids = c(1,2,3,4)
df = tbl_lazy(mtcars, con = simulate_mssql())
df %>% filter(ID %in% list_of_ids ) %>% show_query()

show_query()呈现定义远程表当前版本的代码。在上面的示例中，它返回以下内容 - 请注意，ID 列表现在出现在代码中。

<SQL>
SELECT *
FROM `df`
WHERE (`ID` IN (1.0, 2.0, 3.0, 4.0))

如果 ID 列表很长，那么这个查询的大小就会成为问题。因此，您可以使用这种方法过滤的 ID 数量是有限制的（我没有测试这种方法来找到限制 - 我很少使用该IN子句来处理超过 10 个的列表）。

推荐阅读