The data presentation graphic at right, and many of those sprinkled throughout this article (click them for better resolutions), are highlights from an enthusiast’s gallery which have been produced by the R statistical software package. R is an open-source programming language and environment which has gained much popularity among academics who want to apply statistical methods in their research and add visual pizazz to their publications. It is an interpreted language, thus fairly easy to get started with. Also, being open-source, it is free and widely extensible; many packages and libraries are available and the list keeps growing. This recent STRATA conference primer session gives several recommendations on how to get started with R on your laptop:

         • Download R v2.15.3 (@ http://cran.r-project.org/)
         • Download R Studio IDE (@ http://www.rstudio.com/ide/)
         • install.packages(c("plyr", "reshape2", "stringr", "ggplot2"))
w h i t e s p a c e
Interfacing Oracle and R

Oracle is integratable with R in several ways. First, you could acquire an Exadata machine and take advantage of the fact that Oracle R Enterprise (ORE) comes bundled with it as part of the Oracle Advanced Analytics Option. This offering is part of Oracle’s overall BI marketing strategy, pushing their Exalytic BI-on-a-desktop machines. You can view a series of free educational videos about ORE. Let’s assume, in solidarity with frugal enthusiasts worldwide, that you’d like to circumvent this $ix-figure (at least) approach.

Another choice would be to simply purchase the Oracle Advanced Analytics Option and attach it to your existing database license. But we are going to explore the simplest route, which is to assume you already have or can acquire open-sourced R tools and are looking for a way to feed Oracle database data to it for analytic processing. There’s a proviso in this, in that Oracle marketing literature states that a real limitation exists for R programs which have to deal with large quantities of data natively, and a design element of the ORE package is to widen the pipeline and place the datasize strain onus on the database. Our current purpose, however, is simply to get rolling with R and Oracle.

Consuming Database Tables from R

I checked out the latest release of the ROracle package, whose primary author and maintainer is Denis Mukhin at Oracle. It enables ODBC-style calls to open connections to a database and fetch result sets. There’s a detailed Oracle blog entry by Mark Harnick which tests the relative performance of three Oracle-to-R connectivity tools: ROracle, RODBC, and RJDBC. It compares things like connection times in bulk and SELECT results for various table shapes (row counts and column counts). Worth looking at.

Here are some examples which should look familiar to ODBC users:

    dbcon <- dbConnect(drv, username="scott",
         password="tiger")
    dbrs <- dbSendQuery(dbcon, "select * from emp
         where deptno = 10")
    db_df1 <- fetch(dbrs)

As of this writing, ROracle binaries are freely downloadable for Linux and Unix platforms, but if you’re running on top of Windows you will need to do some linking and building. The more generalized db-access package RODBC is also available within R and can fetch from Oracle databases.

Scalar (one-dimensional) data structures within R are known as vectors. Matrices (2- or N-dimensional data structures) are known as data frames. In R, vectors are assigned and signified via the c() function. The cbind() function can be used to associate two identically sized vectors, and the data.frame() function sets up a matrix. A useful trick to know is that most R functions are self-documenting. To get help, you simply enter ‘?function-name‘.

    # Assign a vector variable
    listA <- c(2012,2011,2010)
    # Combine two vectors
    cbind(listA, listB)
    # Assign a data frame variable
    df1 <- data.frame(cbind(listA, listB))

A very common way to get external data into R is to use CSV files. These are the same flat file formats which are consumed by Excel. R uses the read.csv() method for this, and the target of the read.csv function is a data frame (which needn’t be previously declared). In R there is the concept of a ‘working directory’ where input and output files can reside; this is where incoming CSVs should be placed. You define this work folder via the File/Change Dir… option within the R menu bar. It’s easy enough to have SQL*Plus output a result set as an external table here as well. I’ll mention two ways to do this. The first is to monkey with SQL so that it outputs CSV-formatted rows, including comma-separated field values and quoted column names. Tom Kyte has a nice generalized PL/SQL procedure which does this; it accepts parameters for a table to output and info concerning the output location. You can override the default base query it uses with your own SQL string. Read about it here or just view the procedure below. The other handy way is to use SQL Developer if you’ve got it. Just click on the right choices; it lets you output CSVs from any query result display.

                                                                                                                             view Tom Kyte procedure 

Plotted Oracle Data

I used the following SQL to create some picture-worthy output and exported it as a CSV and then placed the file into my R working directory. The table involved is a cleaned up list of English words, numbering about 76000. (Read this earlier post concerning password hacking if you want to get a sense of this wordfile’s origin and how it’s been manipulated.) The query counts the number of English words according to which letter they begin with.
w h i t e s p a c e

w h i t e s p a c e

                                                                                                                                                view the CSV file 

Now, let’s see some examples of R code and the resulting R visuals for this data. In the interactive R session below, an actual window will open up with the resulting graphic whenever an appropriate function is typed. Graphic functions within R normally have many options controlling display style and color and so forth, which you can specify with arguments. Sample R help panels for some of the functions used are linked below to give an idea of the range of visualization options as well as the documentation style used in R. A good way to explore is to try out the examples usually given at the bottom of most function manual pages. I saved the graphics as JPGs and have displayed them below the R session.

         • Some R function manual pages:     read.csv()     plot()     barplot()     pie()
w h i t e s p a c e

w h i t e s p a c e

R point plot – click to enlarge

R barplot – click to enlarge

R pie chart – click to enlarge

Plot: Ordinary R plots can designate datapoints in several ways, the pch=19 option directs it to use large black dots as shown.
w h i t e s p a c e
Barplot: With this function there are even more customizations possible. In this instance the border enclosing the graph has been removed and the cex.names=0.7 directive controls the height of the y-axis labels. A useful visual aid here would be to overlay vertical lines on the bar chart indicating 2%, 4%, 6% etc. of the entire English wordcount. Since our wordlist contains about 76K words, the 2%-intervals would occur about every 1520 words. So, “S” for example, at over 8000 words, accounts for about 11% of all English words. Overlays are possible in R graphics but they are beyond the scope of this present article.
w h i t e s p a c e
Pie: In the pie chart, I eliminated the five least frequent letters to begin English words because they really bunch up visually. Taken together these five (J,Q,Z,Y,X) account for less than 1400 words, which is a little bit more than the letter “V”, or somewhat less than the letter “W” for comparison sake. In contrast the top six letters (S,C,P,A,M,B) account for the first letter of about 50% of all English words. The optional parameter col=rainbow(7) causes the pie slice colors to rotate among seven ‘rainbow’ hues.
w h i t e s p a c e

R Coding Tutorials

To develop fluency you can explore R independently of Oracle right on your desktop. Nathan Yau provides an intro tutorial aimed at getting basic charts and graphs working. You can either impute some data directly, as he shows, or avail yourself of some the large built-in supply of real world datasets which are included with R. Further tutorials are available at the flowingdata.com website, though you’ll need to sign up as a member to access them. Still another recommended resource is the brief e-book “Data Mashups in R” from O’Reilly Press. It walks you through a multi-faceted data conceptualization problem involving home foreclosures and geospatial mapping, providing an orientation to R along the way.
w h i t e s p a c e

R does Animation!

mandelbrot set animated GIF: click me!

Finally, if you want to mess around with some pure R code, independently of a database, you might enjoy this demo snippet from Wikipedia. It implements 20 generations of a Mandelbrot Set as an animation. Click on the image to activate the .GIF. If you have access to an R environment you can try fiddling with some parameters (red), or even color assignments, and observing the differences. Named after the 20th century French mathematician who specialized in Complexity Theory, Mandelbrot sets became quite popular thirty years ago as a way to visually model fractal curves, seen by some as the underlying construction mechanism for forms and shapes in Nature.

Make certain first that you have installed the library package ‘caTools’; it produces the animation from the computed array of complex numbers. You’ll notice from this snippet how loosely typed R is as a programming language. Spontaneous variables can be assigned arrays, integers, complex numbers, and function results, all without formal declaration.

w h i t e s p a c e

w h i t e s p a c e

~RS