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:
Installing the PostgreSQL ODBC Driver
You will need psqlODBC installed in order to connect to the Immuta Query Engine.
- Navigate to the psqlODBC downloads page.
-
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
.
- If your workstation is 32-bit, select the link that ends with
-
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.
-
In RStudio, navigate to the Connections pane, and click New Connection.
-
In the New Connection dialog, choose PostgreSQL Unicode.
-
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 waspostgresql://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
-
Click Test Connection, and then click OK to close and save the connection dialog.
-
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)