Persistencia de datos - Base de datos relacionales

Programación funcional y reactiva - Computación

Profesor: Ing. Santiago Quiñones

Docente Investigador

Departamento de Ingeniería Civil

Contenidos

Trabajando con bases de datos relacionales

Trabajo con una base de datos

Fundamentos

  • Existen diferentes técnicas para trabajar con bases datos

  • Pero, todas parten de un principio:

    • Conexión a la base

    • Enviar sentencias SQL que se ejecutarán

    • Recuperar los resultados y trabajar

Trabajo con una base de datos

Fundamentos

  • En programación, lo único que cambia es quien escribe el SQL

    • Manual

    • Semi-automático

    • Automático

    • Mapeo

    • Objeto Relación

Trabajo con una base de datos

Desde el nivel más bajo

  • Scala primo hermano de Java
  • Java DataBase Connectivity - JDBC
  • JDBC estándar, cada base de datos implementa su versión

Trabajo con bases de datos relacionales

Arquitectura

BASE DE DATOS

Script

CREATE TABLE MOVIE (
	ID int(11) NOT NULL AUTO_INCREMENT,
	ORIGINAL_TITLE varchar(1024) DEFAULT NULL,
	ID_RAW_DATA int(11) DEFAULT NULL,
	BUDGET double DEFAULT NULL,
	REVENUE bigint(14) DEFAULT NULL,
	RUNTIME int(11) DEFAULT NULL,
	RELEASE_DATE date DEFAULT NULL,
	PRIMARY KEY (ID)
);

BASE DE DATOS

Script

INSERT INTO MOVIE (ORIGINAL_TITLE, ID_RAW_DATA, BUDGET, REVENUE, RUNTIME, RELEASE_DATE) 
VALUES 
('Avatar', 19995, 237000000, 2787965087, 162, '2009-12-10'),
('Pirates of the Caribbean: At World''s End', 285, 300000000, 961000000, 169, '2007-05-19'),
('Spectre', 206647, 245000000, 880674609, 148, '2015-10-26'),
('The Dark Knight Rises', 49026, 250000000, 1084939099, 165, '2012-07-16'),
('John Carter', 49529, 260000000, 284139100, 132, '2012-03-07'),
('Spider-Man 3', 559, 258000000, 890871626, 139, '2007-05-01'),
('Tangled', 38757, 260000000, 591794936, 100, '2010-11-24'),
('Avengers: Age of Ultron', 99861, 280000000, 1405403694, 141, '2015-04-22'),
('Harry Potter and the Half-Blood Prince', 767, 250000000, 933959197, 153, '2009-07-07'),
('Batman v Superman: Dawn of Justice', 209112, 250000000, 873260194, 151, '2016-03-23');

Scala y Base de datos relacional

doobie library

Scala y Base de datos relacional

doobie library

Scala y Base de datos relacional

doobie library - dependencias

"org.tpolecat" %% "doobie-core" % "1.0.0-RC5",      // Dependencias de doobie
"org.tpolecat" %% "doobie-hikari" % "1.0.0-RC5",    // Para gestión de conexiones
"com.mysql" % "mysql-connector-j" % "8.0.31",       // Driver para MySQL
"com.typesafe" % "config"           % "1.4.2"       // Para gestión de archivos de configuración

Scala y Base de datos relacional

doobie library - import

import doobie._
import doobie.implicits._

import cats._
import cats.effect._

import cats.effect.unsafe.implicits.global

- base para trabajar con BD (Transactor, Query)

- facilita las consultas, y permite transformar resultados de consultas

- ayuda en el manejo de cálculos que pueden fallar, en la transformación de datos 

- manejo de efecto secundarios de manera funcional (IO: modela E/S, Resource: manejo de recursos que necesitan ser abiertos - BD)

-  proporciona un contexto global para ejecutar tareas de entrada/salida (IO) que usan Cats Effect

Scala y Base de datos relacional

doobie library - conexión

// conexión
  val xa = Transactor.fromDriverManager[IO](
    driver = "com.mysql.cj.jdbc.Driver", // JDBC driver
    url = "jdbc:mysql://localhost:3306/my_db", // URL de conexión
    user = "root", // Nombre de la base de datos
    password = "integrador", // Password
    logHandler = None // Manejo de la información de Log
  )

-  El Transactor se encarga de establecer y manejar la conexión con la base de datos

Scala y Base de datos relacional

doobie library - recuperando datos

def find(id: Int): ConnectionIO[Option[Movie]] =
    sql"SELECT MOVIE.ID, MOVIE.ORIGINAL_TITLE FROM MOVIE where MOVIE.ID = $id"
      .query[Movie]
      .option
