Stashing and playing with raw data locally from the web

It is getting easier to get data directly into R from the web. Often R packages that retrieve data from the web return useful R data structures to users like a data.frame. This is a good thing of course to make things user friendly.

However, what if you want to drill down into the data that’s returned from a query to a database in R? What if you want to get that nice data.frame in R, but you think you may want to look at the raw data later? The raw data from web queries are often JSON or XML data. This type of data, especially JSON, can be easily stored in schemaless so-called NoSQL databases, and queried later.

A brief aside: What are JSON and XML? This is what JSON looks like (ps if you ever wonder if your JSON is correct, go here):

But don’t worry if it looks complicated – the project I talk about below, sofa, tries to make the interface to JSON and XML easy. Web APIs almost always return either JSON or XML, so this is the raw data.

So here’s the use case I imagine, or workflow:

Query a database on the web, and choose to write the raw data to a local database.

Do whatever you want with the output R object – analyze, visualize, etc.

Now you want to go back and search through some of the raw data. But, that query took an hour. Since you wrote it to a local database, you can search the data.

If you hadn’t written it locally, you would have to make a new web call.

Note that if you are doing calls to web APIs that get small amounts of data you don’t need to worry too much as you can easily just do the call again.

I’ve started an R package to interact with the NoSQL database CouchDB. CouchDB is a schemaless database that speaks JSON, so you can store JSON and get back JSON (don’t worry XML, we got you covered – we can just wrap the XML in JSON before putting it in CouchDB). What’s especially cool is you can interact with CouchDB via a RESTful API. CouchDB doesn’t have full text search built in (though you can build map-reduce Views, basically preset queries on the database), so I added functions (and docs to help) to interact with the CouchDB River plugin for Elasticsearch, which provides powerful full text search via an API interface. But nevermind the tech details – all this just means you can search on the full text of your stored data.

There are plenty of databases you can interact with from R, so why CouchDB? For one, it makes a lot of sense to write to a NoSQL database since this blog post is dealing with a use case writing JSON, which isn’t a good fit for databases like MySQL, SQLite, PostgreSQL, etc. (though postgres allows you to write JSON). It didn’t have to be CouchDB, but at least to me it seems relatively easy to install, you can interact with it via an HTTP API (if you’re into that, which I am), and it has a nice web interface (navigate to http://localhost:5984/_utils/ after starting couchdb).

Is this for the casual R user? Probably not. But, I imagine there are R users out there that want some more flexibility when it comes to interacting with web data in R. It is nice and tidy to get back an R data.frame from a web call, but having the raw data at your fingertips could be super powerful. I’ll describe using an R package to interact with a web database with sofa baked in, and discuss a bit about the functions within sofa.

First start CouchDB in your terminal

You can do this from anywhere in your directory. See here for instructions on how to install CouchDB.

bash
couchdb

Then start elasticsearch in your terminal

See here for instructions on how to install Elasticsearch and the River CouchDB plugin.