Migrating database from MySQL to Neo4j

devtools::install_github("nicolewhite/Rneo4j") 
install.packages("RMySQL")

library(RMySQL)

library(RNeo4j)
library(knitr)

# connect to MySQL local database
movie_con <- dbConnect(MySQL(), user="root", password=mysql_pw, dbname = "movie_nosql", host = "localhost")

#Send query to MySQL and retrieve the movie data from MySQL.
query.movie <- dbSendQuery(movie_con, "SELECT * FROM movie")
movie.data <- dbFetch(query.movie, n=-1)

#Send query to MySQL and retrieve the friends data from MySQL. Friends are the people who rated the movies
query.friends <- dbSendQuery(movie_con, "SELECT * FROM friends")
friends.data <- dbFetch(query.friends, n=-1)

#Send query to MySQL and retrieve the rating data from MySQL.
query.rating <- dbSendQuery(movie_con, "SELECT * FROM rating")
rating.data <- dbFetch(query.rating, n=-1)

#send query to MySQL and retrieve the actor data from MySQL
query.actor <- dbSendQuery(movie_con, "SELECT * FROM actor")

actor.data <- dbFetch(query.actor, n=-1)

#send query to MySQL and retrieve the director data from MySQL
query.director <- dbSendQuery(movie_con, "SELECT * FROM director")
director.data <- dbFetch(query.director, n=-1)
#send query to MySQL and retrieve the writer data from MySQL
query.writer <- dbSendQuery(movie_con, "SELECT * FROM writer")
writer.data <- dbFetch(query.writer, n=-1)

#send query to MySQL and retrieve the genre data from MySQL
query.genre <- dbSendQuery(movie_con, "SELECT * FROM genre")
genre.data <- dbFetch(query.genre, n=-1)

#send query to MySQL and retrieve the country data from MySQL
query.country <- dbSendQuery(movie_con, "SELECT * FROM country")
country.data <- dbFetch(query.country, n=-1)

#send query to MySQL and retrieve the country data from MySQL
query.omdb <- dbSendQuery(movie_con, "SELECT * FROM omdb")
omdb.data <- dbFetch(query.omdb, n=-1)


kable(head(rating.data, n=7), caption = "Rating - the main table")

kable(head(movie.data, n=6),caption = "Movie Table")

kable(head(friends.data, n=5), caption = "Audience Table")

kable(head(actor.data, n=5),caption = "Actor Table")

kable(head(director.data, n=5),caption = "Director Table")

kable(head(writer.data, n=5),caption = "Writer Table")

kable(head(genre.data, n=3),caption = "Genre Table")

kable(head(country.data, n=3),caption = "Country Table")

kable(head(omdb.data, n=3),caption = "Omdb Table")

write.csv(movie.data, "movie.csv")
write.csv(friends.data, "friends.csv")
write.csv(rating.data, "rating.csv")
write.csv(actor.data, "actor.csv")
write.csv(director.data, "director.csv")
write.csv(writer.data, "writer.csv")
write.csv(genre.data, "genre.csv")
write.csv(country.data, "country.csv")
write.csv(omdb.data, "omdb.csv")

dbDisconnect(movie_con)

friends_d <- read.csv(file="https://raw.githubusercontent.com/YunMai-SPS/DA607/master/DA607_homework13/friends.csv", header=TRUE, sep=",", stringsAsFactors = F)

movie_d <- read.csv(file="https://raw.githubusercontent.com/YunMai-SPS/DA607/master/DA607_homework13/movie.csv", header=TRUE, sep=",", stringsAsFactors = F)

rating_d <- read.csv(file="https://raw.githubusercontent.com/YunMai-SPS/DA607/master/DA607_homework13/rating.csv", header=TRUE, sep=",", stringsAsFactors = F)

actor_d <- read.csv(file="https://raw.githubusercontent.com/YunMai-SPS/DA607/master/DA607_homework13/actor.csv", header=T, sep=",", stringsAsFactors = F)

director_d <- read.csv(file="https://raw.githubusercontent.com/YunMai-SPS/DA607/master/DA607_homework13/director.csv", header=TRUE, sep=",", stringsAsFactors = F)

writer_d <- read.csv(file="https://raw.githubusercontent.com/YunMai-SPS/DA607/master/DA607_homework13/writer.csv", header=TRUE, sep=",", stringsAsFactors = F)

genre_d <- read.csv(file="https://raw.githubusercontent.com/YunMai-SPS/DA607/master/DA607_homework13/genre.csv", header=TRUE, sep=",", stringsAsFactors = F)

