Skip to content

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. Follow the instructions for installing psqlODBC on Windows and then proceed.

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.

    New Connection

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

    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.

    Enter Immuta Connection

  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.

    Immuta Connection

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)