Stashing and playing with raw data locally from the web17 Jun 2013 R ropensci API database couchdb nosql
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):
This is what XML looks like:
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
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
First start CouchDB in your terminal
You can do this from anywhere in your directory. See here for instructions on how to install CouchDB.
Then start elasticsearch in your terminal
See here for instructions on how to install Elasticsearch and the River CouchDB plugin.
cd /usr/local/elasticsearch bin/elasticsearch -f
Simultaneously write data to CouchDB along with API calls using the alm package to get altmetrics data on PLoS papers. Ping to make sure CouchDB is on
Create a new database
Write couchdb database name to options
List the databases
Search for altmetrics normally, w/o writing to a database
Search for altmetrics normally, while writing to a database
Make lots of calls, and write them simultaneously
Writing data to CouchDB does take a bit longer
Search using elasticsearch
tell elasticsearch to start indexing your database
Search your database
Write a view - here letting key be the default of null
Get info on your new view
Get data using a view
Delete the view
Well, if no one uses this, then probably nothing. Though, if people think this could be useful:
- It would be cool to make easy hooks into any package making web calls to allow users to write data to CouchDB if they choose to, sort of like the example above with rplos.
- Perhaps automate some of the setup for CouchDB for users, making system calls so they don’t have to.
- Performance: As shown above, simultaneously writing data to CouchDB takes longer than not doing so - removing this time difference will make writing to couch more palatable.
What do you think?
What is your reaction to this post? Do you have a need for this sort of tool? Do you have similar use cases that could be addressed with