country_d <- read.csv(file="https://raw.githubusercontent.com/YunMai-SPS/DA607/master/DA607_homework13/country.csv", header=TRUE, sep=",", stringsAsFactors = F)

omdb_d <- read.csv(file="https://raw.githubusercontent.com/YunMai-SPS/DA607/master/DA607_homework13/omdb.csv", header=TRUE, sep=",", stringsAsFactors = F)

#connect to Neo4j
graph = startGraph("http://localhost:7474/db/data", username = "neo4j", password = neo4j.pw)

#delete all nodes, relationships, indexes, and constraints from the graph database
clear(graph, input = F)

# nodes to be created: 
# Nodes:
#   movie              
#   audience          
#   actor             
#   director                
#   writer              
#   genre
#   country
# the same nodes will be merged.


query = "
         MERGE (movie:Movie {name: {title1}, id:{movieid1}})
         CREATE (audience:Audience {name:{audname},id1:{audid},id2:{movieid2}, rating:{friendrating}})
         CREATE (audience)-[r:RATED]->(movie)
         SET r.rating = {friendrating}
         RETURN audience,movie         
"
tx = newTransaction(graph)
for (i in 1:nrow(rating_d)){
    appendCypher(tx,query,
                title1 = rating_d$MovieName[i],
                movieid1 = rating_d$MovieID[i],
                audname = rating_d$FriendName[i],
                audid = rating_d$FriendID[i],
                movieid2 = rating_d$MovieID[i],
                friendrating = rating_d$FriendRating[i])
               
}
commit(tx)

summary(graph)

# query = "
#         MATCH (movie:Movie {id:{movieid1}})
#         MATCH (audience:Audience {id1:{audid})
#         CREATE (audience)-[r:RATED]->(movie)
#         SET r.rating = {friendrating}
#         RETURN audience,movie         
#"


#tx = newTransaction(graph)
#for (i in 1:nrow(actor_d)){
#  appendCypher(tx,query,
#                actname = actor_d$actors[i],
#                actorid = actor_d$actorID[i],
#                movietitle = actor_d$title[i],
#                title1 = actor_d$title[i])
#}
#commit(tx)

# query = "
#         CREATE (actor:Actor {name:{actname}, id:{actorid}), #title:{movietitle})
#         MATCH (movie:Movie {name: {title1}})
#         MATCH (actor:Actor {name:{actname}})
#         CREATE (actor)-[a:ACTED_IN]->(movie)
#"
#tx = newTransaction(graph)
#appendCypher(tx,query)
#commit(tx)

# nodes to be created: 
# Nodes:
#   movie              
#   audience          
#   actor             
#   director                
#   writer              
#   genre
#   country
# the same nodes will be merged.

# Relationships
#  movie            -[:belongs_to]-> genre
#  movie            -[:produced_in]-> country
#  audience         -[:rated_in]->   movie
#  actor            -[:acted_in]-> movie
#  director         -[:directed]-> movie
#  writer           -[:writed_scripts]->  movie

#connect to Neo4j
graph = startGraph("http://localhost:7474/db/data", username = "neo4j", password = neo4j.pw)

#delete all nodes, relationships, indexes, and constraints from the graph database
clear(graph, input = F)

for (i in 1:nrow(rating_d)){
    movie.node <- createNode(graph, "Movie", name=rating_d$MovieName[i], id=rating_d$MovieID[i])
}

for (i in 1:nrow(rating_d)){
    audience.node <- createNode(graph, "Audience", name=rating_d$FriendName[i], id1=rating_d$FriendID[i],id2=rating_d$MovieID[i],moviename=movie_d$MovieName[i])
}

for (i in 1:nrow(actor_d)){
    actor.node <- createNode(graph, "Actor", name=actor_d$actors[i], id=actor_d$actorID[i], movietitle = actor_d$title[i])
}

for (i in 1:nrow(director_d)){
    director.node <- createNode(graph, "Director", name=director_d$directors[i], id=director_d$directorID[i], movietitle = director_d$title[i])
}

for (i in 1:nrow(writer_d)){
    writer.node <- createNode(graph, "Writer", name=writer_d$writers[i], id=writer_d$writerID[i], movietitle = writer_d$title[i])
}

for (i in 1:nrow(genre_d)){
    genre.node <- createNode(graph, "Genre", name=genre_d$genres[i], id=genre_d$genreID[i], movietitle = genre_d$title[i])
}

for (i in 1:nrow(country_d)){
    country.node <- createNode(graph, "Country", name=country_d$countries[i], id=country_d$countryID[i], movietitle = country_d$title[i])
}

