In imanuelcostigan/RSQLServer: SQL Server R Database Interface (DBI) and 'dplyr' SQL Backend

knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>",
  fig.path = "README-"
)

RSQLServer

An R package that provides a SQL Server R Database Interface (DBI), based on the Microsoft's JDBC driver.

Package status

This package is no longer being actively maintained as there is now an excellent, much better supported package odbc. I will accept clean pull requests, but won't be implementing new features or fix bugs. Over time, this package will likely be archived.

Installation

You can install the development version from GitHub:

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

And when the package is back on CRAN, you can install it the usual way:

install.packages("RSQLServer")

Config file

We recommend that you store server details and credentials in ~/sql.yaml. This is partly so that you do not need to specify a username and password in calls to dbConnect(). But it is also because in testing, we've found that the jTDS single sign-on (SSO) library is a bit flaky. The contents of this file should look something like this:

SQL_PROD:
    server: 11.1.111.11
    type: &type sqlserver
    port: &port 1433
    domain: &domain companyname
    user: &user winusername
    password: &pass winpassword
    useNTLMv2: &ntlm true
SQL_DEV:
    server: 11.1.111.15
    type: *type
    port: *port
    domain: *domain
    user: *user
    password: *pass
    useNTLMv2: *ntlm

Usage

Ensure that your ~/sql.yaml file contains a valid SQL Server entry named TEST. In the following, the TEST server, generously provided by Microsoft for the purposes of this package's development, has a database containing band data sets.

DBI usage

The following illustrates how you can make use of the DBI interface. Note that we do not attach the RSQLServer package.

library(DBI)
con <- dbConnect(RSQLServer::SQLServer(), server = "TEST", database = "rsqlserver")
dbWriteTable(con, "band_members", dplyr::band_members)
dbWriteTable(con, "band_instruments", dplyr::band_instruments)
# RSQLServer only returns tables with type TABLE and VIEW.
dbListTables(con)
dbReadTable(con, 'band_members')
dbListFields(con, 'band_instruments')

# Fetch all results
res <- dbSendQuery(con, "SELECT * FROM band_members WHERE band = 'Beatles'")
dbFetch(res)
dbClearResult(res)

dplyr usage

The following illustrates how you can make use of the dplyr interface. Again, we do not attach the RSQLServer package.

library(dplyr, warn.conflicts = FALSE)
members <- tbl(con, "band_members")
instruments <- tbl(con, "band_instruments")
members %>% 
  left_join(instruments) %>% 
  filter(band == "Beatles")
collect(members)

Clean up

dbRemoveTable(con, "band_instruments")
dbRemoveTable(con, "band_members")
dbDisconnect(con)

imanuelcostigan/RSQLServer documentation built on May 18, 2019, 3:44 a.m.