RPostgreSQL / PostgreSQL -> Serving Data to R - An "Ultra Newb's" Guide (Part 1)
After spending time wandering through Random Forests, I had the opportunity to work with some tools used on data from actual Forests. My friend Jay - that's him in dark shirt in the the picture below, circa 1985, probably explaining something to me - (yes, I'm in a sweater-vest)
Objective: Take some of the R tools and methods that Jay has been using to analyze forestry data, and re-purpose them for other things (and get a basic level of proficiency)
Problem: I'm utterly unfamiliar with PostgreSQL and RPostgreSQL, which are key ingredients for Tool to operate. What I hoped would be a plug and play over a couple of hours, turned out to take me a couple of evenings banging away trying to the SQL connected locally, and connecting with R.
What's below: Part 1 - The VERY basics on how all the pieces fit together, and how to hopefully safe you a little time getting your local system set up and running RPostgreSQL.
- SETWD > if you're borrowing source code, ensure one of the first things you do is modify the file to use the new location of transplanted files - e.g. setwd("C:/Users/Home/Documents/SampleAnalysis")
INSTALL + LIBRARY > if not already done, install RPostgreSQL package packages UI > install packages from CRAN > type in RPostgreSQL and will autocomplete library(RPostgreSQL) #then install library
Issues Worked Through
- Issue: PNG Function did not run well locally on mine (e.g. png(paste('stats/attributes.png', sep=""))
- FIX: - install.packages("Cairo") - need this to replace PNG with cairo PNG library(Cairo) > e.g. CairoPNG(paste('attributes.png'), width = pngwd, height = pnght, pointsize = 16)
- Issue: PERMISSIONS > ERROR: could not open file CSV for reading: Permission denied SQL state: 42501
- FIX: (1) move file to C:\Users\Public (so all users have full access) or (2) fiddle with security settings to add users like postgres to have permissions (I did it second way initially, but having trouble recreating, so opted for 1. Basically, you need to ensure Windows (or system) is "OK" with the user you've defined, reading the file. Need to explicit permission.
- Issue: Rookie with PostgreSQL - not sure if it's even connected/listening
- FIX: # run netstat -an from command line check 5432 is connected (established on 5432 is good, means DB is set up on right port, listening
- Other Info: Background - RPostgreSQL provides a DBI-compliant database connection from GNU R to PostgreSQL. https://code.google.com/p/rpostgresql/ Synopis - RPostgreSQL provides a DBI-compliant database connection from GNU R to PostgreSQL. Summary of basic usage 1. dbDriver(drv, ...) instantiates the driver object. Eg.
Interfacing to Local PostgreSQL deployment with R Studio to Run RPostgreSQL: (My Launch Sequence)
Now, lets' go to R Studio and engage from the Other side
About this blog
Description is...<br/>Data Analytics & Visualization Blog - Generating insights from Data since 2013
Created: July 25, 2014Englishfrançais