R and MongoDB

• Big Data, MongoSoup and NoSQL Statistics
• R statistics with MongoDB and Examples
• Summary & Questions

• mechanism for storage and retrieval
• simplicity of design
• less constrained consistency models <-> schema-less
• Cassandra, HBase, AWS SimpleDB, MongoDB, CouchDB, Riak, Neo4J

• relational schema: defines columns, their names, and their datatypes
• error to insert data that doesn't fit the schema
• schema-less database allows any data
  • structured with individual fields and structures
  • reduces ceremony and increases flexibility
• implicit schema in schema-less
  • code that manipulates the data needs to make some assumptions about its structure, such as the name of fields
  • data that doesn't fit: leading to errors

{'first':'martin', 'last':'fowler',
 'zip_id': {'city':'munich', 'zip':80883},
 'card_id':2334}
{'first':'pramod', 'last':'sadalage', 
 'zip_id':'Munich, Lindwurmstr. 97', 
 'card_id':1333}
{'first':'jez', 'surname':'humble', 
 'zip_id': {'zip':94104, 'city':'Munich'}, 
 'card_id':[2334, 6534]}

• cross-platform document-oriented database system
• most popular NoSQL database system
• German MongoDB as a Service: MongoSoup
  • cloudControl Add-On
  • all features available: shared / dedicated hosting, replica set, sharding
  • 24/7 support available

• SQL provides a standard language to filter, aggregate, group, sort data
• SQL in new places: Hive, Impala, …
• ODBC provides SQL interface to non-database data (Excel, CSV, text files)
• R stores relational data in data.frames (extended lists)

running SQL statements on R data frames

library(sqldf)
sqldf("select * from iris limit 2")

  Sepal_Length Sepal_Width Petal_Length Petal_Width Species
1          5.1         3.5          1.4         0.2  setosa
2          4.9         3.0          1.4         0.2  setosa

sqldf("select count(*) from iris")

  count(*)
1      150

• RMySQL package provides an interface to MySQL
• RPostgreSQL package provides an interface to PostgreSQL
• ROracle package provides an interface for Oracle
• RJDBC package provides access to databases through a JDBC interface
• RSQLite package provides access to SQLite
  (SQLite engine is included)
• One big problem:
  all packages read the full result in R memory

on CRAN there are two packages to connect R with MongoDB

• rmongodb supported by MongoDB, Inc.
  • powerful for big data
  • difficult to use due to BSON objects
• RMongo
  • easy to use
  • limited functionality
  • reads full results in R memory
  • difficult to install on MAC OS X

library(RMongo)
Sys.setenv(NOAWT=1) # for MAC OS X
mongo <- mongoDbConnect("cc_JwQcDLJSYQJb", "dbs001.mongosoup.de", 27017)
dbAuthenticate(mongo, username="JwQcDLJSYQJb", password="RSXPkUkXXXXX")

dbShowCollections(mongo)

[1] "zips"           "ccp"            "system.users"   "system.indexes"
[5] "test"           "test_data"     

• developed on top of the MongoDB supported C driver

library(rmongodb)
mongo <- mongo.create(host="dbs001.mongosoup.de", db="cc_JwQcDLJSYQJb", username="JwQcDLJSYQJb", password="RSXPkUkXXXXX")

mongo

[1] 0
attr(,"mongo")
<pointer: 0x105d2f940>
attr(,"class")
[1] "mongo"
attr(,"host")
[1] "dbs001.mongosoup.de"
attr(,"name")
[1] ""
attr(,"username")
[1] "JwQcDLJSYQJb"
attr(,"password")
[1] "RSXPkUkxRdOX"
attr(,"db")
[1] "cc_JwQcDLJSYQJb"
attr(,"timeout")
[1] 0

buf <- mongo.bson.buffer.create()
mongo.bson.buffer.append(buf, "state", "AL")

[1] TRUE

query <- mongo.bson.from.buffer(buf)
query

    state : 2    AL

res <- mongo.find.one(mongo, "cc_JwQcDLJSYQJb.zips", mongo.bson.from.JSON('{"state":"AL"}'))
res

    city : 2     ACMAR
    loc : 4      
        0 : 1    -86.515570
        1 : 1    33.584132

    pop : 16     6055
    state : 2    AL
    _id : 2      35004

res <- mongo.find.batch(mongo, "cc_JwQcDLJSYQJb.zips", mongo.bson.from.JSON('{"state":"AL"}'))
head(res, n=4)

    city         loc       pop   state _id    
val "ACMAR"      Numeric,2 6055  "AL"  "35004"
val "ADAMSVILLE" Numeric,2 10616 "AL"  "35005"
val "ADGER"      Numeric,2 3205  "AL"  "35006"
val "KEYSTONE"   Numeric,2 14218 "AL"  "35007"

buf <- mongo.bson.buffer.create()
query <- mongo.bson.from.buffer(buf)
buf <- mongo.bson.buffer.create()
err <- mongo.bson.buffer.append(buf, "user", 1)
err <- mongo.bson.buffer.append(buf, "type", 1)
field <- mongo.bson.from.buffer(buf)
out <- mongo.find(mongo, "cc_JwQcDLJSYQJb.ccp", query, fields=field, limit=1000)
res <- NULL
while (mongo.cursor.next(out)){
    value <- mongo.cursor.value(out)
    Rvalue <- mongo.bson.to.list(value)
    res <- rbind(res, Rvalue)
}

res <- mongo.find.batch(mongo, "cc_JwQcDLJSYQJb.ccp", mongo.bson.empty(), fields=mongo.bson.from.JSON('{"user":1, "type":1}'), limit=1000)

mongo.get.keys(mongo, "cc_JwQcDLJSYQJb.ccp") # implemented

mongo.apply(mongo, "test.people", 1, keys="age", mean)

mongo.summary(mongo, "test.people") # implemented

mongo.aggregate( ... )

mongo.table( ... )

• R based MongoDB User Interface
• R packages shiny and rmongodb
• less than 200 lines of code

https://github.com/comsysto/ShinyMongo

• NoSQL can make statistician's life unnecessary miserable
• R can access databases
• MongoDB and rmongodb ready for Big Data
• start playing around with rmongodb and provide feedback
  • https://github.com/mongosoup/rmongodb 

• What functionality is required in R DB packages?

• What were your biggest issues with R DB packages?

• thanks a lot for your attention
• there are R trainings in December 2013 in Munich
  • http://comsysto.com/events.html#r
• we are hosting many events and meetups

Email: markus@mongosoup.de
Twitter: @cloudHPC