在R中并行化SQL查询

Question

在R中并行化SQL查询

sqlrparallel-processingmulticore

17

我有六个SQL查询，通过R脚本执行，每个查询需要很长时间（约30分钟）。每个查询返回后，我会操作数据生成一些标准报告。

我想做的是利用我多核机器从R并行运行这些SQL请求。

我使用Oracle DB的Windows机器。我正在stackoverflow上按照 blog post 的方法尝试使用doSNOW和foreach拆分这些请求，这是我能找到的最好的东西。

我已经成功地为非并行的%do% foreach版本获得了过程，但对于%dopar%，它只返回一个空集。下面是设置表格和运行查询的代码，这样您就可以看到发生了什么。如果有太多基本代码，请提前道歉。

我查看了其他一些R包，但没有发现明显的解决方案。如果您有更好的管理此类过程的方法，我很乐意听取 - 请记住我是分析师而不是计算机科学家。谢谢！

#Creating a cluster
library(doSNOW)
cl <- makeCluster(c("localhost","localhost"), type = "SOCK")
registerDoSNOW(cl)

#Connecting to database through RODBC
ch=odbcConnect("",pwd = "xxxxx", believeNRows=FALSE)
#Test connection
odbcGetInfo(ch)

#Creating database tables for example purposes
qryA1 <- "create table temptable(test int)" 
qryA2 <- "insert into temptable(test) values((1))" 
qryA3 <- "select * from temptable" 
qryA4 <- "drop table temptable" 
qryB1 <- "create table temptable2(test int)" 
qryB2 <- "insert into temptable2(test) values((2))" 
qryB3 <- "select * from temptable2" 
qryB4 <- "drop table temptable2"  

sqlQuery(ch, qryA1) 
sqlQuery(ch, qryA2) 
doesItWork <- sqlQuery(ch, qryA3) 
doesItWork
sqlQuery(ch, qryB1) 
sqlQuery(ch, qryB2) 
doesItWork <- sqlQuery(ch, qryB3) 
doesItWork

result = c()
output = c()
databases <- list('temptable','temptable2')


#Non-parallel version of foreach
system.time(
foreach(i = 1:2)%do%{
result<-sqlQuery(ch,paste('SELECT * FROM ',databases[i]))   
output[i] = result
}
) 

output

#Parallel version of foreach

outputPar = c()

system.time(
foreach(i = 1:2)%dopar%{
#Connecting to database through RODBC
ch=odbcConnect(dsn ,pwd = "xxxxxx", believeNRows=FALSE)
#Test connection
odbcGetInfo(ch)
result<-sqlQuery(ch,paste('SELECT * FROM ',databases[i]))   
outputPar[i] = result
}
) 

outputPar

sqlQuery(ch, qryA4)
sqlQuery(ch, qryB4)

- Andrew Elliott

2

在并行查询数据库时，这可能会使过程变慢，具体取决于瓶颈的原因。您确定瓶颈是运行在您机器上的 R 实例（而不是数据库、网络连接、运行数据库的机器等）吗？ - Joshua Ulrich

集群节点上是否加载了 RODBC 包？如果没有，请在您的 foreach() 调用中添加 .packages="RODBC"。 - BenBarnes

@Joshua Ulrich - 数据库非常强大，因为它是商业版的Oracle安装。查询需要跨越多个表进行连接，需要在数据库中进行大量处理。我手动加速的方法是从SQL Developer启动多个实例，但是一个R脚本可以帮我完成大部分查询后处理工作，所以我更愿意让R同时启动多个查询，这样我需要等待的最长时间就是长查询时间，而不是将所有查询时间相加。 - Andrew Elliott

@BenBarnes 我尝试在 foreach 中添加 .packages="RODBC"，但不幸的是我仍然收到了一个空集合。 - Andrew Elliott

1

RODBC在移动数据时非常慢，因此如果它返回大量数据，则通过将查询结果转储到CSV并通过SMB移动，然后导入，您将获得更好的结果。我在类似问题上工作时获得了1:20的加速比。 - Hansi

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- BenBarnes · Accepted Answer

当您在串行 foreach 循环内进行赋值 outputPar[i] = result 时，这是可以的（但不是 foreach 的预期用法）。而当您在并行循环内进行此操作时，则不可行。请参阅http://tolstoy.newcastle.edu.au/R/e10/help/10/04/3237.html ，其中 David Smith 在 Revolution 中回答了类似问题。

作为解决方案，

system.time(
  outputPar <- foreach(i = 1:2, .packages="RODBC")%dopar%{
#Connecting to database through RODBC
  ch=odbcConnect(dsn ,pwd = "xxxxxx", believeNRows=FALSE)
#Test connection
  odbcGetInfo(ch)
  result<-sqlQuery(ch,paste('SELECT * FROM ',databases[i]))   
  result
}
)