Using RStudio Connections in the RStudio IDE

Follow
If you are experiencing problems, see Troubleshooting Connections or Troubleshooting RStudio Professional Drivers. For more information on using databases with the RStudio IDE, see db.rstudio.com.

Overview

The RStudio Connections Pane makes it possible to easily connect to a variety of data sources, and explore the objects and data inside the connection. It extends, and is designed to work with, a variety of other tools for working with databases in R. You can read more about these other tools on the Databases with RStudio site.

The Connection Pane helps you to connect to existing data sources. It is not a connection manager like you would see in PGAdmin, Toad, or SSMS. Like the Data Import feature, it helps you craft an R statement that you can run to help work with your data in R. It also remembers the R statement so that you can reconnect easily, and provides a means of exploring the data source once you're connected.

Prerequisites

The Connections Pane is available only in RStudio 1.1 and later. If you plan to work with ODBC data sources in the Connections Pane, you’ll also need the latest version of the odbc package from Github, which you can install as follows:

> devtools::install_github("r-dbi/odbc")

Connect to existing data sources

There are two ways to connect to an existing data source:

Use the New Connection button

Click the New Connection button to create a new data connection.

new-connection.png

 
You’ll see a list of available connection types displayed; choose one to create a connection to the associated data source.

create-connection.png

 
The list of connection types in this dialog is drawn from a variety of sources. For instance, if you have the sparklyr package installed, you’ll see the Spark type in the list.

If you’ve installed the odbc package, then you’ll also see your ODBC drivers in this list, as well as any DSNs installed on the system (see Setting up ODBC Drivers for details).

Any R package can supply a connection type to be shown in the list. If you’re an R package author, read our guide on extending RStudio Connections to see how to make your package’s connections appear in RStudio.

Create a Connection Manually

When you create a data connection by executing R code in the console, RStudio will automatically add the connection to the list of connections if the R package involved integrates with RStudio. If you’re an R package author, read our guide on the Connections Contract for instructions on adding this integration.

> con <- DBI::dbConnect(odbc::odbc(),
                        Driver    = "SQL Server", 
                        Server    = [My Server],
                        Database  = [My Database],
                        UID       = [My User ID],
                        PWD       = [My Password],
                        Port      = 1433)

Opening a Data Connection

Data connections are typically ephemeral and are closed when your R session ends or is restarted. To re-establish a data connection, click the Connections tab. This shows a list of all the connections RStudio knows about (see Connections History below for details).

Click on a connection, and then choose how you’d like to (re)connect by clicking the Connect button, which is shown above the code that re-establishes the connection:

connect-types.png

  • R Console will create the connection immediately by executing the code at the R console.
  • New R Script will put your connection into a new R script, and then immediately run the script.
  • New R Notebook will create a new R Notebook with a setup chunk that connects to the data, and then immediately run the setup chunk.
  • Copy to Clipboard will place the connection code onto the clipboard, to make it easy to insert into an existing script or document.

Exploring Connections

When you select a connection that is currently connected, you can explore the objects and data in the connection.

exploring.png

 

Use the blue expanding arrows on the left to drill down to the object you’re interested in. If the object contains data, you’ll see a table icon on the right; click on it to see the first 1,000 rows of data in the object.

Connection History

The Connections Pane show you all the connections you’ve made to supported data sources, and lets you know which connections are currently active. 

This history is specific to your user account and is stored privately in RStudio’s settings; you will see it in all your RStudio projects.

It’s not currently possible to store these saved connections elsewhere, but you shouldn’t need to: If you’re collaborating with other users, you can share an R script (or R notebook, etc.) which establishes the connection, and once other users establish the connection it’ll be saved to their own private list of connections.

You can remove a connection from your history by clicking on the connection and then using the Delete icon in the upper right (note that you must be disconnected in order to do this).

Comments