2

How can one connect a SQL table to R using SparklyR? The cheatsheet shows that you can use DBI::dbWriteTable, but does not mention DBI::dbReadTable. Let's say that my table is here:

driver = "SQL Server Native Client 11.0"
server = "corsql10.corwin.local"
database = "Project_DB"
table = "Participants"

Responses with example code are preferred. Thanks!!

Zafar
  • 1,897
  • 15
  • 33

1 Answers1

3

Dan,

You can try something like this:

install.packages('devtools')
devtools::install_github('imanuelcostigan/RSQLServer')
require(RSQLServer)
require(dplyr)

src <- RSQLServer::src_sqlserver("corsql10.corwin.local", database = "Project_DB")
data <- tbl(src, "Participants")

DBI::dbWriteTable(sc, "spark_Participants", data)

First, define the data source from SQL Server. Second, write it to Spark. tbl should create a reference to the SQL Server table without loading it into memory. It looks like the RSQLServer package is not well maintained and CRAN took it down because the author didn't fix its bugs... So you will have to trouble shoot it. Here is a good resource: Accessing MSSQL Server with R

Jonathan Hill
  • 1,745
  • 15
  • 25
  • This is excellent, but I'm not able to test because `odbc` does not install on my R version. Can you suggest a different driver? I've tried `SQLite`, but could not figure out the rest of this `dbConnect(RSQLite::SQLite(), )`. Vignette says "SQLite only needs a path to the database." – Zafar May 05 '17 at 16:35
  • Can you use RODBC? – Jonathan Hill May 05 '17 at 16:40
  • I'll update the answer's code if that package works for you – Jonathan Hill May 05 '17 at 16:40
  • RODBC would be perfect, I've already experimented with that but was not able to get it going – Zafar May 05 '17 at 16:44
  • `Error in UseMethod("tbl") : no applicable method for 'tbl' applied to an object of class "RODBC"` – Zafar May 05 '17 at 16:46
  • Unfortunately I can't get `RSQLServer` installed either due to my company's R version (issue with `bindrcpp` dependancy). – Zafar May 05 '17 at 17:30
  • 1
    I'd say you've got to work with IT to update R and then use odbc/RSQLServer with the correct drivers installed... Good luck! – Jonathan Hill May 05 '17 at 19:43