Si estás aquí, probablemente estés buscando una forma sencilla de hacer tu primer ETL con R y MySQL. Bienvenido, estás en el lugar correcto. En este post, te guiaré a través de un ETL para principiantes con R y MySQL de la manera más amena y accesible posible.
Hoy les quiero compartir una experiencia personal que tuve utilizando R para hacer un ETL con datos de MySQL. Les mostraré paso a paso cómo transformé un conjunto de datos, creé tablas de dimensiones, y finalmente escribí la tabla de hechos en un archivo CSV.
Cargar librerías
Primero, necesitamos cargar las librerías necesarias para realizar nuestras tareas. Aquí están las que usé:
library(tidyverse) # Conjunto de paquetes para manipulación de datos y visualización, incluye dplyr, ggplot2, etc.
library(dotenv) # Biblioteca para cargar variables de entorno desde un archivo .env
library(DBI) # Interfaz de base de datos para R, proporciona una serie de clases y métodos para manipulación de bases de datos.
library(RMySQL) # Extensión de DBI que proporciona una interfaz de conexión a bases de datos MySQL.Funciones
Luego, creé una función para crear tablas de dimensiones. Esta función toma un conjunto de datos, el nombre de la columna de dimensión y la conexión a la base de datos como parámetros. Aquí está el código de la función:
crearTablaDimensiones <- function(data, columna_dimension, con) {
nombre_tabla_mysql <- paste0("d_",columna_dimension)
# Convertir valores de la columna a minúsculas
data[[columna_dimension]] <- tolower(data[[columna_dimension]])
# Validar si la tabla de dimensiones existe
if (!dbExistsTable(con, nombre_tabla_mysql)) {
# Crear la tabla de dimensiones con una columna de ID autoincremental
query_create <- paste0("CREATE TABLE ", nombre_tabla_mysql, " (",
"id INT AUTO_INCREMENT PRIMARY KEY,",
columna_dimension, " VARCHAR(255))")
dbExecute(con, query_create)
}
# Obtener los valores únicos de la columna de dimensión
dimension_values <- unique(data[[columna_dimension]])
# Crear la tabla de dimensiones
dimension_table <- data.frame(columna_dimension = dimension_values, stringsAsFactors = FALSE)
# Validar si los valores existen en la tabla de dimensiones en MySQL
consulta <- paste0("SELECT ", columna_dimension, " FROM ", nombre_tabla_mysql)
valores_existentes <- dbGetQuery(con, consulta)
nuevos_valores <- setdiff(data[[columna_dimension]], valores_existentes[[columna_dimension]])
if (length(nuevos_valores) > 0) {
# Insertar los nuevos valores en la tabla de dimensiones en MySQL
nuevos_registros <- data.frame(placeholder_name = nuevos_valores)
names(nuevos_registros)[names(nuevos_registros) == "placeholder_name"] <- columna_dimension
tryCatch(
{
# Código que puede generar una excepción
dbWriteTable(con, nombre_tabla_mysql, nuevos_registros, append = TRUE, row.names = FALSE)
},
error = function(e) {
# Manejo de la excepción
print("Se ha producido una excepción:")
print(e$message)
}
)
}
# Reemplazar en la tabla de hechos las descripciones por códigos
consulta <- paste0("SELECT * FROM ", nombre_tabla_mysql)
dimension_table <- dbGetQuery(con, consulta)
codigos <- match(data[[columna_dimension]], dimension_table[[columna_dimension]])
data[[columna_dimension]] <- codigos
# Devolver la tabla de hechos con códigos en lugar de descripciones
return(hechos = data)
}Leer archivo *.csv
Ahora, debemos leer el archivo CSV que contiene nuestros datos. Aquí está cómo lo hice:
(data <- read_csv("G:/Mi unidad/Galileo/2023 Q2/Econometria en R/HT1/amazon_sales_report.csv"))
Limpiar nombres de columnas
Después de cargar nuestros datos, es esencial realizar un paso que a veces puede pasar desapercibido, pero que es fundamental para un análisis de datos efectivo y sin problemas: limpiar los nombres de las columnas. Los nombres de las columnas son nuestra guía para entender qué información se almacena en cada una de ellas, por lo que tener nombres claros, descriptivos y fáciles de manejar puede ahorrar mucho tiempo y evitar confusiones más adelante.
El primer paso que tomé fue obtener los nombres de las columnas originales del conjunto de datos con colnames(data). Luego, para facilitar la manipulación de las columnas más adelante en el análisis, convertí todos los nombres de las columnas a minúsculas con tolower(nombres_columnas). Esto garantiza la consistencia en todo el conjunto de datos, ya que R es sensible a mayúsculas y minúsculas.
A continuación, utilicé la función gsub para reemplazar cualquier espacio en blanco o guión en los nombres de las columnas por guiones bajos. Esto se hace para evitar problemas al referenciar las columnas más adelante en el análisis, ya que los espacios en blanco y los guiones pueden causar problemas de sintaxis en R.
Finalmente, con colnames(data) <- nuevos_nombres, asigné los nuevos nombres a las columnas del conjunto de datos. Verifiqué los nombres de las columnas actualizados con colnames(data) para asegurarme de que los cambios se realizaron correctamente.
Este proceso garantiza que nuestros nombres de columnas estén limpios, sean consistentes y fáciles de manejar, lo que facilitará mucho el análisis posterior.
# Obtener los nombres de las columnas
nombres_columnas <- colnames(data)
# Convertir los nombres de columna a minúsculas y reemplazar espacios en blanco por guiones bajos
nuevos_nombres <- tolower(nombres_columnas)
nuevos_nombres <- gsub(" ", "_", nuevos_nombres)
nuevos_nombres <- gsub("-", "_", nuevos_nombres)
# Asignar los nuevos nombres a las columnas
colnames(data) <- nuevos_nombres
# Verificar los nombres de las columnas actualizados
colnames(data)
Conectándonos a MySQL
Las tablas de dimensiones se guardarán en una base de datos MySQL. Es hora de hablar con nuestra base de datos MySQL. Aquí te muestro cómo lo hice:
# Cargar variables de entorno desde el archivo .env. En este archivo se encuentran las credenciales de la base de datos
load_dot_env(file = ".env")
# Establecer la conexión a la base de datos MySQL
con <- dbConnect(MySQL(), user = Sys.getenv("DB_USER"),
password = Sys.getenv("DB_PASSWORD"),
dbname = Sys.getenv("DB_NAME"),
host = Sys.getenv("DB_HOST"))Creando la Tabla de Dimensiones y la Tabla de Hechos
¡Ahora viene la parte emocionante! Vamos a crear nuestras tablas de dimensiones y hechos. Aquí te muestro cómo lo hice:
# Dimensiones para crear tablas de dimensiones
dimensiones <- c("status", "fulfilment", "sales_channel", "ship_service_level", "category", "style", "size", "courier_status", "ship_city", "ship_state", "currency","sku","asin")
for (i in 1:length(dimensiones)) {
# Utilizar la función crearTablaDimensiones
if (i == 1){
resultado <- crearTablaDimensiones(data, dimensiones[i], con)
} else {
resultado <- crearTablaDimensiones(resultado, dimensiones[i], con)
}
}
(fact <- resultado)
Listo. Las tablas de dimensiones han sido creadas y almacenadas en la base de datos.
Guardando la Tabla de Hechos en un CSV
Este proceso es para crear el dataset que usarán los alumnos del curso Econometría en R de la maestría en Business Intelligence de Universidad Galileo. Entonces voy a guardar la tabla de hechos en un CSV. Si tu deseas también lo puedes guardar en la base de datos, que es lo más común.
#regreso a los nombres originales
colnames(fact) <- nombres_columnas
# Guardar el dataframe en un archivo CSV
write_csv(fact, file = "f_amazon_sales_report.csv")Cerrando la Conexión a MySQL
Finalmente, cerramos nuestra conexión a la base de datos MySQL. Nunca olvides este paso porque puedes afectar el desempeño de la base de datos si dejas demasiadas conexiones abiertas.
# Cerrar la conexión a la base de datos MySQL
dbDisconnect(con)¡Y ahí lo tienes! Acabamos de realizar un ETL sencillo con R y MySQL. ¿Ves? No es tan aterrador como parece. De hecho, es bastante divertido, ¿no crees? Recuerda, la práctica hace al maestro. Así que sigue practicando, sigue experimentando y, sobre todo, sigue disfrutando del emocionante mundo de los datos.
Si te ha gustado esta publicación o si tienes algún comentario o pregunta, por favor, sigamos la conversación en LinkedIn. ¡Me encantaría saber de ti! Y si te ha sido útil, ¿por qué no lo compartes con tus amigos? Recuerda, compartir es cuidar.