Proyecto implementado para Bases de Datos 2
Desarrollado por: Asunción Mariana Sic Sor
La arquitectura en este proyecto es la siguiente
Sistema Operativo | Dirección IP | Motor Base de Datos | Descripción |
---|---|---|---|
Arch Linux | 192.168.1.2 | SQL Server | Máquina para la base de datos de IMDB , se trabajón en la máquina HOST |
Xubuntu | 192.168.1.31 | SQL Server | Máquina virtual en Virtual Box para la base de datos de Netflix |
Ubuntu | 3.21.100.69 | Mongo DB | Máquina virtual en AWS (EC2) para la base de datos No Relacional |
Algunas herramientas adicionales que se trabajaron fueron Azure Data Studio para trabajar con SQL Server y MongoDB Compass para visualizar datos de Mongo.
Esta base de datos se desarrolló en un servidor con Sistema Operativo Arch Linux (Manjaro XFCE)
El modelo Entidad-Relacional es el siguiente
Esta base de datos se ha desarollado en SQL Server y los datos se encuentran en este enlace.
Para cargar dichos datos, se crea una tabla temporal por cada archivo .tsv
de la siguiente manera
Para el procedimiento se utiliza el archivo
namebasics.tsv
Primero se crea la tabla temporal para almacenar los datos
CREATE TABLE namebasics(
nconst VARCHAR(115),
primaryname VARCHAR(115),
birthyear VARCHAR(45),
deathyear VARCHAR(45),
primaryProfession VARCHAR(115),
knownforTitles VARCHAR(115)
);
Luego se insertan los datos con la siguiente instrucción
BULK INSERT namebasics
FROM '/namebasics.tsv'
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n'
);
Y los datos se cargaran
Dicho procedimiento se ejecuta para todos los archivos proporcionados
Luego se crean las tablas según el modelo entidad/relación correspondiente.
Por ejemplo, se muestra con la tabla
Category
Se crea la tabla
create table genre(
id int IDENTITY(1,1),
name VARCHAR(75),
PRIMARY KEY (id)
);
Luego con una consulta a la base de datos de la tabla temporal titleprincipals
se inserta a la tabla
INSERT into category
SELECT distinct category from titleprincipals;
Y ya quedan insertados los datos en la base de datos
De esta manera se insertan los datos en la base de datos.
El script de la inserción en la base de datos se encuentra en el archivo IMDB/insert.sql
Para la base de datos de este sistema, se desarrolló en SQL Server en una máquina virtual con Xubuntu como Sistema Operativo
El modelo entidad relación surge a partir del diagrama de IMDB, pero para la base de datos de Netflix se reduce al siguiente diagrama
Su modelo relacional se encuentra acá
Tabla | Descripción |
---|---|
Genre | En esta tabla se almacenan los géneros posibles de algún título |
TitleType | Se almacenan los posibles tipos de un título. Ej. Show, película, serie, etc. |
Title | En esta tabla se almacena la información relevante de un título y en ella también hace referencia con llave foránea hacia los posibles tipos de títulos y géneros que pueda clasificarse un mismo título. El valor de esta llave primaria es exactamente igual a la de IMDB para poder vincular su rating |
Title_Genre | A esta tabla caen todos los géneros posibles en los que pueda clasificarse un mismo título. |
Episode | En dado caso el título sea una serie, acá se encuentra a detalle su único o varios episodios correspondientes a un mismo título |
Person | Esta tabla almacena todas las personas que pueden verse enrrollada en algún título |
Role | Acá se especifican los posibles roles que las personas pueden ejercer. Éstos pueden ser actor, actriz, director o escritor |
Crew | Acá se definen todas las personas y rol que ejercen en un mismo título |
Se eligió dicho modelo basándose en los datos que actualmente muestra el catálogo de Netflix ©
Fuente: Esta imagen es propiedad de Netflix © y en ella se muestra el título de Vis a Vis
La creación de las tablas descritas se encuentra en Netflix/create.sql
Para estar insertando nuevas películas en la base de datos de Netflix, se necesita un procedimiento almacenado y que la película que se vaya insertando cree un vínculo entre la base de datos IMDB con la de Netflix, dado que ambas están en diferente servidor, se crea un Linked Server para conectar entre servidores.
El servicio enlazado se crea con la siguiente instrucción
USE [NETFLIX]
GO
EXEC sp_addlinkedserver
@server = N'192.168.1.2',
@srvproduct = N'SQL Server';
GO
La dirección IP 192.168.1.2
pertenece al servidor que aloja la base de datos de IMDB. Para verificar que se haya enlazado correctamente, se ejecuta la consulta
SELECT name FROM [192.168.1.2].master.sys.databases;
Para rectificar que entre las bases de datos del otro servidor, se encuentre la de IMDB
La inserción de datos simples como lo son las personas, los roles, las categorías y tipos de título se encuentran en el archivo insert.sql
con el servicio enlazado.
Para llenar las tablas title
, episode
, title_genre
y crew
se hace uso de un procedimiento almacenado
Se comienza creando y asignando el título de insert_title
y con parámetros:
Parámetro | Descripción |
---|---|
@titulo | Es de tipo varchar , contiene el nombre del título que se desea almacenar |
@anio | Es de tipo int , contiene el año de inicio, osea el año en el que se inició o estrenó el título a almacenar |
@tipo_titulo | Es de tipo int , contiene el tipo de título que es (ya sea, tvSerie, show, movie, etc) |
@desc | Es de tipo varchar , contiene una breve descripción del título que se va a almacenar |
@flag | Es de tipo varchar , parámetro de salida para imprimir el resultado del procedimiento almacenado |
Se crea el procedimiento y sus parámetros:
CREATE PROCEDURE insert_title
@titulo varchar(600),
@anio int,
@tipo_titulo int,
@desc varchar(5000)
@flag varchar(115) OUTPUT
Luego se hace uso de una variable @id_title
la cual almacena el id del título en dado ya esté registrado en la base de datos de IMDB
DECLARE @id_title VARCHAR(115);
SET @id_title = (
SELECT TOP 1 tl.id FROM [192.168.1.2].IMDB.dbo.title tl
WHERE UPPER(tl.primaryTitle) LIKE UPPER(@titulo) AND tl.startYear = @anio AND tl.titleTypeId = @tipo_titulo
);
Se hace la respectiva verificación si la variable es nula o no (es decir, si el título existe o no). Si no existe, se sale del procedimiento
IF @id_title IS NULL
BEGIN
-- SIGNIFICA QUE EL TITULO NO ESTA EN EL CATALOGO DE IMDB
SET @flag = 'El título ' + @titulo + ' no se ha encontrado.';
RETURN
END
En dado caso ya exista, se inserta en las tablas respectivas que se han mencionado antes.
Además se vuelve a verificar que no exista el título a agregar en la base de datos de Netflix
IF (SELECT primaryTitle FROM title t WHERE t.id = @id_title) IS NULL
BEGIN
-- INSERTAR A LA TABLA TITLE
INSERT INTO title(id, primaryTitle, isAdult, startYear, endYear, runtime, [description], titleTypeId)
SELECT TOP 1 tl.id, tl.primaryTitle, tl.isAdult, tl.startYear, tl.endYear, tl.runtime, @desc, tl.titleTypeId
FROM [192.168.1.2].IMDB.dbo.title tl WHERE tl.id = @id_title;
-- INSERTAR A LA TABLA CREW
INSERT INTO crew(titleId, personId, roleId)
SELECT TOP 3 pr.titleId, pr.nameId, role.id
FROM [192.168.1.2].IMDB.dbo.principal pr INNER JOIN [192.168.1.2].IMDB.dbo.category cg ON pr.categoryId = cg.id
INNER JOIN role ON role.name = cg.name
WHERE pr.titleId = @id_title AND (cg.name = 'actor' OR cg.name = 'actress');
INSERT INTO crew(titleId, personId, roleId)
SELECT TOP 1 dr.titleId, dr.nameId, role.id
FROM [192.168.1.2].IMDB.dbo.director dr, role
WHERE dr.titleId = @id_title AND role.name = 'director';
INSERT INTO crew(titleId, personId, roleId)
SELECT TOP 1 dr.titleId, dr.nameId, role.id
FROM [192.168.1.2].IMDB.dbo.director dr, role
WHERE dr.titleId = @id_title AND role.name = 'writer';
-- INSERT TITLE_GENRE
INSERT INTO title_genre(genreId, titleId) VALUES (@tipo_titulo, @id_title);
-- INSERTAR A LA TABLA EPISODE
IF (SELECT count(*) FROM [192.168.1.2].IMDB.dbo.episode ep WHERE ep.parentId = @id_title) > 0
BEGIN
INSERT INTO episode(titleId, parentId, season, episode)
SELECT ep.titleId, ep.parentId, ep.season, ep.episode
FROM [192.168.1.2].IMDB.dbo.episode ep WHERE ep.parentId = @id_title;
END
SET @flag = 'El título ' + @titulo + ' ha sido agregado.';
RETURN
END
Y si ya ha sido agregado antes, no se agrega nada en la base de datos
ELSE
BEGIN
SET @flag = 'El título ' + @titulo + ' ya ha sido agregado antes.';
RETURN
END
El procedimiento almacenado completo se encuentra en el archivo stored.sql
Luego ya solo se ejecuta con la instrucción
DECLARE @result VARCHAR(115);
EXEC insert_title 'Frozen',2013,9,'Elsa found her power', @flag = @result OUTPUT;
SELECT @result 'Resultado';
En base al modelo entidad relación de Netflix, la colección en Mongo del mismo queda de la siguiente manera:
{
"_id":"tt2294629",
"primaryTitle":"Frozen",
"isAdult":0,
"startYear":2013,
"endYear":0,
"runtime":102,
"description":"Elsa found her power",
"titleType":"movie",
"cast": ["Kristen Bell","Idina Menzel","Josh Gad"],
"director":"Jennifer Lee",
"writer":"Jennifer Lee",
"genre": ["Comedy","Animation","Adventure"],
"rating": {
"averageRating":7,
"numVotes":552120
}
}
Para la inserción de datos en Mongo DB, se ha utilizado como lenguaje de programación Python con ayuda de las siguientes librerías
Librería | Descripción |
---|---|
pymssql | Para conectar la base de datos de Netflix en SQL Server con Python |
pymongo | Para conectar la base de datos de Mongo DB con Python |
daemonize | Para iniciar un demonio y que actualice los datos de SQL Server a Mongo. Este proceso se detalla más adelante |
Primero se recuperan los códigos de los títulos existentes en Netflix
SELECT id FROM title;
Sabiendo que la anterior consulta devuelve correctamente los códigos de los títulos existente se crea el método all_titles
def all_titles():
cursor.execute('SELECT id FROM title;')
titles = []
row = cursor.fetchone()
while row:
titles.append(str(row[0]))
row = cursor.fetchone()
return titles
El cual devuelve en un arreglo todos los códigos. Esto facilita la inserción ya que luego solo se recorre el arreglo para obtener código en código todos los títulos.
Luego se realizan métodos para recuperar en cada tabla de SQL mandando como parámetro el código de cada título
Con la consulta
SELECT t.id, t.primaryTitle, t.isAdult, t.startYear, t.endYear, t.runtime, t.[description], tp.name
FROM title t INNER JOIN titletype tp ON t.titleTypeId = tp.id WHERE t.id = 'tt2294629';
Se obtienen los detalles del títulos y se retornan en un arreglo
def detailed_title(id_title):
cursor.execute("SELECT t.id, t.primaryTitle, t.isAdult, t.startYear, t.endYear, t.runtime, t.[description], tp.name FROM title t INNER JOIN titletype tp ON t.titleTypeId = tp.id WHERE t.id = '" + str(id_title) + "';")
return cursor.fetchone()
Con las consultas
SELECT p.name FROM crew c INNER JOIN person p ON c.personId = p.id INNER JOIN role r ON c.roleId = r.id
WHERE c.titleId = 'tt2294629' AND (r.name = 'actress' OR r.name = 'actor');
SELECT p.name FROM crew c INNER JOIN person p ON c.personId = p.id INNER JOIN role r ON c.roleId = r.id WHERE c.titleId = 'tt2294629' AND r.name = 'director';
SELECT p.name FROM crew c INNER JOIN person p ON c.personId = p.id INNER JOIN role r ON c.roleId = r.id
WHERE c.titleId = 'tt2294629' AND r.name = 'writer';
Se obtiene el campo de cast que es un arreglo y el dato del director y escritor de un título
def cast_table(id_title):
cursor.execute("SELECT p.name FROM crew c INNER JOIN person p ON c.personId = p.id INNER JOIN role r ON c.roleId = r.id WHERE c.titleId = '" + str(id_title) + "' AND (r.name = 'actress' OR r.name = 'actor');")
cast = []
row = cursor.fetchone()
while row:
cast.append(str(row[0]))
row = cursor.fetchone()
return cast
Y para el director y escritor, un método que además de recibir el código del título como parámetro recibe si se desea obtener el director o escritor
def get_creator(id_title, role):
cursor.execute("SELECT p.name FROM crew c INNER JOIN person p ON c.personId = p.id INNER JOIN role r ON c.roleId = r.id WHERE c.titleId = '" + id_title + "' AND r.name = '" + role + "';")
return cursor.fetchone()[0]
Con la consulta
SELECT g.name FROM title_genre tg INNER JOIN genre g ON tg.genreId = g.id WHERE tg.titleId = 'tt2294629';
Se crea método para regresar los géneros en un sólo arreglo
def get_genres(id_title):
cursor.execute("SELECT g.name FROM title_genre tg INNER JOIN genre g ON tg.genreId = g.id WHERE tg.titleId = '" + str(id_title) + "';")
genres = []
row = cursor.fetchone()
while row:
genres.append(str(row[0]))
row = cursor.fetchone()
return genres
Esta tabla no pertenece a la base de datos de Netflix, por lo que se debe acceder directamente a la de IMDB con el servicio enlazado creado anteriormente con la siguiente consulta
SELECT r.averageRating, r.numVotes FROM [192.168.1.2].IMDB.dbo.rating r WHERE r.titleId = 'tt2294629';
La anterior consulta devuelve solo dos campos por lo que se realiza un método que devuelva éstos dos métodos en un arreglo casteados a su propio tipo de dato.
def get_rating(id_title):
cursor.execute("SELECT r.averageRating, r.numVotes FROM [192.168.1.2].IMDB.dbo.rating r WHERE r.titleId = '" + id_title + "';")
row = cursor.fetchone()
return [float(row[0]), int(row[1])]
Primero se conecta al cliente de mongo que es una máquina virtual de Ubuntu en la nube cuya dirección IP es 3.21.100.69
y el puerto para Mongo es 27017
# Conectar a mongo
client = MongoClient(port=27017, host="3.21.100.69")
# Seleccionar base de datos y coleccion
db = client['Netflix']
collection = db.titles
Luego se crea un método insert_data
que llama a los métodos descritos anteriormente que recuperan datos de la base de datos relacional para formar el objeto JSON
def insert_data():
for single_title in main.all_titles():
# Obtener lista de codigos actuales en la base de datos de Netflix
details = main.detailed_title(single_title)
# Crear el objeto JSON
title = {
"_id": details[0],
"primaryTitle": details[1],
"isAdult": details[2],
"startYear": details[3],
"endYear": details[4],
"runtime": details[5],
"description": details[6],
"titleType": details[7],
"cast": main.cast_table(single_title),
"director": main.get_creator(single_title, 'director'),
"writer": main.get_creator(single_title, 'writer'),
"genre": main.get_genres(single_title),
"rating": {
"averageRating": main.get_rating(single_title)[0],
"numVotes": main.get_rating(single_title)[1]
}
}
# Insertar objeto a Mongo
collection.insert_one(title)
De esta manera, los títulos quedan almacenados en la base de datos de Mongo
Para crear el demonio, se utiliza la librería detallada anteriormente.
Primero se especifica la ruta del archivo PID
from daemonize import Daemonize
pid = "/tmp/collect-mongo.pid"
Luego se crea el método que el demonio va a ejecutar
def first():
while True:
collection.drop()
insert_data()
sleep(71)
El método se va a ejecutar indeterminadas veces y primero elimina los datos existentes en mongo, luego vuelve a insertar todos los registros y hace una pausa de 71 segundos.
Finalmente, se hace instancia de la clase Daemonize
que recibe como parámetro el nombre de la aplicación, la ruta del archivo PID y el método de la acción que va a realizar el demonio
daemon = Daemonize(app="run_netflix", pid=pid, action=first())
daemon.start()
Para ver los datos, se puede conectar el cliente de Mongo a MongoDB Compass para visualizar