Data Mart: Setting up R

by
The PEARS data mart can be used in combination with R and RStudio as follows.

Prerequisites

  1. Visit https://posit.co/download/rstudio-desktop/ to install both R and RStudio, if you haven’t already.
  2. Check with the PEARS Client Success team to ensure the IP address of the computer or server running R has been added to the data mart firewall.
  3. Check with your IT staff to ensure incoming and outbound traffic on the data mart port is allowed to the computer or server running R.
  4. Obtain the host, port, database, user, and password from the PEARS Client Success team.

Setup

Certificates

Before a trusted connection can be established with the data mart, Amazon’s RDS certificates must be downloaded to a local folder on the device.
  1. Download a certificate bundle from Amazon. You will either need the specific bundle for the us-east-1 region or the AWS global bundle.
  2. Place the file downloaded into a location on your local drive that can be accessed by R.
    • One suggestion is under the root of the drive, such as C:\Certificates\.
    • If you have limited access to your local drive, consider a location under your user folder, such as C:\Users\<username>\Certificates\.

Package Installation

R can connect directly to the data mart using the RPostgres library along with the standard DBI library.
install.packages("RPostgres")
install.packages("DBI")
It is recommended that you not provide your password in clear text as part of your script. One way to handle your password more securely is to utilize the keyring with an initial one-time step. The below script will prompt for the password and save it securely for future use.
The PEARS Client Success team will provide your username and password along with the other connection information.
install.packages("keyring")

library(keyring)

# One-time setup to store password
key_set("PEARS_datamart")  # You'll be prompted to enter the password securely

Usage

Following is a sample R script that will connect to the data mart and list all available tables. You will need to replace the values for host, port, dbname, and user with those provided by the PEARS Client Success team. Additionally, sslrootcert should reference the certificate file downloaded as part of the setup described above.
library(DBI)
library(keyring)

db_password <- key_get("PEARS_datamart")

con <- dbConnect(
   RPostgres::Postgres(),
   host = "host name",
   port = 12345,
   dbname = "data mart name",
   sslmode = "verify-full",
   sslrootcert = "C:/Users/<username>/Certificates/global-bundle.pem",
   user = "username",
   password = db_password
)

dbListTables(con)
dbDisconnect(con)