Big Analytics with R, Cassandra, and Hive

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

 

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

 

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.