RStudio Desktop

Audience: Data Owners and Users

Content Summary: RStudio Desktop is an integrated development environment (IDE) for R. This page outlines the process of connecting RStudio to the Immuta Query Engine and running basic SQL queries. For detailed instructions on connecting RStudio to ODBC databases see the official RStudio ODBC documentation.

Prerequisite: You must set up your Immuta SQL account before using the Immuta Query Engine with RStudio. See Creating an Immuta user SQL account for details.

Setup

The Immuta Query Engine exposes a PostgreSQL ODBC connection that you can use to query your Immuta data sources using SQL. Before adding a PostgreSQL ODBC connection in RStudio, you must first install and configure the following:

1. PostgreSQL ODBC Driver
2. R-DBI ODBC

Installing the PostgreSQL ODBC Driver

You will need psqlODBC installed in order to connect to the Immuta Query Engine.

1. Navigate to the psqlODBC downloads page.

2. Download the latest version of psqlODBC (at the bottom of the page).

   • If your workstation is 32-bit, select the link that ends with x86.

   • If your workstation is 64-bit, select the link that ends with x64.

     

3. Once the download completes, open the zip file and double click the .msi file.

   • Follow the prompts to install psqlODBC.

   • Once complete, close the installation wizard.

     

Installing R-DBI ODBC

RStudio uses the R-DBI compliant ODBC package to make connections to ODBC data sources, like the Immuta Query Engine. Install the latest version from CRAN:

install.packages("odbc")

Once the installation is complete you will have a working R-DBI ODBC installation.

Connecting RStudio to the Immuta Query Engine

Your connection to the Immuta Query Engine can be added to the RStudio Connections Pane to make connection management simple.

1. In RStudio, navigate to the Connections pane, and click New Connection.

   

2. In the New Connection dialog, choose PostgreSQL Unicode.

   

3. Enter your Immuta SQL Connection credentials in the Parameters field. See viewing SQL connection information for details on obtaining the required connection information. In order to use this connection string with RStudio, the formatting must be altered slightly.

   Change your connection string from the postgresql:// format to an ODBC compliant format. For example if your Immuta connection string was

   postgresql://immutauser:password@demo2.immuta.com:5432/immuta?sslmode=require
   

   you would enter the following in the Parameters box:

   Username=immutauser;Password=password;Database=immuta;ServerName=demo2.immuta.com;Port=5432;SSLMode=require
   

4. Click Test Connection, and then click OK to close and save the connection dialog.

   

5. Once the connection information is saved, RStudio will start the connection and you can begin to explore the immuta database in the Connection pane.

   

At this point, you have successfully configured Rapid SQL with a connection to your Immuta Query Engine. From here you can run queries against the tables you have access to in the Immuta Query Engine. For more information about accessing data using the Query Engine see the SQL Access Pattern.

Examples of Basic R-DBI ODBC Queries

library(odbc)
con <- dbConnect(odbc::odbc(), .connection_string = "Driver={PostgreSQL Driver};Username=immutauser;Password=password;Database=immuta;ServerName=demo2.immuta.com;Port=5432;SSLMode=require")

# List Tables in the Immuta database
dbListTables(con)
 [1] "orders"   "applications"          "loan_info"     "diagnoses"
 [5] "ticket_data"              "transactions"

# Run a Query
result <- dbSendQuery(con, "SELECT * FROM applications ORDER BY origin")

# Retrieve the first 100 results
first_100 <- dbFetch(result, n = 100)