Back to Blog

Big Analytics with R, Cassandra, and Hive

date: May 11, 2012

The R project is taking over the data world. With a plethora of algorithms at your fingertips it's not hard to see why R is such a powerful data analysis tool. I was fortunate enough to work with some of the original developers of the then S-Engine at bell labs out of college and even managed to write a few CRAN packages. In fact the ROracle package is now shipped with Oracle's big data appliance (who would have ever imagined!)

There has been a lot of work recently to integrate Hadoop with R by means of writing map/reduce in R. Most of the data scientists I've spoken to don't really want this, they really want ways to get data into R and use data sampling and other estimation techniques (for example hive sampling). This post will show how you can interact with Cassandra from R as well as with the Cassandra Hive Driver.

Reading data from Cassandra with JDBC

The prerequisites are the RJDBC module, Cassandra >= 1.0 and the Cassandra-JDBC driver. In the demo I've put the driver in the same directory as the cassandra jars.

The example code assumes you have run through the Portfolio Manager Demo that comes with DSC/DSE

 

  #Load RJDBC
  library(RJDBC)
   
  #Load in the Cassandra-JDBC diver
  cassdrv <- JDBC("org.apache.cassandra.cql.jdbc.CassandraDriver",
  list.files("/Users/jake/workspace/bdp/resources/cassandra/lib/",pattern="jar$",full.names=T))
   
  #Connect to Cassandra node and Keyspace
  casscon <- dbConnect(cassdrv, "jdbc:cassandra://localhost:9160/PortfolioDemo")
   
  #Query timeseries data
  res <- dbGetQuery(casscon, "select * from StockHist limit 10")
   
  #Transpose
  tres <- t(res[2:10])
   
  #Plot
  boxplot(tres,names=res$KEY,col=topo.colors(length(res$KEY)))
  title("BoxPlot of 10 Stock Price Histories")

view rawgistfile1.r hosted with  by GitHub

 

BoxPlot of 10 Stock Price Histories

Alternately there is a new RCassandra package which looks nice too.

R, Cassandra, and Hive

For accessing Hive and Cassandra from R, I will be using DataStax Enterprise.

First, startup the hive server: dse hive --service hiveserver

 

  #Load RJDBC
  library(RJDBC)
   
  #Load Hive JDBC driver
  hivedrv <- JDBC("org.apache.hadoop.hive.jdbc.HiveDriver",
  c(list.files("/Users/jake/workspace/bdp/resources/hadoop",pattern="jar$",full.names=T),
  list.files("/Users/jake/workspace/bdp/resources/hive/lib",pattern="jar$",full.names=T)))
   
  #Connect to Hive service
  hivecon <- dbConnect(hivedrv, "jdbc:hive://localhost:10000/default")
   
  #Create Hive table mapping to Cassandra ColumnFamily
  tmp <- dbSendQuery(hivecon,"create external table StockHist(row_key string, column_name string, value double)
  STORED BY 'org.apache.hadoop.hive.cassandra.CassandraStorageHandler'
  WITH SERDEPROPERTIES ('cassandra.ks.name' = 'PortfolioDemo')")
   
  #Run Hive Query to get returns
  hres <- dbGetQuery(hivecon,"select a.row_key ticker, AVG((b.value - a.value)) ret
  from StockHist a JOIN StockHist b on
  (a.row_key = b.row_key AND date_add(a.column_name,10) = b.column_name)
  group by a.row_key order by ret")
   
  #Plot
  barplot(hres[,2],names.arg=hres[,1],col = topo.colors(length(hres[,2])), border = NA)
  title("Avg 10 Day Return for all Stocks")

view rawgistfile1.r hosted with  by GitHub

 

Avg 10 Day Return for all Stocks

Conclusion

I hope this post has shown how simple it is to access your Cassandra data from R, and why combining it with the hundreds of statistical methods the community has added is a powerful combination.



 

Subscribe to Our Blog Now

Thank You for Signing Up!