Search code examples
sqlsql-serverrsparklyr

Read SQL table into SparklyR

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!!

Solution

  • 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