- Victor Hugo Castillejos Corzo 182344
- Diego Arellano Zamudio 198002
- Guillermo Arredondo Renero 197256
NOTA IMPORTANTE: Los archivos contenidos dentro del contenedor del git son los scripts que utilizamos a lo largo del desarrollo del proyecto; sin embargo, el proyecto en un over-all se puede replicar a través de lo contenido en este README.org
Para este proyecto se utilizó la API de Star Wars gratuita llamada SWAPI, en la cual se almacena la información de personajes, planetas, naves, vehiculos, peliculas, especies de las primeras 7 películas, sí… involucra también a The Force Awakens. Nuestra intención fue utilizar esto debido a su facilidad de conexiones entre los personajes con las películas y objetos en los que aparecen o utilizan duranta las películas.
docker stop mongo
docker rm mongo
export DATA_DIR=`pwd`/data
echo $DATA_DIR
export EX_DIR=`pwd`/mongodb-sample-dataset
echo $EX_DIR
mkdir -p $DATA_DIR
docker run -p 27017:27017 \
-v $DATA_DIR:/data/db \
-v $EX_DIR:/mongodb-sample-dataset \
--name mongo \
-d mongoDentro de Python ejecutar las siguientes sentencias
- A través de las cuales conectamos mongo con python y creamos las distintas colecciones existentes en mongo, dado que no existe un extract all o algo parecido * Nota: la documentación dice que sí se puede a través de https://swapi.py4e.com/api/table/*, pero ¡esto es falso! Solo regresa los primeros 10, por lo tanto tuvimos que extraer todo lo posible iterando
- Nueva sorpresa: la API no está completa, hay indices de URL que no contienen información y devuelven un error 404 :’(
Solución: la iteración se realiza por un while hasta el punto en que se han extraido todos los datos que la api reporta que existen, y el contador no aumenta salvo en el caso de que la respuesta no sea 404. Se apendea en una lista y se utiliza la función insert_many
import requests
from pymongo import MongoClient
client = MongoClient("mongodb://localhost:27017/")
my_db = client["swapi"]
list=["starships", "vehicles", "species", "films", "planets", "people"]
for table in list:
collection = my_db[table]
url = "https://swapi.py4e.com/api/"+table+"/"
i = 1
j = 1
request = requests.get(url)
response = request.json()
limite = response["count"]
print(limite)
res = []
while j <= limite:
url_i = url + str(i)
req = requests.get(url_i)
if req.status_code != 404:
resp = req.json()
res.append(resp)
print(j)
j+=1
i+=1
print("fin de tabla"+table)
collection.insert_many(res)- Primer query, utilizado para extraer la información de mongo y enviarla a monet: practicamente se desglosan los arreglos existentes dentro de cada uno de los fields del json, pero que considere los casos vacíos y nulos
db.people.aggregate([{$unwind:{path:"$films",preserveNullAndEmptyArrays: true}},{$unwind:{path:"$species",preserveNullAndEmptyArrays: true}},{$unwind:{path:"$vehicles",preserveNullAndEmptyArrays: true}},{$unwind:{path:"$starships",preserveNullAndEmptyArrays: true}},{$project:{_id:0}},{$out:"people_monet"}])- Segundo query: Obtener los planetas natales de todos los personajes
db.people.aggregate([
{$project: {"_id":0, "name":1, "homeworld":1}},
{$lookup: {from: "planets", localField: "homeworld", foreignField: "url", as: "planet"}},
{$project: {"name":1, "planet.name": 1}}
])- Tercer query: Buscamos los personajes con las starships mas caras (originalmente contenía un limit(5), lamentablemente hay muchos costos en unknown
db.people.aggregate([
{ $lookup: { from: "starships", localField: "url", foreignField: "pilots", as: "pilot_ship" }},
{ $project: {"name":1, "pilot_ship.name":1, "pilot_ship.cost_in_credits":1}},
{ $unwind: "$pilot_ship"},
{ $group: {_id:"$name", max_costo: {$max: "$pilot_ship.cost_in_credits"}}}
]).sort({"max_costo":-1})Nota: fue importante checar la parte de los delimitadores todavía, porque las comas dentro de los arreglos están ocasionando serios problemas para copiar el csv a monet o a neo
Los datos se sacarán de MongoDB.
Correr en Mongo para sacar el subset de datos que nos interesan
db.people.aggregate([{$unwind:{path:"$films",preserveNullAndEmptyArrays: true}},{$unwind:{path:"$species",preserveNullAndEmptyArrays: true}},{$unwind:{path:"$vehicles",preserveNullAndEmptyArrays: true}},{$unwind:{path:"$starships",preserveNullAndEmptyArrays: true}},{$project:{_id:0}},{$out:"people_monet"}])Ahora sacamos limpiamos un poco los datos de la base para meterlos a MonetDB
docker exec -i mongo mongoexport --db=swapi --collection=people_monet --type=csv -f name,birth_year,eye_color,gender,hair_color,height,mass,skin_color,homeworld,films,species,starships,vehicles,created,edited,url > people_aux.csv
cat people_aux.csv | sed -e 's/, /-/g' -e 's/none//g' -e 's/n\/a//g' -e 's/unknown//g' >people_monet.csvInserción de datos en el Docker
docker cp people_monet.csv monetdb:/var/monetdb5/dbfarm/people.csvNota: https://ugeek.github.io/blog/post/2020-05-27-copiar-directorios-o-archivos-de-un-docker-a-local-o-viceversa.html[Si se requiere ayuda para el copy a docker]
Creación de la base en MonetDB
monetdb create -p monetdb people
#acceso a monetdb con contraseña monetdb
mclient -u monetdb -d people-- creación de esquema people y usuario people
CREATE USER "people" WITH PASSWORD 'people' NAME 'People Explorer' SCHEMA "sys";
CREATE SCHEMA "people" AUTHORIZATION "people";
ALTER USER "people" SET SCHEMA "people";
-- creacion de la tabla
CREATE TABLE people (
name varchar(35),
birth_year varchar(10),
eye_color varchar(20),
gender varchar(17),
hair_color varchar(20),
height int,
mass float,
skin_color varchar(20),
homeworld varchar(60),
films varchar(60),
species varchar(60),
starships varchar(60),
vehicles varchar(60),
created timestamp,
edited timestamp,
url varchar(60));
-- comando de inserción
copy offset 2 into people from '/var/monetdb5/dbfarm/people.csv' on client using delimiters ',',E'\n',E'\"' null as '';- Primer Query, hacemos una consulta para saber cual es el personaje que aparece en más películas.
select p.name, count(distinct p.films) as cont from people p group by p.name order by cont desc limit 10;- Segundo Query, hacemos una consulta que nos indica las estadísticas de masa, altura para cada color de piel. Además, nos da el número de especies que tienen ese color de piel.
select p.skin_color as skin_color,
min(distinct p.mass) as min_mass, avg(distinct p.mass) as avg_mass, max(distinct p.mass) as max_mass, sys.var_pop(distinct p.mass) as variance_mass,
min(distinct p.height) as min_height, avg(distinct p.height) as avg_height, max(distinct p.height) as max_height,sys.var_pop(distinct p.height) as variance_height,
count(distinct p.species) as num_species from people p where p.species is not NULL group by skin_color order by num_species desc;- Tercer Query: buscamos la persona con más años de vida que participaron en la película Return of the Jedi pensando en encontrar quienes lucharon con mayor edad
Nota: Se considera que la batalla de Endor (última escena de esta película) ocurre 4 años después de la Batalla de Yavin (por eso se suman 4 años al birth_date que está calculado en años antes de la Batalla de Yavin) https://starwars.fandom.com/es/wiki/Calendario_Gal%C3%A1ctico_Est%C3%A1ndar[Para sacar de dudas]
select name, CAST(age as real)+4 as tot_age from
(select name, years_alive as age from (select distinct(name), rtrim(birth_year, 'BBY') as years_alive
from people where films='https://swapi.py4e.com/api/films/3/') as sbq1 where sbq1.years_alive<>'null') as sbq2
order by tot_age desc limit 10;Se utilizó una instancia de Neo en AWS de la forma en que se creó en clase.
Para la parte de Neo4j, se exportaron los datos de mongo, completos a csv’s para poder importarlo a neo de una forma más sencilla a través de csv’s dentro de este mismo github:
declare -A cols=( ["people"]="name,birth_year,eye_color,gender,hair_color,height,mass,skin_color,homeworld,films,species,starships,vehicles,created,edited,url" ["films"]="title,episode_id,opening_crawl,director,producer,release_date,species,starships,vehicles,characters,planets,created,edited,url" ["vehicles"]="name,model,vehicle_class,manufacturer,length,cost_in_credits,crew,passengers,max_atmosphering_speed,cargo_capacity,consumables,films,pilots,created,edited,url" ["starships"]="name,model,starship_class,manufacturer,cost_in_credits,length,crew,passengers,max_atmosphering_speed,hyperdrive_rating,MGLT,cargo_capacity,consumables,films,pilots,created,edited,url" ["planets"]="name,diameter,rotation_period,orbital_period,gravity,population,climate,terrain,surface_water,residents,films,created,edited,url" ["species"]="name,classification,designation,average_height,average_lifespan,eye_colors,hair_colors,skin_colors,language,homeworld,people,films,created,edited,url" )
mkdir swapi_csvs
for col in "${!cols[@]}"
do
echo $col
echo ${cols[$col]}
docker exec -i mongo mongoexport --db=swapi --collection=$col --type=csv --fields=${cols[$col]} > /swapi_csvs/$col.csv
donePara la importación de los datos dentro de Neo se utilizó el raw_path de cada uno de los csv’s durante el load.
LOAD CSV WITH HEADERS FROM "https://raw.githubusercontent.com/diego-arellano/proyecto-bdnr/main/people.csv" AS row
CREATE (n:People)
SET n = rowLOAD CSV WITH HEADERS FROM "https://raw.githubusercontent.com/diego-arellano/proyecto-bdnr/main/planets.csv" AS row
CREATE (n:Planets)
SET n = rowLOAD CSV WITH HEADERS FROM "https://raw.githubusercontent.com/diego-arellano/proyecto-bdnr/main/films.csv" AS row
CREATE (n:Films)
SET n=rowLOAD CSV WITH HEADERS FROM "https://raw.githubusercontent.com/diego-arellano/proyecto-bdnr/main/species.csv" AS row
CREATE (n:Species)
SET n=rowLOAD CSV WITH HEADERS FROM "https://raw.githubusercontent.com/diego-arellano/proyecto-bdnr/main/starships.csv" AS row
CREATE (n:Starships)
SET n=rowLOAD CSV WITH HEADERS FROM "https://raw.githubusercontent.com/diego-arellano/proyecto-bdnr/main/vehicles.csv" AS row
CREATE (n:Vehicles)
SET n=rowMATCH (p:People),(pl: Planets)
WHERE p.homeworld = pl.url
CREATE (p)-[:IS_FROM]->(pl)match (p:People) set p.species = replace(p.species, '[', ''), p.species = replace(p.species, ']', ''),
p.species = replace(p.species, '"', '')MATCH (p:People),(s: Species)
WHERE p.species = s.url
CREATE (p)-[:IS]->(s)match (p:People) set p.films = replace(p.films, '[', ''), p.films = replace(p.films, ']', ''),
p.films = replace(p.films, '"', ''), p.films=split(p.films, ",")match (p:People), (f:Films)
where f.url in p.films
create (p)-[:APPEARS_IN]->(f)match (p:People) set p.starships = replace(p.starships, '[', ''), p.starships = replace(p.starships, ']', ''),
p.starships = replace(p.starships, '"', ''), p.starships=split(p.starships, ",")match (p:People), (s:Starships)
where s.url in p.starships
create (p)-[:PILOTS]->(s)match (p:People) set p.vehicles = replace(p.vehicles, '[', ''), p.vehicles = replace(p.vehicles, ']', ''),
p.vehicles = replace(p.vehicles, '"', ''), p.vehicles=split(p.vehicles, ",")match (p:People), (v:Vehicles)
where v.url in p.vehicles
create (p)-[:DRIVES]->(v)match (s:Starships) set s.films = replace(s.films, '[', ''), s.films = replace(s.films, ']', ''),
s.films = replace(s.films, '"', ''), s.films = split(s.films, ',') match (s:Starships), (f:Films)
where f.url in s.films
create (s)-[:APPEARS_IN]->(f)match (v:Vehicles) set v.films = replace(v.films, '[', ''), v.films = replace(v.films, ']', ''),
v.films = replace(v.films, '"', ''), v.films = split(v.films, ',') match (v:Vehicles), (f:Films)
where f.url in v.films
create (v)-[:APPEARS_IN]->(f)match (p:Planets) set p.films = replace(p.films, '[', ''), p.films = replace(p.films, ']', '') ,
p.films = replace(p.films, '"', ''), p.films = split(p.films, ',') match (p:Planets), (f:Films)
where f.url in p.films
create (p)-[:APPEARS_IN]->(f)- Primer Query: Obtenemos un conteo de las especies de todos nuestros personajes
match (p:People)-[r]->(s:Species)
return s.name as especie, count(p.species) as conteo_especies
order by conteo_especies desc- Segundo Query: Obtenemos todas las películas en las que aparecen cada uno de los personajes
match (p:People)-[r]->(f:Films)
return p.name, collect(distinct f.title)- Tercer Query: Obtener la clase de starship más popular por cada especie
match (s:Starships)<-[p:PILOTS]-(p1:People)-[i:IS]->(sp:Species)
WITH distinct sp.name as especie, s.starship_class as clase, count(s.starship_class) as conteo_clases
ORDER BY conteo_clases desc
RETURN especie, collect(clase)[0] as claseFavorita, max(conteo_clases) as num_rep Por último, no nos queda más que agradecerle por todo, queremos decirle que nos la pasamos increíble en cada clase y lo disfrutamos mucho. Ojalá nos veamos después, pero no en la misma materia :)