> For the complete documentation index, see [llms.txt](https://support.pears.io/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://support.pears.io/analyze/data-mart/setting-up-r.md).

# Setting up R

Connect R and RStudio to the PEARS data mart using the RPostgres and DBI libraries.

The PEARS data mart can be used in combination with R and [RStudio](https://posit.co/products/open-source/rstudio/) as follows.

## Prerequisites

{% stepper %}
{% step %}

#### Install R and RStudio

Visit <https://posit.co/download/rstudio-desktop/> to install both R and RStudio, if you haven't already.
{% endstep %}

{% step %}

#### Confirm firewall access

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.
{% endstep %}

{% step %}

#### Confirm network access

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.
{% endstep %}

{% step %}

#### Obtain credentials

Obtain the host, port, database, user, and password from the PEARS Client Success team.
{% endstep %}
{% endstepper %}

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

{% stepper %}
{% step %}

#### Download the certificate bundle

Download a [certificate bundle from Amazon](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.SSL-certificate-rotation.html). You will either need the specific bundle for the us-east-1 region or the AWS global bundle.

* Global (any AWS region): <https://truststore.pki.rds.amazonaws.com/global/global-bundle.pem>
* US East (N. Virginia): <https://truststore.pki.rds.amazonaws.com/us-east-1/us-east-1-bundle.pem>
  {% endstep %}

{% step %}

#### Place the file locally

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\`.
  {% endstep %}
  {% endstepper %}

### Package Installation

R can connect directly to the data mart using the `RPostgres` library along with the standard `DBI` library.

```r
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 script below will prompt for the password and save it securely for future use.

{% hint style="info" %}
**TIP:** The PEARS Client Success team will provide your username and password along with the other connection information.
{% endhint %}

```r
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. 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.

```r
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)
```