This article answers frequently asked questions from:
- RStudio Connections
- Getting Started with RStudio Professional Drivers
- System Requirements for RStudio Professional Drivers
If you need more information on using databases with RStudio, see db.rstudio.com.
When I click New Connection, why don't I see the kind of connection I want to make?
The RStudio IDE will automatically detect specific database connections that are set up on your system. There are four ways to set up connections that will automatically populate in the new connection dialog. For more information, see Using RStudio Connections. The four methods are:
- odbcinst.ini: ODBC drivers installed on your system. If the
odbcpackage is installed, any ODBC driver you have installed will be listed. The method for installing ODBC drivers varies by system. See our page on Setting up ODBC Drivers.
- odbc.ini: ODBC DSN's installed on your system. If the
odbcpackage is installed, any system DSN (Data Source Name) you have installed will be listed.
- R snippets: Connections supplied by your administrator. An administrator may put connection snippets into a folder to make them available to end users. If you're an administrator, see our page on RStudio Connection Snippet Files for more information.
- R packages: Connection types defined by installed R packages. Any R package can supply a connection. Currently, the sparklyr package and odbc package define connection types. Install one of these packages using e.g.,
When I try to connect, why do I always get a message saying I need to install a package?
The latest versions of the R packages that provide database connectivity are required.
If you are using a CRAN repository such as MRAN that does not include the latest package versions, you may not be able to install these from your default CRAN repository. You can either temporarily add our CRAN repository, or install the development version of the package from e.g., Github (see the package's help page for more information).
I made a connection in R code, but why isn't it showing up in the Connections pane?
The package which makes the connection needs to tell RStudio that the connection has been created. Not all R packages that connect to data do this. If you're the package author, you can implement the RStudio Connections Contract to integrate with the Connections pane.
How do I set up an ODBC connection?
If you want to connect R to a database, you will need to install and configure an ODBC driver on the system where you are running R. You can get ODBC drivers from third parties, the database vendor themselves, or from RStudio if you have purchased our professional products. If you work on a platform other than Windows, you may need to install the
unixODBC driver manager if it does not already exist (contact your system administrator for assistance). You may also need to configure your driver location in the
odbcinst.ini file and data source names (DSN) in
odbc.ini. See Setting Up ODBC Drivers for more information. Also refer to your database vendor documentation for specific database material.
What is a DSN?
A data source name (DSN) is a configuration stored in
odbc.ini that contains information about a database connection. The DSN has a name like "SQL Server Marketing DB", and points to a single data source and a specific driver listed in the
odbcinst.ini file. A DSN is one of the four methods RStudio uses to discover existing connections. For more information, please read Setting Up ODBC Drivers.
What are the odbc.ini and odbcinst.ini files?
The system file odbcinst.ini contains information about ODBC drivers available to all users, and the odbc.ini file contains information about DSN's available to all users. You can locate these files with
These files can exist at the system (i.e. server) level or at the user level. The system files are odbcinst.ini and odbc.ini (note no leading dot), and the user file is ~/.odbc.ini in each user's home directory (note leading dot). For more details, please see everything you wanted to know about odbcinst but were afraid to ask.
Should I use unixODBC or iodbc?
RStudio recommends using unixODBC, and the odbc R package depends on it. For more information, see the unixODBC User Manual.
Should I use odbc or RODBC?
The RODBC package is not compatible with DBI; therefore, we recommend switching to the newer odbc package. The DBI package is a common interface between R and database management systems. It has a long history with R, and has recently been enhanced with help from the R Consortium. It is important that modern database connectors for R be DBI-compliant. Compared to the existing RODBC package, odbc is also faster (~3x for reading, ~2x for writing), translates date/time data types, and is under active development.
How can I use JDBC with RStudio?
JDBC drivers are incompatible with the odbc R package maintained by RStudio. If you want to use JDBC drivers along with Java, you will have to install these open-source R packages: rJava, RJDBC, and DBI. The R console will recognize JDBC connections, but they will not be displayed in the the Connections pane unless you or a third party has created a connections contract. RStudio does not provide professional support for JDBC connections.
How do I enable driver manager tracing?
Typically, the best way to identify connection issues is to enable driver logging with your specific drivers (see your driver documentation for instructions). However, you can also enable driver manager tracing as another way to troubleshoot connection issues. For example, in non-Windows platforms, you can enable tracing by modifying
[ODBC] Trace=yes TraceFile=/path/to/file/name.log
How can I connect to an Oracle database?
In addition to installing a driver, Oracle also requires you to download and install the Oracle Instant Client on the system where you are running R. If you use Oracle in your enterprise, you may already have this software installed. If not, you will have to create an Oracle account and accept the licensing terms before downloading the Oracle Instant Client software.
RStudio Server FAQ
How do I configure ODBC drivers with Linux?
If you are having problems connecting to your databases from RStudio, it’s likely that your system is not configured properly. We recommend these steps before contacting support.
- Test your connection from the command line with the isql tool, which is part of unixODBC. You will have to set up a data source name (DSN) first.
- Check your configuration. Driver paths are stored in
odbcinst.ini. DSN's are stored in
odbc.ini. You can locate these files with the
- Examine specific driver dependencies with the ldd tool.
- Enable driver logging to identify problems when a new connection is created.
How can I test a database connection outside of R?
You should test your database connections from the command line before creating new connections in RStudio. You can test connections by running
isql, a command line tool for interactive SQL. Note that
isql requires you to create a data source name first. To create a new DSN, open
odbc.ini in a text editor and create a new test entry:
Driver = SQLServer
Server = my.server.name
Database = dbname
Port = 1433
The driver entry in the DSN should point to the corresponding driver name or path listed in
odbcinst.ini. To test this connection from a terminal enter:
$ isql test
Where are my ODBC configuration files?
Configuration files are typically located under
/etc/. You can find the location of
odbc.ini files by running the
odbcinst -j command:
$ odbcinst -j unixODBC 2.3.1 DRIVERS............: /etc/odbcinst.ini SYSTEM DATA SOURCES: /etc/odbc.ini FILE DATA SOURCES..: /etc/ODBCDataSources USER DATA SOURCES..: /root/.odbc.ini SQLULEN Size.......: 8 SQLLEN Size........: 8 SQLSETPOSIROW Size.: 8
How do I find dependencies for my ODBC drivers?
ODBC drivers on Linux are shared objects with filenames ending in
*.so. You can print the shared object dependencies with the
ldd command. This is useful in identifying missing dependencies.
$ ldd sqlserver/bin/lib/libsqlserverodbc_sb64.so linux-vdso.so.1 => (0x00007fff4ffe2000) libdl.so.2 => /lib64/libdl.so.2 (0x00007f96c8b4e000) libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f96c8932000) librt.so.1 => /lib64/librt.so.1 (0x00007f96c8729000) libstdc++.so.6 => /lib64/libstdc++.so.6 (0x00007f96c8421000) libm.so.6 => /lib64/libm.so.6 (0x00007f96c811f000) libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f96c7f08000) libc.so.6 => /lib64/libc.so.6 (0x00007f96c7b47000) /lib64/ld-linux-x86-64.so.2 (0x00007f96cb7ba000)
How do I enable driver logging with RStudio Professional Drivers?
You can enable driver logging to track activity and troubleshoot issues. Open your driver configuration file (e.g.
rstudio.sqlserverodbc.ini) in a text editor and set values for
LogLevel (e.g. 6) and
/tmp). Make sure that users have permissions to write files to
UseLogPrefix=1 to create a unique log for every user and process with a connection. Important: Logging decreases performance and can consume a large quantity of disk space; only enable logging long enough to capture an issue.
[Driver] LogLevel=6 LogPath=/tmp
Where can I find driver specific guides?
The installation and configuration guide for your specific driver can be found in the bundle that you downloaded from RStudio Professional Drivers. Look for the PDF file under
How do I install RStudio Professional Drivers?
RStudio Professional Drivers are ODBC data connectors that help you connect to some of the most popular databases. If you are using RStudio Server Pro, RStudio Connect, or Shiny Server Pro, then you can download and use RStudio Professional Drivers at no additional charge. For more information please see RStudio Professional Drivers or contact our sales team.
What are the system requirements for RStudio Professional Drivers?
To establish a connection to your database from R, you will need root access and the following components stored on your system:
- At least one RStudio professional product
- The unixODBC driver manager
- The odbc R package
- RStudio Professional Drivers
You will also need the proper permissions and credentials to query your database. For detailed requirements, see System Requirements for RStudio Professional Drivers.
When will you add my data source to RStudio Professional Drivers?
We initially released RStudio Professional Drivers for some of today's most popular data sources. We are committed to increasing the number of data connectors we support in the future. If we don't support your database, we welcome your feedback. Please contact our sales team or email us at firstname.lastname@example.org to let us know what you would like us to add.
How can I connect to an Oracle database?
Oracle also requires Oracle Instant Client on the system where you are running R. If you are working with RStudio Server, you should point your
LD_LIBRARY_PATH at the installation path for the Oracle Instant Client. An easy way to do this is to add a line to
rserver.conf like this:
Note: Whenever you modify
rserver.conf, make sure to restart RStudio Server.
Why does my database catalog only show the first letter of each table?
The character encoding in the driver manager (UnixODBC) likely does not match the character encoding in the driver. Change this file
/etc/rstudio-drivers/amazonredshift/bin/lib/rstudio.amazonredshiftodbc.ini so that
DriverManagerEncoding=UTF-32. This assumes that the driver manager is using
UTF-32 encoding. You can check the character encoding in the driver manager by with instructions from this page. Here are the detailed instructions.
- If you are unsure which driver manager you are using, or where the driver manager is installed, contact your system administrator or see Driver Managers for more information.
- If you are using the unixODBC ODBC driver manager, check which setting to use:
- At a command prompt, type
- If you see the “
DSQL_WCHART_CONVERT” flag, then set
- Otherwise, set
- At a command prompt, type
- For more information about your ODBC driver manager, consult your system administrator or your ODBC Driver Manager documentation.