RStudio+MySQL
CRG
2023-08
Introducción
Este documento pretende mostrar con un sencillo ejemplo, la manera en la que podemos conectarnos desde RStudio con un sistema de gestión de bases de datos relacionales (Microsoft SQL Server, Oracle, Teradata, PostgreSQL, Apache Cassandra, Amazon Athena, MongoDB, Google BigQuery, Salesforce, MySQL, etc); para este caso práctico nos conectaremos con MySQL.
En el ejemplo mostrado, nos conectaremos a nuestra base de datos contenida en nuestro servidor local (nuestra computadora). De esta manera podemos realizar consultas y extraer la información para realizar análisis y graficos con paquetes de R, como el tidyverse.
Librerías
library(tidyverse)## ── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
## ✔ dplyr 1.1.2 ✔ readr 2.1.4
## ✔ forcats 1.0.0 ✔ stringr 1.5.0
## ✔ ggplot2 3.4.2 ✔ tibble 3.2.1
## ✔ lubridate 1.9.2 ✔ tidyr 1.3.0
## ✔ purrr 1.0.1
## ── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
## ✖ dplyr::filter() masks stats::filter()
## ✖ dplyr::lag() masks stats::lag()
## ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errorslibrary(DBI)
library(RODBC)
library(odbc)
library(RMySQL)Estas son las librerías usadas para el desarrollo de la pŕactica.
tidyverse:conjunto de paquetes de software para el lenguaje de programación R que se utiliza para realizar análisis de datos.DBI: El paquete DBI ayuda a conectar R a los sistemas de administración de bases de datos (DBMS). DBI separa la conectividad al DBMS en un “front-end” y un “back-end”. El paquete define una interfaz implementada por backends de DBI como: RPostgres, RMariaDB, RSQLite, odbc, bigrquery.
RODBC : El paquete RODBC implementa la conectividad de base de datos ODBC. Funciones de R que se comunican directamente con la interfaz ODBC.
odbc: El paquete odbc proporciona un Interfaz de base de datos (DBI) para Controladores de conectividad abierta de bases de datos (ODBC - Open Data Base Connectivity) . Existen controladores ODBC para casi todas las bases de datos ampliamente utilizadas.
RMySQL:paquete de R que permite la conexión y manipulación de bases de datos MySQL, proporciona funciones y herramientas para interactuar con bases de datos MySQL utilizando el lenguaje de programación R.
El paquete RMySQL ofrece varias funciones para realizar tareas como:
- Establecer conexiones con bases de datos MySQL.
- Ejecutar consultas SQL en la base de datos.
- Recuperar y manipular datos de la base de datos en R.
- Cargar los resultados de las consultas en estructuras de datos en R, como data frames.
Drivers o controladores
Se recomienda hacer una lectura sobre la lista de controladores para obtener información sobre el controlador que requiere, de acuerdo al gestor de base de datos con el que desee trabajar:
La función odbcListDrivers(), es una función de R que
pertenece al paquete odbc, se utiliza para obtener una
lista de los controladores ODBC instalados en el sistema. Un controlador
ODBC es un software que actúa como un puente entre las aplicaciones y
las bases de datos específicas. Cada base de datos puede tener su propio
controlador ODBC.
# Obtener la lista de controladores ODBC disponibles
drivers <- odbcListDrivers()
# Imprimir la lista de controladores
print(drivers)## [1] name attribute value
## <0 rows> (or 0-length row.names)Crear conexión
La función dbConnect() del paquete DBI de R
se utiliza para establecer una conexión activa entre R y una base de
datos específica. A través de esta conexión, puedes enviar consultas SQL
a la base de datos y recuperar los resultados en R para su análisis
posterior.
Estructura de la conexión:
con <- dbConnect(driver,
user = "usuario",
pass = "contraseña",
dbname = "basedatos",
host = "localhost", ...)
driver: Indica el controlador de base de datos que se utilizará para la conexión. Esto dependerá del paquete que estés utilizando para la conexión a la base de datos. Por ejemplo, si estás utilizando el paquete RMySQL, el valor sería MySQL().user: Nombre de usuario para acceder a la base de datos.password: Contraseña del usuario para acceder a la base de datos.dbname: Nombre de la base de datos a la que deseas conectarte.host: Dirección del host o servidor donde se encuentra la base de datos.
Una vez que hayamos terminado de trabajar con la base de datos, es importante cerrar la conexión usando la función
dbDisconnect(con)para liberar recursos y asegurarnos de que la conexión se cierre adecuadamente.
Vamos a crear nuestra conexión, haciendo uso del driver MySQL, ya que es el gestor de base de datos que está instalado en esta computadora.
Recordemos que el driver a usar, dependerá del sistema de gestión de bases de datos que tengamos instalado.
En este caso, los parámetros de la conexión son obtenidos a través de la interfaz de MySQL Workbench. Estos son los parámetros que se definieron cuando se creó la base de datos. (Estos parámetros cambian de acuerdo a la base de datos creada de cada usuario)
Habiendo confirmado los parámetros de conexión, vamos a crear nuestro enlace con nuestro gestor de base de datos MySQL.
Note que no hemos usado el parámetro dbname.
Creando la conexión:
conexion <- dbConnect(
drv = RMySQL::MySQL(),
server = "localhost",
user = "root",
port = 3306,
pass = "contraseña_base_de_datos"
)Haciendo consultas
dbGetQuery(): es una función utilizada
para enviar consultas SQL a una base de datos a través de una conexión
establecida y recuperar los resultados de la consulta en forma de un
objeto en R.
Como primer argumento tenemos conn, aquí, debemos
indicar la conexión creada anteriormente y mediante el argumento
statement definimos, entre comillas simples o comillas
dobles, la consulta que queremos realizar a la base de datos en
formato SQL. Por ejemplo:
dbGetQuery(conn = conexion, statement = 'show databases')Esto nos muestra las bases de datos que tenemos almacenadas en nuestro gestor de base de datos. Cotejando con MySQL Workbench podemos ver que la información obtenida corresponde a los esquemas o bases de datos que tenemos almacenadas en MySQL.
Ahora es cuando haremos las consultas a una base de datos en particular, por ejemplo a la base de datos combustibles, usando SQL y su sintaxis:
dbGetQuery(conn = conexion, statement = "use combustibles")Sigamos haciendo consultas con el lenguaje SQL:
dbGetQuery(conn = conexion, statement = "show tables")dbGetQuery(conn = conexion, statement = 'describe departamentos')Si definimos el parámetro dbname de manera explícita, la
conexión se realizará directamente con la base de datos que
seleccionemos y estará disponible para realirzarle consultas.
conexion <- dbConnect(
drv = RMySQL::MySQL(),
dbname = "combustibles",
server = "localhost",
user = "root",
port = 3306,
pass = "contraseña_base_de_datos"
)Realicemos otras consultas a nuestra base de datos seleccionada:
dbGetQuery(conn = conexion,
statement = 'select MUNICIPIO, VALOR_PRECIO, DEPARTAMENTO from precios_combustibles
where VALOR_PRECIO > 8300
and DEPARTAMENTO = "AMAZONAS"')dbGetQuery(conn = conexion, statement = 'select date_format(FECHA_REGISTRO, "%Y") from precios_combustibles')dbGetQuery(conn = conexion, statement = 'select sum(VALOR_PRECIO) as "sumatoria", count(id) as "conteo", min(VALOR_PRECIO),
avg(VALOR_PRECIO), DEPARTAMENTO
from precios_combustibles
group by DEPARTAMENTO')Teniendo en cuenta que la información de las consultas que realizamos, cargan como dataframe, podemos aplicar funciones de R sobre los resultados, por ejemplo, pidamos la clase a la consulta que realizamos, con esto validamos que es un data frame:
class(dbGetQuery(conn = conexion, statement = 'select sum(VALOR_PRECIO) as "sumatoria", count(id) as "conteo", min(VALOR_PRECIO),
avg(VALOR_PRECIO), DEPARTAMENTO
from precios_combustibles
group by DEPARTAMENTO'))## [1] "data.frame"También es posible asignar la consulta a un objeto.
consulta <- dbGetQuery(conn = conexion,
"select VALOR_PRECIO, PRODUCTO
from precios_combustibles
where VALOR_PRECIO > 8500")Así mismo podemos aplicar otras funciones a este objeto que contiene la información de la consulta hecha. En este caso imprimimos con knitr, el resultado de la información.
consulta %>% knitr::kable()| VALOR_PRECIO | PRODUCTO |
|---|---|
| 9030 | GASOLINA CORRIENTE |
| 11390 | GASOLINA EXTRA |
Mediante otra consulta hecha a la base de datos, calculamos la media de los valores que obtuvimos.
precios <- dbGetQuery(conn = conexion,
statement = 'select VALOR_PRECIO
from precios_combustibles')
preciosmean(precios$VALOR_PRECIO)## [1] 9042Por supuesto, despues de tener nuestro marco de datos, podemos realizar aplicar las funciones de las librerías gráficas que tiene R.
dbGetQuery(conn = conexion, statement = 'select p.PRODUCTO, p.VALOR_PRECIO, d.DEPARTAMENTO, d.MUNICIPIO
from precios_combustibles2 as p
left join departamentos as d
on p.MUNICIPIO = d.MUNICIPIO') %>%
ggplot(mapping = aes(x = PRODUCTO, y = VALOR_PRECIO))+
geom_bar(stat = "identity" , fill = "cadetblue")
NOTA: si está trabajando en un documento RMarkdown, puede agregar un fragmento de código (chunk) SQL después de crear la conexión y podrá digitar y ejecutar código SQL directamente en este fragmento sin hacer uso de la función dbGetQuery(), sólo debe indicar en el parámetro
connectiondel fragmento el nombre de la conexión creada.
Cerrar conexión
Por último, una vez que hayamos terminado de trabajar con la base de
datos, es importante cerrar la conexión usando la función
dbDisconnect() para liberar recursos y asegurarte de que la
conexión se cierre adecuadamente.
dbDisconnect(conexion)## [1] TRUEConclusión
La sinergia entre MySQL y R en la ciencia de datos ofrece una excelente solución para interactuar con bases de datos de manera eficiente. Aprovechando R como lenguaje analítico, se logra una amalgama que potencia la exploración y análisis de información, estas herramientas facilitan la conexión con los gestores de bases de datos y la interacción con los datos almacenados, ofreciendo flexibilidad al usuario y una mayor capacidad analítica.