// Consulta de un registro
  val result = find(4).transact(xa).unsafeRunSync()
  println(result.get)

cálculo diferido y puro: No se ejecuta inmediatamente, describe lo que se quiere hacer con la base de datos

Toma un cálculo diferido (como un ConnectionIO que ha sido "transaccionado" a IO) y lo evalúa, devolviendo el resultado final.

cálculo diferido y puro: No se ejecuta inmediatamente, describe lo que se quiere hacer con la base de datos

Scala y Base de datos relacional

doobie library - recuperando lista de objetos

def listAllMovies(): ConnectionIO[List[Movie]] =
    sql"SELECT MOVIE.ID, MOVIE.ORIGINAL_TITLE FROM MOVIE"
      .query[Movie]
      .to[List]
// Consulta de todos los registros de películas
  val movieList: List[Movie] = listAllMovies().transact(xa).unsafeRunSync()
  movieList.foreach(println)

Scala y Base de datos relacional

doobie library - recuperando un texto

def avgRuntime(): ConnectionIO[Double] =
    sql"""
          SELECT avg(m.RUNTIME) as avg_runtime
          FROM MOVIE m
         """.stripMargin
      .query[Double]
      .unique
// Consulta del promedio de runtime
  val runTimeAvg: Double = avgRuntime().transact(xa).unsafeRunSync()

  println(runTimeAvg)

Scala y Base de datos relacional

doobie library - inserción

def insertMovie(originalTitle: String, idRawData: String, budget: String, revenue: String, runtime: String, releaseDate: String): ConnectionIO[Int] =
    sql"""
          INSERT INTO MOVIE (ORIGINAL_TITLE, ID_RAW_DATA, BUDGET, REVENUE, RUNTIME, RELEASE_DATE)
          VALUES ($originalTitle, $idRawData, $budget, $revenue, $runtime, $releaseDate)
        """.update.run
// Insertar un nuevo registro
  val newRow = ("Adventure Fantasy Action Science Fiction", "1452", "27000000", "39108192", "154", "2006-06-28")
  val insertResult: Int = insertMovie(newRow._1, newRow._2, newRow._3, newRow._4, newRow._5, newRow._6).transact(xa).unsafeRunSync()

  println(s"Filas insertadas: $insertResult")

Scala y Base de datos relacional

doobie library - todo el código



import doobie._
import doobie.implicits._

import cats._
import cats.effect._

import cats.effect.unsafe.implicits.global



object EjemploBase extends App{
  case class Movie(id: Int, originalTitle: String)

  // conexión
  val xa = Transactor.fromDriverManager[IO](
    driver = "com.mysql.cj.jdbc.Driver", // JDBC driver
    url = "jdbc:mysql://localhost:3306/my_db", // URL de conexión
    user = "root", // Nombre de la base de datos
    password = "integrador", // Password
    logHandler = None // Manejo de la información de Log
  )

  def find(id: Int): ConnectionIO[Option[Movie]] =
    sql"SELECT MOVIE.ID, MOVIE.ORIGINAL_TITLE FROM MOVIE where MOVIE.ID = $id"
      .query[Movie]
      .option

  def listAllMovies(): ConnectionIO[List[Movie]] =
    sql"SELECT MOVIE.ID, MOVIE.ORIGINAL_TITLE FROM MOVIE"
      .query[Movie]
      .to[List]

  def avgRuntime(): ConnectionIO[Double] =
    sql"""
          SELECT avg(m.RUNTIME) as avg_runtime
          FROM MOVIE m
         """.stripMargin
      .query[Double]
      .unique


  // NUEVA FUNCIÓN: Insertar una nueva película
  def insertMovie(originalTitle: String, idRawData: String, budget: String, revenue: String, runtime: String, releaseDate: String): ConnectionIO[Int] =
    sql"""
          INSERT INTO MOVIE (ORIGINAL_TITLE, ID_RAW_DATA, BUDGET, REVENUE, RUNTIME, RELEASE_DATE)
          VALUES ($originalTitle, $idRawData, $budget, $revenue, $runtime, $releaseDate)
        """.update.run

  // Insertar un nuevo registro
  val newRow = ("Adventure Fantasy Action Science Fiction", "1452", "27000000", "39108192", "154", "2006-06-28")
  val insertResult: Int = insertMovie(newRow._1, newRow._2, newRow._3, newRow._4, newRow._5, newRow._6).transact(xa).unsafeRunSync()

  println(s"Filas insertadas: $insertResult")



  // Consulta de un registro
  val result = find(4).transact(xa).unsafeRunSync()
  println(result.get)

