从RODBC中调用sqlQuery

Question

R版本:2.14.1 x64在Windows 7上运行连接远程Microsoft SQL Server 2012数据库

我有一个无序的名字向量，比如:

names<-c(“A”, “B”, “A”, “C”,”C”)

在我的数据库中的表中有一个id。我需要将名称转换为相应的id。

我现在有下面的代码来做这件事。

###
names<-c(“A”, “B”, “A”, “C”,”C”)
dbConn<-odbcDriverConnect(connection=”connection string”) #successfully connects
nameToID<-function(name, dbConn){
                #dbConn : active db connection formed via odbcDriverConnect
                #name     : a char string
                sqlQuery(dbConn, paste(“select id from table where name=’”, name, “’”, sep=””))
}
sapply(names, nameToID, dbConn=dbConn)
###

除非有更好的方法来做到这一点，这可能涉及将表加载到R中，然后处理那里的问题(这是可能的)，我理解为什么以下不工作，但我似乎找不到解决方案。试图通过包' parallel ':

使用并行化

###
names<-c(“A”, “B”, “A”, “C”,”C”)
dbConn<-odbcDriverConnect(connection=”connection string”) #successfully connects
nameToID<-function(name, dbConn){
                #dbConn : active db connection formed via odbcDriverConnect
                #name     : a char string
                sqlQuery(dbConn, paste(“select id from table where name=’”, name, “’”, sep=””))
}
mc<-detectCores()
cl<-makeCluster(mc)
clusterExport(cl, c(“sqlQuery”, “dbConn”))
parSapply(cl, names, nameToID, dbConn=dbConn)    #incorrect passing of nameToID’s second argument
###

如注释所述，这不是将第二个参数分配给nameToID的正确方法。

我还尝试了以下操作:

parSapply(cl, names, function(x) nameToID(x, dbConn))

来代替前面的parSapply调用，但这也不起作用，抛出的错误说"第一个参数不是打开的RODBC连接"，可能指的是sqlQuery()的第一个参数。dbConn仍然打开

下面的代码可以使用并行化。

###
names<-c(“A”, “B”, “A”, “C”,”C”)
dbConn<-odbcDriverConnect(connection=”connection string”) #successfully connects
nameToID<-function(name){
                #name     : a char string
                dbConn<-odbcDriverConnect(connection=”string”)
                result<-sqlQuery(dbConn, paste(“select id from table where name=’”, name, “’”, sep=””))
                odbcClose(dbConn)
                result
}
mc<-detectCores()
cl<-makeCluster(mc)
clusterExport(cl, c(“sqlQuery”, “odbcDriverConnect”, “odbcClose”, “dbConn”, “nameToID”))      #throwing everything in
parSapply(cl, names, nameToID)
###

但是不断地打开和关闭连接破坏了并行化的收益，并且看起来有点愚蠢。

因此，总体问题是如何将第二个参数(打开的db连接)传递给parSapply中的函数，就像在常规应用程序中一样?一般来说，如何将第二个、第三个、第n个参数传递给并行例程中的函数?

谢谢，如果您需要更多的信息请告诉我。

dt

Answer 1

数据库连接对象不能被导出或作为函数参数传递，因为它们包含套接字连接。如果您尝试，它将被序列化，发送到工作线程并反序列化，但它将无法正常工作，因为套接字连接将无效。

解决方案是在调用parSapply之前在每个worker上创建数据库连接。我经常使用clusterEvalQ:

clusterEvalQ(cl, {
    library(RODBC)
    dbConn <- odbcDriverConnect(connection="connection string")
    NULL
})

现在worker函数可以写成:

nameToID <- function(name) {
    sqlQuery(dbConn, paste("select id from table where name='", name, "'", sep=""))
}

，并调用:

parSapply(cl, names, nameToID)  

还请注意，由于RODBC加载在每个worker上，因此您不必导出其中定义的函数，我认为这是良好的编程实践。