2013/02/14

Several packages on CRAN provide (or relate to) interfaces between databases and R.  Here is a summary, mostly in the words of the package descriptions.  Remember that package names are case-sensitive.

The packages that talk about being DBI-compliant are referring to the DBI package (see below in “Other SQL”).

MySQL

dbConnect: Provides a graphical user interface to connect with databases that use MySQL.

RMySQL: The current version complies with the database interface definition as implemented in the package DBI 0.2-2.

TSMySQL: TSMySQL provides a MySQL interface for TSdbi. Comprehensive examples of all the TS* packages are provided in the vignette Guide.pdf with the TSdata package.

Oracle

RODM: This package implements an interface to Oracle Data Mining (ODM). It provides an ideal environment for rapid development of demos and proof of concept data mining studies. It facilitates the prototyping of vertical applications and makes ODM and the RDBMS environment easily accessible to statisticians and data analysts familiar with R but not fluent in SQL or familiar with the database environment.

ROracle: This is a DBI-compliant Oracle driver based on the OCI.

PostgreSQL

RpgSQL: DBI interface to PostgreSQL database via RJDBC.

RPostgreSQL: This package provides a Database Interface (DBI) compliant driver for R to access PostgreSQL database systems.

TSPostgreSQL: TSPostgreSQL provides a PostgreSQL interface for TSdbi. Comprehensive examples of all the TS* packages are provided in the vignette Guide.pdf with the TSdata package.

SQLite

filehashSQLite: Simple key-value database using SQLite as the backend.

RSQLite: This package embeds the SQLite database engine in R and provides an interface compliant with the DBI package. The source for the SQLite engine (currently, version 3.7.14) is included.

RSQLite.extfuns: This package uses SQLite’s loadable extension feature to provide a number of additional SQL functions and aggregates.

TSSQLite: TSSQLite provides an SQLite interface for TSdbi. Comprehensive examples of all the TS* packages are provided in the vignette Guide.pdf with the TSdata package.

Other SQL

DBI: A database interface (DBI) definition for communication between R and relational database management systems. All classes in this package are virtual and need to be extended by the various R/DBMS implementations.

ODB: This package provides functions to create, connect, update and query HSQL databases embedded in Open Document Databases (.odb) files, as OpenOffice and LibreOffice do.

RODBC: An ODBC database interface.

RJDBC: RJDBC is an implementation of R’s DBI interface using JDBC as a back-end. This allows R to connect to any DBMS that has a JDBC driver.

sqldf: This one is an outlier: Manipulate R data frames using SQL.

sqlutils: This package provides utilities for working with a library of SQL files.

TSodbc: TSodbc provides an ODBC interface for TSdbi. Comprehensive examples of all the TS* packages are provided in the vignette Guide.pdf with the TSdata package.

Cassandra

RCassandra: This package provides a direct interface (without the use of Java) to the most basic functionality of Apache Cassandra such as login, updates and queries.

MongoDB

RMongo: MongoDB Database interface for R. The interface is provided via Java calls to the mongo-java-driver.

rmongodb: Provides an interface to MongoDB for R.

fame

fame: Read and write FAME databases.

TSfame: TSfame provides a fame interface for TSdbi. Comprehensive examples of all the TS* packages are provided in the vignette Guide.pdf with the TSdata package.

HDF5

h5r: A package for reading and writing HDF5 files.

hdf5: Interface to the NCSA HDF5 library. The last update was in 2009, and the h5r package seems to be more comprehensive.

H2

RH2: DBI/RJDBC interface to h2 database. h2 (currently, version 1.3.170) is included.

Binary versions on CRAN

Here is a quote from CRAN:

Packages related to many database systems must be linked to the exact 
version of the database system the user has installed, hence it does 
not make sense to provide binaries for packages
	RMySQL, ROracle, ROracleUI, TSMySQL, dbConnect
although it is possible to install such packages from sources by
	install.packages('packagename', type='source')
after reading the manual 'R Installation and Administration'.

Questions

What have I missed?

What do I have wrong?

What are your recommendations for narrowing down choices?

Updates

2013 November 08: see also “Translating between R and SQL: the basics”

Tags: database, SQL