Using the DBI package in Renjin to connect to a variety of databases
For some months now, we have been working together with CWI Database Architectures group to bring some database sauce to Renjin. The first step in this line of work was to create a set of R DBI drivers for Renjin, so that people can run R scripts that talk to a SQL database.
Today we are happy to announce initial support for the following databases:
- SQLite, a small embedded system
- PostgreSQL, a mature, row-oriented database for transactional workloads
- MySQL, also a row-oriented database
- MonetDB, a column-oriented database for analytical workloads
- Oracle database, a row-oriented database produced and marketed by Oracle Corporation
We have worked hard to make our version of DBI source-compatible to the one in GNU R, so that your scripts that use DBI are likely to continue working with only minor changes.
Here is an example on how to use the DBI drivers, adapted from the RSQLite R package:
library(RSQLite)
con <- dbConnect(SQLite(), ":memory:")
dbListTables(con)
dbWriteTable(con, "mtcars", mtcars)
dbListTables(con)
dbListFields(con, "mtcars")
dbReadTable(con, "mtcars")
# You can run a specific query
res <- dbGetQuery(con, "SELECT * FROM mtcars WHERE cyl = 4")
print(res)
# Or fetch the entire table
res <- dbReadTable(con, "mtcars")
# Disconnect from the database
dbDisconnect(con)
This example uses the embedded SQLite database, where no installation is required. For information on how to install the other systems, please refer to the respective documentation. Here are short snippets on how to establish a connection to them:
library(RPostgreSQL)
con <- dbConnect(RPostgreSQL(), "jdbc:postgresql://localhost:5432/mydb", "myuser", "mypasswd")
library(RMySQL)
con <- dbConnect(RMySQL(), "jdbc:mysql://localhost:3306/mydb", "myuser", "mypasswd")
library(MonetDB.R)
con <- dbConnect(MonetDB.R(), "jdbc:monetdb://localhost:50000/mydb", "myuser", "mypasswd")
library(ROracle)
con <- dbConnect(ROracle(), url="jdbc:oracle:thin:@localhost", username="someuser", password="somepass")
We are curious to see what you will be doing with this new feature. If you run into any issues, please let us know by opening an issue in the corresponding GitHub repository or on the public Renjin mailing list.
Also, this package (which is Open Source like the rest of Renjin) shows how complex Java APIs such as JDBC can be easily exposed to R scripts running in Renjin. This allows you to combine the ease of statistical analysis using R with the flexibility and code of the Java ecosystem.
Read more at Renjin's blog or subscribe to the blog's RSS feed.