使用R和来自R数据帧的条件来查询MS SQL [英] Query MS SQL using R with criteria from an R data frame

问题描述

我想查询MS SQL Server中有一个很大的表（1.2亿行）。我在R中还有一个数据框，该数据框具有唯一的ID，我希望将其用作查询条件的一部分。我熟悉dplyr软件包，但不确定是否可以在MS SQL服务器上执行R查询，而不是将所有数据都带到我的笔记本电脑内存中（可能会使我的笔记本电脑崩溃）。

I have rather a large table in MS SQL Server (120 million rows) which I would like to query. I also have a dataframe in R that has unique ID's that I would like to use as part of my query criteria. I am familiar with the dplyr package but not sure if its possible to have the R query execute on the MS SQL server rather than bring all data onto my laptop memory (likely would crash my laptop).

当然，另一种选择是将数据帧作为当前表正在加载到sql上，但我不希望这样做。

of course, other option is to load the dataframe onto sql as a table which is currently what I am doing but I would prefer not to do this.

推荐答案

取决于您要执行的操作，您可能会在 RODBCext 包中找到值。

depending on what exactly you want to do, you may find value in the RODBCext package.

假设您要从MS SQL表中提取ID为R中向量的列，您可以尝试如下代码：

let's say you want to pull columns from an MS SQL table where IDs are in a vector that you have in R. you might try code like this:

library(RODBC)
library(RODBCext)
library(tidyverse)

dbconnect <- odbcDriverConnect('driver={SQL Server};
                          server=servername;database=dbname;trusted_connection=true')

v1 <- c(34,23,56,87,123,45)

qdf <- data_frame(idlist=v1)

sqlq <- "SELECT * FROM tablename WHERE idcol %in% ( ? )"

qr <- sqlExecute(dbconnect,sqlq,qdf,fetch=TRUE)  

基本上，您希望将要传递给查询的所有信息放入数据框。将其视为查询的变量或参数；对于每个参数，您都希望在数据框中添加一列。然后将查询写为字符串并将其存储在变量中。您可以使用 sqlExecute 函数将它们放在一起。

basically you want to put all the info you want to pass to the query into a dataframe. think of it like variables or parameters for your query; for each parameter you want a column in a dataframe. then you write the query as a character string and store it in a variable. you put it all together using the sqlExecute function.

这篇关于使用R和来自R数据帧的条件来查询MS SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！