Receta: Integrar R y MySQL

Vamos a por una receta rápida que permite ejecutar desde el entorno R sentencias SQL contra una BD MySQL remota (así te ahorras tener que ejecutar las sentencias en MySQL, exportar el resultado e importarlo luego en R)

Primero, asumo que la BD es remota y no hay acceso directo (el puerto 3306 está filtrado) PERO sí tenemos acceso vía SSH. Así que construyo un túnel SSH contra MySQL (en un extremo del túnel tenemos localhost, escuchando en el puerto 3307. En el otro extremo, MySQL en su servidor, escuchando en el puerto habitual, 3306)

$ ssh -L 3307:localhost:3306  usuario@IP_SERVIDOR

Ok, ahora vamos a instalar la magia: el paquete r-cran-rmysql permite lanzar sentencias SQL contra una BD MySQL remota. Y lo tenemos paquetizado en Ubuntu 🙂

$ sudo apt-get install r-cran-rmysql

Para que las conexiones a MySQL desde R no requieran que tengamos que acordarnos continuamente del login, pass, db, etc. las apuntamos en my.cnf

$ sudo vi /etc/mysql/my.cnf
[nombreGrupo]
user            = usuario
password        = password
host            = 127.0.0.1
port            = 3307
database        = nombre_de_bbdd

¡Listo! Que empiece la fiesta:

$ R
> library(RMySQL)
Loading required package: DBI
> con <- dbConnect(MySQL(), group="nombreGrupo")
> dbListTables(con)

La primera línea carga el entorno R. La segunda carga la biblioteca RMySQL. La tercera lista las tablas de la BD.

¿Más ejemplos?

> rs <- dbSendQuery(con, "select * from tabla");
> dbHasCompleted(rs)
> data <- fetch(rs, n=50);
> data[1,]

La primera línea lanza una consulta SQL. La segunda recoge los resultados (50 tuplas para empezar). La tercera muestra la primera fila de esos resultados. Para recoger TODOS los resultados/tuplas, podríamos haber hecho:

> data <- fetch(rs, n=-1);

Las consultas también pueden lanzarse y visualizarse directamente así:

> dbGetQuery(con, "show tables");

Finalmente, si no quieres crear un fichero /etc/mysql/my.cnf, también puedes pasar los datos directamente desde la línea de comandos:

> library(RMySQL)
> drv <- dbDriver("MySQL")
> con <- dbConnect(drv, user="usuario", password="contraseña", dbname="nombreDB", host="127.0.0.1", port=3307)

Para más detalles, RTFM 😉

Bonus: R insiste en mostrar los resultados sólo a 80 columnas. Si tienes una pantalla grande, igual te interesa saber que a R se le puede indicar que hay espacio de sobra 🙂 así :

> options(width=150)