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

Setup

R can connect directly to the data mart using the RMariaDB library. Please follow these instructions carefully as the PEARS data mart requires a custom configuration file to support the required encryption.
First, install the binary packages within R by running the following command:
install.packages("RMariaDB")
Next, install the appropriate Connector/C and MySQL client libraries as instructed here: https://github.com/r-dbi/RMariaDB. For example, on a Mac use the following terminal commands:
brew install mariadb-connector-c

brew install mysql-connector-c++
Finally, create a file on the computer running R named something like datamart.cnf with the following contents:
[client]
ssl-enforce=1
ssl-verify-server-cert=0
You can place this file in your home folder, but note the location as it will be referenced below.

Usage

Following is a sample R script that will connect to the data mart and list all available tables. You may need to update the path and file name for default.file. You will also need to replace the host, dbname, username, password, and port with the values provided by Canopy.
library(RMariaDB)
library(DBI)

con <- dbConnect(RMariaDB::MariaDB(),
   default.file = normalizePath("~/datamart.cnf"),
   host="*",
   dbname="*",
   username="*",
   password="*",
   port=12345,
   timeout=20,
   mysql = FALSE,
)

dbListTables(con)
dbDisconnect(con)
Run this script to confirm connection.