#WRITING TO CSV
movieReviews <- matrix(c(5,4,4,2,1,1,3,4,4,3,4,1,1,5,5,5,1,5,3,5,5,5,1,5,3,1,3,1,5,1), ncol=6,byrow=TRUE)
colnames(movieReviews) <- c("Crazy Rich Asians",
"Christopher Robin",
"Incredibles 2",
"Hotel Transylvania 3: Summer Vacation",
"Slender Man",
"Napoleon Dynamite")
rownames(movieReviews) <- c("Bobby Gong",
"Steven Ralbovsky",
"Steve Sr",
"Tommy Pickles",
"Ken Richards")
movieReviews <- as.table(movieReviews)
movieReviews
## Crazy Rich Asians Christopher Robin Incredibles 2
## Bobby Gong 5 4 4
## Steven Ralbovsky 3 4 4
## Steve Sr 1 5 5
## Tommy Pickles 3 5 5
## Ken Richards 3 1 3
## Hotel Transylvania 3: Summer Vacation Slender Man
## Bobby Gong 2 1
## Steven Ralbovsky 3 4
## Steve Sr 5 1
## Tommy Pickles 5 1
## Ken Richards 1 5
## Napoleon Dynamite
## Bobby Gong 1
## Steven Ralbovsky 1
## Steve Sr 5
## Tommy Pickles 5
## Ken Richards 1
write.csv(movieReviews, file = csvFile, row.names = TRUE)
#USING MARIADB
library(RMySQL)
## Loading required package: DBI
library(DBI)
## Connect to my-db as defined in ~/.my.cnf Connect to
con <- dbConnect(RMariaDB::MariaDB(), group = "my-db", password = 'toor', db = 'cuny')
dbListTables(con)
## [1] "movie table" "movieTable" "mtcars" "sys.mtcars"
movieTable <- read.csv(file=csvFile, header=TRUE, sep=",")
movieTable
## X Crazy.Rich.Asians Christopher.Robin Incredibles.2
## 1 Bobby Gong 5 4 4
## 2 Steven Ralbovsky 3 4 4
## 3 Steve Sr 1 5 5
## 4 Tommy Pickles 3 5 5
## 5 Ken Richards 3 1 3
## Hotel.Transylvania.3..Summer.Vacation Slender.Man Napoleon.Dynamite
## 1 2 1 1
## 2 3 4 1
## 3 5 1 5
## 4 5 1 5
## 5 1 5 1
dbWriteTable(con, "movieTable", movieTable, overwrite=TRUE)
dbListTables(con)
## [1] "movie table" "movieTable" "mtcars" "sys.mtcars"
dbListFields(con, "movieTable")
## [1] "X"
## [2] "Crazy.Rich.Asians"
## [3] "Christopher.Robin"
## [4] "Incredibles.2"
## [5] "Hotel.Transylvania.3..Summer.Vacation"
## [6] "Slender.Man"
## [7] "Napoleon.Dynamite"
dbReadTable(con, "movieTable")
## X Crazy.Rich.Asians Christopher.Robin Incredibles.2
## 1 Bobby Gong 5 4 4
## 2 Steven Ralbovsky 3 4 4
## 3 Steve Sr 1 5 5
## 4 Tommy Pickles 3 5 5
## 5 Ken Richards 3 1 3
## Hotel.Transylvania.3..Summer.Vacation Slender.Man Napoleon.Dynamite
## 1 2 1 1
## 2 3 4 1
## 3 5 1 5
## 4 5 1 5
## 5 1 5 1
# You can fetch all results:
res <- dbSendQuery(con, "SELECT * FROM movieTable")
dbFetch(res)
## X Crazy.Rich.Asians Christopher.Robin Incredibles.2
## 1 Bobby Gong 5 4 4
## 2 Steven Ralbovsky 3 4 4
## 3 Steve Sr 1 5 5
## 4 Tommy Pickles 3 5 5
## 5 Ken Richards 3 1 3
## Hotel.Transylvania.3..Summer.Vacation Slender.Man Napoleon.Dynamite
## 1 2 1 1
## 2 3 4 1
## 3 5 1 5
## 4 5 1 5
## 5 1 5 1
dbClearResult(res)
# Clear the result
dbClearResult(res)
## Warning: Expired, result set already closed
# Disconnect from the database
dbDisconnect(con)
#USING SPARK
#http://spark.rstudio.com/
library(sparklyr)
library(DBI)
library(dplyr)
##
## Attaching package: 'dplyr'
## The following objects are masked from 'package:stats':
##
## filter, lag
## The following objects are masked from 'package:base':
##
## intersect, setdiff, setequal, union
#spark_install(version = "2.1.0")
sc <- spark_connect(master = "local")
## * Using Spark: 2.1.0
movieRDD <- spark_read_csv(sc, name = "movieTableSpark", path = csvFile, header = TRUE, delimiter = ",")
#createOrReplaceTempView(movieRDD, "movieRDD")
movieRDD <- sql("SELECT * FROM movieRDD")
movieRDD
## <SQL> SELECT * FROM movieRDD