  // Consulta de todos los registros de películas
  val movieList: List[Movie] = listAllMovies().transact(xa).unsafeRunSync()
  movieList.foreach(println)


  // Consulta del promedio de runtime
  val runTimeAvg: Double = avgRuntime().transact(xa).unsafeRunSync()

  println(runTimeAvg)
}

Reto: Poblar BD de temperturas

Reto

Archivo CSV (temperaturas.csv)

dia,temperatura_mañana,temperatura_tarde
1,18.5,24.3
2,17.8,25.1
3,19.2,23.8
4,16.5,26.2
5,18.9,24.5
6,17.2,25.8

Reto

Organización de solución

project-root/
├── build.sbt
├── src/
│   ├── main/
│   │   ├── scala/
│   │   │   ├── config/
│   │   │   │   └── Database.scala
│   │   │   ├── models/
│   │   │   │   └── Temperatura.scala
│   │   │   ├── dao/
│   │   │   │   └── TemperaturaDAO.scala
│   │   │   ├── services/
│   │   │   │   ├── Estadistica.scala
│   │   │   │   └── DataCleaner.scala
│   │   │   └── Main.scala
│   │   └── resources/
│   │       ├── application.conf
│   │       ├── logback.xml
│   │       └── data/
│   │           └── temperaturas.csv

Proyecto Integrador

Proyecto Integrador o bimestral

Detalles

La presentación del proyecto debe incluir:

Código cargado en GitHub (se revisará el trabajo progresivo)

  • Análisis exploratorio de datos, columnas numéricas y no numéricas. 
  • Limpieza de datos
  • Solución al manejo de la columna Crew
  • Trabajo poblar Base de datos
    • Opción 1: Usando Scripts SQL generados desde Scala
    • Opción 2: Sentencias INSERT INTO a través de la librería (Scala) 

Proyecto Integrador o bimestral

Detalles

La presentación del proyecto debe incluir

 

Formato Wiki (archivo README.md):

  • Documentar el proceso
  • Descripción de los datos 
  • Lectura del CSV
  • Análisis exploratorio
  • Limpieza
  • Trabajo con base de datos.

 

• Entrega del 30 al 31 de enero de 2025

Proyecto Integrador o bimestral

Recordando criterios de limpieza

# Limpieza de Datos

## Manejo de Datos Faltantes
- Examinar valores nulos en columnas numéricas (budget, revenue, runtime)
- Detectar registros sin release_date
- Evaluar campos JSON incompletos (production_companies, production_countries, spoken_languages)

## Formato de Fechas
- Convertir release_date a YYYY-MM-DD
- Identificar y corregir fechas futuras

## Validación Numérica
- Asegurar valores positivos en budget y revenue
- Verificar rango razonable de runtime
- Estandarizar decimales en vote_average

## Limpieza JSON
- Normalizar formato en campos JSON (production_companies, production_countries, spoken_languages, crew)
- Reemplazar comillas simples por dobles
- Validar estructura de arrays
- Manejar caracteres especiales

## Limpieza de Texto
- Eliminar espacios múltiples en original_title y overview
- Asegurar codificación UTF-8
- Eliminar líneas vacías y saltos innecesarios

## Validaciones de Campos
- vote_count: enteros positivos
- vote_average: rango 0-10
- popularity: valores positivos

## Control de Calidad
- Identificar registros duplicados por ID
- Verificar codificación UTF-8 del archivo

Proyecto Integrador o bimestral

Detalles

Se calificara los siguientes ítems:

  • Implementación de funciones de orden superior (anónimas y nombradas). Analizar el caso donde se lo puede aplicar. 
  • Uso de funciones como: Map, Filter, FlatMap, Count, FoldLeft, etc.
  • Uso de estándares de programación para nombrar valores, funciones, case class, etc.
  • Legibilidad del código, uso de espacios, tabulaciones, comentarios en el código.
  • Implementación de diferentes tipos de consultas SQL usando Scala.
  • Creatividad en el diseño de la solución de las tareas (Análisis exploratorio, SQL desde Scala, Explotación de datos).
  • Organización de la solución en repositorio (modelos, utilidades, data, etc.).
  • Documentación técnica del proceso en repositorio WIKI/README (descripción de datos, lectura de CSV, análisis exploratorio, limpieza, trabajo con base de datos).
  • En el momento de la defensa (presentación):
    • Uso del lenguaje técnico para realizar las explicaciones.
    • Explicación detallada de las implementaciones.

B2S14 Base de datos

By Santiago Quiñones Cuenca

B2S14 Base de datos

Persistencia de datos en base de datos relacionales

  • 163