Connecting R to An SQL Database (PostgreSQL, MySQL, Oracle)

-
While reading data into R isn't difficult, there are a variety of reasons why you would want R to directly pull data from a database. Whatever your situation, I will take you through the process of connecting R to that DB and querying data from it.

My favorite SQL platform is PostgreSQL because of its simplicity and versatility. In other words, I use R for most things and PostgreSQL works well with R. You can obtain it here, if needed. I suggest that you download RStudio if you do not already have it as it makes installing packages and other essential tasks easier.

The following code is essentially the same for all SQL platforms, the difference being which R package you load. For postgreSQL we use,

library(RPostgreSQL)

# for a local instance of postgres
con<-dbConnect(dbDriver("PostgreSQL"), dbname="myDB",host="localhost",port="5432")

# or to connect to a server it will look something like...
con<-dbConnect(dbDriver("PostgreSQL"), dbname="myDB",host="hostname",port="xxxx",username="user_name",password="myPassword")

If you don't want your username and password to appear in the R script, you can save a spreadsheet document as a .csv file with your username and password in them and read that in. For example,

library(magrittr)
usepass<-read.csv("credentialsDocuments",header=F,stringsAsFactors=F) %>% as.matrix()
con<-dbConnect(dbDriver("PostgreSQL"), dbname="myDB",host="hostname",port="xxxx",username=usepass[1],password=usepass[2])

For MySQL, it is essentially the same, but with a different R package.

library(RMySQL)
con<-dbConnect(dbDriver("MySQL"), dbname="myDB",host="hostname",port="xxxx",username="user_name",password="myPassword")

Connecting to an Oracle DB can be a bit trickier because the "ROracle" package doesn't download and install like most other R packages. There are a few things that need doing before you are able to install the package.
  1. You'll need to first install oracle instant client found here. ROracle needs instant client to work.
  2. Some environmental variables need to be set within the R environment so the package can find needed files when it attempts to install. Set the path to wherever you put the instant client download. Assuming you put the files on the C drive, it would look like this. 
Sys.setenv('ORACLE_HOME' = 'C:/instantclient_18_3', 'OCI_INC' = 'C:/instantclient_18_3/sdk/include','OCI_LIB64' = 'C:/instantclient_18_3')

You can install the package from source, so I downloaded it and installed it from a local directory. You can also use Rstudio to install the package and it will ask you if you would like to install from source. Select 'yes'.

install.packages("file_path/ROracle_1.3-2.tar.gz",repos=NULL,type="source")


With that, the ROracle package should successfully install. Your code to connect will be essentially the same as connecting to the other platforms, at this point.

***Update (June 2019): I recently installed the latest version of R (3.6.0), and I had some additional struggles trying to reinstall ROracle. Upon attempting to install from source, it had an error claiming it couldn't find a header file called "ociver.h" even though that file is in instantclient_18_3/sdk/include. The way around this was to copy that file over to R's include file (C:/program files/R/R 3.6.0/include). Copy ociver.h from instant client to that include file directory and the installation should complete successfully.

library(ROracle)
con<-dbConnect(dbDriver("Oracle"), dbname="myDB",host="hostname",port="xxxx",username="user_name",password="myPassword")

Querying the database will be the same regardless of SQL platform.

query<-'SELECT *
FROM mytable'

myDat<-dbGetQuery(con,query)
dbDisconnect(con)

That should get you up and running with R pulling data from a database. I hope that was helpful, and if you have any further questions, feel free to reach out to me at https://worth.yt/lazybayesian

Comments

Post a Comment

Popular posts from this blog

How to Get Started Playing Super Metroid / Link to the Past Crossover Randomizer.

-
I've purchased and enjoyed classic consoles like the Super Nintendo Classic and replayed some of the beloved games from my childhood. After redoing a game once, I'm good for a while, and am left hoping for some way to make it more interesting once the nostalgia wears off. One way to make classic games more interesting was to watch speed runs. I've been a fan of watching speed running classic games, like Super Metroid, for some time now. Recently, I noticed many runners starting to participate in randomizer races and was immediately fascinated. The dynamic is different than typical speed-running which focuses on runners mastering mostly repetitive strategies and precise execution. Admittedly, that is still quite entertaining, however, randomizers add a new dimension where you have to think through game logic in order progress. It also adds an element where your regular Joe can play the game and enjoy a new twist every time without spending countless hours mastering particu...
Read more

Two-Step fix for rJava library installation on Mac OS

-
I've been using R for some time and over the years I've had one consistent nagging problem.  rJava Perhaps the single most temperamental library in the whole history of R. If you are like me, you likely try to avoid anything Java based, like using openxlsx  instead of xlsx . I don't use Java but a number of libraries I do use, have it as a dependency. For example, I like to use qdap because it has a lot of nice tools for qualitative analysis, which of course uses Java. The big problem is that rJava never installs properly and gives some error along the lines of not being able to find jdk files, jni.h, or Java home when you try to call the library. I have a couple quick steps here that can get rJava up and running quickly. I haven't noticed this issue in Windows which means the library is probably written for Windows and the developer hasn't bothered to make it function out-of-the-box on Mac OS. Two quick steps and you can get rJava working in R on Mac OS. Downloa...
Read more

Structural Machine Learning in R: Predicting Probabilistic Offender Profiles using FBI's NIBRS Data

-
Image
What is Structural Machine Learning? Most machine learning tasks are designed for classifying data, but what if you have multiple outcomes? Not only do you have multiple outcomes (Y's or output variables), but you also need to enforce specific relationships among those outcomes and your predictors (X). Traditional ML can do many things, but this is not one of them. Traditional ML classifiers have one outcome and they attempt to classify that univariate outcome. A genre of machine learning that handles multiple outcomes and allows data scientists to specify a structure among all variables of interest is called "structured prediction". This genre of ML has existed in the literature for many years, but isn't something I've come across much, so I figured I would present a simple form of structured prediction and motivate it with an illustrative example. The Problem I'll motivate this with the idea of criminal profiling. It's an interesting subject and somethin...
Read more