movie_query = "
MATCH(a:Movie) WHERE a.id={id}
RETURN a
"
audience_query = "
MATCH(b:Audience) WHERE b.id2={id2} AND b.name = {name}
RETURN b
"

for (i in 1:nrow(rating_d)) {
    movie_rated <- getSingleNode(graph, movie_query, id=rating_d$MovieID[i])
    audience_rated <- getSingleNode(graph, audience_query, id2=rating_d$MovieID[i], name=rating_d$FriendName[i])
    createRel(audience_rated, "RATED", movie_rated, rating = rating_d$FriendRating[i])
}
summary(graph)

movie_query = "
MATCH(a:Movie) WHERE a.name={name}
RETURN a
"
actor_query = "
MATCH(b:Actor) WHERE b.movietitle={movietitle} AND b.name={name}  
RETURN b 
"
 
for (i in 1:nrow(actor_d)){
    movie_rated <- getSingleNode(graph, movie_query, name=rating_d$MovieName[i])
    actor_related  <- getSingleNode(graph, actor_query, name=actor_d$actors[i], id=actor_d$id[i], movietitle=actor_d$title[i])
    createRel(actor_related, "ACTED_IN", movie_rated)
}
summary(graph)

movie_query = "
MATCH(a:Movie) WHERE a.name={name}
RETURN a
"
director_query = "
MATCH(b:Director) WHERE b.movietitle={movietitle} AND b.name={name}  
RETURN b
"
for (i in 1:nrow(director_d)){
    movie_rated <- getSingleNode(graph, movie_query, name=rating_d$MovieName[i])
    director_related  <- getSingleNode(graph, director_query, name=director_d$directors[i], movietitle=director_d$title[i])
    createRel(director_related, "DIRECTED", movie_rated)
}
summary(graph)

movie_query = "
MATCH(a:Movie) WHERE a.name={name}
RETURN a
"
writer_query = "
MATCH(b:Writer) WHERE b.movietitle={movietitle} AND b.name={name}  
RETURN b
"
for (i in 1:nrow(writer_d)){
    movie_rated <- getSingleNode(graph, movie_query, name=rating_d$MovieName[i])
    writer_related  <- getSingleNode(graph, writer_query, name=writer_d$writers[i], movietitle=writer_d$title[i])
    createRel(writer_related, "WROTE", movie_rated)
}
summary(graph)

movie_query = "
MATCH(a:Movie) WHERE a.name={name}
RETURN a
"
genre_query = "
MATCH(b:Genre) WHERE b.movietitle={movietitle} AND b.name={name}  
RETURN b
"
for (i in 1:nrow(genre_d)){
    movie_rated <- getSingleNode(graph, movie_query, name=rating_d$MovieName[i])
    genre_related  <- getSingleNode(graph, genre_query, name=genre_d$genres[i], movietitle=genre_d$title[i])
    createRel(movie_rated, "BELONGS_TO", genre_related)
}
summary(graph)

movie_query = "
MATCH(a:Movie) WHERE a.name={name}
RETURN a
"
country_query = "
MATCH(b:Country) WHERE b.movietitle={movietitle} AND b.name={name}  
RETURN b
"
for (i in 1:nrow(country_d)){
    movie_rated <- getSingleNode(graph, movie_query, name=rating_d$MovieName[i])
    country_related  <- getSingleNode(graph, country_query, name=country_d$countries[i], movietitle=country_d$title[i])
    createRel(movie_rated, "PRODUCED_IN", country_related)
}
summary(graph)

query = "
MATCH (audience:Audience)-[r:RATED]->(movie:Movie)
WHERE audience.name = 'Ming'
RETURN audience.name, r.rating, movie.name
"
cypher(graph, query)

query = "
MATCH (audience:Audience)-[r:RATED]->(movie:Movie)
WHERE audience.name = 'Ming'
RETURN audience.name, COLLECT(movie.movie) AS seen
"

cypherToList(graph, query)

query = "
MATCH (audience:Audience)-[r:RATED]-(movie:Movie {name:'Beauty and the Beast'}) 
RETURN audience.name, movie.name,r.rating"

cypher(graph, query)

query = "
MATCH (audience:Audience)-[r:RATED]-> (movie:Movie)
WHERE audience.name = {name1} AND movie.name = {name2}
RETURN audience.name, r.rating, movie.name
"

cypher(graph, query, name1="Hao", name2="The Fate of the Furious")

graph = startGraph("http://localhost:7474/db/data/", username="neo4j", password=neo4j.pw)
browse(graph)