Support

Getting Started with RStudio Professional Drivers

Follow

RStudio makes it easy to use R with databases. You can install RStudio Professional Drivers anywhere you use RStudio professional products. Use these instructions to download, unpack, and configure RStudio professional drivers on your system.

For more information on how to obtain our drivers, visit RStudio Professional Drivers or contact our sales team. For more information on how to use the drivers, see our website on Using Databases with R

Quick Start

Before installing, make sure you meet the System Requirements for RStudio Professional Drivers.

  1. Install the unixODBC driver manager. Do not use iodbc. In addition, if you are working with Oracle, install its other system dependencies such as the Oracle Instant Client.

  2. Using the installer that you downloaded from RStudio Professional Drivers, install RStudio Professional Drivers as root into /etc/<rstudio-drivers>. By default this command will download and configure all drivers:
    $ ./odbc-install.sh
  3. Verify that that your odbcinst.ini file was updated with the correct driver locations. Use odbcinst -j to find this file.

  4. Test your connection with isql (see Testing Your Connection below).
     
  5. [Optional] If you are using RStudio Server Pro version 1.1 or later, create additional DSN's or R snippets as desired (see Connecting with RStudio below).

  6. Install the latest odbc R package with devtools. Note that this also installs the DBI package. Do not use RJDBC or RODBC.
    install.packages("devtools")
    devtools::install_github("rstats-db/odbc")
    
  7. Test your connection in R with DBI::dbConnect and odbc::odbc(). If you are using the RStudio IDE, you can use the "New Connection" dialog to help you write the connection string.
    con <- DBI::dbConnect(odbc::odbc(), 
           Driver = "Driver Name", 
           Database = "Database", 
           UID = "User", 
           PWD = "Password", 
           Server = "Server", 
           Port = 5432

Using the Installer

You can install RStudio professional drivers by running the odbc-install.sh script. We recommend installing as root. By default, the driver installer will fetch, extract, and configure all available drivers. For a current driver list, see RStudio professional drivers.

The driver installer is highly configurable so you can control the installation process. For a complete list of options, run odbc-install.sh --help.

odbc-install [drivers] [--download|-d path] [--odbcinst|-o path]
             [--install|-i path] [--preview|-p] [--uninstall|-u]
             [--reinstall|-r] [--check|-c] [--version|-e version]
             [--verbose|-v] [--eula|l] [--help|-h]

Terms and conditions

You will be asked to accept the terms and conditions subject to the RStudio EULA when you run the installer.  Be sure you agree with these terms before installing and using the drivers.

By installing drivers with this tool I hereby acknowledge and agree
that the drivers may only be used with RStudio commercial software and
may not be used on a standalone basis or with other software. Further,
I agree that the drivers are subject to the RStudio end user license
agreement at https://www.rstudio.com/about/eula/ and is "Software"
under such agreement and I agree to comply with all terms and
conditions thereof.

Usage

Use these options to uninstall and reinstall drivers. You can also check or preview installation actions.

  --uninstall        Uninstalls all drivers or specific ones.
  --preview          When this flag is present, this script will
                     only output the set of actions to be performed.
  --reinstall        Forces the drivers to be reinstalled.
  --verbose          Print verbose progress statements.
  --check            Checks that all drivers installed correctly; not
                     applicable for custom driver installs.
  --eula             Accept EULA terms without prompt.
  --help             Prints this help message.

Installation paths

We recommend that you install drivers under /etc/<rstudio-drivers>. The installer will configure your odbcinst.ini file with the location of the newly installed drivers.

  --install          The path to extract and install the ODBC drivers,
                     all ODBC drivers will be extracted into this path.
  --download         The path to the downloaded ODBC drivers. Useful
                     when downloading and installing in separate locations.
  --odbcinst         The path to 'odbcinst.ini', if not specified,
                     'odbc_config --odbcinstini' or  'odbcinst -j'
                     will be used.

Release versions and bitness

You can control which release version and bitness of the drivers you want to install if you need to install historic releases. Currently there is only one RStudio release version.

  --version          The drivers version formatted as #.#.
  --x32              Use the x32 drivers.
  --x64              Use the x64 drivers.

Control the installation process

You can control each step of the installation process. These options may be helpful if your servers are offline, or if you are administering multiple servers.

  --fetch            Only download the drivers.
  --extract          Only extract the drivers.
  --configure        Only configure the drivers.

Install a single driver

You can install a single driver by specifying the driver name in the command.

  --hive             Installs the Hive driver.
  --impala           Installs the Impala driver.
  --oracle           Installs the Oracle driver.
  --postgresql       Installs the PostgreSQL driver.
  --salesforce       Installs the Salesforce driver.
  --sqlserver        Installs the SQLServer driver.

Testing your connection

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:

[test]
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

Connecting with RStudio Server Pro

If you're using RStudio Server Pro version 1.1 or later, see Using RStudio Connections.

The RStudio IDE will automatically detect specific database connections that an administrator sets up on your system. There are four ways to set up connections that will automatically populate in the new connection wizard. For more information, see Using RStudio Connections. The four methods are:

  1. odbc.ini: ODBC DSN's installed on your system. If the odbc package is installed, any system DSN (Data Source Name) you have installed will be listed.

  2. odbcinst.ini: ODBC drivers installed on your system. If the odbc package 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.

  3. 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.

  4. 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.

Troubleshooting

For troubleshooting common problems, please refer to Troubleshooting Connections. 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 odbcinst -j command.
  • Examine specific driver dependencies with the ldd tool.
  • Enable driver logging to identify problems when a new connection is created.

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 LogPath (e.g. /tmp). Make sure that users have permissions to write files to LogPath. Set UseLogPrefix=1 to create a unique log for every user and process with a connection. For more information please see the diver installation and configuration guide under the docs folder in your driver download bundle. 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
UseLogPrefix=1

Support

RStudio professional software is covered under our support agreement. If you are still having problems with RStudio professional products and drivers, you can visit our support page or send us an email at support@rstudio.com. Include a detailed description of your problem along with your R code, your environment, and your driver logs. Please note that establishing a database connection requires several pieces of software, and priority support does not cover databases, system dependencies, and other open source software.

References

Comments