10 Data Connectivity

10.1 Connectivity using ODBC

RStudio makes ODBC connections available in the Connections Pane. ODBC connections are obtained from the odbcinst.ini file and can be further customized using Snippet Files.

10.1.1 Professional Drivers

RStudio Workbench provides connectivity to data sources through RStudio Professional Drivers. See Getting Started with RStudio Professional Drivers and Databases using R for more information.

10.2 Connectivity using R Packages

For R Packages that provide data connectivity through the Connections Contract, RStudio makes these connections also available in the Connections Pane and can be further customized using Snippet Files. Currently, the odbc and sparklyr packages provide this connectivity.

10.3 Snippet Files

A Connection Snippet File is an R code snippet with additional metadata which is intended to initialize a connection. This file can be as simple as:

library(readr)
data <- read_csv(readr_example("mtcars.csv"))

Once this file is saved under /etc/rstudio/connections/ as Motor Trend Cars.R, RStudio will make this connection as available under the Connection Pane.

The path is configurable through the connections-path environment variable and multiple connection files can be specified.

In order to parameterize this connection, one can create fields using using the ${Position:Label=Default} syntax:

  • Position: The row position starting at zero.
  • Label: The label assigned to this field.
  • Default: An optional default value.

For example, we can filter out this dataframe to produce the following connection interface:

library(readr)
data <- read_csv(readr_example("mtcars.csv"))
data[data$mpg == ${0:Miles per Gallon=21.4} | data$cyl == ${1:Cylinders=6}, ]

In order to create a ; separated list of values, one can use the syntax ${Position:Label=Default:Key}. Semicolon-separated list are common in database connections and therefore, natively supported in snippet files, for instance:

"${2:Letters=ABC:LettersKey}${3:Numbers=123:NumbersKey}"

There are a couple of escape characters supported: $colon$ to escape : and $equal to escape =.

Additional resources are available under RStudio Extensions - Connections.