R, via RStudio

Testing R (v…) and RStudio (v…) on:

MacBook Pro laptop (macOS High Sierra 10.13.6, 2.9 GHz Intel Core i5, 16GB 2133 MHz LPDDR3 memory)
MacBook Air laptop (macOS High Sierra 10.13.6, 1.8 GHz Intel Core i5, 8 GB 1600 MHz DDR3 memory)
Dell Precision T1650 desktop (Windows 10 Enterprise 2016 LTSB, Intel Xeon CPU E3-1270 V2 @ 3.50 GHz, 32GB RAM)

Setting up PostgreSQL Drivers

R connects to PostgreSQL data sources using ODBC generic connections. First, you will probably need to install something that gets the computer ready to manage ODBC connections, then you’ll have to install a specific PostgreSQL driver.

For Mac

Follow these instructions, summarized here:

Install homebrew if you don’t already have it
Use homebrew to install unixODBC
brew install unixodbc
Use homebrew to install psqlODBC driver
brew install psqlodbc

…

For Windows

Download the official PostgreSQL ODBC driver:

Go to downloads page
Click on the “msi” folder
Scroll to bottom and select newest version available

Note: use x86 files for 32-bit Windows, x64 for 64-bit windows
Download to computer
Extract the .msi file from the .zip file (double-click and select “Extract” at the top
Install from the .msi (important to have administrator privileges for this, I think)

You can check whether ODBC knows about the driver by opening the ODBC Data Sources program and looking on the Drivers tab

Connecting to the Data Warehouse

For Mac only

Run “odbcinst -j” in your Terminal to find the correct path the unixodbc config files - specifically the “odbcinst.ini” file
(On my system, it was in /usr/local/etc)

In a text editor, open odbcinst.ini and add information about the new PostgreSQL driver.

[PostgreSQL Driver]

Driver = /usr/local/lib/psqlodbcw.so

Note: the name inside the square brackets [] can be anything, but you have to remember this name to call this driver later.

Note: you should look inside /usr/local/lib make sure this is the correct path to your psqlodbc driver file.

For Mac and Windows

In R, install and load the “DBI” and “odbc” packages
Check for the correct name of the PostgreSQL driver by running this command:

odbc::odbcListDrivers()

The name of the driver should be the same as the name you gave the driver when you edited odbcinst.ini.

Make the connection using code like the following:

con <- DBI::dbConnect(

odbc::odbc(),

driver="PostgreSQL Driver",

database="ldp",

UID="yourusername",

PWD="yourpassword",

server="glintcore.net",

port=5432,

sslmode = "require"

)

Note: Insert the correct driver name returned from the previous command, as well as your correct login credentials.

Note: SSL should be On

Executing Custom SQL (more info)

Using DBI package:

Use basic structure: dbGetQuery(connection,
query)
When you pass in the query, it needs to be surrounded by single quotation marks - '…query…'
If you already have single quotation marks in your query, you can “escape them,” or keep them in your query without breaking the R code, by adding a “\” in front of them
e.g., 'select * from table where name = \'SMITH\';'

Using Rmd files:

If you are using Rmd files, you can create a SQL code chunk instead of an R code chunk.
The syntax is:
```{sql connection = name_of_connection, output.var = ”name_for_result”}
…normal SQL code…
```

Building Queries In Tool

If you are interested in recreating the original queries using R code, you may want to use dplyr/dbplyr as described at:

https://db.rstudio.com/getting-started/database-queries

Viewing and Saving Results

The results of the methods mentioned above should be a standard R data frame. R data frames can be exported to csv using “write.csv” (base R) or “write_csv” (readr package inside the tidyverse). See the standard help documentation for syntax information. Other output formats (e.g., Excel files) may be available using other R packages.

Browser not supported