Universidad Técnica Particular de Loja
Materia:
Practicum 1.1
Autor:
Iam Karin Estrella Zhamungui
Carlos David Piedra
Tema:
INFORME FINAL
Docente:
Omar Alexander Ruiz Vivanco
Paralelo:
“B”
1. Estadística del dataset original
PartidosYGoles
Cantidad de Columnas: 43
Cantidad de Filas: 3723
Cantidad de Datos Completos: 3637
Cantidad de Datos Incompletos: 86
Cantidad de Torneos: 30
Cantidad de Regiones En las Que se Jugó: 11
Cantidad de Equipos: 84
Partidos Jugados: 1248
Máxima Capacidad de un Estadio: 200000
Mínima Capacidad de un Estadio: 5000
Fecha más Antiguo: 1930
Fecha más Reciente: 2022
Cantidad De Países Que Han Ganado El Torneo: 12
Cantidad De Estadios En Los Que Se Jugo: 235
Cantidad de Jugadores Que Han Metido Goles: 1832
Cantidad De Goles Por Penal: 292
AlineacionesXTorneo
Cantidad de Columnas: 14
Cantidad de Filas: 13843
Cantidad Jugadores: 10401
Cantidad Torneos: 30
Cantidad De Equipos: 88
Cantidad Posiciones: 4
2. Proceso de limpieza de datos (Normalización)
Para normalizar estos datos, se siguió el siguiente proceso:
Primero, se seleccionaron los identificadores de las columnas de los archivos.
Posteriormente, se identificaron las columnas clave que serían utilizadas para relacionar los
datos, ya que estas nos servirán para relacionar las tablas entre sí.
Segundo, se procedió con la creación de tablas separadas. Se crearon tablas
individuales para cada identificador con sus posibles atributos.
Proceso al normalizar: Primero se observó un identificador de torneos con el cual se
creó una tabla específica para los torneos. Se vio que el identificador de los partidos está
vinculado al identificador de los torneos, adicionalmente se observaron dos identificadores
en la tabla de partidos, indicando la existencia de dos equipos: locales y visitantes. Se
aplicó un proceso indicado para los identificadores "team_id" y "stadium_id". Todos estos
identificadores están relacionados con la tabla de partidos, lo que indica la relación entre los
partidos y los equipos, así como el estadio donde se realizaron los partidos.
Para la tabla de goles, se siguió un proceso similar al anterior del mismo se vio unas
posibles relaciones con tablas de partidos, torneos, jugadores y equipos. Esto indica que un
gol se realiza por un jugador que está en un equipo en un torneo específico, en un partido
determinado y en un estadio concreto.
Del segundo archivo "AlineacionesXTorneo", se dedujo que para cada alineación
debía existir una clave compuesta. Esta clave compuesta incluye los identificadores del
torneo, el jugador y el equipo. Así, se crearon tablas para las alineaciones, siguiendo el
mismo proceso ya indicado.
3. Esquema Base de Datos
En los documentos, se encontraron varios identificadores (ID) que representan
distintas tablas. En el primer archivo CSV, algunos de los ID del archivo PartidosYGoles
aparecieron también en el segundo archivo CSV AlineacionesXTorneo, lo cual sugiere una
relación entre las tablas.
3.1. Análisis de Datos
En el primer archivo, PartidosYGoles, se generaron cinco tablas: Partidos, Equipos,
Torneos, Estadios y Goles. En este proceso, se separaron los identificadores de cada
elemento y se les asignaron sus atributos correspondientes.
En el segundo archivo, AlineacionesXTorneo, se crearon dos tablas: Alineaciones y
Jugadores. El procedimiento fue similar al del primer archivo.
Tabla Partidos
matches_tournament_id: ID del Torneo - String,
matches_match_id: ID del Partido - String,
matches_away_team_id: ID del Equipo Visitante - String,
matches_home_team_id: ID del Equipo Local - String,
matches_stadium_id: ID del Estadio - String,
matches_match_date: ID de la Fecha del Partido - DATE,
matches_match_time: Tiempo del Partido - String,
matches_stage_name: Fase del Torneo - String,
matches_home_team_score: Goles Local - Int,
matches_away_team_score: Goles Visitante - Int,
matches_extra_time: Partidos Con Tiempo Extra - Int,
matches_penalty_shootout: Partidos Terminados en Penales - Int,
matches_home_team_score_penalties: Goles Por Penal Local -Int,
matches_away_team_score_penalties: Goles Por Penal Visitante - Int,
matches_result: Resultado Partido - String,
Tabla Equipos
team_name: Nombre Equipo - String,
team_region: Nombre Region del Equipo - String,
men_team: Equipo Masculino - Int,
women_team: Equipo Femenino – Int
Tabla Torneos
matches_tournament_id: ID del Torneo - String,
tournaments_tournament_name: Nombre del Torneo -String,
tournaments_year: Año del Torneo - DATE,
tournaments_host_country: Pais del Anfitrión del Torneo - String,
tournaments_winner: Ganador Del Torneo - String,
tournaments_count_teams: Cantidad De Equipos del Torneo - Int,
Tabla Estadios
stadiums_stadium_name: Nombre Estadio - String,
stadiums_city_name: Nombre Ciudad - String,
stadiums_country_name: Nombre Estadio - String,
stadiums_stadium_capacity: Capacidad Estadio - Int,
home_team_name: Equipo Local - String,
home_mens_team: Equipo Local Hombres - Int,
home_womens_team: Equipo Local Mujeres - Int ,
home_region_name: Nombre Region del Local - String,
away_team_name: Nombre Pais Visitante - Int,
away_mens_team : Equipo Visitante Hombres - Int,
away_womens_team: Equipo Visitante Mujeres - Int,
away_region_name: Region Equipo Visitante,
Tabla Goles
goals_goal_id: ID Gol - String,
matches_tournament_id: ID del Torneo - String,
matches_match_id: ID del Partido - String,
goals_team_id: ID Equipo - String,
goals_player_id: ID Jugador - String,
goals_player_team_id: ID Equipo del Jugador - String,
goals_minute_label: Minuto Gol - Int,
goals_minute_regulation: Minuto Gol - Int,
goals_minute_stoppage: Goles Tiro Libre - Int,
goals_match_period: Periodo del Partido - String,
goals_own_goal: Goles en Propia - Int,
goals_penalty: Goles Penal -Int
Tabla Jugadores
squads_player_id: ID Del Jugador - String,
players_family_name: Apellido Jugador - String,
players_given_name: Nombre Jugador - String,
players_birth_date: Año Nacimiento Jugador - Date,
players_midfielder: Jugador Mediocampista - Int,
players_forward: Jugador Delantero - Int,
players_female: Jugador Femenino - Int,
players_goal_keeper: Jugador Arquero - Int,
players_defender: Jugador Defensa - Int
Tabla Squads
squads_team_id: ID Equipo de la Alineación - String,
squads_tournament_id: ID Alineación – String,
squads_player_id: ID Del Jugador - String,
squads_position_name: Posición del Jugador - String,
squads_shirt_number: Numero Camiseta - Int,
3.2. Modelo Conceptual
4. Modelo Relacional
4.1. Diseño Lógico
AlineacionesXTorneo
squads_player_id -> (players_family_name, players_given_name, players_birth_date,
players_female, players_goal_keeper, players_defender, players_midfielder,
players_forward) (Tabla Players)
squads_team_id, squads_tournament_id, squads_player_id -> (squads_position_name,
squads_shirt_number) (Tabla Alignments)
PartidosYGoles
stadiums_stadium_id -> stadiums_stadium_name, stadiums_city_name,
stadiums_country_name, stadiums_stadium_capacity (Tabla Stadiums)
team_id -> team_country, team_region, men_team, women_team
tournaments_tournament_id -> tournaments_tournament_name, tournaments_year,
tournaments_host_country, tournaments_winner, tournaments_count_teams (Tabla
Tournament)
goals_goal_id -> goals_team_id, goals_player_id, matches_tournament_id,
matches_match_id, goals_minute_label, goals_minute_regulation, goals_minute_stoppage,
goals_match_period, goals_own_goal, goals_penalty (Tabla Goals)
matches_match_id -> matches_tournament_id, matches_away_team_id,
matches_home_team_id, matches_stadium_id, matches_match_date,
matches_match_time, matches_stage_name, matches_home_team_score,
matches_away_team_score, matches_extra_time, matches_penalty_shootout,
matches_home_team_score_penalties, matches_away_team_score_penalties,
matches_result (Tabla Matches)
5. Interfaz del Aplicativo
Panel Principal: En donde se encuentran botones mediante los cuales se muestran
las tablas de la base de datos en el programa
Ventana Estadísticas: se muestran estadísticas de la tabla seleccionada al dar click
al botón más
Panel Búsqueda Tabla: La búsqueda va según en id de la tabla seleccionada en los
botones de la izquierda, muestra las tablas relacionadas con esa id.
6. Modelo Físico, Implementación y carga de la base de datos
Script Para crear la base de datos con las tablas:
CREATE DATABASE practicumbdft;
USE practicumbdfr;
DROP TABLE IF EXISTS goals;
DROP TABLE IF EXISTS matches;
DROP TABLE IF EXISTS stadiums;
DROP TABLE IF EXISTS tournaments;
DROP TABLE IF EXISTS players;
DROP TABLE IF EXISTS teams;
DROP TABLE IF EXISTS alignments;
CREATE TABLE teams (
team_id VARCHAR(25) NOT NULL PRIMARY KEY,
team_name VARCHAR(255),
team_region_name VARCHAR(255),
men_team TINYINT(1),
women_team TINYINT(1)
);
CREATE TABLE tournaments (
matches_tournament_id VARCHAR(25) NOT NULL PRIMARY KEY,
tournaments_tournament_name VARCHAR(255),
tournaments_year INT,
tournaments_host_country VARCHAR(255),
tournaments_winner VARCHAR(30),
tournaments_count_teams INT
);
CREATE TABLE stadiums (
matches_stadium_id VARCHAR(25) NOT NULL PRIMARY KEY,
stadiums_stadium_name VARCHAR(255),
stadiums_city_name VARCHAR(255),
stadiums_country_name VARCHAR(255),
stadiums_stadium_capacity INT
);
CREATE TABLE matches (
matches_match_id VARCHAR(25) NOT NULL PRIMARY KEY,
matches_tournament_id VARCHAR(25),
matches_away_team_id VARCHAR(40),
matches_home_team_id VARCHAR(40),
matches_stadium_id VARCHAR(40),
matches_match_date VARCHAR(25),
matches_match_time VARCHAR(25),
matches_stage_name VARCHAR(255),
matches_home_team_score INT,
matches_away_team_score INT,
matches_extra_time VARCHAR(25),
matches_penalty_shootout VARCHAR(25),
matches_home_team_score_penalties INT,
matches_away_team_score_penalties INT,
matches_result VARCHAR(25),
FOREIGN KEY (matches_tournament_id) REFERENCES tournaments (matches_tournament_id),
FOREIGN KEY (matches_away_team_id) REFERENCES teams (team_id),
FOREIGN KEY (matches_home_team_id) REFERENCES teams (team_id),
FOREIGN KEY (matches_stadium_id) REFERENCES stadiums (matches_stadium_id)
);
CREATE TABLE players (
player_id VARCHAR(25) NOT NULL PRIMARY KEY,
players_family_name VARCHAR(255),
players_given_name VARCHAR(255),
players_birth_date VARCHAR(255),
players_female TINYINT(1),
players_goal_keeper TINYINT(1),
players_defender TINYINT(1),
players_midfielder TINYINT(1),
players_forward TINYINT(1)
);
CREATE TABLE alignments (
squads_team_id VARCHAR(25) NOT NULL,
squads_tournament_id VARCHAR(25) NOT NULL,
squads_player_id VARCHAR(25) NOT NULL,
squads_position_name VARCHAR(255),
squads_shirt_number INT,
PRIMARY KEY (squads_team_id, squads_tournament_id, squads_player_id),
FOREIGN KEY (squads_player_id) REFERENCES players (player_id),
FOREIGN KEY (squads_tournament_id) REFERENCES tournaments (matches_tournament_id),
FOREIGN KEY (squads_team_id) REFERENCES teams (team_id)
);
CREATE TABLE goals (
goals_goal_id VARCHAR(25) NOT NULL PRIMARY KEY,
goals_team_id VARCHAR(25),
goals_player_id VARCHAR(25),
matches_tournament_id VARCHAR(25),
matches_match_id VARCHAR(25),
goals_minute_label VARCHAR(255),
goals_minute_regulation INT,
goals_minute_stoppage INT,
goals_match_period VARCHAR(255),
goals_own_goal TINYINT(1),
goals_penalty TINYINT(1),
FOREIGN KEY (goals_team_id) REFERENCES teams (team_id),
FOREIGN KEY (goals_player_id) REFERENCES players (player_id),
FOREIGN KEY (matches_tournament_id) REFERENCES tournaments (matches_tournament_id),
FOREIGN KEY (matches_match_id) REFERENCES matches (matches_match_id)
);
7. Codigos Conexion y Consultas
object ImportadorDatos {
val xa = Transactor.fromDriverManager[IO](
driver = "com.mysql.cj.jdbc.Driver", //JDBC DRIVER
url = "jdbc:mysql://localhost:3306/practicumbdfr", //URL CONEXION
user = "root",
password = "ExLolIKE.",
logHandler = None
)
def obtenerJugadores(): Array[(String, String, String, String, String,
String, String, String, String)] =
sql"SELECT * FROM players"
.query[(String, String, String, String, String, String, String,
String, String)]
.to[Array]
.transact(xa)
.unsafeRunSync()
def obtenerPartidos(): Array[(String, String, String, String, String,
String, String, String, String, String, String, String, String, String,
String)] =
sql"SELECT * FROM matches"
.query[(String, String, String, String, String, String, String,
String, String, String, String, String, String, String, String)]
.to[Array]
.transact(xa)
.unsafeRunSync()
def obtenerGoles(): Array[(String, String, String, String, String,
String, String, String, String, String, String)] =
sql"SELECT * FROM goals"
.query[(String, String, String, String, String, String, String,
String, String, String, String)]
.to[Array]
.transact(xa)
.unsafeRunSync()
def obtenerEstadios(): Array[(String, String, String, String, String)] =
sql"SELECT * FROM stadiums"
.query[(String, String, String, String, String)]
.to[Array]
.transact(xa)
.unsafeRunSync()
def obtenerEquipos(): Array[(String, String, String, String, String)] =
sql"SELECT * FROM teams"
.query[(String, String, String, String, String)]
.to[Array]
.transact(xa)
.unsafeRunSync()
def obtenerTorneos(): Array[(String, String, String, String, String,
String)] =
sql"SELECT * FROM tournaments"
.query[(String, String, String, String, String, String)]
.to[Array]
.transact(xa)
.unsafeRunSync()
def obtenerAlineaciones(): Array[(String, String, String, String,
String)] =
sql"SELECT * FROM alignments"
.query[(String, String, String, String, String)]
.to[Array]
.transact(xa)
.unsafeRunSync()
def statsJugadores(): Array[(String, String, String, String, String,
String, String)] =
sql"""SELECT COUNT(player_id),
SUM(players_female ),
COUNT(player_id) -SUM(players_female),
SUM(players_goal_keeper),
SUM(players_defender),
SUM(players_midfielder),
SUM(players_forward)
FROM players;
"""
.query[(String, String, String, String, String, String, String)]
.to[Array]
.transact(xa)
.unsafeRunSync()
def statsGoles(): Array[(String, String, String, String, String, String)]
=
sql"""SELECT
COUNT(DISTINCT goals_goal_id) AS distinct_goals,
COUNT(DISTINCT goals_player_id) AS distinct_players,
COUNT(DISTINCT matches_tournament_id) AS distinct_tournaments,
COUNT(DISTINCT matches_match_id) AS distinct_matches,
SUM(goals_own_goal) AS total_own_goals,
SUM(goals_penalty) AS total_penalties
FROM goals;
""".query[(String, String, String, String, String, String)]
.to[Array]
.transact(xa)
.unsafeRunSync()
def statsPartidos(): Array[(String, String, String, String, String,
String, String, String, String)] =
sql"""SELECT
COUNT(*) AS total_matches,
COUNT(DISTINCT matches_away_team_id) AS distinct_away_teams,
COUNT(DISTINCT matches_home_team_id) AS distinct_home_teams,
COUNT(DISTINCT matches_stadium_id) AS distinct_stadiums,
SUM(matches_penalty_shootout) AS total_penalty_shootouts,
SUM(matches_home_team_score_penalties) AS total_home_team_penalties,
SUM(matches_away_team_score_penalties) AS total_away_team_penalties,
SUM(matches_home_team_score + matches_away_team_score) AS
total_goals,
SUM(matches_home_team_score_penalties +
matches_away_team_score_penalties) AS total_penalties
FROM matches;
""".query[(String, String, String, String, String, String, String,
String, String)]
.to[Array]
.transact(xa)
.unsafeRunSync()
def statsEstadios(): Array[(String, String, String, String)] =
sql"""SELECT COUNT(matches_stadium_id),
AVG(stadiums_stadium_capacity),
MAX(stadiums_stadium_capacity),
MIN(stadiums_stadium_capacity)
FROM stadiums;
""".query[(String, String, String, String)]
.to[Array]
.transact(xa)
.unsafeRunSync()
def statsEquipos(): Array[(String, String, String, String, String)] =
sql"""SELECT COUNT(team_id),
COUNT(DISTINCT team_region_name),
SUM(men_team),
SUM(women_team),
SUM(men_team + women_team)
FROM teams;
""".query[(String, String, String, String, String)]
.to[Array]
.transact(xa)
.unsafeRunSync()
def statsTorneos(): Array[(String, String, String, String, String)] =
sql"""SELECT COUNT(matches_tournament_id),
COUNT(DISTINCT tournaments_tournament_name),
MAX(tournaments_year),
MIN(tournaments_year),
AVG(tournaments_count_teams)
FROM tournaments;
""".query[(String, String, String, String, String)]
.to[Array]
.transact(xa)
.unsafeRunSync()
def stattsPlayerAlignments(): Array[(String, String, Int)] =
sql"""SELECT squads_tournament_id, squads_team_id, COUNT(*) AS
tournament_team_players_count
FROM alignments
GROUP BY squads_tournament_id, squads_team_id
ORDER BY 1,2;""".query[(String, String, Int)]
.to[Array]
.transact(xa)
.unsafeRunSync()
def stattsStadiumsXCountry(): List[(String, Double)] =
sql"""SELECT stadiums_country_name, COUNT(*)
FROM stadiums
GROUP BY 1
ORDER BY 1;""".query[(String, Double)]
.to[List]
.transact(xa)
.unsafeRunSync()
def stattsTeamsGenere(): List[(String, Double)] =
sql"""SELECT "Men Teams", SUM(men_team)
FROM teams
UNION
SELECT "Women Teams", SUM(women_team)
FROM teams;""".query[(String, Double)]
.to[List]
.transact(xa)
.unsafeRunSync()
def stattsWinnersTournaments(): List[(String, Double)] =
sql"""SELECT tournaments_winner, COUNT(tournaments_winner)
FROM tournaments
GROUP BY 1;""".query[(String, Double)]
.to[List]
.transact(xa)
.unsafeRunSync()
def stattsGenreWandM(): List[(String, Double)] =
sql"""SELECT "Mujeres", SUM(players_female)
|FROM players
|UNION
|SELECT "Hombres", COUNT(players_female) - SUM(players_female)
|FROM players;""".stripMargin.query[(String, Double)]
.to[List]
.transact(xa)
.unsafeRunSync()
}
8. Generación Script para carga de datos en un motor de BD especifico:
Para poblar la base de datos con los archivos csv se pueden usar dos formas,
directamente en Mysql o en código en lenguaje scala, independientemente del método
utilizado, los datos se limpian.
La primer forma de cargar datos en la base de datos y la implementada en el
proyecto es la siguiente:
import cats.*
import cats.effect.*
import cats.effect.unsafe.implicits.global
import com.github.tototoshi.csv.*
import doobie.*
import doobie.implicits.*
import java.io.File
object InsertarDT {
@main
def exportarDatos2BD(): Unit =
val path2DataFile1 = "C:\\dsPartidosYGoles.csv"
val reader1 = CSVReader.open(new File(path2DataFile1))
val contentFilePyG: List[Map[String, String]] =
reader1.allWithHeaders()
reader1.close()
val path2DataFile2 = "C:\\dsAlineacionesXTorneo-2.csv"
val reader2 = CSVReader.open(new File(path2DataFile2))
val contentFileAxT: List[Map[String, String]] =
reader2.allWithHeaders()
reader2.close()
val xa = Transactor.fromDriverManager[IO](
driver = "com.mysql.cj.jdbc.Driver", //JDBC DRIVER
url = "jdbc:mysql://localhost:3306/practicumbdfr", //URL CONEXION
user = "root",
password = "ExLolIKE.",
logHandler = None
)
generateDataPlayers(contentFileAxT)
.foreach(insert => insert.run.transact(xa).unsafeRunSync())
generateDataTeams(contentFilePyG)
.foreach(insert => insert.run.transact(xa).unsafeRunSync())
generateDataTournaments(contentFilePyG)
.foreach(insert => insert.run.transact(xa).unsafeRunSync())
generateDataStadiums(contentFilePyG)
.foreach(insert => insert.run.transact(xa).unsafeRunSync())
generateDataAlignments(contentFileAxT)
.foreach(insert => insert.run.transact(xa).unsafeRunSync())
generateDataMatches(contentFilePyG)
.foreach(insert => insert.run.transact(xa).unsafeRunSync())
generateDataGoals(contentFilePyG)
.foreach(insert => insert.run.transact(xa).unsafeRunSync())
generateScriptAlignments(contentFileAxT)
generateScriptTournaments(contentFilePyG)
def generateDataPlayers(data: List[Map[String, String]]):
List[doobie.Update0] =
val playerTuple = data
.map(
row => (row("players_birth_date"),
row("players_defender"),
row("players_family_name"),
row("players_female"),
row("players_forward"),
row("players_given_name"),
row("players_goal_keeper"),
row("players_midfielder"),
row("squads_player_id"))
)
.distinct
.map(t7 =>
sql""" INSERT INTO players(players_birth_date, players_defender,
players_family_name, players_female,
players_forward, players_given_name, players_goal_keeper,
players_midfielder, player_id)
VALUES(${t7._1}, ${t7._2}, ${t7._3}, ${t7._4}, ${t7._5},
${t7._6} ,${t7._7}, ${t7._8},${t7._9})
""".update)
playerTuple
def generateDataMatches(data: List[Map[String, String]]):
List[doobie.Update0] =
val matchTuple = data
.map(
row => (row("matches_match_id"),
row("matches_tournament_id"),
row("matches_away_team_id"),
row("matches_home_team_id"),
row("matches_stadium_id"),
row("matches_match_date"),
row("matches_match_time"),
row("matches_stage_name"),
row("matches_home_team_score"),
row("matches_away_team_score"),
row("matches_extra_time"),
row("matches_penalty_shootout"),
row("matches_home_team_score_penalties"),
row("matches_away_team_score_penalties"),
row("matches_result"))
)
.distinct
.map(t7 =>
sql""" INSERT INTO matches(matches_match_id, matches_tournament_id,
matches_away_team_id, matches_home_team_id, matches_stadium_id,
matches_match_date, matches_match_time, matches_stage_name,
matches_home_team_score, matches_away_team_score,
matches_extra_time, matches_penalty_shootout,
matches_home_team_score_penalties, matches_away_team_score_penalties,
matches_result)
VALUES(${t7._1}, ${t7._2}, ${t7._3}, ${t7._4}, ${t7._5},
${t7._6} ,${t7._7}, ${t7._8},${t7._9},${t7._10},
${t7._11},${t7._12},${t7._13},${t7._14},${t7._15})
""".update)
matchTuple
def generateDataGoals(data: List[Map[String, String]]):
List[doobie.Update0] =
val goalTuple = data
.map(
row => (row("goals_goal_id").trim,
row("matches_match_id"),
row("goals_team_id"),
row("goals_player_id"),
row("matches_tournament_id"),
row("goals_minute_label"),
row("goals_minute_regulation"),
row("goals_minute_stoppage"),
row("goals_match_period"),
row("goals_own_goal"),
row("goals_penalty"))
)
.filterNot(_._7 == "NA")
.distinct
.map(t7 =>
sql""" INSERT INTO goals(goals_goal_id, matches_match_id,
goals_team_id, goals_player_id, matches_tournament_id,
goals_minute_label, goals_minute_regulation,
goals_minute_stoppage, goals_match_period, goals_own_goal,
goals_penalty)
VALUES(${t7._1}, ${t7._2}, ${t7._3}, ${t7._4}, ${t7._5},
${t7._6} ,${t7._7}, ${t7._8},${t7._9},${t7._10},
${t7._11})
""".update)
goalTuple
def generateDataAlignments(data: List[Map[String, String]]):
List[doobie.Update0] =
val alignmentTuple = data
.map(
row => (row("squads_player_id").trim,
row("squads_position_name"),
row("squads_shirt_number"),
row("squads_team_id"),
row("squads_tournament_id"))
)
.distinct
.map(t7 =>
sql""" INSERT INTO alignments(squads_player_id,
squads_position_name, squads_shirt_number, squads_team_id,
squads_tournament_id)
VALUES(${t7._1}, ${t7._2}, ${t7._3}, ${t7._4}, ${t7._5})
""".update)
alignmentTuple
def generateDataStadiums(data: List[Map[String, String]]):
List[doobie.Update0] =
val stadiumTuple = data
.map(
row => (row("matches_stadium_id").trim,
row("stadiums_city_name"),
row("stadiums_country_name"),
row("stadiums_stadium_capacity"),
row("stadiums_stadium_name"))
)
.distinct
.map(t7 =>
sql""" INSERT INTO stadiums(matches_stadium_id, stadiums_city_name,
stadiums_country_name,
stadiums_stadium_capacity, stadiums_stadium_name)
VALUES(${t7._1}, ${t7._2}, ${t7._3}, ${t7._4}, ${t7._5})
""".update)
stadiumTuple
def generateDataTeams(data: List[Map[String, String]]):
List[doobie.Update0] =
val teamTuple = data
.map(
row => (row("matches_away_team_id"),
row("away_team_name"),
row("away_region_name"),
row("away_mens_team"),
row("away_womens_team"))
)
.distinct
.map(t7 =>
sql""" INSERT INTO teams(team_id, team_name, team_region_name,
men_team, women_team)
VALUES(${t7._1}, ${t7._2}, ${t7._3}, ${t7._4}, ${t7._5})
""".update)
teamTuple
def generateDataTournaments(data: List[Map[String, String]]) =
val tournamentsTuple = data
.map(
row => (row("matches_tournament_id"),
row("tournaments_tournament_name"),
row("tournaments_year"),
row("tournaments_host_country"),
row("tournaments_winner"),
row("tournaments_count_teams"))
)
.distinct
.map(t7 =>
sql""" INSERT INTO tournaments(matches_tournament_id,
tournaments_tournament_name, tournaments_year,
tournaments_host_country, tournaments_winner,
tournaments_count_teams)
VALUES(${t7._1}, ${t7._2}, ${t7._3}, ${t7._4},
${t7._5},${t7._6})
""".update)
tournamentsTuple
def generateScriptAlignments(data: List[Map[String, String]]) =
val sqlInsert = s"INSERT INTO alignments(squads_player_id,
squads_position_name, squads_shirt_number, squads_team_id,
squads_tournament_id)" +
s" VALUES ('%s', '%s', %d, '%s', '%s');"
val alignmentTuple = data
.map(
row => (row("squads_player_id"),
row("squads_position_name"),
row("squads_shirt_number").toInt,
row("squads_team_id"),
row("squads_tournament_id"))
)
.distinct
.map(t => sqlInsert.formatLocal(java.util.Locale.US, t._1, t._2,
t._3, t._4, t._5))
alignmentTuple.foreach(println)
def generateScriptTournaments(data: List[Map[String, String]]) =
val sqlInsert = s" INSERT INTO tournaments(matches_tournament_id,
tournaments_tournament_name, " +
s"tournaments_year, tournaments_host_country, tournaments_winner,
tournaments_count_teams) " +
s"VALUES('%s', '%s', '%s', '%s', '%s', %d);"
val alignmentTuple = data
.map(
row => (row("matches_tournament_id"),
row("tournaments_tournament_name"),
row("tournaments_year"),
row("tournaments_host_country"),
row("tournaments_winner"),
row("tournaments_count_teams").toInt)
)
.distinct
.map(t => sqlInsert.formatLocal(java.util.Locale.US, t._1, t._2,
t._3, t._4, t._5, t._6))
alignmentTuple.foreach(println)
}
La segunda alternativa para cargar datos a las tablas con el siguiente script en
mysql, el cual los archivos csv tendrán que estar importados en la base de datos:
INSERT INTO teams (team_id, team_name, team_region_name, men_team,
women_team)
SELECT DISTINCT
matches_away_team_id,
away_team_name,
away_region_name,
away_mens_team,
away_womens_team
FROM dspartidosygoles
ORDER BY 1, 2;
INSERT INTO players(player_id, players_family_name, players_given_name,
players_birth_date, players_female, players_goal_keeper,
players_defender, players_midfielder, players_forward)
SELECT DISTINCT
squads_player_id,
players_family_name,
players_given_name,
players_birth_date,
players_female,
players_goal_keeper,
players_defender,
players_midfielder,
players_forward
FROM dsalineacionesxtorneo;
INSERT INTO stadiums(matches_stadium_id, stadiums_stadium_name,
stadiums_city_name, stadiums_country_name, stadiums_stadium_capacity)
SELECT DISTINCT
matches_stadium_id,
stadiums_stadium_name,
stadiums_city_name,
stadiums_country_name,
stadiums_stadium_capacity
FROM dspartidosygoles;
INSERT INTO tournaments(matches_tournament_id, tournaments_tournament_name,
tournaments_year, tournaments_host_country, tournaments_winner,
tournaments_count_teams)
SELECT DISTINCT
matches_tournament_id,
tournaments_tournament_name,
tournaments_year,
tournaments_host_country,
tournaments_winner,
tournaments_count_teams
FROM dspartidosygoles;
INSERT INTO matches(matches_match_id, matches_tournament_id,
matches_away_team_id, matches_home_team_id, matches_stadium_id,
matches_match_date, matches_match_time,
matches_stage_name, matches_home_team_score, matches_away_team_score,
matches_extra_time, matches_penalty_shootout,
matches_home_team_score_penalties, matches_away_team_score_penalties,
matches_result)
SELECT DISTINCT
matches_match_id,
matches_tournament_id,
matches_away_team_id,
matches_home_team_id,
matches_stadium_id,
matches_match_date,
matches_match_time,
matches_stage_name,
matches_home_team_score,
matches_away_team_score,
matches_extra_time,
matches_penalty_shootout,
matches_home_team_score_penalties,
matches_away_team_score_penalties,
matches_result
FROM dspartidosygoles;
INSERT INTO goals(goals_goal_id, goals_team_id, goals_player_id,
matches_tournament_id, matches_match_id, goals_minute_label,
goals_minute_regulation, goals_minute_stoppage,
goals_match_period, goals_own_goal, goals_penalty)
SELECT DISTINCT
goals_goal_id,
goals_team_id,
goals_player_id,
matches_tournament_id,
matches_match_id,
goals_minute_label,
goals_minute_regulation,
goals_minute_stoppage,
goals_match_period,
goals_own_goal,
goals_penalty
FROM dspartidosygoles
WHERE goals_minute_regulation IS NOT NULL
ORDER BY 1;
INSERT INTO alignments (squads_player_id, squads_tournament_id,
squads_team_id, squads_position_name, squads_shirt_number)
SELECT DISTINCT
squads_player_id,
squads_tournament_id,
squads_team_id,
squads_position_name,
squads_shirt_number
FROM dsalineacionesxtorneo;
